Aggregate balance on "parent" accounts

[grant....@gmail.com]

Hi

I am starting with Beancount and plaintext accounting in general so, please, forgive me if I am asking a silly question.

I've generated an example beancount file proceeded to issue various commands within bean-report. 

For example if I generate balance report I see this

bean-report  ~/example.beancount bal

.....

Expenses:Financial:Commissions                  331.15  USD

Expenses:Financial:Fees                         132.00  USD

Expenses:Food:Alcohol                            25.43  USD

Expenses:Food:Coffee                             87.25  USD

Expenses:Food:Groceries                        6613.79  USD

Expenses:Food:Restaurant                      12494.94  USD

Expenses:Health:Dental:Insurance                205.90  USD

Expenses:Health:Life:GroupTermLife             1726.72  USD

Expenses:Health:Medical:Insurance              1943.98  USD

Expenses:Health:Vision:Insurance               3003.30  USD

Expenses:Home:Electricity                      2080.00  USD

Expenses:Home:Internet                         2560.75  USD

Expenses:Home:Phone                            1942.67  USD

Expenses:Home:Rent                            76800.00  USD

....

Is there a way to display aggregate balances on "parent" accounts like Expenses:Home and Expenses:Food (they don't exist in the file, but I can open them any time)? Is it even possible?

If it is not possible within bean-report I am ok with bean-query, but I need a hint on this.

Thanks.

[Martin Blais]

You can do something like this with bean-query:

bergamot:~$ bean-query $L 'select parent(account), units(sum(position)) group by 1 order by 1'

It's not perfect, because this will remove exactly one level of account.

Ideally one might want some other scheme, e.g. "max depth 2 levels".

I could easily provide a function like that, or some other ones.

Let me know what you think would be useful.

The SQL shell is an evolving beast and a bit of a prototype, in use so we can find what people use most, and to find out if it can replace the custom reports entirely (I think it can). For that reason, it's the one area where I'm super liberal about adding new stuff.

 

Thanks.

--
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+unsubscribe@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/ca2ca60e-7749-4a9a-a465-caa4c4b33935%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[redst...@gmail.com]

Also: try fava if you haven't (github.com/aumayr/fava). It's an excellent web frontend for beancount. It has a configurable display for aggregate parent balances.

[rac...@rachelblum.com]

Necromancing an old thread... This is almost what I'm looking for, but I'm trying to do rollup reporting - IOW, balances are added up the tree. (This is what ledger does in its 'balance' reporting)

I've provided a sample beancount file at the bottom to help illustrate what I mean. Basically, flight expenses *mostly* broken up by carrier, but some go directly into Expenses:Flights. 

The ledger balance report for this set of transactions is

            $3550.00  Assets:Checking

           $-7000.00  Equity:Opening Balances

            $3450.00  Expenses:Flight

             $400.00    AK

            $3000.00    UA

--------------------

                   0

That's essentially what I'm looking for. I know, it's not actually a balance sheet, but it lets me see spending both in total, and broken up by categories. bean-report delivers an actual balance report instead:

Assets:Checking           3550.00 USD

Equity:Opening-Balances  -7000.00 USD

Expenses:Flights            50.00 USD

Expenses:Flights:AK        400.00 USD

Expenses:Flights:UA       3000.00 USD

Income

Liabilities

It lacks any summation. (I'm actually happy with that, because I'd prefer a balance report to actually balance). 

Choosing your approach, I get:

 parent_account  units_sum_po

---------------- ------------

Assets            3550.00 USD

Equity           -7000.00 USD

Expenses            50.00 USD

Expenses:Flights  3400.00 USD

Which has *moved* the $50 from Expenses:Flights to Expenses. Not the intended effect :)

I don't think the current query language supports that, does it? I'll have to write a reporting module?

[rac...@rachelblum.com]

Sigh. Forgot to include the example file:

2019-01-01 open Assets:Checking

2019-01-01 open Expenses:Flights

2019-01-01 open Expenses:Flights:UA

2019-01-01 open Expenses:Flights:AK

2014-01-01 open Equity:Opening-Balances

2019-01-02 * "Deposit"

  Assets:Checking           7000.00 USD

  Equity:Opening-Balances

2019-02-01 * "United"

  Expenses:Flights:UA  3000.00 USD

  Assets:Checking

2019-02-01 * "Alaska"

  Expenses:Flights:AK  400.00 USD

  Assets:Checking

2019-02-02 * "No-name air"

  Expenses:Flights  50.00 USD

  Assets:Checking

[Martin Blais]

On Thu, Feb 28, 2019, 22:20 <rac...@rachelblum.com> wrote:

Necromancing an old thread... This is almost what I'm looking for, but I'm trying to do rollup reporting - IOW, balances are added up the tree. (This is what ledger does in its 'balance' reporting)

I've provided a sample beancount file at the bottom to help illustrate what I mean. Basically, flight expenses *mostly* broken up by carrier, but some go directly into Expenses:Flights. 

The ledger balance report for this set of transactions is

            $3550.00  Assets:Checking

           $-7000.00  Equity:Opening Balances

            $3450.00  Expenses:Flight

             $400.00    AK

            $3000.00    UA

--------------------

                   0

That's essentially what I'm looking for. I know, it's not actually a balance sheet, but it lets me see spending both in total, and broken up by categories.

That's always bothered me about this ledger output, that by default the sums don't balance. It would be much less confusing for it to display those in a different column. But it can be useful indeed.

bean-report delivers an actual balance report instead:

Assets:Checking           3550.00 USD

Equity:Opening-Balances  -7000.00 USD

Expenses:Flights            50.00 USD

Expenses:Flights:AK        400.00 USD

Expenses:Flights:UA       3000.00 USD

Income

Liabilities

It lacks any summation. (I'm actually happy with that, because I'd prefer a balance report to actually balance). 

Choosing your approach, I get:

 parent_account  units_sum_po

---------------- ------------

Assets            3550.00 USD

Equity           -7000.00 USD

Expenses            50.00 USD

Expenses:Flights  3400.00 USD

Which has *moved* the $50 from Expenses:Flights to Expenses. Not the intended effect :)

I don't think the current query language supports that, does it? I'll have to write a reporting module?

I'm not understanding what you desire precisely. Just above you mention the roll-up is what you want. Confused.

If what you need is roll-ups with an irregular structure, it's not obvious. You could in theory write a function returning the parent account to group by based on some rules (or metadata on account names). 

If you want the full sum on parent accounts it wouldn't work with a group by statement, because that will distribute the set of postings to group. Eg it would always balance.

You could do this with a custom script. Ideally render those larger sums in a separate column. I think Fava will display sums on collapsed nodes.

--

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/f443acd3-9165-4517-a52a-00d230511cd5%40googlegroups.com.