# Shared weel with power and water meters

87 views

### Alessio Caiazza

Jun 10, 2024, 1:41:54 PMJun 10
to Beancount
Hello,

I'm in charge of managing the expenses of a shared well with 3 buildings.

The well has a single power meter and 3 water meters. Each month I record the power usage and split it according to the effective water usage based on the water meters. Then when I receive the electricity bill I extract the kWh price and assign an euro value to each owner.

I was attempting to model this in beancount but could not wrap my head around it.

Thank you very much,
Alessio

### Chary Chary

Jun 11, 2024, 4:07:52 AMJun 11
to Beancount
Hello,

2024-02-01 * "Receiving electricity bill and splitting it with 3 well owners"
Liabilities:Payable:Electricity  -1000 EUR
Assets:Receivable:Electricity     200 EUR
well_owner: "owner1"
Assets:Receivable:Electricity     300 EUR
well_owner: "owner2"
Assets:Receivable:Electricity     500 EUR
well_owner: "owner3"

2024-03-01 * "Paying to electrivity company"
Assets:BankAcc                 -1000 EUR
Liabilities:Payable:Electricity   1000 EUR

2024-03-03 * "Getting paid by well owner1"
Assets:BankAcc                  200 EUR
Assets:Receivable:Electricity  -200 EUR
well_owner: "owner1"

2024-03-04 * "Getting paid by well owner2"
Assets:BankAcc                  300 EUR
Assets:Receivable:Electricity  -300 EUR
well_owner: "owner2"

; 2024-03-05 * "Getting paid by well owner3"
;     Assets:BankAcc                  500 EUR
;     Assets:Receivable:Electricity  -500 EUR
;     well_owner: "owner3"

you can then check for outstanding payments from well owners

SELECT meta("well_owner") as outstanding,
sum(position) as outstanding
WHERE meta("well_owner")
GROUP BY
meta("well_owner")
HAVING not empty(sum(position))

you can experiment with it here:

### Paul Walker

Jun 11, 2024, 8:26:25 AMJun 11
If you open dedicated accounts for each owner, then beancount will do more for you:
2024-01-01 open Assets:Receivable:Electricity:Owner1  ; open on move-in date
2024-01-01 open Assets:Receivable:Electricity:Owner2
2024-01-01 open Assets:Receivable:Electricity:Owner3

Now:
• You can't typo an owner's name
• If an owner moves out, you can close their account and avoid accidentally charging them for future bills

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

### Stefano Merlo

Jun 11, 2024, 10:25:15 AMJun 11
to Beancount
Hello, while the examples above deal with the payments, see my proposal to manage the consumption.
(sorry I am not familiar with pasting from emacs to google maintaining formats)

Ledger:

plugin "beancount.plugins.auto_accounts"

option "operating_currency" "EUR"

2024-03-01 * "Water meter reading, by building"
Expenses:Building1   50 MQ
Expenses:Building2   40 MQ
Expenses:Building2   10 MQ
Assets:Water

2024-03-01 * "Power meter reading for the whole complex"
Assets:Water
Income:Power        100 MQ @@ 200 KWH

2024-03-01 price MQ    12 KWH    ; how many cubic meters of water for each KWH
2024-03-01 price KWH   0.25 EUR  ; cost of each KWH

Beanquery:

bean-query power-water.beancount "select date, leaf(account) as building, position as water, convert(value(position), 'KWH') as kwh, convert(convert(value(position), 'KWH'), 'EUR') as payment from year = 2024 where account ~ 'Expense'"

Result:

date    building  water   kwh    payment
---------- --------- ----- ------- ----------
2024-03-01 Building1 50 MQ 600 KWH 150.00 EUR
2024-03-01 Building2 40 MQ 480 KWH 120.00 EUR
2024-03-01 Building3 10 MQ 120 KWH  30.00 EUR

### Chary Chary

Jun 11, 2024, 10:38:58 AMJun 11
to Beancount
I agree, that  opening a dedicated account for each owner will give one more control

So, I think you example is better, than my

On Tuesday, June 11, 2024 at 2:26:25 PM UTC+2 wpa...@gmail.com wrote:

### Chary Chary

Jun 11, 2024, 12:00:48 PMJun 11
to Beancount
I have now updated the google collab with the option of using dedicated account for every well owner

### Alessio Caiazza

Jun 11, 2024, 2:39:42 PMJun 11
to Beancount

I am more interested in tracking effective kWh and m^3 of water for betetr accountability in case of dispute with the other owners.

Also the EUR value, the electricity bill is on my house meter (we have a dedicated meter on the well pump line) and I have a solar power grid. It is very possible that during summer time the power will just came from the solar grid and the other 2 owners buy it from me. So we will be using my power bill just as a reference for the energy price on that month, but it will be not connected to my personal expense.

Alessio

### Alessio Caiazza

Jun 11, 2024, 6:23:33 PMJun 11
to Beancount
Thank you very much, this was really helpful.

I had to tweak it a bit and see how it actually works as I gather more reading.

Let's begin with the issue, using Income:Power as a positivi amount gave a strange balance sheet, flipping the power meter reading fixed it.

2024-03-01 * "Power meter reading for the whole complex"
Assets:Water        100 MQ @@ 200 KWH
Income:Power

Now that I sorted this out, I realized that the query was using the last available price for each pair instead of the price of that day, this was fixed adding the date parameter to each convert call.

At this point I realized that this was really error prone, 'case a missing price will have screwed up all my reports so I decided to track water with price and use balance directives to check the meters values.

plugin "beancount.plugins.auto_accounts"

option "operating_currency" "EUR"

2024-05-15 * "Initial water meter reading"
Expenses:Building1    1 MQ @ 0 KWH ; paid by the owner
Expenses:Building2    0 MQ @ 0 KWH
Expenses:Building3    0 MQ @ 0 KWH
Income:Power

2024-05-16 balance Expenses:Building1 1 MQ
2024-05-16 balance Expenses:Building2 0 MQ
2024-05-16 balance Expenses:Building3 0 MQ

Expenses:Building1    0 MQ @ 0 KWH
Expenses:Building2    0 MQ @ 0 KWH
Expenses:Building3    1 MQ @ 0.6 KWH
Income:Power

2024-06-01 balance Income:Power -0.6 KWH
2024-06-01 balance Expenses:Building1 1 MQ
2024-06-01 balance Expenses:Building2 0 MQ
2024-06-01 balance Expenses:Building3 1 MQ

Expenses:Building1                            1.0 MQ @ 0.6 KWH
Expenses:Building2                            3.0 MQ @ 0.6 KWH
Expenses:Building3                            1.0 MQ @ 0.6 KWH
Income:Power

9999-12-31 balance Assets:Water 0 MQ ; make sure we are not leaking water

2024-05-15 price KWH 0 EUR
2024-05-30 price KWH   0.25 EUR  ; cost of each KWH

And now the new queries:

Report month-by-month

select date, leaf(account) as building,
position as water, weight as kWh,
convert(weight, 'EUR', date) as payment
from year = 2024 where account ~ 'Expense'

Yearly report:

select leaf(account) as building,
sum(position) as water,
sum(weight) as kwh,
sum(convert(weight, 'EUR', date)) as payment
from year = 2024 where account ~ 'Expense'
group by leaf(account)

I'm quite happy with the result, as I can do a balance sheet to see the meter values, and extract those useful reports. My only concern is that will not be possible to find a missing KWH/EUR price unless there is a way to force convert to find an exact price for the date or fail.

Thank you very much again,
Alessio