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

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.

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.

All code below is provided without any warranty and published under the ISC License, see the code. Please attribute Markus Sipilä, Markus Sipilä - Espoo, Uusimaa, Finland | Professional Profile | LinkedIn, 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: https://www.waveshare.com/wiki/RPi_Relay_Board_(B) 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';

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.
• It also fetches the wind speed and calculates the “feels like” wind chill compenstaed temperature for advanced users.
• 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.
xml = fmi.makeApiCall(place);

Temperature = fmi.preparePoints(xml, 'Temperature');
influx.writePoints('fmi_forecast_temperature', Temperature);

WindSpeedMS = fmi.preparePoints(xml, 'WindSpeedMS');
influx.writePoints('fmi_forecast_WindSpeedMS', WindSpeedMS);

WindChillTemp = fmi.calculateWindChillTempPoints(Temperature, WindSpeedMS);
influx.writePoints('fmi_forecast_WindChillTemp', WindChillTemp);
``````

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ä, Markus Sipilä - Espoo, Uusimaa, Finland | Professional Profile | LinkedIn, 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 (7.3 KB)
fmi.js.txt (4.4 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:

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”)

``````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],
})
)
``````
12 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.
1 Like

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.

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?

The error message here means that you are not able to read the forecaster temperatures from the the Finnish Meteorology Institute’s weather forecast. You most probably do not want to read the weather forecast from Finnish Meteorology Institute anyway?

To narrow things down, you can temporarily modify the rule script so that you don’t calculate the number of hours based on the weather forecast. Instead, just put there a static value like 5 when you’re debugging this:

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

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

// Comment out these two lines that fetch the weather forecast and calculate how many hours are needed.
// t = nibe.getForecastTemp(start, stop);
// n = nibe.calculateNumberOfHours(t);
// Temporarily define the number of hours as five.
n = 5;
nibe.determineHours(start, stop, n, 3, 0.1);
``````

You can add a couple of debug messages so that you can see from your logs what is going on.

Add the debug lines like this to get the complete XML response written to your log:

