Hacking your ComEd electricity bill

Comed’s Hourly Pricing Program is an alternative to traditional fixed pricing, where you pay for electricity at the current hourly market rate instead of a fixed price. Using Home Assistant and an AppDaemon app, it’s possible to use automation to reduce your energy usage during peak times, saving you money. If you have a different power provider than ComEd that offers hourly pricing, the implementation here should work in principle though it may require tweaking depending on the terms of your provider’s service. You may also want to have other things happen in addition to or in place of turning off your thermostat.

The first way to save on your electrical bill in the Hourly Pricing Program is by reducing electricity usage when the current market rate for electricity spikes. The implementation for this is simple. I have a slider in Home Assistant that sets an electricity price threshold. If the current market price for electricity exceeds that threshold, the app turns off my thermostat and sends me a push notification. Once the price drops below the threshold the thermostat is restored to its previous state.

With the simple part out of the way, let’s jump into the more complicated part. One of the biggest factors contributing to your electricity cost in the Hourly Pricing Program is the Capacity Charge. The Capacity Charge is an amount that every ComEd customer pays monthly and allows ComEd to bring on additional energy capacity during peak demand times. Traditional fixed-price customers have their Capacity Charge built in to their fixed electricity rate but Hourly Pricing customers have a separate line item on their bill for it. Your Capacity Charge is a key factor in determining whether the Hourly Pricing Program ends up saving you money or costing you more than you would have paid using traditional fixed pricing.

Your Capacity Charge is recalculated once per year (in June) and you pay that same charge every month for the following year. The Capacity Charge is calculated by multiplying your Capacity Obligation by the Capacity Charge Rate. The Capacity Charge Rate is a fixed number calculated by ComEd (also once per year, in June) and you have no direct control over it. What you do have control over is your Capacity Obligation. This is calculated from your electricity usage during the previous summer. So in June of 2019 your Capacity Obligation will be recalculated based on your usage in the summer (defined as June 1st through September 30th) of 2018.

Your Capacity Obligation is determined by looking at your usage during 10 one hour windows in the previous summer. Five of those hours are the 5 peak load events for ComEd, and the other five are the peak load events for PJM (PJM is basically a regional group made up of multiple power companies such as ComEd). In other words, the calculation looks at how much electricity you used during the 10 times when the total power company demand was greatest last summer. The five ComEd peak hours may or may not be the same as the five PJM peak hours. Your usage during these ten peak hours is averaged together to determine your Capacity Obligation. The time periods begin and end on the hour. In other words, if one of the peak load times occurs at 3:45 PM, your usage for that peak load time will be your usage from 3:00 - 4:00 PM on that day. An important thing to note is that there can only be one peak hour event per day in each of the two categories (ComEd and PJM). In other words, the five actual ComEd peak load hours might all occur one after the other on the same day, however, only the single highest peak load hour from that day will be used.

There is no way to know for certain when the 10 peak hours have occurred until the summer is over. However, monitoring the load data in real time gives us a pretty good way of predicting when a peak hour event is about to happen. To do this we use sensors to track the current load data for ComEd and PJM. I’ve written an AppDaemon app that uses these sensors to help predict when a peak hour event is about to happen and take appropriate action, which for me consists of turning off my thermostat and sending me a push notification alerting me of the possible peak event. Here’s how it works:

First, we have two different SQL sensors, each used once for ComEd and once for PJM, so four in total. These sensors look at the historical data in your Home Assistant database for ComEd and PJM current loads. It’s important to note that you’ll need to have a database purge_interval long enough to not delete any of this year’s load data, so at least 4 months worth of data (June through September). The first SQL sensor grabs the 5th highest peak load value for this season. Because we’re concerned about the top 5 peak load events, we know that if the current load value surpasses at least the current 5th highest load value that it will make it into the top 5. Note that as new peak load events occur, this value will increase. The second SQL sensor grabs the highest load value for the current hour. In other words, if a peak load event occurs at 4:01 PM, the sensor will return that peak load value until that hour has passed and 5:00 PM comes around.

