Query Directive¶

Overview¶

The query directive embeds a named BQL (Beancount Query Language) query within the ledger file. This allows frequently-used queries to be stored alongside the data.

Syntax¶

query = date WHITESPACE "query" WHITESPACE name WHITESPACE query_string
        (NEWLINE metadata)*

name         = string
query_string = string

Components¶

Date¶

The date associated with the query. This can represent: - When the query was created - The default date context for the query - A logical grouping date

Name¶

A string identifier for the query. Used to reference and execute the query.

Query String¶

A BQL query enclosed in quotes.

Examples¶

Basic Query¶

2024-01-01 query "balance" "
  SELECT account, sum(position)
  GROUP BY account
  ORDER BY account
"

Income Statement¶

2024-01-01 query "income-statement" "
  SELECT account, sum(position)
  WHERE account ~ 'Income|Expenses'
  GROUP BY account
  ORDER BY account
"

Monthly Expenses¶

2024-01-01 query "monthly-expenses" "
  SELECT month, sum(position)
  WHERE account ~ 'Expenses'
  GROUP BY month
  ORDER BY month
"

Account Register¶

2024-01-01 query "checking-register" "
  SELECT date, narration, position, balance
  WHERE account = 'Assets:Checking'
  ORDER BY date
"

With Metadata¶

2024-01-01 query "tax-expenses" "
  SELECT account, sum(position)
  WHERE account ~ 'Expenses' AND 'tax-deductible' IN tags
  GROUP BY account
"
  description: "Tax-deductible expenses for filing"
  category: "tax"

Query Execution¶

Embedded queries can be run by name:

# Run a named query
bean-query ledger.beancount --query "balance"

# Run with date filter
bean-query ledger.beancount --query "monthly-expenses" --from 2024-01-01 --to 2024-12-31

Or in interactive mode:

beancount> run balance
beancount> run income-statement

Query Library¶

Organize queries as a library in the ledger:

; ============================================
; QUERY LIBRARY
; ============================================

2024-01-01 query "net-worth" "
  SELECT sum(position)
  WHERE account ~ 'Assets|Liabilities'
"

2024-01-01 query "cash-flow" "
  SELECT month, sum(position)
  WHERE account ~ 'Assets:.*:Checking'
  GROUP BY month
"

2024-01-01 query "spending-by-category" "
  SELECT root(account, 2) AS category, sum(position)
  WHERE account ~ 'Expenses'
  GROUP BY category
  ORDER BY sum(position) DESC
"

2024-01-01 query "investment-performance" "
  SELECT account,
         sum(units(position)) AS units,
         sum(cost(position)) AS cost,
         sum(value(position)) AS value
  WHERE account ~ 'Assets:Investments'
  GROUP BY account
"

Parameterized Queries¶

While BQL doesn't support parameters directly, conventions can be used:

; Use a specific account pattern
2024-01-01 query "account-detail" "
  SELECT date, narration, position, balance
  WHERE account ~ '{ACCOUNT}'
  ORDER BY date
"
  parameter: "ACCOUNT"
  default: "Assets:Checking"

Implementations may support substitution:

bean-query ledger.beancount --query "account-detail" --param ACCOUNT="Liabilities:CreditCard"

Common Queries¶

Balance Sheet¶

2024-01-01 query "balance-sheet" "
  SELECT account, sum(position) AS balance
  WHERE account ~ 'Assets|Liabilities|Equity'
  GROUP BY account
  ORDER BY account
"

Trial Balance¶

2024-01-01 query "trial-balance" "
  SELECT account,
         sum(position) FILTER (WHERE number > 0) AS debits,
         sum(position) FILTER (WHERE number < 0) AS credits
  GROUP BY account
  ORDER BY account
"

Expense Breakdown¶

2024-01-01 query "expense-breakdown" "
  SELECT
    root(account, 2) AS category,
    sum(position) AS total,
    count(*) AS transactions
  WHERE account ~ 'Expenses'
  GROUP BY category
  ORDER BY total DESC
"

Uncleared Transactions¶

2024-01-01 query "uncleared" "
  SELECT date, narration, position
  WHERE flag = '!'
  ORDER BY date
"

Validation¶

Query directives have minimal validation: - Name must be a valid string - Query string must be a valid string

Query syntax errors are detected at execution time, not parse time.

Listing Queries¶

Get all defined queries:

bean-query ledger.beancount --list-queries

Output:

balance              2024-01-01  Balance by account
income-statement     2024-01-01  Income and expenses summary
monthly-expenses     2024-01-01  Expenses by month
checking-register    2024-01-01  Checking account transactions

Use Cases¶

Personal Finance Dashboard¶

2024-01-01 query "dashboard-networth" "SELECT sum(position) WHERE account ~ 'Assets|Liabilities'"
2024-01-01 query "dashboard-monthly-spend" "SELECT sum(position) WHERE account ~ 'Expenses' AND year = 2024 AND month = MONTH(today())"
2024-01-01 query "dashboard-savings-rate" "SELECT 1 - (sum(position) FILTER (WHERE account ~ 'Expenses') / sum(position) FILTER (WHERE account ~ 'Income'))"

Reporting Templates¶

2024-01-01 query "report-annual" "
  SELECT
    root(account, 1) AS type,
    sum(position) AS total
  WHERE year = YEAR(today()) - 1
  GROUP BY type
"
  output-format: "csv"
  schedule: "annually"

Implementation Notes¶

Store queries indexed by name
Parse query string at execution time (not load time)
Date provides context but doesn't filter by default
Support listing all defined queries
Queries don't affect financial calculations