Beancount queries

Create alias:

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

Accounts

List all know acconts:

SELECT account FROM #accounts

List all uses account in year, filter by regex:

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

Account Balances

Balances at the end of the year:

BALANCES
BALANCES FROM CLOSE ON 2023-01-01

Transaction totals for given year:

BALANCES FROM year = 2024

Beware: OPEN ON .. CLOSE ON adds an Opening balance transaction with the balance on the account before the time span! The total not the same as the query above!

BALANCES FROM OPEN ON 2024-01-01 CLOSE ON 2025-01-01

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

Balance totals in 1 number:

SELECT sum(cost(position)) as total 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
JOURNAL 'Expenses'  FROM year >= 2024

Show transaction after a given date:

SELECT date,description,position FROM OPEN ON 2024-01-01 WHERE account ~ 'Spenden'

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'