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 pdfplumber

# 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

Importers

All the importers are maintained in a repository↗ .

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

Reports for Filing Indian Income Tax

I use the following queries to generate reports to file Indian Income tax returns.

Append country names to get numbers filtered by country.

To generate summary number for entire year

SELECT sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-04-01 AND date <= 2026-03-31;
SELECT sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Expenses:.*WithholdingTax' AND date >= 2025-04-01 AND date <= 2026-03-31;

To generate per account summary numbers for entire year

SELECT account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-04-01 AND date <= 2026-03-31 GROUP BY account ORDER BY account;

To generate per account summary numbers for standard accrual periods as per ITR

SELECT 'Upto 15/6' AS period, account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-04-01 AND date <= 2025-06-15 GROUP BY period, account ORDER BY account;
SELECT '16/6 to 15/9' AS period, account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-06-16 AND date <= 2025-09-15 GROUP BY period, account ORDER BY account;
SELECT '16/9 to 15/12' AS period, account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-09-16 AND date <= 2025-12-15 GROUP BY period, account ORDER BY account;
SELECT '16/12 to 15/3' AS period, account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2025-12-16 AND date <= 2026-03-15 GROUP BY period, account ORDER BY account;
SELECT '16/3 to 31/3' AS period, account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Income:.*Dividend' AND date >= 2026-03-16 AND date <= 2026-03-31 GROUP BY period, account ORDER BY account;

To generate detailed i.e per dividend payment report for a period:

SELECT date, account, currency, position, convert(position, 'INR', date_add(date_trunc('month', date), -1)) AS converted WHERE account ~ 'Income:.*Dividend' AND date >= 2025-04-01 AND date <= 2026-03-31 ORDER BY currency, date;
SELECT date, account, currency, position, convert(position, 'INR', date_add(date_trunc('month', date), -1)) AS converted WHERE account ~ 'Expenses:.*WithholdingTax' AND date >= 2025-04-01 AND date <= 2026-03-31 ORDER BY currency, date;

To find Interest withholding tax only:

SELECT account, sum(convert(position, 'INR', date_add(date_trunc('month', date), -1))) AS amount_inr WHERE account ~ 'Expenses:.*:InterestWithheld:' AND date >= 2025-04-01 AND date <= 2026-03-31 GROUP BY account ORDER BY account;

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”

Known issues

Fava “Balance” column issue

Fava’s Journal (register) view for the dividend account tree shows a running “Balance” column that does not match either:

  • The sum of the individual per-transaction amounts displayed on the same page, or
  • The total shown on the Income Statement report, or
  • Any bean-query total computed for the same date range.

Cause: In the Journal view, each transaction line is displayed using its own transaction-date conversion rate, but the running Balance column is a live account-value display. It revalues the cumulative multi-currency balance using the most recent price known at the moment the page was rendered (“today”) — not the price as of the end of the filtered date range, and not tied to the report’s time= filter at all.

Consequence: The Balance figure is a moving target. Reload the same Journal page on a different day and the number can change, even though the underlying transactions haven’t. It reflects “what is this balance worth today,” not “what was this income worth when received” or “what was this income worth as of FY-end.”

Takeaway: Never use the Journal view’s running Balance column for tax reporting. Use the Query page (query) or bean-query directly, where the conversion date is explicit and reproducible. The Income Statement report is fine for accounting/net-worth snapshots but uses a single FY-end mark-to-market rate — also not the right convention for tax filing (see below).

Rule 115 — the Exchange conversion rule

Per Rule 115 of the Income Tax Rules, foreign income must be converted to INR using the SBI Telegraphic Transfer Buying Rate (TTBR) applicable on the last day of the month immediately preceding the month in which the income is declared, distributed, or paid.

Two naive approaches were tried and both were subtly wrong:

Approach1 - Per-transaction “most recent price on/before”

The Query used is “convert(position, ‘INR’, date)”. This fails when a dividend is received on the same day as that month’s own price entry i.e picks the current month’s rate instead of the preceding month’s.

For eg. if dividends were received on 27 Feb 2026 — the same date as the Feb SGD price entry, then it fails the above rule.

SELECT date, account, currency, position, convert(position, 'INR',
date) AS converted WHERE account ~ 'Income:.*Dividend' AND date >=
2025-04-01 AND date <= 2026-03-31 ORDER BY currency, date;

