I think the query you issued should work. Can you be more specific about "it mixes together the balances"? Maybe isolate just a few transactions and run on that, to isolate what you believe is the problem and reply here.
On Sat, Mar 2, 2024 at 9:58 AM Andrea Vidali <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/beancount/71cb61cc-f027-4e6f-ace9-7d4c8b134669n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAK21%2BhNeftC%2BDYgFqVcLXGuzT_WS%2Bg03J1J05qLS6Aeu7TWo3w%40mail.gmail.com.
