Energy Spreadsheets - being organised?

WBCPB

DerwentMailman said:

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)  

This spreadsheet is what i would like, does it involve using macros etc? i am not very spreadsheet savvy but do have the free version of LibreOffice installed, is there an idiot`s guide to set one up online?
Regards 

molerat

Zellah said:

The 'big 6' offer very competitive smart meter only tariffs if going through uswitch or MSE CEC, i'm paying 2.05p kWh for gas with EDF with a daily standing charge of around 20p. Despite living in one of the more costly areas for energy (North Scotland).

For me on CA the cheapest Big 6 dual tariff is 18th at +£106 on my current, gas 3rd at +£33 and electric 24th at +£110. I must admit they are higher up the list on CEC but that is only because CEC don't show all the suppliers. No one is currently offering gas at that price, the cheapest Big 6 is around 2.8 p.  I did look at a really cheap Big 6 gas deal a while back but it was dual fuel only and they more than made up for the cheapness with the electric price. So I'll stick with my cheap non smart tariffs and read my meters once a month from 6ft away knowing that the reading is taken every month and it is accurate.

matelodave

My cheapest big six for the Eastern region (leccy only, we dont have gas) is no 17 on the CEC list and £263 more expensive so I'll stay where I am

victor2

WBCPB said:

DerwentMailman said:

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)  

This spreadsheet is what i would like, does it involve using macros etc? i am not very spreadsheet savvy but do have the free version of LibreOffice installed, is there an idiot`s guide to set one up online?
Regards 

That is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.

Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:

And here's the top sheet showing the result instead of the formulae:

If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.

JustAnotherSaver

victor2 said:

That is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.

Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:

And here's the top sheet showing the result instead of the formulae:

If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.

That's a nice basic spreadsheet too. Probably something i'd be better off doing. Walking before I run, which would be a change.

Do you have separate sheets for electricity and gas tariffs or are your tariffs on the same sheet?

The whole $B$4 thing is something i've not seen before and the multiple brackets thing.

You wouldn't believe that i hit Bs in Maths at school and A's in IT but with 20 years of not using any of it I struggle to recall it.

Out of curiosity, when you switch supplier do you update the single tariff entry or do you create a new entry so that you have a record of what your old tariff/s cost?

I do that when i track my fuel...

I don't actually need to track the P.P.L. as part of the spreadsheet and formula but I do it out of curiosity, nothing else. I can see on the 16th June 2007 the diesel fill up cost me 93.9p per litre.

What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off.

Talldave

$ references are great when similar formulas occur in multiple different locations on a sheet. I'm often making one axis absolute and the other relative, e.g. $B4.
However, you can just label cell B4 as "vatrate" and use that label in formulas. 

JohnB47

JustAnotherSaver said:

victor2 said:

What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off. 

This from an internet search: "If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes."
An energy tracking spreadsheet shouldn't need macros, in my opinion. The maths is relatively simple and can be done using formulas in relevant cells.

victor2

JustAnotherSaver said:

victor2 said:

That is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.

Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:

And here's the top sheet showing the result instead of the formulae:

If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.

That's a nice basic spreadsheet too. Probably something i'd be better off doing. Walking before I run, which would be a change.

Do you have separate sheets for electricity and gas tariffs or are your tariffs on the same sheet?

The whole $B$4 thing is something i've not seen before and the multiple brackets thing.

You wouldn't believe that i hit Bs in Maths at school and A's in IT but with 20 years of not using any of it I struggle to recall it.

Out of curiosity, when you switch supplier do you update the single tariff entry or do you create a new entry so that you have a record of what your old tariff/s cost?

I do that when i track my fuel...

I don't actually need to track the P.P.L. as part of the spreadsheet and formula but I do it out of curiosity, nothing else. I can see on the 16th June 2007 the diesel fill up cost me 93.9p per litre.

What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off.

Indeed, walk before you run.

That spreadsheet was just an example to illustrate the use of a formula and I just chose electricity as it is simpler than gas. If I were doing it for real, I would have both in one spreadsheet, and all the tariff data on another sheet within that file, as illiustrated.

