Prosper Vintage Curves From Private Data

You may have seen my Prosper vintage curves…  While they were hard to make since the data had to be pulled off the Prosper Loan Performance page.

With the new private lender data these vintage curves can be pulled with sql and excel greatly expediting the process.

Check out the Daily Count Late Percentage Vintage curve…

General Loan Population Vintage Curves Count Late

Using this sql:

(You need a table alldays with 1 column aday and a row for every day).  (Unfortunately this sql takes a long time to run (10 minutes) so you won’t be able to run it on ProProsper as the query timeout is set to 30 seconds).

DECLARE @DTD int
SET @DTD=30
SELECT
cast(aday-originationdate as int) as 'PIT',
l.creditgrade,
sum(PrincipalBalance+NetDefaults) as 'Amount',
count(l.[key]) as 'Count',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN PrincipalBalance+NetDefaults ELSE 0 END) as 'AmountLate',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN PrincipalBalance+NetDefaults ELSE 0 END)/sum(PrincipalBalance+NetDefaults) as AmountLatePercentage,
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN 1 ELSE 0 END) as 'CountLate',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN 1.0 ELSE 0.0 END)/count(l.[key]) as 'CountLatePercentage'
FROM
loan l
inner join creditprofile cp on cp.listingkey=l.listingkey
inner join LoanPerformance mld on l.[key]=mld.loankey cross join alldays
where
mld.observationdate = ( select top 1 observationdate from LoanPerformance sub where sub.observationdate < aday and sub.loankey=mld.loankey order by sub.observationdate DESC )
and aday < getDate()
and aday >= '02/01/2006'
and l.creditgrade!='NC'
group by
cast(aday-originationdate as int),
l.creditgrade
having
count(l.[key])>250 and
sum(PrincipalBalance+NetDefaults)>0
order by
'PIT'

Blog Traffic Exchange Related Posts
  • blog traffic exchangeBidding With the Prosper API Code Example QuickSnipe.java For my ProsperDays presentation this year I will demonstrating bidding with the Prosper API...  I have built a quick and dirty bid snipping java program.  (This program is not ready for primetime as bid sniping tool, but it is very close and a very effective demo.  The reason I say......
  • 1 Month Late Curves Count1 Month Late or Worse Curves by Credit Grade (July 1 Update) This is an update to my original 1 month late or worse curves. I belive we are starting to see the flattening of the curves. I have pulled the public data on the performance tab by hand in order to produce a spreadsheet of loan performance. This spreadsheet was then......
  • Lender Rate HistoryProsper Interest Rates On the Rise Prosper interest rates are on the rise -- in case you where wondering this is a good thing for lenders!  The release of the lender bidding guidance (general announcement, secondary analysis) seems to line up with a rise in near prime and sub prime interest rates.  Similarly, the graph that......
  • Vintage Curves 12/2007Prosper Vintage Curves 12/1/2007 Today we are updating the vintage curves for 12/1/2007.  (Here is the original explanation.) The number of loans required for display is 250. There are 3 different populations shown across both count late % and amount late %. General population. All loans in credit grade. Loan Count Late % Dollar......
  • blog traffic exchangeLending Club Loan Volume Misquoted in National Newspaper I encourage all reporters to check facts. In the p2p lending space, both major US p2p lending companies provide data and this data can usually corroborate or debunk basic marketplace claims.  I was reading a recent Pittsburgh Post-Gazette article on p2p lending: The credit crunch: How did it happen and where......
Online Stores If you liked this article, vote for it on del.icio.us and stumbleupon.


Categories:

Uncategorized



Tags:

,


6 comments ↓
#1 Little Troll on 12.17.07 at 1:40 pm

Can you please post this on the prospers.org forum. I haven’t seen it yet on my radar.

#2 Carnival of Peer-To-Peer Lending #1 on 01.04.08 at 10:16 am

[...] Kevin presents Prosper Vintage Curves From Private Data [...]

#3 » 1st Ever Carnival of P2P Lending on 01.04.08 at 10:37 am

[...] If you’re new here, you may want to subscribe to my RSS feed or via email. Thanks for visiting!LazyMan is hosting the first ever carnival of peer-to-peer lending.  Thank you Lazy for including my post (Prosper Vintage Curves From Private Data)  in the rotation. [...]

#4 Prosper Blog: Prosper, the online marketplace for people-to-people lending » Blog Archive » Prosper Roundup — Prosper Blog Nominated For An Annual Award(?) Edition on 01.06.08 at 8:38 am

[...] Prosper Vintage Curves From Private Data (Updated Curves: Prosper Vintage Curve Update — 1/1/2008) [...]

#5 First Peer-to-Peer Lending Carnival | Personal Loan Portfolio on 01.06.08 at 7:12 pm

[...] RateLadder posted new Prosper Vintage Curves. He posted previously an explanation of vintage curves if the graph leaves you scratching your head. [...]

#6 Prosper 1 Month Late or Worse Curves | P2P Lending, Peer to Peer Lending, People to People Lending | P2P Lending News, Information, Borrowing and Lending Strategy on 07.23.08 at 7:54 am

[...] methodolgy or the sql for obtaining the data these curves from the private data export has already been explained in detail, but [...]

Leave a Comment

aa algorithm axis azerbaijan banks benefit blog borrowers ceo Collections credit grade credit history credit information credit score curves doug fuller email friends and family good luck google income borrowers interest rate interest rates investments launch lenders lending institutions liquidity loans marketplace money mozambique peer to peer lending portfolio plan private data promissory notes quiet period registration statement risk securities and exchange securities and exchange commission spreadsheets sql student loans Zopa