BQL Queries
The plugin brings the full power of Beancount Query Language (BQL) directly into Obsidian. You can execute SQL-like queries against your financial data and display the results in your notes.
The plugin supports two distinct modes:
- Code Blocks: For detailed analysis and tables.
- Inline Queries: For embedding live values into your text.
📊 BQL Code Blocks​
Use standard Markdown code blocks with the bql language identifier to create formatted, interactive tables.
Single-Line Queries Only: BQL queries must be written on a single line. Multi-line queries will only execute the first line and ignore subsequent lines.
Basic Usage​
```bql
SELECT account, sum(position) WHERE account ~ '^Expenses' GROUP BY account
```
Features​
- Interactive Results: Sortable columns and responsive layout.
- Tools:
- Refresh (⟳): Re-run the query to get the latest data.
- Copy (📋): Copy the raw CSV results to your clipboard.
- Export (📤): Download results as a CSV file.
- Toggle View: Hide the query code to show only the results table (configurable in Settings).
Common Examples​
List All Accounts:
SELECT account GROUP BY account ORDER BY account
Recent Transactions:
SELECT date, payee, narration, position ORDER BY date DESC LIMIT 20
Monthly Expenses:
SELECT year, month, sum(position) WHERE account ~ '^Expenses' GROUP BY year, month ORDER BY year DESC, month DESC
Current Account Balances:
SELECT account, sum(position) WHERE account ~ '^Assets' GROUP BY account ORDER BY account
Top Expenses by Category:
SELECT account, sum(position) WHERE account ~ '^Expenses' GROUP BY account ORDER BY sum(position) DESC LIMIT 10
📈 Advanced Query Examples​
Investment Tracking​
Current Stock Holdings:
SELECT account, currency, units(sum(position)) WHERE account ~ '^Assets:Brokerage' GROUP BY account, currency
Investment Performance (Market Value):
SELECT account, cost(sum(position)), convert(sum(position), 'USD') WHERE account ~ '^Assets:Brokerage' GROUP BY account
Capital Gains Summary:
SELECT year, sum(position) WHERE account ~ '^Income:CapitalGains' GROUP BY year ORDER BY year DESC
Dividend Income:
SELECT date, payee, position WHERE account ~ '^Income:Dividends' ORDER BY date DESC
Budget Analysis​
Year-over-Year Expense Comparison:
SELECT year, month, convert(sum(position), 'USD') WHERE account ~ '^Expenses' GROUP BY year, month ORDER BY year, month
Spending by Merchant:
SELECT payee, sum(position) WHERE account ~ '^Expenses' GROUP BY payee ORDER BY sum(position) DESC LIMIT 20
Tag-Based Expense Tracking:
SELECT date, payee, narration, position WHERE 'vacation' IN tags ORDER BY date DESC
Account Analysis​
Net Worth Trend:
SELECT year, month, convert(sum(position), 'USD') WHERE account ~ '^(Assets|Liabilities)' GROUP BY year, month ORDER BY year, month
Savings Rate Calculation:
SELECT month, (SELECT convert(sum(position), 'USD') WHERE account ~ '^Income' AND month = parent.month) AS income, (SELECT convert(sum(position), 'USD') WHERE account ~ '^Expenses' AND month = parent.month) AS expenses WHERE year = 2023 GROUP BY month
Credit Card Balance History:
SELECT date, balance WHERE account = 'Liabilities:CreditCard' ORDER BY date DESC LIMIT 50
Multi-Currency​
Non-USD Holdings:
SELECT account, currency, sum(position) WHERE NOT currency = 'USD' AND account ~ '^Assets' GROUP BY account, currency
Currency Conversion:
SELECT account, units(sum(position)), convert(sum(position), 'USD') WHERE account ~ '^Assets' GROUP BY account
Date Range Queries​
This Month's Transactions:
SELECT date, payee, narration, position WHERE date >= 2026-02-01 AND date <= 2026-02-29 ORDER BY date DESC
Quarter Performance:
SELECT account, sum(position) WHERE account ~ '^Income' AND date >= 2026-01-01 AND date <= 2026-03-31 GROUP BY account
Last 90 Days Activity:
SELECT account, sum(position) WHERE account ~ '^Expenses' AND date >= today() - 90 GROUP BY account ORDER BY sum(position) DESC
💰 Inline BQL Queries​
Embed live financial values directly in your sentences using single backticks. This is perfect for daily journaling (e.g., "My checking balance is currently...").
Direct Queries​
Write a full BQL query inside backticks starting with bql::
My net worth is
bql:SELECT convert(sum(position), 'USD') WHERE account ~ '^Assets'
Shorthand Templates​
For complex or frequently used queries, use the Shorthand System.
1. Setup Template​
Create a note (e.g., BQL_Shortcuts.md) and define queries like this:
## WORTH: Net Worth
```bql-shorthand
SELECT convert(sum(position), 'USD') WHERE account ~ '^(Assets|Liabilities)'
```
2. Configure​
Go to Settings and point "Shortcuts template file" to BQL_Shortcuts.md.
3. Use​
Now you can simply write:
My net worth is
bql-sh:WORTH