No announcement yet.

Pull Hourly Data from Octopus Energy into 24 HS Devices

  • Filter
  • Time
  • Show
Clear All
new posts

    Pull Hourly Data from Octopus Energy into 24 HS Devices

    Click image for larger version

Name:	Capture.PNG
Views:	472
Size:	221.3 KB
ID:	1372064

    The below script can be run periodically from an event to create and update HS devices based upon the reporting provided from this energy provider. It is setup to pull the cost including VAT, but this could be changed by editing the 4th line to be "exc" rather than "inc". Whoever uses this provider can change the URL for their specific account. This account is based upon information in another thread.

    The data is reported in 30 minute intervals so each of the two is added to form the hourly total. The parent device contains the data of the last item reported from the site. If a different day such as earlier in the 24 hour period then the sDay logic can be changed in the script.

        Sub Main(ByVal Parms As Object)
            'Get the data
            Dim sDownload As String = hs.GetURL("", "/v1/products/AGILE-18-02-21/electricity-tariffs/E-1R-AGILE-18-02-21-N/standard-unit-rates/?period_from=", True, 80)
            Dim arrS() As String = Split(sDownload, "value_inc_vat"":")
            Dim sDay As String = ""
            'accumulate the 30 minute values
            Dim nHourly(23) As Double
            For i As Integer = 0 To 23
                nHourly(i) = 0.0
            For i As Integer = 1 To arrS.Length - 1
                Dim arrItem() As String = arrS(i).Split(",")
                Dim sUse As String = arrItem(0)
                Dim sStartTime As String = arrItem(2)
                Dim arrDate() As String = sStartTime.Split("T")
                Dim arrDay() As String = arrDate(0).Split("""")
                sDay = arrDay(arrDay.Length - 1)
                Dim arrHour() As String = arrDate(1).Split(":")
                Dim index = CType(arrHour(0), Integer)
                nHourly(index) = nHourly(index) + CType(sUse, Double)
            'store values for each hour, create HS device is not existing
            Dim sDeviceLocation As String = "Energy"
            Dim sDateName As String = "Last Update Date"
            Dim iRef As Integer
            Dim iParentRef As Integer
            iParentRef = hs.GetDeviceRefByName(sDeviceLocation & " " & sDateName)
            If iParentRef = -1 Then
                iParentRef = hs.NewDeviceRef(sDateName)
                Dim dv As Scheduler.Classes.DeviceClass = hs.GetDeviceByRef(iParentRef)
                dv.Location(hs) = sDeviceLocation
                dv.Relationship(hs) = Enums.eRelationship.Parent_Root
            End If
            hs.SetDeviceString(iParentRef, sDay, True)
            For i As Integer = 0 To 23
                Dim sDeviceName As String = "Hour " & i.ToString() & " Use"
                iRef = hs.GetDeviceRefByName(sDeviceLocation & " " & sDeviceName)
                If iRef = -1 Then
                    iRef = hs.NewDeviceRef(sDeviceName)
                    Dim dv As Scheduler.Classes.DeviceClass = hs.GetDeviceByRef(iRef)
                    dv.Location(hs) = sDeviceLocation
                    dv.Relationship(hs) = Enums.eRelationship.Child
                    dv.AssociatedDevice_Add(hs, iParentRef)
                    Dim dvParent As Scheduler.Classes.DeviceClass = hs.GetDeviceByRef(iParentRef)
                    dvParent.AssociatedDevice_Add(hs, iRef)
                    Dim Pair As New HomeSeerAPI.VSPair(HomeSeerAPI.ePairStatusControl.Status)
                    Pair.PairType = HomeSeerAPI.VSVGPairType.Range
                    Pair.RangeStart = &H80000000
                    Pair.RangeEnd = &H7FFFFFFF
                    Pair.RangeStatusDecimals = 2
                    Pair.IncludeValues = True
                    Pair.ValueOffset = 0
                    Pair.RangeStatusPrefix = ""
                    Pair.RangeStatusSuffix = ""
                    Pair.ControlUse = HomeSeerAPI.ePairControlUse.Not_Specified
                    hs.DeviceVSP_AddPair(iRef, Pair)
                    Dim vgPair As New HomeSeerAPI.VSVGPairs.VGPair()
                    vgPair.PairType = HomeSeerAPI.VSVGPairs.VSVGPairType.Range
                    vgPair.RangeStart = &H80000000
                    vgPair.RangeEnd = &H7FFFFFFF
                    vgPair.Graphic = "/images/HomeSeer/status/electricity.gif"
                    hs.DeviceVGP_AddPair(iRef, vgPair)
                End If
                hs.SetDeviceValueByRef(iRef, Math.Round(nHourly(i), 2), True)
        End Sub

    Originally posted by Michael McSharry View Post
    Click image for larger version  Name:	Capture.PNG Views:	10 Size:	221.3 KB ID:	1372064

    The data is reported in 30 minute intervals so each of the two is added to form the hourly total. The parent device contains the data of the last item reported from the site. If a different day such as earlier in the 24 hour period then the sDay logic can be changed in the script.

    This API returns the future cost (tomorrow's rates) of electricity per kWh for each 30 minute segment of the 24 hour period. It does not report actual usage from any historical period, so adding two 30 minute segments together defeats the intended purpose.

    It's purpose is to allow the smart homeowner to plan intensive energy usage (such as laundry, cooking, hot water usage, etc) outside of demand peak times when energy cost is the highest. This sort of transparent segmented pricing is becoming increasingly popular in the UK. Hopefully it will eventually be adopted nationwide here in the USA. But doing so would first require switching every household to a smart meter capable of reporting usage in 30 minute segments. However the potential return on investment is enormous and could lower energy usage nationwide by a significant amount.



      Based upon this understanding it seems there may be more useful measures than just listing the hourly (or 30 minute) rates in HS devices. Looking at 48 devices to find the best time should be done by the script. Perhaps create four devices and show the rate & time of the lowest cost. Perhaps trigger event when the best 30-minute period changes. I will wait for someone who plans on using the data to see what is the most useful information to be available via HS.


        I just found this thread- I’m working on this just now- thanks!

        I also have a Zigbee display+ CAD for UK Smart Meter that does get both real time and historical actual usage, details here;-

        One really useful example I’ve seen implemented in an IOS app (Octopus Watch), is given how long something needs to run, what the best time to start it is.
        Octopus have a suggested call in their API docs:-

        Print cheapest unit rate on a given day:
        $ http $TARIFF_URL/standard-unit-rates/ \ period_from=="2018-05-16T00:00" period_to=="2018-05-17T00:00" | \ jq '.results| min_by('.value_inc_vat')' {

        I have been talking to the display manufacturer and they indicate MQTT support as well as API, May be another option.


          I noticed today your other thread. I did this one during commercials as I was watching TV when I saw a board member was struggling.

          If you need me to do anything to help let me know. Either with the script or with MQTT/API.


            Thanks! I've spent days starting to get to grips with Postman, MQTT, API's- and now scripts!

            There are three main uses I can see for the rates:-
            1) Switch off devices (ie pond pumps) if half hour rate is over a specific amount- possibly with maximum duration a device can be off. Think I could do that if your script was reformatted for half hourly.

            2) Store the half hourly rates so that they can be compared against standard rates- the latter being "x" $ per kWh between certain times, and "y" $ per kWh at other times. Actual consumption would come from the electricity meter API or another whole house meter device.
            @ecuboss and @jon00 seem to have this working in post 8 of the other thread
            The scripts don't work when I try to glue them together (I get expected ")" at line 1 which doesn't bode well)

            3) Switch on devices when rate is below a certain amount. This is the most complex- as:-
            a) Some devices (ie a washing machine) would require power on for a contiguous period of x half hourly slots. It might also be better for battery storage to use contiguous grid power to reduce battery cycling.
            b) Some devices- ie car- could be charged from the "x" lowest priced slots which don't need to be contiguous. I can get the required kWh/ number of half hour slots from Tesla device % charge state times battery capacity divided by charge capacity. Some way to link times when the car's due to be there is also needed.

            Any pointers appreciated...


              My thoughts...

              In you use cases you have a need for

              1. A device that is active when hourly rates are above a specific threshold. I suspect this would not be a specific level, bot represented as a percentage of the average 1/2 hour rates. If absolute you would need to continually update the threshold otherwise as rates increase the pumps would never operate. The objective is to run pumps at minimum hourly rates. Since pump effectiveness will dependent upon total run time per day then it is not just the lowest, but when then it is any of the lowest n, where n is the number of 30 periods of periods per day when it needs to run. This means the device would be true when the rate is within the specified number of slots to satisfy a run time per day.

              2. I did not look at the other script, but my general thinking is that devices should be created for your trigger needs. While a graph of hourly rates is sexy, what is really needed is the information to take automation actions, if you have 5 things you need to automate then you should have 5 devices and these devices populated based upon the data download. It may be possible to reduce device count and use thresholds but this may be harder to maintain. A combination may be best.

              3. along the same reasoning. Created a device and supporting script logic that determines when a washing machine should run. For the car it seems like the case 1 I described. The bottom line is to make devices that contain the trigger logic and make the event triggers very simple.


                I've spent days trying to get the Octopus script(s) working- problem seems to be the Windows 7/ IE version on HomeTroller. The script runs, creates devices but they're always zero value.
                url works fine if I try from Chrome on machine, but it from IE it initially wouldn't download at all until I enabled TLS1.2 in registry. Now that is working but it just tries to download/ save the file if I try it from Internet Explorer (fine from Chrome)

                I suspect as it won't work from IE, GetURL will also struggle? Any suggestions?


                  TLS was the breaking technology in XP that forced me to move on from it. I still have XP and embedded XP, but they are not on the WAN.

                  A microcontroller such as Sonoff Basic, NodeMCU, or Wemos D1 mini does not have this constraint. I can put the download logic in one based on Tasmota if you are interested. It means you would need to install the custom firmware in the device. These are $5 devices.

                  Running on other more modern Linux ow Windows processors is also a option.

                  MQTT provides the easiest way to integrate external sources with HS. McsMQTT is one free HS plugin. There are other free ones and paid one too.

                  Let me know if you are interested in 1) firmware binary you can install or 2) a Wemos D1 mini with the firmware installed. Price including shipping should be $25 to UK for #2. Installing binary is pretty easy with many tutorials available for #1.


                    Thanks- but I probably need to get to grips with scripts so I can deal with the data and logic. Just about reached second grade! Finding my way around Visual Studio and Tenscripting...

                    Have now moved HS3 onto the Win10 machine I'd built for HS4, the Octopus url works fine when entered in IE or Chrome on this machine- but still don'd get values updating when I run your script.

                    If I try GetURLIE with the Octopus URL, the HS machine asks to open/ download the JSON file when the script is run- so at least I know the url sort of works from a script.

                    To help break it down, should this simplified version work? :-

                    Public Sub Main (ByVal Parms As Object)
                    ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12

                    Dim sDownload As Object = hs.GetURL("", "/v1/products/AGILE-18-02-21/electricity-tariffs/E-1R-AGILE-18-02-21-N/standard-unit-rates/?period_from=", True, 80)
                    End Sub

                    Trying that in Tenscripting I get "The underlying connection was closed: An unexpected error occurred on a send"


                      I did not need to specify TLS12 when running HS on W10. It could be a lower level of TLS that is actually being used by Octopus Energy. You should be able to use the same script I posted and strip everything after the download that interprets the received data.

                      The message returned by Octopus does seem to indicate that it is not happy with what is being requested. I use wireshark for lower level debug. If you want to go that direction I can collect wireshark data that you can use for comparison with what you collect.


                        Thanks for the pointer. Running the script in Wireshark I see TLSv1 Client hello then TCP reset from server then one retry.

                        Running the same URL from browser I get TLSv1.2 Client hello, then TLSv1.2 Server Hello, then data packets with values.

                        I've now tried on two Win10 machines running dotnet 4.8 by just copying/ pasting the text into a script- both fail.
                        One machine in particular is very vanilla- was only built for HS with little else on it. Both are up to date with updates.

                        I also tried running script in TenScripting, changed target framework to dotnet 4.8 there- same.

                        Wondering if some of the other PI or references in your system.ini, etc are meaning you use TLSv1.2?

                        I meant to ask if you take donations? Both for MQTT and your patience!


                          If you are doing https this implies that there is an exchange of certificate information. The browser handles this negotiation. I do not know where hs.getURL gets its SHA1 fingerprint or certificate info, but I suspect it uses the same core libraries that the browser uses. This would imply IE or Edge on Windows. When I run https on the ESP8266 to collect public data, such as the Octopus data, I just tell it to ignore the fingerprint. On these libraries it is the setInsecure method. I do not know what the equivalent is in .NET. When using the SHA1 fingerprint that is pulled from the browser Security/View Certificates the https client call is the setFingerprint method. Again I do not know the .NET approach to this.

                          No donations. I enjoy helping and learning.


                            Script isn’t even getting to the certificate exchange, it’s failing at TLS negotiation. It needs to get a server hello before certs are involved:-

                            The TLS handshake

                            Let’s see the steps of the TLS negotiation, how the secure channel is being established between the client and the server before any HTTPS traffic can happen.
                            1. TCP handshake is performed between client and server, then...
                            2. Client Hello: The client sends to server the versions of TLS protocol supported, as well as the suite of ciphers (encryption/decryption algorithms it supports for key exchange, data encryption and handshake security);
                            3. Server Hello: After the server compares what the client sends with its own supported TLS versions and ciphers, the server decides which ones will be used and sends the selection to the client.
                            4. Server certificate: The server has to authenticate itself to the client, so it sends its certificate. The keys will be used for encryption.
                            5. Client switches to encrypted; all following messages from client will be encrypted from this point.
                            6. Server switches to encrypted, too. From this point, normal HTTP requests and response can travel via the encrypted channel.
                            From the above it seems that I’m still not requesting tls12, although I seem a comment that as hs3 is built on dotnet4 (and not later 4.x) it may not support tls12. This might be why others are using webrequest, etc rather than hs.geturl

                            dotnet 4.8 should use system default for crypto, schannel is the mechanism- but Microsoft has been messing about with registry keys and that could also be part of the issue.

                            I’m going to give it another day or two to find a breakthrough, it I can’t I’ll probably try Node Red with REST- others have that working. I have a RazPi waiting...
                            Node red/ MQTT also interests me for Modbus, I have problems with the current HS Modbus PI dropping out connections on a regular basis- not good as my car charger has a watchdog which reduces charge current if the comms fail!


                              Finally got API working, call is now TLS1.2
                              It wasn't too obvious what was wrong, systemdefaultsecurityprotocol was correct at "systemdefault" but there's a lot of registry changes over recent dotnet and windows updates.
                              Ended up going with this fix:-

                              Fix confirmed on dev and live Win 10 HS machines.
                              It's taken a week to get this far, all I need to do is figure out is how to parse the data how I want- and what to do with it!

                              Seen the potential for the data at the weekend as electricity prices fell below zero for a couple of hours during the night and also in the afternoon. So they actually paid me to charge my car and use electric heating instead of gas! But it's not as easy as just selecting specific rates, or specific times. Most of the time I only charge the car to 60%- but if I get paid for it I charge to 90%!
                              the same applies for battery storage- but it doesn't price in yet...