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

UPDATE v1.0.0 now on Github February 2024

The original Node-RED flow is still available but an update is now on Github for anyone who is interested. See post 97 at Octopus Agile - display tariff in graphs & tables, best import/export periods - all done using Node-RED and JSONata - #97 by Biscuit

Introduction

This project focuses on reading and processing UK Octopus Agile electricity tariff prices (both import and export) using Node-RED, with JSONata in change nodes as an alternative to JavaScript in function nodes. I have a working Node-RED flow, together with the necessary settings for graph and table card for HA display.

Disclaimers

The code has been tested (against JSONata v2.0.0) and is working for me at the current time.

Background

I am a fan of Node-RED for its no-code simplicity and speed of prototype / development. I have several flows (running 24/7 in Node-RED as an HA addon) used to monitor and measure solar PV, inverter, battery, and energy performance. I am with Octopus Energy for fixed price electricity, consumption as well as excess solar export, and I read and process my historic meter readings for daily, monthly, and yearly summaries. As a recent exercise, I am attempting to fully use JSONata in place of function nodes and JavaScript.

I have found JSONata quite challenging. JSONata is designed to query and process (select, map, transform, aggregate) JSON structures, and it does this incredibility well. Node-RED can become messy when attempting iteration using split/join nodes, so I use for-loops in JavaScript within a function node. JSONata is a revelation, as arrays and complex objects can be iterated over as part of a functional expression, and with little code required.

Octopus Agile

There are several integrations for Octopus (Agile) already. However, this is written in Node-RED, and therefore may be of use for anyone wanting to do something that they cannot achieve elsewhere. It is also offered as a practical example of how to use JSONata. I have found limited information in my searches for JSONata examples and tutorials, so I hope that this is of benefit to anyone wanting to explore and experiment with JSONata.

My aim in this project is to:

  • Read the Octopus import and export agile tariff prices, once per day
  • Display these prices on a graph in HA, covering both today and tomorrow
  • Provide sensors in HA with current and next prices, updated every half hour
  • Extract a set of ‘best-price slots’ for the (15) lowest-import / highest-export periods
  • Group these ‘best slots’ into sets of longer contiguous periods (sequences)

Get paid for using electricity to charge the battery, and then sell it back later the same day for 24 p per kWh more!

Octopus agile - best time periods

Octopus Agile API – reading the tariff

Octopus offer (authorised) API access to customer’s own meter readings. The tariff products and prices are available without restriction.

More about Octopus open API and the Agile API documentation: -

https://developer.octopus.energy/docs/api/#agile-octopus

The API call I use: -

https://api.octopus.energy/v1/products/AGILE-22-08-31/electricity-tariffs/E-1R-AGILE-22-08-31-L/standard-unit-rates/?page_size=96

The key elements in the API call above are the tariff name ‘AGILE-22-08-31’ which I believe is the most recent offering. Other (older) tariff products are available. The ‘L’ is for my region (South-West) here in the UK. The regions are explained here: -

https://www.energy-stats.uk/dno-region-codes-explained/

The ‘?page_size=96’ is an option I use to pull 96 records rather than the default 100. Octopus publish the entire price tariff history, and every day add the next 24 hours (48 records) worth on the front end. This update happens reliably around 16:00. The price day runs from the 23:00-23:30 period on the same day to 22:30-23:00 for tomorrow (11pm to 11pm due to being priced on CET time zone, 1 hour ahead of the UK on GMT equivalent to UTC).

Each API call retrieves an array of objects, most recent first, with each object being

{"value_exc_vat":25.07,
"value_inc_vat":26.3235,
"valid_from":"2022-12-19T22:30:00Z",
"valid_to":"2022-12-19T23:00:00Z"}

Time appears to be always given as UTC Zulu timezone, so at daylight saving changes the tariff records remain contiguous and the user has to adjust or stay on UTC.

Node-RED flow

This is the Node-RED flow I use to obtain, process, store, and uplift to HA the tariff array, current and next prices, best price periods, and concurrent sequences.

EDIT 25 January 2024:
The Node-RED flow below has been modified to remove the state class ‘measurement’ from the configuration nodes.

