Control a water heater and ground source heat pump based on cheap hours of spot priced electricity

Hi all,

I’m about to implement a home automation system that will control the heating system of our house so that it will avoid the expensive hours when electricity costs the most.

I have a programming background but I’m new to OpenHab and would appreciate some guidance on the Right OpenHab Way to do things.

Let me first explain what I’m planning. I’m writing this quite verbosely so that this would benefit other community members in the future.

Background and context: heating system of our house and Nordpool day ahead spot prices

The electricity price follows the spot price of Nordpool. The price / kWh is different for each hour of the day and the prices for the next day are published on the previous evening. See Market data | Nord Pool

The prices have been jumping like crazy. The cheapest hours are like 0.5 c/kWh but the most expensive hours can be 70 c/kWh or even more.

Our house is heated with a ground source heat pump. It heats up water for the underfloor heating and it has an integrated 180 liter tank for hot water. The heat pump is Nibe F-1226 8kW. The heat pump has connectors for external control so it should be quite easy to control.

In addition to the 180 liter integrated water tank in the heat pump, we have a separate 300 liter water heater (Nibe Haato 300, 3kW). This heater has a mechanical thermostat which controls when the heater is on/off.

The plan for the external water heater

The spot prices for each hour of the day can be fetched with an API provided by Entso-E Transparency Platform. Anyone can create an user account to the platform and request API access to be granted, this is free of charge. See the API user guide if you want to learn more.

My plan for the water heater is quite simple.

  • Read the spot prices for next day from Entso-E

  • Analyze the spot prices and find the cheapest 4 hour slot from the day (e.g. 01:00-05:00). 4 hours will be enough to heat the water to the thermostat max temperature. The 300 liters should be easily enough for one day’s hot water consumption.

  • I will not tamper the water heater’s internal logic. I will simply cut the power from it when I don’t want it to be on. This will be done so that I will connect a relay to Raspberry Pi’s GPIO and this relay will control a contactor which is connected to the power cable of the water heater. This will be the same Raspberry that will run my OpenHab.

  • Word of caution at this point: the connections from the relay output onwards involve high voltage and only authorized electricians are allowed to make those connections. Do not touch them yourself. There is a risk of serious injury or death.

Proof of concept status at the moment

I have connected a simple buzzer to the GPIO of my Raspberry Pi. I installed the GPIO binding add-on and I was able to create a Buzzer Thing and Switch Item which toggles the buzzer on and off. Hooray!

The fact that I’m able to control the buzzer via Openhab GPIO binding also means that I will be able to control a relay. From the connections point of view they are exactly the same, the GPIO 3.3. volts output toggles the relay on/off just like it toggles the buzzer on/off.

I was also able to write a proof of concept script which reads the spot prices for next day from the Entso-E Transparency Platform.

The script looks like this:

// Entso-E security token.
val String token = "insert-your-own-token-here";

// @see https://transparency.entsoe.eu/content/static_content/Static%20content/web%20api/Guide.html#_areas
val String area = "10YFI-1--------U";

// @todo: investigate the timezone handling later.
val String start = now().plusDays(1).format(java.time.format.DateTimeFormatter.ofPattern("YYYYMMdd")) + "0000";
val String end = now().plusDays(1).format(java.time.format.DateTimeFormatter.ofPattern("YYYYMMdd")) + "2200";

// Make the API call to Ensto-E.
val String priceXml = sendHttpGetRequest("https://transparency.entsoe.eu/api?securityToken=" + token + "&documentType=A44&in_Domain=" + area + "&out_Domain=" + area + "&periodStart=" + start + "&periodEnd=" + end);

// Convert the response to JSON for easier handling.
val String priceJson = transform("XSLT", "xml2json.xsl", priceXml);

// Read the start time of the response.
val ZonedDateTime startUtc = ZonedDateTime.parse(transform("JSONPATH", "$['Publication_MarketDocument']['period.timeInterval']['start']", priceJson));

// Print hourly prices to log.
for (var i = 0; i < 24; i++) {
  // Read the price for this hour.
  var String price = transform("JSONPATH", "$['Publication_MarketDocument']['TimeSeries']['Period']['Point'][" + i + "]['price.amount']", priceJson);

  // Convert EUR/MWh to c/kWh and add 24% VAT.
  var Double priceWithVat = Double::parseDouble(price) * 1.24 / 10;
  
  // Print to log
  var ZonedDateTime dt = startUtc.plusHours(i);
  logInfo("entsoe", dt.toString + ": " + priceWithVat.toString + " c/kWh");
}

Output of the script:

22:26:13.284 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-10T22:00Z: 3.44968 c/kWh
22:26:13.305 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-10T23:00Z: 2.44528 c/kWh
22:26:13.329 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T00:00Z: 1.6938400000000002 c/kWh
22:26:13.350 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T01:00Z: 1.6901199999999998 c/kWh
22:26:13.372 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T02:00Z: 1.68764 c/kWh
22:26:13.393 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T03:00Z: 1.76824 c/kWh
22:26:13.414 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T04:00Z: 3.16324 c/kWh
22:26:13.438 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T05:00Z: 4.27676 c/kWh
22:26:13.460 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T06:00Z: 4.50368 c/kWh
22:26:13.480 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T07:00Z: 3.6679199999999996 c/kWh
22:26:13.501 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T08:00Z: 2.67096 c/kWh
22:26:13.523 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T09:00Z: 1.6752399999999998 c/kWh
22:26:13.547 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T10:00Z: 1.3714400000000002 c/kWh
22:26:13.568 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T11:00Z: 1.21148 c/kWh
22:26:13.589 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T12:00Z: 0.5245200000000001 c/kWh
22:26:13.611 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T13:00Z: 1.24992 c/kWh
22:26:13.632 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T14:00Z: 1.44832 c/kWh
22:26:13.656 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T15:00Z: 1.6479599999999999 c/kWh
22:26:13.677 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T16:00Z: 1.6467199999999997 c/kWh
22:26:13.698 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T17:00Z: 1.88976 c/kWh
22:26:13.722 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T18:00Z: 1.9716 c/kWh
22:26:13.745 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T19:00Z: 2.6498800000000005 c/kWh
22:26:13.770 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T20:00Z: 2.1575999999999995 c/kWh
22:26:13.792 [INFO ] [org.openhab.core.model.script.entsoe ] - 2022-06-11T21:00Z: 1.45452 c/kWh

A couple of notes about the script.

  • First of all, the proof-of-concept script only works after the day ahead prices have been published for the next day. That is, in the evening. If you want to read the prices for the current day, remove the plusDays(1).

  • Second of all, the timezone handling needs a bit of love. The script currently has an offset-by-one between Finland (EET / EEST) and Central Europe (CET / CEST).

  • The Entso-E API returns the prices in XML format. I first tried to use the XPath Transformation service to read the values from XML, but there is an issue. Despite the fact that the Entso-E XML declares a namespace, the XML elements are in the default namespace. This means that parsing the XML with XPath would turn out to be ugly as hell because I would need to use fully qualified namespaces in the XPath matching.

  • To keep the code readable, I’m first transforming the XML to JSON using witht the XSLT xml2json.xsl which is available here: Convert XML to JSON using XSLT – Bojan Bjelic

  • Credits for the inspiration of using this XML to JSON technique go to this thread: MythTV Upcoming Recordings Widget, with HTTP Bind, XML to JSON via XSLT and pagination display

