Prosper Vintage Curves From Private Data | P2P Lending, Peer to Peer Lending, People to People Lending

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 ExchangeRelated PostsBlog Traffic ExchangeRelated Websites

  • Australian swine flu cases riseWe mentioned in our last posting on swine flu that if cases in Australia increased significantly, the World Health Organization (WHO) may be forced to declare A(H1N1) a pandemic. Cases in Australia have continued to mount. Friday’s figure was 876 confirmed cases in Australia, according to WHO statistics, which are……
  • New Hosting(s)/caption] Spent most of the last few days mucking around with new hosting setup. The host is okay, I’ve just had a lot of trouble getting slightly more complex things done with the blogs (three going now) due to my lack of knowledge on Unix file systems, permissions etc……
  • Subtotaling dataExcel’s subtotal feature is accessed via the Data tab of the Ribbon and is used with a sorted list. It’s not useful for every single type of database you can create, but only for those in which values can be kept track of for specific, repeated factors. Figure 19-19, for……
  • L’Instant Perfume by Guerlain ReviewThe L’Instant Perfume by Guerlain is a perfume that was immediately destined to become a true classic, because this perfume by Guerlain is both sensual and feminine in nature. The L’Instant Perfume by Guerlain is a sexy, smooth and sophisticated fragrance blend that incorporates notes of citrus honey, amber and……
  • Frugal Friday! 5 Easy Ways to Spend LessBeing frugal isn’t about becoming miserly and miserable. It’s about making sound financial choices that will make you prosper, save you money in the long run, and re-evaluate your connection with consumerism. Every Friday I give you my tip of the week on all things frugal – except this week……

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

Categories:

Uncategorized

Tags:

Leave a Reply

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