Skip to main content

Translate Ledger CLI query syntax into BQL

Project description

ledger2bql

A Ledger CLI-like query syntax for Beancount

Implemented in Python.

The package is available at PyPI version.

Introduction

BQL is quite a powerful language for slicing and dicing Beancount data. But, when all you need are simple queries, writing every field and filter seems tedious. In comparison, Ledger CLI's syntax is short and effective. A simple l b bank will list all bank accounts, assuming a well-organized account tree.

The purpose of this project, a simple CLI utility, is to accept a Ledger-like syntax, generate an appropriate BQL statement, and run it for you.

This is very convenient for quick lookups and everyday insights into your financial data.

For more background on Ledger's query syntax, see the docs.

Usage

Install the package:

uv pip install ledger2bql
# or
uv tool install ledger2bql

Set the BEANCOUNT_FILE variable to point to your Beancount ledger file. You can create an .env file, to customize different ledgers for different folders.

For convenience, you can use a l.cmd as a shortcut for ledger2bql. See the actual file in the project root.

The commands support short aliases:

  • bal can be shortened to b
  • reg can be shortened to r
  • assert can be shortened to a

Run

ledger2bql b card
ledger2bql r card -b 2025-08

For a list of available parameters, simply run

ledger2bql
ledger2bql bal --help
ledger2bql reg --help

Unicode UTF-8 Support

If you are using Unicode characters in your Beancount journal, and are running Windows, you may try setting your system locale to Unicode UTF-8, for better experience.

Development

Clone the repository. Add an .env file, specifying the BEANCOUNT_FILE location.

BEANCOUNT_FILE=tests/sample_ledger.bean

Install the dependencies.

uv sync

Build

uv build

Run

uv run ledger2bql

or run

l ...

Contributions are welcome! See CONTRIBUTING.md for more details.

Tests

All tests use the .env file in the tests/ directory to locate the sample ledger file. Make sure this file is properly configured with the correct path to sample_ledger.bean.

Commands

Balance

Running

l b

will output

Your BQL query is:
SELECT account, sum(position) GROUP BY account ORDER BY account ASC

+--------------------------+---------------+
| Account                  |       Balance |
|--------------------------+---------------|
| Assets:Bank:Checking     |  1,900.00 EUR |
| Assets:Cash:Pocket-Money |    -20.00 EUR |
| Equity:Opening-Balances  | -1,000.00 EUR |
| Expenses:Food            |    100.00 EUR |
| Expenses:Sweets          |     20.00 EUR |
| Income:Salary            | -1,000.00 EUR |
+--------------------------+---------------+

To show a grand total row at the end of the balance report, use the --total or -T flag:

# l b --total

Your BQL query is:
SELECT account, sum(position) GROUP BY account ORDER BY account ASC

+--------------------------+---------------+
| Account                  |       Balance |
|--------------------------+---------------|
| Assets:Bank:Checking     |  1,900.00 EUR |
| Assets:Cash:Pocket-Money |    -20.00 EUR |
| Equity:Opening-Balances  | -1,000.00 EUR |
| Expenses:Food            |    100.00 EUR |
| Expenses:Sweets          |     20.00 EUR |
| Income:Salary            | -1,000.00 EUR |
|--------------------------+---------------|
| Total                    |    -15.35 EUR |
+--------------------------+---------------+

To show a hierarchical view with parent accounts showing aggregated balances, use the --hierarchy or -H flag:

# l b --hierarchy

Your BQL query is:
SELECT account, sum(position) GROUP BY account ORDER BY account ASC