The app uses these four sensors and compares the current real time PJM and ComEd load data with historical load data to determine whether or not a peak load event is likely to occur. The logic contains a few checks. The app does nothing if it’s not summer. It then looks at a hard-coded value (PJM_COMED_MINIMUM_LOAD) and if the load doesn’t meet that minimum threshold the app does nothing. This is because when summer first starts (June 1st) we will have no historical data yet for the current year and any high load would be treated as a potential peak load. After passing these checks, the app calculates the ratio of the current load to the 5th highest peak load so far. Once that ratio hits 99% (this is adjustable using LOAD_RATIO_THRESHOLD) we know a peak load event could be approaching. Then it looks at the derivative (rate of change) of the current load and compares it to our rate thresholds (PJM_COMED_LOAD_RATE_PER_HOUR and PJM_TOTAL_LOAD_RATE_PER_HOUR). When the load is ramping up and the rate of increase is high, the peak event is not imminent. Prior to the peak occurring, the rate of change will slow down and approach 0. Once it hits 0 and begins to go negative, the peak has occurred. However, we need to predict the peak before it happens. For example, if the peak occurs at 3:59PM, we want to reduce our usage for the entire hour starting at 3:00PM. The rate thresholds can be tweaked if you like. If you set the thresholds too high you’ll err on the side of caution and may end up reducing your usage (i.e. not running your air conditioner when it’s really hot out) for several hours before the actual peak event. If you set the rates too low you may end up not predicting the peak event before it occurs and therefore not reducing your usage when you needed to. The rates I chose were based on looking at the load rate of change for the one hour period right before the peak load events occurred for 2018.

Once a peak occurs we remain in reduced usage mode for the entire hour after the peak is over. We consider the peak over once the load rate of change is sufficiently negative. After this, the thermostat is restored to its previous state (i.e. cooling).

Using this system last year I was able to reduce my Capacity Obligation from 3.5 kWh to about 0.7 kWh. Note that this system works much better if your house is well-insulated and able to go for a few hours without running the air conditioner during the heat of summer. If not, you may be miserable implementing this, though you could still use it to send yourself alerts during possible peaks and manually reduce your energy usage.

If you are not yet enrolled in Hourly Pricing, note that you can (and should) call ComEd’s Hourly Pricing department before switching to Hourly Pricing and ask them what your Capacity Charge would be if you were to switch. You can also check your Capacity Charge on by entering your account number on their website here. If you had a smart meter last summer, your Capacity Charge will be calculated based on your usage during last summer’s peak load times, just as if you had been enrolled in Hourly Pricing. You may determine that switching would not be to your advantage at this time. However, you could run this app this summer to keep your peak load usage low while still on traditional fixed pricing, and then switch to Hourly Pricing for next summer, taking advantage of the lower Capacity Charge you’ll see in June when it’s recalculated.

NOTE: This was originally written for a Nest thermostat, hence the use of the term ‘Eco mode’. Instead of turning the thermostat off during times of high load or high price, I would just switch it into Eco mode. I no longer have a Nest so I now just switch my thermostat off, but I still refer to this as Eco mode in the script. I use the eco_activated_due_to_price input_boolean as a flag to know whether or not ‘Eco mode’ was activated due to this script vs. someone manually turning off the thermostat.

For reference, the list of ComEd coincident peaks is here, and the list of PJM coincident peaks is here.

Hopefully this is useful for someone else. Questions / comments / suggestions welcome.

PJM Sensor (place in custom_components/pjm directory)

AppDaemon App

Home Assistant configuration:

Variables:

input_boolean:
  # Used to keep track of whether Eco mode was activated by home assistant due to high prices
  eco_activated_due_to_price:
    name: Eco Mode Activated Due to High Electricity Price
    initial: off

input_select:
  # Used as a variable to store the current thermostat mode when we switch to Eco mode
  # so that we can restore it to this state later
  saved_thermostat_mode:
    options:
      - 'none'
      - 'off'
      - 'heat'
      - 'cool'
      - 'auto'
      - 'eco'
    initial: 'none'

input_number:
  # This is used to set what electricity price threshold activates Nest eco mode
  comed_price_threshold:
    name: ComEd High Electricity Price Threshold
    initial: 19.0
    min: 10.0
    max: 35.0
    step: 0.1

Sensors:

