Query to give converted aggregate balance on date?

[Mark Lodato]

I can't figure out how to write a query that prints the aggregate balance of an account subtree as of a given date, converted to USD using the price as of that date. Example data:

$ cat >test.bean <<EOF

plugin "beancount.plugins.auto_accounts"

2020-01-01 * "Open"

  Assets:Bank:AAA 100 AAA

  Assets:Bank:BBB 100 BBB

  Income:Work

2021-01-01 price AAA 1 USD

2021-01-01 price BBB 10 USD

2021-01-02 price AAA 2 USD

2021-01-02 price BBB 20 USD

2021-01-03 price AAA 3 USD

2021-01-03 price BBB 30 USD

EOF

$ bean-query test.bean 'SELECT convert(units(sum(position)), "USD") AS value WHERE account ~ "Assets:Bank"'

 value

--------

3300 USD

I'd like to be able to specify a date and get the balance at the end of that date, e.g. 2021-01-02 => 2200 USD. I tried using "FROM DATE = 2021-01-02" or "FROM DATE <= 2021-01-02" but it doesn't affect the result.

Thank you in advance,

Mark

[Mark Lodato]

Oops, I sent out that email in haste. I didn't mean it for it to sound so demanding.

If anyone wouldn't mind helping construct such a query, I'd appreciate it!

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/yCPa_tlF2-4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/a1cfbd22-e495-40e8-b6ea-055fd9227d47n%40googlegroups.com.

[Ben Blount]

Have you tried FROM CLOSE ON <date>

See https://beancount.github.io/docs/beancount_query_language.html for more on how this works.

You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAHREChjkG%3Dy7pBKOuYMC4m6OzHfsx-4uTDdWx3DXLksFExdW2g%40mail.gmail.com.

[Patrick Ruckstuhl]

This is part of my query


select
number(only("CHF", convert(sum(value(position, #"2020-12-31")), "CHF", #"2020-12-31"))) as value,
number(only("CHF", convert(sum(cost(position)), "CHF", #"2020-12-31"))) as cost,
account
where
account ~ "(Assets|Liabilities):"
and year <= 2020

That gives both cost as well as value on a specific date

[Mark Lodato]

On Sat, May 29, 2021 at 4:33 PM Ben Blount <b...@bben.us> wrote:

Have you tried FROM CLOSE ON <date>

See https://beancount.github.io/docs/beancount_query_language.html for more on how this works.

Yes I have, and it does not work.

$ bean-query test.bean 'SELECT convert(units(sum(position)), "USD") AS value FROM CLOSE ON 2021-01-02 WHERE account ~ "Assets:Bank"'
 value
--------
3300 USD

 

The documentation leaves a lot to be desired, unfortunately. For example, the "convert()" function is entirely undocumented.

On Sat, May 29, 2021 at 4:44 PM 'Patrick Ruckstuhl' via Beancount <bean...@googlegroups.com> wrote:

This is part of my query


select
number(only("CHF", convert(sum(value(position, #"2020-12-31")), "CHF", #"2020-12-31"))) as value,
number(only("CHF", convert(sum(cost(position)), "CHF", #"2020-12-31"))) as cost,
account
where
account ~ "(Assets|Liabilities):"
and year <= 2020

That gives both cost as well as value on a specific date

Perfect. Thanks! That's what I was missing. The convert function takes a third argument that is a date.

$ bean-query test.bean 'SELECT convert(units(sum(position)), "USD", #"2021-01-02") AS value WHERE account ~ "Assets:Bank"'
 value
--------
2200 USD

 

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/943AD439-FF54-4206-AE77-11BA51B086A9%40ch.tario.org.

[Mark Lodato]

Sadly, no, that didn't do it. I'm surprised that this is so difficult to do. I would have expected more people to want to know a balance on given date.

$ cat >test.bean <<EOF
plugin "beancount.plugins.auto_accounts"
2020-01-01 * "Open"
  Assets:Bank:AAA 100 AAA
  Assets:Bank:BBB 100 BBB
  Income:Work
2021-01-01 price AAA 1 USD
2021-01-01 price BBB 10 USD

2020-01-15 * "Contribute"
  Assets:Bank:AAA 10 AAA
  Assets:Bank:BBB 10 BBB
  Income:Work
2021-02-01 price AAA 2 USD
2021-02-01 price BBB 20 USD
2021-03-01 price AAA 3 USD
2021-03-01 price BBB 30 USD

EOF

$ bean-query test.bean 'SELECT convert(units(sum(position)), "USD", #"2021-01-01") AS value FROM CLOSE ON 2021-01-01 WHERE account ~ "^Assets:Bank"'

 value
--------
1210 USD

The result should be 1100. The third argument to convert() uses the right price, but I can't get the right balance. Using date <= 2021-01-01 has the same result.

[Mark Lodato]

Doh! The problem was with my sample data, which used 2020 instead of 2021. Using 2021 everywhere makes the following query work:

$ bean-query test.bean 'SELECT convert(units(sum(position)), "USD", #"2021-01-01") AS value FROM date <= 2021-01-01 WHERE account ~ "^Assets:Bank"'
 value
--------
1100 USD

[Mark Lodato]

I was also incorrect about this being undocumented. It is found within the CLI command "help", not the doc. That's what threw me for a loop - the doc mentions the "help" command in passing at a few random places. It would help to raise visibility by putting a reference at the top of the doc and mentioning in --help.