+--------------------------+------------------------------------------------+
| Account                  |                                        Balance |
|--------------------------+------------------------------------------------|
| Assets                   | 3,000.00 CHF 1,839.65 EUR -25.00 BAM -7.00 USD |
| Assets:Bank              |                      3,000.00 CHF 1,859.65 EUR |
| Assets:Bank:Bank03581    |                                   3,000.00 CHF |
| Assets:Bank:Checking     |                                   1,359.65 EUR |
| Assets:Bank:Savings      |                                     500.00 EUR |
| Assets:Cash              |                -25.00 BAM -20.00 EUR -7.00 USD |
| Assets:Cash:BAM          |                                     -25.00 BAM |
| Assets:Cash:Pocket-Money |                                     -20.00 EUR |
| Assets:Cash:USD          |                                      -7.00 USD |
| Equity                   |                        -1,000.00 EUR 12.00 ABC |
| Equity:Opening-Balances  |                                  -1,000.00 EUR |
| Equity:Stocks            |                                      12.00 ABC |
| Expenses                 |                  145.00 EUR 25.00 BAM 7.00 USD |
| Expenses:Food            |                           100.00 EUR 25.00 BAM |
| Expenses:Sweets          |                                      20.00 EUR |
| Expenses:Transport       |                                       7.00 USD |
| Expenses:Transport:Bus   |                                      10.00 EUR |
| Expenses:Transport:Train |                                      15.00 EUR |
| Income                   |                    -3,000.00 CHF -1,000.00 EUR |
| Income:Other             |                                  -3,000.00 CHF |
| Income:Salary            |                                  -1,000.00 EUR |
+--------------------------+------------------------------------------------+

To collapse accounts to a specific depth level, use the --depth option:

# l b --depth 2

Your BQL query is:
SELECT account, sum(position) GROUP BY account ORDER BY account ASC

+-------------------------+---------------------------------+
| Account                 |                         Balance |
|-------------------------+---------------------------------|
| Assets:Bank             |       3,000.00 CHF 1,859.65 EUR |
| Assets:Cash             | -25.00 BAM -20.00 EUR -7.00 USD |
| Equity:Opening-Balances |                   -1,000.00 EUR |
| Equity:Stocks           |                       12.00 ABC |
| Expenses:Food           |            100.00 EUR 25.00 BAM |
| Expenses:Sweets         |                       20.00 EUR |
| Expenses:Transport      |              7.00 USD 25.00 EUR |
| Income:Other            |                   -3,000.00 CHF |
| Income:Salary           |                   -1,000.00 EUR |
+-------------------------+---------------------------------+

Register

The register command lists transactions/postings. Running

l r

will output

Your BQL query is:
SELECT date, account, payee, narration, position

+------------+-------------------------+----------------+-------------+---------------+
| Date       | Account                 | Payee          | Narration   |        Amount |
|------------+-------------------------+----------------+-------------+---------------|
| 2025-01-01 | Assets:Bank:Checking    |                | Initial Bal |  1,000.00 EUR |
| 2025-01-01 | Equity:Opening-Balances |                | Initial Bal | -1,000.00 EUR |
| 2025-02-01 | Expenses:Sweets         | Ice Cream Shop | Ice Cream   |     20.00 EUR |
| 2025-02-01 | Assets:Cash:Pocket-Mone | Ice Cream Shop | Ice Cream   |    -20.00 EUR |
| 2025-03-01 | Expenses:Food           | Grocery Store  | Groceries   |    100.00 EUR |
| 2025-03-01 | Assets:Bank:Checking    | Grocery Store  | Groceries   |   -100.00 EUR |
+------------+-------------------------+----------------+-------------+---------------+

To show a running total column in the register report, use the --total or -T flag:

# l r --total

Your BQL query is:
SELECT date, account, payee, narration, position

+------------+--------------------------+----------------+------------------+---------------+-----------------+
| Date       | Account                  | Payee          | Narration        |        Amount |   Running Total |
|------------+--------------------------+----------------+------------------+---------------+-----------------|
| 2025-01-01 | Assets:Bank:Checking     |                | Initial Balance  |  1,000.00 EUR |    1,000.00 EUR |
| 2025-01-01 | Equity:Opening-Balances  |                | Initial Balance  | -1,000.00 EUR |        0.00 EUR |
| 2025-02-01 | Expenses:Sweets          | Ice Cream Shop | Ice Cream        |     20.00 EUR |       20.00 EUR |
| 2025-02-01 | Assets:Cash:Pocket-Money | Ice Cream Shop | Ice Cream        |    -20.00 EUR |        0.00 EUR |
| 2025-03-01 | Expenses:Food            | Grocery Store  | Groceries        |    100.00 EUR |      100.00 EUR |
| 2025-03-01 | Assets:Bank:Checking     | Grocery Store  | Groceries        |   -100.00 EUR |        0.00 EUR |
+------------+--------------------------+----------------+------------------+---------------+-----------------+

