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