Excel formula for spend tracking

movilogo

Just search on web what is Pivot Table. There are plenty of tutorials on YouTube and elsewhere.

Exodi

movilogo said:

Add expenses as row like this
date, food, 21.43
date, fuel, 25.00
use actual date instead of "date" above.

Then use Pivot Table to analyze data. Pivot Table parses dates automatically by month, quarter, year etc.
Then use date as row and category as column with values as sum of expenses.

This will achieve what you want. 

Completely agree with this and would suggest the same.

Exodi

B0bbyEwing said:

I think maybe part of the issue is being told something rather than seeing something. Such as you're telling me what will work and I trust it will but I don't really know what you're saying. Pivot table? Never heard of them, wouldn't know what they were if they slapped me in the face. But I could see something in action and get it if you get me?

It can sound and look complicated, but it's actually very intuitive and useful.

Let's say you set up a tab that showed:

01/05/2022 Supermarket 10.00
05/05/2022 Takeaway 11.00
28/05/2022 Supermarket 5.00
02/06/2022 Supermarket 20.00
02/07/2022 Takeaway 12.00

You could have a pivot table on the next tab that automatically updates and sorts the data by category and month (for example).

E.g. 
                                    May        June        July
Supermarket               15.00      20.00       0.00
Takeaway                    11.00      0.00        12.00

Exodi

B0bbyEwing said:

Now rather than standing there in a supermarket or a shop or a cafe or wherever, trying to work things out to then input them, or open calculator to add this number to that running total, I was wondering if it is possible to input a fresh entry (D1 in this example) which then adds to the running total (B1 here). The problem then being is you would surely then need D1 to erase itself, ready for the next spend in that category while not removing the updated value from B1.

In practice, I think this will be very difficult to implement BUT alternatively to the pivot table option as others and my myself suggest above- 

You could always have Cell B1 in your picture be "=SUM(D1:Z1)" and then just quickly input each transaction in the cells D1 and to the right with B1 showing a running total. It's a pretty crude way to do it, but it's closest to your original vision.

E.g.

Supermarket  £252.10*           £17.20   £84.20   £52.20   £23.70   £74.80
Takeaway         £35.10*          £20.60   £14.50

*auto calculates

gefnew

You could try this from this sites other forum.
Budget Planner: how to manage your money - MoneySavingExpert

getmore4less

I think the best tool for the job is 

MSMoney ( free unlike YNAB)

Does everything you need.
Once set up a lot can be automated.

You can have split transactions to track categories with a mixed spend at say Tesco
Pay by CC that account has one entry.
In that entry you break down the shop.
Food booze household cleaners....

Full reporting all built in.

You can have fine details like food with sub categories, veg, meat, condiments etc.

Makes it easy to track not only what you spend but where if you use multiple shops.

Can also give extra tags to each itemized entry

I use that feature to track holiday spends.
 I can pull up all meals out for a particular holiday.  Or all spends just for that holiday.


Not real time so it's a case of entering when at the machine that has it.
Much like a spreadsheet but better.

The reason it got dropped was nothing to add, now years later app interface would resurrect it.

Once set up it does not take much time to keep on top and the manual entry focusses the mind on where you money is going.

Most find as soon as they start tracking properly the spending goes down anyway.

Debtfreewanabe is the board to hang out, that's where you will find all sorts of money saving ideas.

getmore4less

Do you have a proper budget?
I suspect not with this starting point

It's an essential part of financial planning.

Decide where you want your money to go then map that onto reality and try to get them to match.

A good trick is starting from scratch is do a retrospective of the last 12 months, work out where all your money went.

It easy to start, as a lot is big easy items often on DD.  When people do this they often can't account for a big chunk.

Using that for the basis of the plan of where you would like to get to.

Normalize on annual totals as that gives the big picture.

My old example was our weekly Friday  takeaway, when we analysed we changed the routine and used the money for an extra holiday.

It does not have to be all about saving (unless there is not enough) it's about maximum value.

B0bbyEwing

Exodi said:

It can sound and look complicated, but it's actually very intuitive and useful.

Let's say you set up a tab that showed:

01/05/2022 Supermarket 10.00
05/05/2022 Takeaway 11.00
28/05/2022 Supermarket 5.00
02/06/2022 Supermarket 20.00
02/07/2022 Takeaway 12.00

You could have a pivot table on the next tab that automatically updates and sorts the data by category and month (for example).

E.g. 
                                    May        June        July
Supermarket               15.00      20.00       0.00
Takeaway                    11.00      0.00        12.00

Ah that makes more sense to me, providing I've understood it right. So In Sheet1 (I'm assuming that's what you meant by tab?) I would just have a load of entries that would be put in as we go along. New spend goes on a new line. Spend again it goes underneath the previous one and so on.
But in Sheet2 is where the running totals would be, laid out exactly as I was talking in the OP, kind of, with this use of these pivot table things. 

