Octopus Agile - display tariff in graphs & tables, best import/export periods - all done using Node-RED and JSONata

[Biscuit (Geoff)]

Good to hear that you fixed it - that saved me trying to debug the issue remotely!

The HA websocket nodes are a powerful addition to Node-RED and make full two-way communication between HA and NR possible. They are documented at https://zachowj.github.io/node-red-contrib-home-assistant-websocket/guide/

At first they do take a bit of time and effort to get to understand how it all fits together, but it is worth the effort.

The ‘sensor node’ works with an ‘ha entity config node’. The sensor node is, in effect, just the front-end and visible node that sits in the flow. The work is done using the config node, and these can be seen if you use the ‘configuration node’ tab in the Node-RED editor right hand debug window. The config node holds the information required to create and maintain the sensor entity in HA, and this node in turn uses the HA server configuration node (which sets up and managed the web socket connection between NR and HA).

I can only guess that your HA server node was OK, as you had the other ‘agile sequence table’ working, and that your other sensor node and its related configuration node were ok and happily working. When this all works it works brilliantly, however when something goes wrong it is quite sensible to delete the configuration node, recreate and set it up again. This should force the new configuration node to correctly reconnect to the HA server configuration node, to correctly re-register the entity in HA, to connect to the flow sensor node, and to take the payload / attribute data from the sensor node and pass it to the entity in HA.

Sometimes just disabling the sensor & config nodes, redeploying, and then reenabling can fix things. Sometimes a full restart of NR is required (between disabling and renabling), and in the extreme I have had to delete the sensor and config nodes and restart both HA and Node-RED to force HA to ‘forget’ the entities, and then replace everything.

[PedroKTFC]

Thanks for this. I confess to still not fully understanding how the sensor and config nodes fit together. The documentation explains (a little!) how to configure them but is limited on their structure (and I’ve worked in IT all my life so the concepts aren’t alien to me). One thing that’s “interesting” is that the sensors don’t persist across HA reboots - they get created by Node Red and only exist while it’s running. This may affect how I develop what I’m thinking of doing (this is an evolving idea), let me explain. I wonder if Helpers would help with persistence and be another approach?!

I noticed that the Octopus agile tariffs can be -ve (ie you’re paid for using electricity). We don’t use electricity very much but I was wondering whether I could exploit this a little through our emersion heater. However, I want to do some checks before changing tariff. So, through HA, I want to:

• collect my current (I’m on the Octopus flexible tariff at the moment) and the agile tariffs for an indefinite period and display these both graphically and in a table so I can compare the two over any period I’ve collected data for
• Using my consumption data, calculate the cost of the electricity I’ve used under both tariffs so I can see if the agile tariff would be better or worse.
• Implement an NR automation that will activate if the agile tariff in the current half hour is < 0.
• Maybe have an export facility as some analysis might be better using Excel.

So far, I’ve just started getting the data from Octopus (I’ve used the Octopus Energy integration and your NR template). I now need to start thinking where I’ll store the data (I’ve not done anything in HA like this so will be Googling lots!). It’ll take me some time as I haven’t a lot of time I can dedicate to this but I’ll report back on my progress.

[Biscuit (Geoff)]

In looking recently at another Flex Table card I have I realised that this can be done so very easily I can’t think why I did not think of this before.

The Flex Table card allows for JavaScript eval() action on the data values, and thus

modify: (new Date(x.from)).toLocaleString("en-GB", {day:"2-digit", month:"short", hour:"2-digit", minute:"2-digit", timeZoneName:"short"})

does the trick. The ‘locale’ JS functions use the machine local settings, with location ‘en-GB’ it should show the current UK time, adjusting for DST.

The data fields required are array.from and array.upto, which are already both UTC ISO, and the optional format object picks out just the date/time parts for display.

Full card is now:

type: custom:flex-table-card
title: Octopus Agile Period
entities:
  include: sensor.octopus_agile_sequence_table
columns:
  - data: both_array
    modify: x.mode
    name: Mode
  - data: both_array
    modify: >-
      (new Date(x.from)).toLocaleString("en-GB", {day:"2-digit", month:"short",
      hour:"2-digit", minute:"2-digit", timeZoneName:"short"})
    name: Start
  - data: both_array
    modify: >-
      (new Date(x.upto)).toLocaleString("en-GB", {day:"2-digit", month:"short",
      hour:"2-digit", minute:"2-digit", timeZoneName:"short"})
    name: Stop
  - data: both_array
    modify: x.duration
    name: Mins
  - data: both_array
    modify: x.average
    name: Price (p)

and the result is

[PedroKTFC]

