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…
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
cast(aday-originationdate as int) as ‘PIT’,
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’
inner join creditprofile cp on cp.listingkey=l.listingkey
inner join LoanPerformance mld on l.[key]=mld.loankey cross join alldays
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′
cast(aday-originationdate as int),
Related PostsRelated 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.