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 On Sat, May 29, 2021 at 8:33 PM Mark Lodato <[email protected]> wrote: > 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. > > On Sat, May 29, 2021 at 7:48 PM Mark Lodato <[email protected]> wrote: > >> On Sat, May 29, 2021 at 4:33 PM Ben Blount <[email protected]> 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 < >> [email protected]> 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 >> >> >>> >>> On May 29, 2021 10:33:34 PM GMT+02:00, Ben Blount <[email protected]> wrote: >>>> >>>> Have you tried FROM CLOSE ON <date> >>>> >>>> See https://beancount.github.io/docs/beancount_query_language.html for >>>> more on how this works. >>>> >>>> On Sat, May 29, 2021, 13:12 Mark Lodato <[email protected]> wrote: >>>> >>>>> 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! >>>>> >>>>> On Sat, May 29, 2021, 4:02 PM Mark Lodato <[email protected]> wrote: >>>>> >>>>>> 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 >>>>>> >>>>>> -- >>>>>> 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 >>>>>> [email protected]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/beancount/a1cfbd22-e495-40e8-b6ea-055fd9227d47n%40googlegroups.com >>>>>> <https://groups.google.com/d/msgid/beancount/a1cfbd22-e495-40e8-b6ea-055fd9227d47n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>>>> 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 [email protected]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/beancount/CAHREChjkG%3Dy7pBKOuYMC4m6OzHfsx-4uTDdWx3DXLksFExdW2g%40mail.gmail.com >>>>> <https://groups.google.com/d/msgid/beancount/CAHREChjkG%3Dy7pBKOuYMC4m6OzHfsx-4uTDdWx3DXLksFExdW2g%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>> 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 >>> [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/beancount/943AD439-FF54-4206-AE77-11BA51B086A9%40ch.tario.org >>> <https://groups.google.com/d/msgid/beancount/943AD439-FF54-4206-AE77-11BA51B086A9%40ch.tario.org?utm_medium=email&utm_source=footer> >>> . >>> >> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAHREChjRUWg6EF4RpKzSFGgCLyrTTAW3MSMWsui4vSc0StwqNA%40mail.gmail.com.
