Friends, as you may or may not know, Saturday night is the night of week I like spend documenting large spreadsheets. Lucky me!
In this article I will be describing how to use the spreadsheet I have developed over the last couple of weeks and used in the last couple of articles. The spreadsheet allows one to estimate the likely costs of using particular electricity tariffs – the Octopus GO, FLUX and COSY tariffs – if your dwelling has a domestic battery and solar PV panels. My hope is that it will help people make rational choices about which option is best for them.
Download
The Excel spreadsheet can be downloaded from this link [link updated to v3.2 on 23/3/2023], If you are downloading this macro-enabled file on a Windows computer, then the macros will probably be blocked by default. To change this you may need to first close the file in Excel, then right-click on the file’s icon and select the ‘Properties’ pane. Here you should see a tick box labelled “unblock”. If you unblock the file then it should work correctly.
Please note, the spreadsheet comes with no guarantee of anything at all, and it can at times be very slow to re-calculate.
Structure
The spreadsheet consists of two parts.
- At the top are several boxes that set the parameters of the simulation, and which show the results. I think of this as a ‘dashboard’
- Below this are 8,760 rows, one for each hour of the year. The spreadsheet proceeds through the year hour-by-hour simulating the flows of electricity from solar PV panels and the grid, into and out of batteries and your dwelling.
Throughout the spreadsheet, cells which are ‘inputs’ i.e. cells that you might reasonably want to change, have a yellow background with red text. Cells which are ‘outputs’ i.e. which contain the results of calculations have a red background with white or yellow text.
Click on image for a larger version. The visual appearance of ‘inputs’ and ‘outputs’.
The Dashboard: Overview
Click on image for a larger version.
The dashboard has controls in four regions.
- Regions 1 & 2 are about the tariff being simulated and the prices of the tariffs in each of their cheap, medium and high periods.
- Region 3 sets the parameters of the battery and the charging strategy, the amount of solar PV generation, and the likely household load.
- Region 4 contains the results of the simulation.
The Dashboard: Tariff Section
Click on image for a larger version.
After selecting the tariff to be investigated in the drop down menu, the selected tariff will appear in the box on the far right.
The prices of the different tariff stages can be changed if you desire.
The timings of the tariffs cannot be changed on the spreadsheet. They are set in a Visual Basic macro with the code below. If you understand this sort of thing then you can see how you could adapt the spreadsheet to a different set of tariff timings.
Click on image for a larger version.
The Dashboard: Main Settings
Click on image for a larger version.
The settings for the simulation have four main parts.
The Battery Settings are as follows
- Battery capacity describes the amount of electrical energy the battery can store.
- Round trip efficiency accounts for the energy lost as electricity is stored in the battery and then later drawn from the battery.
- Charge Rate is the rate at which battery charges. This limits how much electricity can be stored in the battery over a given period.
- The initial state of charge is the assumed state of charge at midnight on the 1st January.
Click on image for a larger version.
The cells referring to summer and winter should really be in the neighbouring box about charging strategy [Edit: they have now been moved]. Why are they there? For some systems, it can be sensible to switch between different charging strategies through the year, depending on the amount of solar energy available. For my own system:
- In winter I charge the battery at night using cheap rate electricity and the battery then runs down during the day.
- In summer, there is no need to charge at night because the battery is charged for free during the day by solar PV electricity.
The two boxes allow one to choose the days of the year on which to switch strategy.
In the ‘Grid Charging Strategy‘ box one can choose between charging when electricity is cheap, and/or charging in the hour before electricity becomes expensive. Either option can be chosen to be active in summer, winter or both.
In the solar factor box, one can set the expected amount of solar generation expected. This is generated by scaling the hour-by-hour solar data by the factor shown in the box.
Note that selecting a target amount will give an accurate result if the average solar generation from 2005 to 2016 is selected in the drop down menu at the bottom of the box. If one selects solar data from a particular year then the amount of generation will vary in line with that years variable output.
Click on image for a larger version.
The final box simulates how electricity demand from the household changes through the year.
Click on Image for a larger version.
Demand consists of two components: a steady consumption every day, and a component which peaks in mid-winter simulating the use of a heat pump.
The length of winter depends on a setting from 1 to 5 as shown in the figure below.
Click on Image for a larger version.
How the simulation works.
Each row of the spreadsheet from row 48 downwards calculates the state of charge of the battery, the household demand, and the solar PV according to the settings on the dashboard.
The simulation works row-by-row proceeding hour-by-hour through the year. The columns are as follows
- A: Index
- B: The day of the year expressed as a decimal day.
- C: The hour of the day, used for calculating the appropriate tariff
- D: The season of the year WINTER or SUMMER according to dashboard settings
- E: The Tariff Rate (cheap, medium, or high) determined by the Visual Basic code described above.
- F: G: & H: The hourly background & variable consumption – and their total.
- I: The daily average of the demand
- J: The time of day expressed as a fraction of a whole day
- K: Blank
- L: The 3-day running average of solar generation (used for plotting)
- M: The hour-by-hour solar data selected in the drop down menu in the SOLAR box. This is looked up from the data table in columns AE to AQ
- N: Modified demand: this is the difference between current demand and the amount of solar PV currently being generated.
- O: The amount of energy delivered to the battery (if positive) or drawn from the battery (if negative). This is calculated according to the current state of charge of the battery.
- P: The amount of energy drawn from the grid (if positive) or sent to the grid (if negative). This is calculated according to the current state of charge of the battery.
- Q, R, S, & T : Imports: If column P indicates that electricity has been imported, columns R, S and T list the amount of it in each tariff charging rate: these columns are then totalised at the top to calculate the annual amount to be paid.
- U, V, W, & X : Exports: If column P indicates that electricity has been exported, columns U, W and X list the amount of it in each tariff charging rate: these columns are then totalised at the top to calculate the annual amount of revenue accrued.
- Y: Hourly cost of grid imports
- Z: Hourly amount due from grid exports
- AA: Depending on the charging strategy this is when the battery charges from the grid. Household consumption is also met by the grid during these periods.
- AB: The amount of electrical energy sent to the battery.
- AC: The current state of charge of the battery.
- AD: Blank
- AE to AQ. Hourly solar data for the years 2005 to 2012 downloaded for the EU Sunshine database for my 4,000 kWh/year solar system in Teddington.
Graphs
Click on Image for a larger version.
Scrolling down the spreadsheet will show four graphs which can be helpful in understanding what is happening.
The first graph shows three quantities charted across each day of the year:
- The 3-day average of the selected solar data
- The daily household demand
- The capacity of the battery
The second graph shows the state of charge of the battery charted across each day of the year.
The third and fourth graphs show the state of charge of the battery during a typical summer and a typical winter day.
Protons for Breakfast 
March 15, 2023 at 4:49 pm |
Hi Michael,
I’ve downloaded your incredible spreadsheet for comparing FLUX v GO v COSY. I have carried out the UNBLOCK under Properties for the Excel file but when I enter Octopus rates for my area (Lincs), all the Output boxes in cells range I30 > P30 down to I36 > P36 continue to display either #VALUE! or ### plus columns E and O to AC row 48 to end display #VALUE!
As I’m an Excel amateur, I’d be grateful if you would advise on how to produce numerical values where I have #VALUE! or ###
Thanks in anticipation of receiving your help.
Peter [wellingore2]
March 15, 2023 at 6:02 pm |
Peter
Hi. Mmmm. It’s tricky to diagnose these things remotely, but try this.
Cells with “#####” may just be too narrow to display the number or text in the cell in the font on your spreadsheet. So try dragging the cell divider at the top of the spreadsheet to allow more space.
The “#VALUE!” entries are more puzzling. Were these present when you first downloaded the spreadsheet? If they were then I don’t know what could be going on.
If the downloaded version initially looked OK then it could be that you have inadvertently entered a number which causes an error. As the spreadsheet warns – it does not have limits on many of the values you may enter. So you could try downloading it again, UNBLOCKing it again, and begin by making some subtle changes – change a value by a few pence, and see if the numbers seem to change reasonably. Note that on some computers the changes can take a minute or two to work their way through the spreadsheet.
If you still have a problem, drop me an e-mail at house@depodesta.net with some screenshots.
All the best
Michael
March 16, 2023 at 12:00 am
Michael,
Thanks for your reply.
Happy to report problem solved. My computer literate son pointed out that within Libre Office (opens and saves into all Windows Office applications) I needed to open TOOLS > OPTIONS > Libre Office > Security > Macro Security > and set to MEDIUM to allow macros to automatically open. Your marvellous s’sheet then produces the comparison data for Flux / Go / Cosy tariffs.
THANK YOU.
Peter
March 23, 2023 at 2:40 pm |
Love the spreadsheet, great effort. I’m working through it to help inform my solar and heat pump decision making. Quick query – why does the ‘Grid Export (£)’ (column Z) appear to reference the SELECTED tariff import prices (W7-9)? I’m still getting familiar but had expected it to reference the export equivalent? There could be macro magic or another misunderstanding on my part of course! Many thanks
Andy
March 23, 2023 at 4:36 pm |
Andy. Well-spotted and thank you: that was a genuine error.
I have updated the link on the original page or you download a corrected version using the link below.
https://www.dropbox.com/scl/fi/x2mk4444mutympsz0luo6/Tariff-Comparison-Spreadsheet-3_2.xlsm?dl=0&rlkey=nwq2q8kqgz18lzdmywf5pqzy1
Apologies for the mistake, but I am sure you can understand how easy it is for such mistakes to slip through.
Best wishes
Michael
March 23, 2023 at 4:50 pm
No problem Michael! As soon as I sent the comment I realised the discrepancy doesn’t actually drive the dashboard, so no harm done. Anything this involved is likely to have a few bugs, I know my spreadsheets do! Thanks again for your work here and the wider blog, its really helpful!
March 24, 2023 at 10:00 pm |
Hi Michael,
This spreadsheet is great. I am just trying to get me head around the logic and wanted to know if you could explain how you downloaded the hourly solar data.
“AE to AQ. Hourly solar data for the years 2005 to 2012 downloaded for the EU Sunshine database for my 4,000 kWh/year solar system in Teddington.”
And which columns from that exported data you used in columns AE-AQ. My system is 7kwh so I guess it may give different results to the data already in the spreadsheet.
Thanks,
Nigel
March 24, 2023 at 11:33 pm |
Nigel, Good Evening.
The data is from this site: https://re.jrc.ec.europa.eu/pvg_tools/en/
This is an incredible resource. I wrote about it in this article.
The You Tube Channel ‘Gary Does Solar’ explains how to get basic information for your home in this video
If you want to download the data itself, then you need click on the ‘Hourly Data’ Tab: enter the location of your system and its azimuth (compass direction) and elevation (slope) and then select the period of interest from 2005 to 2020. I suggest just downloading the data year by year.Then click the “download CSV” button. CSV stands for comma separated values and can be read easily by Excel.
Does that make sense?
All the best
Michael
March 27, 2023 at 2:21 pm
This site and your youtube channel is very informative especially as I am in the process of getting quotes. The company I have had my 1st quote say a week for the surveyor and it has the panels and better in stock ready to go. I am slightly confused as I seem to be using a lot more than most (11,000 kw) the Growatt 6.5kw battery and 14 Hyundai Panels equate to £14,000 – Modules
Inverter System Grasol (10 year warranty)
OFGEM approved generation meter High quality BETAflam 125 Flex 4mm-6mm 1500V black DC cable 3 x Premium C&D SDP252 Isolator 16-32A 1000V
Already with Octopus and they said to use either G0, Flex or one of their other tariffs but not sure if I will be able to give any unused KW back due to my high usage (5 Children and all heating is gas). If you can help it would be appreciated after days of looking at many circumstances. Jason
March 27, 2023 at 5:04 pm
Dear Phillips Family, good afternoon.
I’m afraid I can’t really answer specific questions here – there are many relevant factors of either you or I may be unaware and I don’t want to mislead anyone. Here are a couple of comments.
You say you use 11,000 kw. I assume you mean 11,000 kWh/year for electricity alone? If that is correct then that is a high usage” 30 kWh/day equivalent to a constant 1.25 kW. Does that include any heating or hot water?
If your panels have a reasonably good orientation then I would expect that in summer you will generate just under 30 kWh/day – with a peak generation rate of between 3 kW and 4 kW. So during that peak and after charging your battery, you will almost certainly export some electricity in the middle of sunny days. But on the GO tariff you will not earn much back for this.
Does that help?
All the best
Michael
April 4, 2023 at 9:53 am |
I just posted this on your original article before realising you’ve updated it.
You might want to consider switching to Flux for the summer months and back to Go for winter. They will let you switch as long as it’s not within 30 days of the last switch.
Flux throws a spanner in the works with solar diverters if you have one though, as it’s better to export. That realisation also made me realise I was also only saving what the heat pump was going to use heating the water at cheap rate times which is an order of magnitude less cost than what you might casually first think of compared to using an immersion heater during the day!
The diverter becomes useful again on Go but as I disappointingly now realise it’s saving is reduced by COP and the off peak factor. I need a new use for it.
One other subtlety worth mentioning is whether you charge your battery overnight on Flux at all. I’ve not fully worked this out yet but if you don’t AC charge you store more solar before the battery gets to 100% and when it starts to export the power is limited by your inverter e.g. 6kW but you will then be wasting power e.g. 2kW from an 8kW PV array feeding a hybrid inverter. You’ll see this as a flat top peak. Whereas the full 8kW peak is visible when the battery is less than 100%
If, say, 9kWh of AC charge had been added overnight the battery would have got to 100% and start wasting 2kW that much sooner potentially losing up to 9kWh. This all depends how much sun there is and whether your system allows for simultaneous DC charging with full AC output.
August 31, 2023 at 7:48 am |
Yes, I came to the same view ( sorry, read your post late ).
April 8, 2023 at 7:38 am |
Question for you, is this comparison over say a year… what if you were on Flux for summer months, and Go for winter?
April 8, 2023 at 8:27 am |
Paul, Good Morning.
I think using FLUX for summer – where you might export a lot and import very little – and GO for winter – would be the lowest cost.
I haven’t run the experiments, but that’s my guess.
Best wishes
Michael
April 17, 2023 at 2:16 pm |
Fantastic work, thank you. Would it be possible to cater for EVs in the demand section? As the world moves to an EV/Hybrid world this option wouold be invaluable to help compare tariffs.
April 17, 2023 at 5:55 pm |
Mike
Thank you for your kind words. I am looking into the possibility of developing this.
As it, the changes involve being able to specify the household demand – not just on average through the year – but through every of the day so that, for example one can download (say) 10 kWh every night or every other night.
I think I know how to do this but please don’t hold your breath – it’s right at the limit of my capability.
Best wishes
Michael
May 18, 2023 at 5:02 pm
Michael – a thought re this feature request.
There are other loads in addition to an EV which can be considered ‘movable’, for example immersion heating, hot tub, washing machine, etc. In the winter one would probably want to run these in an overnight cheap period (especially if using GO) whilst in the summer, one would run them during the day.
Unfortunately this adds further to the complexity….
Kind regards,
Nick
May 18, 2023 at 7:11 pm
Nick, Thank you. Those are good points. I will add then to the ‘to do’ list.
Michael
May 15, 2023 at 9:56 am |
Hi Michael
Firstly, this is a great spreadsheet. Must have taken a ton of work.
Can I ask what is probably a really stupid question but I just can’t work it out. In the Calculated Annual Quantities box I have 3752 kWh Solar and 3699 kWh Consumption. Both figures totally correct. But to the side of this it shows a Total Import of 3445 kWh and a Total Export of 1664 kWh. I just can’t see how the import and export figures in any way match my solar production and domestic consumption figures, unless I have made a major error in the inputs. Sorry if this is totally stupid but any clarification would help.
Best wishes
Chas
May 15, 2023 at 12:40 pm |
Chas
Hi. I am looking at this now – and it’s the first tome I have looked at this spreadsheet for a couple of months. At first glance the numbers do look odd, but I would like to try to reproduce the results.
Could I ask you please to
(a) check which version you are using: this is cell B2
(b) send me a screen shot (or shots) of the ‘dashboard’ covering the BATTERY, GRID CHARGING STRATEGY, SOLAR FACTOR, and DEMAND
I’ll try and reproduce your result and get to the bottom of it. You can e-mail me at house@depodesta.net
Hopefully we can get to the bottom of it!
All the best
M
May 15, 2023 at 8:10 pm |
Hi Michael
I’m really appreciating your Excel efforts and the analysis behind them, having attempted similar things in the last couple of weeks you’ve saved me a lot of additional effort! Thanks very much. However, I’m about to extend this worksheet to include the PVGIS data 2005-2020 for my location, and I’m wondering what you did with the leap day data… did you remove 29 Feb, or 31 Dec, something else? I just want to keep it consistent with your input.
Cheers, thanks again
John
May 15, 2023 at 8:30 pm |
John, Good Evening. And thank you for your kind words.
I can tell you have thought about this because that’s the kind of question that only occurs once one gets down to it.
I’m afraid I simply discarded the last day of each leap year. I didn’t think about trying to be clever, but it leap years introduce a lot of complicated for very little benefit – they only affect the results by less than 1% which seemed acceptable in this context.
Good luck with your endeavours. Do let me know if you make progress!
All the best
Michael
May 15, 2023 at 9:14 pm
In old speak, ROFL! Since I had no indication when you’d reply, and being impatient to see the results, that’s exactly the strategy I adopted, for exactly those reasons. Now checking tariffs – non-trivial exercise, being dependent on location…
Cheers, thanks
John
May 16, 2023 at 9:51 am |
Hi Michael
Another question. Cell Q20 (Solar Factor) contains (Q16/3848) i.e. Target PV Generation / a magic number, 3848. Where did the number come from? If you’ve put 1kWp into the PVGIS, you get out the Wh each hour. So you know what the average Wh/kWp are, so if you know your array kWp size you can work out the annual kWh.
Since you have a 4kWp array, I can see that that might be the average kWh/yr, in which case if one is using a different array that would need to refer to the appropriate annual total.
Cheers
John
May 16, 2023 at 7:31 pm |
John, Good Evening.
Yes, it’s a fudge factor.
The data I downloaded from PV-GIS was for a 4 kW-peak array. The variability of this data is likely to be typical of the variability just about anywhere in the UK, and it’s that variability that makes it necessary to have such a complicated spreadsheet – if it was the same every day this would be easy!
The figure that most people know about their solar PV system is the number of kWh it is supposed to generate in an average year. So the 384 figure scales the PV-GIS data so that it matches their system for output, but includes realistic variability.
Does that make sense?
All the best
Michael
May 17, 2023 at 2:55 pm
Hi Michael
OK, I’m in the situation of having just acquired a heat pump this week, on much the same CO2 reduction basis as yourself, and I’m just looking at PV+battery acquisition now. I don’t have any ASHP data yet, so I’m extrapolating from existing gas/electricity usage data, and looking at proposed array/battery sizes. I’ve inserted my location solar data extended to 2020, based on a 1kWp array, added a cell for array kWP that uses the average kWh/kWp to give the annual kWh target. That allows me to fiddle with the array size, which is what I’m buying (rather than annual kWh) and have the sheet tell me what’s sensible. I’ve also added a rather informal section to use the observed usage data to calculate potential consumption with ASHP, as input to the Demand section.
Happy to share it with you if you like. Thanks very much for the brilliant approach to the problem!
May 18, 2023 at 10:31 am
John
Yes, do please share. You can write to me at house@depodesta.net
All the best
Michael
June 27, 2023 at 6:27 pm |
Michael,
Thank you so much for sharing your spreadsheet. With PV installed last year and recently a battery added, I have been thinking about how best to exploit these via retail tariffs. Your sheet is helping me with this.
Adding ‘peak extra Summer demand’ (hot tub) would be useful to myself.
Best regards,
Peter
June 27, 2023 at 10:24 pm |
Peter,
I do have a planned update that will allow specification of hour by hour consumption for a winter and a summer season, but it will be a few weeks yet before I can implement it – too much to do!
Best wishes
Michael
August 31, 2023 at 7:38 am |
Just doing similar sums.
One idea is to switch tariffs twice a year – ie Octopus Go in the winter and Octopus Flex in the summer.
August 31, 2023 at 11:34 am |
Yes, a couple of people have suggested that. My understanding is that Octopus are cool with that – and it does look like it would be profitable!
August 31, 2023 at 7:40 am |
BTW, you might be better off switching to Google Sheets … much better for on-line collaboration and sharing
August 31, 2023 at 11:35 am |
Peter, Thanks for the suggestion, and you may well be correct. I’ll take a look, but I’m an old dog and I am not good at new tricks…
M
September 4, 2023 at 5:54 pm |
Wow, I’m impressed. I’ve done my fair share of Excel and VB programming over the years so I appreciate how much work must have gone into this.
I had an interesting journey finding your spreadsheet. I’d downloaded some sheets from another website but they weren’t explained very well and all the calculations weren’t laid out. I don’t trust a spreadsheet that just gives you an answer without understanding it. I’d also looked at the Gary Does Solar utility which is great but limited to just one day at a time. So I came to the realisation that I’d probably have to write something that accumulated the data over a year and factored in all the different variables. “That’s going to be a big job” I thought, “I wonder if anyone has already done it?”. Google found your spreadsheet and I’m truly grateful.
I’m looking at adding a battery to my 4.7kW array (3.68kW inverter). I’ve been playing around with the numbers a bit and noticed that if you set “Charge during CHEAP rate” to No for both Winter and Summer, this will give you a significantly lower Total bill than having it set to yes for winter or summer. It means that the battery spends most of the winter near flat in the winter which is probably not good for battery health but it wasn’t the result I was expecting.
Having given it further thought I wonder if it’s an effect of the way demand is averaged out evenly over the day in column F. Unless I’m missing something, I’m not seeing any intra-day variation in demand being modelled like the way Gary does in his utility. Just a thought. Still doesn’t detract from it’s usefulness and will keep me occupied for a good while mulling over options.
Thanks
Tim
September 4, 2023 at 7:56 pm |
Tim,
Good Evening. I am glad that you (a) noticed how much work was involved in the spreadsheet and (b) noticed its shortcomings.
I think the way to deal with this is to develop a set of ‘daily use’ profiles – e.g. one for summer and winter. This would be a (say) half-hourly profile which could include (say) charging an EV overnight or running a heat pump hot water cycle.
I sort of know what to do – but it may take a while before I can face this!
Anyway – do feel free to take anything you find useful – e.g. 10 years of half-hourly solar data! – and make your own contribution.
Best wishes
Michael
September 4, 2023 at 8:45 pm
I know what you mean. Takes time to get your head back into a piece of code. Problem is that once you’ve done that you’re thinking about nothing else. I usually end up walking away and force myself to think of something else and usually my sub conscious works out the solution to the problem.