Looking at this now, and what I have so far is something like this:- bean-query account.bean "select date, LAST(balance) from OPEN on 2022-01-01 WHERE account ~ 'Assets:Checking' and date <= 2022-01-31"
This gives me the last balance of the day for each day (dates are one offset due to how it works with LAST and OPEN but that's a minor issue). My objective is to get the 'average monthly balance' (which is what my bank uses to qualify me for some products) with as little manual calculations as possible. Is this do-able as is, or should my next step be to take the output of the above and do my own calculations? In particular, if the functionality is not built-in, is there any way I can ensure the query returns one balance per date (right now dates without transactions/postings are skipped)? On Sat, Jun 1, 2019 at 11:46 AM Martin Blais <[email protected]> wrote: > On Fri, May 31, 2019 at 8:51 AM <[email protected]> wrote: > >> >> Thank you for the reply! >> >> Am Montag, 27. Mai 2019 17:32:21 UTC+2 schrieb Justus Pendleton: >>> >>> On Monday, May 27, 2019 at 1:45:18 PM UTC+7, [email protected] wrote: >>> >>>> >>>> 1. Option --display in ledger-cli allows to select a period of time >>>> to which the output should be limited. This only affects the printing of >>>> results but does not apply a filter for selecting the data from the >>>> ledger. >>>> I use it like this: >>>> > ledger --display "d>=[this month]" register Assets:Checking >>>> This gives me all activities on my checking account for the current >>>> month. The last column showing the total equals the balance of my actual >>>> bank account. This makes the comparison between my ledger file and my >>>> bank >>>> account quite easy. >>>> >>>> I've never used ledger but is this the same (or similar) output as you >>> get from bean-query with something like: >>> >>> bean-query my.bean "select date,flag,payee,narration,position,balance >>> from OPEN ON 2019-05-01 WHERE account ~ 'Assets:Checking' " >>> >> >> Your query is roughly the same -- but instead of the running total the >> running average is calculated in my example. >> > > You'll have to write a Python script to accomplish this for now, as it is > not yet supported by the SQL client. > > > >> >> As for the other question: is it possible to limit the output of the >> journal to the current month but include all transactions in the >> calculation of the running total? >> > > Yes > See OPEN and CLOSE clauses from the SQL statement docs or search on the > mailing-list. > Basically the way this works is that it truncates all the transactions > from before your opening date and replaces them by an equlvalent one to > bring up the account balances to their true amount. > See here for details: > http://furius.ca/beancount/doc/intro > > > > >> -- >> 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 post to this group, send email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/beancount/7f198891-02a2-4475-bfb1-c4356f5d42b8%40googlegroups.com >> <https://groups.google.com/d/msgid/beancount/7f198891-02a2-4475-bfb1-c4356f5d42b8%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> For more options, visit https://groups.google.com/d/optout. >> > -- > 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 post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/CAK21%2BhNHhCozqcUiGbhwZ4u5kjmda-OvPkTdaeQ4E8%3DpcLw6QA%40mail.gmail.com > <https://groups.google.com/d/msgid/beancount/CAK21%2BhNHhCozqcUiGbhwZ4u5kjmda-OvPkTdaeQ4E8%3DpcLw6QA%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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/CAGQ70eu8C4v5ZOzRJdY3Edju0Nt-MVVzYqjRLcbpcgbGn7_axA%40mail.gmail.com.
