Sharing household expenses

[Andrew Laurence]

My wife and I have a rather unusual arrangement for dealing with shared expenses. Each payday, we each put an agreed-upon sum into a joint checking account. From that account, we pay regularly-occurring expenses like mortgage, utilities, etc. What makes our situation a bit more complex is that whenever possible, I put shared household expenses on a reward credit card which I also use for personal expenses.

Prior to 2021, I used to pay down the card by generating an online bill pay on the joint checking account web site for each separate transaction, for ease of auditing. This made it fairly easy to match up an expenditure on the card with a payment to the card issuer.

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.

I have used tags as follows:

#hbc-andrew (my contributions)

#hbc-amy (her contributions)

#hbt-direct (a household expense paid directly from joint checking: e.g., mortgage or lawn care)

#hbt-card (a household expense paid with Andrew's credit card)

#hbr (monies paid from joint to cover household expenses charged to Andrew's credit card.

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 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.

Finally, is there another way to handle this rather unorthodox but functional (for us; YMMV) system that does not involve tags? All suggestions welcome.

Thank you in advance! Long live beancount!

Andrew

Sent from Proton Mail for iOS

[Red S]

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? 

[Archimedes Smith]

Does this meet your need? Apart from using meta to declare the ownership of each posting / transaction, you can also declare the ownership of an account so it will be attributed to the right person by default.

[Red S]

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.

[Daniele Nicolodi]

On 17/08/2022 07:00, Red S wrote:
> I would encourage you to go through the bql documentation, and
> specifically understand querying transactions vs. postings (FROM vs
> WHERE in bql terminology).

The capability to use the FROM clause of a BQL query to filter ledger
entries is an unfortunate historical accident. There is no fundamental
difference between the expression that are used in the FROM clause and
in the WHERE clause, except for which table columns are accessible in
the two: the FROM clause has access only to columns derived from
transactions properties, while the WHERE clause has also access to
columns derived from posting properties:

SELECT * FROM date = 2022

or

SELECT * WHERE date = 2022

do exactly the same thing.

Indeed in beanquery (the project spun off from beancoiunt to evolve
bean-query faster) the FROM clause is interpreted rewriting it into a
WHERE clause. This halves the memory usage and the number of iterations
over the data set required to interpret the query.

The support for filter expression in the FROM clause will need to be
deprecated and eventually removed, thus I recommend to move your
expressions to the WHERE clause.

Cheers,
Dan

[Red S]

On Wednesday, August 17, 2022 at 11:43:32 AM UTC-7 dan...@grinta.net wrote:

On 17/08/2022 07:00, Red S wrote:
> I would encourage you to go through the bql documentation, and
> specifically understand querying transactions vs. postings (FROM vs
> WHERE in bql terminology).

The capability to use the FROM clause of a BQL query to filter ledger
entries is an unfortunate historical accident. There is no fundamental
difference between the expression that are used in the FROM clause and
in the WHERE clause, except for which table columns are accessible in
the two: the FROM clause has access only to columns derived from
transactions properties, while the WHERE clause has also access to
columns derived from posting properties:

In this thread, the OP was using a transaction-level solution (tags), and perhaps executing or viewing the equivalent of "SELECT * FROM/WHERE YEAR=2022" which matches some tags. And ending up with postings from all sides of the transaction, leading to a balance sum of zero, which confused them. So at a higher level, I was pointing the OP to thinking about the distinction between transactions and postings, which seems to be a a not-uncommon stumbling block.

But thanks for the context about FROM/WHERE and where that's headed. Makes sense, and removing FROM might also reduce a point of confusion, IMHO.

[Stefano Mihai Canta]

My question might be OT for this thread, but it connects to the FROM/WHERE topic.

How do I write a BQL query that selects transactions from certain accounts, and excludes those transactions that have a posting that matches another account? For example, select all transactions from Credit-Card:Bla:Bla that don't have Assets:Transfer in the postings. I tried FROM accounts ~ 'Credit-Card:Bla:Bla' and WHERE with Assets:Transfer, but I always get a syntax error. (pseudocode, not the actual query...)

Thanks

Thanks,

Stefano

--
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/05fba31f-ecc5-4ab2-91e3-2885dd09f4e8n%40googlegroups.com.

[Red S]

On Monday, August 22, 2022 at 12:56:50 PM UTC-7 cantas...@gmail.com wrote:

My question might be OT for this thread, but it connects to the FROM/WHERE topic.

How do I write a BQL query that selects transactions from certain accounts, and excludes those transactions that have a posting that matches another account? For example, select all transactions from Credit-Card:Bla:Bla that don't have Assets:Transfer in the postings. I tried FROM accounts ~ 'Credit-Card:Bla:Bla' and WHERE with Assets:Transfer, but I always get a syntax error. (pseudocode, not the actual query...)

 FROM accounts ~ 'Blah' is invalid because transactions don't contain accounts (not directly). Postings have an account. You proved my point above with this being a common stumbling block, heh :).

As Dan advised above, use a WHERE instead of from. It helps to remember that a query in BQL filters postings via the WHERE. So you're selecting postings with a certain account, but you want to filter out postings where the posting's parent transaction has another posting with a certain account.

SELECT *

  WHERE account ~ "Credit-Card:Bla:Bla"

  AND NOT STR(FINDFIRST('Assets:Transfer.*', other_accounts))

Try this for edification: "SELECT date,description,account,other_accounts".

'help targets' in BQL would've listed the other_accounts and FINDFIRST functions, but IMHO, constructing the query above is very difficult to do from the documentation. Perhaps a BQL cookbook would help.

[Stefano Mihai Canta]

Thanks, that's what I need. Hard to find this in the documentation.

The query that works for me is without STR.

SELECT *

  WHERE account ~ "Credit-Card:Bla:Bla"

  AND NOT (FINDFIRST('Assets:Transfer.*', other_accounts))

Using it with NOT STR throws an error about bool and str not being valid operand types for &.

Interesting issue:

SELECT *

  WHERE account ~ "Credit-Card:Bla:Bla"

  AND (FINDFIRST('Assets:Transfer.*', other_accounts))

throws

TypeError: unsupported operand type(s) for &: 'bool' and 'NoneType'

while

SELECT *

  WHERE account ~ "Credit-Card:Bla:Bla"

  AND NOT NOT (FINDFIRST('Assets:Transfer.*', other_accounts))

does exactly what I want...

Stefano

--

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/dec68b5b-f88d-41bc-9e4d-0c68cb8c267bn%40googlegroups.com.