Report / beanquery which would show changes to net worth between 2 periods in time due to price changes

[Chary Chary]

Dear all, 

I al looking for some report / beanquery which would explain change between the net worth report, taken at 2 moments in time and will taken into account changes in price.

Explanation:

suppose I have the file

==================================================================

option "operating_currency" "EUR"

2000-01-01 commodity EUR

  name: "Euro"

  asset-class: "cash"

2000-01-01 commodity USD

  name: "US dollar"

  asset-class: "cash"

2000-01-01 commodity CAR

2000-01-01 open Assets:Captinal:Car

2000-01-01 open Assets:Bank-EU

2000-01-01 open Assets:Bank-US

2000-01-01 open Equity:Opening-Balances

2000-01-01 * "Opening Balances"

    Assets:Bank-EU   10000.00 EUR

    Equity:Opening-Balances -10000.00 EUR

2000-01-01 * "Opening Balances"

    Assets:Bank-US   10000.00 USD

    Equity:Opening-Balances -10000.00 USD

2000-01-02 * "Buying Car"

    Assets:Captinal:Car  1 CAR @@ 5000.00 EUR

    Assets:Bank-EU -5000.00 EUR

; Depretiation of the car enetered as a change in price

2000-01-02 price CAR 5000.00 EUR

2000-02-01 price CAR 4500.00 EUR

; Changes in EUR/USD exchange rate

2000-01-01 price USD 1 EUR

2000-04-01 price USD 0.5 EUR

====================================================================

My net worth in EURO at the beginning of the Y2000 is:

beanquery> select account, SUM(convert(cost(position),"EUR", 2000-01-01)) as value where date <= 2000-01-01 and (account ~"Assets" or account ~ "Liabilities")
   account        value
-------------- ------------
Assets:Bank-EU 10000.00 EUR
Assets:Bank-US 10000.00 EUR

Total 20 000 EUR

My net worth in EURO end of the 2000 is:

beanquery> select account, SUM(convert(cost(position),"EUR", 2000-12-31)) as value where date <= 2000-12-31 and (account ~"Assets" or account ~ "Liabilities")
      account          value
------------------- -----------
Assets:Bank-EU      5000.00 EUR
Assets:Bank-US      5000.00 EUR
Assets:Captinal:Car 4500.00 EUR

Total 14 500 EUR

Difference is 5500 EUR

So, I am looking for a report, which would explain this difference in the following way:

price_changes USD/EUR  -5000 EUR

price_changes CAR/EUR    -500 EUR

-------------------------------------------------------------

Total                                     -5500 EUR

Is there something like this? May be with unrealized gains?

[Chary Chary]

Ok,

I now see, that the unrealized gains plugin comes close to what I am looking for, except that it only looks at the plosions, which have cost.

So, I would have to create something, based on this plugin

By the way, what is the exact reason the unrealized gains plugin ignores changes for the positions, where there is no initial cost information?  Correct me if am  wrong, but the reason beancount has a feature to track cost is to accommodate requirements of tax law in certain countries. But for sure one would want to know why his net worth has changed in the period from date A to date B regardless of whether he reordered the cost information. 

[Red S]

On Monday, February 27, 2023 at 12:25:55 PM UTC-8 char...@gmail.com wrote:

Ok,

I now see, that the unrealized gains plugin comes close to what I am looking for, except that it only looks at the plosions, which have cost.

So, I would have to create something, based on this plugin

By the way, what is the exact reason the unrealized gains plugin ignores changes for the positions, where there is no initial cost information?  Correct me if am  wrong, but the reason beancount has a feature to track cost is to accommodate requirements of tax law in certain countries.

Price conversions are generally intended for cases where the conversion is considered to be a one-time transaction, with no connection between that and a subsequent conversion in the reverse direction. 

 

But for sure one would want to know why his net worth has changed in the period from date A to date B regardless of whether he reordered the cost information. 

