Advanced Investment Tracking with BQL
After 5 years of tracking a diverse investment portfolio in Beancount, I’ve developed a set of BQL queries and workflows that make portfolio analysis, tax optimization, and performance tracking straightforward. Let me share the queries and techniques I use.
Portfolio Structure in Beancount
First, here’s how I structure investment accounts:
; Investment accounts
2020-01-01 open Assets:Investments:Brokerage:Fidelity:AAPL
2020-01-01 open Assets:Investments:Brokerage:Fidelity:GOOGL
2020-01-01 open Assets:Investments:Brokerage:Fidelity:VTSAX
2020-01-01 open Assets:Investments:Retirement:IRA:VTSAX
2020-01-01 open Assets:Investments:Retirement:IRA:BND
; Income from investments
2020-01-01 open Income:Investments:Dividends
2020-01-01 open Income:Investments:Interest
2020-01-01 open Income:Investments:CapitalGains:ShortTerm
2020-01-01 open Income:Investments:CapitalGains:LongTerm
; Commodities
2020-01-01 commodity AAPL
name: "Apple Inc."
asset-class: "Stock"
2020-01-01 commodity VTSAX
name: "Vanguard Total Stock Market Index"
asset-class: "Stock"
asset-subclass: "Index Fund"
Recording Investment Transactions
Buying Stock
; Purchase with explicit lot tracking
2024-01-15 * "Buy AAPL"
Assets:Investments:Brokerage:Fidelity:AAPL 10 AAPL {150.00 USD}
Assets:Investments:Brokerage:Fidelity:Cash -1500.00 USD
; Another purchase at different price (different lot)
2024-03-20 * "Buy more AAPL"
Assets:Investments:Brokerage:Fidelity:AAPL 15 AAPL {165.00 USD}
Assets:Investments:Brokerage:Fidelity:Cash -2475.00 USD
Selling Stock with Gains Calculation
; Sell specific lot (FIFO, LIFO, or specific identification)
2024-06-10 * "Sell AAPL (tax-loss harvest)"
Assets:Investments:Brokerage:Fidelity:AAPL -10 AAPL {150.00 USD} @ 180.00 USD
Assets:Investments:Brokerage:Fidelity:Cash 1800.00 USD
Income:Investments:CapitalGains:LongTerm -300.00 USD
; Note:
; - {150.00 USD} = cost basis (what I paid)
; - @ 180.00 USD = sale price
; - Gain = (180 - 150) * 10 = $300
; - Long-term because held > 365 days
Recording Dividends
2024-03-15 * "VTSAX dividend"
Assets:Investments:Brokerage:Fidelity:Cash 45.67 USD
Income:Investments:Dividends -45.67 USD
; With dividend reinvestment
2024-06-15 * "AAPL dividend (reinvested)"
Assets:Investments:Brokerage:Fidelity:AAPL 0.5 AAPL {180.00 USD}
Income:Investments:Dividends -90.00 USD
Essential BQL Queries for Investments
Query 1: Current Holdings Report
SELECT
account,
sum(units(position)) as shares,
sum(cost(position)) as cost_basis,
sum(convert(position, 'USD')) as market_value,
sum(convert(position, 'USD')) - sum(cost(position)) as unrealized_gain
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
GROUP BY account
ORDER BY market_value DESC
Output:
account shares cost_basis market_value unrealized_gain
Assets:Investments:Brokerage:AAPL 25 AAPL 3975.00 USD 4500.00 USD 525.00 USD
Assets:Investments:Brokerage:GOOGL 10 GOOGL 1400.00 USD 1650.00 USD 250.00 USD
Assets:Investments:Brokerage:VTSAX 100 VTSAX 9500.00 USD 11200.00 USD 1700.00 USD
Query 2: Realized Gains by Year
SELECT
YEAR(date) as year,
account,
sum(position) as total_gains
WHERE account ~ 'Income:Investments:CapitalGains'
GROUP BY year, account
ORDER BY year DESC
This shows your taxable gains by year and type (short-term vs long-term).
Query 3: Investment Performance (ROI)
-- Total invested (cost basis)
SELECT sum(cost(position)) as total_invested
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
-- Current value
SELECT sum(convert(position, 'USD')) as current_value
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
-- Then calculate: ROI = (current_value - total_invested) / total_invested
For automated ROI calculation, use a Python script:
# investment_roi.py
from beancount import loader
from beancount.query import query_execute
from decimal import Decimal
entries, errors, options = loader.load_file('main.beancount')
# Get cost basis
cost_query = """
SELECT sum(cost(position)) as total
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
"""
rtypes, rows = query_execute.execute_query(entries, options, cost_query)
total_invested = rows[0].total.get_only_position().units.number
# Get current value
value_query = """
SELECT sum(convert(position, 'USD')) as total
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
"""
rtypes, rows = query_execute.execute_query(entries, options, value_query)
current_value = rows[0].total.get_only_position().units.number
# Calculate
roi = ((current_value - total_invested) / total_invested) * 100
print(f"Total Invested: ${total_invested:,.2f}")
print(f"Current Value: ${current_value:,.2f}")
print(f"Unrealized Gain: ${current_value - total_invested:,.2f}")
print(f"ROI: {roi:.2f}%")
Query 4: Dividend Income Analysis
SELECT
YEAR(date) as year,
MONTH(date) as month,
sum(position) as dividend_income
WHERE account ~ 'Income:Investments:Dividends'
GROUP BY year, month
ORDER BY year DESC, month DESC
LIMIT 12
Shows monthly dividend income trend.
Query 5: Cost Basis by Lot (Tax Planning)
SELECT
account,
currency,
units(position) as shares,
cost(position) as cost_basis,
convert(position, 'USD') as market_value,
cost(position) / units(position) as cost_per_share,
convert(position, 'USD') / units(position) as current_price,
(convert(position, 'USD') - cost(position)) as gain_loss
WHERE account ~ 'Assets:Investments:Brokerage'
AND currency != 'USD'
This is critical for tax-loss harvesting! Shows each lot’s unrealized gain/loss.
Tax Optimization Strategies
Strategy 1: Tax-Loss Harvesting
Goal: Sell losing positions to offset capital gains.
BQL Query to find losses:
SELECT
account,
currency,
units(position) as shares,
cost(position) as cost_basis,
convert(position, 'USD') as market_value,
(convert(position, 'USD') - cost(position)) as unrealized_loss
WHERE account ~ 'Assets:Investments:Brokerage'
AND currency != 'USD'
AND (convert(position, 'USD') - cost(position)) < 0
ORDER BY unrealized_loss ASC
This shows all positions with unrealized losses, sorted by largest loss first.
Workflow:
- Run query in November/December
- Identify losses to harvest
- Sell losing positions
- Wait 31 days (avoid wash sale rule)
- Rebuy or buy similar (but not identical) security
Strategy 2: Lot Selection for Optimal Gains
When selling, choose which lot minimizes taxes:
; Example: I have 3 lots of AAPL
; Lot 1: 10 shares @ $150 (held 2 years) = Long-term
; Lot 2: 15 shares @ $165 (held 6 months) = Short-term
; Lot 3: 20 shares @ $180 (held 1 month) = Short-term
; Current price: $170
; Selling Lot 1: Gain = $200 (long-term, taxed at 15%)
; Selling Lot 2: Loss = -$75 (short-term, offsets ordinary income)
; Selling Lot 3: Loss = -$200 (short-term, offsets ordinary income)
; Best choice for tax: Sell Lot 3 (harvest loss)
2024-12-15 * "Sell AAPL - tax loss harvest"
Assets:Investments:Brokerage:AAPL -20 AAPL {180.00 USD} @ 170.00 USD
Assets:Investments:Brokerage:Cash 3400.00 USD
Income:Investments:CapitalGains:ShortTerm 200.00 USD ; Loss = positive income
Strategy 3: Wash Sale Tracking
Wash sale rule: If you sell at a loss and rebuy within 30 days, you can’t claim the loss.
Use metadata to track:
2024-11-01 * "Sell VTSAX - tax loss"
wash-sale-start: "2024-11-01"
wash-sale-end: "2024-12-01"
Assets:Investments:Brokerage:VTSAX -100 VTSAX {100.00 USD} @ 95.00 USD
Assets:Investments:Brokerage:Cash 9500.00 USD
Income:Investments:CapitalGains:ShortTerm 500.00 USD
; DON'T buy VTSAX again before 2024-12-02!
; Can buy similar fund (VTI) instead
Query to check wash sales:
SELECT date, payee, metadata('wash-sale-end')
WHERE metadata('wash-sale-end') IS NOT NULL
AND metadata('wash-sale-end') >= TODAY()
Advanced Portfolio Analysis
Asset Allocation Report
# asset_allocation.py
from beancount import loader
from beancount.query import query_execute
from collections import defaultdict
entries, errors, options = loader.load_file('main.beancount')
# Get all holdings with asset class
query = """
SELECT
account,
currency,
convert(position, 'USD') as value
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
"""
rtypes, rows = query_execute.execute_query(entries, options, query)
# Look up asset class from commodity directives
asset_classes = defaultdict(Decimal)
total_value = Decimal(0)
for row in rows:
value = row.value.get_only_position().units.number
total_value += value
# Get asset class from commodity metadata
# (simplified - in practice, parse commodity directives)
if 'Stock' in str(row.currency):
asset_classes['Stocks'] += value
elif 'Bond' in str(row.currency):
asset_classes['Bonds'] += value
# Report
print(f"{'Asset Class':<20} {'Value':>15} {'Allocation':>12}")
print("-" * 50)
for asset_class, value in asset_classes.items():
pct = (value / total_value) * 100
print(f"{asset_class:<20} ${value:>14,.2f} {pct:>11.1f}%")
Time-Weighted Return (TWR)
More accurate than simple ROI when adding/withdrawing funds:
# twr.py - Calculate time-weighted return
from beancount import loader
from datetime import date, timedelta
from decimal import Decimal
# Simplified TWR calculation
# For each period between cash flows:
# period_return = (ending_value - beginning_value - net_cash_flow) / beginning_value
# TWR = product of (1 + period_return) - 1
# This requires tracking:
# 1. Portfolio value at each cash flow date
# 2. Cash flows (deposits/withdrawals)
# Example implementation left as exercise - requires price history
Sector Diversification
Add sector metadata to commodities:
2020-01-01 commodity AAPL
name: "Apple Inc."
asset-class: "Stock"
sector: "Technology"
2020-01-01 commodity XOM
name: "Exxon Mobil"
asset-class: "Stock"
sector: "Energy"
Then query by sector:
SELECT
metadata('sector') as sector,
sum(convert(position, 'USD')) as value
WHERE account ~ 'Assets:Investments'
AND currency != 'USD'
GROUP BY sector
Generating Tax Forms
Schedule D (Capital Gains)
# schedule_d.py
from beancount import loader
from beancount.query import query_execute
entries, errors, options = loader.load_file('main.beancount')
# Get all sales in tax year
query = """
SELECT
date,
payee,
account,
units(position),
cost(position),
position
WHERE account ~ 'Income:Investments:CapitalGains'
AND year = 2024
"""
rtypes, rows = query_execute.execute_query(entries, options, query)
print("Schedule D - Capital Gains and Losses (2024)")
print("=" * 80)
print(f"{'Date':<12} {'Security':<10} {'Shares':>8} {'Cost':>12} {'Proceeds':>12} {'Gain/Loss':>12}")
print("-" * 80)
for row in rows:
# Parse and format for Schedule D
# ... implementation details ...
pass
Integration with gains_minimizer Plugin
The beancount.plugins.gains_minimizer can automatically select optimal lots:
plugin "beancount.plugins.gains_minimizer"
; When selling, leave lot empty - plugin picks best
2024-12-15 * "Sell AAPL - plugin picks lot"
Assets:Investments:Brokerage:AAPL -10 AAPL {} @ 180.00 USD
Assets:Investments:Brokerage:Cash 1800.00 USD
Income:Investments:CapitalGains
; Plugin automatically:
; 1. Selects lot with highest cost basis (minimize gain)
; 2. Prefers long-term over short-term if equal
; 3. Fills in the {} with chosen lot
Best Practices
- Record price updates daily: Automated with yfinance
- Use explicit lot selection: Don’t rely on FIFO/LIFO defaults
- Track wash sales: Use metadata to avoid violations
- Separate accounts by tax treatment: Taxable vs IRA vs Roth
- Regular rebalancing: Query asset allocation monthly
- Document tax decisions: Use transaction metadata
Resources
- Beancount investment docs: Trading with Beancount - Beancount Documentation
- fava-investor: Advanced investment analytics plugin
- beancount-reds-plugins: Capital gains classifier
What investment tracking queries do you find useful? Any edge cases I haven’t covered?