``````function makeApiCall(start, end, area, token) {
[...]
priceXml = http.sendHttpGetRequest(url, 10000);
console.debug('Complete XML response:');
console.debug(priceXML);
return priceXml;
``````

Add these debug lines to see if the XML to JSON transformation works:

``````function preparePoints(priceXml, tax) {
[...]
const prices = JSON.parse(transformation.transform('XSLT', 'xml2json.xsl', priceXml));
console.debug('Complete JSON:');
console.debug('prices');
``````

Cheers,
Markus

@Marcus_Carlsson one more thing related to debugging.

If (when) you modify the javascript files, you need to “refresh” your rules so that openHAB will use the updated versions of the js files. This can be done as follows:

• Go to edit the rule that uses the js file
• Save the rule
• Run the rule

If you don’t go to the edit mode and save the rule, openHAB will use the old version of the JS file it had read to memory before.

Hi

Thanks for your efforts to help me out, I managed to get some xml output after changing console.debug to console.log, but somewhere in xml2json there must be a problem, I don’t se any output from extensive console.log in xml2json function. Meybe it wasn’t right to change < to

`````` &lt;
``````

In the xml2jsonfile, I downloaded an copy an pasted xml2json file again and now it works better. However influx db doesn’t contain much data spotprice came up but only one price point

``````2022-07-27 15:06:34.088 [INFO ] [org.openhab.automation.script       ] - entsoe.js: Making an API call to Entso-E API...
2022-07-27 15:06:34.591 [INFO ] [org.openhab.automation.script       ] - Complete XML response:
2022-07-27 15:06:34.600 [INFO ] [org.openhab.automation.script       ] - <?xml version="1.0" encoding="UTF-8"?>
<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0">
<mRID>5481dbea73224a0e8c4cd7ec6b1694af</mRID>
<revisionNumber>1</revisionNumber>
<type>A44</type>
<sender_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</sender_MarketParticipant.mRID>
<sender_MarketParticipant.marketRole.type>A32</sender_MarketParticipant.marketRole.type>
<createdDateTime>2022-07-27T13:06:34Z</createdDateTime>
<period.timeInterval>
<start>2022-07-27T22:00Z</start>
<end>2022-07-28T22:00Z</end>
</period.timeInterval>
<TimeSeries>
<mRID>1</mRID>
<in_Domain.mRID codingScheme="A01">10Y1001A1001A46L</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10Y1001A1001A46L</out_Domain.mRID>
<currency_Unit.name>EUR</currency_Unit.name>
<price_Measure_Unit.name>MWH</price_Measure_Unit.name>
<curveType>A01</curveType>
<Period>
<timeInterval>
<start>2022-07-27T22:00Z</start>
<end>2022-07-28T22:00Z</end>
</timeInterval>
<resolution>PT60M</resolution>
<Point>
<position>1</position>
<price.amount>2.65</price.amount>
</Point>
<Point>
<position>2</position>
<price.amount>2.16</price.amount>
</Point>
<Point>
<position>3</position>
<price.amount>2.26</price.amount>
</Point>
<Point>
<position>4</position>
<price.amount>2.48</price.amount>
</Point>
<Point>
<position>5</position>
<price.amount>3.23</price.amount>
</Point>
<Point>
<position>6</position>
<price.amount>5.23</price.amount>
</Point>
<Point>
<position>7</position>
<price.amount>6.91</price.amount>
</Point>
<Point>
<position>8</position>
<price.amount>50.05</price.amount>
</Point>
<Point>
<position>9</position>
<price.amount>200.05</price.amount>
</Point>
<Point>
<position>10</position>
<price.amount>206.57</price.amount>
</Point>
<Point>
<position>11</position>
<price.amount>209.92</price.amount>
</Point>
<Point>
<position>12</position>
<price.amount>209.98</price.amount>
</Point>
<Point>
<position>13</position>
<price.amount>209.97</price.amount>
</Point>
<Point>
<position>14</position>
<price.amount>197.19</price.amount>
</Point>
<Point>
<position>15</position>
<price.amount>204.25</price.amount>
</Point>
<Point>
<position>16</position>
<price.amount>200.09</price.amount>
</Point>
<Point>
<position>17</position>
<price.amount>205.73</price.amount>
</Point>
<Point>
<position>18</position>
<price.amount>214.49</price.amount>
</Point>
<Point>
<position>19</position>
<price.amount>220.97</price.amount>
</Point>
<Point>
<position>20</position>
<price.amount>220.96</price.amount>
</Point>
<Point>
<position>21</position>
<price.amount>214.91</price.amount>
</Point>
<Point>
<position>22</position>
<price.amount>212.83</price.amount>
</Point>
<Point>
<position>23</position>
<price.amount>207.25</price.amount>
</Point>
<Point>
<position>24</position>
<price.amount>179.56</price.amount>
</Point>
</Period>
</TimeSeries>
</Publication_MarketDocument>
2022-07-27 15:06:34.627 [INFO ] [org.openhab.automation.script       ] - entose.js: transforming XML to JSON and parsing prices...
2022-07-27 15:06:34.828 [INFO ] [org.openhab.automation.script       ] - Complete JSON:
2022-07-27 15:06:34.835 [INFO ] [org.openhab.automation.script       ] - prices[object Object]
2022-07-27 15:06:34.847 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-27T22:00:00.000Z 0.265 c/kWh
2022-07-27 15:06:34.854 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-27T23:00:00.000Z 0.21600000000000003 c/kWh
2022-07-27 15:06:34.861 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T00:00:00.000Z 0.22599999999999998 c/kWh
2022-07-27 15:06:34.868 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T01:00:00.000Z 0.248 c/kWh
2022-07-27 15:06:34.875 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T02:00:00.000Z 0.323 c/kWh
2022-07-27 15:06:34.882 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T03:00:00.000Z 0.523 c/kWh
2022-07-27 15:06:34.890 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T04:00:00.000Z 0.6910000000000001 c/kWh
2022-07-27 15:06:34.897 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T05:00:00.000Z 5.005 c/kWh
2022-07-27 15:06:34.904 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T06:00:00.000Z 20.005000000000003 c/kWh
2022-07-27 15:06:34.911 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T07:00:00.000Z 20.657 c/kWh
2022-07-27 15:06:34.918 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T08:00:00.000Z 20.991999999999997 c/kWh
2022-07-27 15:06:34.926 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T09:00:00.000Z 20.997999999999998 c/kWh
2022-07-27 15:06:34.933 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T10:00:00.000Z 20.997 c/kWh
2022-07-27 15:06:34.940 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T11:00:00.000Z 19.719 c/kWh
2022-07-27 15:06:34.947 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T12:00:00.000Z 20.425 c/kWh
2022-07-27 15:06:34.954 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T13:00:00.000Z 20.009 c/kWh
2022-07-27 15:06:34.961 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T14:00:00.000Z 20.573 c/kWh
2022-07-27 15:06:34.968 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T15:00:00.000Z 21.449 c/kWh
2022-07-27 15:06:34.976 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T16:00:00.000Z 22.097 c/kWh
2022-07-27 15:06:34.983 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T17:00:00.000Z 22.096 c/kWh
2022-07-27 15:06:34.990 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T18:00:00.000Z 21.491 c/kWh
2022-07-27 15:06:34.997 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T19:00:00.000Z 21.283 c/kWh
2022-07-27 15:06:35.004 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T20:00:00.000Z 20.725 c/kWh
2022-07-27 15:06:35.011 [INFO ] [org.openhab.automation.script       ] - entsoe.js: 2022-07-28T21:00:00.000Z 17.956 c/kWh
2022-07-27 15:06:35.017 [INFO ] [org.openhab.automation.script       ] - influx.js: Preparing to write points to the database for spot_price
2022-07-27 15:06:35.200 [INFO ] [org.openhab.automation.script       ] - influx.js: Points successfully saved for measurement spot_price

``````

The reason why you’re not seeing more than one value is that your Influx Query is searching for data for “past 1 hour”. Change the range to include tomorrow and you’ll see tomorrow’s spot prices

Thanks again for all your efforts of helping men out. I will be out travelling for next couple of days but wilöl have spotprice fetching rule up an running every hour for the moment I did indeed have more data if I set a different filer .

I did get FMI forecast script to runt but it didnt write an mesurement, and I beleive that thats the reason why nibe and waterboiler script fails theres no temperature forecast. I will probably convert this to my local realtime temperature but thought I will get this nice example up and running before I start modifying to much. And I will also integrate this with my Easee EV wallbox.

The waterheater rule is not depending on the weather forecast at all. It should work if/when you have the spot prices stored in your influxDB.

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

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

wh.determineHours(start, stop, 4);
``````

The last row means that “between the start midnight and stop midnight, find a 4 hour window when the spot prices are lowest”.

Our hot water consumption does not depend on the outside weather. Four hours should be easily enough to heat the water for the whole day.

• 1 kWh will be required to heat 864 liters of water by 1 degree
• My water heater has a 300 liter tank so it will require 300/864 = 0,347 kWh to raise the water temperature by 1 degree
• My water heater is 3 kW so letting it run for 4 hours means 12 kWh
• Which means that 12 kWh can raise the water temperature by 12/0,347 = 34,5 degrees

This is easily more than enough because the water heater is “behind” or “after” the Nibe ground source heat pump which pre-warms the water in its 180 liter integrated tank to 48 degrees so the starting point for the water heater is barely ever under 40 degrees.

I’m currently planning to install a couple of DS18B20 temperature sensors to the outer shell of the tank of the water heater so that I could measure how cold / warm the water is already and then determine if I need to allow 1, 2, 3 or 4 hours of heating time for the water heater.

1 Like

Here’s what I’ve been able to achieve so far. Blue bars is my consumption which I’m able to fetch from the power grid operator and green line are the spot prices. You can see nice consumption peaks when the prices are lowest.

July 26 and 27 were hugely exceptional days in Finland in terms of the spot prices as there was a lot of wind power and at the same time the Baltic countries had planned maintenance in their power grid which meant that they could not import electricity from Finland which they normally do. I think we washed laundry something like 4 or 5 times, cleaned the oven with pyrolysis and so on.

My electricity contract is not directly bound to the Nordpool spot prices (i.e. spot price + margin). Instead, I have a contract which is 14.8 c / kWh +/- “consumption factor”. The consumption factor is calculated as follows:

consumption factor = (A-B) / E, where

• A is the sum of consumption * spot price over every hour of the month
• B is the total consumption * average price in Nordpool for the month
• E is the total consumption of the month

In other words, if I use power on hours which are cheaper than the month’s average spot price, the consumption factor will be negative and decrease the price that I pay of the whole month’s power. With these optimizations this means that the 14.8 c/kWh is effectively a ceiling price that I will have to pay under any circumstances, even if the Nordpool would be at 100c / kWh next winter.

Here’s a 7 day trend for the consumption factor. If I would be able to keep this trend, I would pay 14.8 - 5.9 c / kWh but it’s not going to be that much negative because the July 26-27 was so highly exceptional. But anyway, this I will have a double digit discount to the power bill, which is is quite nice