|
|
Post by cyrilmadrid on Mar 11, 2017 16:42:14 GMT
Hello. Has anyone tried to build a view of their loan by loan position in GBBA ?
Difficult for example to check that interest paid is correct if you don't know how much you have per loan. Also would be interesting to check there are no over concentrations within my GBBA position
Has anyone tried to do that ? Technically possible by analysing the flow of operations.
|
|
jonah
Member of DD Central
Posts: 2,031
Likes: 1,113
|
Post by jonah on Mar 11, 2017 17:06:02 GMT
Hello. Has anyone tried to build a view of their loan by loan position in GBBA ? Difficult for example to check that interest paid is correct if you don't know how much you have per loan. Also would be interesting to check there are no over concentrations within my GBBA position Has anyone tried to do that ? Technically possible by analysing the flow of operations. yes. Periodically I've posted a top 10 holdings or similar. duck also has done the same. I've never done interest checks in bulk, but occasionally checked larger static holdings which are over a month old and not seen any issues.
|
|
|
|
Post by cyrilmadrid on Mar 11, 2017 17:07:21 GMT
Top 10 holdings you mean all investors in GBBA have the same portfolio ?
Did you get the Top 10 holdings by analysing the flow of operations in your account and constructing the total position from there ?
|
|
|
|
Post by cyrilmadrid on Mar 11, 2017 17:10:30 GMT
Ah I found the top 5 holdings you referred to and seen you and duck do not have the same portfolio. So this answers my last question, not all investors would have a share of the same book. How did you produce this top 5 if I may ? |
|
duck
Member of DD Central
Posts: 2,571 
Likes: 5,658
|
Post by duck on Mar 12, 2017 6:58:17 GMT
If you like a spreadsheet keeping track of the loans that your GBBA holds is not difficult, you can extract it from the downloadable transactions.
That way I know that for instance my wife's account holds
£6.94 of loan 251
£101.36 of loan 278
The largest holding is in loan 168 which represents 14.031% of total investment.
The second largest holding is 7.594% in loan 413
£122.19 is held in currently 'distressed' loans.
My spreadsheet is simple to update (copy and paste the new transactions) and only needs a new column as new loans are added. I use conditional formatting to colour the cell red when the value of a loan drops to zero which usually signifies a repayment. When loans have repaid I move the data to a storage area on the sheet to keep the data that I like to view as condensed as possible.
|
|
|
|
Post by cyrilmadrid on Mar 12, 2017 7:27:17 GMT
duck But do you do that manually or have you automated it ? Because I have 26 pages of 1 GBP transactions, doing it manually would be a nightmare.
|
|
duck
Member of DD Central
Posts: 2,571
Likes: 5,658
|
Post by duck on Mar 12, 2017 8:12:26 GMT
It's what I class as 'semi automated'
I copy the transactions onto the left hand side of the spreadsheet and to the right hand side I have a long series of formulas
For example
=IF(ISNUMBER(SEARCH("Interest",D7759)), E7759,0)
&
=IF(ISNUMBER(SEARCH("Company XYZ",D7759)),(E7759-I7759),0)
These pick up the data from the pasted transactions and all I have to do is copy the formulas for the new lines ........ an update takes 20 seconds max after downloading the info from the AC site.
The results of all the formulas are shown in tables on another sheet so I don't have to scroll through 10K lines.
There are many ways to do this but I have chosen the 'SEARCH' function because I can use it across all the platforms that I invest through.
|
|
|
|
Post by GSV3MIaC on Mar 12, 2017 8:23:17 GMT
/mod hat on duck .. I hope that carpet company is not a real borrower .. if it is,you need to obscure it! |
|
|
|
Post by cyrilmadrid on Mar 12, 2017 10:16:42 GMT
It's what I class as 'semi automated' I copy the transactions onto the left hand side of the spreadsheet and to the right hand side I have a long series of formulas For example =IF(ISNUMBER(SEARCH("Interest",D7759)), E7759,0) & =IF(ISNUMBER(SEARCH("Company XYZ",D7759)),(E7759-I7759),0) These pick up the data from the pasted transactions and all I have to do is copy the formulas for the new lines ........ an update takes 20 seconds max after downloading the info from the AC site. The results of all the formulas are shown in tables on another sheet so I don't have to scroll through 10K lines. There are many ways to do this but I have chosen the 'SEARCH' function because I can use it across all the platforms that I invest through. Thanks, I think a Pivot table should be able to do it. Will try, otherwise will look to replicate your formula. Thanks for sharing.
|
|
|
|
Post by cyrilmadrid on Mar 12, 2017 10:53:16 GMT
Got my top 5 as follow with a pivot table (extracted the loan number with a RIGHT() on the Description field):
413 13,24%
336 12,65%
227 11,29%
388 6,26%
349 5,47%
55 loans but 5 account for 50 %.
|
|
duck
Member of DD Central
Posts: 2,571
Likes: 5,658
|
Post by duck on Mar 12, 2017 11:20:20 GMT
Got my top 5 as follow with a pivot table (extracted the loan number with a RIGHT() on the Description field): 413 13,24% 336 12,65% 227 11,29% 388 6,26% 349 5,47% 55 loans but 5 account for 50 %. Glad to see you are sorted, I like pivot tables and would have used one if I could have taken a common route over the platforms I invest in. (less for my accountants to check and charge for!) What you have found sound about right, my wife has a lot of loans that picked up about the 26th decimal point of 1p which religiously return zero interest every month along with a suitable transaction. So you end up with a long list of loans for which your total investment is 5p  13.24% isn't too bad but when you have a lot concentrated in one loan and then it repays it takes ages to get back up to 100% invested, something to keep an eye on.
|
|
|
|
Post by cyrilmadrid on Mar 12, 2017 11:25:51 GMT
Yes, and these stats let me to redeem in the manual loan program 2 loans in the top 3 of GBBA.
And not much else you can do to reduce concentration since you cannot sell specific loan from your GBBA I understand.
|
|
jonah
Member of DD Central
Posts: 2,031
Likes: 1,113
|
Post by jonah on Mar 12, 2017 12:03:34 GMT
And not much else you can do to reduce concentration since you cannot sell specific loan from your GBBA I understand. That is correct. My current top 5 are: 413 - 19.47% 327 - 10.83% 336 - 8.83% 388 - 6.18% 295 - 6.17% 413 was a flat 20% after it drew down. The capital repayment from that loan and reinvested interest has move it down to its current value. Current 95 active loans, although 15 of them have less than 10p in each! I'm currently 99.7% invested and the remaining 0.3% is in the QAA, so from that perspective, I have nothing to complain about.
|
|
|
|
Post by cyrilmadrid on Mar 12, 2017 15:36:16 GMT
Performed the same check on Green Energy, and got better results than expected (having few loans, I thought concentration would be bigger, but I guess they must be enforcing the 20 % cap you seem to suggest for GBBA):
357 16,56%
359 16,48%
363 16,48%
356 16,02%
426 8,89%
|
|
