Energy bills spreadsheet templates
Discussion
Is anyone using a spreadsheet to keep on top of Electricity, Gas etc on a monthly basis?
Or a formula to add meter readings that automatically show costs. Maybe then comparing kwh £ to historical bills or any future trends upwards. maybe listed downwards by month.
Personally, I find google sheets helpful as you can share with friends and family. Comment and never duplicate by mistake.
Maybe doing this manually might also find any discrepancies between meters and bill providers estimating high energy consumption, then keeping your money for months.
Any links to such a thing or maybe share the formulas here for others to use if anyone has found something particularly useful.
Or a formula to add meter readings that automatically show costs. Maybe then comparing kwh £ to historical bills or any future trends upwards. maybe listed downwards by month.
Personally, I find google sheets helpful as you can share with friends and family. Comment and never duplicate by mistake.
Maybe doing this manually might also find any discrepancies between meters and bill providers estimating high energy consumption, then keeping your money for months.
Any links to such a thing or maybe share the formulas here for others to use if anyone has found something particularly useful.
I just put my readings into a spreadsheet before I enter them on Octopus's website.
Subtract the date from the previous date to give number of days
Subtract new reading from old Elec is in Kwh,
Gas is in m3, but the bill I get from Octopus shows how to convert into kwh
Divide by the number of days.
Multiply by the p/kWh figures.
TBH, I'm mostly doing that in case I've scrambled the meter readings, to check I'm not sending octopus a load of crap.
I then take sets of readings about a year apart and work out kWh/year figures.
Being with Octopus, it's easy to see what you owe up to date, previous suppliers it's been a bit more opaque, hence the spreadsheet.
I use open office.
It's really easy to write your own basic spreadsheet to do this.
Probably easier than understanding what each box in mine does?
Subtract the date from the previous date to give number of days
Subtract new reading from old Elec is in Kwh,
Gas is in m3, but the bill I get from Octopus shows how to convert into kwh
Divide by the number of days.
Multiply by the p/kWh figures.
TBH, I'm mostly doing that in case I've scrambled the meter readings, to check I'm not sending octopus a load of crap.
I then take sets of readings about a year apart and work out kWh/year figures.
Being with Octopus, it's easy to see what you owe up to date, previous suppliers it's been a bit more opaque, hence the spreadsheet.
I use open office.
It's really easy to write your own basic spreadsheet to do this.
Probably easier than understanding what each box in mine does?
I have created a spreadsheet that calculates every Sunday morning my gas and electric usage and calculates the weekly cost for power.
Been doing this for two years to monitor any benefits from underfloor insulation and extra insulation in walls and roof space as I install it.This also give me a estimated usage for each month. Argue with EDF every year about what DD should be and give them my figures and ask them to provide their calculations , never been shown them yet and at the end of the year I am proved correct, claimed an £30 overpayment back last year which is acceptable.
Much better than having a smart meter although EDF are now saying having smart meter is PAYG so might give in and get one but still keep monitoring usage.
Getting a new boiler next month so it will be interesting to see changes to gas especially as we have a hot water tank at moment and that is being done away with.
Been doing this for two years to monitor any benefits from underfloor insulation and extra insulation in walls and roof space as I install it.This also give me a estimated usage for each month. Argue with EDF every year about what DD should be and give them my figures and ask them to provide their calculations , never been shown them yet and at the end of the year I am proved correct, claimed an £30 overpayment back last year which is acceptable.
Much better than having a smart meter although EDF are now saying having smart meter is PAYG so might give in and get one but still keep monitoring usage.
Getting a new boiler next month so it will be interesting to see changes to gas especially as we have a hot water tank at moment and that is being done away with.
Trustmeimadoctor said:
Smart meter does all this for you 
It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
Smart meter doesn't let you have the data to play with though.It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
I haven't fitted the cameras yet, but I have setup a load of stuff in SharePoint so that a camera picture can be saved every 5 minutes and have the meter read automatically, saved to a SharePoint list and built into an app on my phone. The idea is to harass my wife automatically so she stops putting the heating so hot.
paulrockliffe said:
Trustmeimadoctor said:
Smart meter does all this for you
It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
Smart meter doesn't let you have the data to play with though.It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
https://www.futurenetzero.com/hugo/
paulrockliffe said:
Trustmeimadoctor said:
Smart meter does all this for you
It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
Smart meter doesn't let you have the data to play with though.It's easy enough to knock what you want up in excel in 30 minutes or so and I'm not good with excel
I haven't fitted the cameras yet, but I have setup a load of stuff in SharePoint so that a camera picture can be saved every 5 minutes and have the meter read automatically, saved to a SharePoint list and built into an app on my phone. The idea is to harass my wife automatically so she stops putting the heating so hot.
Grey_Area said:
I do, including my smart plugs usage for all the heavy hitters like tumble dryers etc…
It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
You ever looked at how much your smart plugs use?It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
A smart meter will tell you a lot, but I find a spreadsheet is useful to:
Compare one year to the previous years.
See where you'd be with different tariffs
Estimate where your energy is going, like how much is HW, how much is used by various appliances and whether you could make savings.
There's only any point gathering data if you're going to do something with it, like make a decision.
My spreadsheet could tell me what I'd be paying if I went for a BEV and/or solar, ideally I'd put data from the smart meter into the spreadsheet to get a picture of how days and months vary. It's been useful to 'borrow' other people's solar data.
Mostly I don't need any of it, Octopus send me what I think I need to know every month.
If you were trying to optimise weather compensation or making other adjustments to your heating, then a spreadsheet with some weather data might be good.
In a previous house, I spent some time with spreadsheet which looked at the heat loss and insulation, trying to reconcile where all the heat was going. That was back in the day when actually being comfortable was what mattered and gas was cheap....
Compare one year to the previous years.
See where you'd be with different tariffs
Estimate where your energy is going, like how much is HW, how much is used by various appliances and whether you could make savings.
There's only any point gathering data if you're going to do something with it, like make a decision.
My spreadsheet could tell me what I'd be paying if I went for a BEV and/or solar, ideally I'd put data from the smart meter into the spreadsheet to get a picture of how days and months vary. It's been useful to 'borrow' other people's solar data.
Mostly I don't need any of it, Octopus send me what I think I need to know every month.
If you were trying to optimise weather compensation or making other adjustments to your heating, then a spreadsheet with some weather data might be good.
In a previous house, I spent some time with spreadsheet which looked at the heat loss and insulation, trying to reconcile where all the heat was going. That was back in the day when actually being comfortable was what mattered and gas was cheap....
My EDF energy app gives a breakdown of which categories the power goes to & can be exported .csv
Some fancy software ‘knows’ the appliance by the usage profile etc
Obviously records the daily usage numbers also.
I’ve used it to halve last years ‘average’ 15kwh electricity.
In advance of my May21-May23 fixed ending next year.
Some fancy software ‘knows’ the appliance by the usage profile etc
Obviously records the daily usage numbers also.
I’ve used it to halve last years ‘average’ 15kwh electricity.
In advance of my May21-May23 fixed ending next year.
Trustmeimadoctor said:
Grey_Area said:
I do, including my smart plugs usage for all the heavy hitters like tumble dryers etc…
It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
You ever looked at how much your smart plugs use?It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
Grey_Area said:
Trustmeimadoctor said:
Grey_Area said:
I do, including my smart plugs usage for all the heavy hitters like tumble dryers etc…
It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
You ever looked at how much your smart plugs use?It helps inform of just where it’s all going.
I also have an Emporium setup and App for the remaining circuits, like cooker and hob..
All recorded on spreadsheets.
Yes I do get out…
Frequently…
Mine use more power than the device that was plugged into it
I have recorded my readings since moving house 5 years ago.
Spreadsheet that uses graphs to see pattern and conversions to kWh . I used to use Excel but moved to Open Office. Basically the same but free!
I don’t have a smart meter yet so read and input to supplier and spreadsheet.
It is handy to forecast future energy costs to a decent accuracy when prices rise. I also sent my usage/ calculations to Octopus to reduce their DD change. They were a bit over my projected calculation. No quibbles from them.
The graphs show effects of energy saving actions for like for like periods when prices were reasonably stable or supplier changed.
Spreadsheet that uses graphs to see pattern and conversions to kWh . I used to use Excel but moved to Open Office. Basically the same but free!
I don’t have a smart meter yet so read and input to supplier and spreadsheet.
It is handy to forecast future energy costs to a decent accuracy when prices rise. I also sent my usage/ calculations to Octopus to reduce their DD change. They were a bit over my projected calculation. No quibbles from them.
The graphs show effects of energy saving actions for like for like periods when prices were reasonably stable or supplier changed.
Trustmeimadoctor said:
They do actually smets2 and upgraded smets1 meters send their data to the dcc from there using something like n3rgy you can export to csv
Yes, but going to a website, downloading a file and processing it isn't automated. And their API is £4,000 a year which is hilarious.I have recorded my readings since moving house 5 years ago.
Spreadsheet that uses graphs to see pattern and conversions to kWh . I used to use Excel but moved to Open Office. Basically the same but free!
I don’t have a smart meter yet so read and input to supplier and spreadsheet.
It is handy to forecast future energy costs to a decent accuracy when prices rise. I also sent my usage/ calculations to Octopus to reduce their DD change. They were a bit over my projected calculation. No quibbles from them.
The graphs show effects of energy saving actions for like for like periods when prices were reasonably stable or supplier changed.
Spreadsheet that uses graphs to see pattern and conversions to kWh . I used to use Excel but moved to Open Office. Basically the same but free!
I don’t have a smart meter yet so read and input to supplier and spreadsheet.
It is handy to forecast future energy costs to a decent accuracy when prices rise. I also sent my usage/ calculations to Octopus to reduce their DD change. They were a bit over my projected calculation. No quibbles from them.
The graphs show effects of energy saving actions for like for like periods when prices were reasonably stable or supplier changed.
paulrockliffe said:
Trustmeimadoctor said:
They do actually smets2 and upgraded smets1 meters send their data to the dcc from there using something like n3rgy you can export to csv
Yes, but going to a website, downloading a file and processing it isn't automated. And their API is £4,000 a year which is hilarious.Gassing Station | Homes, Gardens and DIY | Top of Page | What's New | My Stuff