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 Python script to parse Fortinet products serial numbers, and detect the associated model and version.

ParseFortinetSerialNumber A Python script to parse Fortinet products serial numbers, and detect the associated model and version. Example $ ./ParseFor

Podalirius 10 Oct 28, 2022
Quick script for automatically extracting syscall numbers for an OS

Syscalls-Extractor Quick script for automatically extracting syscall numbers for an OS $ python3 .\syscalls-extractor.py --help usage: syscalls-extrac

m0rv4i 54 Feb 10, 2022
NGEBUG is a tool that sends viruses to victims

Ngebug NGEBUG adalah tools pengirim virus ke korban NGEBUG adalah tools virus terbaru yang berasal dari rusia Informasi lengkap ada didalam tools Run

Profesor Acc 3 Dec 13, 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
Simple tools for the Horse Reality webgame

Realtools (Web Tools for Horse Reality) These tools were made on request from a close friend of mine who plays this game. A live instance can be found

shay 0 Sep 06, 2022
Fetch data from an excel file and create HTML file

excel-to-html Problem Statement! - Fetch data from excel file and create html file Excel.xlsx file contain the information.in multiple rows that is ne

Vivek Kashyap 1 Oct 25, 2021
A ULauncher/Albert extension that supports currency, units and date time conversion, as well as a calculator that supports complex numbers and functions.

Ulauncher/Albert Calculate Anything Ulauncher/Albert Calculate Anything is an extension for Ulauncher and Albert to calculate things like currency, ti

tchar 67 Jan 01, 2023
Old versions of Deadcord that are problematic or used as reference.

⚠️ Unmaintained and broken. We have decided to release the old version of Deadcord before our v1.0 rewrite. (which will be equiped with much more feat

Galaxzy 1 Feb 10, 2022
ARA Records Ansible and makes it easier to understand and troubleshoot.

ARA Records Ansible ARA Records Ansible and makes it easier to understand and troubleshoot. It's another recursive acronym. What it does Simple to ins

Community managed Ansible repositories 1.6k Dec 25, 2022
BMI-Calculator: Program to Calculate Body Mass Index (BMI)

The Body Mass Index (BMI) or Quetelet index is a value derived from the mass (weight) and height of an individual, male or female.

PyLaboratory 0 Feb 07, 2022
Weblate is a copylefted libre software web-based continuous localization system

Weblate is a copylefted libre software web-based continuous localization system, used by over 2500 libre projects and companies in more than 165 count

Weblate 7 Dec 15, 2022
Ked interpreter built with Lex, Yacc and Python

Ked Ked is the first programming language known to hail from The People's Republic of Cork. It was first discovered and partially described by Adam Ly

Eoin O'Brien 1 Feb 08, 2022
A Python application that simulates the rolling of a dice, randomly picking one of the 6 faces and then displaying it.

dice-roller-app This is an application developed in Python that shuffles between the 6 faces of a dice, using buttons to shuffle and close the applica

Paddy Costelloe 0 Jul 20, 2021
Utility to play with ADCS, allows to request tickets and collect information about related objects

certi Utility to play with ADCS, allows to request tickets and collect information about related objects. Basically, it's the impacket copy of Certify

Eloy 185 Dec 29, 2022
A full-featured, hackable tiling window manager written and configured in Python

A full-featured, hackable tiling window manager written and configured in Python Features Simple, small and extensible. It's easy to write your own la

Qtile 3.8k Dec 31, 2022
Learning objective: Use React.js, Axios, and CSS to build a responsive YouTube clone app

Learning objective: Use React.js, Axios, and CSS to build a responsive YouTube clone app to search for YouTube videos, channels, playlists, and live events via wrapper around Google YouTube API.

Dillon 0 May 03, 2022
Button paginator using discord_components

Button Paginator With discord-components Button paginator using discord_components Welcome! It's a paginator for discord-componets! Thanks to the orig

Decave 7 Feb 12, 2022
Runs macOS on linux with qemu.

mac-on-linux-with-qemu Runs macOS on linux with qemu. Pre-requisites qemu-system-x86_64 dmg2img pulseaudio python[click] Usage After cloning the repos

Arindam Das 177 Dec 26, 2022
Python bindings for `ign-msgs` and `ign-transport`

Python Ignition This project aims to provide Python bindings for ignition-msgs and ignition-transport. It is a work in progress... C++ and Python libr

Rhys Mainwaring 3 Nov 08, 2022
The docker-based Open edX distribution designed for peace of mind

Tutor: the docker-based Open edX distribution designed for peace of mind Tutor is a docker-based Open edX distribution, both for production and local

Overhang.IO 696 Dec 31, 2022