Excel formula for spend tracking

B0bbyEwing

I want to start tracking our spends and see where we can make savings but the problem is going to be keeping it going. I'm thinking something like a shared Google Sheets document.
I think for it to work, it is going to have to be as automatic as it possibly can be, which includes auto-calculations, if that's even possible. 
What I want to avoid is input error & calculation error as much as I can. I can't do a whole lot about input error but I can look to reduce it. Calculation error can be removed if what I'm here asking about is possible. I've just quickly made a mock up to illustrate:



So in the A column you'd have your various categories. 
In the B column would be how much you've spent on that category in that month. Currently I'm thinking of 12 sheets in a single book to cover each month.
But then you spend additionally within a category. In this example, last week we spent £34.50 at the supermarket. We go again & spend £24.37. 

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. 

I know roughly what I'm wanting to achieve but not sure if I'm explaining it all too well. I am also open to suggestions that will work better than this, so if anyone is willing to share what they currently use to tick the same box then that would be most appreciated. Thank you.

Andy_L

Achievable. But I don't think there's a way of doing it without firing up the Developer Tab in excel and playing around with visual basic.

The easier option would be looking at limiting column A to a set of pre-defined categories (eg supermarket, gas, elec, car etc) with a drop down menu (Data>Data validation) to ensure the same one is used each time & having a separate row for each spend.

B0bbyEwing

Andy_L said:

Achievable. But I don't think there's a way of doing it without firing up the Developer Tab in excel and playing around with visual basic.

The easier option would be looking at limiting column A to a set of pre-defined categories (eg supermarket, gas, elec, car etc) with a drop down menu (Data>Data validation) to ensure the same one is used each time & having a separate row for each spend.

I'm not sure what developer tabs or visual basics are so probably not achievable by me by the sounds of it.

Are you saying then that in column A, instead of having only 1 entry for each category & having a running total for each category in B you would just enter everything in individually?

Obviously things like council tax, energy bills, mobile phone bills, union fees etc. --> all these things will only have 1 entry per month.
But other things will have multiple. Supermarket spends, coffee shop, takeaway. I'd like to categorise it like that as I feel we overspend on takeaways and certainly coffee shops so I want to focus some attention there. 
So in my initial suggestion, these would only ever be entered once in column A with a running total next to it in column B. Are you suggesting that if I go to a coffee shop 5 times for example, then there will be 5 individual entries in A with the appropriate value in B next to it? Or have I misunderstood you & you're saying something else?

If I've actually got the right end of the stick then categories will just end up in chronological order - it may go supermarket, coffee shop, coffee shop, takeaway, supermarket, council tax. So in this, two coffee shops are together but the supermarket spends are spread apart. 
Other than manually going down the list & tallying off each category, hoping you haven't missed an entry, is there an efficient way to group each category together & get a final end of month total for each category?

Andy_L

B0bbyEwing said:

Andy_L said:

Achievable. But I don't think there's a way of doing it without firing up the Developer Tab in excel and playing around with visual basic.

The easier option would be looking at limiting column A to a set of pre-defined categories (eg supermarket, gas, elec, car etc) with a drop down menu (Data>Data validation) to ensure the same one is used each time & having a separate row for each spend.

I'm not sure what developer tabs or visual basics are so probably not achievable by me by the sounds of it.

Are you saying then that in column A, instead of having only 1 entry for each category & having a running total for each category in B you would just enter everything in individually?

Yes each transaction would be its own row - its how "proper accounts" would be done. Ideally you should have the date on the row as well 

Obviously things like council tax, energy bills, mobile phone bills, union fees etc. --> all these things will only have 1 entry per month.
But other things will have multiple. Supermarket spends, coffee shop, takeaway. I'd like to categorise it like that as I feel we overspend on takeaways and certainly coffee shops so I want to focus some attention there. 
So in my initial suggestion, these would only ever be entered once in column A with a running total next to it in column B. 

Once its in excel you can sort and filter it by those categories - so you could, eg, look at all the spends on the "takeaway" category

Are you suggesting that if I go to a coffee shop 5 times for example, then there will be 5 individual entries in A with the appropriate value in B next to it? Or have I misunderstood you & you're saying something else?

Nope, that's what I mean

[Deleted User]

Ok so forgive me because I'm not going to specifically answer any of your questions about Excel, but this looks like a problem that has been solved many times before.

You can get ready-made Excel spend trackers such as:

https://templates.office.com/en-us/personal-money-tracker-tm00000038

Or what I would highly recommend is an app called YNAB that I've used for many years now for budgeting and spending tracking:

https://www.youneedabudget.com/

