Hi Vasily,
thank you so much, I see now how it works!
So, for this ledger
ledger = """
2020-01-01 open Assets:Bank1
2020-01-01 open Assets:Bank2
2020-01-01 open Income:Salary
2020-01-01 * "Salary 1"
Income:Salary
Assets:Bank1 10 EUR
2020-01-02 * "Salary 2"
Income:Salary
Assets:Bank2 100 EUR
2021-01-20 * "Salary 3"
Income:Salary
Assets:Bank2 1000 EUR
"""
and this query
query =f"""
SELECT account, LAST(balance) AS amount
WHERE date <= 2020-12-31 AND account ~ 'Assets|Liabilities'
"""
I get the following:
account amount (EUR)
Assets:Bank1 10.0
Assets:Bank2 110.0
And for this query
query =f"""
SELECT LAST(balance) AS amount
WHERE date <= 2020-12-31 AND account ~ 'Assets|Liabilities'
"""
I get the following:
amount (EUR)
110.0
So, as you said, it does respect the WHERE restriction, but calculated
balance as a combination of postings to all accounts, which fall into the
WHERE clause
I think for now I will avoid using *balance *in queries, as it may be
confusing.
For reporting periodic balances I used to use just a series of queries like
this, which I would call against every day I want to know balance for
query = f"""
SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount
where date <= {date_iso} AND account ~ 'Assets|Liabilities'
"""
But as this very inefficient, since there is a lot of re-calculation going
on, I then developed the class BeanSummator
<https://github.com/Ev2geny/evbeantools/blob/97f637d9a599d077239f8a9556776a3c82817b4c/src/evbeantools/summator.py#L248>
with
the function sum_till_date
<https://github.com/Ev2geny/evbeantools/blob/97f637d9a599d077239f8a9556776a3c82817b4c/src/evbeantools/summator.py#L292C9-L292C22>which
I can call periodically with ascending dates and it would calculate a
balance for me quite fast, as it stores a pervious calculation.
On Wednesday, March 26, 2025 at 1:08:25 PM UTC+1 Vasily M wrote:
Hi Chary,
I'll start with a disclaimer that I don't have a perfect understanding of
the "balance" operation intuition. It is useful but it seems it indeed has
limitations and in particular it doesn't work as expected with GROUP BY
statements. I think I have seen this mentioned explicitly but couldn't find
now, at least in the official docs here:
https://beancount.github.io/docs/beancount_query_language.html, only a
hint "Access to the previous row is not a standard SQL feature" suggests
that it might not work as expected.
In your example the issue is not that it doesn't respect the WHERE filter
(I think it does) but that it aggregates balance between different
accounts. That will become more obvious if you change 1000 EUR to different
amounts (say, 500, 600 and 700) in your example transactions.
The partial solution to this is to explicitly use account ~ 'Assets:Bank2'
in filter. This allows to get total balances for the particular account or
the group of accounts that you specify in the filter. For example, in this
case just removing "account" from SELECT will make the query calculate
correctly the net worth across all Assets+Liabilities.
However, I'll also add that at very least the output of the query (when it
includes "SELECT account") seems counter-intuitive to me, if not incorrect.
Not sure if there is at least a way to display a warning if some groupping
is about to happen that is not related to date groupping (I'm not sure
about balances inner workings but it seems it's optimised for that).
P.S. If you choose to try a query for reporting periodic balances, like the
one I've sent in the neighbouring thread or the one from the
fava-dashboards repo example, you'll also likely to come across another
unrelated issue:
https://github.com/andreasgerstmayr/fava-dashboards/issues/28#issuecomment-2573181426
that the rows will not be generated unless there's at least one transaction
present in the time period. Leaving it here just in case.
Best,
Vasily
On Tuesday, March 25, 2025 at 12:17:19 PM UTC+1 Chary Ev2geny wrote:
Dear all,
I must I still struggle to find a way to use balance to calculate the net
worth on certain date (e,g, 2020-12-31 for my example from this thread)
It looks like the *balance *field does not respect the WHERE filter.
In this case, what is the purpose of the balance field all together?
On Sunday, June 2, 2024 at 2:29:23 PM UTC+2 Chary Ev2geny wrote:
Dan,
is it possible to get the result as in the 1st query, but using the *balance
*?
account amount (EUR)
Assets:Bank1 1000.0
Assets:Bank2 1000.0
On Sunday, June 2, 2024 at 12:36:23 AM UTC+2 [email protected] wrote:
On 01/06/24 21:27, Chary Chary wrote:
> I am still trying to find the way to calculate the net worth at a
> certain date using *balance *field (I am not sure it will give me any
> advantage)
>
> But somehow it does not quire work as I expect
>
> E.g.
>
> ledger_text = """
>
> 2020-01-01 open Assets:Bank1
> 2020-01-01 open Assets:Bank2
> 2020-01-01 open Income:Salary
>
> 2020-01-01 * "Salary 1"
> Income:Salary
> Assets:Bank1 1000 EUR
>
> 2020-01-20 * "Salary 2"
> Income:Salary
> Assets:Bank2 1000 EUR
>
> 2021-01-20 * "Salary 3"
> Income:Salary
> Assets:Bank2 1000 EUR
> """
>
> date_iso = datetime.date(2020,12,31).isoformat()
>
> query=f"""
> select account, SUM(position) as amount
> where date <= {date_iso}AND account ~ 'Assets|Liabilities'
> """
>
> result:
>
> account amount (EUR)
> Assets:Bank1 1000.0
> Assets:Bank2 1000.0
>
>
> This is correct!!
> Now using balance field
> query=f"""
> select account, LAST(balance) as amount
> where date <= {date_iso}AND account ~ 'Assets|Liabilities'
> """
> account amount (EUR)
> Assets:Bank1 1000.0
> Assets:Bank2 2000.0 <==== This is incorrect!
> So, what am I doing wrong?
AFAICT, both queries do what they are expected to do.
Cheers,
Dan
--
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 visit
https://groups.google.com/d/msgid/beancount/b18ada74-706f-4c54-8c0f-338a0c716a2en%40googlegroups.com.
