cooling_dude
Bye Bye's for the PPI
Posts: 2,853 
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 12:10:37 GMT
SS Negative Term WatchThe above google sheet is completely automatic (it updates every hour - please note that the data will sometimes disappear and reappear as the worksheet fetches data). It is split into 4 sections > Defaulted Loans - Interest Accruing > Interest Accruing > Interest Serviced by Lendy > Approaching Negative Term (31days to go) I've never tried scraping with google sheets, so if there may be errors; if you spot any please let me know. If you want to see any additional information (which can be published on this forum) it shouldn't be a problem, just add a post to this thread Note to MODs - please don't pin (getting a tad cluttered up there! I'll add a link in my index, and I'm sure ilmoro will do the same). ilmoro - maybe create two links to your STATUS CHANGES thread? (one direct to the Google Sheet, and one to this thread to prevent members commenting about it on your thread) Possibly keep general 'negative term' & 'default' conversation in this thread (a suggestion, not an order!)
|
|
am
Posts: 1,495
Likes: 601
|
Post by am on Mar 4, 2017 12:56:14 GMT
If it's not too difficult perhaps add percentage of loan book, and various (sub)totals (e.g. total defaulted, late, distressed = late or defaulted).
I suspect that the necessary data has disappeared, but another number I'd be mildly interested in is days over the original term.
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 13:06:14 GMT
If it's not too difficult perhaps add percentage of loan book, and various (sub)totals (e.g. total defaulted, late, distressed = late or defaulted). I suspect that the necessary data has disappeared, but another number I'd be mildly interested in is days over the original term. Your first line should be easy Term will be a one off manual addition on each loan as they go live (data in the background); while I'm at it I can also add total term (days since drawdown) I'll get to it later today 
|
|
|
|
Post by dinnerlady on Mar 4, 2017 13:20:07 GMT
Wow! fantastic work and really appreciated! great to see in this format, gives a clear picture of loans, although rather concerning as the balance of loans in negative territory appears to outgrowing the current supply.
|
|
pom
Member of DD Central
Posts: 1,922 
Likes: 1,244
|
Post by pom on Mar 4, 2017 15:47:31 GMT
Why do I get the feeling both you and ilmoro are already gunning for next years awards?  |
|
jonah
Member of DD Central
Posts: 2,031
Likes: 1,113
|
Post by jonah on Mar 4, 2017 15:57:54 GMT
Why do I get the feeling both you and ilmoro are already gunning for next years awards? Awards you say... there is an idea...
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 16:21:25 GMT
very nice, cooling_dude . I'm not positive it's what you need, but if your filter/index calls are pulling something out of a table on a hidden tab, take a look at the vlookup function (which might simplify formulas a little). Thanks vlookup is the function I started with, but I encountered several issues (most of which I wouldn't have come across on Excel because I could have utilised a table, which is not an option on Google Sheets). =Index & =filter are my new best friends 
|
|
|
|
Post by lendinglawyer on Mar 4, 2017 16:40:27 GMT
This is an excellent resource. Really puts in perspective quite how many seriously overdue loans there are and the next 2 weeks or so are going to see a high value of defaults. Fortunately (from a selfish perspective) I don't hold anything on this list but I would rather there were fewer loans in this situation for the sake of platform and SM stability in general!
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 17:29:29 GMT
If it's not too difficult perhaps add percentage of loan book, and various (sub)totals (e.g. total defaulted, late, distressed = late or defaulted). I suspect that the necessary data has disappeared, but another number I'd be mildly interested in is days over the original term. Percentage of loan book & subtotals added Also added a total term (days since drawdown) and original term (data will be added over the coming hour - I need to do the entire loan book, so may take a while!)
|
|
am
Posts: 1,495
Likes: 601
|
Post by am on Mar 4, 2017 17:46:09 GMT
If it's not too difficult perhaps add percentage of loan book, and various (sub)totals (e.g. total defaulted, late, distressed = late or defaulted). I suspect that the necessary data has disappeared, but another number I'd be mildly interested in is days over the original term. Percentage of loan book & subtotals added Also added a total term (days since drawdown) and original term (data will be added over the coming hour - I need to do the entire loan book, so may take a while!) Thanks. A minor bug - some of the emboldenment is on the wrong line - the line belong the subtotals, rather than the subtotals. Update (further comment): the colour contrast between IA and SBL loans is rather weak - inserting the subtotals has the beneficial effect of drawing a more easily visible line between them.
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 18:06:50 GMT
Percentage of loan book & subtotals added Also added a total term (days since drawdown) and original term (data will be added over the coming hour - I need to do the entire loan book, so may take a while!) Thanks. A minor bug - some of the emboldenment is on the wrong line - the line belong the subtotals, rather than the subtotals. Update (further comment): the colour contrast between IA and SBL loans is rather weak - inserting the subtotals has the beneficial effect of drawing a more easily visible line between them. Bold Should be fixed and the colouring has been tweaked
|
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Mar 4, 2017 18:35:31 GMT
Thanks. A minor bug - some of the emboldenment is on the wrong line - the line belong the subtotals, rather than the subtotals. Update (further comment): the colour contrast between IA and SBL loans is rather weak - inserting the subtotals has the beneficial effect of drawing a more easily visible line between them. Bold Should be fixed and the colouring has been tweaked Yet another excellant resource - thank you very much.  Just one question - is the % loan book referring to the total ever loan book or the current live (ie not repaid) loans. If it is the later then the numbers don't add up to 100%, if the former what is that telling us?
|
|
Liz
Member of DD Central
Posts: 2,426
Likes: 1,297
|
Post by Liz on Mar 4, 2017 18:42:47 GMT
PBL006's LTV doesn't take into account 1st charge, from the description:
"This is a 6 month loan. The property valuation is available above. The loan has now gone live. All investors will earn 12%pa immediately upon commitment to this loan. Please be aware that there is an existing first charge on this bridging loan so the overall LTV is actually 59% LTV."
Edit PBL035 also has a 1st charge.
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 18:50:38 GMT
Bold Should be fixed and the colouring has been tweaked Yet another excellant resource - thank you very much. Just one question - is the % loan book referring to the total ever loan book or the current live (ie not repaid) loans. If it is the later then the numbers don't add up to 100%, if the former what is that telling us? It indicates the % against "all current live loan" (i will clarify this in the column heading later). With individual loans it will show how large a loan is relative to the book (and thus what effect it will have on the PF if there are any shortfalls during a default); the subtotals show what state of saving streams current loan book is in
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Mar 4, 2017 18:53:14 GMT
PBL006's LTV doesn't take into account 1st charge, from the description: "This is a 6 month loan. The property valuation is available above. The loan has now gone live. All investors will earn 12%pa immediately upon commitment to this loan. Please be aware that there is an existing first charge on this bridging loan so the overall LTV is actually 59% LTV." Edit PBL035 also has a 1st charge. I tried to cheat and calculate the % on the sheet, forgetting there are some second charges. This will be fixed in the next hour
|
|