Summa summarum, conclusions about the proof of concept status:

  • I’m able to read the spot prices from Entso-E API

  • I’m able to control a buzzer via GPIO

Request for guidance from the community

With all the long background from above, I’d appreciate some guidance from the OpenHab community.

Request for guidance 1: About Things, Channels, Items and Persistence

I’ve been reading the conceptual descriptions what Things, Channels and Items are, but they are quite abstract for an OpenHab newbie like me so I’m hesitating a bit.

How would you recommend me to model the hourly prices of one day? It’s essentially a key-value pair where the

  • key is the datetime of given hour, e.g. 2022-06-10T22:00Z
  • value is the spot price for that hour, e.g. 3.44968

I believe I will have one Thing called “Entso-E”

  • … but how would you recommed me to model the Channels and Items so that I would have places where I can update the 24 x hourly spot prices for each hour?
  • If I run the script twice, the expected result would be that the latter prices would overwrite the previous values for the exact same datetime keys, not creating duplicate values for the same hour.
  • If I run the script again tomorrow, the expected result is that I don’t overwrite the price of the 01:00-02:00 hour of the previous day.
  • Do you think InfluxDB or the default RRD4J is better for this persistence use case and why?
  • I obviously want to be able to plot the hourly prices for each day I have collected the data for, but the actual plotting should be straight forward based on what I have been reading about Grafana.

This is most probably very basic OpenHab stuff but I’m hesitating what is the Right Way to do this with OpenHab concepts so I would really appreciate some guidance on how to configure these different entities (channels, items) and their persistence.

Request for guidance 2. Decoupling the fetching of the hourly prices and determining the cheap hours for the water heater

The above script only fetches the spot prices and once I figure out the answers to the above things / channels / items question above with the help of the community, I can save them.

I definitely do not want to mix the “find the 4 cheap hours” to the script above. I want to de-couple it to a separate piece of code which gets executed after the spot prices have been fetched. The reason for this is that I will also have the other Thing (the heat pump) that will be using the spot prices.

The logic for determining the on/off times for the heat pump will be significantly more complex than finding the 4 cheap hours for the water heater because the heat pump controlling will need to take the weather forecast into account. But anyway, I do not want to fetch the spot prices again for the heat pump controlling, I want to use the same prices that have already been fetched once for the water heater.

So the question is: does the following sound logical way to decouple the logic in OpenHab:

  • Script 1: Fetches the spot prices and saves the values for each hour with Things / Items / Channels

  • Execute script 1 with cron once per day in the evening after the day ahead prices have been published

  • Script 2: Determines the 4 cheapest hours for the water heater for the next day. Save the results to some other Things / Items / Channels

  • Invoke script 2 either by cron slightly later than script 1 or if it’s possible, after script 1 has done it’s magic. Based on what I’ve been reading, an event based trigger would probably be more OpenHab way to invoke this script, would you agree?

  • Run a rule once per hour with cron at every full hour. If the hour changes from cheap to expensive or from expensive to cheap, toggle the relay switch on/off.

Cheers,
Markus

4 Likes

Think of “Things” as the components in the real world outside OH that are part or can be made part of your automation system. They can either be physical devices or some source of information (like your web service for energy prices). A “binding” makes them available to OH.

A “channel” is a way to access aspects of a Thing. As an example think of a multisensor that has various information available for you: temperature, open/closed contacts, humidity… For each aspect there will be a “channel”.

Items are where the data is available within OH. At runtime you will deal with Items. Each channel you want to access will be represented by an Item so you can read the data a Thing provides via the respective channel.
In addition to the items that give access to channels/Things you can freely define items you think you need to do whatever you want to do.

In essence: you will store your information in items.

The data in items can be persisted. There is a choice of persistence options for different use-cases. Very roughly:

  • MAP DB: Easiest choice for startup purposes where an item can be assigned the value it had before OH was shut down. There is only one data point for each item, no historical data.

  • RRD4J: a database that will NOT grow endlessly. Older data will be aggregated by the database itself (no admin intervention needed) so you loose precision for historical data. Can only store numerical data, so keep that in mind when you want to use it for startup data retrieval.

  • Other databases like InfluxDB, MariaDB etc.: You will have maintenance efforts for these databases, but basically no restrictions like with RRD4J or MAP-DB

Not putting the whole logic into one single script is a good approach. Especially as you want to use the fetched prices for different purposes. You may want to think about starting up the system as well and how to aquire the data needed until the next price retrieval in the evening. Persistence will help here, too.

would mean some kind of threshold definition, at least to me when reading your thoughts. That would be an additional item: prices below that threshold would be deemed “cheap”, prices above as “expensive”. That would be a way to decide if heating is allowed at the current time or not. Making that threshold item available to the users in a GUI gives them a way to intervene in certain situations. It can even be calculated from the historical price data…

Many thanks @stefan.oh ! These clarified quite a bit!

Ok, so I need to create a Thing called “Entso API”.

To create this, I need to select a Binding.

  • There is a HTTP Binding which I was first looking at but I was not able to figure out how I would be able to apply the needed logic to prepare the start and end arguments that are needed as URL arguments.
  • As I couldn’t figure out how to make the API request with the HTTP Binding, I wrote that DSL script that I shared above which uses sendHttpGetRequest to call the API.
  • Do I need to write a custom Binding which does the same thing that my DSL script, or is there already some generic dummy Binding which doesn’t do anything (and it doesn’t if the script does all the magic)?

I guess this means that for the Ensto API Thing, I would need to create 1+24 Channels and 1+24 Items.

1 Channel and Item for the date (without time) for which the hourly prices are for
24 Channels and Items for the hourly prices

Would this sound a sensible approach?

Cheers,
Markus

No, not necessarily. With your script you already found a way to aquire the data you are interested in. If it fits your needs, leave it at that for the time being if you want.

You will find out with time there is more than one way to achieve something. It is your decision if you want to learn in the beginning all the possible ways to go ahead and decide the best way forward or if you are satisfied with a working solution and maybe revisit the issue at a later point in time to improve what you got so far. Making things more robust, reduce coding with a different approach, maybe open new possibilities with a different approach…

Channels come predefined with bindings. In a way they are the glue between the items you use in OH itself (by linking an item to a channel) and the capabilities a certain Thing offers.

In your case you already have the data, so you need to store it for later use in rules. I don’t know if Rules-DSL offers arrays, in other programming languages that would be the choice for storing the same data type multiple times as needed here. Defining 24 items (one for each hour of the day) is crude, but maybe that’s what you need to do.
If Rules-DSL restricts you, be aware there are other scripting languages available for OH nowadays.

Yeah, there’s always many ways to achieve the goal, but some of them are usually more elegant than the others. I would not like to twist the platform to a tango it’s not intended.

Creating those 1+24 Items like “Spot price 00:00”, “Spot price 01:00”, “Spot price 02:00” and so on sounds indeed cumbersome. Another approach would be that I would only have one String Item where I would store the hourly prices as an serialized array in JSON format. This way I would only need one Item for the hourly prices.

