Personal finance tracker #

I use custom Python scripts to keep track of our family’s finances. I have always tried to keep a very detailed record of expenses, and recently I spent a few (six actually) hours building something that requires very little effort to maintain: once a month I need to tag a few transactions and run a script and I get a detailed report emailed to me with everything I want to know. The process takes around five minutes (obligatory xkcd).

Getting the data #

The most annoying recurring step is getting all the data. This is manual: I need to log into my bank’s website and download reports for all my accounts and credit cards. It only takes a couple of minutes. I store them in folders by year with a simple naming convention for the different months and accounts.

Then the fun starts: I build a Pandas dataframe for each file using .from_html which works surprisingly well (the files come from the bank’s site as .xls but are actually .html files in disguise; I previously tried BeautifulSoup which is very powerful but also very cumbersome). Then concatenate everything into a single “master” table with all my transactions (because everything is in the same folder, at this point I have my entire history in a dataframe).

Tagging transactions #

I have an additional complication that most people don’t need to deal with: my transactions are in Hebrew and I need to translate everything into English in order for it to look good and format properly (try getting Hebrew and RTL to work in \(\LaTeX\)). So I take every payee from my history and generate a JSON file containing translations, categories and other tags for each transaction.

"משהו בעברית": {
  "english_name": "Something in English",
  "category": "Groceries",
  "subcategory": "Supermarket"
}

I then spent an hour or so tagging everything (I imported a years’ data into the system when I first wrote it); once you have a year or so of transactions, you will find that the list of payees who are brand new each month becomes smaller and smaller with time. I then use this to enrich my data with English payee names, categories and other tags.

Dealing with cash and transfers #

Although I try very hard to eliminate cash from my life, sometimes you need to use it. I tag cash transactions a little differently, allowing for the possibility that one withdrawal of cash is used for a few different things (ugh). For example, below a single withdrawal of 500 shekels is tagged for two different transactions.

{
  "date": "2021-01-22",
  "payee": "כספומט",
  "split": [
    {
      "payee_english": "Friend 1",
      "category": "Dining out",
      "subcategory": "That nice restaurant",
      "total": 200
    },
    {
      "payee_english": "Friend 2",
      "category": "Dining out",
      "subcategory": "That even nicer restaurant",
      "total": 300
    }
  ]
}

Bank transfers are handled similarly.

Portfolio #

For the status of longer-term assets (investments, pension funds, etc) that aren’t associated with my day-to-day accounts, I update the values of all these assets once a month manually in a separate file. This only takes a few minutes.

Report #

I use \(\LaTeX\) to generate the report. My tendency to reach for \(\LaTeX\) (which is underused and misunderstood in my opinion) is a hangover from my days as a mathematician, and it really is a fantastic tool for creating nice-looking documents programmatically. The report includes

  • the current state of the whole portfolio and a graph plotting its history over time
  • a table with month-on-month changes in each assets’ value (total and percent)
  • a breakdown of expenses by category with month-on-month changes (total and percent)
  • stacked bar graphs showing the breakdown of expenses by category over the past year (one with very broad categories and another a little more specific)
  • a nicely formatted table with all expenses for the month

I generate the tables using pd.DataFrame.to_latex() and the plots using Plotly.

Script #

The \(\LaTeX\) file is built programmatically within Python and then compiled in Python; in the end there is a single script that you can run once you have downloaded the new data for the month: it builds the database each run from scratch, prints out a list of untagged transactions (in a format that can be copy-pasted into the relevant tag files) and builds the report. So you run it once, tag everything you need to and run it again. Rebuilding previous months’ reports requires entering the month as an argument to the script.

Monthly time spent #

October 2021 #

  • 12 minutes to collect data and generate the initial report
  • ProjectiFi looks like an interesting tool to help simulate how different decisions and market conditions can change your projected future net work.
  • Firefly, an open source personal finance manager.