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/11388590-e3f7-41cb-93a1-780663806b4d%40googlegroups.com.