Query

The query command allows you to execute named queries defined in your Beancount file. These queries can be defined using the query directive in your Beancount file:

2025-09-02 query "holidays" "select * where payee ~ 'holiday' and account ~ 'expenses'"

To execute a named query, use the query command (or its short alias q):

l q holidays
# or
l query holidays

When using a partial name match, the system will display which actual query is being executed:

l q holi
# Will show: "Running query: holidays" and then execute the "holidays" query

This is useful when you have long query names but want to use shorter aliases to execute them.

Assert

The assert command shows balance assertions from your Beancount file. Balance assertions are statements that confirm the expected balance of an account on a specific date, helping you verify the accuracy of your financial records.

Running

l a

will output

Your BQL query is:
SELECT date, account, amount FROM #balances

+------------+--------------------------+--------------+
| Date       | Account                  |      Balance |
|------------+--------------------------+--------------|
| 2025-11-07 | Assets:Bank:Checking     |   595.47 EUR |
| 2025-11-08 | Assets:Bank:Savings      | 5,775.09 EUR |
| 2025-11-07 | Assets:Cash:Pocket-Money |     0.00 EUR |
+------------+--------------------------+--------------+

Filtering Assertions

The assert command supports the same filtering options as other commands:

# Filter by account name
l a Assets:Bank

# Filter by date range
l a --begin 2025-11-01

# Filter by amount
l a --amount >500
l a -a >500  # shorthand

# Filter by currency
l a --currency EUR
l a -c EUR  # shorthand

# Combine multiple filters
l a Assets:Bank --amount >500 --currency EUR
l a Assets:Bank -a >500 -c EUR  # using shorthands

Sorting and Limiting

You can sort and limit the results:

# Sort by account name
l a --sort account

# Sort by date (descending)
l a --sort -date

# Limit to 10 results
l a --limit 10

Excluding Accounts

Use the not keyword to exclude certain accounts:

# Show all assertions except cash accounts
l a not Assets:Cash

# Show bank assertions excluding savings
l a Assets:Bank not Savings

Advanced Account Filtering

The assert command supports the same advanced account filtering syntax:

# Accounts starting with Assets:Bank
l a ^Assets:Bank

# Accounts ending with Checking
l a Checking$

# Exact account match
l a ^Assets:Bank:Checking$

Lots

The lots command lists investment lots with their purchase details. This is particularly useful for tracking investments and their cost basis.

Running

l l

will output

Your BQL query is:
SELECT date, account, currency(units(position)) as symbol, units(position) as quantity, cost_number as cost, cost_currency WHERE cost_number IS NOT NULL ORDER BY date ASC

+------------+---------------+------------+----------+----------+
| Date       | Account       |   Quantity | Symbol   |     Cost |
|------------+---------------+------------+----------+----------|
| 2025-04-01 | Equity:Stocks |          5 | ABC      | 1.25 EUR |
| 2025-04-02 | Equity:Stocks |          7 | ABC      | 1.30 EUR |
+------------+---------------+------------+----------+----------+

Sorting Options

You can sort the lots by different criteria:

  • --sort-by date - Sort by transaction date (default)
  • --sort-by price - Sort by purchase price
  • --sort-by symbol - Sort by commodity symbol
# Sort by purchase price
l l --sort-by price

# Sort by symbol
l l --sort-by symbol

Average Cost

To show average costs for each symbol, use the --average or -A flag:

l l --average

Filtering

Like other commands, you can filter lots by account name:

# Show lots only from Equity accounts
l l Equity

# Show lots from specific accounts
l l Stocks

Active vs All Lots

By default, the lots command shows only active/open lots (those with positive quantities after all transactions):

