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

🦠 A simple and fast (< 200ms) API for tracking the global coronavirus (COVID-19, SARS-CoV-2) outbreak.

🦠 A simple and fast ( 200ms) API for tracking the global coronavirus (COVID-19, SARS-CoV-2) outbreak. It's written in python using the 🔥 FastAPI framework. Supports multiple sources!

Marius 1.6k Jan 04, 2023
Decipher using Markov Chain Monte Carlo

Decipher using Markov Chain Monte Carlo

Science étonnante 43 Dec 24, 2022
A refresher for PowerBI Desktop documents

PowerBI_Refresher-NPP Informació Per executar el programa s'ha de tenir instalat el python versio 3 o mes. Requeriments a requirements.txt. El fitxer

Nil Pujol 1 May 02, 2022
MIXLAB_NASA_TICKET mixlab 灵感来源于NASA的火星船票

MIXLAB_NASA_TICKET mixlab 灵感来源于NASA的火星船票,我们想要使用开源的代码来定制化这一设计。 其中photo_to_cartoon 是paddle的开源代码:https://github.com/minivision-ai/photo2cartoon-paddle 也借

tongji_cy 38 Feb 20, 2022
A collection of some leetcode challenges in python and JavaScript

Python and Javascript Coding Challenges Some leetcode questions I'm currently working on to open up my mind to better ways of problem solving. Impleme

Ted Ngeene 1 Dec 20, 2021
APRS Track Direct is a collection of tools that can be used to run an APRS website

APRS Track Direct APRS Track Direct is a collection of tools that can be used to run an APRS website. You can use data from APRS-IS, CWOP-IS, OGN, HUB

Per Qvarforth 42 Dec 29, 2022
Given an array of integers, calculate the ratios of its elements that are positive, negative, and zero.

Given an array of integers, calculate the ratios of its elements that are positive, negative, and zero. Print the decimal value of each fraction on a new line with places after the decimal.

Shruti Dhave 2 Nov 29, 2021
A python script to make leaderboards using a CSV with the runners name, IDs and Flag Emojis

SrcLbMaker A python script to make speedrun.com global leaderboards. Installation You need python 3.6 or higher. First, go to the folder where you wan

2 Jul 25, 2022
GMHI: Gut Microbiome Health Index

GMHI: Gut Microbiome Health Index Description Gut Microbiome Health Index (GMHI)

Daniel Chang 2 Jun 30, 2022
App to get data from popular polish pages with job offers

Job board parser I written simple app to get me data from popular pages with job offers, because I wanted to knew immidietly if there is some new offe

0 Jan 04, 2022
Python Project Template

A low dependency and really simple to start project template for Python Projects.

Bruno Rocha 651 Dec 29, 2022
A python package that computes an optimal motion plan for approaching a red light

redlight_approach redlight_approach is a Python package that computes an optimal motion plan during traffic light approach. RLA_demo.mov Given the par

Jonathan Roy 4 Oct 27, 2022
python scripts and other files to generate induction encoder PCBs in Kicad

induction_encoder python scripts and other files to generate induction encoder PCBs in Kicad Targeting the Renesas IPS2200 encoder chips.

Taylor Alexander 8 Feb 16, 2022
A framework that let's you compose websites in Python with ease!

Perry Perry = A framework that let's you compose websites in Python with ease! Perry works similar to Qt and Flutter, allowing you to create componen

Linkus 13 Oct 09, 2022
A tool to help plan vacations with friends and family

Vacationer In Development A tool to help plan vacations with friends and family Deployment Requirements: NPM Docker Docker-Compose Deployment Instruct

JK 2 Oct 05, 2021
Flask html response minifier

Flask-HTMLmin Minify flask text/html mime type responses. Just add MINIFY_HTML = True to your deployment config to minify HTML and text responses of y

Hamid Feizabadi 85 Dec 07, 2022
Auto-ropper is a tool that aims to automate the exploitation of ROP.

Auto-ropper is a tool that aims to automate the exploitation of ROP. Its goal is to become a tool that no longer requires user interaction.

Zerotistic 16 Nov 13, 2022
OnTime is a small python that you set a time and on that time, app will send you notification and also play an alarm.

OnTime Always be OnTime! What is OnTime? OnTime is a small python that you set a time and on that time, app will send you notification and also play a

AmirHossein Mohammadi 11 Jan 16, 2022
Load dependent libraries dynamically.

dypend dypend Load dependent libraries dynamically. A few days ago, I encountered many users feedback in an open source project. The Problem is they c

Louis 5 Mar 02, 2022