Successfully Added ListingKey to Loan Table | P2P Lending, Peer to Peer Lending, People to People Lending

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:

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 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.

Blog Traffic ExchangeRelated PostsBlog Traffic ExchangeRelated Websites

  • Tips for Paying Off Student LoansRight now, the student loan industry is going through one of its worst periods in decades. New Federal regulations have forced many banks to stop offering student loans, and students are being forced to either find a direct loan or start paying back what they owe. Enrollment figures are being……
  • What Happened to Prosper on Lazy Man?Last week, Get Rich Slick asked the question, What Happened To All The Blogs? Two years ago, along with RateLadder, I probably wrote about Prosper more than most personal finance bloggers. So when RichSlick asks why the blogs have seemingly gone silent, I feel that I should stand up……
  • Improving Money Behavior with Online Budget ToolsWe have all heard (or experienced ourselves) that “there is too much month left at the end of the money” and “I just don’t know where all my money goes!” These statements are used to explain why debt reduction plans are not implemented or cannot be completed. Anyone with a computer……
  • Mortgages: VA vs FHA vs Conventional LoansBuying a home is one the biggest investments anybody can make. Therefore, it’s best to consider a number of financing options once you’ve found the home you wish to buy. Seldom do homebuyers pay off their home quickly, meaning you’re entering into a long-term investment. Because of the duration and……
  • Making Extra Money With P2P LendingPeer to Peer lending is quickly becoming one of the hottest new ways to create multiple streams of income. While there is risk involved, there are various methods that can reduce the risks that individual investors face and provide protection against non-payments. If you’re looking for an potential way to……

Online StoresIf you liked this article, vote for it on and stumbleupon.



Leave a Reply

Your email address will not be published. Required fields are marked *