Hey everyone! 
I want to share my journey with Beancount Query Language (BQL) because it completely transformed how I use Beancount - from passive tracking to active financial intelligence.
Where I Started
Two years ago, I was just using bean-report for basic balance checks. I’d look at my net worth, nod approvingly, and move on. But then I discovered BQL, and suddenly I could answer questions like “How much did travel expenses increase year-over-year?” or “Which investment had the best performance attribution?” in seconds.
BQL Basics: The Foundation
BQL has a deceptively simple structure:
SELECT account, SUM(position)
FROM date >= 2025-01-01
WHERE account ~ 'Expenses:Groceries'
The unique part is the two-level filtering:
- FROM clause: Filters entire transactions (preserves double-entry integrity)
- WHERE clause: Filters individual postings within those transactions
This accounting-specific design is what makes BQL powerful - it understands that transactions are atomic units.
Beginner to Intermediate: Query Recipes
Here are the queries that changed my financial life:
1. Multi-Year Spending Trends
SELECT
YEAR(date) as year,
account,
SUM(CONVERT(position, 'USD', date)) as amount
FROM date >= 2022-01-01
WHERE account ~ 'Expenses'
GROUP BY year, account
ORDER BY year, amount DESC
This revealed that my “just a few coffees” habit cost me $2,400 in 2025. Ouch.
2. Monthly Income vs Expenses
SELECT
YEAR(date) as year,
MONTH(date) as month,
SUM(CONVERT(position, 'USD', date)) as amount
WHERE account ~ 'Income' OR account ~ 'Expenses'
GROUP BY year, month
Suddenly every December’s savings dip made sense: holiday expenses + end-of-year splurging.
3. Category Spending Heatmap Data
Export BQL results to a CSV, pull into Pandas, and create a heatmap showing spending patterns by category and month. This is where BQL becomes a data pipeline, not just a reporting tool.
Pro tip: Start simple. Don’t try to build complex queries on day one. Master basic SELECT/FROM/WHERE, then add GROUP BY, then CONVERT for currencies. Build complexity gradually.
Advanced Analytics: Where It Gets Fun
Once you’re comfortable with basic queries, the real power emerges:
Investment Performance Attribution
I use beangrow (the Beancount portfolio returns tool) combined with custom BQL queries to break down performance:
- Capital appreciation vs dividends
- Individual asset returns
- Tax-loss harvesting opportunities
Here’s a query I run monthly to identify underperforming assets:
SELECT
account,
COST(SUM(position)) as cost_basis,
SUM(CONVERT(position, 'USD', date)) as market_value
WHERE account ~ 'Assets:Investments'
GROUP BY account
Then I calculate unrealized gain/loss manually (or in a Jupyter notebook) to spot rebalancing opportunities.
Cash Flow Forecasting
For my FIRE dashboard, I export 3 years of expense data via BQL, feed it into a Python script using Prophet (time-series forecasting), and project future monthly burn rates. This tells me if I’m on track to hit my FI number.
Integration with Pandas
BQL outputs can be exported to CSV or piped directly into Pandas DataFrames:
import subprocess
import pandas as pd
result = subprocess.run(['bean-query', 'ledger.beancount', 'SELECT ...'],
capture_output=True, text=True)
df = pd.read_csv(io.StringIO(result.stdout))
# Now you have full Pandas power for analysis
This unlocks visualization libraries (matplotlib, seaborn, plotly) and statistical analysis beyond what Fava provides.
Common Mistakes I Made (So You Don’t Have To)
- Forgetting the FROM clause filters transactions: If you want to exclude certain postings, use WHERE, not FROM
- Not using CONVERT for multi-currency: Always specify your reporting currency
- Over-engineering early: My first queries were 50-line monstrosities. Simpler is better.
- Ignoring balance assertions: If your queries show weird numbers, check your ledger integrity first
The Community Query Cookbook
Here’s my ask: Let’s build a shared query library. What BQL queries have changed your financial decisions?
- What’s your go-to query for monthly reviews?
- Any creative uses of BQL + external tools?
- What analytics do you wish were easier to generate?
For me, the killer insight was realizing that 40% of my expenses were “lifestyle inflation” since my last promotion. That query motivated me to cut back and increase my savings rate from 25% to 45%.
What’s your BQL story?
Sources for learning BQL:
- Official docs: Beancount Query Language - Beancount Documentation
- SQL queries repo: GitHub - aumayr/beancount-sql-queries: Examples for beancount SQL queries · GitHub
- Fava analytics: Real-Time Financial Analytics