Multiperiod Reports by Month/Quarter/Year in Beancount

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 supported yet 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.

This notebook provides methodology and tools to:

• Process BQL query's output using Pandas library
• Generate yearly totals (multiperiod reports by year) by pivoting a table
• Aggregate values at different account levels for the provided account hierarchy
• Draw treemap plots of expenses for all time period

Details are in this blog post.

Installation and Running

$ git clone https://github.com/isabekov/beancount-multiperiod-reports
$ cd beancount-multiperiod-reports
$ sudo pip install -r requirements.txt
$ jupyter lab

For example, "quarter" version of the notebook (Beancount_Multiperiod_Reports_by_Quarter.ipynb) will do the following operations on an example file:

Executing a BQL Query

cols, rows = run_query(entries, opts, 
                       "SELECT   account,   YEAR(date) AS year,\
                                 MONTH(date) as month,\
                                 SUM(convert(position, '{}', date)) AS amount\
                        WHERE    account ~ 'Expenses'\
                        OR       account ~ 'Income'\
                        GROUP BY account, year, month\
                        ORDER BY account, year, month".format(currency)
                      )

Converting Result Rows to a Pandas Dataframe

Pivoting a Table by a Time Interval (e.g. Quarter)

Creating Multi-Level Accounts

Aggregation at Different Account Levels

At level 1:

At level 0:

Income and Expenses over Time

Treemap Plot of Expenses