Allow a balance column by referencing another column, sum up position converted at original rate

[blais]

trafficstars

Original report by Martin Michlmayr (Bitbucket: tbm13, GitHub: tbm).

---

I posted this to the beancount list and Martin said this isn't supported at the moment. Opening a ticket.

I have expenses in one currency (EUR) and want to convert all of them to another (USD) on the day of the transaction.

This gives the correct result because I pass "date" to CONVERT():

beancount> SELECT sum(convert(position, "USD", date)) WHERE account ~ '^Assets:A';
sum_conver
----------
107.50 USD

But I'd like to see a "register" type view with each individual transaction and the total.

Test case:

plugin "beancount.plugins.implicit_prices"

2000-01-01 open Assets:A
2000-01-01 open Assets:B

2018-12-05 * "Test 1"
    Assets:A                20 EUR @ 1.10 USD
    Assets:B

2018-12-06 * "Test 2"
    Assets:A                45 EUR {1.14 USD} @ 1.14 USD
    Assets:B

2018-12-07 * "Test 3"
    Assets:A                30 EUR {1.14 USD}
    Assets:B

[blais]

Original comment by Martin Michlmayr (Bitbucket: tbm13, GitHub: tbm).

---

Martin Blais' response was:

This is a great use case. I'm afraid this is beyond the current capabilities of the automatically generated "balance" column. We'll have to save that one for the next version and think about a syntax that would allow one to create a balance column by referencing another column. e.g.,

SELECT position, SUM(convert(position, "USD", date)) as usd_amount,
BALANCE(usd_amount) as usd_balance WHERE ...

[dnicolodi]

The way this is done in regular SQL is with window functions, see for example https://www.sqlite.org/windowfunctions.html

CREATE TABLE test (a INTEGER);
INSERT INTO test VALUES (1), (1), (1), (1), (1);
SELECT a, sum(a) OVER (ROWS UNBOUNDED PRECEDING) FROM test;

The balance column currently implemented is a very simplified version of the same that unconditionally returns the equivalent of sum(position) OVER (ROWS UNBOUNDED PRECEDING). Supporting arbitrary window functions may be quite a lot of work, but supporting the most common versions could be very useful, in particular the UNBOUNDED PRECEDING one.

A balance() function could be introduced as a short-hand for the longer form. A catch would be the order in which the values are added. Window functions process the row in arbitrary order, unless an ORDER BY is provided in the OVER clause. For balance() the most natural order would be the order specified for the select, see #50. I don't think there is a standard SQL way to express this.

Implementing balance() is not very difficult, but having it operate on the rows in the order specified by the select's ORDER BY requires implementing some form of deferred evaluation and two pass evaluation of the query results.