sum(value(position)) that uses cost in some cases

[muks]

In management reports prepared according to our country's FRS, the statement of financial position (~balance sheet) is presented with holdings in foreign currency converted to market value, except for non-monetary items. Non-monetary items that are measured in terms of historical cost in a foreign currency are translated using the exchange rates as at the dates of the initial transactions. As an example of such assets, prepayments can be monetary or non-monetary. The statement of financial position contains a mix of both (some presented at value, and some presented at cost), and we have to sum() a mix of them to get the total assets, and total equity and liabilities.

Because there are a mix of both and we want the sum over them, functions such as sum(value(position)) or sum(cost(position)) do not return the correct result as they use the market value or cost for all the positions. I browsed the beanquery code to see if metadata such as value-at-cost: 1 after the open statement can be added and then used in the function that handles value(position) in query_env.py. The function would check if the metadata is set for that account and use cost or value appropriately. However, a Position is just an Amount and Cost and metadata about the account cannot be accessed, so this way didn't work out.

Please could you suggest what I could do to achieve a sum(position) query where it uses the value or the cost depending on the type of asset?

[muks]

Summing non-monetary and monetary accounts at-cost and at-value separately may be an option, but please tell me if you know of a better way. I tried to sum just the accounts marked with metadata value-at-cost, but got an exception:

Sample Beancount input file:

option "title" "Company Private Limited"
option "operating_currency" "SGD"
option "inferred_tolerance_default" "*:0.01"
option "account_rounding" "Equity:RoundingError"

2021-04-01 custom "fava-option" "fiscal-year-end" "03-31"
2021-04-01 open Equity:RoundingError

2021-04-02 open Assets:Current:Bank SGD
2021-04-02 open Assets:Current:Prepayments:Non-Monetary:Vendor1 USD
  value-at-cost: 1

2021-04-02 * "Transaction 1"
  Assets:Current:Bank
  Assets:Current:Prepayments:Non-Monetary:Vendor1 14.00 USD {1.3612 SGD}

