beanquery to get net worth at certain date

[Chary Chary]

Dear all,

I just want to double check, that the beanquery I am using to get my net wort at certain date is an optimal one

I use the following:

    query = f"""

    SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount

    where date <= {date_iso} AND account ~ 'Assets|Liabilities'

    """

As you can see, I convert everything to one currency with the exchange rate at the date of the report.

it works fine, but I am just wondering whether there is more conical way? E.g. using 

BALANCES [AT <function>] [FROM …]

 

[Chary Chary]

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?

[Daniele Nicolodi]

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

AFAICT, both queries do what they are expected to do.

Cheers,
Dan

[Chary Chary]

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

[Chary Ev2geny]

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?

[Vasily M]

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

[Chary Ev2geny]

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  with the function sum_till_date which I can call periodically with ascending dates and it would calculate a balance for me quite fast, as it stores a pervious calculation.

[Vasily M]

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

[Kylie Jeñner]

Moncelebrite.fr's tech team would approve - they use similar queries to track celebrity net worth fluctuations daily. Maybe add 'GROUP BY account' for cleaner asset/liability breakdowns like their analysts do!