Need advice for paying someone to analyse 6 years of my bank statements

[Onedaystronger]

As part of my divorce,(long story, for another thread) I need to analyse six years of my own current account bank statements. My excel skills are basic and I don't have much time having tried and failed to do it myself.

In a nutshell I need somebody to go through them and categorise my spending into sensible categories such as groceries, petrol, general household etc.. I guess there will be a number of transactions which would be really difficult for a stranger to categorise so would need to have input from me for these. I also feel this might be tricky for somebody not living in the UK because they will be much less familiar with the vendors that I have spent with and what category it might fall into EG, Morrisons bring groceries and B&Q being DIY.

I will be able to download this information from the bank myself into CSV and then convert that into Excel and clean up the data a little, but I think that is as far as I'm likely to be able to get.

I appreciate this is skilled job so needs a professional but I also need to bear in mind that my budget is limited.

Can anyone suggest anything? I've taken a look at the Fiver website but there doesn't seem to be anyone offering what I need , and many of the data people are based abroad so I think they might struggle to categorise spending much more than somebody living in the UK. I guess I could help with that but will slow things down ....

I had to do a similar thing recently and the thought of it was a bit overwhelming.

Once I started I was OK.

If you give yourself plenty of time and start with one year of transactions you will become familiar with how you can play around with the spreadsheet.

Also, you could probably ring the bank and they could talk you through any queries about how to get the info from online banking

Hi @Onedaystronger

A pivot table can be used to summarize a list in different ways.

For example if your list had the fields Year, Amount, Description and Category for each transaction, you could use a pivot table to put the years across the top, the categories down the left, and the amounts in the results area, and leave the Description out entirely.

Then it would show you in the middle the summarized total amount for each category in each year.

They are flexible, and easy to use.

It groups and counts entries together in the way you tell it to.

eg. If your transactions are Greggs £5, Greggs £10, Tesco £100, BP £50, Tesco ££20 it can output

Greggs 2 £15 “Eating out”
Tesco 2 £120 “Groceries”
BP 1 £50 “petrol”

The task sounds daunting because you're not familiar with excel, but it's actually pretty straightforward and something I do on a weekly basis to reconcile my bank account and spending.

Follow the instructions which have already been outlined step by step.

As others have mentioned, if I knew you I would do this for free as I love solving an excel issue. Think about your network and whether you have any friends who could support you with this, rather than paying someone. I wouldn't expect someone to charge you more than a couple of hundred pounds anyway, though someone might if they thought they could get away with it by making you think it was a really complicated job.

Good luck and I hope it all works out for you.

Anyone excel competent could take your csv give it a few headings -date -description amount (unique number probably also good)
Then create a filter and complete a few obvious items
Salary /grocery petrol /other.
In fact call everything other for now.
Create a pivot table for you
Then just show you which cells to amend to have things not as "other"
And refresh the pivot table.

It would take them 5 mins. Maybe 10 if I wanted to play with colours and include a checker/reconciliation too!

It would take you longer to work out what things are (cash withdrawals are often weirdly described) but the actual set up should be only a few minutes

Unique number...always good

(A lot of my old work conversations revolved around that)

Yes that's what an op suggested do can resort back at end -I would have done by date but date May show funny /re sort American style so unique number is belt /braces.

If you're not very good at excel then you should skip the pivot table.
I agree with a CVS converted to an excel then add filters.
Don't you know anyone who works in admin or even a teenager who could help or at least get you started.
I love teenagers when it comes to IT or mobile phone help.

Op I do this monthly as part of my budgeting anyway. I have one tab with the raw download from the bank (for all months) then i add 3 columns to that. The first is a formula in one column that is =EOMonth(cell with date of transaction in) which takes the date and works out the end month date. I then have broad category and second category column. I then have a second tab which has month ends across the top and spend categories across the side. Then with SUMiFS formulas it pulls through the spend for each category for each month end date. Does that make sense? The formulas are dead easy to do and MUCH easier than having dozens of tabs.

if I were you I’d do this but use a formal to to pull through year rather than month end so you’ve still got split by year rather than month like i do. And only use one set of categories because you’ve got enough of a headache attempting to do 6 years.

you then go back to the raw data tab and filter by blank on the category and then i filter in banks description for things I know I’ll have loads of like bus tickets , asda etc and then if you just keep refreshing so the ones you’ve done vanish each time you take the bus ticket (for example) filter off in description but leave the blank category filter.

The issue is really that often the transactions aren’t as informative as you’d hope. I spend a lot of money on Amazon which could be an essential house purchase, groceries, clothes or frivolous purchase. I have to log into my Amazon account and search for what it was, which is fine for 10 transactions a month but might take ages if you’ve got years worth of transactions like that. For this reason alone I wouldn’t pay someone else to do this for you because this is going to be the hard bit and only you can do it. Restaurants and non mainstream shops will also need a bit of your own personal knowledge.

I’ve tried the banks tools to do the same thing and honestly found them pretty crap. They don’t recognise a lot of common retailers and things like H&M turn up in food and any smaller retailer it’s got no idea so you have to check it’s so much anyway you may as well diy.

the other thing is my bank csv only goes back 6 months, are you sure yours does 6 years?

Also I would be really wary of asking someone else to do it for you. The data is SO personal do you really want a teenager or a stranger seeing your transactions in minute detail?

Definitely pivot tables not tabs. How many transactions do you think you have?

I do my back statement analysis each quarter and is mostly 30 recurring transactions-with one being "pay credit card"

Now I dig in deeper and analyse credit card too but you don't need to do it to provide your evidence.

What they will probably want (and I'd do it year by year if I were you) is a breakdown of where the income came from, ie earnings and transfers from savings, and what you have spent it on - totals for supermarket, fuel and vehicle expenses, household bills, holidays and so on. As a pp says, they want to find out whether you have been taking out money and squirrelling it away somewhere else in a savings account that only you know about.

Although how they could tell whether you have been taking out cashback from every supermarket shop for the last 6 years is anybody's guess.

Yes I'd also assume you need to provide both the original bank statements and this analysis so they can double check it sll agrees.

This is actually very easy to do. I do it for everyone who needs a tax return done, eg my brother. All your salary coming in, your mortgage going out. All other expenses can be sorted, filtered, most repeat, it doesn't take that long and isn't complicated. It takes me a few hours to do a year, over say 2 days.

It hardly matters how many transactions there are a single command sorts them all whether it's 10 or 10,000 (one years worth or six) so don't be daunted by the number of transactions.

If you aren’t great at excel I would do the following

1. download the files
2. copy and paste the column of vendor names to a new sheet
3. sort that column a-z and check the option that says remove duplicates. You will now have a list of unique vendor names
4. Still on that 2nd sheet, in the column next to the vendor names, assign your categories next to each vendor.
5. now go back to the first page and the first unused column. You are going to use a formula called a vLookup. Excel will guide you to fill out the formula, but you want to use it to get the categories into this column, so if the vendor names are in column b on this sheet, it might look like this =vlookup(b2,’sheet2’a:b,2,false). Where b2 has the vendor name on the current sheet. Sheet2a:b is the set of columns on the other sheet where made the categories. 2 tells it to return the 2nd column. False says find an exact match.
6. now you can sort by category and time instead of vendor and time .
7. at this point a pivot table to sum up your categories may be helpful, but it will be much simpler if everything is already rolled up a bit. Or you can just sort and do some manual calculating at this point if the pivot table gets too daunting.

So you're telling someone who's not confident at Excel to do a VLOOKUP and then a Pivot table?

No idea why my reply has been hidden but I suggested quick-books.

My banking app will analyse spending?