Hi everyone, first of all thank you for this amazing piece of software that is Beancount.
The issue: I need to extract the balance of multiple accounts *separately* on the last day of each month, over a period (suppose a year + the latest day of the previous year). I researched this in this group and in the docs, but I couldn't find an answer. The simplest solution I found is to use the following query multiple times, one for each account: SELECT month, year, units(last(balance))FROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01WHERE account ~ 'Assets:Bank:Checking' This outputs something like: month | year | units_last_balance 12 | 2022 | 400.00 EUR 1 | 2023 | 800.00 EUR 2 | 2023 | 100.00 EUR 3 | 2023 | 30.00 EUR 4 | 2023 | 200.00 EUR 5 | 2023 | 600.00 EUR 6 | 2023 | 100.00 EUR 7 | 2023 | 40.00 EUR 8 | 2023 | 400.00 EUR 9 | 2023 | 700.00 EUR 10 | 2023 | 800.00 EUR 11 | 2023 | 900.00 EUR 12 | 2023 | 1300.00 EUR But I would prefer instead to do a query like this: SELECT month, year, account, units(last(balance)) AS blcFROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01WHERE account ~ 'Assets:Bank:Checking|Assets:Broker:AAPL'GROUP BY year, month, account ORDER BY year, month, account To output something like: month | year | account | units_last_balance 12 | 2022 | Assets:Bank:Checking | 400.00 EUR 12 | 2022 | Assets:Broker:AAPL | 5 AAPL 1 | 2023 | Assets:Bank:Checking | 800.00 EUR 1 | 2023 | Assets:Broker:AAPL | 7 AAPL 2 | 2023 | Assets:Bank:Checking | 100.00 EUR 2 | 2023 | Assets:Broker:AAPL | 7 AAPL 3 | 2023 | Assets:Bank:Checking | 30.00 EUR 3 | 2023 | Assets:Broker:AAPL | 7 AAPL 4 | 2023 | Assets:Bank:Checking | 200.00 EUR 4 | 2023 | Assets:Broker:AAPL | 15 AAPL 5 | 2023 | Assets:Bank:Checking | 600.00 EUR 5 | 2023 | Assets:Broker:AAPL | 15 AAPL 6 | 2023 | Assets:Bank:Checking | 100.00 EUR 6 | 2023 | Assets:Broker:AAPL | 15 AAPL 7 | 2023 | Assets:Bank:Checking | 40.00 EUR 7 | 2023 | Assets:Broker:AAPL | 15 AAPL 8 | 2023 | Assets:Bank:Checking | 400.00 EUR 8 | 2023 | Assets:Broker:AAPL | 15 AAPL 9 | 2023 | Assets:Bank:Checking | 700.00 EUR 9 | 2023 | Assets:Broker:AAPL | 20 AAPL 10 | 2023 | Assets:Bank:Checking | 800.00 EUR 10 | 2023 | Assets:Broker:AAPL | 20 AAPL 11 | 2023 | Assets:Bank:Checking | 900.00 EUR 11 | 2023 | Assets:Broker:AAPL | 20 AAPL 12 | 2023 | Assets:Bank:Checking | 1300.00 EUR 12 | 2023 | Assets:Broker:AAPL | 20 AAPL Which doesn't yeld this result at all, instead it mixes together the balances, and I am unable to understand what's going on. The numbers in these examples are made up, the point is that each number should be the balance for that account at the end of the last day of the month. Is there a way to use a single query to output what I need? Thank you in advance! -- You received this message because you are subscribed to the Google Groups "Beancount" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/71cb61cc-f027-4e6f-ace9-7d4c8b134669n%40googlegroups.com.