# Show only active lots (default behavior)
l l

To show all individual lot transactions including both buys and sells, use the --all flag:

# Show all lots including buys and sells
l l --all

To explicitly show only active lots, use the --active flag:

# Explicitly show only active lots (same as default)
l l --active

Automatic Paging

By default, ledger2bql uses your system's pager (like less on Unix systems or more on Windows) to display output. This is especially useful when viewing large reports with many transactions.

To disable automatic paging and display all output directly in the terminal, use the --no-pager flag:

l b --no-pager

Filter Syntax

The filters have initially matched the Ledger CLI syntax but some have been adjusted for convenience.

They can be combined, providing powerful filtering capabilities. I.e. to list all transactions in BAM, in the specified date period, for accounts containing "exp", to a payee/narration containing "super":

l r -c bam -d 2025-01-05..2025-01-17 exp @super

Account

To narrow-down to certain accounts only, simply write a part of the account name.

l r exp

outputs

Your BQL query is:
SELECT date, account, payee, narration, position WHERE account ~ 'exp' ORDER BY date, account

+------------+-----------------+----------------+-------------+------------+
| Date       | Account         | Payee          | Narration   |     Amount |
|------------+-----------------+----------------+-------------+------------|
| 2025-02-01 | Expenses:Sweets | Ice Cream Shop | Ice Cream   |  20.00 EUR |
| 2025-03-01 | Expenses:Food   | Grocery Store  | Groceries   | 100.00 EUR |
+------------+-----------------+----------------+-------------+------------+

Excluding Accounts with "not"

To exclude certain accounts from the results, use the not keyword followed by account patterns:

l b not bank

This will show all accounts except those matching "bank". You can also exclude multiple patterns:

l r not bank cash

This will show all transactions except those involving bank or cash accounts.

You can combine inclusion and exclusion filters:

l b assets not bank

This will show only asset accounts that don't match "bank".

Advanced Account Filtering

You can use special syntax to match accounts more precisely:

  • ^pattern - Matches accounts that start with the specified pattern

    l b ^Assets:Bank
    

    This will show all accounts that start with "Assets:Bank", such as "Assets:Bank:Checking" and "Assets:Bank:Savings".

  • pattern$ - Matches accounts that end with the specified pattern

    l b Checking$
    

    This will show all accounts that end with "Checking", regardless of what comes before it.

  • ^pattern$ - Matches accounts that exactly match the specified pattern

    l b ^Assets:Bank:Checking$
    

    This will show only the exact account "Assets:Bank:Checking", excluding any other accounts.

These patterns can be combined with the not keyword for exclusion filtering as well.

Amount

Filtering by amount is done via the -a or --amount parameter. This allows you to filter transactions based on their amount values.

The amount filter supports the following operators:

  • > - Greater than
  • < - Less than
  • >= - Greater than or equal to
  • <= - Less than or equal to
  • = - Equal to (default if no operator is specified)

You can also specify a currency code after the amount to filter by both amount and currency.

# Show transactions with amounts greater than 50 (in any currency)
l r -a ">50"

# Show transactions with amounts greater than 50 EUR
l r -a ">50EUR"

# Show transactions with amounts less than or equal to 100
l r -a "<=100"

# Show transactions with amounts equal to 25 BAM
l r -a "=25BAM"
l r -a "25BAM"  # equivalent to above (default is =)

Note: The amount filter values must be quoted to prevent shell interpretation of the operators.

Description / Payee

Similar to Ledger's Payee spec, @some_store, the @ syntax is available. For Beancount, however, it is more useful to search through the Description, which is a combination of Payee and Narration fields.

D:\src\ledger2bql>l b @ice

Your BQL query is:
SELECT account, units(sum(position)) as Balance WHERE description ~ 'ice' ORDER BY account ASC

+--------------------------+------------+
| Account                  |    Balance |
|--------------------------+------------|
| Assets:Cash:Pocket-Money | -20.00 EUR |
| Expenses:Sweets          |  20.00 EUR |
+--------------------------+------------+