Then that second script would come into play. It would run later in the evening after the spot prices for the next day have been fetched. It would read all the individual “Spot price” Items or the serialized String Item (that contains the JSON array) and determine the start time when the power for the water heater would be turned on and off. This value would then be stored into an Item “Water heater start time” and Item “Water heater stop time”.

Then, I would have a rule running on every full hour.

  • If the Water Heater GPIO Switch Item is currently on, it would check if we have reached the “Water heater stop time”. If yes, it would turn the Water heater GPIO Switch Item off.
  • If the Water Heater GPIO Switch Item is currently off, it would check if we have reached the “Water heater start time”. If yes, it would turn the Water heater GPIO Switch Item on.

The serialized JSON array sound more elegant to me, but I need to check how difficult the plotting would be.

I’d like to plot something similar like this:

  • MLP is a Finnish abbreviation for the heat pump
  • LVV is a Finnish abbreviation for the water heater

The green symbols indicate the hours of the day when they are allowed to be on. And the red symbols indicate when they are not allowed to be on, respectively.

The thin blue line is the price of the electricity on a given hour, and the bar chart tells the cumulative load that is allowed to be “on” at the same time.

Thanks for your advice again! I’ll try the multiple items vs. one JSON item approach and then start to bang my head against the plotting wall!

I have a gut feeling that more traditional database approach might turn out to be easier but I would really like to get this done with OpenHab because the framework provides so nice capabilities that I really would not like to build from scratch myself…

Cheers,
Markus

Note to self, this looks promising: OpenWeather. How to store future time series data

After sleeping one night, things start to be a bit more clear in my mind. This is what I’m probably going to do.

  1. The script will fetch the spot prices for the next day in the evening. Let this script be called “fetch-spot-prices”.

  2. The “fetch-spot-prices” script will use the InfluxDB HTTP API to write the prices with future timestamps as discussed in the thread Iinked in my previous comment. With this approach, the prices will be a true time series, which makes it easy to plot (among other benefits).

  3. Once the “fetch-spot-prices” has been executed, a second script will get invoked. Let this script be called “determine-allowed-hours-for-the-water-heater”. This script will read the spot prices from the InfluxDB and determine the hours when the water heater should be “on” and when it should be “off”. The results will be written to another time series with future timestamps.

  4. I will create a Water Heater Thing, with a Channel that connects the Thing to a Water Heater Item, which is a Switch Item. The Thing uses the GPIO Binding, which controls the relay.

  5. I will have a rule that runs every full hour.

  • This rule will check if the Water Heater is already “on”. If yes, and the “allowed-hours-for-the-water-heater” time series says that this hour is not allowed, it will turn the Water Heater Switch Item to “off”.
  • Correspondingly, if the Water Heater is “off” and the “allowed-hours-for-the-water-heater” time series says that this hour is allowed, it will turn the Water Heater Switch Item to “on”.
  1. I will make the Water Heater Switch Item to persist its state as yet another time series, which means that
  • I can plot the hourly spot prices
  • I can plot the hours that the “determine-allowed-hours-for-the-water-heater” script was saying which ones are allowed
  • I can plot the hours when the water heater was actually on / off, because the Water Heater Switch Item can be manually be toggled on/off.

Cheers,
Markus

2 Likes

Beside the program structure:
After taking a look at your graph, have you considered, taking (outside) temperature into account? Electricity might be cheapest in the middle of the night, but it’s also the coldest hours of the day, so your heat pump runs least efficient. So overall cost could be less, running it in the afternoon/evening with slightly higher electricity costs but much better efficiency.
By using weather forecast, you could also calculate this in advance.

@AlexW the ground source heat pump will be the next phase of the project after I’m done with the water heater. And yes, the outside temperature definitely needs to be taken into account in that.

This is how I see things at this point of time…

Hot water
The hot water is first heated by the heat pump and is in the 180 liter integrated water tank. This is done very efficiently with the ground source heat pump. However, the 180 liters is not enough for our house because we have a bathtub. For that reason, we have that external water heater (300 liters) that is “behind” or “after” the 180 liters integrated water tank. That is heated simply with electricity.

I want to first control the 300 liter water heater so that I avoid a situation where I would cut off the heat pump during expensive hours but then the 300 liter water heater would be turned on as a consequence. So this is going to be phase 1 of the project, and that is conceptually quite straight forward as described above; I only need to find the 4h period which is cheapest and the water heater will heat the water to the thermostat max temperature.

Controlling the ground source heat pump
Once the 300 liter water heater is sorted out, it’s time to focus on the heat pump. In Finland the outside temperature during the coldest winter can be -25 Celsius or even colder. So during this kind of a time, I can probably only disallow the single most expensive hour of the day. Or it might be that I’ll be able to disallow 2 or 3 most expensive hours as long as they are not adjacent hours. Time will tell next winter.

However, during the summer months, the house does not heating at all, it’s actually the opposite that we need to cool the house with air source heat pumps. The only thing that the ground source heat pump is doing during the summer months is heating the hot water (which then goes to the 300 liter water heater before it is delivered to the taps and showers). During these kind of outdoor temperatures I can easily have similar logic that I have with the water heater, where I basically find some cheap hours and allow the compressor to run during those hours.

I need to experiment this a bit but my work hypothesis is that the ground source heat pump will be something like this:

  • when it’s -20 Celsius, I need to allow the ground source heat pump to run 22 hours of the day (I can block 2 most expensive hours)
  • when it’s 0 Celsius, I need to allow the the ground source heat pump to run 10 hours of the day
  • when it’s +20 Celsius, I only need to allow the ground source heat pump to run 2 hours of the day

Other temperatures would obviously be somewhere in between these thresholds.

Charging the electric car
A third factor will be the charging of an electric car which hopefully will be eventually delivered this fall (I ordered it last October). I haven’t done the math yet, but then I need to see which of the three big loads (car charging, heat pump, water heater) can be on at the same time. The car charging will be 11kW, ground source heat pump is 8kW and water heater 3kW. Our main fuse is 3x35A.

I’m also trying to achive some similar stuff like you do. I’m having a Nibe geothermal heatpump that I prevent electrical boiler from running if powerconsumption is high.

For this winter I’m trying to add on a economic state for all my high consumptions thta is Nibe heatpump, and Easee car charger, and two pools, all this is firts of all gonna fit on 3x25A main fuses, and primarly also run while spotprice is low.

I did steal your example and did get spotprices from API and store all spotprices in a string item containing the whole Json result from xslt to json transforming.

However your printout for eventlog is off by 2 hours for me, first hour is 23 and last hour is 21 meaning 1 hour is missing. And the I need to or at leats like to convert UTC time to loacaltime (Sweden).

If you setup every hot as a Number Item and join them to a minimugroup, you will allways habe the hours sorted from cheapest hour to most expensive hour, purly baed on cost rate.

PS Eassee charging box is highly recommended, worsk like a charm with the built in simcard, my box is controlled via Openhab.

I fill up wit a little progress, stored start date time into DateTime item as below. Time stores nicely and sitemaps convert it to local time, now I just need to figure out how to make comparsions with UTC time to local Time or just store same value as localtime into a item.

SpotpriceStartDateTimeUTC.postUpdate(transform("JSONPATH", "$['Publication_MarketDocument']['period.timeInterval']['start']", priceJson));

