Excel formula for spend tracking

ircE

Hi B0bby - have you found a solution you are happy with yet?

I can recommend Google Sheets' template Monthly Budget tracker. In the first sheet are the running totals for incomes and expenditures, and on the second sheet you can add in each entry.

If you want to keep it as close to the example you gave as possible, then perhaps the simplest solution is to keep all your receipts and have a notes app on your phone (or pen and notebook with you at all times) and whenever you get time, update the one cell in your spreadsheet with the sums. This is basically how I budget on a weekly basis and it's not too onerous because almost all of my spending is done by card, and I have different cards for different categories of spending.

B0bbyEwing

ircE said:

Hi B0bby - have you found a solution you are happy with yet?

Possibly. I'm not too sure. I haven't really found anything that jumps out at me yet and says THAT ONE.

I looked up on YouTube and saw this one https://www.youtube.com/watch?v=dPoAHHcprus which seems ok and something I could do myself. She charges £3 for it, but everything I've seen so far looks on in areas but not so much other areas. 

So maybe something like the above. Maybe the one where I mentioned say 12 sheets representing 12 months and each one having 31 entries representing the days so that when we spend something on the 15th, it gets entered in the 15th cell. That way you don't need to do any adding up yourself plus you can easily identify an exact date an exact amount was spent.

But I'm not too sure yet.

We were discussing my initial plan with this which was to see what we're spending where, but after talking about it we decided it's a little pointless because to truly do it (saving) properly, we'd only then end up gravitating towards proper budgeting later - assigning amounts to categories, and once those amounts have been hit then it's tough, within reason. So for example if we set a coffee shop allowance of £10 and we hit it in the first week then the remaining 3 weeks is just no coffee shops no matter how much we'd like to go. Obviously a degree of flexibility is needed. If we set an allowance of £200 for food & we've miscalculated & we're 3 weeks in and spent up, we're not going to not eat for a week just because of that, but once we get in to it a bit more & get used to it, we should be better assigning values to categories. 

So as I say, after discussion, we decided that we may as well just jump straight in on the budgeting side of things rather than the tracking what we're spending where & then reacting to it.


Drifts a little off topic but we have so many bank accounts. I've managed them myself for years but they get quite time consuming. I'm seriously considering just massively simplifying it alongside this which would make it much easier to see what we have and don't have. Part of me feels it's not a great idea to give up chasing the highest interest rates but really when some regular savers are 3% and Chase are doing 1.5% on their savings account, how much extra £ are we getting through having those extra bank accounts? A few quid? Enough to buy a pint during the year maybe & that's it? Just deciding whether it's worth the time vs just having a simplistic life as far as managing the accounts goes.

getmore4less

Budgets are dynamic, if the initial guess is wrong you adjust but it has to come from another category or go to another category.


MSM handles multiple accounts very well, also have cash accounts(£$€) to track cash spending.

k_man

Tracking spending before setting budgets can help get ballpark values, and identify whether your budgets are realistic, and which budget areas will need the concerted effort to meet.

k_man

k_man said:

Tracking spending before setting budgets can help get ballpark values, and identify whether your budgets are realistic, and which budget areas will need the concerted effort to meet.

To clarify, I don't mean start tracking now, and build up over time (may as well budget at the same time), but rather spend a few hours categorising the last 3 to 6 months of spending.
Either manually from statements, or through an Open Banking aggregator.

Harder if lots of spending is cash though!

Finally, there is a risk of searching for the perfect solution, rather than having a go, using the historical info you have.
Once you have started this via one method, it is much easier the second time, as you should have some of the spending already categorised, and have done much of the groundwork.
Changing your strategy/method isn't a failure or a waste, just part of improving the process.

B0bbyEwing

k_man said:

Harder if lots of spending is cash though!

There wont be lots of spending cash but there may be some. None of it will be through me though. I pay for everything with my phone these days. Can't remember the last time I paid for something with real cash in the hand.
My wife on the other hand has this thing in her head where if it's under (I think it's...) £5 then she can't pay / doesn't like to pay by contactless/card. 

For me, I even get £1 scratch cards via contactless. I bought a chocolate bar the other day for 49p - contactless. I personally don't see the big deal.


Actually I tell a lie. A few weeks ago I had a chippy dinner & anything under £5 has to be cash. I can't remember the last time before this though.

getmore4less

The history exercise is about identifying as much as you can, then you have a category for unidentified.

You can guess a lot of it, you kind of know what gets spent even if the amounts are unknown and variable.

Massive head start over a standing start tracking.

Over time you fine tune with the real tracking and the plan.

The trick is to identify categories early, too many is better than not enough.


Have a look at the SOA template that is a good start for the main categories.

https://www.lemonfool.co.uk/financecalculators/soa.php

B0bbyEwing

ircE said:

Hi B0bby - have you found a solution you are happy with yet?

At the moment looking like a toss up between:

https://youtu.be/GcqrPfOU0Zs

https://youtu.be/IAOVEOolTPU

https://youtu.be/GgWn2NHpLmI

With the favourites so far being the 1st & 3rd. Problem with the 3rd is I've had a headache since first thing this morning & the guys voice is sending me to sleep but his spreadsheet looks decent.

While I prefer Excel to Google Sheets, the advantage of Google Sheets is they can be left in a shared folder & can be edited by anyone who spends at that moment in time. It's not down to just 1 person to keep updating.

Heedtheadvice

Provided it is an accessible folder, Excel is great for sharing plus you can track changes to see who changed what and when!

k_man

You can do this using OneDrive as the storage for the Excel file.
You can also use the browser version of Excel (www.office.com).
However in my experience, Google sheets deals with multiple users editing at the same time much better, and updates are almost real time (and also includes change tracking).