In my actual spreadsheet, I do keep a history of all the tariffs I've been on. When a new one starts, I adjust the formulae to use the new tariff data and leave the old ones in place, so that I have a full and accurate history of my costs.

The use of the $ in cell references allows you to make a formula that you can copy from cell to cell, but still link to one certain cell all the time. It is well worth understanding - just look for some sort of tutorial illustrating the difference between relative and absolute cell references.

The use of brackets is important to appreciate too. Blow the cobwebs out of your school maths lessons, and it might come back! They both control and explain the order of how a sequence of operations in a formula is carried out. Apologies for going back to basics, but this is an example of how brackets work...

Suppose you have 1, 2 and 3 in cells A1, A2 and A3 respectively and you wanted the formula in A4 to be A1 plus A2 multiplied by A3, giving 1 plus 2 multipled by 3, which would be 3 multiplied by 3, or 9.

So you put =A1+A2*A3 in cell A4 and get the answer 7.

Why? Because of the "precedence" of operations. Multiplication has precedence over addition, so the formula is executed as 1 plus (2 multiplied by 3), where 2 is multiplied by 3 before 1 is added, which gives you 7. See how the brackets illustrated what was happening.

The result would be the same if entered as =A1+(A2*A3).

If you wanted 9, the formula would have to be =(A1+A2)*A3

So, sometimes, brackets are a necessity, other times they just help make the formula more understandable.

As for macros, they are a way of automating tasks you do repetitively, and are not needed in such analysis.

By the way, your spreadsheet shows what is presumably the starting and ending mileage in each row (2nd and 3rd columns). If column 2 is always the same as column 3 in the row above, it is redundant. You can delete the column and make any formulae that refer to it (such as the elapsed miles) refer to the cell above in column 3.

Without seeing the formulae, there are probably other redundant cells too, or you've entered data where it could be calculated from others. Your indication of the PPL is a bit strange too, but ultimately, it is what works for you. There are no hard and fast rules about how you must do things, but there are ways to make life easier.

Oh dear, I sound like a teacher!!

JustAnotherSaver

victor2 said:

By the way, your spreadsheet shows what is presumably the starting and ending mileage in each row (2nd and 3rd columns). If column 2 is always the same as column 3 in the row above, it is redundant. You can delete the column and make any formulae that refer to it (such as the elapsed miles) refer to the cell above in column 3.

Without seeing the formulae, there are probably other redundant cells too, or you've entered data where it could be calculated from others. Your indication of the PPL is a bit strange too, but ultimately, it is what works for you. There are no hard and fast rules about how you must do things, but there are ways to make life easier.

Oh dear, I sound like a teacher!!

Thanks for your explanation. I'm due to shift from Scottish Power to Shell on 31st December (let's see if that goes without error!) but i'll certainly be keeping tabs on consumption from now on.

To explain my sheet since you asked ...

If you take the date as A, the first mileage as B so on and so forth then basically the formula in say B248 is just entered as [=C247] (without the [ ]). I then highlight the bottom corner and just drag it down. All i ever actually do is enter in to the C column whatever my mileage display is when i fill up.

D is the P.P.L. which serves as nothing more than a reference for a later date if i'm ever nosey and want to look back. It doesn't tie in with any formula.

E is obviously the price. This is just inputted off the receipt.

F is the mileage display - how  many miles i've covered since the last fill up. So when i fill up, i'm the guy who's annoying to the impatient guy behind because i'm noting down my total mileage as well as the miles i've covered (for the decimal point).

G is the litres put in.

H is the MP.G. which after some googling i found is miles / litres * 4.5461 so basically it reads [=F266/G266*4.5461] (again without [ ]). My initial Googling took the calculation as 4.54, Googling further brought 4.544 and Googling further still brought an apparent exact number as 4.5461. Who knows.

It may be able to run more efficiently or better in another way. I don't know. That was the best I could do with the knowledge I have though.

bagand96

Quick question for those who have their own set up... And use absolute cell reference for tarrif rates... 

How do you handle when you change supplier or tarrif? Do you input new tarrif into new cell and change the formula $ reference? Or start a new sheet for each tarrif?