I decided to refactor my scripts to Javascript so that I can reuse the code in different places. I hope to be able to share my rules and where I am either today or tomorrow.

What’s working so far, but I’m currently working to refactor the code:

  1. Fetch spot prices from Entso-E API and store them as future-timestampped points to InfluxDB

  2. Read the stored spot prices from InfluxDB and calculate the on/off hours for the waterheater based on that. These on/off hours are stored as different future-timestampped points to InfluxDB

  3. An hourly rule that runs every full hour that checks if the waterheater should be turned on/off and controls the GPIO output. I currently have a simple buzzer connected to the GPIO but I should get my father-in-law (an electrician) to do the relay & contactor connections in the next week or so.

1 Like

Alright, here we go.

1. READ THIS FIRST
I’m sharing the solution and code here so that other community members can use this as an insipration and modify it for their needs. I’m intentionally not publishing this via github because I do not have possibility to provide long term support based on other people’s feature requests. I’m responding to the questions in this thread for the time being and clarify these instructions based on the feedback.

I have used quite a lot of time for writing these instructions, so I kindly ask you to carefully re-read this comment marked as “solution” before asking for support. If you feel that you have followed every step and still haven’t figured it out, please increase the log level to see the debug level logs. If you’re still not able to figure out what is boiling down, ask for support in this thread, I’ll be glad to help.

Disclaimer
This solution is provided as an inspiration for other community members. I disclaim all warranties and responsibilities if you use this solution. In no event shall I be liable for any direct or indirect damages resulting of use this solution. High voltage connections must always be designed and performed by an authorized electrician.

License
All code below is provided without any warranty and published under the ISC License, see the code. Please attribute Markus Sipilä, https://www.linkedin.com/in/markussipila/, if you use the code.

2. WHAT ARE WE TRYING TO ACHIEVE
Spot priced electricity contract means that the price of 1 kWh is different on every hour of the day. The prices are determined by Nordpool and the day-ahead prices for the next day are published at 12.45 CET / CEST.

Because there is a significant difference in the spot prices during the day, the point is to schedule as much of the power consumption to the cheapest hours and try to minimize the consumption during the most expensive hours.

The screenshot below illustrates the result of this optimization. The blue bars represent the consumption of our house (in kWh), whereas the green line illustrate the spot price of that hour (c / kWh). As you can see, our consumption peaks systematically when the prices are at lowest.

This is achieved by scheduling the usage of electricity. Two biggest energy consumers in our house are

  • Heating of the house. Our house has a Nibe F-1226 ground source heat pump that warms the water for an underfloor heating system.
  • Heating the hot tap water. In addition to the integrated 180 liter water tank in the heat pump, we also have a separate 300 liter waterheater that heats the water.

Nibe heat pumps have connections for external controls, which are designed for this kind of controlling. This includes only 3.3V low voltage connections, as described later in this tutorial. I use a relay to tell the heat pump when the compressor is blocked and when it is allowed to run normally. There are two wires connected to the heat pump. All this relay does is that it connects these two wires together, which will block the heat pump compressor.

The waterheater does not have external controls. Luckily the waterheater is a super simple device, so I will simply cut the power supply from it when I don’t want it to be on. When the power supply is on, the device will simply use its own thermostat to determine when it will actually start and stop. The water heater uses 3 x 230V power supply and consumes way more power than the relay board could handle, so a small modification was made to the electrical cabinet by an authorized electrician. The relay controls a contactor, which allows or blocks the power supply to the waterheater. Details are explained below.

3. CONCEPTUAL DESCRIPTION

My Raspberry Pi is connected to a relay board so that I can control the relays with GPIO. I use Waveshare relay board with 8 relays: RPi Relay Board (B) - Waveshare Wiki This relay board can be mounted to a DIN-rail so it was very easy to attach to the box.

Side note: Even though I use the GPIO controlled relay board, the same thing can be achieved also by using smart relays, see for example https://www.shelly.cloud


Raspberry Pi and connected to a relay board

Waterheater
One of the GPIO controlled relays control the contactor for the waterheater. The contactor allows / cuts the power input for the waterheater. My openHab logic allows the power supply during the cheapest hours of the day and blocks the power supply on other hours. The contactor controls 3 phases of 230V voltage. I repeat my warning: The 230 voltage connections must only be done by an authorized electrician. High voltage can kill you.

There are a couple of ways to do the connections between the Raspberry relay board (or smart relay) and the contactor. Leave the detailed design and implementation of the 230V connections to an authorized electrician, the illustrations below are just conceptual examples.

Water heater hardware connections: option 1


GPIO controlled relay controls a contactor. The contactor is located in the electrical cabinet and it allows / cuts the power supply for the water heater. The downside of this connection option is that the connection between the Raspberry relay board and the contactor is 230 V. You absolutely must have a hard cover box and ensure that there is no voltage in these cables every time before you open the hard cover box of your Raspberry and relay board.

I originally had this connection option so I printed a warning sticker to the cover box with a reminder which fuse I need to turn off before opening the cover, see picture below.


Hard cover box with a warning sticker.

Water heater: option 2
A safer connection option is to have a second relay in your electrical cabinet so that the connection from the GPIO controlled relay board to this second relay is only 24V. This way you don’t need to bring 230V cables to the box of your Raspberry and relay board. My setup was updated to this connection option (again by an authorized electrician) for safety reasons.

Waterheater contactor in the electrical cabinet


The black component in the picture above is the contactor, which allows / cuts the power supply for the water heater. Installed by an authorized electrician.

Ground source heat pump
My ground source heat pump (Nibe F1226-8) has two external inputs. When pins 3-4 are connected, that means that AUX1 input is enabled. When pins 5-6 are connected, that means that AUX2 is enabled. The meaning of AUX1 and AUX2 inputs can be configured in the menu of the Nibe heat pump. There is for example an option “block comperssor” and “block hot water”, see the “Nibe installation manual”, it’s only described there but not in the “user guide” manual. This is a low voltage (3.3V) connection so you can test the Nibe side of this simply by using a short copper wire and connect pins 3 and 4 for AUX1 and pins 5-6 for AUX2.


My GPIO controlled relay connects / disconnects pins 3-4 as illustrated below. I have configured AUX1 to mean that the compressor is not allowed to run. This way my openHab logic can choose when the house heating is allowed and when not.

Failsafe considerations
Consider which way you want the Raspberry relay to work. In my case the connections have been made so that when the relay pulls, the power supply for the water heater is blocked. If Raspberry is completely offline and the relay does not pull, power supply is allowed.

Winters can be “quite” cold here in Finland so I need a quick way to get the waterheater and ground source heat pump to behave as if the Raspberry did not exist. This failsafe needs to be so simple that other family members can control it if I’m not at home. For this reason, the electrician installed these switches to the electrical cabinet. “LVV” is a Finnish abbreviation for “waterheater”. The first switch can be used to decide if Raspberry can control the contactor or if the power supply is always on as if the Raspberry did not exist.


Failsafe switches in the electrical cabinet

4. OPENHAB THINGS, BINDINGS, CHANNELS AND ITEMS
Warning: Do not have the physical cabling connected to your relay board when you are experimenting and building the openHab solution. The relays make a loud click when their state changes and there is also a green led indicating when the relay pulls so you can easily notice if your on/off state changes work.