As promised, a report on how I’ve got on with comparing my existing costs with what they would be if I were on the agile tariff. It was certainly quite complex, more in working out how to do it than doing it. I felt it was worth a detailed explanation so I’ve published it on my substack.

I’ve used InfluxDB and Grafana in my approach and insert data into InfluxDB directly (this was the hardest part - nowhere on Google could I find anything on how to insert into InfluxDB).

Any feedback welcome.

[WaylanderUK]

@PedroKTFC - im having the same issue as you with the agile rates not being retrieved yet the sequence table works fine. Can you elaborate on your fix (or better still post your node red json?). I did look for the @config sections you mention but there seem to be multiple…

[PedroKTFC]

My fix wasn’t really a fix, it was a workaround. I deleted Geoff’s original entity config nodes. You’ll see mine listed below:

Click on the cog wheel to see them (highlighted in yellow). You can check this has worked properly by going into the Developer Tools and select the STATES tab (at the top). You can then use the Filter entities field to search for Geoff’s original entities by name (might be a good idea to do a search before and after you delete the entities to see they go).

Having deleted the nodes, Node Red should now show the Octopus Agile Sequences and Prices nodes as in error. You now need to create a config entity node. Make sure you use a different name to that originally used (I think somewhere some old config information is kept that prevents the original names being updated correctly). Check your new named sensors are present in HA using the Developer Tools.

You might want to force injection so that the sensors get populated.

Hope that’s clear and what you need. Let me know if not and we can try and work out what else is happening.

[PedroKTFC]

I should have said once you click on the cog wheel, double click on the config node which displays the config node. At the top are three buttons including a Delete button. Use that to delete Geoff’s original nodes.

[WaylanderUK]

Thanks for your help Pedro - I’ll give this a try. I did have some luck modifying the original sequence sensor to ALSO carry the rates property… thought then I don’t really know what to do with it in any of the cards i.e. how to show it.

[PedroKTFC]

Good luck with it. I do recommend InfluxDB and Grafana for dealing with storing and displaying this type of data. It should store and display Geoff’s data more or less automatically.

[Biscuit (Geoff)]

The full Agile Tariff array (96 records ie two days) is already passed to the ‘Octopus Agile Prices’ sensor entity in the attributes as ‘array’, so you really don’t need to add it to the sequence entity.

There is a limit to the size of data that can be passed in attributes (as I found out the hard way) and there is a general view that all data should be passed to HA using only state values and not attributes. However…

HA entity states can only take simple variables, so objects and arrays have to go via attributes. This therefore requires a bit more work to get at the data in HA, however displaying the full agile price table is relatively straightforward.

I use the custom flex-table-card, which builds basic tables very easily. For attribute data it requires a bit of code to modify the data. Above is a simple test card that uses the octopus-agile-prices entity, picks up the data from the ‘array’ attribute, and then uses a bit of simple code (x.‘object key’) to pull the variables from the object. Since it shows the data ‘as is’ the time is the UTC timestamp, and the full 96 records are shown, but this should give you the basics of how to build a table using entity attributes.

https://github.com/custom-cards/flex-table-card

If you don’t fancy installing and using a custom card, then the included markdown card will also work, with a bit of code.

Time            Price
{%- set results = state_attr('sensor.octopus_agile_prices', 'array') %}
{%- for record in results %}
{%- set ts = as_timestamp(record.from) %}
{%- set ts_now = ((as_timestamp(now())/1800)|round(0,'floor')|int * 1800) %}
{%- if ts >= ts_now %}
{{ ts|timestamp_custom ('%H:%M', local=true)
}}           {{
record.export }}
{%- endif %}
{%- endfor %}

Here the ‘code’ is the included template based on Jinja2. You can find the details of HA templating at
https://www.home-assistant.io/docs/configuration/templating/
and test it using the developer > template sandbox in HA.
The ‘code’ here does a bit of work to pull the entity attribute, get the timestamp, and test against the current time so as to only display current and future records. The use of ‘local=true’ in the timestamp rendering filter ‘timestamp_custom’ does the dirty work of dealing with DST on the fly.

Big tables of data are naturally more difficult to build, display sensibly, and to use, so it may well be more appropriate to just stick with the ‘current’ and ‘next’ values. These are available in the sensor as attributes, so can be displayed using a basic entity card. Personally I like graphs, as they show a great deal of data (and relationships) visually in a compact format.

[Biscuit (Geoff)]

Observation.

OK, so Octopus Energy API is for the most part very stable and reliable.

However, in looking today I have an error in my sensor node. Digging further and looking at the return from the API call itself (which calls for the latest 96 records…) I get

