BQL query for transfers between assets?

[Artur Matos]

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.

[Artur Matos]

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);

[Red S]

Could you replace "X in other_accounts" with "number > 0"?

[Patrick Ruckstuhl]

What I've done before that might work in your case is something like

where joinstr(other_accounts) ~ ".*;Foo"

Basically use a regexp against the joined string of the other accounts.

[Artur Matos]

Thanks, this works!

[Artur Matos]

Thanks. That doesn't quite work because "number > 0"  would also pick up the company top up below:

2019-12-02 * "Company tops up with 10%"

  Assets:CompanyCard:Starbucks  2 USD

  Income:CompanyCard:Starbucks

[Jonathan Klabunde Tomer]

Isn't this what FROM is for?

You could add a metadata tag to your "cash" accounts, like is_cash: true, and then use FROM OPEN_META('is_cash') to filter to only transactions affecting at least one account with the given metadata tag.

[Oon-Ee Ng]

Just wanted to pop in and say "thank you very much".

I found this while searching for a way to restrict my queries to only certain types. In my case it was that my prepaid card has a limit on how much it can be topped up from credit card sources, and I'm targeting to hit that limit every month to maximize certain benefits. Now I have a query which tells me exactly how much I've topped up so far each month. Something like:-

SELECT

    year, month, account, sum(cost(position))

WHERE

    account ~ 'Assets:Banking:PrepaidCard'

    and joinstr(other_accounts) ~ 'Liabilities:CreditCards'

    and NOT(joinstr(other_accounts) ~ 'Assets:Banking:PrepaidCard')

GROUP BY year, month, account

The final line under WHERE is to exclude transactions between prepaid cards where I have a split transaction.

--
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/853DCCA4-B50D-48CC-B6B2-60C01B3769D5%40ch.tario.org.