If you’re new to openHab, you might want to read this conceptual background first: Concepts | openHAB

Prerequisites:

  • Install openHab GPIO binding and pigpio-remote, see GPIO - Bindings | openHAB
  • GPIO Binding itself can be installed at Settings - Bindings like any other openHab Binding.

Create an openHab Thing called the Waterheater and use GPIO Binding

Give a unique name for the Thing and remember to set the Network Address as ::1 like mentioned in the GPIO binding documentation linked above.

Create a Channel, configure it to use GPIO Digital Output and configure the GPIO Pin number that matches the relay that you will be using. The Waveshare relay board that I use have its GPIO pin numbers documented at RPi Relay Board (B) - Waveshare Wiki. The GPIO Pin 5 matches to the first relay of the board.

Finally, add a link to an Item to this Channel. Pay attention to the name WaterheaterPower, it will be used in the Rules later on. The type of this Item is Switch as illustrated in the picture below.

You should now be able to toggle the relay on and off using the switch Item “WaterHeaterPower” you just created. You can also find your newly created Item in the Items menu.

A hint at this point. If you installed openHabian SD image, you can use Frontail log viewer at port 9001 of your Raspberry using your web browser. You can see log entries when the state of your Item and Thing change.

Because I also want to control the Nibe ground source heat pump, I repeated the steps above and

  • created a Thing called HeatPump
  • created a Channel called Compressor, configured it to use GPIO output 21
  • linked the Channel to a new Item called “HeatPumpCompressor”, which is of type Switch. Also pay attention to the name of this Item, it is used in the Rules below.

Testing of relay state changes and introduction to openHab Rules and Persistence
You should now be able to change the state of the relays using the openHab Items called “WaterHeaterPower” and “HeatPumpCompressor”. If you can hear the loud clicks and see the green leds turn on/off, everything works as expected.

This also means that everything is set for creating the openHab Rules that automatically change the states of these Items. If you’re new to openHab, read this first: Rules - Introduction | openHAB

This solution uses Influx2 database to store the data. openHabian OS comes with influx database server pre-installed but you can run it on any server. I run InfluxDB on my NAS server so that the data is physically stored in a more reliable place than the SD card of the Raspberry.

Once your influx database is up and running and you can log in to the Influx Data Explorer

  • install the openHab InfluxDB Persistence addon from Settings - Other add-ons.
  • enable it at Settings - Persistence.

State change of the Items “WaterHeaterPower” and “HeatPumpCompressor” should now be visible in your Influx Data Explorer, like in the picture below.

If you’re new to openHab, read more about data persistence here: Persistence | openHAB

In the next chapters:

  • we will fetch next days spot prices from Entso-E API and store them to an Influx2 database
  • calculate a control value (1 or 0) for every hour of the next day to determine if the Item should be on/off during that hour. We save these control values to the Influx2 database.
  • create Rules that toggle the Items on / off based on the control value for that hour

5. FETCHING THE NORDPOOL DAY-AHEAD SPOT PRICES FROM ENTSOE-E API
This chapter describes how the Nordpool day-ahead spot prices can be fetched from Entso-E API and stored to an Influx2 database.

Pre-requisites

  • The JSScripting addon must be installed. The rules are written as ECMAScript 262 Edition 11. Note the version 11.
  • XSLT Transformation addon must be installed.
  • JSONPath Transformation addon must be installed.
  • You must have your influxDB2 up and running.
  • The InfluxDB Persistence addon must be installed, for normal persistence reasons like described above. Writing the future-timestamped measurement points is done directly via the InfluxDB HTTP API, though.
  • xml2json.xsl must be downloaded from Convert XML to JSON using XSLT – Bojan Bjelic and it must be saved to /etc/openhab/transform/xml2json.xsl . Ensure that the openhab user has read access to this file.
  • Copy the javascript files from below and save them to /etc/openhab/automation/js/node_modules/kolapuuntie (change the name of the directory if you want, but remember to reflect the change in your code)

Create a Rule “FetchSpotPrices”

  • Copy the code below as the Script Action (ECMAScript 262 Edition 11).
  • The script invokes entsoe.js, which fetches the spot prices from Entso-E API and writes the spot prices to Influx2 database as “spot_price” measurement points with timestamps in the future via Influx HTTP API v2
  • Entso-E publishes the spot prices for next day in the afternoon. date-helper.js module has logic that if the script is executed at 14:00 or later, it will fetch tomorrow’s prices. If the rule is executed before that, it will fetch current day’s prices. The script can be executed multiple times, possible previous database points are overwritten.
  • You need to request a personal API access token from Entso-E and update it to this script action.
  • If you’re interested in some other bidding zone than Finland, change the bidding zone code to this script action.
  • If the VAT tax rate is not 24% in your country, change that as well.
  • Modify your influxDB connection parameters directly to the influx.js. After you have made changes to the js files, you need to re-save the Script Action (the snippet below) to make sure openHab re-reads the js file.
  • Run the rule manually and check from your influxDB data explorer that you can see the spot prices for today / tomorrow (depending on the time of the day when you executed the script). If you run the rule in the afternoon or evening, the script will fetch tomorrow’s spot prices. Remember to choose a date range in Influx data explorer which includes the day you just fetched the prices for.
  • If you’re not able to see the “spot_price” measurement data in your influxDB data explorer, increase the log level to see the debug level logs
  • After you are able to fetch the spot prices, schedule the Rule to run in the afternoon. You might want to schedule the rule to run for example at 14.00 and 15.00 so that if the first attempt is not able to fetch the prices, the second hopefully works.

Script action for fetching the spot prices:

dh = require('kolapuuntie/date-helper.js');
entsoe = require('kolapuuntie/entsoe.js');
influx = require('kolapuuntie/influx.js');

// Entso-E bidding zone.
zone = '10YFI-1--------U';

// Entso-E API access token.
token = 'insert-your-access-token-here';

// Multiplier for VAT
tax = 1.24;

// Get date range in the correct format for Entso-E API
start = dh.getEntsoStart();
end = dh.getEntsoEnd();

// Read spot prices and write them to the database.
points = entsoe.getSpotPrices(start, end, zone, token, tax);
influx.writePoints('spot_price', points);

The below screenshot is from the InfluxDB data explorer and it shows the spot prices in Finland on 2022-12-22.

A couple of gotchas

  • Entsoe-E API returns the spot prices in XML format. For easier handling, we transform the XML to JSON with the xml2json.xslt transformation as mentioned above.
  • Entso-E API seems to take input parameters in CET/CEST but responds in UTC.

6. CONTROLLING THE WATERHEATER
Now that you have the spot prices available in your influx database, you want to

  • Find the cheapest hours for the waterheater
  • Toggle the waterheater on/off when needed

