Logging OVO Actuals Data and Octopus Agile Half-hourly Prices

[TerryE]

AFAIK the OVO portal doesn't have a publicly available RESTful API for you to be able to download the sort of detailed half-hourly usage analysis that you can view online through their portal.  However the portal's user interface makes heavy use of Javascript (JS ) scripts to do the webpage renders, and these in turn make internal JSON callbacks to the OVO server to fetch the data.  I reverse-engineered this internal JSON API using a Chromium browser and its webtools window, so that I could automate downloading this usage data and aggregating store it in a database.  I have now collected 5 years of this data.  I wrote earlier versions in Python and Perl, but I now only maintain a JS script version that runs as part of my NodeRED system used to control my CH, DHW and temperature logging.  If anyone is interested in doing the same, I can share my script and NodeRED flow, and if you have NodeRED running on an RPi or equiv, then you should be able to use this pretty much as-is.  Alternatively if you know enough Javascript and MySQL to read and understand what I am doing, then you could recode this into your favourite scripting language.

 

I am also considering switching to an Octopus Agile Import tariff, so what I want to do over the next few months is to benchmark my current OVO plan vs Octopus Agile to see if there is an overall benefits case for our doing this.  Luckily Octopus do make their Energy API publicly available here. I plan to do a similar download exercise for Octopus Agile half hourly prices, as well as some what-ifs based on having this day-ahead data available. 

 

For example my CH algorithm also works day-ahead and computes how many hours of 3kW Willis heating the in-slab UFH to keep the house within planned set points.  Using an example 360 minute heating requirement, I currently schedule this during the 7-hour E7 cheap rate window (from 1-7AM), but I could just as easily use the cheapest 12 half hour slots during the day. I could also switch some heating to my (remotely controlled) oil-filled rads, turning them on during the cheapest half-hour slots.  Ditto for scheduling our SunAmps.

 

Also because the price dips tend to follow low-demand periods, e.g. prices between 0-4 AM are often a few pence per kWh or negative, we could also reschedule time-settable devices such as dish and cloths washing to use these cheap windows (ours are electrically heated cold-fill so are quite power intensive when heating).

 

We seem to have around ⅓ kWh / hr base load in the house from electronic devices, fridges etc. Another what-if game to play is what would the cost benefits of adding say a 6kW battery to shift all use to these extremely cheap priced half-hour slots, but that's a separate topic in its own right. I suspect that we aren't there yet but soon.  😊

 

Edited October 15, 2023 by TerryE

[TerryE]

I've done the Perl script that downloads the prices since the start of the current Agile Tariff in March for a given Grid supply point.  (You can lookup the GSP for your MPAN.)  I'll dump this into a SQL table and mod the script so that it only requests new data from the last dump.  Lots of fun analysis to follow. 🤔

[S2D2]

If only every company were as open with their APIs, another big plus for Octopus.

[TerryE]

I've done the 1st cut of two Perl scripts. (Sorry.  I know Perl ain't too fashionable these days, but it's the first productivity scripting language that I learnt about 30 years ago, and I still prefer it for this sort of quick and dirty stuff than Python, JS, PHP, etc.)

• The first downloads the half hourly price data for the current Octopus Agile Tariff into a MySQL (actually MariaDB) table. I've written this so I can add extra days of price data from time to time.  My meter is in GSP band B.
• The second does some analysis of the downloaded to do aggregated views.

I can upload to my public files server if anyone is interested.  Anyway, here are a couple of analysis cuts taken by importing the CSV data into Google Calc and exporting the charts to GIF:

This shows how the average (ex VAT) pricing reflects the typical "Duck Curve".  Incidentally my current Ovo E7 rates are 18.12p and 29.21p per kWh so a lot more overall.

This second analysis is derived by sorting the half hourly price slots low to high, and this underlines the price advantage you can get if you can do load time-shifting across the day.  For example at the moment I heat my slab during the E7 off-peak window, even though bunching the heating overnight gives a residual about a 1°C ripple on the room temp, but we live with this because doing so works at 40% cheaper than using peak rate.

 