Various Net-worth change analyses can be helpful, but I'm not sure I understand specifically what question you are trying to answer. "Wanting to know why one's networth changed over a period of time" is actually a deep question that could involve capital gains, earnings, and taxation. In your example, the CAR case is trivial: you have one car, and its price changed. With banking, it's far less so. Your networth might have changed due to contributions, withdrawals, and interest. Your question seems (to me) to assume that the only effect is due to price changes, and which in turn implies there are zero banking transactions between Jan 1 and Dec 31, which I'm sure you didn't mean, but I don't quite see. If you could clarify these, it might be easier to offer a solution.

[Chary Chary]

For sure, I understand, that  net worth change involves more than my example, but in my example I concentrated on the thing which as I understand beancount does not do at the moment.

effectively I want the report, which would show exactly why my networth has change in the period from A to B, when all converted to a single currency. In this period networth at the date A shall be measured in the prices of the date A, networth at the date B shall be measured in the prices of the date B. All incomes and expenses in the period between date A and date B shall be measured at the prices of that date.

To make it more formal:

In a simple situation of a single currency my networth change in the period A=>B is a difference between income and expenses in this period.  Taking into account signs if beancount  (Liabilities and Income are normally negative) the following formula is true:

 Assets(date A) + Liabilities (Date A) - [ Assets(date B) + Liabilities (Date B)] = Income (period A-B) + Expenses (Period A-B) 

In another words in a situation of a single currency to understand why my net worth has changed I just need to analyze income and expenses and the difference between them (sum, taking into account beancount signs) will be exactly equal to change in my net worth.

So, now I want to be able to do exactly that, converting all to a single currency  (networth at prices of dates of measurement and changes at prices of transaction date ) but in the situation of whatever complexity of financial transactions in the period A-B (baying/selling stock, converting to different currencies, buying selling crypto, having a car, which drops in price and having a house, which grows in price). 

To express in a Python pseudo code I would want something like this:

def get_single_number_beanquery_result(query:str, entries):

    """

    Function returns a single number from a beanquery result.

    """

    result = None

   

    ## Some code goes here to update rsult

   

    return result

   

def get_net_worth_at_date_and_currency(entries,my_date, currency):

    query=f"""

    select SUM(CONVERT(COST(position),{currency},{ my_date})

    WHERE (account ~'Assets' OR account ~'Liabilities') AND date <= {my_date}

     """

    return get_single_number_beanquery_result(query, entries)

def get_changes_to_net_worth_over_a_period_in_single_currency(entries,start_date, end_date, currency):

   

    # Note, that the date is the date of transaction here

    query=f"""

    select SUM(CONVERT(COST(position),{currency},date)) as cost

    WHERE (account ~'Expenses' OR account ~'Income' ) AND date >= f{start_date} AND date  <= {end_date}

    """

    return get_single_number_beanquery_result(query, entries)

def insert_unrealyzed_gains_for_networth_diff_analisys(entries, start_date, end_date, currency):

    """

    This is the function I am looking for

    """

    updated_entries = None

    # Some code to update entries

    return updated_entries

And then I want  the following test to always pass:

class TestNetWorthDiff(unittest.TestCase):

   

    @loader.load_doc()

    def test_insert_unrealyzed_gains_for_networth_diff_analisys(self, entries, errors, options_map):

        """

        Some beancount code goes here

        """

        currency = "EUR"

       

        start_date, end_date = None, None

       

        #TODO: Assign some real start and end dates

       

        net_worth_start = get_net_worth_at_date_and_currency(entries, start_date -1, currency)

       

        net_worth_end = get_net_worth_at_date_and_currency(entries, end_date, currency)

       

        updated_entries = insert_unrealyzed_gains_for_networth_diff_analisys(entries, start_date, end_date, currency)

       

        changes_over_period = get_changes_to_net_worth_over_a_period_in_single_currency(updated_entries, start_date, end_date, currency)

       

        self.assertEqual(changes_over_period, net_worth_end - net_worth_start)