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 ....

If you download into a csv, have you tried sorting by supplier name instead of date? Then all the 'New Look' transactions are together to put them in a 'clothing' column. All shell ones are together to put in a 'petrol' column etc. Should save lots of time. Just sort by date again when complete

With your CSV file, have column A as the date, column B as the description and column C as the amount. Under Column D head it "type", then go through each line and enter eg shopping= 1, gas bill = 2, phone bill = 3 etc. Then head the next columns E-G gas bill/shopping/phone bill.

You can then put an IF formula to say "if column D = 1, show the amount in column C" and drag down.

Once you've been through all the lines you'll have a total for the gas bill, shopping total etc.

Or ask a local accountant, shouldn't take too long.

Have you tried Upwork? That's the freelancing site I mostly use (as a freelancer)

(My excel skills are advanced but I probably charge more than you are looking for 😊)

Thank you all for your advice. I'll give it a go. Someone has suggested that AI might help too so that's another option.

You haven't said about the specific background to this, but I'd question the usefulness of analysing your transactions over such a long period because of the increase in price of things like groceries and utilities especially, also the increase in interest rates if you have a mortgage. What you spent on these things 6 years ago will be a lot less than it is today.

Plus there could have been changes in your spending patterns, eg if you've moved house, changed jobs so you use more/less fuel and of course the effect of COVID if you've WFH so lower travel expenses but more heating for example.

But if you can download the transactions into a spreadsheet, you can then sort and split by category.

What exactly do you need to achieve from the analysis? Did you use a card for enough of your spending to have the information, or was there more than minimal spending using cash?

DH has an app, the name escapes me, but it does exactly what you want, you link it to your online banking and it tells him each day / week / month how we spent our money. It knows a lot of vendors but you have to tell it what some things are.

Not sure how far back it would go, but it might be worth searching on the App Store for various options?

Don't pay someone for this op. Get yourself on YouTube for what you need and teach yourself a skill at the same time.

If you can download and convert a csv you can do some basic data sorting, formulas and analysis.

My DH got Chat GPT to do this for us when we were looking to see what we were spending when we were thinking of moving. I’m not sure how he did that exactly though.

Are you on any local Facebook groups. Our local ones are full of students looking for work and I know maybe someone doing A level business studies etc would be able to sort this out for you. I doubt it would take them long and wouldn’t cost very much.

Honestly OP don't waste your money, you can do this yourself with confidence. Just learn the filter button on excel and filter per supplier, group the supplier to the category and that is far easier! If I knew you, I'd do this for free!

@Bjorkdidit I understand your point. There was negligible cash spending or withdrawal. I have to do this despite it not being useful for the reasons you've mentioned because I had a decent amount of savings before this time which were spent via my current account and whether I like it or not I've been ordered by the court to answer this question from EXh. I've nothing to hide, done nothing wrong, juts need to show the information required and in finding it extremely difficult as it's a huge task....

@gojumpjump I'd probably do this for free too. I love a good Excel financial project.

But OP you don't even need to use the filter function - you could also just sort the merchants alphabetically then cut and paste different ones onto different tabs for groceries, petrol etc? Then have a master page that lists all the categories and amounts.

How accurate do you need to be? Hopefully it won't matter if one week you bought a book when you were grocery shopping but don't account for this, for example?

You can certainly get an AI tool to do this for you - in fact some of the newer challenger banks do this anyway for you.

Have you got newer version of Microsoft? CoPilot should be able to do this directly onto the csv files.
I found this article on LinkedIn with some other suggestions - a bit US centric but these may be able to do the job.

If you really don't want to use these then I'd do some simple Excel stuff. So for example, sort the spreadsheet by merchant and find the big blocks of spend e.g supermarkets and just add a column and categorise them all as food, utilities also equally easy to find and so you can categorise them too. Do that until you've done the major spend items, food, utility, rent/mortgage, petrol etc, then categorise everything else as "other".

Then do a pivot table and you can get the pivot to show you by month, year etc and if you then find it isn't granular enough go back and recategorise some of the "others".

Edit to add link Leveraging AI to Optimize Household Spending: Analyzing Shopping Habits for Financial Wellness
www.linkedin.com/pulse/leveraging-ai-optimize-household-spending-analyzing-shopping-auwerx-iqyse?utm_source=share&utm_medium=member_android&utm_campaign=share_via

