stevio
Member of DD Central
Posts: 2,065 
Likes: 894
|
Post by stevio on Sept 16, 2015 21:15:42 GMT
Trying to track my portfolio of loans on SS.
Currently copying and pasting from Live Loans Part page and sorting by PBL number. However I have to manually add the totals of multiples of the same PBL, which is a pain, particularly with multiple PBLs.
Also, the size of the loan and LTV is not seen on Live Loan Part page, so I then have to copy then in beside the loans.
Just wondering if anyone has found an easier way? Maybe a way I just add whats new, but then I would also have to take away what sold?
Thanks
|
|
|
|
Post by solicitorious on Sept 16, 2015 21:59:48 GMT
A pivot table should add up all the bits for each loan for you. Only needs to be done once every so often.
I then use a separate manual sheet listing all my totals per loan. Buying or selling I just use mental arithmetic to update this. Buys are almost instantaneous of course, selling can take longer, so I have a column for pending sales, which I then deduct when completed. Some nice totals at the bottom for total portfolio, amount uninvested, etc, plus lots of other fancy stuff to record LTVs with and without the PF, quartiles of risk and %invested in each quartile, %invested in each loan, % of loan I hold, max invested, average invested, etc.
|
|
SteveT
Member of DD Central
Posts: 6,873
Likes: 7,918
|
Post by SteveT on Sept 17, 2015 11:10:18 GMT
You're welcome to a copy of mine, attached, which just does the basic job of totalling by loan and tracks % split across each loan. [I've replaced my data with dummy loan part numbers and values] To use it, simply highlight all your live loan parts on the SS website (exclude the headers at the top and the total at the bottom) and then Copy. Then click on the yellow-shaded cell (A2) on the "Loans Extract" sheet and Paste Special - Text. [Of course, if you have fewer than the 33 dummy loan parts currently in the proforma, simply delete any remaining dummy rows] Then go to the "Loans Pivot Table" sheet, right-click anywhere on the table and Refresh (to update the pivot table with the latest data). I use the Comments column to note how close certain loans are to repayment. You can sort the table by whichever column you prefer (right-click at the top of the column and Sort). NB. Column J on the "Loans Extract" page is a calculated field rather than part of the data extract. It just turns the text string that SS brings across as "Remaining" into a clean number that Excel can interpret in the pivot table. If you delete it by mistake, the formula you want in each cell of column J (starting in cell J2) is =SUBSTITUTE(G2," days","")+0 and then copy it down into all rows containing your loan parts. Saving Stream - blank.xlsx (20.65 KB) |
|
webwiz
Posts: 1,133
Likes: 210
|
Post by webwiz on Sept 17, 2015 12:12:39 GMT
Nice one Steve. My Jurassic era Excel would not open it but Open Office did the trick.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Sept 17, 2015 12:17:16 GMT
Nice one Steve. My Jurassic era Excel would not open it but Open Office did the trick. You can also try Google Sheets, which has the added benefit of working on tablets/phones whilst travelling.
|
|
mikes1531
Member of DD Central
Posts: 6,452
Likes: 2,320
|
Post by mikes1531 on Sept 17, 2015 12:57:21 GMT
You're welcome to a copy of mine, attached, which just does the basic job of totalling by loan and tracks % split across each loan. : Thanks so much. It's great! Spreadsheets can do such wonderful things if you know how to use their powerful features. Unfortunately, I don't.
|
|
will
Member of DD Central
Posts: 107
Likes: 8
|
Post by will on Sept 17, 2015 13:42:18 GMT
Hey stevet, that's truly magical.
Even I can do it!
Many thanks.
|
|
stevio
Member of DD Central
Posts: 2,065
Likes: 894
|
Post by stevio on Sept 17, 2015 13:58:59 GMT
You're welcome to a copy of mine, attached, which just does the basic job of totalling by loan and tracks % split across each loan. [I've replaced my data with dummy loan part numbers and values] To use it, simply highlight all your live loan parts on the SS website (exclude the headers at the top and the total at the bottom) and then Copy. Then click on the yellow-shaded cell (A2) on the "Loans Extract" sheet and Paste Special - Values. Then go to the "Loans Pivot Table" sheet, right-click anywhere on the table and Refresh (to update the pivot table with the latest data). I use the Comments column to note how close certain loans are to repayment. You can sort the table by whichever column you prefer (right-click at the top of the column and Sort). Thanks for this! Should make life a lot easier Just one question, the Loans Extract page has a final column of 'days' which doesn't seem to exist in Live Loan Parts, so no data is transfered across - is there a way of using 'remaining days' instead? I tried clicking on 'remaining days' in 'loans pivot table' but I seem to have broke it! (added +/- to each loan part !!!) Thanks
|
|
SteveT
Member of DD Central
Posts: 6,873
Likes: 7,918
|
Post by SteveT on Sept 17, 2015 14:43:12 GMT
Sorry stevio, I should have shaded or locked column J to show it's a calculated field rather than part of the data extract. It just turns the text string that SS brings across as "Remaining" into a clean number that Excel can interpret in the pivot table. The formula you want in each cell of column J (starting in cell J2) is =SUBSTITUTE(G2," days","")+0 and then copy it down into all rows containing your loan parts. I'll update my instructions slightly to reflect this. |
|
stevio
Member of DD Central
Posts: 2,065
Likes: 894
|
Post by stevio on Sept 17, 2015 15:37:10 GMT
Sorry stevio, I should have shaded or locked column J to show it's a calculated field rather than part of the data extract. It just turns the text string that SS brings across as "Remaining" into a clean number that Excel can interpret in the pivot table. The formula you want in each cell of column J (starting in cell J2) is =SUBSTITUTE(G2," days","")+0 and then copy it down into all rows containing your loan parts. I'll update my instructions slightly to reflect this. Works a treat, thanks for the tweak and glad you understand these as I have no clue!
|
|
|
|
Post by robberbaron on Sept 17, 2015 17:42:23 GMT
_I use the attached spreadsheet which is based on the "Transactions" Excel Export. I find it quite useful to reconcile deposits, withdrawals and balance because Saving Stream occasionally get things wrong! To use the spreadsheet you just have to go to the Transactions page on the SS website and click on Export to Excel. Once opened copy paste the content into the "Transactions" tab of my spreadsheet, go back to the "Summary" tab and voilà! N.B. I only tested it with OpenOffice |
|
mikes1531
Member of DD Central
Posts: 6,452
Likes: 2,320
|
Post by mikes1531 on Sept 17, 2015 19:40:16 GMT
I use the attached spreadsheet which is based on the "Transactions" Excel Export. I find it quite useful to reconcile deposits, withdrawals and balance because Saving Stream occasionally get things wrong! Thanks for sharing this with us. It looks like it could be very useful.
|
|
dawn
Member of DD Central
Posts: 308 
Likes: 275
|
Post by dawn on Oct 10, 2015 11:08:38 GMT
Thank you Robberbaron,
I have only been with Saving Stream for about a month and developed a small Excel spreadsheet to track what I had been doing also using a copy of the Transactions report. However your summary sheet is so much more comprehensive and very useful. I have slightly adapted the table to include start and end dates for each loan so that I can easily see how long each of them has to run (assuming they don't get extended). This information needs to added manually but only once when a new loan is added to the list.
Thanks for sharing this useful spreadsheet.
|
|
webwiz
Posts: 1,133
Likes: 210
|
Post by webwiz on Oct 10, 2015 11:46:34 GMT
I use the attached spreadsheet which is based on the "Transactions" Excel Export. I find it quite useful to reconcile deposits, withdrawals and balance because Saving Stream occasionally get things wrong! To use the spreadsheet you just have to go to the Transactions page on the SS website and click on Export to Excel. Once opened copy paste the content into the "Transactions" tab of my spreadsheet, go back to the "Summary" tab and voilà! N.B. I only tested it with OpenOffice When I run it the top part of the summary is correct but a number of loans are missing from the loan list and so the totals are wrong. Any ideas?
|
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Oct 10, 2015 14:19:51 GMT
I added the missing (recent) loans in manually and then the numbers added up and matched those I had calculated elsewhere. There doesn't appear to be a way of currently downloading the complete list of live loans from the Saving Stream site, but new ones are not that frequent and can be added manually to the list as they arrive.
|
|
