Beanquery - Select expenses tied to a specific asset

[Oscar & Alejandra]

Hello everyone,

I am using the latest beanquery from PyPI (v0.1.dev0) and I'm pretty new to SQL and beanquery's SQL-like syntax. I am trying to produce a report where I can see all of my expenses for a certain period, something like what my bank statement would show but grouped by my expense categories. I found this in the examples in the docs and it's pretty close to what I want but I'm not sure how to grab the 'transfers' from my asset account. For example when I make a payment towards my credit card or when I transfer into my savings.

FROM OPEN ON 2024-09-12 CLOSE ON 2024-09-26

SELECT account, SUM(position)

WHERE account~"Income|Expenses"

GROUP BY 1

ORDER BY 1

The above query produces something like the following:

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

              account                SUM(position

Expenses:Auto:Fuel                      50.00 USD

Expenses:Auto:Insurance                140.00 USD

Expenses:Clothes                        20.00 USD

Expenses:Debt:Interest                  83.00 USD

Expenses:Food:Dining                   100.00 USD

Expenses:Food:Groceries                140.00 USD

Expenses:Home:Utilities:Electricity     80.00 USD

Expenses:Home:Utilities:Water           70.00 USD

Income:Salary                        -3000.00 USD

Where I would really like something like this

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

              account                SUM(position

Expenses:Auto:Fuel                      50.00 USD

Expenses:Auto:Insurance                140.00 USD

Expenses:Clothes                        20.00 USD

Expenses:Debt:Interest                  83.00 USD

Expenses:Food:Dining                   100.00 USD

Expenses:Food:Groceries                140.00 USD

Expenses:Home:Utilities:Electricity     80.00 USD

Expenses:Home:Utilities:Water           70.00 USD

Income:Salary                        -3000.00 USD

Liabilities:CreditCard                 100.00 USD

Assets:Savings                         300.00 USD

Which would essentially show everything my 'paycheck' was spent on for the period selected. Is this possible using beanquery?

Thanks!

Oscar & Ale

ma...@aleyoscar.com

[Chary Chary]

Hi,

I am not sure exactly what you want to achieve, but the following query will give you a sum of all changes to to all accounts (including Assets and Liabilities) over the period of time 

SELECT account, SUM(position)

WHERE date >=2024-09-12 and date <=2024-09-26

This will be all of your incomes, all expenses over the period, as well as changes to Assets and Liabilities over the same period.

Total will be 0.

[Chary Chary]

P.S. changes to Equity will also be shown

[Oscar & Alejandra]

Wow, so simple. That is pretty close to what I need, I'm just wondering if there is a way to filter out only transactions that have a posting with a specific account.

For example, take these transactions:

2024-01-01 * "Opening Balances"

    Liabilities:Credit  -1000.00 USD

    Equity:Opening       1000.00 USD

2024-09-01 * "Salary"

    Assets:Checking      3000.00 USD

    Income:Salary       -3000.00 USD

2024-09-02 * "Interest"

    Expenses:Interest      10.00 USD

    Liabilities:Credit    -10.00 USD

2024-09-03 * "Credit payment"

    Liabilities:Credit    100.00 USD

    Assets:Checking      -100.00 USD

2024-09-04 * "Groceries"

    Expenses:Food         200.00 USD

    Assets:Checking      -200.00 USD

Running that query will list all the accounts with their balances for that period, which is great:

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

       account           SUM(position

Assets:Checking           2700.00 USD

Expenses:Food              200.00 USD

Expenses:Interest           10.00 USD

Income:Salary            -3000.00 USD

Liabilities:Credit          90.00 USD

But is there a way to only select the transactions that have postings with 'Assets:Checking'? So it would look like this

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

       account           SUM(position

Assets:Checking           2700.00 USD

Expenses:Food              200.00 USD

Income:Salary            -3000.00 USD

Liabilities:Credit         100.00 USD

So I can see everything that came out of my checking account in the given period? Note that the Liabilities:Credit balance is adjusted to no longer include the interest charge.

--

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/12877477-b999-45f6-aaba-042d4c101254n%40googlegroups.com.

Oscar & Alejandra Camorlinga
ma...@aleyoscar.com

[Chary Chary]

I think this is what you are looking for

SELECT account, SUM(position)

WHERE account = "Assets:Checking" OR "Assets:Checking" in other_accounts

the trick is the other_accounts thing

The human explanation is that it shows a sum of changes to all accounts by transactions, which involve Assets:Checking

You can experiment here:

https://colab.research.google.com/drive/1iGqhrQ6UUGCN6BmKDkprtldla6xuB29e?usp=sharing

[Oscar & Alejandra]

That's it! Thank you so much! The 'other_accounts' was exactly what I was needing.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/d682d34c-6eb5-4183-a390-e9635adb6df4n%40googlegroups.com.