personal accounting in excel - Suggestions?

[mistymoney]

Hi all,

So one NY resolution coming up is to keep much better tabs on the cash flow situation. What I am doing now is periodically downloading all transactions from X to Y date, for checking and credit cards, do a few categories changes/additions, and then add to a yearly excel sheet. I then use pivot tables to get category by month summaries.

I've tried other things in the past, like mint, YNAB, etc. and that hasn't been helpful. On YNAB I made some input error but was unable to rectify it and just gave up. Mint is gone and YNAB is now a pricey product, and while I recall some nice visuals, doesn't seem worth it.

So - some questions for those of you using excel for spending tracking:
How often do you enter data?
How often do you summarize?
What column headings do you use?

I have:
type (debit or credit)
source (checking or which cc)
date
month
day
description (from checking or cc)
category (like utilities or holiday)
category sub1 (like electric or christmas)
category sub2 (not used often, could be like DD or DS under christmas, for example or which pet went to the vet)
amount.

For main categories I have:
Car
Clothing
Electronics
Entertainment
Food
Gifts & Donations
Health & Wellness
Holiday
House Maintenance
Income
Mortgage
Payments
Pets
Savings
Services
Side Gig
Student Loans
Taxes
Travel
Utilities
Withdrawal
Work

Where I keep getting tripped up is in some circular things. Like, a charge goes onto a credit card, I pay the credit card out of checking, etc. I have given myself a heart attack or two when I try to download everything for a longer period of time, and look at total spent and it is HUGE! But then I realize that I have the credit card payment in there as a spent, but then all the items from the credit card in there as spent as well. Embarrassingly, I seem to make this mistake alot!.

Then there are reimbursements. I have been working off of net income, so not inputting anything taken out of my paycheck. Maybe that is a mistake, but was trying to save time. So tax for example only shows up when I have a refund or need to pay more and then any expenses associated with filing (turbotax, or a person, or whatever....). So then I have reimbursements and I don't think those are accruate. Like there is work travel where I made money, then I spent on work, then I get reimbursement. So this shows up as higher spending and higher income than should be.

Then there are medical expense reimbursements. Money goes into HSA, flex, insurance plans - but I haven't been keeping that in my spreadsheet using only net. then I spend money, then get reimbursed. In some cases it goes first to insurance, get that check, then to flex. So if I start with gross, and put all my paycheck dedcution in, how would you experts work this in excel?

Also get tripped up with what is a minus and a plus - checking makes my spending negative and credit card a postive and that has tripped me too!

Since I don't do the download and summary very often, it is always a headache, and then I don't do it and the headache increases!

And then after having a series of obvious errors, that I look through and fix or discount somehow, I get something fairly reasonable looking - but I have low confidence in the results. As well as there being things that are just lost - like a big pile of amazon changes cover 6 months or more....very unweildly. I've tried to back over those and categorize but on the one hand it's a lot, and on the other a relatively trivial amount to try to account for given the effort to try to code them using forensic techniques.

Amazingly, I work very well with data in my dayjob! sidegig too! This data just eludes me - I don't have an intuitive feel for how it works. Maybe past trauma around finances is a factor too. Decades of it, really.

Interested in advice on structure - columns, categories, etc. and process. Seems like some of you enjoy this kind of thing....how? I just want the results, lol!

[Sanitary Stache]

Quickly, I don’t download the data from my bank or CC. I recreate my own data in split screen. I do it with DW so we can talk about what category something should go in if it’s not obvious. Sometimes a purchase at one location really needs to be split, like clothes and animal feed.

I fill my spreadsheet once a month, sometimes twice if my work is super boring. I put a note in on certain expenses if I need to check with DW. I usually put a note in. I have maybe 70 data points to enter in a big spending month. It’s takes 25-40 minute.

 I started out tracking expenses by date, but now I limit my spreadsheet to 10 expense cells per month. It keeps my hole tracking sheet from needing to scroll along columns. This also helps limit how many purchases I make in a month. Each month is a new sheet. Refunds get entered in as negative to the category they were made in if in a different month from when the item was purchased or into the same cell as the purchase.

I track income and taxes in different sheets in the same workbook and link them where appropriate. I try to track gross income and payroll taxes. I built my income spreadsheet around my paystub. But I can add other sources of income in so it gets into my tracking sheet. This is more to let me know how much I have coming in each month and matching my expenses to it. My savings are tracked on the monthly budget but separately from spending so when I create a year end graph showing where money went I can look at just spending or just saving a little easier.

