I was also incorrect about this being undocumented. It is found within the CLI command "help", not the doc <https://beancount.github.io/docs/beancount_query_language.html>. 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.
On Sat, May 29, 2021 at 8:52 PM Mark Lodato <[email protected]> wrote: > 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/CAHREChivO--sKruNfUHrg%3D0pD_YUvNF87Jv30A%3DQJ4UZkHGrsg%40mail.gmail.com.
