Thanks Martin for the response. You are right, I didn't detailed enough.
Now I made a specific beancount file to show what I meant. I defined three
Assets and two commodities to make it more comprehensive:
2022-11-01 commodity EUR 2022-11-01 commodity AAPL 2022-11-01 open
Assets:Bank:Checking EUR 2022-11-01 open Assets:Personal:Cash EUR
2022-11-01 open Assets:Broker:AAPL AAPL 2022-11-01 open Income:Salary
2022-11-01 open Income:Gift 2022-11-01 open Expenses:Stuff ; 2022
2022-11-15 * "Savings" "" Assets:Personal:Cash 30 EUR Income:Gift
2022-11-15 * "Salary" "" Assets:Bank:Checking 100 EUR Income:Salary
2022-12-20 * "Broker" "Buy AAPL" Assets:Broker:AAPL 5 AAPL {5 EUR}
Assets:Bank:Checking -25 EUR ; 2023 2023-01-15 * "Amazon" ""
Assets:Bank:Checking -5 EUR Expenses:Stuff 2023-03-15 * "Savings" ""
Assets:Personal:Cash 20 EUR Income:Gift 2023-06-15 * "Broker" "Buy AAPL"
Assets:Broker:AAPL 3 AAPL {10 EUR} Assets:Bank:Checking -30 EUR 2023-09-15
* "Amazon" "" Assets:Bank:Checking -20 EUR Expenses:Stuff 2023-09-15 *
"Savings" "" Assets:Personal:Cash 10 EUR Income:Gift 2023-10-15 * "Salary"
"" Assets:Bank:Checking 100 EUR Income:Salary
Now I can extract the monthly balances for the year 2023 by making the
following query for each account:
> bean-query data/test.beancount "SELECT month, year, units(last(balance))
FROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01 WHERE account ~
'Assets:Broker:AAPL'" mo year units_ -- ---- ------ 12 2022 5 AAPL 6 2023 8
AAPL
> bean-query data/test.beancount "SELECT month, year, units(last(balance))
FROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01 WHERE account ~
'Assets:Personal:Cash'" mo year units_ -- ---- ------ 12 2022 30 EUR 3 2023
50 EUR 9 2023 60 EUR
> bean-query data/test.beancount "SELECT month, year, units(last(balance))
FROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01 WHERE account ~
'Assets:Bank:Checking'" mo year units_l -- ---- ------- 12 2022 75 EUR 1
2023 70 EUR 6 2023 40 EUR 9 2023 20 EUR 10 2023 120 EUR
But if I wanted this information presented using only one query, I would
execute the following:
> bean-query data/test.beancount "SELECT month, year, account,
units(last(balance)) AS blc FROM OPEN ON 2023-01-01 CLOSE ON 2024-01-01
WHERE account ~
'Assets:Bank:Checking|Assets:Personal:Cash|Assets:Broker:AAPL' GROUP BY
year, month, account ORDER BY year, month, account" mo year account blc --
---- -------------------- -------- 12 2022 Assets:Bank:Checking 75 EUR 12
2022 Assets:Broker:AAPL 75 EUR , 5 AAPL 12 2022 Assets:Personal:Cash 105
EUR , 5 AAPL 1 2023 Assets:Bank:Checking 100 EUR , 5 AAPL 3 2023
Assets:Personal:Cash 120 EUR , 5 AAPL 6 2023 Assets:Bank:Checking 90 EUR ,
8 AAPL 6 2023 Assets:Broker:AAPL 120 EUR , 8 AAPL 9 2023
Assets:Bank:Checking 70 EUR , 8 AAPL 9 2023 Assets:Personal:Cash 80 EUR , 8
AAPL 10 2023 Assets:Bank:Checking 180 EUR , 8 AAPL
Which outputs multiple balances for each line (I wouldn't mind if they were
correct) but it seems like it sums the balances for the same currency. For
example, in line 3 the account Assets:Personal:Cash on 2022-12 should be
equal to 30 EUR, but it shows 105 EUR, which I guess is summing 30 with the
75 of Assets:Bank:Checking.
What I expected was the following output.
mo year account blc -- ---- -------------------- -------- 12 2022
Assets:Bank:Checking 75 EUR 12 2022 Assets:Broker:AAPL 5 AAPL 12 2022
Assets:Personal:Cash 30 EUR 1 2023 Assets:Bank:Checking 70 EUR 3 2023
Assets:Personal:Cash 50 EUR 6 2023 Assets:Bank:Checking 40 EUR 6 2023
Assets:Broker:AAPL 8 AAPL 9 2023 Assets:Bank:Checking 20 EUR 9 2023
Assets:Personal:Cash 60 EUR 10 2023 Assets:Bank:Checking 120 EUR
I may be wrong here in expecting this output because I misinterpreted the
query, or maybe I am missing something stupid :)
I hope I have clarified my issue. Thank you so much.
Andrea
Il giorno sabato 2 marzo 2024 alle 17:06:10 UTC+1 bl...@furius.ca ha
scritto:
> 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 <kiv...@gmail.com> 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 beancount+...@googlegroups.com.
>> 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 beancount+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/bd5a0491-3b74-4a25-83b4-086159210178n%40googlegroups.com.
