|
|
Post by lordgarner on Mar 3, 2015 8:36:24 GMT
Can anyone tell me how to easily find out how much monthly income I receive from my investments please. In other words how much repaid principal and how much interest and any miscellaneous income.
The reports that are available do not appear to provide this very fundamental statistic.
Thanks
|
|
JamesFrance
Member of DD Central
Port Grimaud 1974
Posts: 1,323 
Likes: 897
|
Post by JamesFrance on Mar 3, 2015 9:46:15 GMT
Yes, you need to navigate to Invest>My Investments>Cashflow and set the date range you want information for.
This shows expected and actual principal and interest payments.
|
|
|
|
Post by lordgarner on Mar 3, 2015 11:12:49 GMT
Hi James,
Unfortunately it doesn't as I found out this month.
It includes cash reserved for loans and the return of the cash if the loan isn't completed. February had an enormous amount of failed loans which drew my attention to how my "Income" had gone up ten fold!
If you look under the Account Statement report you will notice reserve funds going out and then later their return.
Hence it can massively overstate what I previously thought, like yourself, was the real income and only that which is a fundamental statistic that helps me decide if I wish to invest more funds .
I cannot understand why Bondora think that this form of Cashflow is of any use.
Rgds
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 3, 2015 17:29:16 GMT
I run a spreadsheet with a few simple 'formulas'* , download my account csv, dump that into the spreadsheet. That way I can separate out principal, interest, late payments, fees, countries etc
To me that is the most simple way of seeing exactly what I am earning on a continual basis and for example what effect splurges of +60 day loans is having.
*Example from my Excel sheet (=IF(H4159="Interest",F4159,0)) drags out interest payments and by having a column for what you want to separate out you can draw out the information that you require.
I actually use this spreadsheet for my tax return so the half hour it takes me each week isn't a major chore.
|
|
|
|
Post by lordgarner on Mar 3, 2015 22:20:36 GMT
Hi duck!,
Thanks for your response and I appreciate it is possible if you are able to manipulate csv files which I struggle with.
Surely Bondora should provide this basic information without the need to be an expert on such files.
If Bondora monitor this forum I would hope others may express their demands for the standard reports to include what I consider to be a fundamental measure of performance.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 4, 2015 5:20:11 GMT
lordgarner if you are having 'trouble' with the .csv files and want to take that route I can scribble down a quick 'how to do' when I have a few spare minutes .....
Do however agree with the point you are making, without my automated spreadsheet I would be manually looking through +5000 lines every month to extract the useful information!
|
|
|
|
Post by lordgarner on Mar 4, 2015 8:35:02 GMT
Hi duck,
I really would appreciate a "how to do" if you would be kind enough to provide it.
On the same lines of Bondora providing fundamental statistics, I was surprised to see the figure under my "Account Value" from the new presentation as it was much lower than previously thought. I had assumed that this was indeed the account value but it turns out that whilst it includes expected "Principal Repayments" it doesn't include expected "Interest Payments" so again is misleading.
The "true" value of the account is held under "Statistics" labelled "Portfolio Value"
I have raised these issues with Bondora but haven't received a response to date.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 4, 2015 17:56:00 GMT
OK this works with Excel 2007 and 2010 ........... and probably other versions
Download the .csv
Delete the rows containing text at the top, rarely any use!
Highlight Column A (select all)
From ‘Data’ tab select ‘Text to Columns’
Select ‘delimited’ button followed by ‘next’
Tick ‘Tab’ ‘Semicolon’ ‘Comma’ and ‘Space’
Hit ‘Next’ then ‘Finish’
Then everything will be in columns that can be manipulated.
With the Bondora ‘Account’ .csv having done the above you end up with a lot of blank lines*, so to get rid of these
Highlight the first column
Press Ctrl +G
Hit ‘Special’
Highlight the ‘Blanks’ button then OK
All the Blank cells in the first column will be highlighted
Right click on one of the blank cells in the first column
Click ‘delete’
Click 'Entire row' in the pop up that will appear
Click 'OK'
All the blank rows will be gone
*The blank lines only started appearing a couple of months ago after an 'update'
There are many other ways of ending up with the information in useable form so it is worth 'playing about' but my spreadsheet reads the data in in the form produced ..... and when you have done it a couple of times it only takes a minute to sort the .csv out. You can of course get Excel to read the .csv directly but I prefer to have the input data in easily readable form, makes error spotting easier!
|
|
|
|
Post by lordgarner on Mar 5, 2015 12:58:43 GMT
Hi duck,
Many thanks for the script!
I have followed it and it works!
Now all I have to do is to download the files by the month as it seems I can't download 12 months worth in one go, unless you have a way to do that as well?
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 5, 2015 17:17:13 GMT
Glad to be of help!
I haven't actually looked for a way of downloading a year but I suspect you won't be lucky. My suspicion is based on the shear mass of data that the larger investors would be asking for, I'm by no means a large investor (I hold just over 2000 loans) but I see 500 - 2500 lines per week, so multiply that by 52 and you can see why my accountants loved me when I submitted my spreadsheets last year 
I actually download the account, +60 day and late .csv's every week since it allows me to see a 'real' picture when I add in the current exchange rates.
|
|
|
|
Post by lordgarner on Mar 6, 2015 8:23:26 GMT
Hi again,
Is there a formula that can split out any income that isn't a whole number? It would appear that repaid principal and interest always has at least one digit after the decimal point whereas cancelled loans don't.
I hope that makes sense as trying to catch up on nearly 12 months backlog a month at a time having that formula would speed up the process no end. I want to determine all income except returned loans and am having to scan a very long list each month and delete these items. What then remind can simply be totalled by Epsilon sum.
Thanks again.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 6, 2015 18:08:03 GMT
You can do what you want to do in Excel, not very elegant ........... but it works
Assuming you have all the amounts in one column that would be the whole numbers (the loans not taken up/repaid) the interest, capital and late penalties.
1. Insert a column to the right of the column with the amounts in (if there isn't an empty one there already) 2. If the first amount is in say cell D1 insert into D2 =INT(D1)=D1 3. If the number in D1 is a whole number (integer) a value of 'True' will be returned (False if there is a decimal point followed by a number) 4. Copy the formula down for all the cells with values entered.
You will now have a long list containing True and False.
5. Highlight all the cells containing True and False. 6. From the Data tab in Excel hit the 'Filter' button, this will place the filter arrow in D2 7. Click the arrow, uncheck the 'select all' and check 'True' ............... you will only then display the whole numbers which if you want to you can delete all the lines in a single delete.
Obviously there are choices here, you can delete all the whole numbers or you can show the decimals only (check 'False') and copy them to another sheet (preserves the original data) but either way you will have separated out the numbers that you want to.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,898
|
Post by duck on Mar 7, 2015 7:35:58 GMT
Just logged onto Bondora to do my weekly transaction download and it is no longer available! (it was there this time last week)
I used to download the .csv from Account .....Transactions and now all that is available under Transactions is a balance sheet 
The 'Data Export (beta)' presents information in a totally different manner (for instance no borrower ID) so is totally unreadable by my spreadsheets .................and what's more you have to produce a separate 'download' for income/aftermarket activity so no simple 'whats happened' this week download.
lordgarner I've had a 'play' and I can't find a way to download more than 1 months transactions.
Oh how grumpy do I feel now!
EDIT I see I'm not the only one www.bondora.ee/forum/default.aspx?g=posts&t=7247
|
|
|
|
Post by xyon100 on Mar 7, 2015 12:01:11 GMT
I have to admit that all this spreadsheet business is a little beyond me. I'm not at all happy with the way Bondora presents the figures I need, nor am I happy with the way the automation works and manual investments become extremely time consuming, as does working the secondary market. I made a 2000 Euro investment just to get the feel of things, and I don't like the feel of things! I'll leave the money there but won't be micromanaging it or adding to my holding.
|
|
|
|
Post by lordgarner on Mar 7, 2015 17:57:00 GMT
Hi,
Again many thanks for the new script.
I will give it a go tomorrow as my eyes need a rest from scanning all the "income" figures deleting the obvious reserved and returned figures which are always larger and whole numbers.
The script will help no end!
I did look at the site last night and believed my eyes were failing as the "Account Statement" tab seemed to have disappeared but you have confirmed that it has!
Can't understand why Bondora is making it so hard to determine what I think are basic statistics by providing understated, overstated, misleading or useless (in my opinion) ones.
Under ACCOUNT, TRANSACTIONS, the Account Balance does give the stats that I want if they really do represent what they say i.e. Repaid Principal and Repaid Interest.... The pop up explanation does say it is these and no other but I am sceptical. Also a breakdown each month would be useful as otherwise you have to check the new figures on the same day each month and subtract the previous months figure.
Not helpful!
|
|
