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/CAHREChg9heGx-nhSp1vx6WgyQiPY3cM2he-M7E5tq23%3DQQJ_qg%40mail.gmail.com.