Determining the cheapest hours for the waterheater
This script action will find the cheapest 4-hour window and write “waterheater_control” points to the InfluxDB. Value 1 means that the waterheater should be on and 0 means that it should be off. There is a point for every hour, either 1 or 0.

  • Note that the name of the influx “measurement” is “waterheater_control” and not “WaterHeaterPower”. The “WaterHeaterPower” is the actual time when the openHab Item called WaterHeaterPower has been on / off. The measurement “waterheater_control” are the control points in the future when the water heater should be automatically toggled on / off.
  • In the script action below, we will find the cheapest 4 hour window. Change this parameter to a value of your choice, but use a value high enough so that you waterheater can reach the thermostate max temperature to avoid legionella bacteria.
  • This Script Action can either be added as a second Action in the “FetchSpotPrices” Rule or you can create a Rule of its own for this (as long as you run this rule AFTER the spot prices are saved to your influxDB).
  • date-helper.js determines the start and stop midnights based on when the script is executed. Same today vs. tomorrow logic applies that I used with Entso API (cutoff time is at 14.00).
  • Run the Rule manually and check from the InfluxDB data explorer that you are able to see the “waterheater_control” measurement points. If you’re not able to see them, increase your log level to DEBUG and check the logs.

Script action for determining the on/off hours for the waterheater:

dh = require('kolapuuntie/date-helper.js');
wh = require('kolapuuntie/waterheater.js');
influx = require('kolapuuntie/influx.js');

start = dh.getMidnight('start');
stop = dh.getMidnight('stop');

// Determine cheap hours and write control values to the database
hours = 4;
points = wh.determineHours(start, stop, hours);
influx.writePoints('waterheater_control', points);

Hourly script to toggle the waterheater on/off based on the control points

  • You now have the “waterheater_control” points in your influxDB for every hour, either 1 or 0.
  • Create a rule “WaterheaterPower” that runs on every full hour and executes the script action below.
  • This script action will read the currently ongoing hour’s “waterheater_control” point from the InfluxDB
  • If the “WaterHeaterPower” Item is currently ON and the “waterheater_control” point has value 0 for the hour that has just started, the WaterHeaterPower item will be set to OFF. And vice versa, if the WaterHeaterPower needs to be set to ON.
dh = require('kolapuuntie/date-helper.js');
wh = require('kolapuuntie/waterheater.js');
influx = require('kolapuuntie/influx.js');

start = dh.getCurrentHour();
control = influx.getCurrentControl('waterheater_control', start);

waterheater = items.getItem("WaterheaterPower");

// Check if the waterheater is off and should be turned on.
if (waterheater.state == 'OFF' && control == 1) {
  waterheater.sendCommand('ON');
  console.log('Waterheater ON.');
}
// Check if the waterheater is on and should be turned off.
else if (waterheater.state == 'ON' && control == 0) {
  waterheater.sendCommand('OFF');
  console.log('Waterheater OFF.');
}
else {
  console.log('Waterheater: No state change needed.');
}

7. CONTROLLING THE GROND SOURCE HEAT PUMP
The logic for the waterheater above was quite simple because all we wanted to achieve is to find a window of N cheap hours. However, the heating of the house is a bit more complex because it depends on outside temperature.

  • The logic below works so that we first determine the number of needed heating hours based on a weather forecast. When it’s -20 celsius or colder, 22 hours will be required (we can avoid 2 most expensive hours). When it’s +20 celsius or hotter, 2 hours will be required for heating the hot water. Every house is different, so modify this heat curve directly in the js-code based on your experiments.
  • The logic for the waterheater was to find a cheapest N-hour window to ensure that the waterheater can reach the max temperature of its internal thermostat. The heat pump, on the other hand, can run for an hour, then be blocked for an expensive hour and then continue again.
  • In the context of heating the house, we want to avoid a situation where the heat pump is allowed to be on for say 12 hours in a row and then blocked for the next 12 hours, because the house might cool down too much during the 12 blocked hours. To avoid this, we can split the day to a desired amount of “slices”, for example 2 x 12 hour slices or 3 x 8 hour slices. For every slice, we can define a minimum percentage of the “on” hours.
  • Example: Let’s say 10h of heating is required and the day is split into 2 x 12 hour slices. We can define that at least 10% of the heating hours must be allocated to each slice. In this case both slices would have at least 0.1 x 10h = 1 hour of heating.
  • The slices and percentages are configurable in the script action.

Fetching weather forecast from the Finnish Meteorology Institute’s API and saving them to the database

  • Rule “FetchWeatherForecast” has the following script action.
  • This action invokes fmi.js which fetches the weather forecast from FMI’s API and writes the forecasted temperatures to Influx2 database as “fmi_forecast_temperature” measurement points with timestamps in the future.
  • The script can be executed multiple times a day, possible previous database points are overwritten.

Script action:

fmi = require('kolapuuntie/fmi.js');
influx = require('kolapuuntie/influx.js');

// Place recognized by Finnish Meteorology Institute's API.
place = 'veini';

// Read weather forecast and write them to the database.
points = fmi.getForecast(place);
influx.writePoints('fmi_forecast_temperature', points);

Determining the cheapest hours for the ground source heat pump

  • This Script Action can either be added as a third Action in the “FetchSpotPrices” Rule or you can create a Rule of its own for this (as long as you run this rule AFTER the spot prices are saved to your influxDB and the weather forecast has been fetched).
  • This script action invokes nibe.js which will first read the “fmi_forecast_temperature” measurement points from the Influx database and determine the number of needed heating hours.
  • Once the number of needed heating hours has been calculated, it determines the allowed / blocked hours for the heat pump and saves them as “nibe_control” measurement points to the InfluxDB.
  • The script action below splits the day to 3x8 slices and each slice must have 10% of the heating hours.
dh = require('kolapuuntie/date-helper.js');
nibe = require('kolapuuntie/nibe.js');
influx = require('kolapuuntie/influx.js');

start = dh.getMidnight('start');
stop = dh.getMidnight('stop');

t = nibe.getForecastTemp(start, stop);
n = nibe.calculateNumberOfHours(t);
slices = 3;
min_heating = 0.1;
points = nibe.determineHours(start, stop, n, slices, min_heating);
influx.writePoints('nibe_control', points);

Hourly script to toggle the ground source heat pump on/off based on the control points

  • Create a rule that runs on every full hour and executes the script action below.
  • This script action will read the currently ongoing hour’s “nibe_control” point from the InfluxDB
  • If the HeatPumpCompressor item is currently ON and the “nibe_control” point has value 0 for the hour that has just started, the HeatPumpCompressor will be set to OFF. And vice versa, if the heat pump needs to be set to ON.
dh = require('kolapuuntie/date-helper.js');
nibe = require('kolapuuntie/nibe.js');
influx = require('kolapuuntie/influx.js');

start = dh.getCurrentHour();
control = influx.getCurrentControl('nibe_control', start);
compressor = items.getItem("HeatPumpCompressor");

if (compressor.state == "ON" && control == 0) {
  compressor.sendCommand('OFF'); 
}
else if (compressor.state == "OFF" && control == 1) {
  compressor.sendCommand('ON');
}
else {
  console.log('Heat pump compressor: No state change needed.');
}

8. HELPER MODULES USED IN THE SCRIPT ACTIONS ABOVE
All code below is provided without any warranty and published under the ISC License, see the code. Please attribute Markus Sipilä, https://www.linkedin.com/in/markussipila/, if you use the code.

These files are placed at /etc/openhab/automation/js/node_modules/kolapuuntie. Change the name of yor module as you see best and modify the paths accordingly in the code.

