webwiz
Posts: 1,133
Likes: 210
|
Post by webwiz on Oct 10, 2015 16:01:27 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. That's not my problem. I used robberbaron's spreadsheet and all the loans are showing in the transactions tab and the total of all loans is correct in the upper part of the summary tab, but in the bottom part a number of loans, mostly but not all recent, are missing and so the total at the top of the column does not equal the total in the upper part.
|
|
dawn
Member of DD Central
Posts: 308 
Likes: 275
|
Post by dawn on Oct 10, 2015 16:42:34 GMT
Sorry - I didn't explain it properly. I added the loans to the list at the bottom of the summary sheet - the bit where your numbers are not adding up. I had the same problem when I first imported my transactions into the transactions tab - I have money lent to loans that weren't in the list on the summary tab so needed to add them in. I've attached my latest version of RobberBarons spreadsheet which now contains all current live loans - I have moved some of the parts at the top of the summary sheet and added columns for start date, end date, days left and % lent to each loan. I also have colours grouping loans I think are from the same company and in the total lent column to highlight how much I have lent to each loan. I've left one day of my transactions in the transactions tab so you can see that they all add up - if you paste in your transactions statement hopefully your numbers will add up too
Link removed
|
|
webwiz
Posts: 1,133
Likes: 210
|
Post by webwiz on Oct 10, 2015 18:35:09 GMT
Thanks for trying to help dawn, but my old version of excel won't open your sheet. Perhaps robberbaron will comment? The loans missing from the summary tab are there in the transactions tab.
|
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Oct 10, 2015 20:20:51 GMT
Sorry Webwiz - I didn't think about that. Here is a 97-2003 format (ie a .xls extension). Hopefully that will work instead.
6/11 - Link removed as requested - see later post with new spreadsheet minus names
The list of loans on the summary sheet isn't created from the transactions sheet as far as I can see. I'll have a look and see if that can be done (easily).
|
|
webwiz
Posts: 1,133
Likes: 210
|
Post by webwiz on Oct 10, 2015 21:25:37 GMT
Thanks dawn, I can open that one. I will have a play with it but my excel skills are very poor. I must find a free course.
|
|
|
|
Post by Deleted on Oct 11, 2015 15:54:47 GMT
Sorry - I didn't explain it properly. I added the loans to the list at the bottom of the summary sheet - the bit where your numbers are not adding up. I had the same problem when I first imported my transactions into the transactions tab - I have money lent to loans that weren't in the list on the summary tab so needed to add them in. I've attached my latest version of RobberBarons spreadsheet which now contains all current live loans - I have moved some of the parts at the top of the summary sheet and added columns for start date, end date, days left and % lent to each loan. I also have colours grouping loans I think are from the same company and in the total lent column to highlight how much I have lent to each loan. I've left one day of my transactions in the transactions tab so you can see that they all add up - if you paste in your transactions statement hopefully your numbers will add up too Dawn, thank you and robberbaron for the effort. This is an excellent tool. a) Why are the numbers negative ? (they sum up correctly but it looks confusing to have all negatives in) b) can you describe a bit better the color coding? In the net invested and Spit columns I have cells in red, amber, yellow and green. Are they just a percentage size indicator?
|
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Oct 11, 2015 17:31:47 GMT
The numbers were negative in RobberBarons original spreadsheet - as they are on the Transactions report that it is based on. I take negative numbers to be money that has gone out of my account (ie I don't have it at the moment) and positive numbers to be money that has come back in (interest, repayments (not had any yet), etc). This fits with the transaction report. It would be possible to multiple everything by -1 but then interest would appear as a negative number which is counter-intuitive to me. Regarding the colours I used the colour scales option of the conditional formatting tool in Excel. I chose it so that green means no money lent (or very little) through to red being the most lent. Yellow and amber are intermediate amounts. So Excel takes the lowest amount and sets it to green and highest amount and sets it to red and scales all the other figures in between. This means when I look at the spreadsheet I can see at glance which loans I'm not lending to (or only lending a very small amount), which I might want to lend a bit more too and which I have already lent a lot to - just a quick way of highlighting which loans to look out for (provided the 'time left' column to the right of the 'split' column isn't negative or very small  ). The colour scales in the splits column work the same way. Does that help? |
|
|
|
Post by robberbaron on Oct 12, 2015 18:08:49 GMT
I probably should have explained that the list of loan was static and therefore had to be updated manually. There is a way to remove duplicates in Excel via functions but not in OpenOffice to my knowledge where I have to use filters instead. That's why it is static.
Thanks dawn for updating the spreadsheet.
|
|
treeman
Member of DD Central
Posts: 1,026 
Likes: 557
|
Post by treeman on Nov 1, 2015 19:35:24 GMT
AAARGGHH!! If anyone is using robberbaron's spreadsheet or similar (as mine with a static PBL list) you'll find some weirdness when the new transaction/statement sheet is pasted in...... Some of the Loan Part Details descriptions have changed meaning the spreadsheet can't find what it's looking for and do the sums properly. For example PBL39 - ******, C*****t Lane, Gloucestershire is now PBL39 - ******, C*****t Lane, Gloucs I found 5/6 of the loans I'm in are slightly different-there may be more. Took me a while to figure it out - thanks SS - looking for all sorts of Gremlins.... My head hurts !  |
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Nov 2, 2015 0:29:55 GMT
Updated spreadsheet with new names of loans - either copy and paste your transactions into this one or copy the summary sheet into your existing one (or just rows 10 onwards from the summary sheet) |
|
jonbvn
Member of DD Central
Posts: 326
Likes: 95
|
Post by jonbvn on Nov 2, 2015 8:59:16 GMT
Thank you stevet for an excellent spreadsheet - just what I was looking for. TBH, this is something that should be available on the SS website - but not holding my breath  |
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Nov 5, 2015 14:51:06 GMT
Updated spreadsheet with new names of loans - either copy and paste your transactions into this one or copy the summary sheet into your existing one (or just rows 10 onwards from the summary sheet) Saving Stream have changed the Loan part details again - making all the PBL PBLxxx - which is fine - I can fix that. However they have also added the loan amount into the name of the Loan part detail. I am trying to find a way round that (as different parts for the same loan may have different amounts) and will post an updated spreadsheet when I've managed to find a way.
|
|
dawn
Member of DD Central
Posts: 308
Likes: 275
|
Post by dawn on Nov 5, 2015 15:34:01 GMT
Updated spreadsheet with new names of loans - either copy and paste your transactions into this one or copy the summary sheet into your existing one (or just rows 10 onwards from the summary sheet) Saving Stream have changed the Loan part details again - making all the PBL PBLxxx - which is fine - I can fix that. However they have also added the loan amount into the name of the Loan part detail. I am trying to find a way round that (as different parts for the same loan may have different amounts) and will post an updated spreadsheet when I've managed to find a way. Updated spreadsheet - now contains PBL numbers with 3 digits afterwards so matches new Transaction statements. I have also managed to change the summary sheet so it only looks for the PBL number not the full name (actually looks for the first 7 characters from the name of the loan to allow for 4a and 4b - also works for Super Yacht by looking for "2005 Su"). Therefore it doesn't matter that they've added the loan amount into the Loan name. Link removed
|
|
|
|
Post by Financial Thing on Nov 5, 2015 16:52:16 GMT
SteveT I tried your Sheet on my Mac, copy and paste breaks the pivot table sheet. Darn Mac.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Nov 5, 2015 16:55:25 GMT
SteveT I tried your Sheet on my Mac, copy and paste breaks the pivot table sheet. Darn Mac. That's odd. My only venture into the Mac world is my iPhone so I can't help you really. Did you "Paste Special - Text" rather than simply "Paste"?
|
|
). The colour scales in the splits column work the same way.