The API call appears to be returning duplicate records. Normal return is for the latest record and working backwards, hence asking for 96 records this afternoon returns from tomorrow end of period (23:00 UTC) back to yesterday. Here I can see the exact same record twice (on multiple occasions), which means that the records I am expecting for now (today) are just not there.

Not an error that can easily be recovered from, but this may explain the (recent?) issues with not seeing the Agile Prices sensor return. My usual option is to wait until tomorrow, but clearly getting unexpected duplicates on a direct API call is not easy to deal with.

If anyone is interested, out of the 96 records called for there are 52 distinct ‘valid_from’ times, so this is not just repeating every single record, just most of them.

[Shaggy91 (Hugh Adrian Hall)]

Thanks Geoff, I was just trying to diagnose this, removed Node red, re installed, deleted code, re installed code, tried older versions of node red, still with the same result - should have checked here first…

Hopefully after the next update it will return back to normal as i like your gubbins here - it works turning stuff on and off and i can visually see when it decides are the best times…

Regards,

Shaggy

[Biscuit (Geoff)]

Only spotted this by chance, and I have not seen it before. Although it looks like an Octopus API issue it may be something to do with the Node-RED http request node. The https://agileprices.co.uk/?tariff=AGILE-22-08-31&region=L website is showing the correct figures for today (2nd) but has missing figures for yesterday (1st). My bet is on the Octopus records. Debugging stuff takes a lot of time and effort…

In the mean time, a simple patch fix can be applied to the ‘Results’ node just after the API call.
The JSONata function $distinct() returns an array removing duplicates. At the moment this for me reduces the 96 records down to 52, so it works fine, except between 16:00 and 23:00 immediately after the update when the tail end of the 96 (being the remains of today) is missing, as it was yesterday up to around 20:00.

PATCH:

In the ‘Results’ change node, set the ‘to the value’ dropdown to ‘J:’ JSONata, and add the expression

$distinct(payload.results)

This should do the trick if the problem repeats, although I suspect at 16:00 the issue will have gone away.

I have made several changes to this code (adding in BST local time and a binary ‘switch’ sensor) but every time I think it is good to release something else crops up, so I may have another think - one thing would be to pull down 144 records, strip out duplicates (flag an error if the 144 shrinks in size) then pick out the top 96 (and check the start-end dates are correct). As usual the error avoidance and checking code is considerably bigger than the code required to do the work.

[Shaggy91 (Hugh Adrian Hall)]

Okay, I’ve screwed it up!