Date Range

A new, date range, syntax has been introduced. Instead of using -b 2025 -e 2025-06, you can simply write -d 2025..2025-06.

D:\src\ledger2bql>l r -d 2025-01

Your BQL query is:
SELECT date, account, payee, narration, position WHERE date >= date("2025-01-01") AND date < date("2025-02-01")

+------------+-------------------------+---------+-----------------+---------------+
| Date       | Account                 | Payee   | Narration       |        Amount |
|------------+-------------------------+---------+-----------------+---------------|
| 2025-01-01 | Assets:Bank:Checking    |         | Initial Balance |  1,000.00 EUR |
| 2025-01-01 | Equity:Opening-Balances |         | Initial Balance | -1,000.00 EUR |
+------------+-------------------------+---------+-----------------+---------------+

The date range can be used either with both or with just a beginning/ending value. Specifying just a value, without the .. operator, will use it as a range, as well.

l b -d 2025-01-07..2025-02-15
l r -d 2025-03..
l r -d ..2025-03
l r -d 2025-03-01 # => 2025-03-01..2025-03-02
l r -d 2025-02    # => 2025-02-01..2025-03-01
l r -d 2025       # => 2025-01-01..2026-01-01

Currency

Filtering by currency is done via -c parameter. The currency spec is case-insensitive. Multiple currencies can be specified, separated by comma, without spaces.

D:\src\ledger2bql>l r -c abc,bam

Your BQL query is:
SELECT date, account, payee, narration, position WHERE currency IN ('ABC', 'BAM')

+------------+-----------------+-------------+-----------------+------------+
| Date       | Account         | Payee       | Narration       |     Amount |
|------------+-----------------+-------------+-----------------+------------|
| 2025-04-01 | Equity:Stocks   |             | Buy Stocks      |   5.00 ABC |
| 2025-04-02 | Equity:Stocks   |             | Buy more stocks |   7.00 ABC |
| 2025-05-01 | Expenses:Food   | Supermarket | drinks          |  25.00 BAM |
| 2025-05-01 | Assets:Cash:BAM | Supermarket | drinks          | -25.00 BAM |
+------------+-----------------+-------------+-----------------+------------+

Exchange

Converting all amounts to a specified currency is done via the -X or --exchange parameter. This works similarly to Ledger CLI's -X option, converting all transaction amounts and running totals to the target currency using Beancount's price database.

The currency code is case-insensitive and will be automatically converted to uppercase. When a conversion rate is not available for a particular currency pair, the converted amount will show as 0 in the target currency.

# Convert all amounts to USD
D:\src\ledger2bql>l r -X USD

Your BQL query is:
SELECT date, account, payee, narration, position, convert(position, 'USD') as converted_position

+------------+--------------------------+----------------+------------------+---------------+----------------+
| Date       | Account                  | Payee          | Narration        |        Amount |   Amount (USD) |
|------------+--------------------------+----------------+------------------+---------------+----------------|
| 2025-01-01 | Assets:Bank:Checking     |                | Initial Balance  |  1,000.00 EUR |   1,132.50 USD |
| 2025-01-01 | Equity:Opening-Balances  |                | Initial Balance  | -1,000.00 EUR |  -1,132.50 USD |
| 2025-05-01 | Expenses:Food            | Supermarket    | drinks           |     25.00 BAM |      25.00 BAM |  # No conversion rate available
| 2025-06-04 | Expenses:Transport       | Metro          | public transport |      7.00 USD |       7.00 USD |
+------------+--------------------------+----------------+------------------+---------------+----------------+

# Convert amounts and show running totals in both original and target currencies
D:\src\ledger2bql>l r -X usd -T  # lowercase currency is automatically converted to uppercase

Your BQL query is:
SELECT date, account, payee, narration, position, convert(position, 'USD') as converted_position