sensor:
  - platform: template
    sensors:
      nest_home_away:
        friendly_name: "Nest Home/Away State"
        value_template: "{% if is_state_attr('climate.kitchen', 'away_mode', 'off') %}home{% else %}away{% endif %}"

  - platform: comed_hourly_pricing
    monitored_feeds:
      - type: five_minute
      - type: current_hour_average
      - type: five_minute
        offset: 11.2
        name: "ComEd Total 5 Minute Price"
      - type: current_hour_average
        offset: 11.2
        name: "ComEd Total Current Hour Average Price"

  - platform: pjm
    monitored_variables:
      - type: instantaneous_total_load
      - type: instantaneous_zone_load
        zone: 'COMED'

  - platform: sql
    db_url: postgres://@/homeassistant
    queries:
      # Get the 5th highest PJM hourly total load value since June 1st of this year
      - name: 'PJM Total Load High Marker'
        query: >
                SELECT last_updated, cast(state AS integer) AS state FROM (
                    SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY date_trunc('day', last_updated)
                      ORDER BY cast(state as integer) DESC) AS _rn
                    FROM states
                    WHERE entity_id = 'sensor.pjm_total_load_current_hour_high'
                    AND state != 'unknown' AND state != ''
                    AND last_updated > make_date(cast(extract(year FROM (date_trunc('year', now() - interval '5 month'))) AS integer), 6, 1)
                    AND last_updated < make_date(cast(extract(year FROM (date_trunc('year', now() - interval '5 month'))) AS integer), 10, 1)
                    ) AS _max
                WHERE _rn = 1
                ORDER BY cast(state AS integer) desc
                LIMIT 1 OFFSET 4;
        column: 'state'
        unit_of_measurement: 'MW'

      # Get the 5th highest Comed hourly load value since June 1st of this year
      - name: 'PJM Comed Load High Marker'
        query: >
                SELECT last_updated, cast(state AS integer) AS state FROM (
                    SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY date_trunc('day', last_updated)
                      ORDER BY cast(state as integer) DESC) AS _rn
                    FROM states
                    WHERE entity_id = 'sensor.pjm_comed_load_current_hour_high'
                    AND state != 'unknown' AND state != ''
                    AND last_updated > make_date(cast(extract(year FROM (date_trunc('year', now() - interval '5 month'))) AS integer), 6, 1)
                    AND last_updated < make_date(cast(extract(year FROM (date_trunc('year', now() - interval '5 month'))) AS integer), 10, 1)
                    ) AS _max
                WHERE _rn = 1
                ORDER BY cast(state AS integer) desc
                LIMIT 1 OFFSET 4;
        column: 'state'
        unit_of_measurement: 'MW'

      # Get the highest PJM total load value from the last hour, starting on the hour,
      # but if we don't have data yet for this hour, get the last value from the previous hour
      # NOTE: The SQL sensor only works if the query starts with the word 'select' so we have
      # to add in a dummy select.  It also adds a LIMIT clause if the query doesn't contain
      # the uppercase string 'LIMIT'
      - name: 'PJM Total Load Current Hour High'
        query: >
                SELECT null;
                WITH t1 AS
                  (SELECT MAX(CAST(state AS integer)) AS state
                    FROM states
                    WHERE entity_id = 'sensor.pjm_instantaneous_total_load'
                      AND last_updated >= date_trunc('hour', now())
                      AND state != 'unknown'
                      AND state != ''
                  )
                SELECT t1.*
                  FROM t1
                  UNION ALL
                    (SELECT CAST(state AS integer) AS state2
                      FROM states
                      WHERE entity_id = 'sensor.pjm_instantaneous_total_load'
                        AND last_updated < date_trunc('hour', now())
                        AND state != 'unknown'
                        AND state != ''
                        AND NOT EXISTS
                          (SELECT state
                            FROM t1
                            WHERE state > 0
                          )
                      ORDER BY last_updated DESC
                      LIMIT 1
                    );
        column: 'state'
        unit_of_measurement: 'MW'

      # Get the highest PJM Comed load value from the last hour, starting on the hour,
      # but if we don't have data yet for this hour, get the last value from the previous hour
      # NOTE: The SQL sensor only works if the query starts with the word 'select' so we have
      # to add in a dummy select.  It also adds a LIMIT clause if the query doesn't contain
      # the uppercase string 'LIMIT'
      - name: 'PJM Comed Load Current Hour High'
        query: >
                SELECT null;
                WITH t1 AS
                  (SELECT MAX(CAST(state AS integer)) AS state
                  FROM states
                  WHERE entity_id = 'sensor.pjm_instantaneous_zone_load_comed'
                    AND last_updated >= date_trunc('hour', now())
                    AND state != 'unknown'
                    AND state != ''
                  )
                SELECT t1.*
                FROM t1
                UNION ALL
                  (SELECT CAST(state AS integer) AS state2
                  FROM states
                  WHERE entity_id = 'sensor.pjm_instantaneous_zone_load_comed'
                    AND last_updated < date_trunc('hour', now())
                    AND state != 'unknown'
                    AND state != ''
                    AND NOT EXISTS
                      (SELECT state
                      FROM t1
                      WHERE state > 0
                      )
                  ORDER BY last_updated DESC
                  LIMIT 1
                  );
        column: 'state'
        unit_of_measurement: 'MW'
