SteveT
Member of DD Central
Posts: 6,875 
Likes: 7,924
|
Post by SteveT on Nov 19, 2015 9:23:15 GMT
An updated Excel pivot table proforma is attached, to help anyone wanting to track their SS loan holdings offline. It now uses the transactions "Export to Excel" download from the new website as its source data (NB. all borrower descriptions have been removed, loanpart IDs blanked out and dummy values used) Saving Stream pivot proforma.xlsx (115.46 KB) Instructions:1) Download a copy of the proforma file and save wherever you want to keep it. Open it up in Excel. 2) Download the "Export to Excel" transactions file from your SS account. Open the file, highlight from cell A3 to cell I??? and Copy (ie. copy all of your dated transactions data, but exclude the top 2 rows and the very last one) 3) Go the proforma file, click on the yellow-shaded cell (A2) in the "Transactions Extract" sheet and then Paste Special - Values to paste in your copied transactions data NB. The first time you do this, delete any remaining dummy transaction rows that still are visible below your own transactions! (the dummy transactions run to row 810; you may have fewer than this) 4) Then go to the "Loans Summary" sheet, right-click anywhere on the pivot table and Refresh (to update the loans table with your data). It will automatically add any new loans as you buy into them. I use column D to keep track of SS comments for the loans I hold, but just delete it if you don't want it. 5) The final "Transactions Summary" sheet gives a high-level cashflow summary of your account. Again, right-click anywhere on the pivot table and Refresh to pull in your data from the "Transactions Extract" sheet. Optional:You can change the pivot table to filter out your zero-value loan holdings (ie. repaid and sold loans) if you prefer not to see them: - on the "Loans Summary" sheet, click on the little drop-down filter button in cell A6 (right-hand side of the cell) - click on Value Filters - Greater Than...- type 0.001 in the box on the right and click OK |
|
stevio
Member of DD Central
Posts: 2,065
Likes: 894
|
Post by stevio on Nov 19, 2015 11:48:32 GMT
Thanks!
I have currently just cut and paste from the live loans page, which I kinda like as it amalgamates all the loans parts for each loan so you don't need to copy and paste reams and it is fairly easy to update the totals as you buy and sell or just copy and paste again
Would this new spreadsheet have any advantages over this?
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Nov 19, 2015 13:06:46 GMT
Thanks! I have currently just cut and paste from the live loans page, which I kinda like as it amalgamates all the loans parts for each loan so you don't need to copy and paste reams and it is fairly easy to update the totals as you buy and sell or just copy and paste again Would this new spreadsheet have any advantages over this? Well, I guess it gives you the cashflow summary of your transactions too but it depends what you're after. Takes no time to hit the "Export to Excel" button and paste your transactions over (click cell A3 at the top, then shift-click cell I??? at the bottom and it highlights the whole lot instantly)
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Nov 19, 2015 15:43:59 GMT
You can change the pivot table to filter out your zero-value loan holdings (ie. repaid and sold loans) if you prefer not to see them:
- on the "Loans Summary" sheet, click on the little drop-down filter button in cell A6 (right-hand side of the cell)
- click on Value Filters - Greater Than...
- type 0.001 in the box on the right and click OK
|
|
registerme
Member of DD Central
Posts: 6,624
Likes: 6,437
|
Post by registerme on Dec 2, 2015 17:01:48 GMT
SteveT, the sheet no longer works for me. I'm thinking that SS may have changed the export file? Or I'm being dumb. Very possible that  .
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 2, 2015 18:06:12 GMT
SteveT, the sheet no longer works for me. I'm thinking that SS may have changed the export file? Or I'm being dumb. Very possible that . I've not spotted any change and my copy is still working fine. Did you make sure to paste the data across as just values, so that the pivot table can cope with it? Ps. Or did you export their new Loan Parts download instead of the Transactions download?
|
|
|
|
Post by uncletone on Dec 2, 2015 18:09:29 GMT
Failure could be caused by Saving Stream's changing the loan descriptions for the download. Note that the downloaded descriptions are now devoid of commas.  (Yes, I know it's a comma separated values file) |
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 2, 2015 18:16:50 GMT
Opening the CSV download file with Excel parses the data neatly into individual cells so I don't think that's it. I've just tried a fresh download (in case something changed this afternoon) and it still works fine for me.
|
|
registerme
Member of DD Central
Posts: 6,624
Likes: 6,437
|
Post by registerme on Dec 2, 2015 18:48:43 GMT
Ps. Or did you export their new Loan Parts download instead of the Transactions download? That was it. +1 to my being dumb count. Sorry 'bout that  .
|
|
gt94sss2
Member of DD Central
Posts: 281
Likes: 137
|
Post by gt94sss2 on Dec 10, 2015 17:19:23 GMT
SteveTMany thanks for this - I have just downloaded this and given it a go. It all seems to work fine though on the loans summary sheet I have found all the values against the loans showing as negative figures (not sure why?) The one enhancement which I would like to see (if possible - I'm not good with excel) is somewhere to put in the anticipated repayment date of each loan and being able to view that in a more friendly manner (graph or by repayment date) - though I appreciate the transactions data doesn't provide that.
|
|
|
|
Post by ladywhitenap on Dec 10, 2015 21:41:04 GMT
I've just started playing with this Stevet
I've downloaded my transaction data into the txns sheet, done the paste special-values and deleted the extra cells - all looks fine
However when I look at the other sheets and right click either pivot table, the Refresh Data option is greyed out - what am I doing wrong please?
I'm not that familiar with pivot tables so it is possibly me.....
TIA
LW
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 10, 2015 21:50:02 GMT
I've just started playing with this Stevet I've downloaded my transaction data into the txns sheet, done the paste special-values and deleted the extra cells - all looks fine However when I look at the other sheets and right click either pivot table, the Refresh Data option is greyed out - what am I doing wrong please? I'm not that familiar with pivot tables so it is possibly me..... TIA LW Hmmm, not sure what's going on there, unless somehow the link between the data and pivot table has been lost (eg. if one of the data columns had been deleted). I've not come across it before and, aside from trying again from scratch, am unsure what to suggest. When you deleted the "extra cells", I assume you selected and deleted the whole rows (although it ought to work even if you just deleted the cells containing dummy data).
|
|
|
|
Post by ladywhitenap on Dec 10, 2015 21:59:17 GMT
Thanks for the reply SteveT.
Since writing my previous post, I started afresh and just downloaded your sheet again and without changing anything, ie just using your dummy data, the refresh option is still greyed out.
One clue might be that I had to pass the file through the official microsloth converter to get into .xls which my version of Excel needs. The process did not throw up any errors though.
LW
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 10, 2015 22:19:56 GMT
Thanks for the reply SteveT. Since writing my previous post, I started afresh and just downloaded your sheet again and without changing anything, ie just using your dummy data, the refresh option is still greyed out. One clue might be that I had to pass the file through the official microsloth converter to get into .xls which my version of Excel needs. The process did not throw up any errors though. LW Try this version, which I've saved directly as .xls (the Excel conversion came up with a few formatting warnings but the pivots still seem to be operating OK): [Now moved to the OP at the top of this thread]
|
|
|
|
Post by ladywhitenap on Dec 10, 2015 22:37:59 GMT
Hi SteveT,
Thanks for the revised file. The .xls version behaves in the same way. I will have to investigate my excel set up - looks like it is blocking pivot tables somehow.
I've checked that macros are enabled if that might be relevant..
Your help is much appreciated
LW
|
|
.
.