Tracking utilities (electricity/gas/water) with real-world meter readings

[goo...@polvanaubel.com]

Hi all,

Excuse me if I get some of the terms wrong, I'm pretty new to proper bookkeeping (though I had a fun week "importing" 6 years of YNAB history (read: re-recording real transactions from January 1st, 2019, and using YNAB's reporting features to eyeball 5.5 years of percentage-distributed categorized income and expense transactions. It's not accurate to the cent but it'll get the job done in terms of trend reporting)).

The general idea of what I want to do is the following:

- Accurately track my week-to-week (or month-to-month or day-to-day, whatever) utility usage.

- Put that in a liabilities-account.

- Also put the monthly prepayments of my utility provider in that account.

- Have an accurate view of what I can expect from the yearly reconciliation, before it happens.

I've been trying to figure out how to track utility usage without having to use a secondary file in which I record metered values, and then only input the difference in transactions in BeanCount. Now there's of course no way around using the difference between metered values, but I'd very much like to be able to also track the actual metered values in beancount -- that way I can be sure I did not make a mistake anywhere using balance statements. I'll use gas as a shorthand, but all utilities behave this way.

I'm going to use a commodity GAS_M3 (that's cubic metre, I don't know what US'ies use to track gas volume) as a commodity, which has a single but potentially per-month fluctuating price. (At some point I want to figure out how to handle electricity and solar panels and how all that works in my country but let's focus on the simple case for now.)

Now initially you might think this is no different than a normal commodity using normal accounts, except for the following two truths:

- The total amount of  used is not an asset or a liability (nor an equity?) because once you use it it's immediately transformed into light, heat, etc. It's not something you can sell again. So for the total sum I guess we'd have to use an expense account, and a liabilities account only holds the delta for the current reporting period / billing period. Nothing wrong with balance checks on an expense account though, this will work.

- There needs to be an initial balance in the Expense account in order for it to accurately reflect the meter values (since meters don't reset to 0 when you move in). However, those GAS_M3s represent no *value* because I did not pay for them, they're not a liability to me, nothing of the sort.

I'm thinking one way might be to set it up as follows:

2018-12-01 open Equity:Utilities:Gas GAS_M3

2018-12-01 open Expenses:Utilities:Gas GAS_M3

2019-01-01 open Liabilities:Utilities:Gas GAS_M3

2019-01-01 open Assets:Checking EUR

2019-01-01 balance Expenses:Utilities:Gas  5000 GAS_M3 ; Initial meter reading

2018-12-01 pad Expenses:Utilities:Gas Equity:Utilities:Gas ; put before the start of reporting so that it's not included

Then, gas usage looks like this:

2019-01-05 * "Meter reading"

    Expenses:Utilities:Gas    10 GAS_M3

    Liabilities:Utilities:Gas    -10 GAS_M3

2019-01-06 balance Expenses:Utilities:Gas 5010 GAS_M3

and the monthly pre-paid bill would be something like

2019-01-25 * "Pre-paying gas"

    Assets:Checking     -100 EUR

    Liabilities:Utilities:Gas   20 GAS_M3 @@ 100EUR

(yes those numbers are totally unrealistic).

This works, but has the (obvious) drawback that the gas expenditure does not seem to be convertible to an expense in fava's reporting, and I have to do the mental math for conversions manually. That's probably to do with how the price feature works, and I think the cost feature might be very helpful. Gas prices are generally set per year or per month. So now we might have

2019-01-25 * "Pre-paying gas"

    Assets:Checking     -100 EUR {0.20 GAS_M3}

    Liabilities:Utilities:Gas

However, that triggers an error because no position in the checking account matches that. Okay, fine, I guess I'll have to precompute the amounts after all:

2019-01-25 * "Pre-paying gas"

    Assets:Checking     -100 EUR

    Liabilities:Utilities:Gas 20 GAS_M3 {5 EUR}

Okay, but then I also need to specify that cost basis when it's used initially, so

2019-01-05 * "Meter reading"

    Expenses:Utilities:Gas    10 GAS_M3 {5 EUR}

    Liabilities:Utilities:Gas    -10 GAS_M3 {5 EUR}

but then I get the problem that, since I'm pre-paying and might expect to get a return on my year bill, that commodity *might switch sign*, as it's doing here. I guess I could turn to the NONE booking method but from the documentation I can't really tell whether that's going to bite me later on. One advantage using the held-at-cost commodities is that the initial balance that ends up in equity is never converted to EUR because it can't: it doesn't have an attached cost; but the other commodities behave as expected. Also, I realize as I'm typing this, that my prepaid bill isn't "pre-allocated" to gas/electricity, I just pay a single company in bulk and they just reimburse / charge the total overage at the end of the year.

A better option at this point might be to use subaccounts as follows:

2019-01-01 open Liabilities:Utilities:Prepaid EUR

2019-01-01 open Liabilities:Utilities:Gas GAS_M3

2019-01-01 open Liabilities:Utilities:Electricity KWH

and use the normal booking method (or maybe FIFO), and then if I book lots into the Gas and Electricity accounts I'll use their Euro rate as cost, looks like the reporting can take care of automatically converting it then. When I receive the final bill and reimbursement I can do a balancing transaction, "paying off" the sub-accounts with the money in Prepaid and return the leftovers to my Checking account -- and if I have to pay additional I can just first add that as a transaction to the Prepaid liability and then pay off the entire lot, I guess.

Random closing thought: I could even put the initial Expense balance into a leaf account to ensure it's recorded and shown separate from my own usage, and still put balance statements on the higher node for actual meter readings.

Comments are very welcome.

Questions I still have:

- Does putting the Equity account under Equity:Opening-Balances:Gas give me any special benefits such as not reporting it?

- Will any of this come back to bite me in the future?

- Am I totally insane for even wanting to do this?

-- Pol

[Justus Pendleton]

On Wednesday, May 22, 2019 at 12:24:10 PM UTC+7, bean...@qwfp.nl wrote:

(though I had a fun week "importing" 6 years of YNAB history (read: re-recording real transactions from January 1st, 2019, and using YNAB's reporting features to eyeball 5.5 years of percentage-distributed categorized income and expense transactions. It's not accurate to the cent but it'll get the job done in terms of trend reporting)).

You should have tried my YNAB importer: https://groups.google.com/d/topic/beancount/REYYwMeq1vU/discussion

I think trying to track the cubic meters used is crazy. If it were me -- and I cared for some reason -- I'd just attach it as metadata to the bill I pay.

[goo...@polvanaubel.com]

On Wednesday, 22 May 2019 16:04:43 UTC+2, Justus Pendleton wrote:

On Wednesday, May 22, 2019 at 12:24:10 PM UTC+7, bean...@qwfp.nl wrote:

(though I had a fun week "importing" 6 years of YNAB history (read: re-recording real transactions from January 1st, 2019, and using YNAB's reporting features to eyeball 5.5 years of percentage-distributed categorized income and expense transactions. It's not accurate to the cent but it'll get the job done in terms of trend reporting)).

You should have tried my YNAB importer: https://groups.google.com/d/topic/beancount/REYYwMeq1vU/discussion

I did, actually -- and found that it died on split transactions, of which I have many. Rather than spend time debugging, I decided that I don't really care about the accounting accuracy of those past years; rather, I mostly care that the spending trends are kept. It's accurate to a few tens of euros in each budget category, so that's good enough.

 

I think trying to track the cubic meters used is crazy. If it were me -- and I cared for some reason -- I'd just attach it as metadata to the bill I pay.

The point of this exercise it that I do care, mostly to not get any nasty surprises at the end of the year, but also so that I can track energy use over time. I've been playing with it some more and have it now set up to track all the individual counters on my meters. I am pretty confident that this works. I'll see if I can anonymize it and then I'll post the account setup I've got now.

[Justus Pendleton]

On Thursday, May 23, 2019 at 1:00:12 PM UTC+7, bean...@qwfp.nl wrote:

On Wednesday, 22 May 2019 16:04:43 UTC+2, Justus Pendleton wrote:

On Wednesday, May 22, 2019 at 12:24:10 PM UTC+7, bean...@qwfp.nl wrote:

(though I had a fun week "importing" 6 years of YNAB history (read: re-recording real transactions from January 1st, 2019, and using YNAB's reporting features to eyeball 5.5 years of percentage-distributed categorized income and expense transactions. It's not accurate to the cent but it'll get the job done in terms of trend reporting)).

You should have tried my YNAB importer: https://groups.google.com/d/topic/beancount/REYYwMeq1vU/discussion

I did, actually -- and found that it died on split transactions, of which I have many.

I didn't even realise YNAB had those...though in retrospect obviously it must. Clearly I don't use them :) Looking at YNAB's JSON file, it doesn't look like it'd be too hard to add but it looks like you found a good enough solution anyway. As you've said, I find that historical data doesn't need to be especially accurate anyway in many cases.

I'll see if I can anonymize it and then I'll post the account setup I've got now.

That's great that you found a solution. Definitely post it when you've got a chance!