4 Likes

Along similar lines, I have a Philips Hue light bulb specifically to monitor my hourly pricing. I have it coded using Node-RED to change color whenever the price changes. I’ve got the bulb set up in my kitchen in a corner (a central location in our home during the day).

My flow also accounts for day/night, and will dim the bulb after 11pm using the light-scheduler component and using a brightness variable.

image

I know that if the bulb turns orange or red, the price is higher than what I would have paid otherwise (about 6-7c/kWh) and so I start turning things off. However, I don’t automate this because on certain days I’m willing to pay the higher cost (e.g. if it’s 100°F out, there’s no way I’m turning off the A/C!).

Additionally, if you have a kWh meter, you can set up a template to display the running cost of whatever is plugged into the meter (e.g. the Belkin WeMo Insight switch). For example, I have a TV plugged into it and with a bit of math (detected kW current * current ComEd hourly price), I can show the current cost (and/or aggregated daliy cost) to run the TV.

image

      kitchen_tv_operating_cost:
        friendly_name: "Kitchen TV Operating Cost"
        unit_of_measurement: 'c'
        icon_template: 'mdi:television'
        value_template: >-
          {% if is_state('switch.kitchen_tv', 'on') %}
            {{ (float((state_attr('switch.kitchen_tv', 'current_power_w') / 1000)) * float(states('sensor.comed_current_hour_average_price'))) | round(4) }}
          {% else %}
            {{ float(0) }}
          {% endif %}

There’s tons of cool stuff you can do on Hourly Pricing - and you’ll usually save money, too! (I’ve saved about $75 this year so far!)

1 Like

Thanks for sharing. I’ve often thought about how to implement some kind of visible indicator to show the current electricity price but never thought of using a colored bulb. Great idea!

This is awesome! Excellent write up! I will be diving into this over the next few weeks. Got an EV and my electric bill is soaring.

Finally getting around to doing this! One issue I’m having, I can’t get the pjm sensor to work via the custom component install. sensor.py is sitting in custom_components, but HA can’t find it. Something tells me the custom component installation needs to be tweaked… Any ideas?

In my custom_components directory, I have a pjm subdirectory, and in it I have:

__init__.py
manifest.json
sensor.py

Do you have all three of those? I believe at some point the HA requirements for custom components changed and I had to add those other two files.

That was it! Thank you! I was able to manually piece it together and is working dandy. BTW, I found the activity on git hub where you were working to make this an integration part of all home assistant. Though it died off?

I’m working on getting your setup going - just switched to hourly pricing and my capacity obligation is almost at 7 kWh! Had a pregnant wife last summer so AC was a must. Trying to manage it better this summer. Instead of using the app daemon, i’m going to try to get the automation piece (checking current loads, comparing against peaks, turning on/off HVAC, notifications) running in node red as that’s where all my other automations run. Should be a fun endeavor!

Thanks again for this spectacular write up. Your explanations along with the code were done extremely well.

No problem, I’m glad you were able to use this. If I recall correctly I think I had submitted the PJM sensor to be included in HA, but the devs wanted me to break out the code into a separate repository and library, even though it just uses a very simple web scraper. Python is not a strength of mine and I didn’t have the time or desire to do that so I abandoned the pull request.

