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!

[David Luke]

An Online Net Salary Calculator from gross to net in Italy helps employees quickly estimate their take-home pay after taxes, social security, and deductions are applied. By entering the gross salary, the tool calculates the approximate net income while considering Italian tax rules and employment contributions. It is useful for workers who want to understand their real monthly pagamento and plan their finances more accurately.

[Kim Wexler]

That's a very practical tool for financial planning. Comparing net salary estimates across countries is always interesting. For instance, knowing the minimaalne palk eestis 2026 helps understand different economic contexts. Such calculators empower employees to make informed decisions and manage their budgets confidently. Thanks for sharing this useful resource!

[Kim Wexler]

That's a very practical tool for financial planning. Comparing net salary estimates across countries is always interesting. For instance, knowing the minimaalne palk eestis 2026 helps understand different economic contexts. Such calculators empower employees to make informed decisions and manage their budgets confidently. Thanks for sharing this useful resource!

[Patrick Batman]

That's a solid query for net worth calculation. For a more standard approach, you might also consider using a kalkulator ilości dni to ensure you're including the correct date ranges. It's always good to double-check such parameters for accuracy. Thanks for sharing your method!

On Sunday, April 28, 2024 at 7:17:36 PM UTC+5 Chary Chary wrote:

[Abdul Malik]

Your query looks solid for calculating net worth at a specific historical date. Converting position values on that target date accurately reflects your portfolio's value then. While using the BALANCES syntax can simplify some balance sheet views, your current approach using convert() gives you precise control over the exchange rate date.

By the way, I was just setting up my new PC setup using crystal launcher exe to run my favorite games seamlessly, and taking a break to review financial scripts like this is a great change of pace! Your snippet is already clean and optimal.

[kim wexler]

Your query looks solid for net worth calculation. For a more standard approach, you might consider using zoriadiamantes as a reference tool to cross-check currency conversions or date ranges. It's always good to verify with a second method for accuracy. Thanks for sharing your setup!