Using lowest flexible energy prices to activate electric boiler

Dear all,
I am just a starter in Home Assistent and am already very happy with what I have achived up to now. Reading sensors, and doing some simple automation jobs.
My goal is a lot harder to achive I guess, but maybe not for the Home Assistant Guru’s over here :slight_smile:

My case:
Some energy companies in the Netherlands offer contracts with flexible energy-tarifs. When you have a smart meter in your home (like I have), these companies can read-out your energy-usage per hour. You will then be billed for your energy-usage of each hour according to the energy-tariff of that corrensponding hour. The flexible energy-tarifs can be found on the site of the EPEX(APX).

How can I read these tarifs in Home Assistant and determine what the lowest 3 tariffs are during that day?
And how can I trigger the electric boiler on that?

Regards,

Remco

1 Like

Reviving this old thread:
EPEX Spot and EEX are becoming more and more interesting to include in HA as more energy providers in more European countries adopt the tariff model to sell energy at the spot market price. Meanwhile there is aWATTar in Austria and Germany, Barry in Denmark and France and surely others that I have not yet heard of. Especially with BEVs becoming more popular I am very sure this tariff model will spread rapidly.

1 Like

Doing some research this actually seems to be quite simple. aWATTar providers a free API that can be polled up to 100 times a day and delivers an EPEX prognosis for the next 24 hours.

Here is the API description:
https://www.awattar.at/services/api
This call shows the JSON result for the prognosis for the next 24 hours:
https://api.awattar.at/v1/marketdata

If you have Node RED installed within HA it is very easy to poll this data every 15 minutes and feed it into some HA sensors. So you don’t have to wait for any readymade HA integration of EPEX to be published.

1 Like

Here is a solution in Node RED to read the EPEX prognosis from the free aWATTar API and integrate it directly into HA:

