Support arithmetic operations on Amounts

[dnicolodi]

In a mailing list thread https://groups.google.com/g/beancount/c/4EkTUWoLXwI and in beancount/beancount#688 it was proposed to add the possibility of performing arithmetic operations on Amounts. The only operations that make sense are multiplication and division of amount by an integer or a decimal, and addition and subtraction of amounts.

Multiplication and division by an integer or a decimal are not an issue as these operations always succeed and return another Amount object.

For sums and differences the problem is a bit more complex. What to do when amounts with incompatible commodities are summed or subtracted? Following the SQL philosophy, we do not want to raise an exception based on the data involved in the query. The only sensible thing is to return NULL. However this would be dangerous, because one very common thing to do is to sum these values into an Inventory, and in this case the problem would not be evident at all.

@IguanaBen, what is your use case for addition and subtractions of Amounts in BQL?

[zacchiro]

I can provide a couple of use cases too.

For profit & loss tracking sometimes I have explicit "PnL" accounts; sometimes I have separate accounts (e.g., Income:Foo-gain and Expenses:Foo-loss). In the latter case a common operation for me is having to add together Foo-gain and Foo-loss amounts.

Another one is related to investments, where you generally have the book value and the current market value, and want to generate an additional column in your BQL output with the difference between the two.

[dnicolodi]

I realize that some of my thought on the matter didn't go through the keyboard.

My main reserve is that in other parts of the code, when amount are added, the resulting type is an Inventory. I am not sure deviating from this is a good idea. Would your use cases be served if amountA + amountB would result in an Inventory like sum(amountA) does? If not, is amount(1.23, 'USD') + amount(4.56, 'EUR') = NULL a reasonable behavior?

Also, from these examples, it seems that what you really want to add or subtract are not Amounts but Postings. We can support this too, but this needs to return an Inventory.

Finally, subtracting Amounts is already possible, just cumbersome: amount(number(amountA) + number(amountB), commodity(amountA)). The fact that is cumbersome however, has the advantage that users doing this are paying more attention to what they are doing.

Edit: I realized that the amount(number, commodity) constructor exists only in a commit in my local repository. I'll be pushing it out shortly.

[IguanaBen]

I agree with @zacchiro for the use-cases. Also separate Gain/Loss accounts are often used for GST/HST to track credits and expenses separately but often you want to see the net between the two.

From a user-perspective, I think adding different currencies should result in an error as this is a request the requires more information. Upon an error, the user could use Convert() to explicitly state what currency they want and how to convert to it. Also the error could be specific and informative as to how to fix it.

But if we want to avoid raising an exception, I think returning NULL is as reasonable as anything else.

I think it would be weird to add two Amounts and not have that return an Amount.

The amount(number,commodity) function does give a work-around for this functionality, which is nice. But it would be easy for a bad query to not handle different commodities right when adding them.

[dnicolodi]

From a user-perspective, I think adding different currencies should result in an error as this is a request the requires more information.

Adding amounts with different currencies is not something that can be detected at query compilation time and returning an error during execution of the query is something I absolutely want to avoid.

I think it would be weird to add two Amounts and not have that return an Amount.

When you do SELECT sum(cost(position)) you get an Inventory back despite the fact that you are adding Amounts, and this is not going to change, I think. I don't know what would be more confusing: having sum() behave differently than + or having a different type returned. For example, division of integers returns a Decimal and no one is surprised.

Having a minimal ledger and an example query that uses this hypothetical functionality would help greatly to decide how it should look like.

[blais]

On Fri, Apr 8, 2022 at 10:52 AM Daniele Nicolodi @.***> wrote:

From a user-perspective, I think adding different currencies should result in an error as this is a request the requires more information.

Adding amounts with different currencies is not something that can be detected at query compilation time and returning an error during execution of the query is something I absolutely want to avoid.

I think it would be weird to add two Amounts and not have that return an Amount.

When you do SELECT sum(cost(position)) you get an Inventory back despite the fact that you are adding Amounts, and this is not going to change, I think. I don't know what would be more confusing: having sum() behave differently than + or having a different type returned. For example, division of integers returns a Decimal and no one is surprised.

(Sorry I'm coming in late and partially on this discussion.) Aggregating amounts or positions naturally should result in an inventory because there may be multiple currencies involved. That's expected and normal. What may be needed is better ways to reduce inventories to single amounts, along with error reporting when it's impossible to do so.

Having a minimal ledger and an example query that uses this hypothetical

functionality would help greatly to decide how it should look like.

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

[dnicolodi]

Indeed. The question in this context is whether amountA + amountB should be an Amount or an Inventory. I see reasons for going either way. Right now I'm leaning toward returning an Amount or NULL if the currency is not the same.

[blais]

I think it should always be an inventory. Or perhaps you could support another type of addition, one that throws and error if the currencies mismatch.

[dnicolodi]

Not a big fan of raising errors from query execution: it becomes a mess to understand from which table row the error comes from. I am thinking about making account addition to return an inventory and add a reduce() function that return the only amount in an inventory or NULL if there are more than one. Ideas on a better name are welcome.

[ericaltendorf]

Another potentially relevant example, FWIW:

I'm producing tables for reporting capital gains. Right now I'm trying out producing an "inventory" table at quarter boundaries, as well as a "disposals" table of assets disposed (sold, reduced) during the quarter. The idea is to be able to match each disposal during the quarter to an entry in the beginning-of-quarter inventory, and then verify that is missing from the end-of-quarter inventory.

The inventories list the per-unit cost for each position. So to make it easy for a human to match, I'd like the disposals table to list the per-unit cost for each disposal. Right now I'm selecting

number(cost(position)) / number(units(position)) as CostEach

which works but feels like a hack.

PS: agreed that raising errors from query execution should be avoided if possible.

PPS: multiplication and division of Amounts by other Amounts makes sense if you have a full units algebra; 4 apples divided by 2 dollars equals 2 apples/dollar; 10 dollars times 2 apples/dollar equals 20 apples; 100 apples divided by 2 apples/dollar equals 50 dollars, and 2 apples/dollar divided by 100 yen/dollar equals 0.02 apples/yen. And those are really what prices and exchange rates actually are... Might be worth thinking about whether generalizing prices to general units algebra would elegantly solve some problems (and more so than it would introduce complexity and opportunity for error)