Calculate average kWh price from consumption and spot data

Energy
1

Hi,

I’ve been spending some time trying to create a new sensor to calculate my realized average daily €/kWh value. I have the nordpool integration to fetch me hourly kWh pricing, and an optical sensor to track my house’s energy consumption. And I have created sensors to calculate €/h data and such. The problem is that my sensor calculating the average daily price/kWh seems to always output “unknown” or “unavailable”, and I think the reason is there’s something wrong with the input sensor value formats.

So the math for the new sensor would be pretty much a weighted average:

Avg.price = (hour1_consumption * hour1_price) + hour2_consumption * hour2_price … + hour24_consumption * hour24_price) / Daily_consumption

What I’ve tried (several variations all seem to lead to the same result “unavailable” value calculated at sensor output):

#average_electricity_price_by_consumption_tariff_included
- platform: template
  sensors:
   price_kwh_average_by_consumption_w_tariff:
    unit_of_measurement: '€/kWh'
    friendly_name: price.kwh.average.w.tariff
    value_template: "{{(states('sensor.price.spot.w.tariff.1h') | float ) / (states('sensor.daily_energy') | float ) }}"

Both input sensors “sensor.price.spot.w.tariff.1h” (hourly total cost) and “sensor.daily_energy” (daily total consumption) give valid results and work as they should as separate sensors. But as inputs for this template they don’t.

To dismiss errors in my code, when I use a sensor template which is mathematically quite similar, such as my total hourly energy price:

- platform: template
  sensors:
   price_spot_w_tariff_1h:
    unit_of_measurement: '€'
    friendly_name: price.spot.w.tariff.1h
    value_template: "{{(states('sensor.hourly_energy') | float )* (states('sensor.spot_total') | multiply(0.01) | float ) }}"

And input the sensor names I’m trying to use for the new calculation, the output immediately turns to Unknown. So my thinking is that the syntax is technically correct, but there’s something wrong with the inputs. For example they should/should not have the “| float” with these input sensors (thinking perhaps I shouldn’t specify the input as a number anymore, since it’s already done at the input sensor level), or something similar. Is there a guideline to what kind of input data I should use for whichever template type I’m trying to calculate, or a way to see the differences with the inputs inside my own ha?

Any thoughts?

2

Have you tried to go to Developer Tools / Templates and then paste just the template part of your code?

{{(states('sensor.price.spot.w.tariff.1h') | float ) / (states('sensor.daily_energy') | float ) }}

Any error?

Are you sure you are typing the sensor name correctly? “_” is more common than “.” to separate words…

It shouldn’t be the source of the issue you are reporting, but you are using the older templating format… You should consider start using the new format instead.
Something like this:

#average_electricity_price_by_consumption_tariff_included 
template:
  - sensor:
      - name: price.kwh.average.w.tariff
        unique_id: price_kwh_average_by_consumption_w_tariff
        unit_of_measurement: '€/kWh'
        state: "{{(states('sensor.price_spot_w_tariff_1h') | float(0) ) / (states('sensor.daily_energy') | float(-1)) }}"
3

Are you sure you are typing the sensor name correctly? “_” is more common than “.” to separate words…

Oh that’s true, I had put down the friendly name (price.spot.w.tariff.1h), while the actual sensor name is price_spot_w_tariff_1h. This is the current correct form, but the situation stays the same.

Have you tried to go to Developer Tools / Templates and then paste just the template part of your code?

This was new to me. The Template tab seems like a very handy test area for these kinds of things (nice to have learned this!).

The output string of
value_template: "{{(states('price_spot_w_tariff_1h') | float ) / (states('sensor.daily_energy') | float ) }}"

gives

ValueError: Template error: float got invalid input 'unknown' when rendering template 'value_template: "{{(states('price_spot_w_tariff_1h') | float ) / (states('sensor.daily_energy') | float ) }}"' but no default was specified
4

You forgot to add the “sensor.” prefix to the entity_id.
Try this (please note that I’ve also removed the value_template: part in order for this to work on the template tab):

{{(states('sensor.price_spot_w_tariff_1h') | float ) / (states('sensor.daily_energy') | float ) }}
5

Ok, now it’s outputting:

Result type: number

0.001981191991950672

And after making the corrections you mentioned, it seems to give a proper output in the UI card as well.

Thank you very much for the help, glad it got solved so quickly!

Now I’ll just need to see how the output works after full 24 hours of data. I’m not exactly certain if my current formula actually calculates the desired value correctly (not sure how ha uses different time frame inputs of hourly data and daily data in the calculation), but I’m sure I’ll find out in a while.

1 Like
6

Alright it seems the calculation formula I first implemented was incorrect. It would always divide the current daily cost by monthly consumption, obviously leading to way too low values.

I got it corrected by making a utility_meter sensor from all the input sensors (so now every component is calculated by cumulatively as it should be), and now it works as it should.

image

image
image939×474 18.6 KB

Here’s for anyone looking to replicate this on their system:

- platform: template
  sensors:
   price_kwh_average_daily_by_consumption_no_tariff:
    unit_of_measurement: 'c/kWh'
    friendly_name: price_kwh_average_daily_by_consumption_no_tariff
    value_template: "{{((states('sensor.daily_cost_spot_no_tariff') | multiply(100) | float ) / (states('sensor.daily_energy') | float )) | round(3) }}"
1 Like
7

Stupid question? How do you calculate this: sensor.daily_cost_spot_no_tariff?