Beancount

Beancount is a open source plain text accounting software written in python . It makes use of Org Mode in Emacs and i use it in Linux . I use this to track all my expenses including Farming at Thenkodipakkam and my investments for taxation purposes.

Installation

Installation using pip must be done using venv only. In Alpine Linux ensure that py3-pip is installed as it’s not the default.

Currently the venv is located at /data/myhome/prabu/bean_venv.

$ source /data/myhome/prabu/bean_venv/bin/activate.fish
(bean_venv) prabu@homepc2 ~> pip install fava
(bean_venv) prabu@homepc2 ~> pip install smart_importer

# Soft links for the following binaries needs to be created or $PATH needs to be updated.
lrwxrwxrwx 1 prabu prabu 37 Jul 17 11:13 .local/bin/fava -> /data/myhome/prabu/bean_venv/bin/fava*
lrwxrwxrwx 1 prabu prabu 43 Jul 17 11:14 .local/bin/bean-check -> /data/myhome/prabu/bean_venv/bin/bean-check*
lrwxrwxrwx 1 prabu prabu 43 Jul 17 11:14 .local/bin/bean-query -> /data/myhome/prabu/bean_venv/bin/bean-query*

Source: https://beancount.github.io/docs/installing_beancount.html ↗ ↗

emacs for beancount

In the .emacs.d/init.el file, the following lines were added…

