duck
Member of DD Central
Posts: 2,864 
Likes: 6,899
Member is Online
|
Post by duck on Dec 22, 2013 8:33:25 GMT
Having downloaded my statement (csv) for the full period that I have been investing I note that a simple 'addition' of all the transactions gives a different total to that shown on my account dashboard - c£1700 less.
Whilst I suspect 'user stupidity' I would like to clear up this basic so that I don't introduce errors into the spreadsheet that I am building for my own use.
I haven't withdrawn any monies that I have added and I note the statement covers 'forward' deductions for bids.
What am I missing apart from the fact that building spreadsheets early on a Sunday morning is not a bright idea!
Note - When picking up some of Anglesey yesterday I used the slider and was allocated one less part than I put in (exact multiple of £100 shown) yet could pick up another single unit and another multiple of £100 so there were certainly funds available.
|
|
|
|
Post by bracknellboy on Dec 22, 2013 8:55:47 GMT
Interesting: 'cos I acquired one more than I intended. I think it may have been due to it giving me an error message on one of the transactions (see other comments on slider bar) but actually still bought a unit. Which I didn't spot when I made some later transactions, leaving me slightly over subscribed.
Maybe I got your missing one ? :-)
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,899
Member is Online
|
Post by duck on Dec 22, 2013 9:02:12 GMT
Yes I've read the other comments on the slider but even with my 'missing' unit that wouldn't account for the 'discrepancy' between the two figures. Still scratching my head thinking 'what am I missing'!
|
|
|
|
Post by mrclondon on Dec 22, 2013 12:47:08 GMT
In a true double-entry book keeping system the values on the transaction statement should sum to the holding account balance.
Have a look at the column C "status", if I filter out all rows with a status of "deleted" the remaining rows sum to £0.00 for me (which is my holding account balance at present). My guess is you have £1700 of deleted transactions on the statement.
"deleted" is the refund of bids on auctions pulled by AC either due to not filling, or subsequent failure to drawdown.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,899
Member is Online
|
Post by duck on Dec 22, 2013 16:44:29 GMT
Spot on mrclondon.
'User Stupidity' as I said in my initial post but now I can build my spreadsheet with confidence ............ Thanks.
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 22, 2013 17:21:20 GMT
"deleted" is the refund of bids on auctions pulled by AC either due to not filling, or subsequent failure to drawdown. Rather than classifying an entry as "deleted", which effectively says 'ignore this line', would it be better if the statement kept those lines as active and instead put an entry in at the time that money was released? Maybe something like "Loan to XX cancelled and committed funds returned"? Without that, it's impossible to build a record of the account balance over time. If you ignore the deleted lines, it will look as if there were more idle funds in the account than there really were. And if you include the deleted lines the idle funds balance is correct only until a loan was cancelled, but you don't know when it was cancelled so you can't make the appropriate adjustment to reclassify the money from being tied up to being available.
|
|
duck
Member of DD Central
Posts: 2,864
Likes: 6,899
Member is Online
|
Post by duck on Dec 22, 2013 18:02:59 GMT
I agree with you mikes1531, as the statement stands at present it is impossible to easily differentiate between idle funds and offered funds not subsequently drawn.
|
|
|
|
Post by jevans4949 on Dec 22, 2013 19:16:10 GMT
Couple of thoughts on improving the statement - Separate columns for nature of transaction and loan to which it relates
- Shorter title for loan, e.g., "Birmingham Bridging Loan" followed by announcement date.
- Does the lender's account detail need to be on every line of the CSV?
|
|
|
|
Post by batchoy on Dec 22, 2013 19:46:08 GMT
Couple of thoughts on improving the statement - Separate columns for nature of transaction and loan to which it relates
- Shorter title for loan, e.g., "Birmingham Bridging Loan" followed by announcement date.
- Does the lender's account detail need to be on every line of the CSV?
I find having the lenders account useful as it allows my accounting package to import the the data into the right place but it could be shortened down to just the reference. I agree on splitting the loan title and transaction and shortening loan name. Another suggestion would be to shorten the date down to just the date so that excel and accounts packages can handle it, if people need the time then it could be split into another column.
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 23, 2013 3:23:10 GMT
I downloaded the CSV statement and opened it in Excel with no problem, though I do agree that the 'narrative' data is rather excessive and would be better abbreviated in some way. Or, as someone suggested, add a new column specifying the nature of the transaction (Deposit, Withdrawal, Offer, Purchase, Sale, Principal, Interest). If I had that, I'd probably hide the narrative entry most of the time.
I also tried the XLS statement, which Excel opened OK, though not without complaining first that the file "is in a different format than specified by the file extension". I'm using Excel 2010.
Finally, with respect to the Date & Time (Column B)... This came in as text rather than something Excel could recognise as a date and time. I had to delete the time before Excel would treat it as a date. Also, deleting the date wasn't enough to allow Excel to recognise the remainder as a time. In order to achieve that, I also had to delete the decimal point and everything to the right of the decimal. I think the answer here is to divide Column B into two columns -- one for yyyy-mm-dd and the other for hh:mm:ss.
PS. I also notice that while the statement on my screen showed today's entries, the downloaded files did not. Does this mean I have to wait until tomorrow to download a statement that included today's transactions? If that's the way it's set up now, does it have to be? I would have thought that if the online statement is bang up to date the downloaded files could -- and should -- also be equally up to date and match what's displayed on the screen.
|
|
|
|
Post by chris on Dec 23, 2013 8:42:17 GMT
The nature of the statements is currently part of a large internal debate, particularly around deleted transactions, and we're currently seeking professional advice. At the moment the statement shows "forward" transactions - those that represent a promise to pay or reservation of funds rather than an actual movement of money. These transactions behave in the same way as a pre-authorisation against your debit card.
Forward transactions are used primarily for bids, but also in other places where we want the system to process transactions asynchronously for performance reasons. If a bid is rejected or knocked out of an auction then rather than having to reverse the transaction the reservation of funds is simply cancelled / the transaction flagged as deleted.
In my opinion the forward transactions shouldn't be on the statement at all, just as with your bank statement you don't see pre-authorisations, as they do not represent an actual movement of funds. The alternative would be to do away with forward transactions and actually create movements of money when a bid is placed and then the reverse transactions when that bid is rejected, which would all then show on the statement and give a complete record of all activity. This is a fair bit of work but is doable.
The problem with the second solution is that it complicates modifications of transaction amounts. Consider a reverse auction where an underwriter places a £1m bid which is then slowly removed by 50,000 £20 bids. This is an extreme example but in that situation the statement would become unwieldy and the volume of transactions would start to introduce performance problems (imagine trying to open a statement with hundreds of thousands of lines in Excel). Whilst reverse auctions are currently a rarity that doesn't mean this will always be the case, particularly as we continue to diversify the types of loans we offer.
This is why I prefer the solution of treating bids as a pre-authorisation, as is actually happening in the current code, and removing them from the statement. If memory serves that was how the site originally worked and it was an end user request for bids to be shown on the statement, something that I feel we should be over ruling. We are seeking professional advice as to the best approach from an accounting point of view, but I'm also open to discussion here.
|
|
|
|
Post by batchoy on Dec 23, 2013 9:28:49 GMT
In my opinion the forward transactions shouldn't be on the statement at all, just as with your bank statement you don't see pre-authorisations, as they do not represent an actual movement of funds. The alternative would be to do away with forward transactions and actually create movements of money when a bid is placed and then the reverse transactions when that bid is rejected, which would all then show on the statement and give a complete record of all activity. This is a fair bit of work but is doable. It's a difficult one, to some extent I agree with you if people are using it as a simple statement. However unlike a bank account once a bid has been placed the funds are committed and can't be used until either the bids are rejected/deleted, or the auction completes and they are paid back. Personally I use a full double entry accounting system with nominal ledgers so having the forward transactions in the statement allows me to track funds completely.
|
|
|
|
Post by chris on Dec 23, 2013 9:52:08 GMT
batchoy - preauthorisations on your debit card do work in the same way in that the funds are reserved and cannot be used for other purposes (or will send you overdrawn if you do use them). They're a guarantee that funds will be transferred further down the line should the transaction not be modified or cancelled - exactly the same concept as our forward transactions. Behind the scenes we have a double entry accounting system where every lender and borrower have one or more virtual bank accounts, and all movements of money debit and credit an account. The system balance always adds up to zero as does every individual transaction - both scenarios are tested in real time. However this concept is extended with forward transactions / pre-authorisations that have been borrowed from the banking world and my experiences building another P2P platform.
|
|
|
|
Post by jevans4949 on Dec 23, 2013 10:17:21 GMT
In connection with the previous few comments, it could be useful to have a couple of balancing entries to indicate when a bid becomes a loan part. A punter could then run 2 accounts - bids and loans.
|
|
|
|
Post by chris on Dec 23, 2013 13:08:27 GMT
Just been out to walk the dog and whilst exhausting myself it did occur to me that perhaps the solution is as follows: remove forward transactions from the main statement, use the settled date rather than created date as the timestamp on the statement, and then create a separate export for all outstanding forward transactions based on their created date.
Would that provide lenders with the information needed?
|
|