[{"id":"e42010beae8f7fa5","type":"group","z":"1cc7d2e94a4815fe","name":"Octopus Agile Prices","style":{"label":true,"stroke":"#6f2fa0","fill":"#dbcbe7","color":"#001f60"},"nodes":["db6ebccc4bb88b8d","63bba87f93e0eaae","65094b21c3938d73","22fdc0bfd7bdd329","3c0e6412766b7286","0ba1d9b378092267","416e22ecfcbf5616","ce579e86db6fcc9b","be04e63b7258b035","4430da6cfe290e39","3797b648d315c3a1","bc4c506918ed4d9e","b98e955f8996efa2","c63c0e9f64999a1e","a28359905d33b762","59f0bbc0e3c76b0d","aa94392fea62febc","87bddd29d5e5ab90","224dc5d34ebe8967","8a65a4744d5f228a","3c01c3e92c0898b2","03b0c5c88010997c","339092c8ec953466","d8bb3057287698de","a92b801c291d33d0","56dd710454ea2e25","ee45b426f60a08a0","a9942de6076269cf"],"x":94,"y":139,"w":982,"h":462},{"id":"db6ebccc4bb88b8d","type":"http request","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Export","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-OUTGOING-19-05-13/electricity-tariffs/E-1R-AGILE-OUTGOING-19-05-13-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":380,"y":260,"wires":[["65094b21c3938d73"]]},{"id":"63bba87f93e0eaae","type":"http request","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Import","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-22-08-31/electricity-tariffs/E-1R-AGILE-22-08-31-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":380,"y":220,"wires":[["22fdc0bfd7bdd329"]]},{"id":"65094b21c3938d73","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":260,"wires":[["3c0e6412766b7286"]]},{"id":"22fdc0bfd7bdd329","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":220,"wires":[["3c0e6412766b7286"]]},{"id":"3c0e6412766b7286","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Combine","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":700,"y":220,"wires":[["416e22ecfcbf5616"]]},{"id":"0ba1d9b378092267","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"@ 16:05","props":[{"p":"payload"}],"repeat":"","crontab":"05 16 * * *","once":true,"onceDelay":"2","topic":"","payload":"","payloadType":"date","x":200,"y":220,"wires":[["db6ebccc4bb88b8d","63bba87f93e0eaae"]]},{"id":"416e22ecfcbf5616","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Build & Save Tariff Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.import#$i.{\t   \"from\": valid_from,\t   \"upto\": valid_to,\t   \"date\": $substring(valid_from,0,10),\t   \"timefrom\": $substring(valid_from,11,5),\t   \"timeupto\": $substring(valid_to,11,5),\t   \"import\": value_inc_vat,\t   \"export\": %.export[$i].value_inc_vat\t}^(from)","tot":"jsonata"},{"t":"set","p":"OctAgileTariff","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":890,"y":220,"wires":[["56dd710454ea2e25"]]},{"id":"ce579e86db6fcc9b","type":"comment","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus agile prices (2 days) JSONata V2.0.0","info":"","x":290,"y":180,"wires":[]},{"id":"be04e63b7258b035","type":"ha-sensor","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Agile Prices","entityConfig":"893ae24be6da2123","version":0,"state":"agile.now.from","stateType":"msg","attributes":[{"property":"array","value":"payload","valueType":"msg"},{"property":"import","value":"agile.now.import","valueType":"msg"},{"property":"export","value":"agile.now.export","valueType":"msg"},{"property":"import_next","value":"agile.next.import","valueType":"msg"},{"property":"export_next","value":"agile.next.export","valueType":"msg"}],"inputOverride":"allow","outputProperties":[],"x":920,"y":560,"wires":[[]],"server":""},{"id":"4430da6cfe290e39","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Value Now","rules":[{"t":"set","p":"agile","pt":"msg","to":"(\t   /* function to add <delta> minutes to now */\t   $tstep:=function($mdelta) {\t       $fromMillis($toMillis($now())+$mdelta*60000)\t   };\t\t   /* function to build key value from timestamp                         */\t   /* strip fraction of seconds, floor minutes to 00 or 30, zero seconds */\t   $keyis:=function($ts) {\t       (\t           $t:=$replace($ts, /(\\.[0-9]+)/, '');\t           $m:=$substring($t,-6,2).$number()<30 ? '00': '30';\t           $substring($t,0,14) & $m & \":00\" & $substring($t,-1);\t           )\t   };\t\t   /* return object of 'now' and 'next' value objects */\t   {\t       \"now\": payload[from=$keyis($now())],\t       \"next\": payload[from=$keyis($tstep(30))]\t   };\t   )","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":560,"wires":[["be04e63b7258b035"]]},{"id":"3797b648d315c3a1","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":560,"wires":[["4430da6cfe290e39"]]},{"id":"bc4c506918ed4d9e","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Import Low","rules":[{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* look at latest prices, sorted by ascending import cost     */\t/* pick the lowest <sample size>, create new object for each  */\t/* with {date, from, upto, cost} Sort by date & time          */\t\tpayload[[48..95]]^(import)[[0..$$.sample-1]].{\t   \"from\": from,\t   \"upto\": upto,\t   \"date\": date,\t   \"timefrom\": timefrom,\t   \"timeupto\": timeupto,\t   \"value\": import\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":360,"wires":[["aa94392fea62febc"]]},{"id":"b98e955f8996efa2","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":210,"y":360,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"c63c0e9f64999a1e","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Manual","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":320,"wires":[["b98e955f8996efa2"]]},{"id":"a28359905d33b762","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Export High","rules":[{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* for export values - as for import but */\t/* sorted from highest to lowest, top <n> */\t\tpayload[[48..95]]^(>export)[[0..$$.sample-1]].{\t   \"from\": from,\t   \"upto\": upto,\t   \"date\": date,\t   \"timefrom\": timefrom,\t   \"timeupto\": timeupto,\t   \"value\": export\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":400,"wires":[["aa94392fea62febc"]]},{"id":"59f0bbc0e3c76b0d","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Groups","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* get start and end indexes, and zip into a sequence array of [start, end]                */\t/* map this array of sequences to an array of objects, one for each sequence to include    */\t/* sequence number, index range, date, start time, stop time, count of half-hours          */\t/* duration, and average (sum of values in sequence divided by count of items in sequence) */\t\t(\t   $chain:=$zip(payload[position=\"start\"].index, payload[position=\"end\"].index);\t   $map($chain, function($item, $index) {\t       (\t        $aa:=payload[$a:=$item[0]];\t        $bb:=payload[$b:=$item[1]];\t        $count:=$b-$a+1;\t        {\t        \"mode\": topic,\t        \"sample\": sample,\t        \"sequence\": $index+1,\t        \"range\": ($a+1) & \"-\" & ($b+1),\t        \"from\": $aa.from,\t        \"upto\":  $bb.upto,\t        \"date\": $aa.date,\t        \"timefrom\": $aa.timefrom,\t        \"timeupto\": $bb.timeupto,\t        \"periods\": $count,\t        \"duration\": $count*30,\t        \"average\": $round(($sum(payload[[$a..$b]].value)/$count)*1000)/1000\t    })\t   })^(from)[];\t)\t\t/* (>duration) gets longest periods first, however (from) sorts           */\t/* chronologically, which is required for correct display on tariff graph */\t/* final [] places result into an array even if only one sequence group   */\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":460,"wires":[["8a65a4744d5f228a"]]},{"id":"aa94392fea62febc","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Sequence V2","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* For Version 2 - variable change needed  */\t/* note index position, and test if items  */\t/* are consecutive - forwards and backwards */\t/* set position as only, start, mid, end */\t\tpayload#$v.(\t   $last:=$v<1 ? false : from=%.payload[$v-1].upto;\t   $next:=upto=%.payload[$v+1].from;\t   $position:=(\t       $last ? ($next ? \"middle\" : \"end\") : ($next ? \"start\" : \"only\")\t   );\t   {\t       \"from\": from,\t       \"upto\": upto,\t       \"date\": date,\t       \"timefrom\": timefrom,\t       \"timeupto\": timeupto,\t       \"value\": value,\t       \"index\": $v,\t       \"link_last\": $last,\t       \"link_next\": $next,\t       \"position\": $position\t}\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":360,"wires":[["59f0bbc0e3c76b0d","224dc5d34ebe8967"]]},{"id":"87bddd29d5e5ab90","type":"link in","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":245,"y":400,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"224dc5d34ebe8967","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":730,"y":360,"wires":[["3c01c3e92c0898b2"]]},{"id":"8a65a4744d5f228a","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":590,"y":460,"wires":[["a92b801c291d33d0"]]},{"id":"3c01c3e92c0898b2","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Save Best","rules":[{"t":"set","p":"OctAgileBest","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":360,"wires":[[]]},{"id":"03b0c5c88010997c","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Save Period","rules":[{"t":"set","p":"OctAgilePeriod","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":420,"wires":[[]]},{"id":"339092c8ec953466","type":"ha-sensor","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Agile Sequences","entityConfig":"6f8158bd34274dd3","version":0,"state":"$count(payload.export) + $count(payload.import)","stateType":"jsonata","attributes":[{"property":"export_array","value":"payload.export","valueType":"msg"},{"property":"import_array","value":"payload.import","valueType":"msg"},{"property":"both_array","value":"payload.both","valueType":"msg"},{"property":"bid_offer_spread","value":"$min(payload.import.average)-$max(payload.export.average)","valueType":"jsonata"}],"inputOverride":"allow","outputProperties":[],"x":910,"y":460,"wires":[[]],"server":""},{"id":"d8bb3057287698de","type":"link in","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":615,"y":540,"wires":[["4430da6cfe290e39"]]},{"id":"a92b801c291d33d0","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Table","rules":[{"t":"set","p":"payload.both","pt":"msg","to":"$append(payload.import, payload.export)\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":710,"y":460,"wires":[["03b0c5c88010997c","339092c8ec953466"]]},{"id":"56dd710454ea2e25","type":"link out","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily Update","mode":"link","links":["87bddd29d5e5ab90","d8bb3057287698de"],"x":1035,"y":220,"wires":[]},{"id":"ee45b426f60a08a0","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Half Hour","props":[{"p":"payload"}],"repeat":"","crontab":"*/30 0-23 * * *","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":560,"wires":[["a9942de6076269cf"]]},{"id":"a9942de6076269cf","type":"delay","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"10s","pauseType":"delay","timeout":"10","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":350,"y":560,"wires":[["3797b648d315c3a1"]]},{"id":"893ae24be6da2123","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct Agile Prices","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Prices"},{"property":"icon","value":""},{"property":"entity_picture","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":""}],"resend":true,"debugEnabled":false},{"id":"6f8158bd34274dd3","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct Agile Seq","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Sequence Table"},{"property":"icon","value":""},{"property":"entity_picture","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":""}],"resend":true,"debugEnabled":false}]

JSONata

This is an open-source language and execution engine (run using JavaScript) that operates on JSON data structures using a functional and declarative approach. JSONata has been added to Node-RED, and in the Node-RED HA add-on it has a few extra HA-specific functions to allow access to HA entities. (Thanks are due to the people who ported Node-RED into HA, and JSONata into Node-RED.)

Within Node-RED, JSONata can be found in several of the nodes, providing the opportunity to extract information from and/or perform predicate tests on a JSON data structure. The ‘change node’ has the ability to set a message variable to the result of a JSONata expression, using the ‘J:’ option, which provides a powerful opportunity to use JSONata to the full.

How JSONata works

JSONata executes a one-line statement, eg “payload.input.value_inc_vat”

Each part of the statement is parsed and evaluated as “<sequence> <operator> <expression>” by executing each operator found in the line, in turn, and from the left. Each operator is evaluated using the input sequence generated or taken from the left-hand side, the operator, the expression on the right-hand side, and the current context. The evaluation result is returned, and each result is then used as the input sequence to the next operator. At the end of the line, the final result is returned as the outcome of the entire line statement.

Each operator execution takes the current context, which begins as the top-level JSON document, and is then set to the outcome of each operation. Context will therefore change as the statement line is evaluated.

The basic execution in JSONata is carried out as a ‘JSON path search’, evaluating object keys found in the statement against the current context. Any key (such as ‘payload’ or ‘input’ or ‘value_inc_vat’) found in the current context is evaluated and the value of that key returned. In simple cases, this execution mirrors what we would expect from any JSON object path notation.

The main JSONata path operators are: -

‘.’ as a mapping function

’ as a filtering function

‘{}’ as a grouping function

‘^()’ as an ordering function (or sort)

Note that these operators are in addition to (overload) a traditional use as object path notation, as an array constructor and array address, and as a JSON object constructor.

Sequences: The execution of each operator evaluates, where appropriate (array or object), the left-hand side (from the current context) as a sequence of items, and applies the right-hand side as a function to each item from the sequence, in turn. The result will typically be another sequence, which is automatically returned in an array as the result.

(There are a few subtleties not yet mentioned, such as path searches can go down into nested structures to return all possible matches, ‘’ binds tighter than ‘.’, singleton items are still returned in an array, and the result sequence will be lifted and flattened for nested result objects.)

Testing and debugging JSONata

The people who created JSONata have provided a website for developing and testing code.

https://try.jsonata.org/

This comes with a few inbuilt examples, but it is very easy to paste our own JSON in the left-hand pane, write a JSONata expression in the top right pane, and immediately see the result in the bottom right pane.

TIP – if you want to copy a JSON object from Node-RED flow to paste into the try.jsonata website, set your debug node to ‘full message’ output and copy the full message object. This will then have payload (and topic etc) within the top-level document. When writing JSONata code, ‘payload’ at the front will be required. This means that your test code can then be pasted back into a Node-RED change node and it will work without modification.

There is an inbuilt JSON formatter (see top right of left-hand pane) which makes the pasted object easier to read. Editing the input JSON also has immediate effect, so it is very simple to test code by editing either/both the input JSON and the JSONata statement.

My JSONata code explained

Combine import and export tariffs into one array:

Note that any formatting here is cosmetic (done by using the formatter in the change node editor, and also by the formatter here - in JSONata # is an operator, not a comment). JSONata just sees one long line:

payload.import#$i.{
   "from": valid_from,
   "upto": valid_to,
   "date": $substring(valid_from,0,10),
   "time": $substring(valid_from,11,5),
   "import": value_inc_vat,
   "export": %.export[$i].value_inc_vat
}^(from)

try-jsonata link: https://try.jsonata.org/cUoa0QVAt

What this JSONata code does

I have exceeded the posting size limit, so if anyone is interested I can post my writeup explanation of the JSONata code separately.

Moving data to Home Assistant for display

Getting tariff prices for ‘now’ and ‘next period’

This part of the Node-RED flow runs every 30 minutes, a few seconds after the hour and half hour. Starting with the full tariff array (read back from context) I need to get the tariff price object where the ‘from’ timestamp is for now. Since the tariff time is always hh:00 or hh:30, I need to round the minutes and remove the seconds.

A Home Assistant sensor node is all that is required to move the data to HA. I have placed the current ‘timestamp’ value into the sensor state, and the full tariff array with the now/next import/export values into attributes. In HA these values can be displayed in an entities card using the attribute.

Table display

I use the custom component flex-table-card, which can display an array as a table. Here I display the best-price sequences. To more easily display both import and export sequences together on one card, I have combined both sets of data into one ‘both_array’ object first. As the flex-table-card needs to dig down into this object, the ‘modify:x.’ step is required for each object key.

type: custom:flex-table-card
title: Octopus Agile - best times
entities:
  include: sensor.octopus_agile_sequence_table
columns:
  - data: both_array
    modify: x.mode
    name: Mode
  - data: both_array
    modify: x.date
    name: Date
  - data: both_array
    modify: x.timefrom
    name: Start
  - data: both_array
    modify: x.timeupto
    name: Stop
  - data: both_array
    modify: x.duration
    name: Mins
  - data: both_array
    modify: x.average
    name: Price (p)

Graphical display

I use the custom apexcharts-card to display the tariff prices. This requires some graph configuration settings as well as data generator code to get the display I want. I use the sensor octopus_agile_prices, which is my Node-RED sensor containing the tariff price array in the attribute variable ‘array’.

Card settings:

I set graph_span to 48h, and span to start at the beginning of the day, and offset by -1h so the graph runs from 23:00 yesterday to 23:00 tomorrow.

I set the tariff series as step-line, and use a data generator. This uses the attribute.array, and pulls the from (timestamp) and price (import/export) mapping from the full array to the new time/price array I need for the chart. The step-line will plot horizontally from the start (from) of the period data-point to the next data point. At the last data point I need to add one extra data point to make the last entry run horizontally on to the end of the day. Hence the last ‘final.push’ adding the ‘upto’ timestamp (of the last period) and the last period price.

On top of the tariff prices, I also plot the best-price sequences. This is a little more complex. The data comes from the other sensor, octopus_agile_sequence_table, and requires a data generator to build an array of all the start-end data points. As this is again a step-line plot, the first data point is at the start of the graph period with value zero, then the next data point is the first sequence-start time and sequence-start average (to give a horizontal from the day start to sequence start, then a vertical at the sequence start). My data plot array therefore starts with [start, 0], then builds [sequence-start, value] and [sequence-end, 0] to give the step-hump for each sequence. The end of the plot finishes at the end of the graph with [end, 0].

As a fudge, I actually plot the sequence lower value at -12, and then force the graph axis to display from -10, thus hiding the connecting horizontal lines. This works nicely for (most) negative import prices, but the settings can be adjusted to accommodate more dramatic ‘plunge pricing’ as required.
Anyway, it all seems to work!

type: custom:apexcharts-card
header:
  show: true
  title: Octopus Agile Prices
show:
  last_updated: true
now:
  show: true
  label: now
graph_span: 48h
span:
  start: day
  offset: '-1h'
series:
  - entity: sensor.octopus_agile_prices
    data_generator: |
      let prices = entity.attributes.array;
      let ends = prices.length-1;
      let final = prices.map((item, index) => {
        return [item.from, item.import];
      });
      final.push([prices[ends].upto, prices[ends].import]);
      return final;
    curve: stepline
    name: Import
    show:
      legend_value: false
      extremas: true
    stroke_width: 2
    color: red
  - entity: sensor.octopus_agile_prices
    data_generator: |
      let prices = entity.attributes.array;
      let ends = prices.length-1;
      let final = prices.map((item, index) => {
        return [item.from, item.export];
      });
      final.push([prices[ends].upto, prices[ends].export]);
      return final;
    curve: stepline
    name: Export
    show:
      legend_value: false
    stroke_width: 2
    color: green
  - entity: sensor.octopus_agile_sequence_table
    data_generator: |
      let prices = entity.attributes.import_array;
      let final = [];
      let i=0;
      let j=0;
      final[j]=[start, -12];
      j++;
      for (i=0; i<prices.length; i++){
        final[j] = [prices[i].from, prices[i].average];
        final[j+1] = [prices[i].upto, -12];
        j=j+2;
      };
      final[j]=[end, -12];
      return final;
    curve: stepline
    name: Low Import
    stroke_width: 1
    color: purple
    show:
      legend_value: false
  - entity: sensor.octopus_agile_sequence_table
    data_generator: |
      let prices = entity.attributes.export_array;
      let final = [];
      let i=0;
      let j=0;
      final[j]=[start, -12];
      j++;
      for (i=0; i<prices.length; i++){
        final[j] = [prices[i].from, prices[i].average];
        final[j+1] = [prices[i].upto, -12];
        j=j+2;
      };
      final[j]=[end, -12];
      return final;
    curve: stepline
    name: High Export
    stroke_width: 1
    color: blue
    show:
      legend_value: false
yaxis:
  - min: -10
4 Likes

Wow - this looks great - just what I was looking for as I am due to have solar and battery installed in a few weeks.

best start to learn node-RED

Great looking project. Ive just moved over to agile and am interested in looking into implementing something like this. Is your project code available to share anywhere? If it is I cant find the link :slight_smile:
Thanks

My approach here is just one way of working with Octopus Agile in HA - there are many ways to do this.

I have done all of the work in Node-RED (which is readily available as an addon in HA) and using JSONata. The entire Node-RED flow (the ‘code’ if you like) is shared above (see immediately under the Node-RED flow picture). If you have Node-RED, then this is simply a ‘copy and paste-import’ job. If you are not familiar with Node-RED, then I am afraid that it will make very little sense (and even less practical use).

Edit. Node-RED flow copied out below VV.

[{"id":"e42010beae8f7fa5","type":"group","z":"2dd6b0b4a5f86125","name":"Octopus Agile Prices","style":{"label":true,"stroke":"#6f2fa0","fill":"#dbcbe7","color":"#001f60"},"nodes":["db6ebccc4bb88b8d","63bba87f93e0eaae","65094b21c3938d73","22fdc0bfd7bdd329","3c0e6412766b7286","0ba1d9b378092267","416e22ecfcbf5616","ce579e86db6fcc9b","be04e63b7258b035","4430da6cfe290e39","3797b648d315c3a1","bc4c506918ed4d9e","b98e955f8996efa2","c63c0e9f64999a1e","a28359905d33b762","59f0bbc0e3c76b0d","aa94392fea62febc","87bddd29d5e5ab90","224dc5d34ebe8967","8a65a4744d5f228a","3c01c3e92c0898b2","03b0c5c88010997c","339092c8ec953466","d8bb3057287698de","a92b801c291d33d0","56dd710454ea2e25","ee45b426f60a08a0","a9942de6076269cf"],"x":34,"y":1839,"w":982,"h":462},{"id":"db6ebccc4bb88b8d","type":"http request","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Octopus Export","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-OUTGOING-19-05-13/electricity-tariffs/E-1R-AGILE-OUTGOING-19-05-13-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":320,"y":1960,"wires":[["65094b21c3938d73"]]},{"id":"63bba87f93e0eaae","type":"http request","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Octopus Import","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-22-08-31/electricity-tariffs/E-1R-AGILE-22-08-31-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":320,"y":1920,"wires":[["22fdc0bfd7bdd329"]]},{"id":"65094b21c3938d73","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":1960,"wires":[["3c0e6412766b7286"]]},{"id":"22fdc0bfd7bdd329","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":1920,"wires":[["3c0e6412766b7286"]]},{"id":"3c0e6412766b7286","type":"join","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Combine","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":640,"y":1920,"wires":[["416e22ecfcbf5616"]]},{"id":"0ba1d9b378092267","type":"inject","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"@ 16:05","props":[{"p":"payload"}],"repeat":"","crontab":"05 16 * * *","once":true,"onceDelay":"2","topic":"","payload":"","payloadType":"date","x":140,"y":1920,"wires":[["db6ebccc4bb88b8d","63bba87f93e0eaae"]]},{"id":"416e22ecfcbf5616","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Build & Save Tariff Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.import#$i.{\t   \"from\": valid_from,\t   \"upto\": valid_to,\t   \"date\": $substring(valid_from,0,10),\t   \"timefrom\": $substring(valid_from,11,5),\t   \"timeupto\": $substring(valid_to,11,5),\t   \"import\": value_inc_vat,\t   \"export\": %.export[$i].value_inc_vat\t}^(from)","tot":"jsonata"},{"t":"set","p":"OctAgileTariff","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":1920,"wires":[["56dd710454ea2e25"]]},{"id":"ce579e86db6fcc9b","type":"comment","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Octopus agile prices (2 days) JSONata V2.0.0","info":"","x":230,"y":1880,"wires":[]},{"id":"be04e63b7258b035","type":"ha-sensor","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Octopus Agile Prices","entityConfig":"893ae24be6da2123","version":0,"state":"agile.now.from","stateType":"msg","attributes":[{"property":"array","value":"payload","valueType":"msg"},{"property":"import","value":"agile.now.import","valueType":"msg"},{"property":"export","value":"agile.now.export","valueType":"msg"},{"property":"import_next","value":"agile.next.import","valueType":"msg"},{"property":"export_next","value":"agile.next.export","valueType":"msg"}],"inputOverride":"allow","outputProperties":[],"x":860,"y":2260,"wires":[[]]},{"id":"4430da6cfe290e39","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Value Now","rules":[{"t":"set","p":"agile","pt":"msg","to":"(\t   /* function to add <delta> minutes to now */\t   $tstep:=function($mdelta) {\t       $fromMillis($toMillis($now())+$mdelta*60000)\t   };\t\t   /* function to build key value from timestamp                         */\t   /* strip fraction of seconds, floor minutes to 00 or 30, zero seconds */\t   $keyis:=function($ts) {\t       (\t           $t:=$replace($ts, /(\\.[0-9]+)/, '');\t           $m:=$substring($t,-6,2).$number()<30 ? '00': '30';\t           $substring($t,0,14) & $m & \":00\" & $substring($t,-1);\t           )\t   };\t\t   /* return object of 'now' and 'next' value objects */\t   {\t       \"now\": payload[from=$keyis($now())],\t       \"next\": payload[from=$keyis($tstep(30))]\t   };\t   )","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":670,"y":2260,"wires":[["be04e63b7258b035"]]},{"id":"3797b648d315c3a1","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":2260,"wires":[["4430da6cfe290e39"]]},{"id":"bc4c506918ed4d9e","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Import Low","rules":[{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* look at latest prices, sorted by ascending import cost     */\t/* pick the lowest <sample size>, create new object for each  */\t/* with {date, from, upto, cost} Sort by date & time          */\t\tpayload[[48..95]]^(import)[[0..$$.sample-1]].{\t   \"from\": from,\t   \"upto\": upto,\t   \"date\": date,\t   \"timefrom\": timefrom,\t   \"timeupto\": timeupto,\t   \"value\": import\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":2060,"wires":[["aa94392fea62febc"]]},{"id":"b98e955f8996efa2","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":150,"y":2060,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"c63c0e9f64999a1e","type":"inject","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Manual","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":2020,"wires":[["b98e955f8996efa2"]]},{"id":"a28359905d33b762","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Export High","rules":[{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* for export values - as for import but */\t/* sorted from highest to lowest, top <n> */\t\tpayload[[48..95]]^(>export)[[0..$$.sample-1]].{\t   \"from\": from,\t   \"upto\": upto,\t   \"date\": date,\t   \"timefrom\": timefrom,\t   \"timeupto\": timeupto,\t   \"value\": export\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":2100,"wires":[["aa94392fea62febc"]]},{"id":"59f0bbc0e3c76b0d","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Groups","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* get start and end indexes, and zip into a sequence array of [start, end]                */\t/* map this array of sequences to an array of objects, one for each sequence to include    */\t/* sequence number, index range, date, start time, stop time, count of half-hours          */\t/* duration, and average (sum of values in sequence divided by count of items in sequence) */\t\t(\t   $chain:=$zip(payload[position=\"start\"].index, payload[position=\"end\"].index);\t   $map($chain, function($item, $index) {\t       (\t        $aa:=payload[$a:=$item[0]];\t        $bb:=payload[$b:=$item[1]];\t        $count:=$b-$a+1;\t        {\t        \"mode\": topic,\t        \"sample\": sample,\t        \"sequence\": $index+1,\t        \"range\": ($a+1) & \"-\" & ($b+1),\t        \"from\": $aa.from,\t        \"upto\":  $bb.upto,\t        \"date\": $aa.date,\t        \"timefrom\": $aa.timefrom,\t        \"timeupto\": $bb.timeupto,\t        \"periods\": $count,\t        \"duration\": $count*30,\t        \"average\": $round(($sum(payload[[$a..$b]].value)/$count)*1000)/1000\t    })\t   })^(from)[];\t)\t\t/* (>duration) gets longest periods first, however (from) sorts           */\t/* chronologically, which is required for correct display on tariff graph */\t/* final [] places result into an array even if only one sequence group   */\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":2160,"wires":[["8a65a4744d5f228a"]]},{"id":"aa94392fea62febc","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Sequence V2","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* For Version 2 - variable change needed  */\t/* note index position, and test if items  */\t/* are consecutive - forwards and backwards */\t/* set position as only, start, mid, end */\t\tpayload#$v.(\t   $last:=$v<1 ? false : from=%.payload[$v-1].upto;\t   $next:=upto=%.payload[$v+1].from;\t   $position:=(\t       $last ? ($next ? \"middle\" : \"end\") : ($next ? \"start\" : \"only\")\t   );\t   {\t       \"from\": from,\t       \"upto\": upto,\t       \"date\": date,\t       \"timefrom\": timefrom,\t       \"timeupto\": timeupto,\t       \"value\": value,\t       \"index\": $v,\t       \"link_last\": $last,\t       \"link_next\": $next,\t       \"position\": $position\t}\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":2060,"wires":[["59f0bbc0e3c76b0d","224dc5d34ebe8967"]]},{"id":"87bddd29d5e5ab90","type":"link in","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":185,"y":2100,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"224dc5d34ebe8967","type":"join","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":670,"y":2060,"wires":[["3c01c3e92c0898b2"]]},{"id":"8a65a4744d5f228a","type":"join","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":530,"y":2160,"wires":[["a92b801c291d33d0"]]},{"id":"3c01c3e92c0898b2","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Save Best","rules":[{"t":"set","p":"OctAgileBest","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":810,"y":2060,"wires":[[]]},{"id":"03b0c5c88010997c","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Save Period","rules":[{"t":"set","p":"OctAgilePeriod","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":810,"y":2120,"wires":[[]]},{"id":"339092c8ec953466","type":"ha-sensor","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Octopus Agile Sequences","entityConfig":"6f8158bd34274dd3","version":0,"state":"$count(payload.export) + $count(payload.import)","stateType":"jsonata","attributes":[{"property":"export_array","value":"payload.export","valueType":"msg"},{"property":"import_array","value":"payload.import","valueType":"msg"},{"property":"both_array","value":"payload.both","valueType":"msg"},{"property":"bid_offer_spread","value":"$min(payload.import.average)-$max(payload.export.average)","valueType":"jsonata"}],"inputOverride":"allow","outputProperties":[],"x":850,"y":2160,"wires":[[]]},{"id":"d8bb3057287698de","type":"link in","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":555,"y":2240,"wires":[["4430da6cfe290e39"]]},{"id":"a92b801c291d33d0","type":"change","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Table","rules":[{"t":"set","p":"payload.both","pt":"msg","to":"$append(payload.import, payload.export)\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":2160,"wires":[["03b0c5c88010997c","339092c8ec953466"]]},{"id":"56dd710454ea2e25","type":"link out","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Daily Update","mode":"link","links":["87bddd29d5e5ab90","d8bb3057287698de"],"x":975,"y":1920,"wires":[]},{"id":"ee45b426f60a08a0","type":"inject","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"Half Hour","props":[{"p":"payload"}],"repeat":"","crontab":"*/30 0-23 * * *","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":2260,"wires":[["a9942de6076269cf"]]},{"id":"a9942de6076269cf","type":"delay","z":"2dd6b0b4a5f86125","g":"e42010beae8f7fa5","name":"10s","pauseType":"delay","timeout":"10","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":290,"y":2260,"wires":[["3797b648d315c3a1"]]},{"id":"893ae24be6da2123","type":"ha-entity-config","server":"44b2605f.5d41","deviceConfig":"","name":"SC Oct Agile Prices","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Prices"},{"property":"icon","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":"measurement"}],"resend":true},{"id":"6f8158bd34274dd3","type":"ha-entity-config","server":"44b2605f.5d41","deviceConfig":"","name":"SC Oct Agile Seq","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Sequence Table"},{"property":"icon","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":"measurement"}],"resend":true}]

After that, it is a case of making sure the websocket node HA server is set up, working and connected to HA, then checking the websocket entity sensor nodes are correctly configured and working correctly.

I have provided the configuration settings (‘code’) for the flex-table-card and for the apex-charts-card, which means the agile tariffs are available in HA in entity, table, and graphical form.

I have tested, checked, and double checked all of this, and it should work. After that, it is a case of deciding yourself how to use the sensor data for your own purpose.

I did this in Node-RED as I find it simple and ‘code free’. Basically, there are only about ten nodes that do all the work, and they are configured using UI. How much simpler could this be?! It also means that, for anyone who is interested in using Node-RED alongside/within HA, this is something that works but can be easily modified and adapted.

Complicated as it may appear, I did the ‘coding’ in JSONata as I wanted a real-life example to learn from, and I hope that this can assist others who also want to use JSONata to process JSON structures without having to use Python or JavaScript.

I am aware that Node-RED is very much a minority interest in HA, and I believe that JSONata is even further down the list of interesting topics, but on the other hand I find this an easy and powerful way to obtain, process, and present information to HA.

Hi Geoff,

This code looks just what I need. Unfortunately my coding experience is very little!
I have HA configured with apex charts/node red etc
Could you do a guide for the barely literate amongst us please?
ie. 1. install xxx
2. copy xxx to xxxx

Much appreciated,
Bill

  1. Ensure you have Node-RED as an addon: installed and running.
    https://github.com/hassio-addons/addon-node-red/blob/0a0c4f049124de14e25be07ccb28148259a58ec6/node-red/DOCS.md

  2. Ensure that Node-RED has the node-red-contrib-home-assistant-websocket nodes installed (check your Node-RED pallet).
    https://github.com/zachowj/node-red-contrib-home-assistant-websocket
    Worth checking that you have a homeassistant configuration (server) node working at this point.

  3. Copy the Node-RED flow (see above, just use the ‘copy’ option top right of the quoted flow). In Node-RED go to the hamburger menu, select ‘import’, paste the flow (control V), and import.

  4. Deploy. If you already have the websocket nodes installed you should have a homeassistant configuration (server) node working, if not you will need to add this and set it up. If you have NR installed as an HA addon, this will already be there. Worth opening (edit) one HA entity sensor node, opening (edit) the configuration node, and checking that the server is correctly configured.

  5. Check: Test that the API calls work, check that the context is updated with the output, and check that the HA sensor nodes update (see status) and update in HA.

  6. The HA sensors should now be updating - you will need to create one or more entity cards to display the state/attributes. You will need to add a new apex-charts card and configure by copying the entire code (see the quoted section under Graphical Display - ‘card settings’) and pasting over the default code.

And: if it is any consolation, one year ago I started with HA and knew absolutely nothing of any of all of this. It is amazing what you can learn in a year.

Hi Geoff,

Many thanks for your very speedy response!, much appreciated. I have now resolved the issue. The thing I was not doing, when I tried to deploy the flow, was going into the entities/sensors it said were missing in node red. This was through clicking show sensors. I then needed to click the update button in the top right hand corner.

Still trying to clamber up that steep learning curve!

Regards,
Bill

To export Node-RED flows (for sharing on this site etc) we should use the ‘scrubber’ https://zachowj.github.io/node-red-contrib-home-assistant-websocket/scrubber/ to remove personal settings, and optionally the homeassistant server. This prevents the import from trying to bring in a potentially conflicting ha server configuration node. The downside is that, yes, you do need to reconfigure the ha sensor nodes with your own (already existing / create new) ha server configuration node.

I hope it now all works for you, and that you find it useful. The advantage, in my mind, of having something like this in Node-RED rather than an ‘integration’ is that it is much easier to adapt, change, and re-use. As long as it all works, I would suggest you steer clear of the JSONata for a while. That is not a steep learning curve but rather a vertical rock face with an overhang at the top.

Geoff,

Firstly this is a great post. Thank you so much for sharing your setup like this. It’s exactly what I’ve been looking to do.

I now have your setup working well on my system. I’ve managed to set up my solar inverter and battery so that Home Assistant can control whether to charge or discharge.

The next step is to come up with a stragegy to maximise charge/discharge profits and ideally maximise solar self consumption. Is this something that you have set up on your system? I’m interested in everyone’s thoughts on the best charge/discharge strategy to use, and on where we could take it!

Options I’ve considered so far are:

Option 1. Set import and export price limits below and above which the battery is charged/discharged.

Option 2. Do some sort of look ahead to decide on the best charge/discharge times, possibly using the “best” periods calculated calculated in your flow.

Option 3. Integrate the Forecast.Solar data to try to maximise the amount of free solar energy used, along with maximising profits from charge/discharge cycles.

Thanks!

Thanks for the positive feedback.

I believe that for active solar/battery control we must have accurate data (solar, forecast, battery SOC, load, and utility pricing) as well as rock solid inverter control, and an overall strategy.
I am mostly there.
As a strategy I have a prototype based on Octopus export pricing - dump battery charge at high price, sell excess solar, charge battery, or increase domestic load.
Currently I am on fixed price Octopus tariff, and with a battery storage capacity equal to 24hr domestic load, agile export does not offer any real financial benefit. I am monitoring agile import this winter to see if purchase for use/charge for later use has a price difference that exceeds the battery charge/discharge round trip costs. So far I am not convinced that, for myself, the limited benefits outway the additional operating costs and complexity.
I have used standby capacity and automated inverter and battery management to take advantage of the Demand Management saving sessions, which may be easier to use and just as financial rewarding.

Hey,

Firstly - @Biscuit - Thank you so much for spending your time on this, I’ve just followed the instructions and can report that (despite me needing to perform a few updates and HACS installs) worked seamlessly!

What I cant work out however (no doubt I’ve missed a step somewhere) is how do I get the table shown in your very very first image, the lower half - showing the now and next prices.

Also - more for my use case - what and where do I look to remove any ‘export’ elements from the flex-table resultant data. I dont (and never will) do any exporting to Octopus as am still on a FIT.

Thanks again

Dan

Thanks for the positive feedback. I am glad that it worked for you, HA and Node RED do require some TLC from time to time to keep it all up to date!

Octopus Agile (import / export price for now /next ) is just a basic entities card, setup to read the attributes. The current thought is to provide an HA entity / sensor for each and every value, but I personally like to pass groups of data using attributes. Keeps things tidy, but getting at them in HA does require a bit more work.

Use a standard entities card. The attributes require a type that is not supported in the UI editor, so switch to the code editor and paste in the following. Modify as you require - it just uses the sensors created by the Node RED code, picking up the attributes ‘import’, ‘import_next’ and so on.

I added the ‘best bid-offer’ spread value as I wanted to see when (if ever) it was cost effective to charge my battery on the cheapest import rate, and sell it back on the most expensive export rate.

type: entities
entities:
  - entity: sensor.octopus_agile_prices
    name: Time period
    secondary_info: last-updated
    icon: mdi:av-timer
  - type: attribute
    entity: sensor.octopus_agile_prices
    attribute: import
    name: Import Price - now
    suffix: p
    icon: mdi:currency-gbp
  - type: attribute
    entity: sensor.octopus_agile_prices
    attribute: import_next
    name: Import Price - next
    suffix: p
    icon: mdi:currency-gbp
  - type: attribute
    entity: sensor.octopus_agile_prices
    attribute: export
    name: Export Price - now
    suffix: p
    icon: mdi:currency-gbp
  - type: attribute
    entity: sensor.octopus_agile_prices
    attribute: export_next
    name: Export Price - next
    suffix: p
    icon: mdi:currency-gbp
  - type: divider
  - type: attribute
    entity: sensor.octopus_agile_sequence_table
    attribute: bid_offer_spread
    name: Best bid-offer
    suffix: p
    icon: mdi:basket
title: Octopus Agile
show_header_toggle: false
state_color: false

Agile best periods - import only
I am on fixed tariff import and export, but I like looking at the import/export agile prices as it is a good indicator of the weather tomorrow - high export price means it is not going to be windy in the UK. I took part in the Octopus Saver Sessions last winter, and found the day-ahead Agile prices a useful indicator of when a session was likely.

The JSONata for the ‘Octopus Agile Sequence_Table’ sensor generates an array for import, and an array for export. Originally I had two tables, but then wanted to combine into just one table, so I created an array of both. However, the code still generates an import only and an export only table, which you can get to in the attributes import_array or export_array, rather than both_array.

The following configuration for the flex table card will show just the import best periods. Again it is quite easy to modify, so you can remove any of the columns you don’t want. The ‘sample’ column and the ‘mode’ column can be removed just by deleating the first two -data: blocks.

type: custom:flex-table-card
title: Octopus Agile times - IMPORT
entities:
  include: sensor.octopus_agile_sequence_table
columns:
  - data: import_array
    modify: x.sample
    name: Size
  - data: import_array
    modify: x.mode
    name: Mode
  - data: import_array
    modify: x.date
    name: Date
  - data: import_array
    modify: x.timefrom
    name: Start
  - data: import_array
    modify: x.timeupto
    name: Stop
  - data: import_array
    modify: x.duration
    name: Mins
  - data: import_array
    modify: x.average
    name: Cost (p)

Hope that answers your questions.

2 Likes

Hi, I have found this fascinating and have put your code to good use. Now i am having trouble trying to get an automation running utilising the best import times, as flagged up in node-red/agile_sequence_table, and switching on and off a charging device.

Has anyone done this already as it is proving to be a nightmare from my perspective…

Although I wrote the Node-RED flow, I don’t use Agile so I did not get as far as writing anything to use this. However, it is quite possible to extract the bits of information, either using JSONata in change nodes, or using templates.

This is certainly clunky, and may not be correct, but the following template should return ‘true’ if any one of the ‘on’ times in the array of import best prices is within two minutes of ‘now’.

{% set ns=namespace() %}
{% set ns.flag=false %}
{% for event in state_attr('sensor.octopus_agile_sequence_table', 'import_array')%}
  {% set ontime=(as_timestamp(event.from)|int-as_timestamp(now())|int) %}
  {% set ns.flag = ns.flag or (ontime<120 and ontime>0) %}
{% endfor %}
{{ns.flag}}

The namespace() is required to create a variable that encompasses the for-loop scope.
The loop iterates over the import_array (attribute) in the sensor, and ‘ontime’ pics up the number of seconds between each on time and now. The ns.flag is set to true if ontime is between 120 seconds and 0 seconds before now, and will be set to true for any of the events in the array.
{{ns.flag}} at the end returns the true/false value.

I am no expert in automations, but I understand that a trigger can be a template (as above) that returns true to fire the automation. After that, you only need to do something with the automation, and repeat this with ‘event.upto’ in another automation trigger template to turn off at the end of each period. The ‘120’ ensures that the template will be true for at least one period between template evaluations (which may only happen once per minute in this case).

This should work for each and all of the given event periods.
Hope this helps!

Geoff, blimey my head hurts…I can program in sinclair basic (shows my background) and now having jumped into homeassistant 1 month ago…okay so from your table I am trying to implement the import array and the start time and stop time to start a charging device (and stop it) so that my batteries get a decent charge and the inverter can run afterward powering the house. I really have limted modern programming knowledge but I am trying to get back in the saddle.

I’ll take your code, thank you so much, and explore setting up said automation in homeassistant and try to get this working. I don’t want to go to the cloud but rather keep this in house so to speak.

Thank you so much…this is a steep learning curve…

I to learnt to program on a ZX Spectrum. I have been writing up my automation on my blog. They are not to @Biscuit level, but you might find them interesting.

Perhaps I misread your expertise from your original question. Node-RED is not that popular with HA users, so anyone who has NR installed and working must know what they are doing!

I have great sympathy since I started with Home Assistant only 18 months ago, and yes it is a very steep learning curve.

There is so much to cover

  • how HA is setup and maintained
  • the basic ‘event’ engine and what it does
  • entities and how they work
  • the history and database subsystem
  • how to configure HA using the YAML config file (and how to correctly code YAML files)
  • how to configure various parts using the UI settings (helpers, automations, etc)
  • to to add ‘addon’ programs
  • how to add integrations (both automatically found and custom from HACS)
  • how to build templates (required for template sensors and automations) using Jinja

I have decided to use Node-RED for most of my work, and that necessitated

  • how to set up Node-RED
  • how to manage the node pallet (add new node sets and update regularly)
  • how to write function nodes using JavaScript
  • JSONata
  • the HA websocket nodes (connecting NR to HA)

To do anything useful in HA also requires hardware - it is designed to connect to the real world to take in data (sensors) and control devices In my case that means a solar inverter and battery set, using Modbus over TCP and RS485 - hence the need to learn how to manage and work with these things and their various protocols in Node-RED and HA

The challenge is that, not being turn-key or plug and play, to get anything ‘useful’ done requires a bit of everything. Hence the simple answer to your question is ‘yes, but it takes a lot of learning and work’. Which, for me, about sums up the whole Home Assistant experience!

Starting from the end and working backwards: you need something to turn on and off, controlled by HA. Assuming that you are only charging small stuff like phones etc, a smart plug will do nicely.

I have recently bought some TAPO plugs (switch) - they are the new brand offering from TP-link
https://www.tp-link.com/uk/home-networking/smart-plug/

I got the P110 version as that has power monitoring included. Requires a TPlink account and app to set them up, but they work and I have integrated them into my Google home so I can ask Google to turn ‘T3’ on or off.

There is a custom integration for Tapo devices - https://github.com/petretiandrea/home-assistant-tapo-p100. Again this is a bit klunky (it requires access to the cloud TPlink account to verify) but it works and I can control the plug (switches) from HA. Easy enough to put a ‘switch’ on the dashboard for my smart plug.

After that, I found some Node-RED contrib (community) nodes that work with Tapo devices. I am using https://flows.nodered.org/node/node-red-contrib-tplink-tapo-connect-api which seems to work nicely with my TP110s. The ‘on’ and ‘off’ nodes even have an ‘alias’ mode, so I can set the node to turn the ‘T3’ plug on just using the name and not the IP address (useful as the address is not static and my BT router refuses to load any more into the static address table). Slow, but it works.

Now I have a choice

  • use HA to control the smart plug (automation or similar, based on agile price time period)
  • use Node-RED to control the smart plug (coded flow)

Since Octopus Energy appear to have been granted a licence to crenellate, the agile price goes up and down by the half hour, so my original work was an attempt to try and identify the ‘best’ concurrent periods for low prices. In your case, assuming that you are looking to charge something like phones, it does not matter about concurrency, more about lowest price. If I were looking to do this, I would probably go back to my ‘best 15 periods’ (OctAgileBest in context), sort by ascending value, and say pick the first 10 (giving five hours of charging at the lowest possible price). Then look to turn the smart plug on and off accordingly.

The tricky bit is, at each end off period, looking to see if the next on period is concurrent, and thereby joining them to avoid turning off and then on again. If I have time next month I might try and look at that problem - it would be easier having a switch entity in HA that just goes ‘on’ and ‘off’ when the agile import price was in one of the lowest (say) 10 periods!

I am currently trying to get my head around Octopus Flux (new tariff) to try and model my solar/load/battery to see a) if it would be financially better and b) how to control the inverter to drive the charge/discharge periods required.

There is always something to learn!

Good luck with your project.

Thank you for this!

I had this working for a couple of days before I had a really bizarre bug with the chart going haywire:

It turned out that the prices coming into the data_generator weren’t in any time order for some reason. I added this line into the data_generator just after prices were defined:

prices.sort((a, b) => new Date(a.from) - new Date(b.from));

and that solved the issue. I also added some gradient colouring to the chart to visualise pricing a bit easier. Here’s my version if anyone’s interested. N.B. I don’t generate any energy so I’ve removed the export charges to clean it up a bit.

type: custom:apexcharts-card
experimental:
  color_threshold: true
header:
  show: true
  title: Octopus Agile Prices
show:
  last_updated: true
now:
  show: true
  label: now
graph_span: 24h
span:
  start: day
  offset: '-1h'
series:
  - entity: sensor.octopus_agile_prices
    data_generator: |
      let prices = entity.attributes.array;
      prices.sort((a, b) => new Date(a.from) - new Date(b.from));
      let ends = prices.length-1;
      let final = prices.map((item, index) => {
        return [item.from, item.import];
      });
      final.push([(prices[ends].upto), prices[ends].import]);
      return final;
    curve: stepline
    name: Import
    show:
      legend_value: false
      extremas: true
    stroke_width: 2
    color: red
    color_threshold:
      - value: 0
        color: midnightblue
      - value: 5
        color: lightgreen
      - value: 10
        color: green
      - value: 15
        color: yellow
      - value: 20
        color: orange
      - value: 25
        color: red
      - value: 30
        color: magenta
yaxis:
  - min: 0
1 Like

1 Like

Hi Geoff, I appreciate the information and a chance to get stuck into coding. Taking others coding and working out what bit does what is what i tend to do to learn but sometimes i get stuck. I am still ploughing on with this as it is part of a massive project:-

2 x 24v tesla batteries in parallel
2 x 1000w grid tie inverters to feed back
2 x 50 amp 24v chargers to charge up the batteries
and various components to control switch on/off and monitor voltage/current/temperature

so i am digging into your code as i want my chargers to switch on and rapidly charge the batteries at the best times of the day. To use a fixed price means every day i have to monitor and modify. I could use IFTTT in the cloud but i want home assistant to do it all so if the internet goes poof for a few hours, the charger and inverter continue to work offline and HA can continue to control them.

So far i am about to switch over to Agile from econ 7 and currently the setup works charging overnight and feeding back during the day but i am trying different automations to see if i can use your graph to switch the charger on and fill up on the cheapest juice possible.

There may be other ways through the octopus agile integration but my mind is fizzing with ideas and i like an option or two…

Many thanks for your input and insights, good code you have here,

Shaggy