Ah, so I'm guessing it's something like you have to demonstrate that your savings have gone on joint household essentials rather than your own personal luxuries or you've squirreled them away somewhere else that you're not disclosing?

Here's how I would do it (I've never used AI)

Get yourself an empty spreadsheet (use Google sheets or the Apple equivalent if you don't have Excel) and add a load more tabs. You can add more later if you need them. Start to label these as groceries, petrol, council tax etc. You can add more tabs or change the names later.

download all transactions into a 'spare' tab - you might have to do this year by year and it might be easier to deal with this way anyway.

Sort by merchant and then cut and paste into the relevant tabs. Keep going until you've done this for all the transactions.

Check you're happy with the categorisation, depending on how accurate you need to be, do you need to make any adjustments, eg if you bought clothes from the supermarket etc.

Once you have all the transactions in all the different tabs, you need to sum these. To do this click in the cell below the list and type the sum formula: =sum(A1:A10) (put the right cell refs to sum all the amounts, not A1 and A10 as in my example).

You then have the amounts for each category and you can then do a summary page which just lists the total amount for each category. You can use a formula to automatically populate these, or you can just write in the number.

Once you get going with it, I wouldn't have thought it would take more than an hour or two, if that?

@Bjorkdidit something like that yes. I think believe it or not that what I've spent it on is less important than showing I've not squirrelled it away as cash or given it to someone else. I have spent responsibly anyway, so I'm not concerned but showing it is mind boggling given that all the savings were spent via my current account into which all my other income also was paid as I'm self employed and there's no requirement form me to have a separate business bank account (I have one now though but back then I didn't expect to need to be able to have to show all this).

Thanks for your advise. I'll give it a go. There's an awful lot of transactions as it goes back 6 years!

Thanks again

To answer a question- it doesn't need pinpoint accurate in terms of buying a book from a supermarket, or a point of milk from a garage.....

The other thing to remember is that if there have been no large cash withdrawals or bank transfers to other accounts, then I can't see how you could have hidden it in another account or given money away?

Making the analysis somewhat unnecessary. Could you just send them the bank statements and let them have a look for what isn't there?

To do this you will need to know how to put a filter on the Excel spreadsheet and how to use the sum function to add things up. You will also need to tidy up the data a bit, make columns wider etc

When you have downloaded the first year of transactions into the Excel spreadsheet put the filter on the top line. You can then opt to filter out certain transactions like, for example Tescos shopping. Then use the sum function to to add it up.

If you highlight the transactions that you have filtered in different colours you can make sure that you have covered every transaction that you need to

Hopefully that helps

I was going to suggest a Pivot Table, but @Andwegoroundagain beat me to it.

If I had to do this I would download as a CSV. Sort out the column widths and save as an excel file.

Add filters. Add a column at the end and give each transaction a unique number (means you can return everything to its original order after sorting it if you need to). Add another column on the end called "category". Sort on the vendor column and go through and manually list the transaction categories. *

(This should have a star but the formatting won't behave) * there's an option here which uses a pivot table which would be easier overall but requires a bit more excel knowledge. If I were doing this myself I would select the whole table, go to the "insert" tab then "pivot table" and add a pivot table. In "rows" you'll want vendor and in value you'll want the spend amount.

Then you can categorise each vendor on there, copy and paste the pivot table as values and re-pivot it.

I reckon this would take me about 30 mins or max an hour in front of the telly and I would be happy to do it to help a non-techy friend.

I have a simple guide to pivot tables somewhere that I can dig out if you want.

@Ineffable23 thank you so much, if the guide is handy that would help. It's one of those things that I guess is easy if you know how but I'll give it a shot and maybe learn something in the process. I'd been assuming it will take me a very very long time and I really hope I'm wrong!

Thank you. Really grateful for all the suggestions that I've had and I'm going to try to give it a go myself.

In hindsight this did occur to me as an alternative. Unfortunately it's too late to change the court order which details exactly which questions both of us have to answer and this question is worded in a way which means doing it the way you've suggested won't answer it as needed- albeit a really sensible approach.

I wish I'd considered it before.

The whole process is so brutal and I could fill another thread about that- but have decided to just get on with it as I've absolutely nothing to hide.

I really appreciate your thoughtful advice and I think I'll provide a separate sheet detailing any cash withdrawals and transfers to other people (which I'm sure will be negligible but we are going back 6 years so I will check).

Pivot table- can anyone briefly explain how this can help please? I think that if I can understand its function that will help me to work out how to use it?