Balance for each account for each month

[Andrea Vidali]

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!

[Martin Blais]

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.

--
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.

[Andrea Vidali]

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

​

[Daniele Nicolodi]

On 03/03/24 23:16, Andrea Vidali wrote:

> Which outputs multiple balances for each line

You have one balance per row, as expected. However, each balance
contains amounts in different commodities, presented separated by a comma.

The `balance` column in beanquery is just the cumulative sums of all the
positions iterated by the query. It does not obey the GROUP BY clause.

Adding the possibility to compute what you ask is on the todo list, but
it is not trivial. One of the issues it that the SQL-like syntax for
such a query is quite complex (actually, I cannot even come up with a
SQL query that does exactly what you would like, without involving one
subquery, if someone know a better way I'll be happy to see it).

Cheers,
Dan

[Andrea Vidali]

Thank you Dan for the insights, I will continue using single queries without any issues. Thanks for the help! 

Andrea