Querying Your Ledger
How much did we spend on food in March? What was in the bank at the end of the quarter? Where did the hotel money go? The Query page answers questions like these with a query language compatible with Beancount’s (BQL). This guide shows how to use the page and gives queries to start from. Every clause, table and function is described in Query Language.
The Query page
Section titled “The Query page”Open Query from the More group of the sidebar.
- Type a query in the editor and select Run, or press Ctrl+Enter (Cmd+Enter on macOS).
- The result is a table. A result with a label column and an amount column is also drawn as a chart: a line over time, a bar chart, or a treemap when the labels are accounts. The Table and Chart switch chooses what you see.
- Examples puts a ready-made query in the editor and runs it.
- Saved lists the queries saved in your ledger (see below).
- Reference lists the tables, columns and functions. Select one to insert it into the editor.
- If the query has an error, the editor points at the line and column.
Queries are read-only: they never change your ledger.
Queries to start from
Section titled “Queries to start from”The results below come from this small ledger:
The example ledger
option "title" "Household"option "operating_currency" "CNY"
1970-01-01 commodity CNY
2024-01-01 open Assets:Bank:Checking CNY2024-01-01 open Assets:Cash CNY2024-01-01 open Liabilities:CreditCard CNY2024-01-01 open Income:Salary CNY2024-01-01 open Expenses:Food:Groceries CNY2024-01-01 open Expenses:Food:Restaurants CNY2024-01-01 open Expenses:Transport CNY2024-01-01 open Expenses:Travel CNY2024-01-01 open Equity:Opening-Balances CNY
2024-01-01 balance Assets:Bank:Checking 5000 CNY with pad Equity:Opening-Balances2024-01-01 balance Assets:Cash 300 CNY with pad Equity:Opening-Balances
2024-03-01 * "ACME Corp" "March salary" Assets:Bank:Checking 12000 CNY Income:Salary2024-03-02 * "Supermarket" "Weekly shopping" Liabilities:CreditCard -386.50 CNY Expenses:Food:Groceries2024-03-05 * "Noodle House" "Lunch" #work Liabilities:CreditCard -48 CNY Expenses:Food:Restaurants2024-03-09 * "Hotel" "Two nights in Hangzhou" #trip-hangzhou Liabilities:CreditCard -840 CNY Expenses:Travel2024-03-10 * "Metro" "Top-up" Assets:Cash -100 CNY Expenses:Transport2024-03-16 * "Supermarket" "Weekly shopping" Liabilities:CreditCard -412.30 CNY Expenses:Food:Groceries2024-03-25 * "Bank" "Credit card bill" Assets:Bank:Checking -1686.80 CNY Liabilities:CreditCard2024-04-01 * "ACME Corp" "April salary" Assets:Bank:Checking 12000 CNY Income:Salary2024-04-06 * "Supermarket" "Weekly shopping" Liabilities:CreditCard -295.00 CNY Expenses:Food:GroceriesEach query reads the postings: one row per posting, with the date, payee and narration of its transaction, its account and its position (the amount). ~ matches a regular expression anywhere in a text and ignores case, and ^ anchors it to the start.
Spending by category in a month
Section titled “Spending by category in a month”SELECT account, sum(position) AS spentWHERE account ~ '^Expenses' AND year = 2024 AND month = 3GROUP BY accountORDER BY account| account | spent |
|---|---|
| Expenses:Food:Groceries | 798.80 CNY |
| Expenses:Food:Restaurants | 48 CNY |
| Expenses:Transport | 100 CNY |
| Expenses:Travel | 840 CNY |
The labels are accounts, so the page also draws this as a treemap.
Spending per month
Section titled “Spending per month”SELECT yearmonth(date) AS month, sum(position) AS spentWHERE account ~ '^Expenses'GROUP BY monthORDER BY month| month | spent |
|---|---|
| 2024-03-01 | 1786.80 CNY |
| 2024-04-01 | 295.00 CNY |
yearmonth(date) is the first day of the posting’s month. With dates as labels, the chart is a line over time.
The balance of an account on a date
Section titled “The balance of an account on a date”SELECT account, sum(position) AS balanceWHERE account ~ '^Assets:Bank:Checking' AND date < 2024-04-01GROUP BY account| account | balance |
|---|---|
| Assets:Bank:Checking | 15313.20 CNY |
This adds up every posting before 1 April 2024, so it is the balance at the end of March. For the balances of many accounts at once, BALANCES with CLOSE ON gives the balance sheet at a date:
BALANCES FROM CLOSE ON 2024-04-01 WHERE account ~ '^(Assets|Liabilities)'| account | sum(position) |
|---|---|
| Assets:Bank:Checking | 15313.20 CNY |
| Assets:Cash | 200 CNY |
| Liabilities:CreditCard |
An account whose postings add up to nothing, like the credit card after its bill was paid, shows an empty amount.
Everything bought from one payee
Section titled “Everything bought from one payee”SELECT date, narration, account, positionWHERE payee = 'Supermarket' AND account ~ '^Expenses'ORDER BY date| date | narration | account | position |
|---|---|---|---|
| 2024-03-02 | Weekly shopping | Expenses:Food:Groceries | 386.50 CNY |
| 2024-03-16 | Weekly shopping | Expenses:Food:Groceries | 412.30 CNY |
| 2024-04-06 | Weekly shopping | Expenses:Food:Groceries | 295.00 CNY |
To total it per payee instead, group by payee: SELECT payee, sum(position) WHERE account ~ '^Expenses:Food' GROUP BY payee.
An account’s journal with its running balance
Section titled “An account’s journal with its running balance”JOURNAL 'Liabilities:CreditCard' FROM year = 2024 AND month = 3JOURNAL lists the postings of the accounts matching the pattern, with a running balance in the last column:
| date | payee | narration | position | balance |
|---|---|---|---|---|
| 2024-03-02 | Supermarket | Weekly shopping | -386.50 CNY | -386.50 CNY |
| 2024-03-05 | Noodle House | Lunch | -48 CNY | -434.50 CNY |
| 2024-03-09 | Hotel | Two nights in Hangzhou | -840 CNY | -1274.50 CNY |
| 2024-03-16 | Supermarket | Weekly shopping | -412.30 CNY | -1686.80 CNY |
| 2024-03-25 | Bank | Credit card bill | 1686.80 CNY |
The table leaves out the flag and account columns of the result, and shortens the names of its payee and narration columns, maxwidth(payee, 48) and maxwidth(narration, 80).
Postings with a tag
Section titled “Postings with a tag”SELECT date, payee, account, positionWHERE 'trip-hangzhou' IN tags| date | payee | account | position |
|---|---|---|---|
| 2024-03-09 | Hotel | Liabilities:CreditCard | -840 CNY |
| 2024-03-09 | Hotel | Expenses:Travel | 840 CNY |
Large expenses
Section titled “Large expenses”SELECT date, payee, account, positionWHERE account ~ '^Expenses' AND number > 400ORDER BY date| date | payee | account | position |
|---|---|---|---|
| 2024-03-09 | Hotel | Expenses:Travel | 840 CNY |
| 2024-03-16 | Supermarket | Expenses:Food:Groceries | 412.30 CNY |
number is the number of the posting’s amount, without its commodity.
- Balances and Padding finds failing balance assertions with the
#balancestable. - Lots and Cost Basis values holdings at cost and at market prices.
- The Examples of the reference include an income statement, a balance sheet and a portfolio valuation.
Save a query
Section titled “Save a query”Save the queries you run often in the ledger, with the query directive:
2024-03-31 query "food by payee" "SELECT payee, sum(position) AS spent WHERE account ~ '^Expenses:Food' GROUP BY payee ORDER BY payee"It appears in the Saved menu of the Query page under its name. Choosing it puts the query in the editor and runs it.
Export the result
Section titled “Export the result”Export CSV downloads the result of the query in the editor as query.csv. Each amount column becomes one numeric column per commodity, so the file opens cleanly in a spreadsheet:
account,spent (CNY)Expenses:Food:Groceries,798.80Expenses:Food:Restaurants,48Expenses:Transport,100Expenses:Travel,840Scripts can run queries over HTTP too, with POST /api/query and POST /api/query/csv. See HTTP API.