Energy Spreadsheets - being organised?

JustAnotherSaver

At some point within the last month or two when i was trying to wade through the mud that is Tonik Energy, some of you were talking about how you keep tabs of your energy consumption with the use of spreadsheets and others went on to say that they know to the penny what their bill should be as all the formulas are locked in place, they simply just enter their readings.

Would any of you care to share what your spreadsheet looks like, what formulas you use & where?

Not really a big Excel user so it'd be helpful to see what someone else is doing and how it works so that I don't find myself in a situation like I have with Tonik again. Maybe the thread will help others too to become more organised & sure about their billing?

[Deleted User]

Yes I use the free Open Office Spreadsheet and it pretty much looks after itself, aggregating a rolling 12 months data and needs only 4 things to keep it running smoothly - electricity reading (in kWH), gas reading (in m3),  and calorific value of gas for the billing period (average of the daily published calorific value).  The last one is needed to convert gas units (col E) into kWH of gas equivalent (col F) so has the most complicated formula but this can be found easily enough.  The other formulae are mostly just adding the last 12 months to give the yearly figure which does fluctuate a little   As you might see from my spreadsheet the gas has gone up but the electricity has gone down to compensate over the last couple of year.  Of course you can be as fancy as you wish (as I have done) drilling it down to per day usage for each billing period as the month day count varies.  And you can do a weekly read to keep a really close eye on things.  Rows 2-25 are hidden because it is data older than May 2019.  Figures relating to early 2020 are a little awry as I was moving from Breeze to YE (via BG)  

tim_p

Take a look at an app (IOS only) called Meter Readings by Graham Haley. I think the basic version is free. I’ve used it for years and it’s pretty good. Probably not as powerful as a full blown spreadsheet but a lot easier to set up and use if you’re not familiar with spreadsheets. 

Caveat: no connection whatsoever with creator, just a satisfied user. 

Cardew

Much as I am a fan of spreadsheets for some applications, surely it is a matter of simple arithmetic to keep tabs on your energy consumption and bills.

For electricity take current meter reading and subtract last meter reading and you have consumption in kWh. Multiply those kWh by unit price, add number of days @ daily standing charge and you have cost.

The same for gas except the meter readings are for gas units so multiply these by 11.2*(for a metric meter) to get kWh.

* this can vary very slightly depending, but 11.2 is close enough and you would need to enter a new value into a spreadsheet every time if you wanted it to be exact.

With a calculator I can calculate gas and electricity consumption and cost in a couple of minutes.

Talldave

Using number of days in month to work out December's standing charge:

=(DAY(EOMONTH(T20,0))*0.17)     where T20 = 1/12/2020 and daily standing charge = 17p.

Working out December's full energy bill for one fuel:

=(U20*0.0237)+(DAY(EOMONTH(T20,0))*0.17)    where T20 = 1/12/2020, U20 = kWhs, standing charge = 17p, unit rate =2.37p

don't forget to add VAT (*1.05) if not included in the rates.

Gas calculations can be difficult to match exactly with your bill because the calorific value changes, sometimes mid-billing period, but to get gas kWh I use:

=(P75*Q75*1.02264)/3.6     where P75 = gas used in m3, Q75 = calorific value.

My weekly readings sheet looks like this, I enter the data in columns A to E, the calculations and averages for electricity auto-adjust based on whether night reading is zero:

brewerdave

I use an "average" figure for calorific value of 39.1 - never out by more than a few p each month. Even over the 9 months since I swapped gas supplier, I'm only 3p different to their calculations on credit balance.

Gerry1

https://www.businessenergy.com/business-gas/gas-bill-calculator/ will convert a volume in cubic metres or cubic feet to kWh, and to ££ as well.

All you then need to do is add the standing charge (and 5% VAT if not already included).

If you want to be really precise you can find the Calorific Value at https://mip-prd-web.azurewebsites.net/DataItemExplorer/Index

binao

tim_p said:

