|
|
Post by newlender on Mar 8, 2017 6:37:50 GMT
My monthly transactions spreadsheet for March is already at over 1300 rows. This is mostly capital and interest payments with a few £10 loans. I find this very hard to navigate and would like to see the capital and interest payments in two separate columns with the payments out in a third. Surely some techie at Zopa could make this spreadsheet simpler to see and navigate. Is there any Excel expert out there who can provide a quick way for me to do this manually with a formula or a search and replace function. I've had fun with the =(IF(x=y,z)...... function and its varaiants and have had a bit of success but don't know enough in order to use it properly. Basically, is there a function to re-order data without having to do it all manually?
|
|
aju
Member of DD Central
Posts: 3,484 
Likes: 917
|
Post by aju on Mar 8, 2017 9:12:37 GMT
My monthly transactions spreadsheet for March is already at over 1300 rows. This is mostly capital and interest payments with a few £10 loans. I find this very hard to navigate and would like to see the capital and interest payments in two separate columns with the payments out in a third. Surely some techie at Zopa could make this spreadsheet simpler to see and navigate. Is there any Excel expert out there who can provide a quick way for me to do this manually with a formula or a search and replace function. I've had fun with the =(IF(x=y,z)...... function and its varaiants and have had a bit of success but don't know enough in order to use it properly. Basically, is there a function to re-order data without having to do it all manually? Try this spreadsheet, it has macros in it if you want to use the automated parts but otherwise you can use the months to load your data and it will make a good attempt at working out the totals. If you don't want to automate it then just select tab February and right click in the left column A3 onwards say and use the "refresh"option. I've made it compatable with excel 2017 and above (xls) Ok it was way too big to upload so I pulled a single tab and removed all the macros to fit into site limitation just manually refresh the a3 using right click and load the downloaded statement file the sections at the top have the totals for each types that may be relevant hope this helps. This sheet is part of the whole year stats and there is a collating table to give other useful stuff but the whole thing is too big to upload here. My monthly data is over 3500 so it should work for you. The last two columns may be a bit temperamental but using excel double click copydown function should fix them. Attachments:example.xls (651.5 KB)
|
|
|
|
Post by dualinvestor on Mar 8, 2017 12:41:16 GMT
My monthly transactions spreadsheet for March is already at over 1300 rows. This is mostly capital and interest payments with a few £10 loans. I find this very hard to navigate and would like to see the capital and interest payments in two separate columns with the payments out in a third. Surely some techie at Zopa could make this spreadsheet simpler to see and navigate. Is there any Excel expert out there who can provide a quick way for me to do this manually with a formula or a search and replace function. I've had fun with the =(IF(x=y,z)...... function and its varaiants and have had a bit of success but don't know enough in order to use it properly. Basically, is there a function to re-order data without having to do it all manually? There is a standard formula to separate the columns if you are not familiar with macros or don't like importing files onto your computer =IF(ISNUMBER(SEARCH("interest",B2)),C2,0) That will isolate interest payments from the C column, in say F column. I am sure there are many better qualfied people on here who might be able to give you a simpler way of doing it
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Mar 8, 2017 15:34:37 GMT
My monthly transactions spreadsheet for March is already at over 1300 rows. This is mostly capital and interest payments with a few £10 loans. I find this very hard to navigate and would like to see the capital and interest payments in two separate columns with the payments out in a third. Surely some techie at Zopa could make this spreadsheet simpler to see and navigate. Is there any Excel expert out there who can provide a quick way for me to do this manually with a formula or a search and replace function. I've had fun with the =(IF(x=y,z)...... function and its varaiants and have had a bit of success but don't know enough in order to use it properly. Basically, is there a function to re-order data without having to do it all manually? There is a standard formula to separate the columns if you are not familiar with macros or don't like importing files onto your computer =IF(ISNUMBER(SEARCH("interest",B2)),C2,0) That will isolate interest payments from the C column, in say F column. I am sure there are many better qualfied people on here who might be able to give you a simpler way of doing it thats what I used in the example.xls, one can get way more complicated if you want but for me that worked well, I needed some additional columns for error checks when Zopa added different types of text etc than the obvious but that tends to be for early adopters like me where there is a bonus that was previous part of interest. The error checking fields allow me to pick up on any differences in the description field that means that none of the string checks end in an assignable value, which is an error
|
|
|
|
Post by newlender on Mar 8, 2017 19:21:53 GMT
The spreadsheet works a treat, many thanks for that. Just looking at the formulae, I can see that they look for a key word in the description and then put the appropriate figure (nicely rounded) into the column containing the formula. Can you explain the 0 at the end. What is the significance of that? Now all I have to do is steady my mouse hand in order to block 1300 rows before copy and pasting into the new spreadsheet.
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Mar 8, 2017 20:05:25 GMT
The spreadsheet works a treat, many thanks for that. Just looking at the formulae, I can see that they look for a key word in the description and then put the appropriate figure (nicely rounded) into the column containing the formula. Can you explain the 0 at the end. What is the significance of that? Now all I have to do is steady my mouse hand in order to block 1300 rows before copy and pasting into the new spreadsheet. glad it works for you . The 0 at the end is actually a count of any errors in the counters, its actually looking for rows that have not caught relevant data from paid in and paid out columns. Its looking at Capital Fees Interest Disbursal Bonus on a row basis and saying if all the above contain 0 then the search functions have missed an entry in the description field. The actual function is counting the FALSE states and subtracting the count from the error table. It was a simple way to deal with different sized data sets and count if there were errors. My tables were all set to 4000 records. I guess it was a bit of a kludge really but if it shows >0 then you can simply use the errors column filter to look for "missed entry" and then just use up/down to find real missed entries. =COUNTIF(L3:L1991,"Missed Entry")-COUNTIF(M3:M1991,"FALSE") In the table "FALSE" in state means there is nothing in column A for this row and "Missed entry in the Error column means nothing has been registered in Cols F to K The single tab is not that useful for me so I have one for each month of a financial year and I collect all this data together in an overview tab to see monthly data and then yearly data. It enables me to quickly see the return on a monthly basis and yearly basis. As I say the whole spreadsheet is too big to upload in its current form.
|
|
|
|
Post by newlender on Mar 9, 2017 6:14:30 GMT
Ah, I see. Now that I've got your sheet up and running I have a couple of other queries. I know that the macros are disabled, but how did you manage to upload the latest spreadsheet data from Zopa into your sheet. Presumably you have both loaded and then run a macro - is that hard to do? I see that column L is meant to tell you if there's data missing. I have an interest payment of .004p (how? but that's another story I suppose) and this is placed into the interest column as 0, presumably because it's an amount which can't be recognised as currency. But the error message doesn't show, so I assume that the 0 is read by the sheet as .004p and added to the final total at the top. My Zopa statement early this morning shows interest about 50p more than the new spreadsheet. I see that the Zopa web version was updated at 21.11 last night but the web spreadsheet at 20.52. So they won't always correlate, of course. Anyway, thanks for the Excel lesson - I love those functions and it's amazing what they can do. Now.....any tips on useful macros and how to write them (I mean in the context of Zopa)? I do use the Help in Excel quite a lot when I don't understand stuff but there seems to be a wealth of info. on this forum. Interestingly Ratesetter don't supply spreadsheets and you have to manually download a CSV file with the data you need.
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Mar 9, 2017 7:30:24 GMT
I don't think I did any rounding you can use excel to change the level of what you see in the fields. There is no rounding its all there if you operate it right - you can use the home menu - I think and use the decimal point mover button to change the view - it widens the columns sometimes. Right click will give you formatting options. You can of course use rounding functions if you so desire but I rarely do that. Most of the data in statements is at quite a low level when lending £10 units over a number of years.
The update button has a macro behind it that does some extra things but essentially I just recorded the manual loading process and then attached it to the button. - I never remember how to do these things I just fiddle a bit till I get what I want. (See below)
Manually re-loading data is simple, put your mouse cursor into col "A" at the first row of data (I think its A3 but any row where data is loaded should work) right click in that field and you should get a menu that has refresh probably the last entry (I only have excel 2007). Once the openfile window comes up just load any statement and it should load correctly. When you get used to it you, the right click on the column A field should allow you to change any of the data and import parameters you desire. (If you manually copied data into the table from another sheet then you may have trashed the refresh option - just go back to the original sheet. You can do this your self on any sheet by using the import text option.
If you mean how do you load more data on top of last months together then thats a bit more complex and i'm working on that still. (At the moment I have over 100 months of data to load to see all my defaults running back - I have over 33 of them that are way older than 5 years that I'd like to be able to see. I may eventually break open my old copy of oracle to just import the data easier - it runs to in the region of 300,000+ lines I think.).
As i said earlier I have a spread sheet that holds data for a year so i can look at all the data in summary but unlike the old zopa forum some of these are too big to load up to this forum. I'm happy to pass it on as a starting point for anyone but most of my stuff is very thrown together and then just managed as I need to. feels a bit like the way zopa is run at the moment but it means that they are quite complex and undocumented. Sadly i'm not an excel person by trade my background is in oracle/SQL in a very large company but that was over 10 years ago now and excel and its macros are very clever - I have created some quite complex business system using multiple tables and proper data design techniques but it does have a tendency like old school basic for sloppy quick fix approaches - I guess that's a benefit as well as a hindrance in my world at least - it is fun though and keeps my mind agile.
Glad my single sheet as helped though.
|
|