Remove the .txt file extension which was required to upload the files here.
influx.js.txt (7.2 KB)
waterheater.js.txt (3.2 KB)
nibe.js.txt (6.6 KB)
fmi.js.txt (3.0 KB)
entsoe.js.txt (3.7 KB)
date-helper.js.txt (3.2 KB)

Update the influx.js with connection parameters to your InfluxDB2 instance.

Make sure that the linux file permissions are defined so that the openhab user is able to read them.

9. NOTES ON SOFTWARE COMPONENTS

  • I’m running openHab 3 on a Raspberry Pi 4, with Openhabian 32-bit OS
  • I’m running InfluxDB2 on my NAS, but you could run it also in the Raspberry. If you run InfluxDB on your Raspberry, be aware that the SD card will wear out at some point because of many write operations and will eventually get corrupted. Search for the community posts on this topic.
  • I’m running Grafana on the same Raspberry Pi that runs openHab. It comes pre-installed with openHabian SD-image.
  • I use Tailscale VPN for remote access so that I can use the browser of mobile phone even when I’m not at home. Tailscale client comes pre-installed with openHabian SD-image but it’s very straight forward to install if you don’t use openHabian OS. Never ever expose your openHab directly to the public internet. Read more on options for secure remote access: Securing Communication and Access | openHAB

10. USER INTERFACES
You are of course welcome to build your user interfaces as you like, but here some screenshots of my UI as an inspiration. If you’re new to openHab, you can read more about Pages at Pages - Introduction | openHAB

Overview page
Screenshot of my overview page is illustrated below. Translations of the Finnish UI labels:

  • Olohuone = Living room. Displays the room temperature. The temperature measurements are not in the scope of this tutorial.
  • Sisäilman kosteus = Humidity. Displays the humidity, not in scope of this tutorial. I use the Vallox binding to get the measurement from our air conditioning unit.
  • Sähkön hinta nyt = Spot price now. I have created an Item with the exact same name “spot_price” that is used by the script that fetches the spot prices from Entso-E API. I have an hourly script that updates the Item so that I can render the Item in the UI. See the script action below the image.
  • Ulkolämpötila = Outside temperature. Taken from the Vallox air conditioning unit. Not in the scope of this tutorial.
  • Custom widget that displays the spot prices of today. Built with openHab Charts functionality.
  • I have two items that control the rules. Kotona = At home and Automaattiohjaus = Automatic control. The “At home” switch controls the air conditioning unit (among other things) and “Automatic control” is used in the hourly scripts for the waterheater and ground source heat pump. If the Automatic control is ON, the hourly scripts are toggling the waterheater / heat pump on / off based on the control points. If the automation is “off”, I can manually control them using the switches below.
  • Below these two, I have switches for the waterheater, heat pump, air conditioning unit and other things.

Script action for an hourly rule that updates the spot_price Item so that I can render it in the UI:

dh = require('kolapuuntie/date-helper.js');
influx = require('kolapuuntie/influx.js');

// Update the spot price Item with the price of the current hour
now = dh.getCurrentHour();
current_price = influx.getCurrentControl('spot_price', now);
spot_price = items.getItem('spot_price');
spot_price.postUpdate(current_price);

Spot prices and control values with Grafana
I originally built a visualization for spot prices vs. control values with Grafana as illustrated in the screenshot below. If you use openhabian OS, grafana is pre-installed for you, see openHABian | openHAB

The Grafana dashboard is then embedded to an openHab “Layout” page as an WebFrame widget. I have the forecasted temperature for today, spot prices for today, calculated control points for the waterheater and for ground source heat pump and the spot prices as a table. Below that, there is the average spot price for today. The second tab of the openHab “Tabbed Page” provides the same data for tomorrow.

To get the URL that you can embedd to the WebFrame, Grafana allows you to “share” a dashboard or panel like this:

Note that the time range is not “locked”. You can re-use the same dashboard for “today” and “tomorrow” or any other time range by appending the desired relative date range in the Grafana URL, for example like this: http://192.168.1.33:3000/d/27zjm2R4z/tuntihinnat-ja-ohjaukset?from=now/d&to=now/d&orgId=1&kiosk

Flux-queries for the graphs:
image
Query for the spot prices:

from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
      r._measurement == "spot_price" and
      r._field == "value"
      )
  |> map(fn: (r) => ({ _value:r._value, _time:r._time, _field:"Sähkön tuntihinta (c/kWh)" }))

And for the control value:

from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
      r._measurement == "nibe_control" and 
      r._field == "value"
      )
  |> map(fn: (r) => ({ _value:r._value, _time:r._time, _field:"Maalämpöpumpun kompressori" }))

Average price of the day (change the visualization as “Stat”)
image

from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
      r._measurement == "spot_price" and 
      r._field == "value"
      )
  |>mean()

To render the spot prices as a table, change the visualization as “Table” in Grafana.

Spot prices and control values as openHab Charts Page
I recently migrated from the Grafana based approach to an openHab Charts Page solution so that I can more flexibly navigate between days. My current page looks like this. The gray Step Line / Area represents the spot prices (and the dotted gray line the daily average price), the red Step Line / Area the heating control values and the blue Step Line / Area the waterheater control values. Additionally I’m rendering the forecasted temperature and its average.

Read more about Chart Pages: Chart Pages | openHAB The openHab Charts are essentially Apache ECharts. The chart configurations in the openHab UI are quite basic, but you can utilize all ECharts options by modifying the chart definition YAML code. For a reference on the configuration options, for example for Areas or Step lines, see Get Started - Handbook - Apache ECharts

Control parameters page
I sometimes want to fine tune the automatically calculated control points. For this reason, I have have a Tabbed page called “Control parameters”, which has a tab for heating, water heater and our Air Heat Pumps (I control our Air Heat pump using the MelCloud Binding, not in the scope of this tutorial):

  • Lämmitystuntien määrä: Number of heating hours
  • Vuorokauden jako lämmitysosiin: Number of heating slices
  • Force heating ON for a given hour
  • Force heating OFF for a given hour

I have modified the rules so that I have an Item called “HeatingHours” and “HeatingSlices”. My own implementation is slightly more complex than illustrated in the tutorial section above:

  • I calculate the number of heating hours based on the weather forecast and update the HeatingHours item. I can adjust the value also manually using this UI.
  • The script action that calculates the control points for the ground source heat pump reads the number of needed hours from the HeatingHours item instead of calculating it on-demand as illustrated in the tutorial section above.
  • If the number of heating hours or heating slices is updated here, it triggers a re-calculation of the control points.
  • The force-on and force-off items have a datetime input widget. If these are changed, it triggers a rule that sets the control point for that specific hour as 1 or 0.

Energy consumption vs spot prices
I fetch the actual power consumption from Caruna using GitHub - kimmolinna/pycaruna: Caruna API for Python (not in the scope of this tutorial) and store the consumption to InfluxDB as “caruna_consumption” measurement. I have also created a dummy Item called “caruna_consumption” to openHab with the exact same name, which means I can access the values in Charts Page using this Item. Here is the consumption of one week, blue bars is consumption and green line is the spot price. Note the date range slider at the bottom and week navigation on the top. I like this navigation flexibility as opposed to building this with Grafana and embedding to openHab as a WebFrame.