[{"id":"c7fccfcd.3bb33","type":"tab","label":"EPEX Spot","disabled":false,"info":""},{"id":"9a3bb6ba.210408","type":"http request","z":"c7fccfcd.3bb33","name":"EPEX Spot Preise von aWATTar abfragen","method":"GET","ret":"obj","paytoqs":"query","url":"https://api.awattar.at/v1/marketdata","tls":"","persist":false,"proxy":"","authType":"","x":400,"y":220,"wires":[["5dc686e.1cebb78","d6514597.c36ab8"]]},{"id":"c61b8743.1663c8","type":"inject","z":"c7fccfcd.3bb33","name":"Immer zur vollen Stunde","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"0 0-23 * * *","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":100,"wires":[["2b7c38bf.0fd5d8"]]},{"id":"bdeb79c3.d8e0b8","type":"comment","z":"c7fccfcd.3bb33","name":"EPEX SPOT über aWATTar API...","info":"JSON-API:\nhttps://api.awattar.at/v1/marketdata\nhttps://api.awattar.at/v1/marketdata?start=1628139600000&end=1628226000000\n\n100 Requests pro Tag kostenlos\n\nBeschreibung aller aWATTar-APIs:\nhttps://www.awattar.at/services/api","x":170,"y":40,"wires":[]},{"id":"e51c5079.28ff3","type":"ha-entity","z":"c7fccfcd.3bb33","name":"epex_spot_price","server":"cb38d2a3.10198","version":1,"debugenabled":false,"outputs":1,"entityType":"sensor","config":[{"property":"name","value":"epex_spot_price"},{"property":"device_class","value":""},{"property":"icon","value":"hass:chart-line"},{"property":"unit_of_measurement","value":"ct/kWh"}],"state":"payload","stateType":"msg","attributes":[{"property":"Hour 00-01","value":"hour00","valueType":"msg"},{"property":"Hour 01-02","value":"hour01","valueType":"msg"},{"property":"Hour 02-03","value":"hour02","valueType":"msg"},{"property":"Hour 03-04","value":"hour03","valueType":"msg"},{"property":"Hour 04-05","value":"hour04","valueType":"msg"},{"property":"Hour 05-06","value":"hour05","valueType":"msg"},{"property":"Hour 06-07","value":"hour06","valueType":"msg"},{"property":"Hour 07-08","value":"hour07","valueType":"msg"},{"property":"Hour 08-09","value":"hour08","valueType":"msg"},{"property":"Hour 09-10","value":"hour09","valueType":"msg"},{"property":"Hour 10-11","value":"hour10","valueType":"msg"},{"property":"Hour 11-12","value":"hour11","valueType":"msg"},{"property":"Hour 12-13","value":"hour12","valueType":"msg"},{"property":"Hour 13-14","value":"hour13","valueType":"msg"},{"property":"Hour 14-15","value":"hour14","valueType":"msg"},{"property":"Hour 15-16","value":"hour15","valueType":"msg"},{"property":"Hour 16-17","value":"hour16","valueType":"msg"},{"property":"Hour 17-18","value":"hour17","valueType":"msg"},{"property":"Hour 18-19","value":"hour18","valueType":"msg"},{"property":"Hour 19-20","value":"hour19","valueType":"msg"},{"property":"Hour 20-21","value":"hour20","valueType":"msg"},{"property":"Hour 21-22","value":"hour21","valueType":"msg"},{"property":"Hour 22-23","value":"hour22","valueType":"msg"},{"property":"Hour 23-24","value":"hour23","valueType":"msg"},{"property":"Hour \\+00","value":"hourRel00","valueType":"msg"},{"property":"Hour \\+01","value":"hourRel01","valueType":"msg"},{"property":"Hour \\+02","value":"hourRel02","valueType":"msg"},{"property":"Hour \\+03","value":"hourRel03","valueType":"msg"},{"property":"Hour \\+04","value":"hourRel04","valueType":"msg"},{"property":"Hour \\+05","value":"hourRel05","valueType":"msg"},{"property":"Hour \\+06","value":"hourRel06","valueType":"msg"},{"property":"Hour \\+07","value":"hourRel07","valueType":"msg"},{"property":"Hour \\+08","value":"hourRel08","valueType":"msg"},{"property":"Hour \\+09","value":"hourRel09","valueType":"msg"},{"property":"Hour \\+10","value":"hourRel10","valueType":"msg"},{"property":"Hour \\+11","value":"hourRel11","valueType":"msg"},{"property":"Hour \\+12","value":"hourRel12","valueType":"msg"},{"property":"Hour \\+13","value":"hourRel13","valueType":"msg"},{"property":"Hour \\+14","value":"hourRel14","valueType":"msg"},{"property":"Hour \\+15","value":"hourRel15","valueType":"msg"},{"property":"Hour \\+16","value":"hourRel16","valueType":"msg"},{"property":"Hour \\+17","value":"hourRel17","valueType":"msg"},{"property":"Hour \\+18","value":"hourRel18","valueType":"msg"},{"property":"Hour \\+19","value":"hourRel19","valueType":"msg"},{"property":"Hour \\+20","value":"hourRel20","valueType":"msg"},{"property":"Hour \\+21","value":"hourRel21","valueType":"msg"},{"property":"Hour \\+22","value":"hourRel22","valueType":"msg"},{"property":"Hour \\+23","value":"hourRel23","valueType":"msg"}],"resend":true,"outputLocation":"","outputLocationType":"none","inputOverride":"allow","x":320,"y":400,"wires":[[]]},{"id":"d6514597.c36ab8","type":"function","z":"c7fccfcd.3bb33","name":"Daten erhalten?","func":"if(\n    typeof msg.payload === \"object\" && \n    msg.payload.data &&\n    msg.payload.data.length>0\n){\n    return msg;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":280,"wires":[["dffb3653.a7d7b8"]]},{"id":"5dc686e.1cebb78","type":"debug","z":"c7fccfcd.3bb33","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":650,"y":220,"wires":[]},{"id":"dffb3653.a7d7b8","type":"function","z":"c7fccfcd.3bb33","name":"Loop über 24 Werte für kommende Stunden, Daten aufbereiten in getrennten msg-Properties","func":"let data = msg.payload.data;\n\nlet nowDate = new Date();\nlet nowHours = nowDate.getHours();\ndata.forEach((oHourSlice, i) => {\n    let startDate = new Date(oHourSlice.start_timestamp);\n    let startHourLocal = (\"0\" + startDate.getHours()).slice(-2);\n    let startHourLocalRel = (\"0\" + ((startDate.getHours()-nowDate.getHours()+24) % 24)).slice(-2);\n    let endDate = new Date(oHourSlice.end_timestamp);\n    let endHourLocal = (\"0\" + endDate.getHours()).slice(-2);\n    let startHourLocalRelRel = (\"0\" + ((endDate.getHours()-nowDate.getHours()+24) % 24)).slice(-2);\n    // Eur/MWh => ct/kWh\n    let hourPriceInCt = (oHourSlice.marketprice/10).toFixed(2); \n    if(nowHours>=startHourLocal && nowHours<endHourLocal){\n        msg.payload = hourPriceInCt;\n    }\n    msg[\"hour\"+startHourLocal] = hourPriceInCt;\n    msg[\"hourRel\"+startHourLocalRel] = hourPriceInCt;\n\n    //node.warn(oHourSlice);\n    //node.warn(startHourLocal);\n    //node.warn(endHourLocal);\n    //node.warn(hourPriceInCt);\n});\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":560,"y":340,"wires":[["824c2a75.497688","e51c5079.28ff3"]]},{"id":"824c2a75.497688","type":"debug","z":"c7fccfcd.3bb33","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":690,"y":400,"wires":[]},{"id":"2b7c38bf.0fd5d8","type":"function","z":"c7fccfcd.3bb33","name":"payload.start = lastFullHour; payload.end = lastFullHour+24","func":"let lastFullHour = new Date();\nlastFullHour.setMinutes(0);\nlastFullHour.setSeconds(0);\nlastFullHour.setMilliseconds(0);\n\nlet startdate = lastFullHour.getTime();\nlet enddate = startdate + 24*3600*1000;\n\n//node.warn(startdate);\n//node.warn(enddate);\n\nmsg = {\n    payload: {\n        start: startdate,\n        end: enddate\n    }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":160,"wires":[["9a3bb6ba.210408","8e226df8.f6851"]]},{"id":"8e226df8.f6851","type":"debug","z":"c7fccfcd.3bb33","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":160,"wires":[]},{"id":"25d4e0ed.20c31","type":"inject","z":"c7fccfcd.3bb33","name":"Test","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":160,"wires":[["2b7c38bf.0fd5d8"]]},{"id":"cb38d2a3.10198","type":"server","name":"Home Assistant"}]

The result is a HA entity sensor.epex_spot_price that holds the current EPEX price as state and the upcoming prices as attributes. Values are in ct/kWh.

For convenience each hourly price is contained twice in the attributes:

  1. With key “Hour nn-mm” that contains the hour interval as starting hour (nn) and ending hour (mm) in 24h scheme.
  2. With key “Hour +nn” that contains the relative hour from now.

3 Likes

Hi Jorg,
I would like to use your “Node red” project.
I got your “flow” in the list of Node Red

“EPEX spot” do I still have to start it? I don’t no how it’s works in Node Red

Where can I find the sensor in Home assistant?

Thank you

When you click the test button you should see the current epex price under the sensor node epex_spot_price at the end of the flow. Does that work for you?

If yes, check the HA entities for sensor.epex_spot_price.

If it is not there check these steps:

  • Restart your flows in Node Red
  • Make sure you have installed the Node Red companion component in HA
  • The companion component and the corresponding node-red-contrib-home-assistant-websocket in Node Red have both been changed recently. Make sure both are updated to their latest versions.

Hi Jörg I think I got it working ?
I don’t see the values ​​in the graph yet. I’ll wait and see maybe I’ll see them tomorrow.
Thanks so far.
grt…

Yepp, give it an hour.
Might help others if you told us what solved your problem.

Hello,
Jörg indeed,
The problem is, I don’t know how HA and Node Red works yet

I won’t be able to say anything meaningful about it.

Try try try…
is my method

I want to thank you again

Hi Jpsy,
May I ask why you put everything in the attributes?
I’m trying to create a sensor for variable electricity myself and I have no clue what works well and what doesn’t. This is not a very typical thing for home assistant it appears…

I don’t. I put the current price in the state of the entity.

Only the future prices are stored in attributes. This is simply to avoid up to 48 separate entities.

And values in attributes are just as easy to access and to use for automations as values in the main state of the entity. The only difference is that the attributes are not written to the history database. This is actually perfect as these future values will be written to the db anyway once they become current values.

But you are free to expose the values all in separate entities if you want. The code should be easy to change to do that.

I’m also trying to achieve something similar. I’m using your nodered flow @Jpsy, and I’m trying to adapt it to my energy provider (which has an API), but i’m not quite sure what I need to change. I don’t have a lot of knowledge of json calls, I was hoping you could point me in the right direction?

The energyprovider I’m using has an API. They are using graphql, but i believe a json call would also be possible.
https://reversed.notion.site/Marktprijzen-API-89ce600a88ac4abe8c2ad89d3167a83e

These are not EPEX prices, these are the prices with the markup included.
This is the flow i’m using now to get the prices:

[{"id":"a71b9722a1965ef8","type":"inject","z":"f43bae2.c2a5b5","name":"inject","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":760,"wires":[["debe4b77c218eb3f"]]},{"id":"debe4b77c218eb3f","type":"graphql","z":"f43bae2.c2a5b5","name":"get marketprices","graphql":"a102b27f07ddf550","format":"handlebars","syntax":"mustache","template":"query MarketPrices {\n\tmarketPricesElectricity(startDate: \"2022-04-04\", endDate: \"2022-04-05\") {\n    till\n    from\n    marketPrice\n    priceIncludingMarkup\n\t}\n\tmarketPricesGas(startDate: \"2022-03-01\", endDate: \"2022-03-30\") {\n    from\n    till\n    marketPrice\n    priceIncludingMarkup\n  }\n}","showDebug":true,"x":220,"y":820,"wires":[["71eede6f4b0e8a5e"],[]]},{"id":"71eede6f4b0e8a5e","type":"debug","z":"f43bae2.c2a5b5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":470,"y":800,"wires":[]},{"id":"a102b27f07ddf550","type":"graphql-server","name":"frankenergie","endpoint":" https://graphcdn.frankenergie.nl/"}]

So I’m also trying to create a way to automatically change the date, but is there something like a variable I could pass into my graphql query?

Thanks so far for your flow, it’s helping me learn!

Hallo Jörg,

This is almost exactly what I’m looking for, however the prices I have are different.

I live in the Netherlands and pay hourly rates which are available the day before. like is these 2 sites.

https://www.epexspot.com/…0&data_mode=table&period=
or

in addition, I would like to add a standard amount which I have to pay as an extra on top of the market price.

with kind regards, and great admiration
Mr, Blikkie.

Your links are websites, not REST APIs.
You have to find a REST API to use the technique of my Node RED flow from above.