I realize I didn't fully express my thoughts on the query on the first
(simple) solution below. Fundamentally, you want to find *postings* on your
credit card that are for joint expenses:
SELECT
*
WHERE
(account ~ Expenses:Utilities:* OR account ~ Expenses:Mortgage:* OR
account ~ .)
AND STR(FINDFIRST('Liabilities:Credit-Cards:Andrew:Card1',
other_accounts))
If you simply add transfer postings to the query above, you will also find
the current balance owed (as long as the query is not restricted in time):
SELECT
sum(number)
WHERE
((account ~ Expenses:Utilities:* OR account ~ Expenses:Mortgage:* OR
account ~ .)
AND STR(FINDFIRST('Liabilities:Credit-Cards:Andrew:Card1',
other_accounts))
OR
(account ~ Assets:Bank:Andrew AND STR(FINDFIRST("Assets:Bank:Joint",
other_accounts)))
Queries above are untested. What I like about the queries above are, they
don't rely on you annotating your accounts (with tags, metadata, etc.).
Instead, the complexity is pushed into the query, and getting it right
solves your problems.
*Yet another way* to peel this orange is to use metadata and annotate
postings (not transactions, as tags do) with "owed_to_andrew" like so:
2022-01-10 * "Utility"
Liabilities:Credit-Cards:Andrew:Card1 -150 USD
Expenses:Utilities 150 USD
owned_to_andrew: 1
And the transfer transaction:
2022-01-10 * "Transfer"
Assets:Bank:Joint -700 USD
Assets:Bank:Andrew 700 USD
owned_to_andrew: 1
Then, you simply query for posting with the owned_to_andrew: 1 metadata.
Very simply query, though it relies on you annotating correctly.
On Tuesday, August 16, 2022 at 10:00:44 PM UTC-7 Red S wrote:
> Finally, is there another way to handle this rather unorthodox but
>> functional (for us; YMMV) system that does not involve tags? All
>> suggestions welcome.
>
>
> Great starting point. I would personally solve all the questions you want
> to answer by using queries, and not using tags at all. This helps simplify,
> leading to fewer errors, and eventually opens the door to automation of
> import.
>
> I would encourage you to go through the bql documentation, and
> specifically understand querying transactions vs. postings (FROM vs WHERE
> in bql terminology). Your post makes me think you are querying transactions
> when what you want to be querying is postings. This should answer almost
> every question you had. Let's look at a couple examples:
>
>
>
>> Starting in 2021, I began to pay my credit card bill in full each month
>> from my personal checking account, by allowing the card issuer to initiate
>> a withdrawal for the necessary amount.
>>
>
> If I understood correctly, you need to be able to tell how much of each
> month's credit card bill came from joint expenses vs individual expenses.
> As an aside, some card providers will allow an "add-on" card, for which the
> transactions are separated out. This will make things trivial for you. With
> other card providers, the transactions of the add-on and primary card are
> intermingled. Let's assume the latter is the case.
>
> 2022-01-10 * "Bought shoes"
> Liabilities:Credit-Cards:Andrew:Card1 -100 USD
> Expenses:Clothing:Andrew
>
> 2022-01-10 * "Utility"
> Liabilities:Credit-Cards:Andrew:Card1 -150 USD
> Expenses:Utilities
>
> SELECT * WHERE (account ~ Expenses:Utilities:* OR account ~
> Expenses:Mortgage:* OR account ~ .)
>
> This tells you what is owed by the joint account to your account. You can
> add a "FROM YEAR=222 and MONTH=3" or any arbitrary dates to filter down to
> a billing cycle. You can add all the common expenses to the list above. To
> make it even simpler, you could organize your account hierarchy like so:
> "Expenses:Joint:*, Expenses:Andrew:*, Expenses:Amy:*, if that works for you.
>
> Once you know the amount, you would transfer it from your joint account to
> yours.
>
> Here's a fancier solution that continuously tracks the amount owed. Your
> joint transactions would look like:
> 2022-01-10 * "Utility"
> Liabilities:Credit-Cards:Andrew:Card1 -150 USD
> Expenses:Utilities 150 USD
> Liabilities:Joint-Owed-To-Andrew -150 USD
> Assets:Due-From-Joint 150 USD
>
> And the transfer transaction:
> 2022-01-10 * "Transfer"
> Assets:Bank:Joint -700 USD
> Assets:Bank:Andrew 700 USD
> Liabilities:Joint-Owed-To-Andrew 700 USD
> Assets:Due-From-Joint -700 USD
>
> Simply looking at the balance of Assets:Due-From-Joint at any point tells
> you the balance owed.
>
>
>> To calculate annual budget, I bean-query for transactions tagged with
>> #hbt- (either direct or indirect), sum each expense category by month, and
>> write out an Excel worksheet with expense categories listed down column A
>> and months across row 1, plus a column to get annual total and a row to get
>> total by month. The problem is that because of double-entry bookkeeping,
>> the whole thing sums to zero. How do I determine what source data to ignore
>> here? Is there any way to get bean-query to filter on whether amount is
>> more or less than zero?
>>
>
> The query above works for the annual budget as well. Have you used Fava?
> If so, it shows you annual and monthly summaries for any part of the
> hierarchy (eg: Expenses:Joint) with no further work from you.
>
>
> The contrib pages are a straight list of paydays, how much each partner
>> contributed each payday, what the expected amount based on annual contrib /
>> 365 x day of the year.
>>
>> The balance page is where I'm really baffled. The query picks up tags
>> #hbt-card and #hbr and is meant to keep a running total of contributions
>> vs. expenditures. A discrepancy here means that joint owes me money or I
>> owe money to joint. It must balance to zero, or if not, a transfer in the
>> appropriate direction will zero that out. Again, double-entry bookkeeping
>> makes it less than obvious which data to ignore.
>>
>
> Ditto: query the postings, not the transactions.
>
> Does that help?
>
--
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/658a85d9-c992-4e47-8061-2b23596453ban%40googlegroups.com.
