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)
On Sat, Feb 4, 2023 at 9:05 AM Oon-Ee Ng <[email protected]> wrote:
> 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/CAGQ70evec33fNBRWj-Kextse_6fh_XUtXpZ2eSNrQqx-eJ%2ByuA%40mail.gmail.com.
