We are data producers, every time we purchase something we are creating data, when we go to run or even everyday our footsteps can be data if we have the right tool to measure it. Data is important since if you need to improve something, you have to measure this data, apply a change and after that, measure again to see if there is any gain. The money that we get and spend is just another measure that we can get. In this post I am going to explain you how to configure and use beancount, a plain text accounting system.
Since some years ago I particullary enjoy analyzing my own data. Foot steps, the places that I have visited, in what I have been spending my time, etc… I found data a reason to motivate myself to improve in different areas. Now I am trying the same approach to analyze my personal finance. I would like to have the information to be able to reply to the following questions:
- Which months of the year you spend most money?
- How much did you spend the last week in beer?
- How much did I send to my my rent + taxes + bills?
You can only ask that kind of questions if you monitor your own data.
I wanted a system that even a person like me with no finance knowledge could use without spend much time. I am not the kind of person that is going to write every transaction by hand to obtain this data, so it has to be a solution that can be hackable and automated, but first things first.
Minimal accounting knowledge
In double accounting systems there is at least 2 accounts that are involved in a trasaction. That is reasonable, when you receive the salary corresponding to your payslip in your bank, your account receives a deposit of let’s say 5000 GBP but the money does not grow in the garden behind your home, if you received that amount of money is because your company has payed it, so that is our second account involved in this transaction. We can describe that transaction as it follows:
25-02-2017 "Salary slip payment" Assets:UK:MyAccount 5000.00 GBP Income:UK:MyPayer:Salary -5000.00 GBP
That are the very basics, we can even detail it more and assign multiple accounts, so for example another one for your National insurance contributions, payable taxes etc. But that is not going to be the case, I wanted to keep it simple.
The last concept before we continue, an asset is something that you own and a liability is something that you owe. In the previous example, We our payer gave us 5000 GBP this month, so that is an asset. And here ends my financial knowledge.
When I was about to choose a tool I had this requisites:
- Easy to use, I am newbie, so better if it is easy at least for now.
- Graphical, I want to visualize my information quickly.
- Ability to query data.
- Automated, As I said before, I don’t want to spend time doing my finance manually.
- Unification, be able to see all my accounts at once.
- Categorize all my expenses.
So having that in mind I found different tools like ledger, hledger and beancount. The 3 of them seem to be nice, but I choose beancount simply because it has a 3rd party tool called fava that covers my 2nd requisite, graphical and honestly that is really important.
Beancount can be queried to obtain data in a SQL like way, so you can create reports, see your information in graphs or simply make your own queries.
Another cool thing is that you can create accounts for categories of expenses, for ex: Expenses:Fun:Cinema o Expenses:Home:Rent so later on you can see them organized
One cool thing about this tools is that they don’t use fancy formats to store your data, they use plain text files. So yes, you can edit it in your own in case that you need. There is a really cool website about this topic that is called Plain Text Accounting that really helped me choosing the right tool.
All of the tools that I listed have the same problem, you have to manually introduce the data for each transaction and that is a huge pain, at least for me. I couldn’t find any integration with my current bank so I decided to create a tool by my own.
Given that every of the banking services that I use has the ability of export CSVs with all the transactions, I created a conversor from CSV to beancount.
This conversor is pretty easy to use, the only thing that you need is a CSV with the transaction and a yml file with the parsing rules. The file looks like this one:
--- csv: date: 0 amount_in: 6 amount_out: 5 description: 4 processing_account: "Assets:UK:MyAccount" default_account: "Expenses:Unknown" currency: "GBP" delimiter: ";" transactions: EMPLOYER: account: "Income:UK:MyPayer:Salary" comment: "Salary paid from employer" GIFFGAFF: account: "Expenses:Communications:Mobile"
As you can see, in the csv section you can define rules related with the csv file like the indexes of the fields in the file, the account that this file refers to, currency, etc…
In the transaction section you define for every row of the CSV file an account and an optional comment to create a transaction. Remember that we need two accounts, the first one is implicit because we are parsing a CSV file of a bank account, that is our first account or processing account. The second one is the account that has generated the transaction, in the case above, a transaction with description “EMPLOYER” will be matched to an account “Income:UK:MyPayer:Salary”. With this data we can generate a beancount transaction:
2014-02-28 * "EMPLOYER" Assets:UK:MyAccount 5000 GBP Income:UK:Mypayer:Salary -5000 GBP
Even if is not 100% autommatic, is enough for me, the only thing that I have to do is export my transactions to a file once every month run the conversor and autmmatically I will have my transactions to consume it in fava/beancount.
With this setup I can see all my spanish and brithish accounts at once in the same dashboard. I can query my data, categorize my expenses (almost) autommatically see my data with perspective and have a quick overview over my assets.
Fava offers a demo site where you can explore and play with it to see if is good for you.
For now I don’t host this in any service. I consume it directly in my machine and I store my files encrypted with gpg. Maybe it could be not perfect for everyone, but I prefer to have my own data safe and I don’t need to consult this data when I am out of my home. If this is not your case, you can host your own fava version in pythonanywhere for a small price.