Aggregate accounts on income statement

[Daniel González]

Hi,

I'm generating an income statement (with only my expenses) like this:

SELECT account, sum(position) FROM OPEN ON 2023-03-01 CLOSE ON 2023-03-30 WHERE account ~ "Expenses" GROUP BY 1 ORDER BY 1

But I have some subdivisions of my accounts that I would like to report one level up.

For example, now I get the following output:

...

Expenses:Gifts:JohnDoe 50 USD

Expenses:Gifts:JaneDoe 25 USD

...

And I would like to have:

...

Expenses:Gifts 75 USD

...

Is there a way to aggregate the accounts using beanquery?

Thank you.

[Chary Chary]

Not sure it can be done in beanquery, but here is example how it can easily be done in pandas

https://www.isabekov.pro/multiperiod-hledger-style-reports-in-beancount-pivoting-a-table/

In general it seems like using beanquery together with pandas in jupyter   notebook is the best way forward for reporting out our beancount.

in pandas you can do some other things, which are not available in beanquery: pivot by, add totals to columns and rows etc.

Plus add plotting with matplotlib - what else do you need? 

[Daniele Nicolodi]

SELECT
root(account, 2),
sum(position)
FROM ...
WHERE
root(account, 1) = 'Expenses'

GROUP BY 1
ORDER BY 1

This aggregates all accounts to the second account name component, like
in your example. This requires that your account name structure is
somehow regular, if it is not, there is no way of doing what you want
with bean-query only.

Cheers,
Dan

[Daniel González]

Char,

That is a great resource! Thanks for sharing. I will look into that for more customized reports than what I can get with Fava.

Dan,

Thanks! That works perfectly for me, given how I have my Expense accounts structured

[Chary Chary]

Yes, 

combination of beanquery with jupyter-notebook, pandas and matplotlib seems to be very powerful indeed.

Add to this the fact, that VS ode has a native support for  jupyter-notebook as well as an extension for beancount