Beancount Query Language - SQL-like Financial Queries
Beancount features a powerful, SQL-like Query Language (BQL) that allows you to slice, dice, and analyze your financial data with precision. Whether you want to generate a quick report, debug an entry, or perform complex analysis, mastering BQL is key to unlocking the full potential of your plaintext accounting ledger. This guide will walk you through its structure, functions, and best practices. 🔍
Query Structure and Execution
The core of BQL is its familiar, SQL-inspired syntax. Queries are executed using the bean-query
command-line tool, which processes your ledger file and returns the results directly in your terminal.
Basic Query Format
A BQL query is composed of three main clauses: SELECT, FROM, and WHERE.
SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
SELECT
: Specifies which columns of data you want to retrieve.FROM
: Filters entire transactions before they are processed.WHERE
: Filters the individual posting lines after the transaction has been selected.
Two-Level Filtering System
Understanding the difference between the FROM
and WHERE
clauses is crucial for writing accurate queries. BQL uses a two-level filtering process.
-
Transaction Level (
FROM
) This clause acts on entire transactions. If a transaction matches theFROM
condition, the entire transaction (including all its postings) is passed to the next stage. This is the primary way to filter data, as it preserves the integrity of the double-entry accounting system. For example, filteringFROM year = 2024
selects all transactions that occurred in 2024. -
Posting Level (
WHERE
) This clause filters the individual postings within the transactions selected by theFROM
clause. This is useful for presentation and for focusing on specific legs of a transaction. However, be aware that filtering at this level can "break" the integrity of a transaction in the output, as you might only see one side of an entry. For example, you could select all postings to yourExpenses:Groceries
account.
Data Model
To query your data effectively, you need to understand how Beancount structures it. A ledger is a list of directives, but BQL primarily focuses on Transaction
entries.
Transaction Structure
Each Transaction
is a container with top-level attributes and a list of Posting
objects.
Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
├── account
├── units
├── cost
├── price
└── metadata
Available Column Types
You can SELECT
any of the attributes from the transaction or its postings.
-
Transaction Attributes These columns are the same for every posting within a single transaction.
SELECT
date, -- The date of the transaction (datetime.date)
year, -- The year of the transaction (int)
month, -- The month of the transaction (int)
day, -- The day of the transaction (int)
flag, -- The transaction flag, e.g., "*" or "!" (str)
payee, -- The payee (str)
narration, -- The description or memo (str)
tags, -- A set of tags, e.g., #trip-2024 (set[str])
links -- A set of links, e.g., ^expense-report (set[str])