Managing personal finances using Python

Sep 16, 2018 | Beancount , Personal Finance , Python

Have you ever been in a situation where you find yourself wondering at the end of the month where hundreds of Euros (or $CURRENCY) basically just "disappeared" and you have no idea where it all went?

I know I have been. It really does not make a difference what your income is. With the amount of marketing-speak around you encouraging you every single moment to "buy more", you inevitably end up doing just that. It also doesn't help that buying something now is as easy as clicking a few buttons on Amazon. It does let you buy things conveniently, but that convenience has a hidden price. And that hidden price adds up quickly, the more you buy.

So what's the solution? Well, the best thing is to spend less (obviously). But the second best thing (which is also a bit more realistic) is to know what you're spending on.

Early this year I found myself in a similar situation. At the end of the month, I would look at my balance and had no idea how much of that money (which was not there) was spent on eating out, rent, car, and so on. I wanted to track where all that money was going, and which spendings were not absolutely necessary.

Solution Space

Once you've identified the problem, there are plenty of solutions in the market. There are tools which you can buy (🤷‍♂️) that integrate with your bank, do their analysis, and show you shiny graphs on what you have been spending the most on and where you could save.

The problem is, that most of these tools are either proprietary or cloud-hosted.

Cloud-hosted is basically a no-go. I honestly don't care what the privacy policy says or what's in the security whitepaper. I've worked at enough software companies and talked to enough software developers to know how things really work.

Proprietary is something I could consider, but not a single one of those tools supported the German banks I have my accounts with. Oh, and the fact that I'm using Linux as my daily operating system doesn't help either.

Plain Text Accounting

Then I discovered Plain Text Accounting. The concept is simple - you maintain a list of all your transactions in a plain text file, which you are responsible for curating. So every single time you make a transaction that involves money, you record it in this file. This list of transactions follows the Double Entry Accounting method, and is written in a domain specific language which may vary slightly from tool to tool.

I found this workflow quite appealing. It basically checked off every single thing on the list of my requirements.

✔️ Simple

✔️ Open Source

✔️ Runs on Linux

✔️ No data in the cloud (unless you really want)

There are multiple implementations of this method in multiple languages - ledger, hledger, and beancount to name the most popular ones. I settled on using beancount simply because it also checked the fourth Python checkbox 🐍.

Double Entry Accounting

Double Entry Accounting is a fairly simple idea, but it can be slightly tricky to really get. It's based around the concept of "accounts" and "transactions between accounts", and the core of it is the following constraint.

In a transaction, the sum total of all the amounts posted to all the accounts must be zero.

As an example, consider the following transaction.

2018-01-15 * "Lastschrift REWE SAGT DANKE. 12345678"
    Assets:Commerzbank:EC               -31.24 EUR
    Expenses:Supermarket                 31.24 EUR

Here, an amount of 31.24 EUR is being posted to the "Supermarket" account, and a corresponding -31.24 EUR to the main Commerzbank account so that the sum total of both these amounts is 0.

Beancount

Beancount is a set of tools that lets you keep track of your finances using the double-entry method.

The idea is that you record all your transactions in a plain text file. Beancount then reads all those transactions and lets you assess your finances in any way that you can imagine. Not only can it tell you what all you've been spending money on and where all your money is, but also where all your money has ever been at any given point of time since you opened your account.

In addition, it provides a SQL-like query language which you can use to write arbitrary SQL queries over your financial history. With this in place, there's really no limit on what analysis you can make. It's quite powerful.

Here's a basic example of something that Beancount can work with.

;; -*- mode: beancount -*-

; Date format - YYYY-MM-DD

option "title" "John Doe"
option "operating_currency" "EUR"

2018-01-01 open Assets:Commerzbank:EC
2018-01-01 open Expenses:Supermarket

2018-01-15 * "Lastschrift REWE SAGT DANKE. 12345678"
    Assets:Commerzbank:EC               -31.24 EUR
    Expenses:Supermarket                 31.24 EUR

The first few lines with the option directives tell Beancount some metadata (what the title of this workspace is and that the working currency is EUR).

Next, we open two accounts, one called Assets:Commerzbank:EC, which is an Asset account with a physical bank (Commerzbank in this example). This could be, for example, where you get your salary. The second account is called Expenses:Supermarket which is an Expense account. Such accounts track where you're spending your money. You can create more Expense accounts for things like rent, car, fuel, etc.

Note that the "accounts" in Beancount don't have to be real-world bank accounts. They're just a way for you to keep track of the flow of money. Apart from the Asset and Expense accounts, there are a few other kinds of accounts available which can be used for different purposes. For example, if you decide to buy a house and take a loan, Beancount lets you define how much you still owe the bank using a Liability account. The documentation explains this in much more detail.

Given something like this, all Beancount does is track where the money is flowing. In this example there is just one transaction with roughly 30 euros going from the Commerzbank account to the Supermarket account.

It's exactly this counting that Beancount does extremely well. So well, that the final result looks something like the following.

Beancount Fava

Flow

Here's a very high level overview of what such a process looks like.

  1. At the beginning of every month, open your bank's website and download all your transactions for all your accounts for the previous month in the CSV format. Almost every bank allows doing this, and if yours does not, then it's time to switch. I personally do this once a month, but the frequency depends entirely on you. You could also do it weekly, or if you're particularly hardcore, then also daily.
  2. Convert these CSV files to the .beancount format. This format is the DSL that follows double entry bookkeeping and that Beancount understands. Beancount provides you with a set of helpers to import external data so you can do this conversion easily.
  3. That's basically it. Once your transactions are in the .beancount format, you can ask Beancount for anything you'd like to know. You can either use the console-based tools included along with Beancount, or you could install Fava and look at all your financial history in colorful graphs.

It might be some effort in the beginning to set things up, especially if you're looking to import historical data. You need to look at every transaction individually and decide which account should this be posted to.

But that's actually the point. This way you're aware of what you're doing with your money. If you spend say €50 every day purchasing random stuff on Amazon, all of that will come back and bite you at the end of the month when you have to tag all those transactions manually.

There are some drawbacks to this setup. For instance, the website of the banks where I have my accounts is not particularly optimized for downloading CSVs for a given time range.

But I think with the level of flexibility and power this whole setup provides, it's a trade-off worth making. I highly doubt any other piece of personal finance software comes even close.