(defconst user-init-dir

(cond ((boundp 'user-emacs-directory)

user-emacs-directory)

((boundp 'user-init-directory)

user-init-directory)

(t "~/.emacs.d/")))

(defun load-user-file (file)

(interactive "f")

"Load a file in current user's configuration directory"

(load-file (expand-file-name file user-init-dir)))

(load-user-file "beancount.el")

(load-user-file "beancountcustom.el")

(load-user-file "org.el")

How to use plugin in beancount

(beancount.plugins.implicit_prices)

You add the following line to your .beancount file to activate the

plugin:

plugin “beancount.plugins.implicit_prices”

That’s it. When you add this plugin, price info via @ and @@ will generate price entries.

Full example. Put this in test.beancount:

;———————

plugin “beancount.plugins.implicit_prices”

2000-01-01 open Assets:Bank

2000-01-01 open Expenses:Snacks

2020-08-02 * “Bought chocolate”

Expenses:Snacks 1.00 EUR @ 0.90 GBP

Assets:Bank -0.90 GBP

;———————

Now run:

bean-report test.beancount print | grep price

This will show:

2020-08-02 price EUR 0.90 GBP

This was generated by the implicit_prices plugin.

Basically the built in plugin isn’t directly editing the beancount file. Beancount plugins modify the entries (in memory) when the file is loaded by beancount. - Martin Michlmayr

Examples for using Bean-query

SELECT <target1>, <target2>, …FROM <entry-filter-expression> WHERE <posting-filter-expression>;

Special columns

“Id” - It is a unique hash that identifies each transaction uniquely

“Balance” - column of the cumulative balance of the selected postings rows which is automatically calculated based on the previous selected rows:

$print from id = ‘447f87a4dae698f0e584000e07428229’;

OUTPUT:

2020-03-26 * “(Dividend) SELECT SECTOR SPDR TRUST SHS BEN INT ENERGY”

Assets:US:Investment:ETrade:Cash 0.57 USD

Income:US:Investment:Dividend:XLE -0.57 USD

Sources: http://aumayr.github.io/beancount-sql-queries/ ↗ ↗

https://ledger-cli.narkive.com/3xjksGud/beancount-example-tutorial ↗ ↗

https://lyz-code.github.io/blue-book/ ↗

Match the monthly statement from your bank

$SELECT date, payee,narration, position, balance from date >= 2019-04-01 AND date <= 2020-03-31 WHERE account ~ “ICICIBank”;

$select date, payee, narration, change, balance FROM date > 2019-04-01 AND date < 2020-03-31 WHERE account ~ “SBI”;

OUTPUT:

datenarrationposition (INR)balance (INR)
2019-04-02BIL/INFT/001676362079/26Feb Loanrepay/4900049000
2019-04-04BIL/ONL/001679123822/Amazon-266046340
2019-04-05Neft-N095190794055126-D18246522

List all dividends and interest in a period

select date, narration,position from date >= 2019-04-01 AND date <= 2020-03-31 where account ~ “Dividend:”;

select date, narration,position from date >= 2019-04-01 AND date <= 2020-03-31 where account ~ “Interest”;

List dividends by month, date

SELECT account, sum(cost(position)) as total, month from year= 2020 WHERE account ~ “Dividend” GROUP BY month, account ORDER BY total, account DESC;

SELECT account, sum(cost(position)) as total, date FROM date > 2019-04-01 AND date < 2020-03-31 WHERE account ~ “Dividend” GROUP BY date, account ORDER BY total, account DESC;

$ bean-query -f csv -m ~/beancount/prabu/prabu.beancount ‘select date, account,narration,position from date >= 2021-04-01 AND date <= 2022-03-31 where account ~ “Dividend”’ > dividends.csv

$ bean-query -f csv -m ~/beancount/prabu/prabu.beancount ‘select date, account,narration,position from date >= 2021-04-01 AND date <= 2022-03-31 where account ~ “WithholdingTax”’>dividends_wh.csv

Journal

JOURNAL “Dividend” AT COST FROM HAS_ACCOUNT(“Income”);

JOURNAL “Invest” AT COST FROM HAS_ACCOUNT(“Assets:US”);

JOURNAL “Expenses” AT COST FROM HAS_ACCOUNT(“Expenses”);

JOURNAL “Dividend” AT COST FROM HAS_ACCOUNT(“Income”) and Date >= 2019-04-01 and Date <=2020-04-01;

JOURNAL “Interest” AT COST FROM date > 2019-04-01 AND date < 2020-03-31;

JOURNAL “Dividend” AT COST FROM date > 2019-04-01 AND date < 2020-03-31;

SELECT account, YEAR(date) AS year, SUM(COST(position)) AS balance WHERE account ~ ‘Dividend’ AND year >= 2012 GROUP BY 1,2 ORDER BY 1,2;

Filtering based on metatags

Meta tags can be used in the bean-query to filter entries.

SELECT date, flag, narration, position,balance where any_meta(“crop”) ~ “casuarina” and account ~ “Seed”

SELECT date, account,narration, position,balance where any_meta(“crop”) ~ “Casua” and account ~ “Exp”

Exporting expenses

Added the following directives to the beancount file to be compliant with google finance

bean-query -f csv -m -o expenses.csv beancount/ledgers/household/kvnagar.beancount “select date,account,payee,narration,position, balance where account ~ ‘Expenses’;”

The exported csv has been uploaded to google drive and is available in accounts->household->beancount folder. A google sheets version is available there along with some basic pivot table output. To be worked upon later:

https://drive.google.com/drive/folders/1Q-eb-2K0y2XunDwggJttsL9FJEB2XChY ↗ ↗

https://docs.google.com/spreadsheets/d/19x_2wJvZbC4UtDoNuCZW8FVs5VuTqaUDnETNO5pRmUw/edit?usp=share_link ↗ ↗

To track payments made by Sudharsan amma at kpm

bean-query -f csv -m kvnagar.beancount “select date,Payee,Narration,number,balance where account ~ ‘Assets:Kpmhouse:Vimala’” > test.csv

To track all expense made at kpm from date or period

bean-query -f csv kvnagar.beancount “select date,Payee,Narration,number,balance where account ~ ' Expenses:Kpmhouse’ and date >= 2022-03-01”

Beancount + Fava optimizations

Obsolete topics

Changing data in Libreoffice

In Libreoffice, when changing the date format to YYYY-MM-DD, an apostrophe will appear before the date and the date will still appear as DD-MM-YYYY.

To solve this, there are two methods:

Solution1: Choose the column. Click on Data->Text to Columns option. Now dates will appear without apostrophes and dates will appear properly.

Solution2: It involves a “find and replace” function. From the “Edit” menu, choose “Find & Replace.” In the “Find” box, type “^.” (i.e., carat

course). But you have to do one more thing for this to work. At the bottom of the “Find & Replace” window you’ll see a button that says “More Options.” Click on it, then select the box next to “Regular Expressions.” You should now have a “Find & Replace” box that looks like this:

Now, hit “Replace All” and all of your apostrophes will disappear. Your series of numbers will now be seen as numbers by your spreadsheet program and will be in cells formatted as number cells. Problem solved.

https://www.ryananddebi.com/2009/11/29/remove-apostrophes-before-numbers-in-openoffice ↗ -

List of pip packages

packages required for fava

(bean_venv) prabu@homepc2 ~> pip list Package Version

anyio 4.12.1 babel 2.18.0 beancount 3.2.0 beangulp 0.2.0 beanquery 0.2.0 beautifulsoup4 4.14.3 blinker 1.9.0 chardet 6.0.0.post1 cheroot 11.1.2 click 8.3.1 fava 1.30.12 Flask 3.1.3 flask-babel 4.0.0 idna 3.11 itsdangerous 2.2.0 jaraco.functools 4.4.0 Jinja2 3.1.6 lxml 6.0.2 markdown2 2.5.4 MarkupSafe 3.0.3 more-itertools 10.8.0 pip 26.0.1 ply 3.11 python-dateutil 2.9.0.post0 python-magic 0.4.27 pytz 2025.2 regex 2026.2.19 simplejson 3.20.2 six 1.17.0 soupsieve 2.8.3 TatSu-LTS 5.16.0 typing_extensions 4.15.0 watchfiles 1.1.1 Werkzeug 3.1.6

packages required for smart_importer

© Prabu Anand K 2020-2026