beancount> select sum(cost(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE 'value-at-cost' IN OPEN_META(account) AND account ~ '^Assets';
Traceback (most recent call last):
  File "/usr/lib64/python3.9/cmd.py", line 214, in onecmd
    func = getattr(self, 'do_' + cmd)
AttributeError: 'BQLShell' object has no attribute 'do_select'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/shell.py", line 271, in run_parser
    self.dispatch(statement)
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/shell.py", line 251, in dispatch
    return method(statement)
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/shell.py", line 416, in on_Select
    rtypes, rrows = query_execute.execute_query(c_query,
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/query_execute.py", line 317, in execute_query
    if c_where is None or c_where(context):
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/query_compile.py", line 121, in __call__
    return self.operator(self.left(context), self.right(context))
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/query_compile.py", line 181, in __call__
    arg_right = self.right(context)
  File "/home/muks/venv.fava/lib64/python3.9/site-packages/beancount/query/query_env.py", line 409, in __call__
    open_entry, _ = context.open_close_map[args[0]]
KeyError: 'Equity:Conversions:Current'
beancount> 

Using:

(venv.fava) $ pip list | grep bean
beancount                2.3.5
(venv.fava) $

which is the current version that pip installed.

[muks]

Explicitly opening the virtual accounts caused the exception to go away:

2021-04-01 open Equity:Conversions:Current
2021-04-01 open Equity:Earnings:Current

[muks]

Updated sample Beancount input file:

option "title" "Company Private Limited"
option "operating_currency" "SGD"
option "inferred_tolerance_default" "*:0.01"
option "account_rounding" "Equity:RoundingError"

2021-04-01 custom "fava-option" "fiscal-year-end" "03-31"
2021-04-01 open Equity:RoundingError
2021-04-01 open Equity:Conversions:Current
2021-04-01 open Equity:Earnings:Current

2021-04-02 open Assets:Current:Bank SGD
2021-04-02 open Assets:Current:Prepayments:Non-Monetary:Vendor1 USD
  value-at-cost: 1

2021-04-02 open Assets:Current:Prepayments:Monetary:Vendor2 USD

2021-04-02 * "Transaction 1"
  Assets:Current:Bank
  Assets:Current:Prepayments:Non-Monetary:Vendor1 14.00 USD {1.3612 SGD}

2021-04-02 * "Transaction 2"
  Assets:Current:Bank
  Assets:Current:Prepayments:Monetary:Vendor2 14.00 USD {1.3612 SGD}

2022-03-31 price USD 1.3534 SGD

Results of bean-query queries:

beancount> select sum(cost(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE 'value-at-cost' IN OPEN_META(account) AND account ~ '^Assets';
sum_cost_po
-----------
19.0568 SGD
beancount> select sum(value(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE NOT 'value-at-cost' IN OPEN_META(account) AND account ~ '^Assets';
sum_value_po
------------
-19.1724 SGD
beancount> select sum(value(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE account ~ '^Assets';
sum_value_p
-----------
-0.2248 SGD
beancount> select (19.0568 + (-19.1724));
add_c19
-------
-0.1156
-0.1156
-0.1156
-0.1156
beancount> 

The difference between -0.2248 and -0.1156 is what we want to avoid in the statement of financial position.

[blais]

I think this is custom enough that it is squarely justified to write a script for it.

On Sat, Aug 13, 2022, 19:57 Mukund Sivaraman @.***> wrote:

Updated sample Beancount input file:

option "title" "Company Private Limited" option "operating_currency" "SGD" option "inferred_tolerance_default" "*:0.01" option "account_rounding" "Equity:RoundingError"

2021-04-01 custom "fava-option" "fiscal-year-end" "03-31" 2021-04-01 open Equity:RoundingError 2021-04-01 open Equity:Conversions:Current 2021-04-01 open Equity:Earnings:Current

2021-04-02 open Assets:Current:Bank SGD 2021-04-02 open Assets:Current:Prepayments:Non-Monetary:Vendor1 USD value-at-cost: 1

2021-04-02 open Assets:Current:Prepayments:Monetary:Vendor2 USD

2021-04-02 * "Transaction 1" Assets:Current:Bank Assets:Current:Prepayments:Non-Monetary:Vendor1 14.00 USD {1.3612 SGD}

2021-04-02 * "Transaction 2" Assets:Current:Bank Assets:Current:Prepayments:Non-Monetary:Vendor1 14.00 USD {1.3612 SGD}

2022-03-31 price USD 1.3534 SGD

Results of bean-query queries:

beancount> select sum(cost(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE 'value-at-cost' IN OPEN_META(account) AND account ~ '^Assets'; sum_cost_po

38.1136 SGD beancount> select sum(value(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE NOT 'value-at-cost' IN OPEN_META(account) AND account ~ '^Assets'; sum_value_po

-38.12 SGD beancount> select sum(value(position)) FROM CLOSE ON 2022-04-01 CLEAR WHERE account ~ '^Assets'; sum_value_p

-0.2248 SGD beancount> select (38.1136 + (-38.12)); add_c38

-0.0064 -0.0064 -0.0064 -0.0064 beancount>

The difference between -0.2248 and -0.0064 is what we want to avoid in the statement of financial position.

— Reply to this email directly, view it on GitHub https://github.com/beancount/beanquery/issues/108#issuecomment-1214198166, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACSBE7RSJZPIFXKLMQV6PDVY7OZTANCNFSM56OMJKKA . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[muks]

I think this is custom enough that it is squarely justified to write a script for it.

I've done that for our purpose now. It makes 2 queries, one which sum(value(position, date)) WHERE NOT 'value-at-cost' IN OPEN_META(account) and another which sum(cost(position)) WHERE 'value-at-cost' IN OPEN_META(account), and we add both together for the final value. It uses a Python helper method that performs the 2 queries for every grouping in the financial statements and returns the combined sum. This is then rendered along with several other things via the Jinja2 templating library which generates the LaTeX source for the management report.

Although I understand that you would like to keep Beancount simple, it would have been nice to have as a single function. :) But looking at Position's definition, I guess it would be some work to implement.

[blais]

I meant you could forego calling the sql code at all and just use a loop over your transactions and add to a mapping of Inventory objects.

This works though, glad to hear you found a solution.

On Sun, Aug 14, 2022, 07:21 Mukund Sivaraman @.***> wrote:

I think this is custom enough that it is squarely justified to write a script for it.

I've done that for our purpose now. It makes 2 queries, one which sum(value(position, date)) WHERE NOT 'value-at-cost' IN OPEN_META(account) and another which sum(cost(position)) WHERE 'value-at-cost' IN OPEN_META(account), and we add both together for the final value. It uses a Python helper method that performs the 2 queries for every grouping in the financial statements and returns the combined sum. This is then rendered along with several other things via the Jinja2 templating library which generates the LaTeX source for the management report.

Although I understand that you would like to keep Beancount simple, it would have been nice to have as a single function. :) But looking at Position's definition, I guess it would be some work to implement.

— Reply to this email directly, view it on GitHub https://github.com/beancount/beanquery/issues/108#issuecomment-1214288216, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACSBEZVZOMJC3FIXNWTY4LVZB66JANCNFSM56OMJKKA . You are receiving this because you commented.Message ID: @.***>

[dnicolodi]

I don't see beanquery gaining a function to compute something like this. However, in the medium term, the capability to add user-defined functions to the query language will be added, and in the long run, support for sub-queries may evolve to be enough to compute something like this.