I usually use the case study spreadsheet to estimate taxes.

My visuals aren’t so great in my budget tracking. I get better visuals if I copy yearly spending into my projection workbook where I look at past years and make projections on spending for figure years and track my investments.

[hooplady]

Whenever I've done any kind of accounting in Excel I always have three main categories: Income, Expense, and Transfer. I'll make sub-categories under Income and Expense. Those "circular" things you're referring to are simply Transfers between accounts. Your transfers should always net to zero; I usually have one pivot table filtered just for Transfers so I can be sure nothings was accidentally entered as something else.

When analyzing your Income and Expenses, it's easy to just filter out the Transfer category. Or you can leave it there since there's no net effect. No more heart attacks!

[hooplady]

Oh and now that I'm FIRE I'm super lazy, I only download things once a year - mainly to prepare for taxes.

[NorthernIkigai]

I’ve been tracking our outgoings for the last three years in a simple, home made Google Sheet. It’s evolved a little bit over this time, but not much. I don’t cover income at all in this, so many of the issues you mention just don’t occur.

My categories are
Housing   Groceries etc   Eating out   Child care etc   Insurance   Kid's hobbies   Internet / kid's phone   Health   Family outings   Kids' clothes   Electricity   Cable   Alcohol   Household stuff   Gifts   Bus passes for kid   Kid haircuts   Travel

In addition, we have an amount of spending money for each adult that doesn’t get tracked at all. I’d go crazy (and I’m sure spouse would have given up on this whole thing a long time ago) if we’d record every single packet of gum (gum bought as part of groceries of course does fit in here…).

Every item gets tracked when it is paid for. It helps that we don’t use credit cards much and usually pay off any credit usage straight away. Where we live you don’t get US style benefits from using credit cards, but it’s safer to buy online using credit than debit.) Filling this sheet in daily means it’s super easy and quick, and having a shared account where almost all household expenses go from helps a lot, too.

I don’t get the question about summarizing — this is a spreadsheet, it does it for you. And I don’t have any fancy graphics for this spreadsheet, I save that for my stash spreadsheet… And anyway, I think graphics make sense once you start building some data. Before that, it’s harder to figure out what you want to visualise and how.

[Gremlin]

We do.

I would take a step back, though, and ask why are you wanting to track your expenses?  I'm a big believer that any analysis needs to have a purpose and the purpose then defines what you track, and potentially how and why.  What was the motivation behind your NY resolution?

For us, the purpose is to ensure we are spending in line with what we think we're spending and to keep us motivated.  Because we're tracking to something in line with what we think we're spending, we also have a 'forecast' or a 'budget' for each category.  We look at spending performance monthly, but record daily - it's a two minute job once a day, 15 minutes once a month and an hour or two once every six months (when we revisit/revise our budgets) to manage.

Therefore, we split our expenses into three supercategories:

• Regular - these expenses are relatively small and repeatable things that happen every month.  For example, groceries are here, as are recurring monthly bills that are the same each month
• Major - probably not the best name, because they are not necessarily 'big' expenses, but these are the things that happen at known times of the year, but not every month.  For example, our quarterly utility bills or annual insurances are here
• Irregular - these are things that we think we're going to spend money on, but we don't know how much or when.  For example, we have 'home repairs' here

Within each supercategory, we have categories of spend.  Eg, within 'Regular' our categories are Groceries, Health, Internet, Petrol.  Some we break down further.

This enables us to understand seasonality in our spending.  Previously we used to be harsh on ourselves when our December spending was through the roof.  Gee, we must be overindulging at Christmas - even though it didn't feel like it.  But now we've adopted this approach, we can easily see that we have a lot of 'Major' expenses that fall due in December.  December is our most expensive month due to insurances and a seasonally high electricity bill, not because of Christmas overindulgence.

