SQL queries for Beancount

This is a list of example SQL queries for Beancount. It should be a reference and inspiration for users of beancount. If you want to contribute and add your own query to this list please create an issue or submit a pull request. For more information about the SQL-like query language for beancount visit the Beancount Query Language documentation.

Queries

List of Expenses for this Month

by har96

To display a list of Expenses for the current month, you can use the following query:

SELECT 
    account, sum(cost(position)) as total, month 
WHERE
    account ~ "Expenses:*" and year = YEAR(today()) and month = MONTH(today()) 
GROUP BY month, account 
ORDER BY total, account DESC

Queries suitable for exporting

by Alex Johnstone

If you want to save a query as a CSV/xls etc., then it’s useful to separate the number and the currency into individual columns. Here is an example of monthly expenses and income for 2016 which does that.

SELECT 
  month, account, sum(number) as Total, currency
FROM
    year = 2016  
WHERE 
    account ~ 'Expenses' OR
    account ~ 'Income'
GROUP BY month, account, currency 
ORDER BY month, currency, account

Monthly expenses report

by Alex Johnstone

Similar to “Expenses, Income and Loans/Mortgage monthly totals”, but now showing all expense subaccounts.

SELECT
    year, month, account, sum(position)
FROM
    date > 2015-01-01 AND date < 2016-02-29
WHERE
    account ~ "Expenses"
    GROUP BY year, month, account
    ORDER by year, month, account
    FLATTEN

Holdings Report

by Alex Johnstone

Tries to replicate bean-report holdings. Shows units, book value (what you paid) and market value (what they’re worth now).

SELECT 
	account, units(sum(position)) AS quantity, cost(sum(position)) AS Book , convert(units(sum(position)), "USD") AS Market
WHERE
	account ~ "Assets:Trading" 
GROUP BY account 
ORDER BY account

Expenses, Income and Loans/Mortgage monthly totals

by Alex Johnstone

This query produces a table showing top level account monthly totals. A very high level summary over all time. FLATTEN puts each currency on its own line. Useful for saving as csv/pasting into a spreadsheet.

SELECT
    year, month, root(account, 1) as account, sum(position) as total
  FROM
    date > 2010-01-01 AND date < 2016-12-31
  WHERE
     account ~ "Expenses" OR
     account ~ "Liabilities:Mortgage" OR
     account ~ "Liabilities:Loan" OR
     account ~ "Income"
  GROUP BY year, month, account
  ORDER BY year, month, account
  FLATTEN

USD-Expenses in 2015, grouped by month 

SELECT
  MONTH(date) AS month,
  SUM(COST(position)) AS balance
WHERE
   account ~ 'Expenses:' AND
   currency = 'USD' AND
   YEAR(date) = 2015
GROUP BY 1
ORDER BY 1;

Account Balances in 2013 

balances FROM year = 2013

Balances of accounts which contain "Phone" in their name

This query shows the balances of accounts which contain “Phone” in their name, on each day a transaction was made on that account.

SELECT
  date, account, position, balance 
WHERE 
  account ~ 'Phone';