Consumption Factor
Finally, I have a Grafana dashboard where I can see the spot price average of the defined date range and see how much below our actual consumption is. The calculation formula here is the same as is used in Väre Välkky, Helen Fiksusähkö and Fortum Duo contracts, where you have a base price but you can then affect the final price with scheduling the consumption to the cheap hours.

Query for the average spot price, change the visualization to “Stat”:

from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r["_measurement"] == "spot_price"))
  |> filter(fn: (r) => (r["_field"] == "value"))
  |> mean()

Query for the consumption factor (“kulutusvaikutus”), visualize as “Stat”

avg_price =from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r["_measurement"] == "spot_price"))
  |> filter(fn: (r) => (r["_field"] == "value"))
  |> mean()
  |> findColumn(fn: (key) => key._measurement == "spot_price", column: "_value") 

total_consumption =from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r["_measurement"] == "caruna_consumption"))
  |> filter(fn: (r) => (r["_field"] == "value"))
  |> sum()
  |> findColumn(fn: (key) => key._measurement == "caruna_consumption", column: "_value")

caruna_consumption = from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "caruna_consumption" and r["_field"] == "value")

spot_price = from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "spot_price" and r["_field"] == "value")

join(tables:{caruna_consumption:caruna_consumption, spot_price:spot_price}, on:["_time"])
  |> map(fn:(r) => ({
      time: r._time,
      _value: ((r._value_spot_price - avg_price[0]) * r._value_caruna_consumption) / total_consumption[0], 
    })
  )
  |> sum()

Query for the bar chart, visualize as Time Series, bar chart:

avg_price =from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r["_measurement"] == "spot_price"))
  |> filter(fn: (r) => (r["_field"] == "value"))
  |> mean()
  |> findColumn(fn: (key) => key._measurement == "spot_price", column: "_value") 

total_consumption =from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => (r["_measurement"] == "caruna_consumption"))
  |> filter(fn: (r) => (r["_field"] == "value"))
  |> sum()
  |> findColumn(fn: (key) => key._measurement == "caruna_consumption", column: "_value")

caruna_consumption = from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "caruna_consumption" and r["_field"] == "value")

spot_price = from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "spot_price" and r["_field"] == "value")

join(tables:{caruna_consumption:caruna_consumption, spot_price:spot_price}, on:["_time"])
  |> map(fn:(r) => ({
      time: r._time,
      Kulutusvaikutus: ((r._value_spot_price - avg_price[0]) * r._value_caruna_consumption) / total_consumption[0], 
    })
)
7 Likes

Thanks all who helped and pushed me to the right direction along the way!

If you have any suggestions to improve the code, I’m all ears.

And one more disclaimer: I promised myself 21 years ago that I will never ever touch javascript again and this is now the first time I broke that promise (I have to admit it was not that bad after all.) But having said this, this is literally the first time in more than 20 years that I’m writing JS so it might be that I’m not following some conventions properly…

I edited my own comment #13 (which has been marked as the solution) so that it now also includes the control logic for my Nibe ground source heat pump.

  • I read the weather forecast from the Finnish Meteorology Institute’s API and store the temperatures for each hour to Influx database.
  • Based on the average temperature for the day, I will determine how many hours the heat pump compressor must be allowed to run. When it’s -20 celsius, 22 hours will be required (I can avoid 2 most expensive hours). When it’s +20 celsius, 2 hours will be required for heating the hot water. This heat curve is configurable in the code.
  • Once the number of hours is known, I’ll determine the control points as “nibe_control” measurement points.
  • To avoid a situation during the winter where the heat pump is on for say 12 hours in a row and then off for the next 12 hours, the day can be split to a desired amount of “slices”, for example 2 x 12 hours or 3 x 8 hours.
  • For every slice, we can define a minimum percentage of the “on” hours. Example: Let’s say 10h of heating is required and the day is split into 2 slices. We can define that at least 10% of the 10 hours must be allocated to all slices. Meaning that both slices would have 0.1 x 10h = 1 hour of heating.
  • The slices and percentages are configurable in the code.

Looks like a really nice solution I will try to use the most of your solution, but will try to use mariadb instead of influxdb, I do not know if thats even possible but I will give it a try as soon vaccation is over and I’m at home.

1 Like

Nice that you found this useful @Marcus_Carlsson !

Regarding the MariaDB vs. InfluxDB. The key gotcha here is that you must be able to write measurement points with future timestamps.

I decided to move my database to run on a NAS server instead of the Raspberry when openHAB runs so that I can minimize the write operations to the Raspberry SD card. When I did that transition, I tried to use MariaDB (which was available on my NAS with a point-and-click configuration) but I was not able to figure out how to write points with future timestamps, see Is it possible to use the JDBC service to access MariaDB from a script

If you’re able to crack where I went to the woods, feel free to comment on that thread.

I ended up installing InfluxDB on my NAS and it’s now up an running. The first results look quite promising. The screenshot below is from my electricity company. The black line is the price and bars are my consumption. :slight_smile:

Cheers,
Markus

Hi now I am in the deed of getting this tested for my solution, and I am absolutley no professional programmer but I try to learn as we go.

I stole all your scripts and folder structure for testing, influxdb runs in a docker container on an synology nas and openhab succeccfully writes other data to it.

I did change database adress and token in influx and set token and area in entose.js

But when I create rules and launch them all rule fail with

Script execution of rule with UID ‘a8a539c0b4’ failed: ReferenceError: “require” is not defined in at line number 1

Do I need to install any extra script engine or will it run with ECAM 262 edition 5.1

Reply to myself, Edition 11 was the right one, installed JSSripting and now have edition 11, however Exeption throws on xslt transformation.

entose.js: transforming XML to JSON and parsing prices…
17:29:04.612 [ERROR] [org.openhab.automation.script ] - entsoe.js: Exception parsing spot prices: Invalid JSON: :1:176 Trailing comma is not allowed in JSON
{“Publication_MarketDocument” : {“mRID” : “eda9f974a1d9439db156d7dd57f2df6d”

However script says it succefully written mesaurments to influxdb, but I cannot find them.

It might be a problem with the xslt transformation. When I trigger the rule and use xml2json.xsl the following error occure that ´probably ends up not having any values there right now.

The value of attribute “test” associated with an element type “xsl:if” must not contain the ‘<’ character.

And I found documentation about that < was not allowed and nedded to be replace with < thats the correct operator. When I did that I do not get that error but still no data in influx.

FORECAST SCRIPT
Errors with
fmi.js: Making an API call to FMI API…
20:27:04.890 [ERROR] [org.openhab.automation.script ] - fmi.js: Temperatures parsing failed!
20:27:04.897 [INFO ] [org.openhab.automation.script ] - influx.js: Preparing to write points to the database

And fetch spotprice erros with

  • entsoe.js: Making an API call to Entso-E API…
    20:30:35.173 [INFO ] [org.openhab.automation.script ] - entose.js: transforming XML to JSON and parsing prices…
    20:30:35.283 [ERROR] [org.openhab.automation.script ] - entsoe.js: Exception parsing spot prices: Invalid JSON: :2:0 Invalid JSON: String contains control character
    " :
    ^
    20:30:35.287 [INFO ] [org.openhab.automation.script ] - influx.js: Preparing to write points to the database for spot_price

Does anyone have any idea?