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
Account | YearMonth | Amount (USD) | |
---|---|---|---|
0 | Expenses:Financial:Commissions | 2018-10 | 44.75 |
1 | Expenses:Financial:Commissions | 2018-11 | 35.8 |
2 | Expenses:Financial:Commissions | 2018-12 | 35.8 |
3 | Expenses:Financial:Commissions | 2019-05 | 35.8 |
4 | Expenses:Financial:Commissions | 2019-06 | 8.95 |
Pivoting a Table by a Time Interval (e.g. Quarter)
Account | 2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | 2019-Q1 | 2019-Q2 | 2019-Q3 | 2019-Q4 | 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Expenses:Financial:Commissions | 0 | 0 | 0 | 116.35 | 0 | 44.75 | 71.6 | 8.95 | 0 | 98.45 | 62.65 | 71.6 |
1 | Expenses:Financial:Fees | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 |
2 | Expenses:Food:Coffee | 0 | 5.49 | 0 | 0 | 0 | 0 | 36.76 | 0 | 0 | 0 | 43.07 | 0 |
3 | Expenses:Food:Groceries | 582.97 | 559.27 | 616.3 | 540.3 | 480.78 | 722.67 | 520.4 | 641.2 | 711.49 | 581.02 | 442.03 | 557.04 |
4 | Expenses:Food:Restaurant | 948.18 | 948.24 | 1139.92 | 1027.88 | 983.15 | 1127.47 | 1780.95 | 1064.27 | 1109.25 | 1143.04 | 1214.8 | 933.98 |
Creating Multi-Level Accounts
Account_L0 | Account_L1 | Account_L2 | Account_L3 | Account_L4 | Account_L5 | 2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | 2019-Q1 | 2019-Q2 | 2019-Q3 | 2019-Q4 | 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Expenses | Financial | Commissions | 0 | 0 | 0 | 116.35 | 0 | 44.75 | 71.6 | 8.95 | 0 | 98.45 | 62.65 | 71.6 | |||
1 | Expenses | Financial | Fees | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | 12 | |||
2 | Expenses | Food | Coffee | 0 | 5.49 | 0 | 0 | 0 | 0 | 36.76 | 0 | 0 | 0 | 43.07 | 0 | |||
3 | Expenses | Food | Groceries | 582.97 | 559.27 | 616.3 | 540.3 | 480.78 | 722.67 | 520.4 | 641.2 | 711.49 | 581.02 | 442.03 | 557.04 | |||
4 | Expenses | Food | Restaurant | 948.18 | 948.24 | 1139.92 | 1027.88 | 983.15 | 1127.47 | 1780.95 | 1064.27 | 1109.25 | 1143.04 | 1214.8 | 933.98 |
Aggregation at Different Account Levels
At level 1:
Account_L0 | Account_L1 | 2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | 2019-Q1 | 2019-Q2 | 2019-Q3 | 2019-Q4 | 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Expenses | Financial | 12 | 12 | 12 | 128.35 | 12 | 56.75 | 83.6 | 20.95 | 12 | 110.45 | 74.65 | 83.6 |
1 | Expenses | Food | 1531.15 | 1513 | 1756.22 | 1568.18 | 1463.93 | 1850.14 | 2338.11 | 1705.47 | 1820.74 | 1724.06 | 1699.9 | 1491.02 |
2 | Expenses | Health | 678.3 | 581.4 | 678.3 | 581.4 | 678.3 | 581.4 | 678.3 | 581.4 | 678.3 | 581.4 | 678.3 | 678.3 |
3 | Expenses | Home | 7803.2 | 7810.58 | 7790.05 | 7806.36 | 7798.26 | 7821.55 | 7820.87 | 7828.41 | 7819.9 | 7819.41 | 7820.04 | 5234.56 |
4 | Expenses | Taxes | 13945.4 | 11953.2 | 13945.4 | 11633.2 | 14854.4 | 11953.2 | 13945.4 | 11633.2 | 14882.1 | 11953.2 | 13945.4 | 13343.9 |
At level 0:
Account_L0 | 2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | 2019-Q1 | 2019-Q2 | 2019-Q3 | 2019-Q4 | 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Expenses | 24330 | 22230.2 | 24542 | 22077.5 | 25166.9 | 22623 | 25106.3 | 22129.4 | 25573.1 | 22548.5 | 24578.3 | 21191.3 |
1 | Income | -36677.9 | -31438.2 | -33927.9 | -27956 | -36728.8 | -31368.2 | -34178.4 | -27953.5 | -36793.2 | -32343.2 | -34095.5 | -32699 |