Prosper’s public data does not have a primary key foreign key relationship between the loan and listing table.  This keeps one from being able to correlate loans to listings which prosper does intentionally for privacy reasons. However, the other data in both tables is adequate to match on ~99.8% of the loans.  I think this is really bad because the data is freely and openly available.  While I am under Prosper Terms of Service as a lender, the link provided below requires no agreement.  That means a lay person can download the data use similar techniques to find and publish all late loans with links to the listings in turn linking to the actual borrowers.

I started off trying to produce a single sql statement to perform the correlation.  However, after a couple of days of thought and experiments, I have changed my strategy for producing a loan to listing link out of the freely available data from prosper.

The approach I am taking now adds a listingkey column to the loan table. (Very useful for me since I use crystal reports.)  It takes several sql statements to add the column and set the value without duplicates through a multipass approach.  Today’s data yielded 7575/7591 loans identified… 99.8%.

The entire process takes less than 10 seconds.

The Prosper data is openly and freely available here: ProsperDataExport_xml.zip

I am still not sure of the legal or moral arguments around publishing the sql script to the world, however… A $5 dollar donation and a request (kevin at rateladder.com) will get you a copy for personal use.  Don’t share.  Don’t publish. Your internal use only for analyzing loan opportunities.  Don’t attribute loan/listing correlation to RateLadder for any purpose without prior written permission. No Warranty. No promise to be accurate. Past success is no guarantee it will work in the future.

 

If you liked this article, vote for it on del.icio.us and stumbleupon.


Categories:

Prosper.com, Statistics, Tools