You can use it all online and use a mobile app to input spending immediately as it happens, or just import just transactions from your bank.

Compared to setting up your own spend tracker on Excel and troubleshooting your formula for hours when you realise you are out by £14.23 at the end of the month, just use a ready-made tried and tested system.

B0bbyEwing

[Deleted User] said:

Or what I would highly recommend is an app called YNAB that I've used for many years now for budgeting and spending tracking:

h t t p s : / / w w w . y o u n e e d a b u d g e t . c o m /

You can use it all online and use a mobile app to input spending immediately as it happens, or just import just transactions from your bank.

Thanks. Sounds promising. 
Not sure about importing transactions from the bank as we have that many accounts that it depends what's being spent on as to what account is being used. It may be the Amex C/Card, the Nationwide C/Card, my Chase acc, partners Chase acc or another acc. 
I can take care of the direct debits, that's easy enough. It's the going to a supermarket, cafe, sweet shop, market stall etc where it may fall down so am looking to reduce that.This YNAB you mention, can you share an account?

So for example, partner goes to the supermarket and buys food to make dinner tonight. They update this account in YNAB to say they just spent £9.99. At the same time though I'm also at the corner shop picking up a newspaper & a sly chocolate bar. Can I update that exact same account so we can both see the spends or do we each need individual ones to manage ourselves?

grumbler

B0bbyEwing said:

So in the A column you'd have your various categories. 
In the B column would be how much you've spent on that category in that month.

Sorry, I don't get it. Do you calculate this figure manually and it's a number, not a formula? If not, why not to add £24.37 to all other expenses where they are listed?

WillowLea1962

You want a Data Table with at least SIX columns: Col A Date; Col B Month [use =TEXT(A1,"MMMM") to calculate the month from the date in the previous column]; Col C Store; Col D Expenditure Category; Col E Amount; Col F Reconciled.  I recommend your table has a header row with these titles as it makes it really easy to sort and filter.

I suggest you create some standard Expenditure Categories for your drop down list, i.e. Groceries; Clothes; Car; House etc etc depending on what you spend your money on

Then using Data Validation reference the list of these Categories 

Lastly use a pivot table to analyse the spend each month.  In the Field List use Filter as the both the MONTH and RECONCILED and Rows the CATEGORY and the Values Sum of AMOUNT.

To give yourself an extra check when the AMOUNT appears on your statement mark the row (in col F) with an R for Reconciled i.e. checked as correct data entry

As an added feature for this table; if it was only recording credit card spend but you have more than one card i.e. a AMEX, Barclaycard, M&S Bank etc then add another column into the Table to identify on which card the transaction took place (again use a Drop Down List to validate the data entry)

B0bbyEwing

WillowLea1962 said:

You want a Data Table with at least SIX columns: Col A Date; Col B Month [use =TEXT(A1,"MMMM") to calculate the month from the date in the previous column]; Col C Store; Col D Expenditure Category; Col E Amount; Col F Reconciled.  I recommend your table has a header row with these titles as it makes it really easy to sort and filter.

I suggest you create some standard Expenditure Categories for your drop down list, i.e. Groceries; Clothes; Car; House etc etc depending on what you spend your money on

Then using Data Validation reference the list of these Categories 

Lastly use a pivot table to analyse the spend each month.  In the Field List use Filter as the both the MONTH and RECONCILED and Rows the CATEGORY and the Values Sum of AMOUNT.

To give yourself an extra check when the AMOUNT appears on your statement mark the row (in col F) with an R for Reconciled i.e. checked as correct data entry

As an added feature for this table; if it was only recording credit card spend but you have more than one card i.e. a AMEX, Barclaycard, M&S Bank etc then add another column into the Table to identify on which card the transaction took place (again use a Drop Down List to validate the data entry)

I'm sorry, I don't have a clue what you just said. Thanks for taking the time to reply though.

I'll do some template searching to see if I can find anything suitable as per tallmansix suggestion while I wait on the YNAB answer.

movilogo

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. 

B0bbyEwing

movilogo said:

use actual date instead of "date" above.

Oh dear. Are the vibes I'm giving off that bad? 

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?

After what tallmansix said about templates I was having a look this morning. I can't post links so it tells me but I found one that had an alternative look on the same issue. Where I'm looking at say 1 money value entry per category, this template had up to 31 - one for each day of the month. 
So the categories would go in the A column but then beginning with B1 that would be 1 (1st), C1 would be 2 (2nd) and so on.

Seems so simple and one of those wonder why I didn't think of it moments but it may do the job. I'll have a look at a few more templates to see if I can find anything better.