The integration was working fine until yesterday when I suddenly got the following error:
curl: (35) error:0A000412:SSL routines::sslv3 alert bad certificate
Anyone seen this error?
Removed the device, recreated the certs, added the device again, and now it works. Hope this is not a monthly exercise…
It get’s a little complicated with 3 tier on-demand pricing and different summer/winter pricing. I had to set up quite a few automations, Node-Red flows, helpers, and template sensors but I was able to get it fully automated to calculate montly electric bill taking into account the 3 tier pricing and it will automatically update the rates when they change from summer to winter.
My finished dashboard looks like this:
Monthly power costs is only on/mid/off peak energy cost. The “This Month Electric Bill” takes all of that into account along with the other fees and costs that are based on usage.
Energy dashboard shows that it does accurately capture the 3 tier pricing:
I’ve checked it against Xcel’s site and it is spot on.
Mostlychris video on YT covers it it pretty good detail on how to set up most of that. I went a bit further with holidays and adding in the other monthly costs.
1 Like
If it isn’t too much work, can you tell us what automations you did? The values and the times, specifically?
I’m guessing you made an input helper to store the value. Then a boolean to save the summer/winter status. And then an automation that run twice a day to set the cost based on the summer/winter boolean?
I can post up the details tonight. It was quite a bit so I’ll have to think back on the order I did everything.
I’ll try to be as detailed as possible here with my current set up. This is assuming you already have your meter sensors set up as described earlier in this thread. I have 2 set up: 1 for instantaneous usage and 1 for total kWh usage.
Set up Time of Use Helpers
I am billed for on, off, and mid peak usage.
Go to Settings > Devices & Services > Helpers and select Create Helper. Then select Utility Meter.
First helper will be what I called Xcel Meter. For the sensor, you want to select the total kWh usage sensor. I’m billed monthly on the 16th so for the reset cycle I select monthly and for offset days I use 16. For supported tarrifs, since I have 3 I typed in On Peak, Off Peak, and Mid Peak. HA will create new helpers for these 3 tarrifs. I turned periodic resetting off since this value is always increasing and I left Delta Values off since mine are absolute values. Once you create that helper, HA will create select helper and 3 sensor time of use helpers.
The next helper is total power consumption and it’s set up exactly like the one above except leave tarriffs blank.
Since the on peak and mid peak rates differ in summer and winter, I had to add 2 input_number helpers that will be used to set the rate depending on the time of the year. I set the minimum to whatever the lowest possible rate for that time of use is and the maximum to the highest possible cost. Unit of measurement is USD/kWh and for step size I used 0.00001. I show the Node Red flow I used later to set this number automatically.
At this point you should have a select helper, 3 time of use helpers (or whatever number you set up), a total power consumption helper, and 2 time of use rate number helpers.
Set up Energy Meter
Reboot HA before starting if the sensors arent showing up.
Go to your energy dashboard and under Grid consumption select Add Consumption. You’ll have to do this for each time of use helper. So for me, I added 3: on peak, off peak, and mid peak.
My off peak rate is the same all year so for that one I just entered in the static price that I got my Xcel’s rate schedule.
For on peak and mid peak, those rates are different in summer and winter so for those 2 I selected to use an entity with current price and for the entity I selected the number helper I created for each.
Automations
Before I create the automations to set which time of use to use, I need to set up 2 other automations for holidays. For this one, I looked in Xcel’s rate schedule and found which holidays they observe and on those days we will be using off peak pricing no matter what day of the week it is. So create an Holiday input boolean helper first. Then for the 1st automation (I called it Turn Holiday On), I want to create one that will turn that input boolen on if it is a holiday. Trigger is fixed time at 0:00:00. There are 10 holidays so for Conditions I used an Or condition and set up template conditions for each holiday. For example, New Years Day:
- condition: template
value_template: >
{% set n = now() %}
{{ n.year == 2023 and n.month == 1 and n.day == 1 }}
And the Action is to use Input boolean: Turn On service with the input_boolean.holidays as the target.
Then I created a Turn off Holiday automation that is the same as the turn on automation except that the Conditions are a Not condition.
Now I create 3 automations that will set the select sensor to either on, off, or mid peak depending on the time of day.
Off peak is set up with a fixed time trigger at 7pm. For the action, Service is set to Select: Select; target is set to select.xcel_meter; and Option is set to Off Peak.
Mid peak is set up with a fixed time trigger at 1pm but it has an And condition and they are that the holiday input boolean is off and a time condition with only weekdays selected. In other words, the automation will only set select.xcel_meter to mid peak if it’s not a holiday and it is Monday - Friday. All other times, this automation will not run.
On peak is set up the same as mid peak except the trigger time is 3pm.
So for automations we have this:
Node Red
I mentioned earlier that on and mid peak pricing changes depending on the time of the year. To handle this, I set up a Node Red flow that will set the input_number entity to the correct price. That looks like this:
Big Timer for winter TOU is set to include special day Oct 1st. That’s when Xcel changes to Winter prices. And summer TOU is set to include special day June 1st.
The call service is set up like this:
At this point, everything is pretty much automated. The TOU will automatically change at the correct time of day and remain on off peak pricing during weekends and holidays.
But I wanted to be able to display the current day energy cost and monthly energy cost. To do that, you have to create a few more helpers. You’ll need monthly and daily utility meter helpers for each TOU. These are set up much like the total consumption helper we created earlier except for the input sensor you’ll want to select sensor.xcel_meter_mid_peak_cost (may differ depending on your naming convention). But these are sensors that HA automatically created when you set up the Energy dashboard. These are set up with periodic reseting turned on. For the monthly ones you’ll use the same offset days as the previous ones you created and tarriffs will be left blank. For the daily ones, the reset cycle will be set to daily and offset is zero.
Now that we have daily and monthly sensors set up for each TOU, we can create template sensors to calculate total monthly costs. And for these I’m just going to copy/paste from my sensors.yaml file. It’s pretty self explanatory. Monthly energy cost is just adding up the 3 monthly helpers we just created. Same with daily energy costs. The GRSA E, EGCRR, and electric adjustments and fees that are defined in the Xcel Rate Schedule and are based on monthly kWh usage. For those I’m using the total power consumption sensor that we created that keeps track of monthly kWh usage and I’m multiplying that by the rate from the rate schedule. Billed electric costs is summing all of those plus the fixed base rate of $7.94.
#Template sensor to get monthly energy cost
edgewood_total_energy_cost:
unique_id: edgewood_total_energy_cost
friendly_name: "Edgewood Total Energy Cost"
value_template: "{{ (states('sensor.monthly_on_peak_cost') | float + states('sensor.monthly_mid_peak_cost') | float + states('sensor.monthly_off_peak_cost') | float) | round(2) }}"
unit_of_measurement: "$"
#Template sensor to get daily energy cost
edgewood_daily_energy_cost:
unique_id: edgewood_daily_energy_cost
friendly_name: "Edgewood Daily Energy Cost"
value_template: "{{ (states('sensor.daily_on_peak_cost') | float + states('sensor.daily_mid_peak_cost') | float + states('sensor.daily_off_peak_cost') | float) | round(2) }}"
unit_of_measurement: "$"
#Template sensor for GRSA E
edgewood_grsa_e_cost:
unique_id: edgewood_grsa_e_cost
friendly_name: "GRSA E Cost"
value_template: "{{ ((states('sensor.total_power_consumption') | float * 0.01271) | float) | round(2) }}"
unit_of_measurement: "$"
#Template sensor for EGCRR
edgewood_egcrr_cost:
unique_id: edgewood_egcrr_cost
friendly_name: "EGCRR Cost"
value_template: "{{ ((states('sensor.total_power_consumption') | float * 0.002390) | float) | round(2) }}"
unit_of_measurement: "$"
#Template sensor for Electric Adjustments
edgewood_elec_adj_cost:
unique_id: edgewood_elec_adj_cost
friendly_name: "Electric Adjustment Cost"
value_template: "{{ ((states('sensor.edgewood_total_energy_cost') | float * 0.5222) | float) | round(2) }}"
unit_of_measurement: "$"
#Template sensor for Billed Electric Cost
edgewood_billed_elec_cost:
unique_id: edgewood_billed_elec_cost
friendly_name: "Billed Electric Cost"
value_template: "{{ (states('sensor.edgewood_total_energy_cost') | float + states('sensor.edgewood_egcrr_cost') | float + states('sensor.edgewood_grsa_e_cost') | float + states('sensor.edgewood_elec_adj_cost') | float + 7.94 | float) | round(2) }}"
unit_of_measurement: "$"
I think I covered everything. I did this over a couple days so it’s possible I got something out of order as I was typing it out now but it should be close enough to get you going.
3 Likes
This new meter is giving me fits! I have everything set up and can get data from the device, but it regularly shuts down the port for varying long periods of time. I am in the Denver area and understand that meters deployed here still have metering agent version 1. In this version there are only 3 values to be retrieved: 1 - Instantaneous Demand, 2 - Current Summation Received and 3 - Current Summation Delivered. If the customer does not have solar then Reading #2 will remain at 0 and Reading #1 will always be a positive value. I do have solar, so my code for Reading #1 also retrieves the sign of the value.
- platform: command_line
unique_id: xcel_meter_power
name: "Smart Electric Meter Power"
command: "OPENSSL_CONF=/config/xcelcerts/openssl.conf /usr/bin/curl --ciphers ECDHE-ECDSA-AES128-CCM8 --insecure --url https://192.168.0.12:8081/upt/1/mr/1/r --cert /config/xcelcerts/cert.pem --key /config/xcelcerts/key.pem 2>&1 | grep -o '<value>.*</value>' | grep -Eo '[-]?[0-9]+'"
unit_of_measurement: "W"
value_template: "{{ float(value) if is_number(value_json) }}"
scan_interval: 31536000
command_timeout: 16
- platform: command_line
unique_id: xcel_meter_production
name: "Solar Meter"
command: "OPENSSL_CONF=/config/xcelcerts/openssl.conf /usr/bin/curl --ciphers ECDHE-ECDSA-AES128-CCM8 --insecure --url https://192.168.0.12:8081/upt/1/mr/2/r --cert /config/xcelcerts/cert.pem --key /config/xcelcerts/key.pem 2>&1 | grep -o '<value>.*</value>' | grep -Eo '[0-9]+'"
unit_of_measurement: "kWh"
value_template: "{{ float(value) | multiply(0.001) | round(3) if is_number(value) }}"
scan_interval: 31536000
command_timeout: 16
- platform: command_line
unique_id: xcel_meter_consumption
name: "Net Meter"
command: "OPENSSL_CONF=/config/xcelcerts/openssl.conf /usr/bin/curl --ciphers ECDHE-ECDSA-AES128-CCM8 --insecure --url https://192.168.0.12:8081/upt/1/mr/3/r --cert /config/xcelcerts/cert.pem --key /config/xcelcerts/key.pem 2>&1 | grep -o '<value>.*</value>' | grep -Eo '[0-9]+'"
unit_of_measurement: "kWh"
value_template: "{{ float(value) | multiply(0.001) | round(3) if is_number(value) }}"
scan_interval: 31536000
command_timeout: 16
The scan intervals are high because I needed to trigger the polling commands as an automation to easily disable them when they fail. I currently have Readings #1 & #3 being polled at 10 minute intervals and Reading #2 at 12 minute intervals only between sunrise and sunset. Even paring back the polling frequency has not resulted in long term data gathering stability. I have never had the queries running for 24 hours without the port being shut down by the meter.
Are others successfully doing continuous reading on this meter? If so, what scan interval is working and is your cURL command significantly different from mine? I am failing to understand why this data connection is so touchy and if it is just me experiencing this problem. Thanks in advance for any insight!
I’m also in the Denver area and havent had any issues with it stopping receiving data. My scan intervals are set at 5 and command timeout also at 5.
My curl command is the same as you have.
Only difference I have is I dont use a value template for reading #1 and for reading #3 my value template is:
value_template: "{{ value | multiply(0.001) | round(3)}}"
I’ve been up and running since Apr 20 and hasnt missed any data yet.
1 Like
Whoa! I didn’t know you could do that! I don’t have solar, but finding an inverter that works locally with HA was a big concern. If the meter just does it for me, then I can avoid that pain.
I haven’t noticed any missing data for a while. My timeout is 16 and my scan interval is 180 (so 3 mins). Is it possible your automation is triggering too quickly sometimes?
I also have these if statements in the value templates:
# For instant power consumption
value_template: "{{ float(value) if is_number(value_json) }}"
# For total consumption
value_template: "{{ float(value) | multiply(0.001) | round(3) if is_number(value) }}"
1 Like
This is an excellent writeup and there are many more steps than I thought there would be.
Can you change the TOU input numbers from an automation instead of using node red? I don’t have NR installed ATM.
There are quite a few tricks in the billing, it seems. And some extra tricks to get HA to do the multiplication correctly. Glad you figured it out. I will have some time in a few weeks to try it.
You might consider making a new post, so it doesn’t get buried as the 195th comment here. It is really good info.
Quick link to the TOU pricing numbers. I assume these are the values you used?
https://co.my.xcelenergy.com/s/billing-payment/residential-rates/time-of-use-pricing
You could probably use an automation to change the TOU pricing. It was just easier for me to use Node Red. I use it for most all of my automations and just more comfortable using it over the HA automations.
I used the actual rates which are out to 5 decimal places rather than the rounded values.
2 Likes
I finally got a successful response back from the meter after hours of no response. This query was sent manually from a different RPi4 than my HassOS RPi. I wanted to see what the complete transaction looked like, not just the grepped meter value. I checked that I used the correct command to generate my key and cert files. Does anybody who is familiar with this kind of input see anything wrong that would upset the meter agent and cause it to close the port?
root@192:~# OPENSSL_CONF=openssl.conf curl --ciphers ECDHE-ECDSA-AES128-CCM8 --insecure -v --url https://192.168.0.12:8081/upt/1/mr/1/r --cert cert.pem --key key.pem
* Trying 192.168.0.12:8081...
* Connected to 192.168.0.12 (192.168.0.12) port 8081 (#0)
* ALPN, offering h2
* ALPN, offering http/1.1
* Cipher selection: ECDHE-ECDSA-AES128-CCM8
* TLSv1.0 (OUT), TLS header, Certificate Status (22):
* TLSv1.3 (OUT), TLS handshake, Client hello (1):
* TLSv1.2 (IN), TLS header, Certificate Status (22):
* TLSv1.3 (IN), TLS handshake, Server hello (2):
* TLSv1.2 (IN), TLS handshake, Certificate (11):
* TLSv1.2 (IN), TLS handshake, Server key exchange (12):
* TLSv1.2 (IN), TLS handshake, Request CERT (13):
* TLSv1.2 (IN), TLS handshake, Server finished (14):
* TLSv1.2 (OUT), TLS header, Certificate Status (22):
* TLSv1.2 (OUT), TLS handshake, Certificate (11):
* TLSv1.2 (OUT), TLS header, Certificate Status (22):
* TLSv1.2 (OUT), TLS handshake, Client key exchange (16):
* TLSv1.2 (OUT), TLS header, Certificate Status (22):
* TLSv1.2 (OUT), TLS handshake, CERT verify (15):
* TLSv1.2 (OUT), TLS header, Finished (20):
* TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):
* TLSv1.2 (OUT), TLS header, Certificate Status (22):
* TLSv1.2 (OUT), TLS handshake, Finished (20):
* TLSv1.2 (IN), TLS header, Finished (20):
* TLSv1.2 (IN), TLS header, Certificate Status (22):
* TLSv1.2 (IN), TLS handshake, Finished (20):
* SSL connection using TLSv1.2 / ECDHE-ECDSA-AES128-CCM8
* ALPN, server did not agree to a protocol
* Server certificate:
* subject: [NONE]
* start date: Mar 17 01:53:09 2023 GMT
* expire date: Dec 31 23:59:00 9999 GMT
* issuer: C=US; O=Itron; CN=IEEE 2030.5 MICA; serialNumber=7
* SSL certificate verify result: self-signed certificate in certificate chain (19), continuing anyway.
* TLSv1.2 (OUT), TLS header, Supplemental data (23):
> GET /upt/1/mr/1/r HTTP/1.1
> Host: 192.168.0.12:8081
> User-Agent: curl/7.81.0
> Accept: */*
>
* TLSv1.2 (IN), TLS header, Supplemental data (23):
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Date: Thu, 25 May 2023 22:22:09 GMT
< Content-Length: 252
< Content-Type: application/sep+xml
<
<Reading
xmlns="urn:ieee:std:2030.5:ns"
href="/upt/1/mr/1/r">
<qualityFlags>01</qualityFlags>
<timePeriod>
<duration>1</duration>
<start>1685053328</start>
</timePeriod>
<value>567</value>
</Reading>
* Connection #0 to host 192.168.0.12 left intact
In order to hopefully add something of value as well, I want to post what I found regarding reading the actual meter display:
TL Index is the smaller numbers displayed at the top left. Symbols in parentheses are the small icons displayed at the bottom)
1 Like
Wow. That is a big doc.
These are the numbers?
Where are the holidays defined?
It would be nice if the meter just told us if it was recording peak, mid, or normal.
Yea those are the TOU rate. Holidays are defined on Page 19.
Holiday New Year’s Day, Martin Luther King, Jr. Day, Presidents’ Day, Memorial Day, Independence Day, Labor Day, Columbus Day, Veterans Day, Thanksgiving Day, and Christmas Day.
Some of these move around and arent on the same day of the month each year so I’m not sure what to do about that. Currently, I’m going to have to update my template conditions at the beginning of each year to make sure I have them set to the correct day. Realistically, it’s probably close enough for me to not even bother changing them every year.
And if you look at your electric bill you’ll see some other charges e.g., Service & Facility, GRSA E, EGCRR, Trans Cost Adj, etc. These are all defined in that pdf. Some are just base charges (Service & Facility, Renew. Energy Std Adj, Colo Energy Plan Adj, and Energy Assistance Chg) so I have those just summed up and added to the usage costs in my billed_elec_cost sensor. It does look like some of these change month to month so there’s going to be some error in the billed cost I’m calculating but it will be minimal. The others are based on usage and are defined on the rate schedule.
Well after looking again, Renew. Energy Std Adj and Colo Energy Plan Adj are supposedly 1% of the total electric bill but when I try to calcuate that I’m off a few cents so I’m not entirely sure what they are using to calc that 1%.
1 Like
For those of you in the Denver area with solar, do you have 2 meters and have them both linked up to HA? I’ve got two of the new smart meters but was only able to set up the one directly fed by solar. That means I can see how much electricity is being generated / used from the panels but until I can get the other one set up, I can’t see how much electricity is being pulled from the grid.
Currently have an open ticket with Xcel about getting the other meter on my WiFi. Having the issue where the “EDIT” button doesn’t appear to do anything. Dev tools show there’s an error response coming back from a background call that I’ve shared with support.
That’s interesting. I’m in Denver, have solar and have 2 new Gen 5 Riva meters. When signing up of Energy Launchpad, I never had the option to choose which meter to connect. As you can see from my Itron meter display key a few posts above, the main meter shows HanCOn (connected to wifi) and solar meter shows Hand15 (disconnected from wifi). As the main meter shows both power pulled from the grid and power delivered to the grid, there really is no need to connect to the solar meter imho. I can get the total solar power generated from my Enphase Envoy and subtracting the value of power delivered to the grid from the Xcel meter gives me the amount of solar power consumed by my residence.
I’m happy you’re paying for attention. I am really mostly interested in getting within 2-5%. Having a good way to visualize the approximate price is worth a lot. Recomputing the bill isn’t as important. But I am happy someone is doing it.
Thanks for the work on this… I’m in Denver and just got my new meter, and I’m excited to play around with it this weekend. I’m curious, what are the long-term thoughts on the integration? Will it be integrated into HA directly or as a HA add-on? It’s such a nice compliment to the Energey monitoring dashboard, and it would be great to see it gain wider adoption.
1 Like
I’m in the Minnesota (St. Paul area). Just got a brand new Itron Riva Gen5 meter. Does anyone know what the IR test LEd outputs? I know it pulses something every few seconds, but it’s not in a regular interval, so I’m assuming it’s a pulse of data in some sort of protocol. What is the IR LED pulsing? The Excel Launchpad is not even showing up here … not available yet I assume.
2 Likes
On some meters the LED pulses once per watthour. I’d like to find an affordable way to display cumulative usage in watthours; there are generic counters but I’m not sure what the pulse is like. Does your meter display any info other than cumulative kWh?
EDIT: I see the meter cycles through a bunch of values including: time, kWh in A, B and C timeframes, total kWh, and some other things (‘handle’ number, etc) and one reading in units of kW, but it stays fixed at 4.165 or something. Could that be max power? And there are 2 rectangles and an arrow that emulate the spinning disk on mechanical meters. I don’t know how fast it spins in watt-hours.