No problem, glad it helped!
Intuitively sounds like BeanSummator might do something similar to what's
happening with balance BQL under the hood...
In any case, it would be cool if balance query supported grouping correctly
as it would expand on what dashboards it's possible to build (e.g. stacked
net worth graph). I haven't looked into the code myself or thought deeply
about whether it's possible though.
Best regards,
Vasily
On Wednesday, March 26, 2025 at 3:45:42 PM UTC+1 Chary Ev2geny wrote:
> 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/ae2a0f2d-4514-4629-b3c8-19821b901d47n%40googlegroups.com.