Wow, 7kWh! Do you have two central ACs? My wife was pregnant the first summer I started running this, and there were a few times she definitely wasn’t very happy with me! But our basement always stays pretty cool so we got through it.

Ha - yes we do have 2 ACs. Older units on an older house so they use a bit - I think they are 8 tons in total. Unfortunately our basement isn’t finished so we can’t do too much down there - project for another day. Another large factor was we got an EV last spring so I was charging that at misc times during the day which pulls a ton of current (wish I knew about capacity obligations!).

Totally get it re the PJM integration. I’ve always wanted to build an integration but with two young kids at the house I find it hard to do much these days. Maintaining a code base can be a bit of work.

Thanks again - I’ll report back with any success! Might be a little bit - my main goal was to get all the sensors in to at least start aggregating data.

This is great work. Exactly what I was looking for to integrate in my existing energy management stuff. I’ve had to modify the SQL sensors a bit due to some differences in query language between PostgreSQL & MySQL/MariaDB? Don’t have them all working yet, but the instantaneous load is working which is most important. First time using the SQL sensor, so not sure.Really useful. Thanks.

Maybe it is because I am new to HA, but I would sure love to see a procedural as to the steps to do to integrate this. Could you talk me through it like I was 5yo ? I have the latest version of HA, 0.114.3 but don’t understand what to do with your variables or sensor code above. I am using stock Lovelace, fwiw.

@Mil I also struggled getting these SQL queries updated to work with MariaDB. I’ve only really used postgres but recently switched over to MariaDB since it’s an add-on and works a little more seamlessly for my setup. I think (major empahsis on think) that I got them working. I modified things a bit to 1) work with MySQL and 2) work with some of the nuances with the SQL sensor in HA. Have a look at the below and I welcome any feedback.

MariaDB Queries:

  - platform: sql
    db_url: !secret mariadb_server_cred
    queries:
      # Get the 5th highest PJM hourly total load value since June 1st of this year
      - name: "PJM Total Load High Marker"
        query: >
          select last_updated, cast(state as integer) as state from (
            select *,
            row_number() over (partition BY date_format(last_updated,'%Y-%m-%d')
                order by cast(state as integer) desc) as _rn
            from states
            where entity_id = 'sensor.pjm_total_load_current_hour_high'
            and state != 'unknown' and state != ''
            and last_updated > date_format(date_sub(current_date(),interval 5 month),'%Y-06-01')
            and last_updated < date_format(date_sub(current_date(),interval 5 month),'%Y-10-01')
            ) as _max
          where _rn = 1
          order by cast(state as integer) desc
          limit 1 offset 4
        column: "state"
        unit_of_measurement: "MW"

      # Get the 5th highest Comed hourly load value since June 1st of this year
      - name: "PJM Comed Load High Marker"
        query: >
          select last_updated, cast(state as integer) as state from (
            select *,
            row_number() over (partition BY date_format(last_updated,'%Y-%m-%d')
                order by cast(state as integer) desc) as _rn
            from states
            where entity_id = 'sensor.pjm_comed_load_current_hour_high'
            and state != 'unknown' and state != ''
            and last_updated > date_format(date_sub(current_date(),interval 5 month),'%Y-06-01')
            and last_updated < date_format(date_sub(current_date(),interval 5 month),'%Y-10-01')
            ) as _max
          where _rn = 1
          order by cast(state as integer) desc
          limit 1 offset 4
        column: "state"
        unit_of_measurement: "MW"

      # Get the highest PJM total load value from the last hour, starting on the hour,
      # but if we don't have data yet for this hour, get the last value from the previous hour
      # NOTE: The SQL sensor only pulls in the last value (row) of data on a query. Since UNION
      # statements cause nulls to pull in if no results are included and its other nuances, an
      # additional UNION with dummy data is included to get the order correct.
      - name: "PJM Total Load Current Hour High"
        query: >
          select state
          from (
              select null as state
            union (
              select cast(state AS integer) AS state
              from states
              where entity_id = 'sensor.pjm_instantaneous_total_load'
              and last_updated < date_format(utc_timestamp(),'%Y-%m-%d %H:00:00')
              and state != 'unknown'
              and state != ''
              order by last_updated desc
              limit 1
            )
            union (
              select max(cast(state AS integer)) AS state
              from states
              where entity_id = 'sensor.pjm_instantaneous_total_load'
              and last_updated >= date_format(utc_timestamp(),'%Y-%m-%d %H:00:00')
              and state != 'unknown'
              and state != ''
            )
          ) as t1
        column: "state"
        unit_of_measurement: "MW"

      # Get the highest PJM Comed load value from the last hour, starting on the hour,
      # but if we don't have data yet for this hour, get the last value from the previous hour
      # NOTE: The SQL sensor only pulls in the last value (row) of data on a query. Since UNION
      # statements cause nulls to pull in if no results are included and its other nuances, an
      # additional UNION with dummy data is included to get the order correct.
      - name: "PJM Comed Load Current Hour High"
        query: >
          select state
          from (
              select null as state
            union (
              select cast(state AS integer) AS state
              from states
              where entity_id = 'sensor.pjm_instantaneous_zone_load_comed'
              and last_updated < date_format(utc_timestamp(),'%Y-%m-%d %H:00:00')
              and state != 'unknown'
              and state != ''
              order by last_updated desc
              limit 1
            )
            union (
              select max(cast(state AS integer)) AS state
              from states
              where entity_id = 'sensor.pjm_instantaneous_zone_load_comed'
              and last_updated >= date_format(utc_timestamp(),'%Y-%m-%d %H:00:00')
              and state != 'unknown'
              and state != ''
            )
          ) as t1
        column: "state"
        unit_of_measurement: "MW"

