How to get the sum of positions sent from one account

[Peter]

I'd like to fetch the sum (per account) of the amount of money I sent from my checking account to any account during the year. The goal is to compute the average outflow per month to get a better understanding of the cashflow.

I tired:

SELECT account, other_accounts, sum(units(position)) WHERE account ~ "Assets:Cash:Checking" group by account, other_accounts

or

SELECT account, other_accounts, units(sum(position)) WHERE account ~ "Assets:Cash:Checking" group by account, other_accounts

and variations of that. Each query failed by telling me

> TypeError: unhashable type: 'list'

I guess, there's a problem calculating a hash or so, but I don't know. Tried to search in this group, but I didn't find anything related that helped (I'm pretty sure I used the wrong search term, tbh).

How do I get the summarized outflow from the checking account to any other account using bean-query?

[Peter]

Edit:

I started with this query:

SELECT date, account, other_accounts, position WHERE account ~ "Assets:Cash:Checking"

which outputs more or less a journal, but I'd like to summarize it per other_accounts.

I also tried to use

SELECT account, other_accounts, position WHERE account ~ "Assets:Barvermögen:Giro:DKB" group by other_accounts

But this yields the error

> Received invalid data as query error.

(As all transactions from checking to any other account do only have one posting, there should be no problems regarding other_accounts holding potentially multiple postings/accounts.)

[Martin Blais]

This is a bug introduced in ca24621e377130fa4763812c0b8ebb8c62ebb9b0

Daniele: are you okay with rolling this back? 

My version of "hashable" was more lenient actually, and I think would work in this case.

For this purpose we don't actually care much about a proper definition of hashable because the stream of data is frozen in time.

I don't even remember why I made a check. Might have been that computing a hash of an inventory isn't useful; alternatively we could just allow it and make Inventory hashable... and remove the check altogether.

 

[Martin Blais]

On Sat, Nov 19, 2022 at 4:24 AM Peter <tobias....@gmail.com> wrote:

Edit:

I started with this query:

SELECT date, account, other_accounts, position WHERE account ~ "Assets:Cash:Checking"

which outputs more or less a journal, but I'd like to summarize it per other_accounts.

I also tried to use

SELECT account, other_accounts, position WHERE account ~ "Assets:Barvermögen:Giro:DKB" group by other_accounts

But this yields the error

> Received invalid data as query error.

I can't reproduce. Which version are you using? Might be related to unicode.

 

(As all transactions from checking to any other account do only have one posting, there should be no problems regarding other_accounts holding potentially multiple postings/accounts.)

Another function that could be useful for this type of usage is an "other_account_or_null" type of function, whereby if there are only two postings (very common) returns the account of the other, and otherwise None.  It's bound to be a little fragile though, because plugins in the future may add more postings, e.g. for currency accounts and that would break existing queries using that function. Don't know.

 

Peter schrieb am Samstag, 19. November 2022 um 10:12:50 UTC+1:

I'd like to fetch the sum (per account) of the amount of money I sent from my checking account to any account during the year. The goal is to compute the average outflow per month to get a better understanding of the cashflow.

I tired:

SELECT account, other_accounts, sum(units(position)) WHERE account ~ "Assets:Cash:Checking" group by account, other_accounts

or

SELECT account, other_accounts, units(sum(position)) WHERE account ~ "Assets:Cash:Checking" group by account, other_accounts

and variations of that. Each query failed by telling me

> TypeError: unhashable type: 'list'

I guess, there's a problem calculating a hash or so, but I don't know. Tried to search in this group, but I didn't find anything related that helped (I'm pretty sure I used the wrong search term, tbh).

How do I get the summarized outflow from the checking account to any other account using bean-query?

--
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/3dc2d327-eb0f-44f3-902e-a15a7b3de106n%40googlegroups.com.

[Daniele Nicolodi]

From memory, without looking at the code, the columns in the "group by"
clause need to be hashable because "group by" is implemented with a
Python dictionary.

An exception would have been raised if the check would let the list
pass. Indeed, it seems that the reported exception is from the code in
beancount v2 and not from the code in beanquery.

The fix is to return other_accounts as a tuple and not as a list.

I'll look into this.

Cheers,
Dan

[Martin Blais]

Yes but it's a dictionary we know isn't going to change - everything is frozen at that stage, at least for the duration of the query execution - so we can compute a hash anyway.

Nothing in the query itself can mutate the stream of directives.

An exception would have been raised if the check would let the list
pass. Indeed, it seems that the reported exception is from the code in
beancount v2 and not from the code in beanquery.

The fix is to return other_accounts as a tuple and not as a list.

Maybe; I still think Python's notion of hashable doesn't apply here.

I'll look into this.

Cheers,
Dan

--
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/75ff42dc-2d07-70fa-af88-e7debafd6fe9%40grinta.net.

[Daniele Nicolodi]

On 19/11/2022 21:13, Martin Blais wrote:
> On Sat, Nov 19, 2022 at 2:32 PM Daniele Nicolodi <dan...@grinta.net
> <mailto:dan...@grinta.net>> wrote:
>
> On 19/11/2022 19:42, Martin Blais wrote:
> > On Sat, Nov 19, 2022 at 4:12 AM Peter <tobias....@gmail.com
> <mailto:tobias....@gmail.com>

> > <mailto:tobias....@gmail.com

