Beancount queries

Create alias:

alias bq="bean-query ~/beancount/personal.beancount"

Accounts

List all know acconts:

SELECT DISTINCT account ORDER BY account

List all uses account in year, filter by regex:

SELECT DISTINCT account FROM year = 2024 WHERE account ~ 'Expenses' ORDER BY account

Account Balances

SELECT account, sum(position)
  FROM year = 2019 WHERE account ~ 'Expenses:Privat'
  ORDER BY account

Balance totals in 1 number:

SELECT sum(cost(position)) as income FROM year = 2020
  WHERE account ~ 'Expenses:Privat'

Balance until given date:

SELECT account, sum(position) FROM CLOSE on 2022-01-01
  WHERE account = 'Assets:Privat'

Income total

SELECT sum(cost(position)) as income FROM year = 2018
  WHERE account ~ 'Income:Privat'

Net profit

SELECT sum(cost(position)) as income FROM year = 2018 WHERE account ~ 'Income:Privat|Expenses:Privat'

Show income with metadata mwst = 19:

SELECT account, sum(position) FROM year = 2020 \
  WHERE account ~ 'Income' AND any_meta('mwst') = '19'

Find amounts

SELECT * WHERE Number = 3.8
SELECT date,description,account,position WHERE number = 229.06
SELECT date,description,account,position FROM year = 2020 \
  WHERE account ~ '^Expenses' AND number > 800

Journal entries / list transactions

See links section below for a SQL query that is similar to a JOURNAL query, but contains more fields like i.e. links!

From the mailing list thread:

FROM can be used only with transaction columns and WHERE can be used only with posting columns.

JOURNAL "Expenses" AT COST FROM year = 2019
JOURNAL 'Assets' FROM NOT narration ~ 'foo'
JOURNAL 'Assets' FROM NOT narration ~ 'foo' AND year = 2014

Search for narration (description):

JOURNAL FROM narration ~ 'foo'
JOURNAL 'Assets' FROM narration ~ 'foo'

Date ranges:

journal FROM OPEN on 2014-01-01 CLOSE on 2015-01-01
journal "Assets" FROM CLOSE on 2021-01-14

Calculate total from date range:

SELECT account, sum(position) FROM OPEN ON 2021-09-01 CLOSE ON 2022-09-01 \
  WHERE account ~ "Income|Expenses" GROUP BY 1 ORDER BY 1

Include transaction flag:

SELECT date,description,account,position,flag WHERE account ~ '^Income'

Show unverified transactions:

SELECT date,description,account,position,flag \
  WHERE account ~  '^Income' AND flag != '*'

Sum of transactions

Generate csv file with sum of income/expenses for date range:

SELECT account, sum(position) \
  FROM OPEN ON 2021-09-01 CLOSE ON 2022-09-01 \
  WHERE account ~ "Income|Expenses" GROUP BY 1 ORDER BY 1;

Flags

Transactions

Handling of flags in the lexer:

The lexer recognizes the !&#?%PSTCURM characters as flags

Default txn flag will be evaluated as * !

Links

SELECT date,description,account,position,balance,links FROM year = 2020 \
  WHERE account = 'Income'

Metadata

Transaction metadata:

SELECT date,description,account,position,tags WHERE any_meta('mwst') = '19'
SELECT date, description, position, balance
  WHERE any_meta('invoice') = '200'  and ('payment' in tags)

Account meatadata:

select getitem(open_meta('Income'), 'mwst') as name group by 1

Print journal entries matching a query:

PRINT FROM year = 2019 AND HAS_ACCOUNT('Assets')
PRINT FROM HAS_ACCOUNT('Income')
PRINT FROM narration ~ 'Mobile'