@10Gauge - it’s on my todo list to make an additional guide to implement @Joe248’s awesome work. Unforch I don’t have an ETA but will share it here.

Nice. I’ve never used MariaDB but if you’ve got it working then good job. Curious if you ended up using any of this last summer to get your capacity charge down?

I’ve been spoiled by postgres hah! I can’t say I recommend MariaDB, but it is much more light weight apparently. It’s not nearly as feature rich as postgres.

I used this setup somewhat - didn’t actually get around to implementing automation on the peak load events, instead I was just monitoring electricity price and watching out for peak load events manually. Automations were in place to kill the AC when it got too expensive and to pre-cool the house in the AM.

Overall though, I’ve been able to drop my capacity obligation from 4.8kWh to 2.25kWh which is great. Once I get around to the fully automated setup, I think I could even get it lower.

You might already be doing this, but putting those sensors together in a graph is an easy way to tell visually if we might be approaching a peak load event, if you’re doing it manually.

Screen Shot 2021-07-28 at 2.34.41 PM

This is a great idea! I made some graphs in Grafana but didn’t think to add the high marker. Thanks for the idea.

One other request - are you able to provide the top 5 peak load days so far this summer for both PJM total and ComEd zone? I didn’t have the sensor working right after setting up MariaDB and wasn’t recording any data.

Sure, here you go:

PJM:

Load of 148003 at 2021-06-29 15:43:19.680885-05
Load of 147208 at 2021-07-06 15:42:03.350556-05
Load of 143915 at 2021-07-07 13:42:10.122867-05
Load of 141964 at 2021-06-28 16:53:43.066782-05
Load of 141264 at 2021-07-27 15:58:29.757299-05

Comed:

Load of 19505 at 2021-07-06 16:41:33.513595-05
Load of 19334 at 2021-07-28 16:46:41.920905-05
Load of 19031 at 2021-07-26 17:09:51.542947-05
Load of 18693 at 2021-06-11 14:06:54.672814-05
Load of 18604 at 2021-06-10 16:48:03.053914-05
1 Like

