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

This solution will require:

### 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.series.values[]]|[first,last]|{"value":(.-.)}'``

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.series.values[]]|[first,last]|{"value":(.-.)}'``

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.series.values[]]|[first,last]|{"value":(.-.)}'
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.series.values[]]|[first,last]|{"value":(.-.)}'
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

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