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 On Tuesday, June 11, 2024 at 4:25:15 PM UTC+2 [email protected] wrote: > 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 > > > On Tuesday, June 11, 2024 at 2:26:25 PM UTC+2 [email protected] wrote: > >> 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 >> >> >> On Tue, Jun 11, 2024 at 4:07 AM Chary Chary <[email protected]> wrote: >> >>> 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 >>> >>> >>> On Monday, June 10, 2024 at 7:41:54 PM UTC+2 [email protected] wrote: >>> >>>> 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 >>>> >>> -- >>> 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/49147181-c7c8-43c7-82a5-bd81dbfc5bdan%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/beancount/49147181-c7c8-43c7-82a5-bd81dbfc5bdan%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- 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/d12516bd-c35c-4146-b347-58d27c74b6c2n%40googlegroups.com.