‘’’
platform: sql
db_url: mysql://homeassistant:[email protected]/historical_sensors?charset=utf8mb4
queries:
# Get the 5th highest PJM hourly total load value since June 1st of this year
- name: ‘PJM Total Load High Marker’
query: >
SELECT last_updated, cast(state as integer) as state,
ROW_NUMBER() OVER (PARTITION BY dayofmonth(last_updated)
ORDER BY cast(state as integer) DESC) AS _rn
FROM states
WHERE entity_id = ‘sensor.pjm_total_load_current_hour_high’
AND state != ‘unknown’ AND state != ‘’
AND last_updated > makedate(year(adddate(UTC_TIMESTAMP(), interval -5 month)),dayofyear(‘2020-06-01’))
AND last_updated < makedate(year(adddate(UTC_TIMESTAMP(), interval -5 month)),dayofyear(‘2020-10-01’))
order by cast(state as integer) DESC
LIMIT 1 OFFSET 4;
column: ‘state’
unit_of_measurement: ‘MW’

  # Get the 5th highest Comed hourly load value since June 1st of this year
- name: 'PJM Comed Load High Marker'
  query: >
          SELECT last_updated, cast(state as integer) as state,
            ROW_NUMBER() OVER (PARTITION BY dayofmonth(last_updated)
                  ORDER BY cast(state as integer) DESC) AS _rn
          FROM states
          WHERE entity_id = 'sensor.pjm_comed_load_current_hour_high'
          AND state != 'unknown' AND state != ''
          AND last_updated > makedate(year(adddate(UTC_TIMESTAMP(), interval -5 month)),dayofyear('2020-06-01'))
          AND last_updated < makedate(year(adddate(UTC_TIMESTAMP(), interval -5 month)),dayofyear('2020-10-01'))
          order by  cast(state as integer) DESC 
          LIMIT 1 OFFSET 4;    
  column: 'state'
  unit_of_measurement: 'MW'
- name: 'PJM Total Load Current Hour High'
  query: >
            SELECT MAX(CAST(state AS integer)) AS state
                FROM states
                WHERE entity_id = 'sensor.pjm_instantaneous_total_load'
                  AND last_updated >= timestamp(date_format(UTC_TIMESTAMP(), '%Y-%m-%d'), maketime(hour(UTC_TIMESTAMP()), 00, 00))
                  AND state != 'unknown'
                  AND state != ''
            union ALL
               SELECT MAX(CAST(state AS integer)) AS state
                FROM states
                WHERE entity_id = 'sensor.pjm_instantaneous_total_load'
                  AND last_updated >= timestamp(date_format(UTC_TIMESTAMP(), '%Y-%m-%d'), maketime(hour(UTC_TIMESTAMP()) - 1, 00, 00))
                  AND state != 'unknown'
                  AND state != ''
                  LIMIT 1;  
  column: 'state'
  unit_of_measurement: 'MW'
- name: 'PJM Comed Load Current Hour High'
  query: >    
            SELECT MAX(CAST(state AS integer)) AS state
              FROM states
              WHERE entity_id = 'sensor.pjm_instantaneous_zone_load_comed_zone'
                AND last_updated >= timestamp(date_format(UTC_TIMESTAMP(), '%Y-%m-%d'), maketime(hour(UTC_TIMESTAMP()), 00, 00))
                AND state != 'unknown'
                AND state != ''
            UNION ALL
            SELECT MAX(CAST(state AS integer)) AS state
              FROM states
              WHERE entity_id = 'sensor.pjm_instantaneous_zone_load_comed_zone'
                AND last_updated >= timestamp(date_format(UTC_TIMESTAMP(), '%Y-%m-%d'), maketime(hour(UTC_TIMESTAMP()) - 1, 00, 00))
                AND state != 'unknown'
                AND state != ''              
              LIMIT 1;
  column: 'state'
  unit_of_measurement: 'MW'

‘’’
take a look at mine… Maria DB

1 Like

@joe248 … Thanks for the all your work… I am in the negative for this summer… Started in June with RTP and so far based upon RTP vs Fixed I have lost almost $13 dollars for the summer… LOL… My Capacity Charge is 5.65 KWH and that is killing the savings potential… Did you see the same when you lower yours the first year?

Really need to have the Capacity at my baseline which is about .4 Kwh… Then I could be a savings of almost 100 this summer… LOL Capacity Charge at your minimum is key for hacking COMED… Maybe next year…

But in fairness I have saved almost 20 bucks with the Peak Time Savings integration to shut down my house. I have a very good friend that works for ComEd in the energy savings program and I thought my Capacity didn’t make sense from 2020 so I got a detail report of the calculation if anyone is interested…