Because we have seasonality in our spending, we also therefore have seasonality in our budget.  Analysing monthly (other than the 'Irregulars, which we analyse annually) helps us stay on track and stay motivated.

[Sandi_k]

I use a spreadsheet as well. Each tab is a month, and each spreadsheet is for the year - so I can always search by the year if I am looking for historical data.

My main categories are:

Income
Savings - right off the top, retirement and sinking funds here.

Household
 - PITI
 - Utilities (garbage, gas, electric, water)
 - Entertainment (Cable and Netflix)
 - Groceries (Costco, Safeway, BevMo)
 - Dining Out
 - Hardware/maintenance
 - Pet & Vet
 - Car insurance
 - Car maintenance
 - Gas

One Time expenses this month
 - Examples: Holiday party food if we're hosting; tree trimming 2x per year; car registration or smogging; Xmas tree

Irregular Expenses:
- Property taxes
- House Insurance
- Fed taxes
- State taxes
- Vacation
- Tax Prep
- Boating Insurance

Since you're so committed to spreadsheets, have you considered Tiller? It's my top contender for the next level of expense tracking, as we are <2 years out from retirement...

[mistymoney]

thanks @Sandi_k, tiller does look interesting, more affordable than YNAB too.

Would prefer not to spend on this, but I do like some of those features they have.

[trollwithamustache]

we do this manually and monthly. But we aren't super anal. So we often round up / down amounts to make it easy to quickly scan down the CC statement and shove all the dollars into different categories. Yes, I then use an Other Credit Card expenses slush/swing unaccounted fund to make up any difference.   

Since we are comfortable with our spending habits and we spend less than we make, it's just not worth it to worry about the details too much. In terms of net assets, I rack those up 2x a year.

[JuneApple]

I'm an Excel user, but for several years I used the Google Sheet "One Sheet to Rule Them All" created by @IndyPendent. It's the only thing I've used Google Sheets for, and I didn't find the many minor differences between Sheets/Excel too annoying. https://forum.mrmoneymustache.com/share-your-badassity/one-sheet-to-rule-them-all/

I've since transitioned to GnuCash. It's free and open source. And it's simple enough that I taught my Mom (who is NOT tech savvy) how to use it for her personal accounting.

[the lorax]

I download transaction data fortnightly into Excel from our current account and credit card. I categorise the spends from each into my summary table. I also have the debits for the current account and the credit card transactions showing up as positive but I just convert them so they are all the same in the table. I note the individual credit card transactions then just ignore the payments that clear the credit card to make things easier (they would net out to zero anyway). For medical expenses (but in NZ so likely simpler here) I just put them in as the whole cost when they occur and then have a debit entry in that same 'medical expenses' column when we get reimbursed. 

The way I have mine organised is by fortnightly blocks and then I have a small block of rows with average spend YTD, cumulative spend YTD, expected spend YTD and also a comparision with the year prior all formatted so I can easily see where we are spending more than I thought we would.
I then copy and paste that summary block after each time I run our numbers and just add the new fortnight's data into the formulae for those rows. It's not the most elegant solution but it works and I like seeing how we are tracking vs previous years

[FIRE 20/20]

We do.

I would take a step back, though, and ask why are you wanting to track your expenses?  I'm a big believer that any analysis needs to have a purpose and the purpose then defines what you track, and potentially how and why.  What was the motivation behind your NY resolution?

I want to highlight this comment.  When I was working as an engineer, one of the most common pitfalls was people starting to work on the solution before understanding the problem.  Usually the problem feels so obvious that it doesn't need to be defined, but neglecting to do so often results in a sub-optimal solution. 

We only track expenses.  My partner handles almost all of our finances, and she likes to manually check each credit card statement when it comes in to ensure no one has stolen our card information.  While she checks it she enters expenses into the spreadsheet.  Our setup is very simple; it's just there to track expenses.  She separately checks account balances once a week.  She knows this is far more often than necessary, but it gives her peace of mind.

Our main categories are:
Housing
Transportation
Food
Miscellaneous
Personal care
Entertainment
Taxes
Gifts/Donations
Travel

Sub-categories vary from 2-12.  The sub-categories have changed a little over time. 

Going back to my main point above, since my partner primarily cares about knowing what our spending is and verifying that our cards haven't been compromised, the main spreadsheet only takes in spending (primarily credit cards).  I'm more concerned about trends, so I have separate tabs that let me check in throughout the year with a very simple estimate of total annual spending (just average spending / month * 12).  I initially tried a much more complicated solution which included actual vs. expected costs / month, but it was vastly more complicated and didn't actually help anything.  If we're way off we would already know, and the monthly average would confirm that just as well as the complicated approach.  So we just go with what's simple and meets our needs.