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 favaan 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:

Or make a treemap plot of the expenses:

Aggregation at Month and Quarter Levels

Aggregation of transaction amounts at different time intervals (months, quarters) requires a different BQL query and a bit more playing with pandas.DatetimeIndex. The scripts and details are in the git repository.

Summary

  • "Pivot by" functionality can be implemented using Pandas library.
  • Negative sign of the quantities in the "Income" account can be fixed easily and graphs/figures similar to fava's ones can be plotted using matplotlib. In fava, negative sign of the income category has not been fixed yet.
  • Aggregation at different account levels can be done easily using mutli-index dataframes in Pandas.
  • The source code is available in this git repository.

 

References

"Beancount Query Languge" by Martin Blais

"Managing my personal finances with beancount" by Alex Johnstone