Home Assistant – Add electricity provider cost for current month

This tutorial will show you how to calculate the monthly cost of your electricity provider.

This solution will require:

• Shelly EM
• Home Asisistant
• InfluxDB and Grafana configured on Home Assistant

First, gather the data

We will take advantage of Grafana to build the query we need.

Instead of doing lots of calculations to get the data we need with shelly_shem_XXXXXX_1_current_consumption, we will instead use the shelly_shem_XXXXX_1_total_consumption because this is a cumulative counter over time on which we can get the value from the first day and the last day of the date selector, apply the delta function and return the total of that date. Its much easier and accurate!

• 

• 

SELECT mean("value") FROM "autogen"."kWh" WHERE ("entity_id" = 'shelly_shem_XXXXXX_1_total_consumption') AND $timeFilter GROUP BY time($__interval) fill(none)

Now, using the query as is will not work via command line. We need to change the $timeFilter to a value the terminal can understand.

Since we are interested in knowing the cost since the first day of each month, we can replace it by $(date --date="$(date +'%Y-%m-01') but first lets convert the current query to a escaped string to use with curl.

Use https://www.url-encode-decode.com/ to convert the query to a safe URL query.

SELECT+mean%28%22value%22%29+FROM+%22autogen%22.%22kWh%22+WHERE+%28%22entity_id%22+%3D+%27shelly_shem_XXXXXX_1_total_consumption%27%29+AND+%24timeFilter+GROUP+BY+time%28%24__interval%29+fill%28none%29

Now replace %24timeFilter with time+%3E%3D+$(date --date="$(date +'%Y-%m-01')" +%s)000ms

SELECT+mean%28%22value%22%29+FROM+%22autogen%22.%22kWh%22+WHERE+%28%22entity_id%22+%3D+%27shelly_shem_XXXXXX_1_total_consumption%27%29+AND+time+%3E%3D+$(date --date="$(date +'%Y-%m-01')" +%s)000ms+GROUP+BY+time%28%24__interval%29+fill%28none%29

This is the payload we will use. Let’s try fetching the data using Curl.

The Curl format is the following:

curl -s -G -H "Authorization: Basic [your auth]" "http://[your-server-ip]:[your-server-port]/query?db=homeassistant&q=[url-safe-query]" | jq '[.results[0].series[0].values[][1]]|[first,last]|{"value":(.[1]-.[0])}'

The query we are using here on this example already includes the date range to be from the 1st day of the current month, to the current day.

Using the example above, we get this:

curl -s -G -H "Authorization: Basic <auth>" "http://localhost:8086/query?db=homeassistant&q=SELECT+mean(%22value%22)+FROM+%22autogen%22.%22kWh%22+WHERE+(%22entity_id%22+%3D+'shelly_shem_c7f57c_1_total_consumption')+AND+time+%3E%3D+$(date --date="$(date +'%Y-%m-01')" +%s)000ms+GROUP+BY+time(30m)+fill(none)&epoch=ms" | jq '[.results[0].series[0].values[][1]]|[first,last]|{"value":(.[1]-.[0])}'

And what exactly does this does? It will fetch the first result and last from Influx, and calculate the difference. The result is the difference in Kilowatts

Running the command above should return a valid response. If the response is not ok please check that you have the login data correct.

We have the data.. now let’s put it on Home Assistant

To display the data on Home Assistant we will turn the output above in a sensor that we can use as any other entity. We are aiming for this:

• 

To achieve this you need to edit the sensor.yaml file and insert the following:

- platform: command_line
  command: >
    curl -s -G -H "Authorization: Basic [auth]" "http://localhost:8086/query?db=homeassistant&q=SELECT+mean(%22value%22)+FROM+%22autogen%22.%22kWh%22+WHERE+(%22entity_id%22+%3D+'shelly_shem_c7f57c_1_total_consumption')+AND+time+%3E%3D+$(date --date="$(date +'%Y-%m-01')" +%s)000ms+GROUP+BY+time(30m)+fill(none)&epoch=ms" | jq '[.results[0].series[0].values[][1]]|[first,last]|{"value":(.[1]-.[0])}'
  value_template: |
    {% set kw_total = value_json.value | round(2) %}
    {% set kw_price = 0.1515 %}
    {% set discount = 0.02 %}
    {% set first_kw_with_lower_tax = 103 %}
    {% set contracted_value_with_discount = (31*0.3443)-((31*0.3443)*discount) %}
    {% set DGEG = 0.07 %}
    {% set IEC = kw_total*0.001 %}
    {% set first_kw_with_discount = ((first_kw_with_lower_tax*kw_price)-((first_kw_with_lower_tax*kw_price)*discount)) %}
    {% set excess_kw = kw_total - first_kw_with_lower_tax %}
    {% set excess_kw_with_discount = ((excess_kw*kw_price)-((excess_kw*kw_price)*discount)) %}
    
    {% if kw_total > first_kw_with_lower_tax %}
    
    {{ ((first_kw_with_discount*1.13) + ((excess_kw_with_discount+contracted_value_with_discount+DGEG+IEC)*1.23)) | round(2) }}
    
    {%- else -%}
    
    {{ ((first_kw_with_discount*1.13) + DGEG + IEC) | round(2) }}
    
    {%- endif %}
  name: "EDP Current month cost"
  unit_of_measurement: "€"
  scan_interval: 60
- platform: command_line
  command: >
    curl -s -G -H "Authorization: Basic [auth]" "http://localhost:8086/query?db=homeassistant&q=SELECT+mean(%22value%22)+FROM+%22autogen%22.%22kWh%22+WHERE+(%22entity_id%22+%3D+'shelly_shem_c7f57c_1_total_consumption')+AND+time+%3E%3D+$(date --date="$(date +'%Y-%m-01')" +%s)000ms+GROUP+BY+time(30m)+fill(none)&epoch=ms" | jq '[.results[0].series[0].values[][1]]|[first,last]|{"value":(.[1]-.[0])}'
  value_template: |
    {{ value_json.value | round(2) }}
  name: "EDP Current month consumption"
  unit_of_measurement: "Kw"
  scan_interval: 60

Not that you should update the query with your own!

So what you are adding above is a command that will run by Home Assistant each 60 seconds and that will parse the output thru a template.

This template is unique to my provider, which is EDP (in Portugal) and you will need to adapt for your use case.

The easiest way to do it is inside Developer tab of home assistant where you can test the templates to help you design it.

The template uses Jinja2 which is a fairly common language for templating.

Now you just save and restart Home Assistant for the sensor to appear.

Time to show it on Home Assistant

Edit your Lovelace dashboard and add a Entities card.

Click on Show Code Editor and you can paste this code;

type: entities
entities:
  - entity: sensor.edp_current_month_consumption
    icon: 'hass:transmission-tower'
  - entity: sensor.edp_current_month_cost
    icon: 'hass:cash-usd'
state_color: false
show_header_toggle: true

As soon as you paste this code, you should see the updated output on the right side

• 

Now you can save the card and it’s done, your entity will be always updated every 60 seconds.