However I could just as easily use any N hours and also spread them through the day to flatten the ripple.  We also need to schedule where practical cooking and use of cold file whitegoods outside that expensive 3-6PM high demand period and preferably use 10PM - 4AM in the winter or 11AM-2PM in the summer for peak use.  Because I've got my actuals usage by half-hour for the last 5 years, I can do some "what-if"s to get an estimate of what switching to a flexible time-day-strategy will save us.

 

PS. 

code: "AGILE-FLEX-22-11-25"
        brand: "OCTOPUS_ENERGY", direction: "IMPORT"
        display_name: "Agile Octopus", full_name: "Agile Octopus November 2022 v1"
        description: "With Agile Octopus, you get access to half-hourly energy prices, tied to wholesale prices \
            :          and updated daily.  The unit rate is capped at 100p/kWh (including VAT)."
        available_from: "2022-11-25T00:00:00Z", available_to: N/A, term: N/A
        links: {"href: "https://api.octopus.energy/v1/products/AGILE-FLEX-22-11-25/", method: "GET", rel: "self"}

code: "AGILE-FLEX-BB-23-02-08"
        brand: "BULB", direction: "IMPORT"
        display_name: "Agile Octopus", full_name: "Agile Octopus February 2023 v1"
        description: "With Agile Octopus, you get access to half-hourly energy prices, tied to wholesale prices \
                      and updated daily.  The unit rate is capped at 100p/kWh (including VAT)."
        available_from: "2023-02-08T00:00:00Z", available_to: N/A, term: N/A
        links: {"href: "https://api.octopus.energy/v1/products/AGILE-FLEX-BB-23-02-08/", method: "GET", rel: "self"}

 

It looks like I pulled down the wrong tariff.  The AGILE-FLEX-BB-23-02-08 tariff is actually the flexible tariff for ex-Bulb customers; AGILE-FLEX-22-11-25 is the current Octopus one.  Both are unrestricted, green, variable, monthly in-arrears, consumer tariffs.  Hopefully their pricing is the same or very similar, but let me redo this analysis. 

 

Edited October 20, 2023 by TerryE

[S2D2]

6 minutes ago, TerryE said:

 

 

Interesting, though with all Octopus tariffs it depends... Cosy is ~16p/kWh in the cheap period though depends on a battery to stop that figure climbing outside the cheap period.

 

You'd need to factor in the increased cost of times outside these figures due to your high base load. I've been interested in this tariff too but haven't done the sums yet.

[TerryE]

Most tariffs require the provider to do ToD and within each quarter price levelling based on futures forecasting.  This involves risk and this risk must be factored into any price-to-consumer calcs.   I am happy to "self-insure" on such forecasting, as I have the cash floats to do so.

 

I haven't done the sums yet either, but I have enough data now.  Watch this space. 🙂

 

[SteamyTea]

10 hours ago, S2D2 said:

Cosy is ~16p/kWh in the cheap period

Really

How much is the expensive period?

EDF are now charging me 12p and 39p/kWh.

Stitched up with the 67p/day standing charge.

[S2D2]

10 minutes ago, SteamyTea said:

Really

How much is the expensive period?

EDF are now charging me 12p and 39p/kWh.

Stitched up with the 67p/day standing charge.

Relative to standard variable it's -40% in the six hour cheap period, +60% in the three hour peak period, around 42p. You can check exact rates/standing order for your region here: https://octopus.energy/smart/cosy-octopus/?location=rates

[TerryE]

1 hour ago, SteamyTea said:

How much is the expensive period?  EDF are now charging me 12p and 39p/kWh.

 

Yup and OVO are charging me 19p for off-peak.  EDF are taking the futures risk here or laying them off on their wholesale suppliers via fixed N-day ahead contracts -- which is why these prices can be fixed.  As @S2D2 says, Octopus have an equivalent Cosy tariff. 

 

With the Agile tariff they pass their half-hourly supplier costs straight onto the customer, albeit with a built-in handling mark-up.  The customer takes the risk, so the prices can peak at a lot higher than these fixed tariffs, but they should be less overall, especially if you time-shift demand to the cheaper half-hour slots.

 

[TerryE]

Here is the graph for the Octopus tariff. It's essentially the same unit pricing except it goes back into 2022.  I've only included the 2023 weeks:

[Alan Ambrose]

Firstly, thanks for doing this analysis and putting it up for us to see - very interesting. In that last graph is week 0 beginning of Jan? I’m surprised to see low pricing then?

[SteamyTea]

11 hours ago, Alan Ambrose said:

In that last graph is week 0 beginning of Jan? I’m surprised to see low pricing then

Can depend when the first working day is.  Also can depend which spreadsheet option was used to start the week numbers.

The Christmas week is a low usage week.

 

 

 

[Alan Ambrose]

@SteamyTea - are you a physicist? - you 're always on the button with materials calcs etc, and now Excel minutiae?

 

I'm also interested in whether Agile gives the customer a proper reduction for taking on / managing the risk. I've seen that, for instance, the tiers of premium reductions for various excess levels when buying car & house insurance often don't.

 

I still don't quite get why the prices go like that in the winter. Also, why the 4-6pm hump - I would have guessed that industry / offices would be using power during the day. Lastly, it would be interesting to plot against wholesale prices.

Edited October 21, 2023 by Alan Ambrose

[SteamyTea]

32 minutes ago, Alan Ambrose said:

@SteamyTea - are you a physicist? - you 're always on the button with materials calcs etc, and now Excel minutiae

No.

I studied automotive engineering, then renewable energy, then environmental science, then climate science and threw in a PGCE, as well as a photography and sign language course for a laugh.

My RE course was mostly taught by physicists and I found I had an affinity to thermodynamics and statistics. Ended up teaching statistics, if nothing else, it helps one spot bullshit pretty quickly.

I am moving away from Excel and onto LibreOffice Calc. It is no where near as good, but I am too tight to pay for MS Office.

 

40 minutes ago, Alan Ambrose said:

still don't quite get why the prices go like that in the winter

Much of it will be to do with the auctioning system. Some producers will have a mix of fixed price, variable price and half hour future price. There will be times it is not worth them switching off a generator, as they know they have to supply in an hour or two, so it works out cheaper, for them, to pay to have it taken away.

I believe the price of non delivery is very high, so they try to avoid that at all costs.

 

I am starting to think that the current auction system may not be the best method in the future, mainly because we will have cheaper storage and better management.

Octopus is really playing an interesting game, but at a tiny scale that has no real impact on prices or emissions at the moment. If all the major power resellers did it, then the system would collapse both technically and financially.

The idea system is a steady state generation from the lowest polluting systems, at an affordable price, then storage systems deal with the variations at a volatile price.

We are a long way from that at the moment.

[TerryE]

On 20/10/2023 at 21:16, Alan Ambrose said:

is week 0 beginning of Jan?

 

Speadsheet WEEKNUM() by default starts on Sun with week 1 starting on the first Sunday in the year, in this case the 1^st.  I initially extended back into 2022 but this caused a bump back to 52 in the previous year.  So I arbitrarily did at cut-of at "0" which started on Christmas Day 2022.

 

On 21/10/2023 at 12:04, SteamyTea said:

Octopus is really playing an interesting game, but at a tiny scale that has no real impact on prices or emissions at the moment. If all the major power resellers did it, then the system would collapse both technically and financially.

 

The current system is going to have to go through some phase change (using Tony Seba's phrase) because of the inexorable encrosion of non-baseload renewables.  Getting businesses and a reasonable fraction of the end-consumer base onto tariffs which encourage time shifting helps to remove peaker generation capacity.  

 

 

Edited October 23, 2023 by TerryE

[TerryE]

OK, here are my results based on my Dec-Oct actual OVO reading recorded by my Smart Meter:

 

• Dec was a funny month because of the Russian energy squeeze, a triple whammy: Russia; we were still on an old fixed tariff for the first few days, and the short term prices were in panic which OVO were better buffered from.  We might get a similar pulse this year but we are on an OVO variable tariff and the markets are better prepared so I have decided to ignore this as a one-off glitch.  (Note that the Oct figures are off since it is an extrapolation of the first 20 days, but the heating only started to come on on the 17th so this will rise slightly.)
• That aside, the Octopus Agile tariff gives overall lower monthly prices than OVO E7, but not hugely so.  The main difference is during those May-Sep summer months where the usage profile is pretty flat and we get hit hard by the OVO peak rate.
• The Red bars are based on historic actuals as-is at the then Octopus Agile price.  The Yellow ones are where I had a play with moving some of the time-shiftable load (CH, DHW, cold-fill washing) to cheaper time slots.  This did seem to make a small but noticeable improvement, but not earth-shattering.  My time shifting algo was just a first cut, and more work is needed here.

So all in all, it looks like we'll be shifting to the Octopus Agile tariff in the expectation of modest savings.

 

Edited October 23, 2023 by TerryE
Replace chart with updated version

[Alan Ambrose]

Hi Terry, that's very interesting. Can you give approx %s - looks like 20& reduction for OVO->Agile move and a few % for a bit of load shifting?

 

Would it be fairly easy to do an approx 'daily time shift using battery' calc? By eyeballing it looks like a rough shift from 35p electricity to 15p?

[TerryE]

@Alan Ambrose Yes, if you ignore December, then the saving for 2023 to date would be 21% just by aggregated tariff reduction, increasing to 26% with a bit of time-shifted load optimisation. In the summer, I don't have that much shiftable load, so any savings are largely because the OVO tariff ignores the daytime excess of demand vs supply.  The bills are higher in the winter, but the scope for optimisation is greater.  

 

We don't have rooftop PV because the planners were against for our site location, so if we had a battery then it would be battery only.  At the moment there isn't a case because Home battery pack prices are still in the £500-1K range per kWh, despite LFP bare pack prices having now dropped to £100 / kWh, so there is still a load of headroom for prices to fall.  Secondly, a side-effect of increasing renewable supply is that there is going to be increasing time-of-day price variability in the coming years.

 

So my take for us is: not yet for home battery, but possibly within the next few years.

[MikeSharp01]

Very interesting Terry - I was thinking of switching over to Octopus agile. here at millstone manor, now our move from bulb is finally complete and then load shifting but I think that while we still have a cooked supper it might be worth waiting until we fully retire and have a cooked lunch instead!

[SteamyTea]

Have you done a comparison with a standard rate?

 

[TerryE]

@SteamyTea. No just my current OVO Simpler Energy - Economy 7 plan to the Octopus Agile Flex one. 

[Alan Ambrose]

Is this a reasonable wholesale comparison with Terry's kWh price above i.e. about 10p per kWh? Don't shoot, I know the electricity market is complicated...

 

   

[TerryE]

Yes, the market is complicated.  There are different rates, e.g. next day, and various N month rates where the distributor is offering fixed price contracts, say 1 year term, and it wants to lay off this risk onto the generators.   There is also a half-hourly price, which is fixed 1 day ahead at Midnight UTC+1 (CET).   Individual distributors and generators can also enter into supply contract which are priced by negotiation (e.g. if OVO wants to buy types of green electricity, though these negotiations are heavily influenced by the prevailing spot rate.

 

What this does show is that the Octopus Agile prices track the spot rate far more than OVO does.  My E7 peak rate averaged over this year from OVO has been 35p / kWh when the average spot rate is less than a third of that. 

Edited October 26, 2023 by TerryE

[TerryE]

I've also loaded the following into github as Gists:

• octopus_agile_download.pl
• octopus_price_comparison.pl

 

[TerryE]

Well I've just switched to Octopus Agile.  It proved relatively painless and far quicker than I expected:

• 4th Nov. I had to request an E7 tariff quote and accept this to get the process rolling.
• 5th Nov.  Both OVO and Octopus confirmed that the switch was under way.  I had to do an old fashion eyeball meter reading
• 8th Nov.  First day of Octopus supply.
• 9th Nov.  Octopus API access to my Secure SMETS1 meter confirmed, and half-hourly data being captured
• 9th Nov.  Requested cut-over to Agile tariff.  Confirmation of the switch by email return.  My account details confirm that I am on the Agile tariff.

So now I've got to make the changes to my heating algo to make use of the new ToD tariff, but this merits its own blog post.

Edited November 9, 2023 by TerryE