+------------+--------------------------+----------------+------------------+---------------+----------------+-----------------+---------------+
| Date       | Account                  | Payee          | Narration        |        Amount |   Amount (USD) |   Running Total |   Total (USD) |
|------------+--------------------------+----------------+------------------+---------------+----------------+-----------------+---------------|
| 2025-01-01 | Assets:Bank:Checking     |                | Initial Balance  |  1,000.00 EUR |   1,132.50 USD |    1,000.00 EUR |  1,132.50 USD |
| 2025-01-01 | Equity:Opening-Balances  |                | Initial Balance  | -1,000.00 EUR |  -1,132.50 USD |        0.00 EUR |      0.00 USD |
| 2025-05-01 | Expenses:Food            | Supermarket    | drinks           |     25.00 BAM |      25.00 BAM |       25.00 BAM |      0.00 USD |  # BAM not converted, USD total unchanged
| 2025-06-04 | Expenses:Transport       | Metro          | public transport |      7.00 USD |       7.00 USD |        7.00 USD |      7.00 USD |  # USD transaction updates USD total
+------------+--------------------------+----------------+------------------+---------------+----------------+-----------------+---------------+

For balance reports, the converted totals are shown in the "Total (USD)" column:

D:\src\ledger2bql>l b -X usd

Your BQL query is:
SELECT account, units(sum(position)) as Balance, convert(sum(position), 'USD') as Converted ORDER BY account ASC

+--------------------------+----------------------+---------------+
| Account                  |              Balance |   Total (USD) |
|--------------------------+----------------------+---------------|
| Assets:Bank:Checking     |         1,884.65 EUR |  2,134.37 USD |
| Assets:Cash:BAM          |           -25.00 BAM |      0.00 USD |
| Assets:Cash:Pocket-Money |           -20.00 EUR |    -22.65 USD |
| Assets:Cash:USD          |            -7.00 USD |     -7.00 USD |
| Equity:Opening-Balances  |        -1,000.00 EUR | -1,132.50 USD |
| Equity:Stocks            |            12.00 ABC |      0.00 USD |
| Expenses:Food            | 100.00 EUR 25.00 BAM |    113.25 USD |
| Expenses:Sweets          |            20.00 EUR |     22.65 USD |
| Expenses:Transport       |             7.00 USD |      7.00 USD |
| Income:Salary            |        -1,000.00 EUR | -1,132.50 USD |
+--------------------------+----------------------+---------------+

License

See the license file.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

ledger2bql-1.16.1.tar.gz (37.7 kB view details)

Uploaded Source

Built Distribution

If you're not sure about the file name format, learn more about wheel file names.

ledger2bql-1.16.1-py3-none-any.whl (41.0 kB view details)

Uploaded Python 3

File details

Details for the file ledger2bql-1.16.1.tar.gz.

File metadata

  • Download URL: ledger2bql-1.16.1.tar.gz
  • Upload date:
  • Size: 37.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: uv/0.9.21 {"installer":{"name":"uv","version":"0.9.21","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":null,"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}

File hashes

Hashes for ledger2bql-1.16.1.tar.gz
Algorithm Hash digest
SHA256 e9ddf7079caf2c2934e9e6c89fc52a6644012873c25ca7ebea7be0eef5842086
MD5 769ccbd837274d6d4722b671f55399fb
BLAKE2b-256 eaa3609d40758117ae976fd7b529252e52008ff50f31b76f8a368c974f6093b7

See more details on using hashes here.

File details

Details for the file ledger2bql-1.16.1-py3-none-any.whl.

File metadata

  • Download URL: ledger2bql-1.16.1-py3-none-any.whl
  • Upload date:
  • Size: 41.0 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: uv/0.9.21 {"installer":{"name":"uv","version":"0.9.21","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":null,"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}

File hashes

Hashes for ledger2bql-1.16.1-py3-none-any.whl
Algorithm Hash digest
SHA256 08a5247eda3efd795ac6791cfb0c5d31da1059789ba1827a0cd7fb2ad7cebdeb
MD5 b9c5046c746078b28b8a97208aea5018
BLAKE2b-256 7e8eafdcd353029656b6a19972ce5ba3f05a0719aa2d7e84f2cb7a49a03227c1

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Depot Continuous Integration Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page