Take a look at an app (IOS only) called Meter Readings by Graham Haley. I think the basic version is free. I’ve used it for years and it’s pretty good. Probably not as powerful as a full blown spreadsheet but a lot easier to set up and use if you’re not familiar with spreadsheets. 

Caveat: no connection whatsoever with creator, just a satisfied user. 

Tim's app is IOS only.

Is there one for Android?

[Deleted User]

Cardew said:

Much as I am a fan of spreadsheets for some applications, surely it is a matter of simple arithmetic to keep tabs on your energy consumption and bills.

For electricity take current meter reading and subtract last meter reading and you have consumption in kWh. Multiply those kWh by unit price, add number of days @ daily standing charge and you have cost.

The same for gas except the meter readings are for gas units so multiply these by 11.2*(for a metric meter) to get kWh.

* this can vary very slightly depending, but 11.2 is close enough and you would need to enter a new value into a spreadsheet every time if you wanted it to be exact.

With a calculator I can calculate gas and electricity consumption and cost in a couple of minutes.

Very true it is and certainly spreadsheet data storage and calculations are not for everyone's needs BUT the value (for me) in having a spreadsheet is that it gives me a record of all my collated numbers and calculations since (in my case) May 2017.  I had to recreate much of it when I reinstalled Windows a while back and had forgotten to back-up one or two spreadsheets (not now though lesson learned).  And once I set up the spreadsheet with the correct formulae then it looks after itself.  The other thing my spreadsheet does for me is identify trends in usage as my yearly usage is not a constant.  So when my gas boiler broke down in early 2019 I was without central heating for a number of weeks and I can clearly see the impact on both my electricity and gas consumption!  My rolling average consumption in 2020 has also been slightly decreasing so I'm confidant that figures used for yearly consumption are going to be a slight overestimate if anything when I decide to move from SP.   

tim_p

binao said:

tim_p said:

Take a look at an app (IOS only) called Meter Readings by Graham Haley. I think the basic version is free. I’ve used it for years and it’s pretty good. Probably not as powerful as a full blown spreadsheet but a lot easier to set up and use if you’re not familiar with spreadsheets. 

Caveat: no connection whatsoever with creator, just a satisfied user. 

Tim's app is IOS only.

Is there one for Android?

Almost certainly there will be, just not by that particular developer.  I’ve got at least 9 years worth of history and that in itself is, to me, a good reason to keep something like a spreadsheet / app. Yes it’s simple to do the maths on a calculator but there’s no history that way. 

victor2

JustAnotherSaver said:

At some point within the last month or two when i was trying to wade through the mud that is Tonik Energy, some of you were talking about how you keep tabs of your energy consumption with the use of spreadsheets and others went on to say that they know to the penny what their bill should be as all the formulas are locked in place, they simply just enter their readings.

Would any of you care to share what your spreadsheet looks like, what formulas you use & where?

Not really a big Excel user so it'd be helpful to see what someone else is doing and how it works so that I don't find myself in a situation like I have with Tonik again. Maybe the thread will help others too to become more organised & sure about their billing?

As has been said, the gas CV varies on a daily basis, so the formulae are not "locked in place". Suppliers use an average CV for each billing period, so if you want to reconcile the supplier's bill to the penny, you must use that same CV. Several years ago, I compared the average of the daily CV figure published by National Grid for my area to the one used by my supplier, and it was consistently in my favour. It only made a penny or two difference on the monthly bill anyway.

So, you can be as sophisticated as you want with your analysis. As you are not a big Excel user, you may well want to keep it simple, so using a constant multiplier for the gas meter readings to get kWh will be easier and usually within pennies of the billed amount.

I've been recording my meter readings for 10 years and have a quite sophisticated spreadsheet with graphs showing my usage. I have seperate sheets for the tariff details and calorific values (as used by the supplier). The cost formula then looks these rates up.

I also forecast my future cost, based on the previous year's usage for each utility, and calculate what I think my DD should be to hit zero by the end of contract. If my prediction drifts far away from the supplier's actual DD, I will adjust it.

I did start out with a simple goal of monitoring my usage, but it has increased in sophistication as the years went by - and it's something I enjoy doing.