Approach2 - Fixed FY-end rate for everything The query used is “convert(position, ‘INR’, 2026-03-31)” This Matches Fava’s Income Statement, but applies a single year-end mark-to-market rate to the whole year — not a receipt-date-based conversion at all. Wrong convention for Rule 115.

Approach3 - Per-transaction “last day of preceding month”

The query used is (convert(…, date_add(date_trunc(‘month’, date), -1))).

Compute the reference date explicitly per transaction, rather than relying on “most recent available”:

sql

convert(position, 'INR', date_add(date_trunc('month', date), -1))
date_trunc('month', date) → the 1st of the transaction's own month
date_add(..., -1) → steps back one day → the last day of the preceding month

This is computed independently for every row, so it’s correct regardless of which ITR advance-tax period (upto 15/6, 16/6–15/9, etc.) the transaction falls into, and regardless of whether a dividend happens to land exactly on a price-entry date.

Price directives in the ledger are to be based on the published SBI TTBR rates. Rule 115 requires the rate on the last banking day of the preceding month — if a month-end falls on a weekend/bank holiday, the price directive for that month must be dated to the last actual banking day SBI published a rate on, not the literal calendar month-end, to avoid a similar subtle mismatch in future years.

Beancount + Fava optimizations

  • Disable expensive Fava features
    option "fava-journal-show-accounts" "false"
option "fava-show-accounts-with-zero-balance" "false"
  • Reduce price database size by moving old price history to a separate file. Also keep monthly prices instead of daily.
  • Run Fava without auto reload i.e fava –no-reload ledger.bean
  • Add balance assertions both in the beginning and ending of each period.
  • Disable Charts and other settings via Fava Options File ~/.config/fava/options or create fava_options.toml in the same directory as your .beancount file.
    # ===============================================
# Ultra-Minimal, Performance-Optimized Fava Options
# ===============================================

# --- Journal display ---
fava-journal-show-accounts = false   # hide account columns for faster rendering
fava-journal-show-postings = true    # show postings if needed
fava-journal-show-payee = true       # optional: show payee for context
fava-journal-show-tags = false       # hide tags for performance
fava-journal-show-links = false      # hide links to reduce parsing overhead

# --- Charts and graphs ---
show-charts = false                  # disables all charts globally for speed

# --- Defaults ---
default-account = "Assets:Bank"      # default account for reports
default-report = "Balance"           # default report on startup
default-start-date = "2026-01-01"    # optional: start date filter
default-end-date = "2026-12-31"      # optional: end date filter

# --- Caching and performance ---
cache = true                         # enable Fava report caching
cache-dir = "~/.cache/fava"          # optional: specify cache directory
highlighting = false                 # disable syntax highlighting in journal (faster)

# --- Miscellaneous optimizations ---
show-splits = false                   # hide split postings if not needed
show-unrealized = false               # skip unrealized gains/losses for speed
  • If the above doesn’t work, here’s the alternate approach
    2026-01-01 custom "fava-option" "default-page" "balance_sheet/"       # open balance sheet on startup
2026-01-01 custom "fava-option" "default-account" "Assets:Bank"       # default account filter
2026-01-01 custom "fava-option" "start-date" "2026-01-01"             # start date filter
2026-01-01 custom "fava-option" "end-date" "2026-12-31"               # end date filter

2026-01-01 custom "fava-option" "show-closed-accounts" "false"        # hide closed accounts
2026-01-01 custom "fava-option" "sidebar-show-queries" "5"           # reduce sidebar load
2026-01-01 custom "fava-option" "sidebar-show-accounts" "true"        # show only active accounts

2026-01-01 custom "fava-option" "show-charts" "false"                 # disable all charts/graphs
2026-01-01 custom "fava-option" "show-splits" "false"                 # hide split postings if not needed
2026-01-01 custom "fava-option" "show-unrealized" "false"             # skip unrealized gains/losses
  • Split transactions into yearly files. Move old years to archive and include only recent years or only current period in the main file.
    ledger/
    ├── main.bean
    ├── accounts.bean
    ├── commodities.bean
    ├── prices.bean
    │
    ├── transactions/
    │   ├── 2026.bean
    │   ├── 2025.bean
    │   └── 2024.bean
    │
    └── archive/
        ├── 2023.bean
        ├── 2022.bean
        └── older.bean

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

  • period). In the “Replace” box, type “&” (without the quotes, of

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