If that's the case then I'll need to take a look at that as that could be ideal.

Exodi said:

very intuitive 

Seems everything is these days. Every video you look at on YouTube or TikTok that word must be thrown in about 5 times a minute. A tin of beans is probably intuitive these days. I've been on YouTube looking at various gadgety things. Fitness related tech, computing tech, all sorts. Every single item is apparently intuitive

getmore4less said:

MSMoney ( free unlike YNAB)

Ah that would be the deal breaker on YNAB then I'm afraid. 
At its most base form, I would rather just a complete list of entries that were in chronological order (so naturally a bit jumbled) than pay for something.
Although it sounds like MS Money would fall down as there's 2 of us that'd be needing to make entries & then we'd need to fire up the PC to do that, which requires remembering to do that when you're at the PC. 
Sounds a great idea and I believe it'd work if done right but I'm just being honest here as one or both of us would just simply forget by the time we get to going on the PC.

Heedtheadvice

My input.

Excel is great for these tasks as is but probably more difficult to understand a database.

Excel sort of can mimic a database and that is what is suggested by row by row entry for each spend on a 'database' sheet/tab.

 You need columns for date, the category of spend (can be prepopulated selectable list for all the types and categories you are interested in and that individual spend. The pivot table does all the analysis and reporting that is flexible to show it by date(s) category, amounts or if fact anything yo might have entered in your 'database' sheet/tab column. You just need all those elements identified with a column header.

Excel does it great, can be shared online.......but needs a bit of setting up and validating that it works correctly.

Templates for the task are better and easier to get going and are great provided they will do what you want. Many available often free on the net.

Provided they meet your need the easiest solution is the tried and tested pre made app and unless you use Excel a lot (i.e already familiar) or wish to learn probably the best solution.

(For Excel  -you can use other spreadsheet apps virtually similarly for simple tasks)

Conclusion, would suggest looking at the ready made solutions first......and has been said consider what your budgets are as tge tech solutions are just part and the simplest of the task!.

getmore4less

B0bbyEwing said:

Exodi said:

It can sound and look complicated, but it's actually very intuitive and useful.

Let's say you set up a tab that showed:

01/05/2022 Supermarket 10.00
05/05/2022 Takeaway 11.00
28/05/2022 Supermarket 5.00
02/06/2022 Supermarket 20.00
02/07/2022 Takeaway 12.00

You could have a pivot table on the next tab that automatically updates and sorts the data by category and month (for example).

E.g. 
                                    May        June        July
Supermarket               15.00      20.00       0.00
Takeaway                    11.00      0.00        12.00

Ah that makes more sense to me, providing I've understood it right. So In Sheet1 (I'm assuming that's what you meant by tab?) I would just have a load of entries that would be put in as we go along. New spend goes on a new line. Spend again it goes underneath the previous one and so on.
But in Sheet2 is where the running totals would be, laid out exactly as I was talking in the OP, kind of, with this use of these pivot table things. 

If that's the case then I'll need to take a look at that as that could be ideal.

Exodi said:

very intuitive 

Seems everything is these days. Every video you look at on YouTube or TikTok that word must be thrown in about 5 times a minute. A tin of beans is probably intuitive these days. I've been on YouTube looking at various gadgety things. Fitness related tech, computing tech, all sorts. Every single item is apparently intuitive

getmore4less said:

MSMoney ( free unlike YNAB)

Ah that would be the deal breaker on YNAB then I'm afraid. 
At its most base form, I would rather just a complete list of entries that were in chronological order (so naturally a bit jumbled) than pay for something.
Although it sounds like MS Money would fall down as there's 2 of us that'd be needing to make entries & then we'd need to fire up the PC to do that, which requires remembering to do that when you're at the PC. 
Sounds a great idea and I believe it'd work if done right but I'm just being honest here as one or both of us would just simply forget by the time we get to going on the PC.

To make it work you need to budget(make a plan).

It's important to look at everything for wasted spends, no point saving a few £ on shopping when there are bigger wins.

That needs sitting down together to go over the details and decide what you want to be spending on things.

Just recorded in a spreadsheet won't achieve the goal.

Once a week update the data and discuss.
Easy to generate the visual reports with MSM as they are built in.

Get receipts or make a note of the spends if you can't remember, white board could work.

Once a full proper budget is in place most spending is planned before it happens anyway.

One problem is once there is enough money people loose visibility of their spending and end up with lots of poor value spends.
Start thinking like you have debts it the same as not having enough savings.
Eg if you want £10k more savings think of it as a £10k debt 

ood goals are a motivator,  the big one is how to retire early and work back to see how much of your income is left for spending.


If one year back data is too much work try June and July

Easy to set up anything with a set of data to work from 2 months, should cover most things and you can add the less frequent if they have not happened like holidays.