Sorry I've just realized I posted the wrong query. This is the query I got working:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT account, convert(units(sum(position)), "USD") as total WHERE (account ~ "Assets:CompanyCard" AND "Assets:Cash:CurrentAccount" in other_accounts) or (account ~ "Expenses" AND "Assets:Cash:CurrentAccount" IN other_accounts); ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The problem is that this doesn't scale, as I have multiple cash and company card accounts. Ideally I would do something like this (if this imaginary operator was available): (account ~ "Expenses" AND "Assets:Cash" ~IN other_accounts); On Friday, 27 December 2019 17:11:08 UTC, Artur Matos wrote: > > Hi, > > I'm trying to write a BQL query for generating a monthly budget report. > Besides tracking monthly expenses, I also want to track certain transfers > between assets: as an example, I have a company Starbucks card that I can > load up with additional money and get a company contribution on top. > Ideally I would like to see my expenses, including these transfers as they > are technically expenses; additionally any expense happening inside the > card shouldn't be captured in the report. > > Hopefully this is clearer with an example. Imagine you have the following > transactions: > > > -------------------------------------------------------------------------------------------------------------------- > > 2019-12-01 open Equity:OpeningBalances > 2019-12-01 open Assets:Cash:CurrentAccount > 2019-12-01 open Assets:CompanyCard:Starbucks > 2019-12-01 open Income:CompanyCard:Starbucks > 2019-12-01 open Expenses:Coffee > > 2019-12-02 * "Opening balance" > Assets:Cash:CurrentAccount 100 USD > Equity:OpeningBalances > > 2019-12-02 * "Loading up Starbucks card" > Assets:Cash:CurrentAccount -20 USD > Assets:CompanyCard:Starbucks > > 2019-12-02 * "Company tops up with 10%" > Assets:CompanyCard:Starbucks 2 USD > Income:CompanyCard:Starbucks > > 2019-12-02 * "Buy coffee with the card" > Assets:CompanyCard:Starbucks -10 USD > Expenses:Coffee > > 2019-12-02 * "Buy coffee without the card" > Assets:Cash:CurrentAccount -5 USD > Expenses:Coffee > > --------------------------------------------------------------------------------------------------------------------- > > > > Ideally I would like an output like this: > > account total > ---------------------------- ------ > Assets:CompanyCard:Starbucks 20 USD > Expenses:Coffee 5 USD > > > ---------------------------------------------------------------------------------------------------------------------- > > Basically only the cash transactions are captured - not the company top-up > inside the card or any coffee bought with the card. > > Is there any way to do this? I've tried several combinations of BQL and > this is the closest I've got: > > ---------------------------------------------------- > SELECT account, convert(units(sum(position)), "USD") as total WHERE > (account ~ "Assets:CompanyCard:Starbucks" and "Assets:Cash:CurrentAccount" > in other_accounts) or account ~ "Expenses"; > ---------------------------------------------------- > > But this isn't great as I would like to do this for multiple company cards > (IN doesn't do a tree match, only an exact match). > > Any advice? 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/17f81e45-4830-4f59-aa5f-1bc77007396e%40googlegroups.com.
