Shared - how i calculated projected cost for the month

I was trying to work out best way to use historical data i had for my energy usage/cost sensors to forecast projected costs for the current month, and came up with the following. (the graph only shows part of a month as i started collecting data a few weeks back)

So, i have a Sensor that records energy usage via EmonPi, i then do some calculates to get a daily cost Sensor…

  • platform: template
    sensors:
    energy_import_cost_2022_3:
    friendly_name: ‘Import Cost 2022-2023’
    icon_template: mdi:home-import-outline
    unit_of_measurement: ‘£’
    value_template: “{{ (states(‘sensor.energy_imported_daily_kwh’) | float * 0.33018 + 0.45769) | round(2) }}”

i then created an SQL sensor to calculate for the current month, what the average cost is per day

SELECT *, AVG(dailymax) AS month_average
FROM (
SELECT id,metadata_id,date(created) AS date, strftime(’%Y-%m’, created) year_month, strftime(’%m’,created) month, max(mean) AS “dailymax”, strftime(’%m’,date(‘now’)) AS todays_month
FROM “statistics”
WHERE metadata_id=107 AND month=todays_month
GROUP BY date
ORDER BY created DESC) daily_max;

the metadata_id=107 is the unique id for the sensor i’d created, as recorded in the SQlite database

now i know for the current month what my average daily costs is, i just use that to multiple by number of days in the month. first work out number of days in current month

  • platform: template
    sensors:
    days_in_month:
    value_template: >
    {% if now().month in [1,3,5,7,8,10,12] %}
    31
    {% elif now().month in [4,6,9,11] %}
    30
    {% elif now().month == 2 and ((now().year-2000) % 4 > 0) %}
    28
    {% elif now().month == 2 and ((now().year-2000) % 4 == 0) %}
    29
    {% endif %}

then use this to make the forecasted figure for the whole month

  • platform: template
    sensors:
    monthly_energy_cost_forecast:
    friendly_name: “Monthly Energy Cost Forecast”
    unit_of_measurement: ‘£’
    value_template: >
    {{ (states(‘sensor.sql_months_average_cost’)|float * states(‘sensor.days_in_month’)|float) |round(2) }}

hope this helps out others who maybe want to check historical data to calculate projections on costs etc.

thanks for sharing. Do you have access to usage data for a longer period, say 24 months. I assume not via HA (as you say you only started collecting a few weeks ago), but via your bills perhaps?

I’ve been looking to calculate potential usage and have been using the total monthly gas and electricity usage for the last 18 months (basically, since I got a smart meter, prior to that I was bad at taking readings so monthly values would fluctuate wildly).

In November last year, I used three times as much as gas as October, it doubled in December (everyone home and lots of Christmas cooking), then dropped back a bit in January. Now my draughty old house takes a lot of heating so YMMV, but you might need to think about developing this further to take things such as seasons into account.

Hi, i have a lot of manual meter readings over a number of years, but these are from so far three different properties, my job (as a Minister) has meant a number of moved, but since moving back to our old home, and it now has solar panels, i’ve been trying to use HA to help utilise the solar energy as best, and as i’ve played i’ve also started to add in meter/statistical stats, some of which are wrong becuase i set sample rates to low etc, hence the need to take the raw data i do have in HA and use SQL against it to make my own AVG stat etc.