That's it! Thank you so much! The 'other_accounts' was exactly what I was needing.
September 21, 2024 at 12:14 PM, "Chary Chary" <[email protected] mailto:[email protected]?to=%22Chary%20Chary%22%20%3Cchary314%40gmail.com%3E > wrote: > > 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 > > On Friday, September 20, 2024 at 10:15:41 PM UTC+2 Oscar & Alejandra wrote: > > > > > 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. > > > > September 20, 2024 at 1:18 PM, "Chary Chary" <[email protected] > > https://cloud.aleyoscar.com/apps/snappymail/ > wrote: > > > > > > > > P.S. changes to Equity will also be shown > > > > > > On Friday, September 20, 2024 at 8:16:13 PM UTC+2 Chary Chary wrote: > > > > > > > > > > > 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. > > > > > > > > On Friday, September 20, 2024 at 6:30:29 PM UTC+2 Oscar & Alejandra > > > > wrote: > > > > > > > > > > > > > > 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 > > > > > > > > > > [email protected] > > > > > > > > > > > > > > > > > > > > -- > > > 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 [email protected] > > > https://cloud.aleyoscar.com/apps/snappymail/ . > > > To view this discussion on the web visit > > > https://groups.google.com/d/msgid/beancount/12877477-b999-45f6-aaba-042d4c101254n%40googlegroups.com > > > > > > https://groups.google.com/d/msgid/beancount/12877477-b999-45f6-aaba-042d4c101254n%40googlegroups.com?utm_medium=email&utm_source=footer > > > . > > > > > > > Oscar & Alejandra Camorlinga > > [email protected] https://cloud.aleyoscar.com/apps/snappymail/ > > > -- > 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 [email protected] > mailto:[email protected] . > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/d682d34c-6eb5-4183-a390-e9635adb6df4n%40googlegroups.com > > https://groups.google.com/d/msgid/beancount/d682d34c-6eb5-4183-a390-e9635adb6df4n%40googlegroups.com?utm_medium=email&utm_source=footer > . > Oscar & Alejandra Camorlinga [email protected] mailto:[email protected] -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e6e271828d06fb75fc60446f5c4b2b6dbff1db7a%40aleyoscar.com.
