Custom script that iterates postings with `open on` and `close on` dates

[Aaron Stacy]

HI all! I'd like to make a customized income statement where some accounts are added together. For example, I'd like to know all income for the past month, whether it was Income:Hooli:Salary or Income:Hooli:RSU's. I can get pretty close with the query:

    select account, sum(position) from open on 2020-12-01 close on 2020-12-31 where account ~ "Income" group by account order by account;

But then I need to go through and sum the specific accounts I happen to care about. I'm comfortable importing beancount, using it to parse my ledger, and iterating through expenses, but can someone point me to what I'd do to get the open on/close on part?

Thanks!

[Ben Blount]

Here are some examples showing modifying a journal based on a query: Delete entry | Rename account.

It takes a full beancount SQL query and executes it on a journal. You could use that part and strip out the parts that stage changes to the journal.

You could run the query you've got there and then do some postprocessing to sum up the accounts accordingly.

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/beancount/26067fa0-0a9a-41ec-9b5d-2f62c9714ff0n%40googlegroups.com.

[Martin Blais]

If you're only interested in income just filter the date interval you need.

The "close" operation is essentially there to ensure that the balances of the assets/liabilities accounts are correct when you filter out transactions from the past, i.e., it just inserts a transfer from all of the past's net income to the balance sheet accounts at the head of the filtered transactions.

--

[Aaron Stacy]

Thanks Martin and Ben!

The following gives me precisely what I need:

    select number(only("USD", sum(position)))

    from open on 2020-10-01 close on 2020-11-01

    where

        account ~ "My:Checking" and

        number(units(position)) > 0 and

        findfirst("Income:Hooli", other_accounts) != NULL

EXCEPT I'd like a number in Python instead of text output in the terminal. Cribbing from Ben's examples, I came up with the following to run it from Python:

    from beancount.query.query_compile import compile

    from beancount.query.query_env import FilterPostingsEnvironment, FilterEntriesEnvironment, TargetsEnvironment

    from beancount.query.query_execute import execute_query

    from beancount.query.query_parser import Parser

    from beancount.loader import load_file

    entries, errors, options = load_file('my.beancount')

    text = '... the query from above ...'

    ast = Parser().parse(text)

    compiled = compile(ast, TargetsEnvironment(), FilterPostingsEnvironment(), FilterEntriesEnvironment())

    columns, results = execute_query(compiled, entries, options)

Thanks again for taking the time to help!

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/_XEernfER5w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNCseoKOVRPFUgpmKg3UM1VmcKonEDZA_Ck%2BCTrc%2BbhRA%40mail.gmail.com.

[Martin Blais]

Use run_query() instead:

https://github.com/beancount/beancount/blob/master/beancount/query/query.py#L13

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CACjABkm2Gh3VXoJinXDMw224hSD%2B6z1LCQK_gRN3WsCVut7JzQ%40mail.gmail.com.

[Aaron Stacy]

🙇‍♂️

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhMnuezFxeoTJ6%2BDEat-njGpFNges2s9gPmEg3T5pg25pg%40mail.gmail.com.