Integrating Hourly Energy Pricing into Powerwall-Dashboard

[SCHibbard]

@jasonacox suggested I post some dashboard panels I have added to Powerwall-Dashboard. The first set is for energy pricing. Commonwealth Edison, the main Northern Illinois energy provider, offers hourly pricing rates (https://hourlypricing.comed.com). They also offer an API to pull current pricing (see https://hourlypricing.comed.com/hp-api). They publish pricing updates every 5-minutes. The rate used for billing is based on an hourly average of the 5-minute prices (:00 to :55) for the hour the energy was consumed. ComEd’s API offers an Hourly Average, but the value is incorrect, as it calculates the past hour average, not top of hour to 55 past. So just the 5-minute value is pulled from the API, an hourly average is calculated from :00 to :55 and applied to energy used/credited/saved for that same hour. The Grafana Panels currently in use to display results are below. Note that are a bunch of other charges in a ComEd bill that are based mainly on KW consumption, crediting (if on net metering), or percentage of bill (taxes). Unfortunately many do not have a month-to-month rate consistency that can be reduced to a calculation. I wish the bills were as cheap as the one panel shows, but they are normally 5-7x that (!) Plan to investigate that more one of these days.

[petersloan]

Hi @SCHibbard

I am also in the Chicago area on the hourly pricing plan (since AMI meter installed in 2015) and just got my 2x PW3 + enphase panels w/IQ8X inverters installed awaiting final inspection. I currently ingest the hourly pricing data into an influx DB for historical consumption/price monitoring but looking to get more sophisticated now that I will be generating credits. I presume you setup a separate datasource? Did you end up leveraging the Powerwall-Dashboard tools->mysql connector or did you go a different route. Anything you are willing to share (such as schema/grafana queries) or even potentially collaborate on would be appreciated.

Thanks!

[SCHibbard]

Hi Peter: I ingest the data into the same Powerwall influx database created by the Powerwall Dashboard used for the rest of the Powerwall & weather data. I created a python script that runs as another Docker Container. It polls ComEd every 5 minutes and saves the current 5-minute price. I don't save the hourly average they publish, due to the inaccuracies noted above. Instead, at the top of the hour it calculates the hourly average of the previous hour. That value is then applied to the various KwH values (Grid, PW, Solar) to generate the values used in the panels above. I have on my list to clean up the code to make it publishable here, but I first wanted to tackle the problem of all the other charges ComEd tacks on, to try and get the monthly costs (last panel above) a little more accurate. Been procrastinating that challenge! They publish the rate in the bills, but many of them vary from month to month. The "Distribution Facility Charge", is a good portion of the bill. Over a one year period, it went as low as 0.04065 to as high as 0.04777, That one alone contributes a lot to the bill total. I guess I could just use an average of the year since it is only about a 20% swing and be done with it, but I was hoping to find a way to pull it online. I created an account at PJM (the data supplier to ComEd & others), but have not had time to do any investigation into what they can offer there. Any help in deciphering those Delivery, Taxes, and Fees charges would be welcome!!

[petersloan]

@SCHibbard

For folks like myself with sufficient battery storage that will be operating in self powered mode, I would likely value my generation at the fixed rate price since I can use my generated power at any time. So I would compute savings = (ComEd Generated Credit) + (kWh_locally_consumed * (Distribution_Facility_Charge + IL_Elec_Dist_Charge + PriceToCompareRate)) (See below for where I get these values). I am hoping I can programmatically get the ComEd Generated Credit from their website but if not, its one value I would have to inject into InfluxDB monthly and the other values only change year (Starting in June I believe). As for taxes and fees, I don't think the data entry is worth it and maybe a timeseries the latest fixed percentage can be used instead. For example, with my bills, it appears that 25% of the computed "savings" would get you pretty close. Anyways, on with the data dump.....

In my mind, essentially what you are trying to do is come up with two things (savings and actual cost) and will need to balance between manual data entry and automation.

Savings

This is the "value" that your solar system provided. I think there are two ways to think about this. If you are on the BHES (ComEd's TOU plan), you can actually use the value of the credit you generated that month since you are not credited kWH but rather the cash value at the time of generation. Until my system is operational and starts to generate $ based credits, I won't know if this will be exposed anywhere in the various ComEd apis. There is a site that the NREL referenced that has detailed rate information APIs but appears to be horribly out of date (https://apps.openei.org/services/doc/rest/util_rates/?version=7) after I signed up for key to look around. Last update seems around 2016 and the source pages for the rates are all in the old site format (looks like you used to be able to get your rate information right from the site once you logged in).

Electric Costs == Generation + Transmission

The other option or if you are on the standard BES plan is to lookup the Price To Compare price as this factors in both electricity cost and transmission cost. PlugInIllinois lists the current Fixed Rate Electric cost + Transmission cost.

Delivery == ComEd's last mile distribution

Go to https://www.comed.com/current-rates-tariffs then click on your appropriate rate tariff and select A Guide to the Retail Customer’s Billed Delivery Service Charges (PDF) And its a PDF printout of a excel doc because OF COURSE.... so you will need a process to manually key this in every 3 months. The last column matches up with my bill.
Current rates can be found here.

Tariff and Fees

Each line item in this section has its own document. If you go to https://www.comed.com/current-rates-tariffs you can see the PDFs corresponding to the actual adjustments although the docs can be named slightly differently so human involvement will be required.

Actual costs

This is what ComED is actually charging you.

I was hoping to dig around find a way to get my bill in a format that is consumable by an API. ComEd actually uses Oracles Opower platform which has the capability to do such things however it appears that each utility has their own instance which has been customized to their own needs and wants. ComEd does not seem to have any endpoints that I can find that expose line item details however I am not sure that is exactly necessary. There is an endpoint that exposes a massive JSON with just about everything ComEd knows about you and powers the My Bill Details of the ComEd Dashboard. The tricky part is figuring out how to get to it.

I was happy to stumble on a python module called Opower which is used by Home Assistant. I was hoping I could use it directly but as its a module designed to do one thing (get very basic consumption metrics), I couldn't easily leverage as a library import with my limited python skills so I opted to look how the comed (aka excelon) connector is implemented.

I apologize for the python that is about to follow as I am sure it will make more experienced folks eyes bleed. Being more of a C# developer, I never really spent much time in python other than quick and dirty things.

Most of the steps required to actually get logged in and get all the appropriate bearer/csrf/etc tokens was from the Exelon specific OPower connector. You just need to set the three variables at the top with your account specific credentials and account number ( I didn't feel like implementing account lookup for this quick POC ). All the usual disclaimers around lack of robust error checking, handling multiple accounts, non residential accounts, MFA, or other edge cases I can't even think of. Hopefully this could be a jumping off point to something more robust. If there is a way to directly leverage the connector, that would be awesome as I am sure that project will be much better at keeping it up to date as the site changes (they implemented significant changes earlier this year that resulted in them being down for well over a week if I recall)

I would be really curious what this looks like for someone that has a month of production credits?

# USER EDIT VARS
accountid = "0000000000" #must be quoted, get this from your bill PDF or the My Account page of the dashboard
username = 'comedcust@domain.com' #What you use to login to comed's site
password = 'superdupersecrets'
# END USER EDIT VARS

import requests
import json
import re
import urllib3

USER_AGENT = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
url = 'https://secure.comed.com/Pages/Login.aspx?/login'
login_domain = 'secure.comed.com'
headers = {'User-Agent': USER_AGENT}

session = requests.Session()
response = session.get(url, headers=headers)
result = response.text

parsedResponseUrl = urllib3.util.parse_url(response.url)

if parsedResponseUrl.path.endswith("/authorize"):
    settings_match = re.search(r"var SETTINGS = ({.*});", result)
    assert settings_match
    settings = json.loads(settings_match.group(1))
    login_post_domain = parsedResponseUrl.host
    assert login_post_domain
    
    response = session.post(
        "https://" + login_post_domain + settings["hosts"]["tenant"] + "/SelfAsserted",
        params={
            "tx": settings["transId"],
            "p": settings["hosts"]["policy"]
        },
        data={
            "request_type": "RESPONSE",
            "signInName": username,
            "password": password
        },
        headers={
            "X-CSRF-TOKEN": settings["csrf"],
            "User-Agent": USER_AGENT
        }
    )

    result_json = json.loads(response.text)

    if result_json["status"] != "200":
        print("Login Failed")
        print(result_json["message"])
    
    response = session.get(
        "https://"
        + login_post_domain
        + settings["hosts"]["tenant"]
        + "/api/"
        + settings["api"]
        + "/confirmed",
        params={
            "rememberMe": settings["config"]["enableRememberMe"],
            "csrf_token": settings["csrf"],
            "tx": settings["transId"],
            "p": settings["hosts"]["policy"],
            "diags": json.dumps(
                {
                    "pageViewId": settings["pageViewId"],
                    "pageId": settings["api"],
                    "trace": []
                }
            )
        },
        headers={"User-Agent": USER_AGENT}
    )

    # We are now logged in but have to acquire a bearer token
    response = session.get(
        "https://"
        + login_domain
        + "/api/Services/MyAccountService.svc/GetSession",
        headers={"User-Agent": USER_AGENT}
    )

    result_json = json.loads(response.text)
    bearer_token = result_json["token"]
    #print(json.dumps(result_json, indent=2))

    response = session.get(
        "https://" + login_domain 
        + "/.euapi/mobile/custom/auth/accounts/"
        + accountid
        + "?payments=false",
        headers={
            "User-Agent": USER_AGENT,
            "Authorization": "Bearer " + bearer_token
        }
    )

    accountJson = json.loads(response.text)
    #print(json.dumps(accountJson))
    #print(json.dumps(accountJson["data"]["BillingInfo"], indent=2))
    
    print("Supply: $" + str(accountJson["data"]["BillingInfo"]["supplyCharges"]))
    print("Delivery: $" + str(accountJson["data"]["BillingInfo"]["deliveryCharges"]))
    print("Taxes And Fees: $" + str(accountJson["data"]["BillingInfo"]["taxesAndFees"]))

[petersloan]

I will also mention the meat of the data is in the opower database which uses graphql to expose the account level data. It would be somewhat meaty to try to fully explore this but the exelon connector referenced above has examples on how to get the bearer token from comed to hit the opower endpoints. Might be something I mess around with more when I have time. I would be loathe to try and construct my own queries and instead would rely on discovering an existing request that essentially could be replayed.

https://cec.opower.com/ei/edge/apis/dsm-graphql-v1/cws/graphql

https://docs.oracle.com/en/industries/energy-water/integration-hub/graphql-user-guide/Content/GraphQL/buidlingAndExploring.htm

[petersloan]

Just for the hell of it, I started messing around with creating a bill parser. Super rough and crude but technically could automate downloading the bill PDF and running it through the parser with all the bits I started assembling. Got to start heading to bed now so this is in no way completed, just some rough proof of concept type work.

There appears to be an API to get a list of bills https://secure.comed.com/.euapi/mobile/custom/auth/accounts/{accoundID}/billing/history and then another you can call to download the bill https://secure.comed.com/.euapi/mobile/custom/auth/accounts/{accountID}/billing/{Bill YYYY-MM-DD}/pdf

import pypdf
import json
import re

#Path to Bill to Parse, relative paths work
billPdfPath = "BillImage.pdf"

def extract_charges_and_fees(text):
    #Extracts charges, fees, total kWh, and per kWh cost from the given text.

    charges_data = []
    chargeTypeCategories = ["SUPPLY", "DELIVERY", "TAXES&FEES"]
    chargeCategory = "" 
    
    for line in text.splitlines():
        # We want to persist chargeCategory as we enumerate so we don't reset it between lines
        chargeDesc = ""
        kwhUsed = ""
        perKwhPrice = ""
        total = ""
        lineChunks = line.split();
        numOfLineChunks = len(lineChunks)
        
        if numOfLineChunks == 0:
            continue
        elif numOfLineChunks == 1:
            #skip as this is an info line
            continue
        elif numOfLineChunks == 2:
            # no kwh info
            for chargeTypeCategory in chargeTypeCategories:
                if line.startswith(chargeTypeCategory):
                    chargeCategory = chargeTypeCategory
                    break
            chargeDesc = lineChunks[0]
            total = lineChunks[1]
        elif numOfLineChunks == 3:
            # kwh info
            chargeDesc = lineChunks[0]
            total = lineChunks[2]
            if 'X' in lineChunks[1]:
                kwhInfoChunks = lineChunks[1].split('X')
                kwhUsed = kwhInfoChunks[0].lower().replace('kwh','')
                perKwhPrice = kwhInfoChunks[1]

        charges_data.append({"ChargeTypeCategory": chargeCategory, "ChargeDescription": chargeDesc, "KwhUsed": kwhUsed, "PerkWhPrice": perKwhPrice, "Total": total})

    return charges_data
        
            


def extract_section(text, start_marker, end_marker):
    start_index = text.find(start_marker)
    end_index = text.find(end_marker, start_index + len(start_marker))

    if start_index != -1 and end_index != -1:
        return text[start_index + len(start_marker):end_index]
    else:
        return None


with open(billPdfPath, 'rb') as pdf_file:
    pdf_reader = pypdf.PdfReader(pdf_file)
    num_pages = len(pdf_reader.pages)
    page = pdf_reader.pages[1]
    text = page.extract_text()
    
    billText = extract_section(text, "CHARGEDETAILS", "UPDATES")
    billObject = extract_charges_and_fees(billText)
    billJson = json.dumps(billObject, indent=2)
    print(billJson)

[SCHibbard]

@petersloan:

You dug up some good info! A couple comments:

I assume by “I would likely value my generation at the fixed rate price since I can use my generated power at any time” you are suggesting you’d just use the summer or winter BES cents/kWh instead of the BESH hourly average. I am of the opinion that it is much more accurate to use the actual hourly rate. My system has been up for over a year now. I have 2 EVs, so I can drain the PWs if both my wife & I put on the miles during the day, but normally not. In the summer cycle we are in now, the hourly price is normally high during the day, low at night. So, when solar is producing, you’re saving quite a bit. The batteries are riding through the overnight, but it is at a much lower BES cents/kWh rate.

Pulling the BESH rates from their open API at https://hourlypricing.comed.com/hp-api is fairly simple, just the aforementioned problem of the hourly rate not being what they really use for billing. The power used values store in PWDB actually align very well with what ComEd says I’ve used at https://secure.comed.com/MyAccount/MyBillUsage/pages/secure/ViewMyUsage.aspx - normally within 1% of each other. I chalk that off to efficiency losses in the PW Gateway, and/or jitter in times between ComEd and the Gateway. So, grabbing the 5-minute value from the above API, calculating the average from :00 to :55, and applying it to the various kWh values already in PWDB (grid, solar, PW), gives pretty good results, albeit just raw Supply Charges, not transmission service charges, etc. Here’s part of a comparison I did after the ComEd Docker container was up & running:

ComEd reported price from above API tracks perfect with the calculated hourly average from the program. Accuracy of what ComEd says I used or generated is within a percent or 2. The issue is net cost per hour. What ComEd reports appears to include all the items under Supply & Delivery on a bill (just not Taxes & Fees). The program only has Supply Charge so far.

I am aware of the Delivery & Tariff documentation, but like you said, it involves manual maintenance, and I have been hoping to avoid that. I was not aware of the Opower info – good find! I’ll dig into that and see what can be automated with it. One immediately nice thing is it will provide bill date-range, which ComEd is not consistent at!

By the way, I added alerts in Grafana when pricing is going high – they track ComEd’s text messages, actually faster. Also added one when it goes negative … plan is to tie that to charging the cars if they are home & plugged in & the PWs are full.

I’m going to be out of pocket the next couple of weeks at a conference, so it may be a while before I can dig into this.

---Scott---

[petersloan]

Sounds good @SCHibbard I have a few commitments coming up as well so will chew on your response on the next few days.

Just to answer your question about the BES vs BESH rates as to how I would value savings. I have been storing that data influx since 2021 and even longer in a mysql backend. I don't have an EV so for me, moving to BESH was about reducing and shifting my consumption in order to generate savings (keeping my bill below $100 while others were pushing $300+), something that has required pretty significant lifestyle changes and automation that polls the hourly pricing API and controls a bunch of smart switches and thermostat based on real time rate thresholds. By resuming "normal" life, I am effectively getting the "value" of being on a fixed rate plan again where I don't have to worry about TOU price fluctuations by relying on Self Powered Mode. If electric supply was at .20/kwh, all my high draw devices would be automatically powered down, something that I expect I won't need to do with the storage. Hopefully that explains why my approach to savings valuation is different. I guess if powered spiked to over $3.50/kwh like it did back in Dec 2022 I could value any self consumption at that rate but in reality if I didn't have the ability to throttle down grid consumption, I wouldn't be on the BESH plan in the first place.