Sure, but this code is going to fail anyway
https://github.com/beancount/beancount/blob/a6352005a466bf3377d7caf5b1570d3bd08207fe/beancount/query/query_execute.py#L328

beanquery has very similar code.

> An exception would have been raised if the check would let the list
> pass. Indeed, it seems that the reported exception is from the code in
> beancount v2 and not from the code in beanquery.
>
> The fix is to return other_accounts as a tuple and not as a list.
>
> Maybe; I still think Python's notion of hashable doesn't apply here.

It applies as long as the think is implemented as Python dictionary :-)

Cheers,
Dan

[very_t...@icloud.com]

Hi Peter,

Since `other_accounts` is a set (often with a single item) you can make it hashable by turning it into a comma-joined string with JOINSTR:

beancount> SELECT account, JOINSTR(other_accounts) AS other_accounts, sum(units(position)) WHERE account ~ "^Assets:Checking" GROUP BY 1, 2 ORDER BY 3, 1, 2

    account            other_accounts        sum_units_po

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

Assets:Checking Expenses:Rent                -2000.00 USD

Assets:Checking Expenses:Groceries            -253.96 USD

Assets:Checking Assets:Cash,Expenses:ATMFees  -202.50 USD

Assets:Checking Expenses:Utilities            -184.30 USD

Assets:Checking Expenses:Transit               -98.00 USD

Assets:Checking Income:Wages                  3000.00 USD

Another way to write this if you want to report the other side would be to use FINDFIRST:

beancount> SELECT account, sum(units(position)) WHERE FINDFIRST('Assets:Che.*', other_accounts) != NULL ORDER BY 2

     account       sum_units_po

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

Income:Wages       -3000.00 USD

Expenses:ATMFees       2.50 USD

Expenses:Transit      98.00 USD

Expenses:Utilities   184.30 USD

Assets:Cash          200.00 USD

Expenses:Groceries   253.96 USD

Expenses:Rent       2000.00 USD

Or use IN with an exact account name

beancount> SELECT account, sum(units(position)) WHERE 'Assets:Checking' IN other_accounts ORDER BY 2

     account       sum_units_po

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

Income:Wages       -3000.00 USD

Expenses:ATMFees       2.50 USD

Expenses:Transit      98.00 USD

Expenses:Utilities   184.30 USD

Assets:Cash          200.00 USD

Expenses:Groceries   253.96 USD

Expenses:Rent       2000.00 USD

I’m using this file for examples:

% cat example.beancount 

plugin "beancount.plugins.auto"

option "operating_currency" "USD"

2022-11-04 * "ACME Corporation" "Net Pay"

  Assets:Checking        1,500.00 USD

  Income:Wages

2022-11-05 * "ACME Groceries" "Weekly Grocery Shop"

  Assets:Checking         -154.20 USD

  Expenses:Groceries

2022-10-05 * "ACME Cash" "ATM Withdrawal"

  Assets:Checking         -202.50 USD

  Expenses:ATMFees           2.50 USD

  Assets:Cash              200.00 USD

2022-11-10 * "ACME Water Power and Sewer" ""

  Assets:Checking         -184.30 USD

  Expenses:Utilities

2022-11-12 * "ACME Groceries" "Weekly Grocery Shop"

  Assets:Checking          -99.76 USD

  Expenses:Groceries

2022-11-18 * "ACME Corporation" "Net Pay"

  Assets:Checking        1,500.00 USD

  Income:Wages

2022-11-19 * "Supermercado ACME" "Weekly Grocery Shop"

  Assets:Cash              -200.00 USD

  Expenses:Groceries        198.00 USD

  Expenses:Gambling           2.00 USD ; Lotto ticket

2022-11-29 ! "ACME Transit" "Transit Pass"

  Assets:Checking          -98.00 USD

  Expenses:Transit

2022-11-30 ! "ACME Property Management" "Rent" #scheduled

  Assets:Checking       -2,000.00 USD

  Expenses:Rent

HTH,

Kevin

-- 
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/ec5b7887-63d9-49a3-80f7-5ee317013db6n%40googlegroups.com.

[Martin Blais]

I don't understand what you're saying. We just make it not fail.

 

>     An exception would have been raised if the check would let the list
>     pass. Indeed, it seems that the reported exception is from the code in
>     beancount v2 and not from the code in beanquery.
>
>     The fix is to return other_accounts as a tuple and not as a list.
> 
> Maybe; I still think Python's notion of hashable doesn't apply here.

It applies as long as the think is implemented as Python dictionary :-)

It actually doesn't matter what it is. 

What matters is that its value - whatever it is, mutable or not - is derived from the non-aggregate expressions and then thrown away.

The fact that it's a Python dict is irrelevant. 

It's not going to live beyond the retrieval of the accumulator for the aggregates.

 

Cheers,
Dan

--
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/911f0706-ff61-4559-bb0e-608c4aa34d9d%40grinta.net.

[Peter]

Hi Kevin,

thanks for your suggestions. They seem to work just fine and to be just what I was looking for.

The SELECT account, sum(units(position)) WHERE 'Assets:Checking' IN other_accounts ORDER BY 2 is really beautiful. It is easy to read and understand, and it solves (at least that's what I'm suspecting) the "problem" with multiple postings in one transaction—as it tackles the task exactly the other way around.

Thanks!