Tracking Personal Finances

I spend a few hours every month, neurotically tracking personal finances for me and my family.  At the end of every month, I go through each of our accounts and move each transaction to a spreadsheet that holds every penny, aggregated by category, organized into balances, and that shows how we’re doing month-to-month.  I’ve been doing this for 8 years.

Yes, it takes a long time to do this – a few hours each month.  I’ve tried the alternatives though: Quicken, Mint, and Personal.  None of these products does everything I want.  In particular, none of them seamlessly connects to all of my accounts electronically.  My family has several checking and savings accounts, retirement accounts, credit cards, etc… While most personal finances connect without any issues to Chase, not all consistently can pull back, for example, my wife’s NYC Teachers Retirement balances.

It’s been a while since I used Quicken, but I recall it was pain dealing with dividends and anything investment related.  With Mint, I was never able to get all of my accounts in sync.  There’s a particular way I want to look at my balances, and the only way to get there is to control it directly myself.

Nothing Beats Excel

Transactions

So I have a big-ass spreadsheet.  On one tab are transactions.  Each transaction from each account gets put here. Each transaction is categorized into one of a few high-level buckets: Food, Entertainment, Clothing, Mortgage, etc… Once a transaction is categorized, the spreadsheet learns (via VLOOKUP) how to categorize that payee/payer thereafter.  A pivot table overlays the transactions enabling a view of spending by category, month-to-month.

Balances

A second worksheet holds another pivot table of the transactions, but this time cumulatively aggregated by account, month-to-month.  This table shows how much we have, owe, and gained/lost per account from one month to the next.  Each account is categorized as Asset, Liability or Mortgage, which allows the pivot table to group things appropriately.

One perhaps unique thing I do with investments is add dummy entries that mark my holdings to market at end-of-month.  If I own some stock that’s tanked this month, I want my balances to account for this; however, as the balances are entirely driven by the transactions, the only way to reconcile things is to create entries for Unrealized Gains/Losses.   At one point, I was downloading prices directly into the spreadsheet, but I didn’t gain any value from that.

Investments

A third worksheet holds our budget.  There are three tables here.  The first table is a forecast – for our fixed and known expenses, it projects out what we can expect to be spending month-to-month.  The second table holds our actual spending, for just those transactions relevant to the budget.  That is, it doesn’t make sense to include dividends received in the budget, so each transaction has to know whether it should or shouldn’t be included in the budget dataset.  Lastly, a third table combines the projection with the actual data, showing how far or close we are to meeting our goals.

A fourth worksheet holds some meta-data about the accounts, and a fifth contains a dashboard summarizing the whole thing (Net Worth and Budget vs Spent).

Scraping Support?

I’ve tried replicating this spreadsheet on Google Sheets and on Apple Numbers.  Both of these pale in comparison to Excel.  If there’s enough interest in a blank template of this thing, I can put that together.

Along the way I worked on automating this whole thing: scrape my transactions, store them in a database, throw in some visuals, and I don’t have to deal with the spreadsheet any longer.  I found that scraping transactions from 30 or so accounts sucks.  By the time I got my Chase scraper working, they changed their website.  It’s understandable how companies like Yodlee can stay in business.  That said, my code for this process provides a solid framework should anyone want to take a similar stab for themselves.  The repository is available on github here.