Calculating average from a forecast

Much more involved, yes. I used to do this with the Octopus Agile electricity tariff, with its half- hourly pricing. I used AppDaemon for this. See the discussion starting here:

As you read down, you’ll get to a Python script I wrote to do this, initially without using AppDaemon. Eventually, I moved it across to AD for various reasons — I had to install it for a different project anyway.

I’m already thankful for what you and @Troon did up till now and make sure you enjoy your diner!

To illustrate see below graph. In some cases you have tasks (eg running the washing machine) that take (for example) 2 hours. In that case you want to begin at a time you mimimize cost over those 2 hours and in those case starting at the lowest point is not always the best moment. The shorter the task, the less of problem it is but with very long tasks (eg charging your EV or heating your hottub) you want to be able to choose the right window with the right starting time.

p.s. below is using both your templates;

If this day is common then it seems the three cheapest hours are consecutive from one hour before to one hour after the cheapest price.
Which seems to make sense in my opinion.

@Troon Having some issues with this to my surprise as the data seemed correct the last 2 days…

See below graph; It things the lowest price is now while it clearly in the future. Do you understand what is going on?

\

This is the sensor:

- platform: template
  sensors:
    time_low_coming:
      friendly_name: Time lowest price
      unique_id: time_low_coming
      device_class: timestamp
      value_template: >
        {% set fclist = state_attr('sensor.zonneplan_current_electricity_tariff','forcast') %}
        {% set pmin = fclist|map(attribute='price')|list|min %}
        {{ (fclist|selectattr('price','eq',pmin)|first)['datetime'] }}
This is the arttribute data
state_class: measurement
forcast: 
- price: 1690007
  electricity_price: 1690007
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T07:00:00.000000Z'
  sustainability_score: 544
  carbon_footprint_in_grams: 0
- price: 2007026
  electricity_price: 2007026
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T08:00:00.000000Z'
  sustainability_score: 558
  carbon_footprint_in_grams: 0
- price: 2024693
  electricity_price: 2024693
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T09:00:00.000000Z'
  sustainability_score: 643
  carbon_footprint_in_grams: 0
- price: 2460777
  electricity_price: 2460777
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T10:00:00.000000Z'
  sustainability_score: 708
  carbon_footprint_in_grams: 0
- price: 2522487
  electricity_price: 2522487
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T11:00:00.000000Z'
  sustainability_score: 765
  carbon_footprint_in_grams: 0
- price: 2341108
  electricity_price: 2341108
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T12:00:00.000000Z'
  sustainability_score: 805
  carbon_footprint_in_grams: 0
- price: 2524302
  electricity_price: 2524302
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T13:00:00.000000Z'
  sustainability_score: 763
  carbon_footprint_in_grams: 0
- price: 2595329
  electricity_price: 2595329
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T14:00:00.000000Z'
  sustainability_score: 663
  carbon_footprint_in_grams: 0
- price: 2703987
  electricity_price: 2703987
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T15:00:00.000000Z'
  sustainability_score: 533
  carbon_footprint_in_grams: 0
- price: 3244130
  electricity_price: 3244130
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T16:00:00.000000Z'
  sustainability_score: 453
  carbon_footprint_in_grams: 0
- price: 3667147
  electricity_price: 3667147
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T17:00:00.000000Z'
  sustainability_score: 395
  carbon_footprint_in_grams: 0
- price: 3946656
  electricity_price: 3946656
  tariff_group: high
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T18:00:00.000000Z'
  sustainability_score: 361
  carbon_footprint_in_grams: 0
- price: 3973277
  electricity_price: 3973277
  tariff_group: high
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T19:00:00.000000Z'
  sustainability_score: 306
  carbon_footprint_in_grams: 0
- price: 3591401
  electricity_price: 3591401
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T20:00:00.000000Z'
  sustainability_score: 253
  carbon_footprint_in_grams: 0
- price: 3490487
  electricity_price: 3490487
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T21:00:00.000000Z'
  sustainability_score: 216
  carbon_footprint_in_grams: 0
- price: 3163787
  electricity_price: 3163787
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T22:00:00.000000Z'
  sustainability_score: 227
  carbon_footprint_in_grams: 0
- price: 2280608
  electricity_price: 2280608
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-15T23:00:00.000000Z'
  sustainability_score: 218
  carbon_footprint_in_grams: 0
- price: 2280608
  electricity_price: 2280608
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T00:00:00.000000Z'
  sustainability_score: 204
  carbon_footprint_in_grams: 0
- price: 2313399
  electricity_price: 2313399
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T01:00:00.000000Z'
  sustainability_score: 195
  carbon_footprint_in_grams: 0
- price: 2233297
  electricity_price: 2233297
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T02:00:00.000000Z'
  sustainability_score: 188
  carbon_footprint_in_grams: 0
- price: 2320417
  electricity_price: 2320417
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T03:00:00.000000Z'
  sustainability_score: 219
  carbon_footprint_in_grams: 0
- price: 2607186
  electricity_price: 2607186
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T04:00:00.000000Z'
  sustainability_score: 200
  carbon_footprint_in_grams: 0
- price: 3425510
  electricity_price: 3425510
  gas_price: 13988606
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T05:00:00.000000Z'
  sustainability_score: 202
  carbon_footprint_in_grams: 1312
- price: 3673318
  electricity_price: 3673318
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T06:00:00.000000Z'
  sustainability_score: 185
  carbon_footprint_in_grams: 0
- price: 3804966
  electricity_price: 3804966
  tariff_group: high
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T07:00:00.000000Z'
  sustainability_score: 185
  carbon_footprint_in_grams: 0
- price: 3611487
  electricity_price: 3611487
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T08:00:00.000000Z'
  sustainability_score: 211
  carbon_footprint_in_grams: 0
- price: 3975697
  electricity_price: 3975697
  tariff_group: high
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T09:00:00.000000Z'
  sustainability_score: 289
  carbon_footprint_in_grams: 0
- price: 3558247
  electricity_price: 3558247
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T10:00:00.000000Z'
  sustainability_score: 280
  carbon_footprint_in_grams: 0
- price: 3352426
  electricity_price: 3352426
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T11:00:00.000000Z'
  sustainability_score: 284
  carbon_footprint_in_grams: 0
- price: 3314673
  electricity_price: 3314673
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T12:00:00.000000Z'
  sustainability_score: 279
  carbon_footprint_in_grams: 0
- price: 3322297
  electricity_price: 3322297
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T13:00:00.000000Z'
  sustainability_score: 269
  carbon_footprint_in_grams: 0
- price: 3368277
  electricity_price: 3368277
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T14:00:00.000000Z'
  sustainability_score: 245
  carbon_footprint_in_grams: 0
- price: 3387032
  electricity_price: 3387032
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T15:00:00.000000Z'
  sustainability_score: 184
  carbon_footprint_in_grams: 0
- price: 3572646
  electricity_price: 3572646
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T16:00:00.000000Z'
  sustainability_score: 145
  carbon_footprint_in_grams: 0
- price: 3656498
  electricity_price: 3656498
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T17:00:00.000000Z'
  sustainability_score: 148
  carbon_footprint_in_grams: 0
- price: 3375537
  electricity_price: 3375537
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T18:00:00.000000Z'
  sustainability_score: 165
  carbon_footprint_in_grams: 0
- price: 3319877
  electricity_price: 3319877
  tariff_group: normal
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T19:00:00.000000Z'
  sustainability_score: 159
  carbon_footprint_in_grams: 0
- price: 3151324
  electricity_price: 3151324
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T20:00:00.000000Z'
  sustainability_score: 242
  carbon_footprint_in_grams: 0
- price: 3094938
  electricity_price: 3094938
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T21:00:00.000000Z'
  sustainability_score: 330
  carbon_footprint_in_grams: 0
- price: 3006487
  electricity_price: 3006487
  tariff_group: low
  solar_percentage: 0
  solar_yield: 0
  datetime: '2023-01-16T22:00:00.000000Z'
  sustainability_score: 375
  carbon_footprint_in_grams: 0

unit_of_measurement: €/kWh
icon: mdi:cash
friendly_name: Zonneplan current electricity tariff

Take a look at the times and prices in the data you posted. Which time period in your data has a lower price than the first price of 1690007?

Looks to me like the graph and the data don’t match.

Here’s how to do an hourly cheapest-rate finder:

{% set fclist = state_attr('sensor.zonneplan_current_electricity_tariff','forcast') %}
{% set plist = fclist|map(attribute='price')|list %}
{% set ns = namespace(fc2=[]) %}
{%- for fc in fclist[:-1] -%}
{%- set ns.fc2 = ns.fc2 + [{'datetime':fc['datetime'],'price':(plist[loop.index0] + plist[loop.index0+1])/2}] -%}
{%- endfor -%}
{% set pmin = ns.fc2|map(attribute='price')|list|min %}
{{ (ns.fc2|selectattr('price','eq',pmin)|first)['datetime'] }}

It reads through the forecast up to the penultimate item (that’s what fclist[:-1] means) and builds a list of dictionaries with the datetime copied from the original sensor, and the price calculated as the mean of that slot and the next one.

We then do the same process of finding the minimum price in the new list and find the matching datetime.

You can expand this to any size of time period — just add subsequent items and adjust the total divisor in the mean calculation, and adjust the end point of the list so that your “looking forward” doesn’t go past the end.

I guess you are in a different timezone or looked at the wrong date (the price you mention is from 15/01)?

The ‘NOW’ in the screenshot was the 8-9am slot on the 16/01 (@ a price of 36.11) then at 22:00 (in attribute data and graph) the price is 30.06

OK — this is nothing to do with timezones, but is the problem of you blindly copying the code we suggest without understanding what it does. I’d missed the fact that your sensor also includes past data — my code finds the datetime of the cheapest price in the data regardless of when it is.

Try this for the single-slot forecast, which starts out by restricting the data to future entries:

        {% set dtnow = now().isoformat()[0:26]~"Z" %}
        {% set fclist = state_attr('sensor.zonneplan_current_electricity_tariff','forcast')
                        |selectattr('datetime','>=',dtnow)|list %}
        {% set pmin = fclist|map(attribute='price')|list|min %}
        {{ (fclist|selectattr('price','eq',pmin)|first)['datetime'] }}

The first line builds a datetime string that matches the format in your data; the second part of the second line uses that as a “filter” on the sensor data. Strongly recommend you play around in Developer Tools / Templates to see what’s going on.

You can work out how to translate that into the hourly calculator I posted above.

Thanks again. Will study this (and the one above). And you right, I only understand max 70% of your template but quite a steep learning curve as these are quite advanced compared to the ones I’m able to make.

No problem at all. As I say, play with the template editor. Paste in this and make changes to see what happens:

{{ now().isoformat() }}
{{ now().isoformat()[0:26] }}
{{ now().isoformat()[0:26]~"Z" }}
***
{{ state_attr('sensor.zonneplan_current_electricity_tariff','forcast') }}

@Troon I have been playing around with your template and starting to understand the logic (that is at least something) .

Few questions:

  1. I understand what the [Z] does at the end of the datetime but not sure about the [0:26] when testing. Could also not find it by Googling. Could you explain?

  2. on the template mentioned in this post;

  • Checking: To increase the consecutive hours at lowest price you mean adding another loop.index0-1 (for instance) the 5th line and then for each addition making sure I divide by the correct total divisor (to get the avg of those values & compare them with others). I’ve played around with it and this seems the logic but not 100% sure. Is my assumption Correct?
  • Half way the day, new data is added (another 24hours) to the attributes . This can create a situation where the lowest price is more than a day away (after this addition). This is still valuable info, but is it also possible to limit the list it creates and/or looks in to find the lowest price? With the objective to find -for example- the lowest price after ‘now’ but not further away then 12 hours?

Your sensor contains datetimes that look like this:

  datetime: '2023-01-15T07:00:00.000000Z'

so I manipulate the output of now().isoformat() to match that format: first 26 characters and stick a Z on the end. Once that’s done, it’s possible to compare correctly-ordered date/time strings “alphabetically” as a later time string will always evaluate as “greater” than an earlier time string. See the first general principle of ISO8601.

Nearly, but not loop.index0-1 as that would be looking backwards. The idea is to look forward n slots (hours) and take the average. My example above was for two hours; for three, you’d do:

{% set fclist = state_attr('sensor.zonneplan_current_electricity_tariff','forcast') %}
{% set plist = fclist|map(attribute='price')|list %}
{% set ns = namespace(fc3=[]) %}
{%- for fc in fclist[:-2] -%}
{%- set ns.fc3 = ns.fc3 + [{'datetime':fc['datetime'],
                            'price':(plist[loop.index0] +
                                     plist[loop.index0+1] +
                                     plist[loop.index0+2]) / 3}] -%}
{%- endfor -%}
{% set pmin = ns.fc3|map(attribute='price')|list|min %}
{{ (ns.fc3|selectattr('price','eq',pmin)|first)['datetime'] }}

The set ns.fc3 line now gets the mean of the following three hours starting at each time. The previous (fourth) line fclist[:-2] restricts the starting point of the search to only go up to the third-last item of the list, because you can’t look two steps forward from later items.

Yes. Have a play in the template editor: see if you can filter fclist to exclude too-far-out elements from the start of the template. Here’s how to generate a correctly-formatted timestamp that is 12 hours away:

{% set dtend = (now()+timedelta(hours=12)).isoformat()[0:26]~"Z" %}

Just add a |selectattr('datetime','<=',dtend) onto your fclist definition and you should be good to go.

Your a star @Troon !

Ah! That was the part I did not get at first. I was assuming your approach, but was reaching the end of the list so that was I was going down. I just needed to adjust the starting point to compensate which makes total sense.

Your suggestions on limiting the fclist search also works!

On the datatime: I played around with that (the ones below tested in the in the template test part of HA) and I get different outputs (obviously), but I’m a bit surprised that the total template does not create another output if I modify the date part. All works independent of isoformat so I should not be concerned but was just trying to understand why that is.

{{ now().isoformat() }}   gives 2023-01-18T10:09:00.101813+01:00
{{ now().isoformat()[0:26] }} gives 2023-01-18T10:09:00.102033
{{ now().isoformat()[0:26]~"Z" }} gives 2023-01-18T10:09:00.102180Z (which is the format the sensor we are looking into uses)

I don’t understand what you mean by this. Example?

now() generates a datetime object, whereas you need a string for comparison (the way we’re doing it here) (see the time section of the template docs).

isoformat() (docs: I hadn’t realised this wasn’t directly in the HA docs but only referred to under “other datetime functions”) converts that datetime object to a string in a consistent format that we can pull about to match what we need.

The way I’ve approached this is not the only way it could be done: there are many different ways to work with date/time comparisons.

Can you tell what the outcome is and how it looks like now with showing the code from the apexchart and sensor code maby? Would be nice, thanks!

Below is the final code that gives you the time of the cheapest 2 hour upcoming.

You see a section that mentions ‘hours=6’ ; so this now looks 6 hours in the future eg cheapest 2 hours within 6 hours. Change that to 12, it looks to the next 12 hours cheapest slot.

This sensor has no direct link with apex-chart. Or am I misunderstanding your question?

- platform: template
  sensors:
    price_low_coming_short:
      friendly_name: Price lowest coming short
      unique_id: price_low_coming_short
      device_class: monetary
      value_template: >
        {% set dtnow = now().isoformat()[0:26]~"Z" %}
        {% set dtend = (now()+timedelta(hours=6)).isoformat()[0:26]~"Z" %}
        {% set fclist = state_attr('sensor.zonneplan_current_electricity_tariff','forcast')
                        |selectattr('datetime','>=',dtnow)|selectattr('datetime','<=',dtend)|list %}
        {% set plist = fclist|map(attribute='price')|list %}
        {% set ns = namespace(fc2=[]) %}
        {%- for fc in fclist[:-1] -%}
        {%- set ns.fc2 = ns.fc2 + [{'datetime':fc['datetime'],'price':(plist[loop.index0] + plist[loop.index0+1])/2}] -%}
        {%- endfor -%}
        {% set pmin = ns.fc2|map(attribute='price')|list|min %}
        {{ int((ns.fc2|selectattr('price','eq',pmin)|first)['price'])/100000 }}

Tyfoon,

How can I make this template to load every 24:00 hours?
So that the cheapest hour does not shift throughout the day or it is possible to make this in an automation?

Thank you in advance

Make the sensor trigger based

Check out Hellis81’s solution. Would expect that to work although I have never tried this.

I’m curious to here your use case for a sensor like you mention as in your case (if I understand you correctly) you will get prices from the past.

Also not that most energy integrations will get new info from their supplier around mid day. At that moment in time, there is new data to evaluate and a new lowest price can come out. You have to think how you deal with that. That is the reason why I have 3 versions of this sensor; cheapest in next 3 hours (when I’m in a hurry), cheapest in 6 and 12 hours. I then created sensors that show the saving vs now in % so I can decide how long I want to postpone my ‘consumption’.

Beside Hellis81 suggestion you could adapt above. You will need to play with the template in template editor. The first line in the code is where it starts searching from. In this sensor is ‘now’ so it will only look for cheapest in the future (after now). You can also set this to any time you like, and it will start searching in the attributes from that time. You will have to play around with that.

Last thing; note that the ‘zonneplan’ integration has changed and ‘forcast’ was changed to ‘forecast’

thanks Tyfoon and Hellis81

I was thinking of making it like this:
current price - lowest price = difference.
This difference must not exceed the set value.

It’s for my water heater and washing machine.