journal functions: compute running average in balance / apply filter only to printing output

[freak...@gmail.com]

Hi,

I'm coming from ledger-cli which provides two options/functions which I use quite extensively and for which I didn't find an equivalent in beancount:

1. Option --average in ledger-cli shows the running average instead of the running total. I use this like this:
     > ledger --period "this year" --monthly --average --collapse register Expenses Income
   This gives a nice overview of the average net income on a monthly basis for the current year. I find this very useful to get a feeling if things go in the right/wrong direction.
   
   
2. 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.

Is it possible to achieve a similar output with beancount?

Thank you and kind regards,

Fred

[Justus Pendleton]

On Monday, May 27, 2019 at 1:45:18 PM UTC+7, frea...@gmail.com 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' "

?

I don't think there's an easy way to get an average for your other question. You could do

bean-query --numberify -f csv my.bean "select date,balance from OPEN ON 2019-05-01 WHERE account ~ 'Assets:Checking'" | script-that-does-math

and calculate the time-weighted average. Though that's clearly not nearly as convenient as ledger having it built in.

[freak...@gmail.com]

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, frea...@gmail.com 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.

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?

[Martin Blais]

On Fri, May 31, 2019 at 8:51 AM <freak...@gmail.com> 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, frea...@gmail.com 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 beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/7f198891-02a2-4475-bfb1-c4356f5d42b8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Oon-Ee Ng]

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)?

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNHhCozqcUiGbhwZ4u5kjmda-OvPkTdaeQ4E8%3DpcLw6QA%40mail.gmail.com.

[Oon-Ee Ng]

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.

[Oon-Ee Ng]

In the absence of replies, I decided to rely on what I know better (python/pandas) and have done the following. Leaving it here in case it helps anyone else.

#!/usr/bin/env python3

import datetime
import sys

import matplotlib.pyplot as plt
import matplotlib
import numpy as np
import pandas as pd

from beancount import loader
from beancount.query import query

BEANCOUNT_FILE = 'MyAccount.bc
entries, errors, options = loader.load_file(BEANCOUNT_FILE,
                                            log_errors=sys.stderr)
if len(errors) > 0:
    exit(1)

startdate = datetime.datetime.now() + datetime.timedelta(days=-183) # 6 months
year = startdate.year
month = startdate.month if startdate.month > 9 else "0" + str(startdate.month)

query_str = """SELECT date, LAST(balance)
FROM OPEN ON {}-{}-02
WHERE account ~ 'Assets:Banking:Account:To:Check'
""".format(year, month)

rtypes, rrows = query.run_query(entries, options, query_str)

data = {}
for row in rrows:
    pos = row.last_balance.get_only_position()
    amount = float(pos.units.number) if pos else float(0.0)
    data[row.date] = amount

df = pd.DataFrame.from_dict(data, orient='index', columns=['balance'])
df.index = pd.to_datetime(df.index)  # Convert index to be datetime

# Insert appropriate values for start of the first month
# and end of the last month
first_day = datetime.datetime(year=int(year), month=int(month), day=1)
if first_day not in df.index:
    df.loc[first_day] = df.iloc[0]
last_day = (
             (datetime.datetime.now().replace(day=28)
              + datetime.timedelta(days=4)).replace(day=1)
            + datetime.timedelta(days=-1))
if last_day not in df.index:
    df.loc[last_day] = df.iloc[0]
   
filled_df = df.resample("D").ffill() # Fill days without balances
print(filled_df.to_string())  # Balance of every day (for error checking)
average_balances = filled_df.resample("M").mean()
print(average_balances)  # Monthly Average Balance for the last 6 months)