Multiperiod hledger-Style Reports in beancount: Pivoting a Table

I am using plain text accounting tools like hledger and beancount alongside KMyMoney to track my financial transactions and to produce summary reports about income and expenses. If you've ever used hledger, you might like its ability to produce nice reports. One of the reports' feature is the table structure, where rows are accounts and columns are weeks, months, quarters or years. Looking at earnings and spendings as a function of time can give you more insights about your finances.

However, if you are using beancount, this feature is not yet supported in the command line interface. You need to use fava, an awesome web-interface for beancount, which has a graph drawing capability as described in this tutorial. fava is not ideal and sometimes you might need more custom reports than the ones available in fava.

Problem Statement

Suppose that you have an example beancount journal file and you want to see your income and expenses for every year. This can be accomplished by following BQL-query:

which will generate the following table:

"PIVOT BY account, year" would implement the following restructuring of the table by adding time dimension (year):

This feature is NOT implemented yet, but there is a way to emulate it using Pandas library.

Exporting beancount Data to Pandas Dataframe

We can dive in into internals of the beancount library and use Python functions to extract raw or processed data:

will print the following dataframe:

Pivoting the table by year

yields:

Converting account names into multi-level account hierarchy

produces a multi-index dataframe:

This dataframe can help to produce aggregation at different account levels:


You can invert the counter-intuitive negative sign of the "Income" account and plot income (level 0) and expenses (level 0) aggregated at a year interval: