EPEX Spot and Awattar Electricity Prices

Erstmal vielen Dank für diese Integration, genau danach hatte ich gesucht.

Ich suche nur gerade noch nach einer Problemlösung.

Ich möchte das meine Geräte, zb die Waschmaschine in Abhängigkeit zu der Programmdauer, zum günstigsten Preis startet.

Dein Template hat nur eine fixe Zeit.

'''
template:
  - sensor:
      - name: epex_start_low_period
        state: >-
          {% set ns = namespace(attr_dict=[]) %}
          {% for item in (state_attr('sensor.epex_spot_data_price', 'data'))[0:24] %}
              {%- set ns.attr_dict = ns.attr_dict + [(loop.index-1,item["price_eur_per_mwh"])] %}
          {% endfor %}
          {%- set price_map = dict(ns.attr_dict) %}
          {%- set price_sort = price_map.values()|list %}
          {%- set keys_list = price_map.keys()|list %}
          {%- set ns = namespace(combo=[]) %}
          {%- for p in keys_list %}
            {%- set p = p|int %}
            {%- if p < 22 %}
              {%- set ns.combo = ns.combo + [(p, ((price_sort)[p] + (price_sort)[p+1] + (price_sort)[p+2])|round(2))] %}
            {%- endif %}
          {%- endfor %}
          {%- set mapper = dict(ns.combo) %}
          {%- set key = mapper.keys()|list %}
          {%- set val = mapper.values()|list %}
          {%- set val_min = mapper.values()|min %}
          {{ key[val.index(val_min)]|string + ":00" }}

'''

Jetzt hat meine Waschmaschine zb einen Sonor, der sagt wie lange das Programm braucht… sensor.waschmaschine_remaining_time

Wie bringe ich dein Templet dazu, dynamisch diese Werte zu übernähmen und den besten Zeitpunkt anhand dieses Wertes zu berechnen?

Ok, habs gefunden wie^^

   - trigger:
       - platform: state
         entity_id:
           - sensor.waschmaschine_remaining_time
     action:
       - service: epex_spot.get_lowest_price_interval
         data:
           earliest_start: >-
             {{ as_timestamp(now()) | timestamp_custom('%H:%M:%S') }}
           latest_end: "23:00:00"
           duration: >-
             {{ (states('sensor.waschmaschine_remaining_time')|int) |
             timestamp_custom('%H:%M:%S') }}
         response_variable: resp
     sensor:
       - name: waschmaschine_beste_zeit
         device_class: timestamp
         state: "{{ resp.start is defined and resp.start }}"

There is another solution now. Have a look:

I cannot connect my appliances to HA and have HA control the devices via automations. Even if I connect them to a smart-plug and provide power only when the Binary Sensor is on, the appliances will not automatically start running. Hence, the Binary Sensors’ main output (on/off) is useless to me.

However, I can program the washing machine to finish in X hours or the dishwasher to start in X hours. This means that the “Data” Attribute of the binary sensors has the information I require. For example, my dishwasher sensor tells me that I should program the dishwasher to start at midnight on the 7th.

My question is: How do I take the data that I have marked in red, format it nicely, and display it in a card on my dashboard?

This is the kind of end result I am looking for:
image
I made this with Inspect Element :sweat_smile:

Sorry for the double post, but I managed to get this done by using ChatGPT 4.0. Here’s how I’ve set everything up now.

I have three EPEX Spot Sensors
  • binary_sensor.30deg_batch_cheapest_window
  • binary_sensor.60deg_batch_cheapest_window
  • binary_sensor.dishwasher_cheapest_window
I created three Template Sensor in the Helpers section using the UI for calculating the duration, but also taking only the future time into account.

sensor.dishwasher_start_duration

{% set data = state_attr('binary_sensor.dishwasher_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('start_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set start_time = strptime(next_window['start_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {% set time_to_start = (start_time - now).total_seconds() %}
  {% set hours = (time_to_start // 3600) | int %}
  {% set minutes = ((time_to_start % 3600) // 60) | int %}
  {% set time_str = '{:02}:{:02}'.format(hours, minutes) %}
  {{ time_str }}
{% else %}
  'No upcoming start'
{% endif %}

sensor.next_30deg_batch_end_duration

{% set data = state_attr('binary_sensor.30deg_batch_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('end_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set end_time = strptime(next_window['end_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {% set time_to_end = (end_time - now).total_seconds() %}
  {% set hours = (time_to_end // 3600) | int %}
  {% set minutes = ((time_to_end % 3600) // 60) | int %}
  {% set time_str = '{:02}:{:02}'.format(hours, minutes) %}
  {{ time_str }}
{% else %}
  Waiting for new data.
{% endif %}

sensor.next_60deg_batch_end_duration

{% set data = state_attr('binary_sensor.60deg_batch_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('end_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set end_time = strptime(next_window['end_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {% set time_to_end = (end_time - now).total_seconds() %}
  {% set hours = (time_to_end // 3600) | int %}
  {% set minutes = ((time_to_end % 3600) // 60) | int %}
  {% set time_str = '{:02}:{:02}'.format(hours, minutes) %}
  {{ time_str }}
{% else %}
  Waiting for new data.
{% endif %}
I then made three Template Sensor for reporting only the future times.

sensor.dishwasher_start_time

{% set data = state_attr('binary_sensor.dishwasher_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('start_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set start_time = strptime(next_window['start_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {{ start_time.strftime('%H:%M on %d/%m/%y') }}
{% else %}
  Waiting for new data
{% endif %}

sensor.next_30deg_batch_end_time

{% set data = state_attr('binary_sensor.30deg_batch_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('end_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set end_time = strptime(next_window['end_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {{ end_time.strftime('%H:%M on %d/%m/%y') }}
{% else %}
  Waiting for new data.
{% endif %}

sensor.next_60deg_batch_end_time

{% set data = state_attr('binary_sensor.60deg_batch_cheapest_window', 'data') %}
{% set now = now() %}
{% set future_windows = data | selectattr('end_time', '>', now.timestamp() | timestamp_local) | list %}
{% if future_windows %}
  {% set next_window = future_windows | first %}
  {% set end_time = strptime(next_window['end_time'], '%Y-%m-%dT%H:%M:%S%z') %}
  {{ end_time.strftime('%H:%M on %d/%m/%y') }}
{% else %}
  Waiting for new data.
{% endif %}

Next, I needed to install Card Tools & Secondary Info entity Row via HACS.

Finally, I created an Entites Card in the dashboard with this code
type: entities
entities:
  - entity: sensor.dishwasher_start_duration
    name: Dishwasher Start
    icon: mdi:dishwasher
    type: custom:secondaryinfo-entity-row
    secondary_info: '[[ sensor.dishwasher_start_time ]]'
  - entity: sensor.next_30deg_batch_end_duration
    name: 30° Batch End
    icon: mdi:washing-machine
    type: custom:secondaryinfo-entity-row
    secondary_info: '[[ sensor.next_30deg_batch_end_time ]]'
  - entity: sensor.next_60deg_batch_end_duration
    name: 60° Batch End
    icon: mdi:washing-machine
    type: custom:secondaryinfo-entity-row
    secondary_info: '[[ sensor.next_60deg_batch_end_time ]]'
title: Appliance Schedules
state_color: true
show_header_toggle: false

And this is the final result
image

As a side-note, I am quite mind-blown right now with how well ChatGPT guided me, a complete HomeAssistant noob, through the process. I primarily gave it screenshots and it parsed everything VERY well. All the code you see here was written by ChatGPT, with very minor tweaks from me (eg changing variable names correcting capitalisation and other minor cosmetic changes).

EDIT: I found a Home Assistant Assistant GPT and used it to make this card conditional.

Originally, if the data for the following day hadn't been updated, then the entities would simply show "Waiting for new data":

image

I figured that it didn't make much sense to display the entity if there wasn't any data yet, so I asked GPT to hide it for me

This is what it coded

type: entities
title: Appliance Schedules
state_color: true
show_header_toggle: false
entities:
  - type: conditional
    conditions:
      - entity: sensor.dishwasher_start_duration
        state_not: "Waiting for new data"
    row:
      entity: sensor.dishwasher_start_duration
      name: Dishwasher Start
      icon: mdi:dishwasher
      type: custom:secondaryinfo-entity-row
      secondary_info: '[[ sensor.dishwasher_start_time ]]'
  - type: conditional
    conditions:
      - entity: sensor.next_30deg_batch_end_duration
        state_not: "Waiting for new data"
    row:
      entity: sensor.next_30deg_batch_end_duration
      name: 30° Batch End
      icon: mdi:washing-machine
      type: custom:secondaryinfo-entity-row
      secondary_info: '[[ sensor.next_30deg_batch_end_time ]]'
  - type: conditional
    conditions:
      - entity: sensor.next_60deg_batch_end_duration
        state_not: "Waiting for new data"
    row:
      entity: sensor.next_60deg_batch_end_duration
      name: 60° Batch End
      icon: mdi:washing-machine
      type: custom:secondaryinfo-entity-row
      secondary_info: '[[ sensor.next_60deg_batch_end_time ]]'

However, the result was (expectedly) an empty card with just the heading
image

I had no idea that state_not was even a possibility.

I decided that if the entire card is empty, might as well just hide it

This is the final YAML for the card now:

type: vertical-stack
cards:
  - type: conditional
    conditions:
      - entity: sensor.dishwasher_start_duration
        state_not: "Waiting for new data"
      - entity: sensor.next_30deg_batch_end_duration
        state_not: "Waiting for new data"
      - entity: sensor.next_60deg_batch_end_duration
        state_not: "Waiting for new data"
    card:
      type: entities
      title: Appliance Schedules
      state_color: true
      show_header_toggle: false
      entities:
        - type: conditional
          conditions:
            - entity: sensor.dishwasher_start_duration
              state_not: "Waiting for new data"
          row:
            entity: sensor.dishwasher_start_duration
            name: Dishwasher Start
            icon: mdi:dishwasher
            type: custom:secondaryinfo-entity-row
            secondary_info: '[[ sensor.dishwasher_start_time ]]'
        - type: conditional
          conditions:
            - entity: sensor.next_30deg_batch_end_duration
              state_not: "Waiting for new data"
          row:
            entity: sensor.next_30deg_batch_end_duration
            name: 30° Batch End
            icon: mdi:washing-machine
            type: custom:secondaryinfo-entity-row
            secondary_info: '[[ sensor.next_30deg_batch_end_time ]]'
        - type: conditional
          conditions:
            - entity: sensor.next_60deg_batch_end_duration
              state_not: "Waiting for new data"
          row:
            entity: sensor.next_60deg_batch_end_duration
            name: 60° Batch End
            icon: mdi:washing-machine
            type: custom:secondaryinfo-entity-row
            secondary_info: '[[ sensor.next_60deg_batch_end_time ]]'

Big thanks to @webwude for the inspiration - I did not know that this was even a possibility till I saw his type : conditional card!

I love how much of a help an LLM has been through this process - it has been great for learning and I think that it can definitely lower the barrier of entry for people like me who may not be complete coding noobs, but are absolutely not programmers, and don’t have enough time/resources to sit down and scour through loads of documentation. 🩵

3 Likes

Hi there,
the features provided are amazing!
For combining the prices with my estimated solar production, I would need each hourly value as a sensor.
As I am very new to this, probably someone could give me a hint as to how to achieve this.
Thanks in advance!
w.

I noticed that just the average price for the next day comes online at 2 pm, but not the rest of the pricing data. Do you know around what time the rest of the graph gets populated with values? Does this depend on the provider somehow? I am using the smart energy API and even at 18:00, I can see (what I think is) the average price for tomorrow but not the hourly pricing data.

I get all data. Otherwise I could not show the whole data for the next day. I just currently awattar. Maybe smartenergy just send the average?

Interesting. Yeah then it really might just be different with Smart Energy. This is what my card looks like right now (big thanks for the code btw), and then sometime in a few hours that bottom graph will have all of the hourly data for tomorrow.

EDIT: Sorry, this was a RTFM issue :roll_eyes:

Die API-Schnittstelle liefert die Daten der EPEX Spot At Strombörse im 15 – Minuten Takt. Ab 17:00 Uhr werden die Preise für den nächsten Tag zusätzlich dargestellt: API–Schnittstellen - by smartENERGY

Ok. Good to know.

Nevertheless you have different values compared to me and the smart energy website as well.
Do you add anything else to the prices?

Yes, I added ¢1.44 to the price since the prices reported by the API do not include the Grundgebühr & Abwicklungsgebühr.

Edit: I’m not sure how to account for the monthly Grundgebühren.

Abwicklung should be included (according to the raw EPEX spot data and the published formula). Grundgebühr certainly not.

According to their API page, Smart Energy doesn’t include the Abwicklungsgebühr.

This might not be correct.

If you use their formula: “EPEX Spot AT plus Abwicklungsgebühr in Höhe von 1,44 ct/kWh inkl. 20 % USt (kaufmännisch gerundet auf zwei Nachkommastellen)” you end up with their posted values on the website - and mine.

I don’t know what their API currently delivers, as I am using the raw EPEX spot data, I get exactly the same value using the formula.

Interesting. I do notice that there’s a mismatch between what their website the current electricity price is (¢8.55) and what my dashboard shows (¢8.842). Their API entry for right now says ¢7.114 which is exactly ¢1.44 short of their website.

I think the integration is computing something incorrectly. I cannot get it to show ¢8.55 at all no matter how I manipulate the tax and the surcharge fields. When I get home I’ll try setting up the EPEX Spot pricing and see if that works better.

Für me it is

EPEX Spot AT raw
Surcharge 1.2
Tax 20%

(With that, I get 1,44 surcharge incl. VAT).
Right now, I get 8,554 ct/kWh.

1 Like

@stwo One sensor per hour sounds strange, but anyhow: Please see the post above yours for an example using a template sensor. I think this could be also work for you.

@AkshayR You have to configure tax and surcharges in the configuration window of the integration.

Of course. I had originally configured it like this
image

based on this information from smartENERGY

However, that information is apparently wrong, and using the values provided by @webwude reports the correct price.
This config is what is working correctly:
image

Hy!
I´ve installed and configured all correctly, I´ve added the “Netzkosten” from our Provider to the Absolute Surcharge, so i can see (not exactly, but better then i need) the whole Price for power consuming.
Now i like to calculate my daily, monthly Costs with € and kWh.
How can i do this?
Calculating hourly with the consumed Power in this Hour?