Still have the same query (same time of the year so I'm revisiting the same thing). Last year I just manually calculated the monthly averages through copy pasting and the inserting the 'skipped' dates.
On Sun, Feb 6, 2022 at 10:55 AM Oon-Ee Ng <[email protected]> wrote: > 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/CAGQ70etoW4fsH4ZZC_nKeoOPbXZus4%3DzJ3%2BxMoFQ3tLy0-tm_g%40mail.gmail.com.
