bean-query balance weirdness

[Max Katsev]

I'm trying to make a query that would return networth over time. With a lot of experimentation, I've found that 

  SELECT year, month, last(balance) WHERE account ~ 'Assets|Liabilities'

works (using the examples/simple/basic.beancount ledger from the github repo):

year  mo                             last(balance)
----  --  -------------------------------------------------------------------
2007  12                        200.00 CAD                       44152.81 USD
2008   1  30 AAPL {185.40 USD}  200.00 CAD                       40465.46 USD
2008   2                        200.00 CAD  100 EWJ {13.34 USD}  46904.98 USD
2008   3                        200.00 CAD  100 EWJ {13.34 USD}  44995.59 USD
2008   4                        200.00 CAD  100 EWJ {13.34 USD}  44995.59 USD

However, if I try to calculate the networth at the beginning of the period instead of the end, the following query:

    SELECT year, month, first(balance) WHERE account ~ 'Assets|Liabilities'

returns absolute nonsense:

year  mo       first(balance)    
----  --  -----------------------
2007  12  200.00 CAD
2008   1  200.00 CAD  -212.61 USD
2008   2  200.00 CAD  -425.22 USD
2008   3  200.00 CAD  -637.83 USD
2008   4  640.00 CAD  -637.83 USD

Admittedly I'm struggling to understand what the balance column actually does, but this behavior seems pretty unexpected.

[Chary Ev2geny]

I use the following query to get a Net Worth at a specific date, converted to a target currency and grouped by account

    query = f"""

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

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

    """

We discussed it here, and so far came to the conclusion, that usage of the balance is somehow is not very suitable for getting the Net Worth  

[Max Katsev]

The difference is that I want a query to calculate net worth for every time period in a table, not a single date.