After some issues from me mucking about with it i uninstalled everything Node-red (including what directories i could find, reinstalled, re configured, working okay so far, imported the code in, checked its connected to the webserver, failed when deploying:–

2 Aug 19:58:33 - [info] Node-RED version: v3.0.2
2 Aug 19:58:33 - [info] Node.js  version: v18.17.0
2 Aug 19:58:33 - [info] Linux 6.1.11-meson64 arm64 LE
2 Aug 19:58:36 - [info] Loading palette nodes
2 Aug 19:58:46 - [info] Dashboard version 3.5.0 started at /endpoint/ui
2 Aug 19:58:49 - [info] Settings file  : /etc/node-red/config.js
2 Aug 19:58:49 - [info] Context store  : 'default' [module=memory]
2 Aug 19:58:49 - [info] User directory : /config/node-red/
2 Aug 19:58:49 - [warn] Projects disabled : editorTheme.projects.enabled=false
2 Aug 19:58:49 - [info] Flows file     : /config/node-red/flows.json
2 Aug 19:58:49 - [warn] Encrypted credentials not found
2 Aug 19:58:49 - [info] Server now running at http://127.0.0.1:46836/
2 Aug 19:58:49 - [info] Starting flows
[19:58:49] INFO: Starting NGinx...
2 Aug 19:58:49 - [info] Started flows
2 Aug 19:58:54 - [info] [server:Home Assistant] Connecting to http://supervisor/core
2 Aug 19:58:54 - [info] [server:Home Assistant] Connected to http://supervisor/core
2 Aug 20:01:52 - [info] Stopping flows
2 Aug 20:01:52 - [info] [server:Home Assistant] Closing connection to http://supervisor/core
2 Aug 20:01:52 - [info] Stopped flows
2 Aug 20:01:52 - [info] Updated flows
2 Aug 20:01:52 - [info] Starting flows
2 Aug 20:01:52 - [info] Started flows
2 Aug 20:01:52 - [info] [server:Home Assistant] Connecting to http://supervisor/core
2 Aug 20:01:52 - [info] [server:Home Assistant] Connected to http://supervisor/core
2 Aug 20:01:58 - [error] [ha-sensor:Octopus Agile Prices] InputError: Integration not loaded
2 Aug 20:01:58 - [error] [ha-sensor:Octopus Agile Sequences] InputError: Integration not loaded
2 Aug 20:02:05 - [error] [ha-sensor:Octopus Agile Prices] InputError: Integration not loaded
2 Aug 20:02:05 - [error] [ha-sensor:Octopus Agile Sequences] InputError: Integration not loaded
2 Aug 20:02:09 - [error] [ha-sensor:Octopus Agile Sequences] InputError: Integration not loaded
2 Aug 20:02:22 - [error] [ha-sensor:Octopus Agile Prices] InputError: Integration not loaded

What am i missing?

Regards

Shaggy

[Shaggy91 (Hugh Adrian Hall)]

Found in homeassistant logs…

Logger: homeassistant.setup
Source: setup.py:193
First occurred: 20:19:09 (1 occurrences)
Last logged: 20:19:09

Setup failed for nodered: Integration not found.

[Biscuit (Geoff)]

Not an issue I have seen, but at a guess the companion integration is missing, or needs updating.

https://github.com/zachowj/hass-node-red#installation

To make all this work, as well as Node-RED (the HA addon) which comes with the pallet containing the HA nodes (node-red-contrib-home-assistant-websocket), the ‘custom integration’ is the bit that does the work to make the HA server and websocket/API operate.

Suggest…
Check for the Node Red Companion in the HA integration page.
Check your config/custom_components/nodered folder, and if necessary remove everything and reinstall the config (link above).

When the Node-RED addon is updated the pallet updates, including the bundled HA websocket nodes. The version of the custom integration that these require also updates. The latest is 2.1.1. I did have issues with updating a version some time ago, the problem being that I kept old copies of the files renamed ‘old*’ in the same folder but the python engine was finding and reading the old file.

Hope this helps

[Shaggy91 (Hugh Adrian Hall)]

Cheers Geoff, I got the integration added back in and the error has gone away, no errors whatsoever but the entity agile_prices remains empty even though i have updated with my API http code ?page_size=96. The sequence connectd and works with no issue, just no prices. The Agile_sequence has worked giving me times when the best rates are…

I guess i need to wait til update tomorrow and see if it all loads up…

Regards

Shaggy

[Shaggy91 (Hugh Adrian Hall)]

Hi,

Basically i have jumped in on this after looking at it - HA now switches my boiler off for hot water if the agile prices will be at least 1p kwh cheaper than gas (which i am on the gas tracker), and then will switch on the immersion when the electric price gets to at least 1 p/kw cheaper than gas. As for price comparisons i have Octopus compare on my phone that tracks my usage daily/weekly/monthly and shows me stats on usage compared to another tariff i have chosen, not sure if this is any help to you…
When prices go negative thats when i go nuts…I have battery storage that gets charged up but i will hold the charging back until i can take advantage of max -tive prices. the immersion goes on as well, air conditioners, oven, grill, 3 old bitcoin miners that had been retired and a dehumidifier…so far the best i have done is they paid me £1.85…

Regards

Shaggy

PS there is also an historical website for agile prices along with one for when the prices go negative, and a gas/electric price tracker for the tracker tariffs going back to december 2022…

[PedroKTFC]

That’s very interesting. So you’re using the agile tariff! When did you go onto it and what from? I presume it’s saved you money? I wonder what would have happened to you last year when the prices sky rocketed? Would you have been “toasted”?! :o)

[Shaggy91 (Hugh Adrian Hall)]

I was originally on econ 7 so without the battery i was getting stung at £300 a month with electricity. Moving on with the battery reduced that to £130 to £170 and with agile currently that is down to £76. What would it have been with agile pricing last year? Alot more than econ 7! Prices were high all the time according to https://agileprices.co.uk/ so yes i would have been toasted…but that being said i am assured that i can jump off the agile tariff, without notice or penalty, onto another tariff but cannot rejoin agile for 30 days - so if prices rocket again i shall be looking at other tariffs but at this time agile fits the bill.
Gas Tracker allows me to cut my gas price in half. Looking at the history for gas and electricity, last december the electric price peaked at 60p kw but averaged 30p kw through December then dropped to 20p/kw January onwards. I am on the gas tracker so that did peak above standard prices but has been below standard for months now. If that goes nuts i can drop off onto a standard tariff but it will take two weeks to do so and i cannot go back on it for 9 months, so really that is the one i need to monitor and calculate using past prices…

Best regards

Shaggy