Hacking your ComEd electricity bill

I’ve revised your sensor.py to include the 5-min LMP average, and 5-min load forecast.

The 5 min load forecast can be used predict the peak. It’s updated every 5 min and extends out 2 hrs. You can then run an automation that triggers if the peak is predicted to occur within the next hour.

You should be able to pass any utility zone, so it’s now applicable to any utility within PJM.

Here’s the config.yaml:

sensor:
  - platform: pjm
    monitored_variables:
      - type: instantaneous_total_load
      - type: total_load_forecast
      - type: total_short_forecast
      - type: instantaneous_zone_load
        zone: "COMED"
      - type: zone_load_forecast
        zone: "COMED"
      - type: zone_short_forecast
        zone: "COMED"
      - type: zonal_lmp
        zone: "COMED"

  - platform: template
    sensors:
      current_time_plus_one_hour:
        value_template: >
          {{ (as_timestamp(now()) + 3600) | int }}
        friendly_name: "Next Hour" 

Sensor.py

Was also having trouble with getting the sql to work - kept getting invalid errors using the SQL integration in the web UI. These ended up working for me (just tweak to point to the ComEd or PJM load sensor to create the hourly peak, and point to the new hourly peak SQL sensor to develop a 5th coincident peak):

Hourly Peak

FROM (
  SELECT CAST(state AS INTEGER) AS state, 
         ROW_NUMBER() OVER (ORDER BY state DESC) as rownum
  FROM states
  WHERE metadata_id = (
    SELECT metadata_id 
    FROM states_meta 
    WHERE entity_id = 'sensor.comed_zone_load'
  )
  AND state != 'unknown'
  AND state != ''
  AND strftime('%Y-%m-%d %H', last_updated_ts, 'unixepoch') = strftime('%Y-%m-%d %H', 'now')
) AS ranked
WHERE rownum = 1;

and 5th Coincident Peak

WITH daily_peaks AS (
  SELECT 
    date(last_updated_ts, 'unixepoch') AS date,
    state,
    MAX(CAST(state AS INTEGER)) AS peak_value
  FROM states
  WHERE metadata_id = (
    SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.pjm_total_load_current_hour_high'
  )
  AND state NOT IN ('unknown', '', 'unavailable')
  AND state GLOB '[0-9]*'  -- Ensures the state contains only digits
  AND last_updated_ts >= strftime('%s', 'now', '-4 months')
  GROUP BY date
),
ranked_peaks AS (
  SELECT state, peak_value, date,
         ROW_NUMBER() OVER (ORDER BY peak_value DESC) AS rank
  FROM daily_peaks
)
SELECT peak_value
FROM ranked_peaks
WHERE rank = 5