Shared weel with power and water meters

[Alessio Caiazza]

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]

Hello,

what about something like this?

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:

https://colab.research.google.com/drive/1KGGlmfeKpzTsphcO9P9Iizhv-cBqJIOw?usp=drive_link

[Paul Walker]

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/49147181-c7c8-43c7-82a5-bd81dbfc5bdan%40googlegroups.com.

[Stefano Merlo]

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]

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]

I have now updated the google collab with the option of using dedicated account for every well owner

https://colab.research.google.com/drive/1KGGlmfeKpzTsphcO9P9Iizhv-cBqJIOw?usp=drive_link

[Alessio Caiazza]

Thank you for your input.

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]

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

2024-05-30 * "May water/power readings"
  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

2024-06-11 ! "Fake reading"
  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