Multiperiod Reports by Month/Quarter/Year in Beancount.

Overview

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

Income and Expenses over Time

png

Treemap Plot of Expenses

png

You might also like...
A chain of stores wants a 3-month demand forecast for its 10 different stores and 50 different products.
A chain of stores wants a 3-month demand forecast for its 10 different stores and 50 different products.

Demand Forecasting Objective A chain store wants a machine learning project for a 3-month demand forecast for 10 different stores and 50 different pro

This is a Fava extension to display a grouped portfolio view in Fava for a set of Beancount accounts.
This is a Fava extension to display a grouped portfolio view in Fava for a set of Beancount accounts.

Fava Portfolio Summary This is a Fava extension to display a grouped portfolio view in Fava for a set of Beancount accounts. It can also calculate MWR

A chain of stores, 10 different stores and 50 different requests a 3-month demand forecast for its product.
A chain of stores, 10 different stores and 50 different requests a 3-month demand forecast for its product.

Demand-Forecasting Business Problem A chain of stores, 10 different stores and 50 different requests a 3-month demand forecast for its product.

Generates images of calendar month tables and can paste them onto suitable photos.
Generates images of calendar month tables and can paste them onto suitable photos.

📆 calendizer README Generates images of calendar month tables and can paste them onto suitable photos. A quick way to make your own calendar for prin

This is a python interactive story game that I made to show off what I've learnt in python coding for a month

Purpose The files in this repository are for that of a story game created with python version 3.8.5 The purpose of this project was to get familiar wi

I'm doing Genuary, an aritifiacilly generated month to build code that make beautiful things
I'm doing Genuary, an aritifiacilly generated month to build code that make beautiful things

Genuary 2022 I'm doing Genuary, an aritifiacilly generated month to build code that make beautiful things. Every day there is a new prompt for making

for those who dont want to pay $10/month for high school game footage with ads

nfhs-scraper Disclaimer: I am in no way responsible for what you choose to do with this script and guide. I do not endorse avoiding paywalls or any il

Opinionated code formatter, just like Python's black code formatter but for Beancount

beancount-black Opinionated code formatter, just like Python's black code formatter but for Beancount Try it out online here Features MIT licensed - b

It's final year project of Diploma Engineering. This project is based on Computer Vision.

Face-Recognition-Based-Attendance-System It's final year project of Diploma Engineering. This project is based on Computer Vision. Brief idea about ou

This is the 25 + 1 year anniversary version of the 1995 Rachford-Rice contest
This is the 25 + 1 year anniversary version of the 1995 Rachford-Rice contest

Rachford-Rice Contest This is the 25 + 1 year anniversary version of the 1995 Rachford-Rice contest. Can you solve the Rachford-Rice problem for all t

Creating a statistical model to predict 10 year treasury yields
Creating a statistical model to predict 10 year treasury yields

Predicting 10-Year Treasury Yields Intitially, I wanted to see if the volatility in the stock market, represented by the VIX index (data source), had

An 8D music player made to enjoy Halloween this year!🤘

HAPPY HALLOWEEN buddy! Split Player Hello There! Welcome to SplitPlayer... Supposed To Be A 8DPlayer.... You Decide.... It can play the ordinary audio

Show my read on kindle this year

Show my kindle status on GitHub

Our Ping Pong Project of numerical analysis, 2nd year IC B2 INSA Toulouse

Ping Pong Project The objective of this project was to determine the moment of impact of the ball with the ground. To do this, we used different model

Predicting diabetes over a five year period using logistic regression and the Pima First-Nation dataset

Diabetes This script uses the Pima First Nations dataset to create a model to predict whether or not an individual will develop Diabetes Mellitus Type

Bezlik Year Calendar Planner
Bezlik Year Calendar Planner

Bezlik Year Calendar Planner Scribus script for creating year planners on one page A1 paper format. Script is based on Year-Calendar-Script-for-Scribu

Data Analysis for First Year Laboratory at Imperial College, London.
Data Analysis for First Year Laboratory at Imperial College, London.

Data Analysis for First Year Laboratory at Imperial College, London. For personal reference only, and to reference in lab reports and lab books.

With Christmas and New Year ahead, it is time for some festive coding. Here is a Christmas Card for you all!

Christmas Card With Christmas and New Year ahead, it is time for some festive coding! Here is a Christmas Card for you all! NOTE: I have not made this

This is Gaurav's IP Project Completed in the year session of 2021-2022.

The Analyser by Gaurav Rayat Why this Project? Today we are continuously hearing about growth in Crime rates and the number of murders executed day by

This is a Fava extension to display a grouped portfolio view in Fava for a set of Beancount accounts.

Fava Portfolio Summary This is a Fava extension to display a grouped portfolio view in Fava for a set of Beancount accounts. It can also calculate MWR

null 18 Dec 26, 2022
Show my read on kindle this year

Show my kindle status on GitHub

yihong 26 Jun 20, 2022
Our Ping Pong Project of numerical analysis, 2nd year IC B2 INSA Toulouse

Ping Pong Project The objective of this project was to determine the moment of impact of the ball with the ground. To do this, we used different model

null 0 Jan 2, 2022
With Christmas and New Year ahead, it is time for some festive coding. Here is a Christmas Card for you all!

Christmas Card With Christmas and New Year ahead, it is time for some festive coding! Here is a Christmas Card for you all! NOTE: I have not made this

CodeMaster7000 1 Dec 25, 2021
This is Gaurav's IP Project Completed in the year session of 2021-2022.

The Analyser by Gaurav Rayat Why this Project? Today we are continuously hearing about growth in Crime rates and the number of murders executed day by

null 1 Dec 30, 2021
Socorro is the Mozilla crash ingestion pipeline. It accepts and processes Breakpad-style crash reports. It provides analysis tools.

Socorro Socorro is a Mozilla-centric ingestion pipeline and analysis tools for crash reports using the Breakpad libraries. Support This is a Mozilla-s

Mozilla Services 552 Dec 19, 2022
A simply dashboard to view commodities position data based on CFTC reports

commodities-dashboard A simply dashboard to view commodities position data based on CFTC reports This is a python project using Dash and plotly to con

null 71 Dec 19, 2022
Write a program that works out whether if a given year is a leap year

Leap Year ?? This is a Difficult Challenge ?? Instructions Write a program that works out whether if a given year is a leap year. A normal year has 36

Rodrigo Santos 0 Jun 22, 2022
Trading Strategies (~50%) developed by GreenT on QuantConnect platform over the autumn quarter

Trading Strategies ~50% of codes from the Applied Financial Technology Course. Contributors: Claire W. Derrick T. Frank L. Utkarsh T. Course Leads: Dy

Utkarsh 2 Feb 7, 2022
Fava - web interface for Beancount

Fava is a web interface for the double-entry bookkeeping software Beancount with a focus on features and usability. Check out the online demo and lear

null 1.5k Dec 30, 2022