Stephen Nimmo

Energy Trading, Risk Management and Software Development

Month: June 2015

Overview of the Retail Energy Broker Business

Most people in Texas buy their power through the unregulated market. This means there are two entities involved in the delivery of your house’s electricity: the retail electric provider (REP) and the Transmission Distribution Service Provider (TDSP).  The REP sources the power from the market, either purchasing the power directly from specific generators or by bidding in the open market for the power to be provided by a pool of available generators. They handle your contract trends, your billing based on the usage and all the other stuff related to payments or customer services. The TDSP and utility are responsible for the physical delivery of the power purchased by the REP across the grid through transmission lines and other physical assets shared by the market.

Why Energy Brokers?  

For residential customers, the process of choosing a REP can be fairly simple, usually handled by going to a REP’s website and signing up, or using one of the tools available like powertochoose.org. Residential customers power contracts are relatively simple, usually a fixed price per KWh – maybe with a few twists like free nights but overall, residential load is consistent and straightforward.  However, for some small businesses and larger commercial and industrial entities, their power usage is not as simple. They may have very particular needs for power management related to issues like large HVAC usage or load related to running large machinery. Because their needs are much more complex, they have more complicated contracts to give the ability to tailor their needs. In these cases, energy brokers come into the picture to help navigate these complexities.

Energy brokers can play many different roles for a customer, ranging from simply finding the best available prices from a variety of REPs to performing in depth consulting and analysis on the load characteristics of the business and translating those needs into an optimized contract for their customer, often negotiating with the REP in a detailed manner.

There are also different types of brokers, such as aggregators. These types of organizations consolidate load by gathering many similar entities, such as residences, together to bring a higher load to a particular REP. As with any business, the more potential revenue in a single transaction a company can bring, the more optimized it’s cost to serve can be, greatly reducing costs like cost to acquire. Aggregators use this to their advantage by creating a position of negotiation for the group and (hopefully) getting a better rate for everyone.

Compensation  

The compensation for the third parties is paid by the REP directly and is calculated as part of the cost to serve. The residential customer rarely sees any impact on their prices, however the larger C&I customers will see an impact on their bill, as the REP will usually pass through or incorporate the cost of the broker commissions into the fixed price portion of the billing statement.

Third parties are compensated using two main types of compensation: the flat fee and residual fees. The flat fee, usually associated with residential and small business contracts, is paid as a fixed amount after the contract is executed. These amounts are usually in the range of $25 to $100. These amounts are paid with the understanding the customer needs to remain a customer until the REP delivers enough power to break even on the costs. If a customer cancels or drops before this can happen, it may result in a “clawback” by the REP, where the commission paid will be returned due to the cancellation, perhaps on a pro-rata basis.  The residual fee is paid to the third party as a commission based on the customers actual usage. Designated in a Mill, which is 1/1000 of a cent, the commission rate is paid per KWh of usage. Let’s say a broker is contracted to receive 5 mills ($0.005) per KWh and the customer uses 10,000 KWh in the previous month, the broker would be then due a commission of $50, usually payable to the broker after the invoice is paid to ensure the REP can recoup the commission from the customer.

The Complications for REPs 

So far, the scenarios presented have been fairly straight forward and easy to manage. However, there are two other aspects to broker commission management that can make it very hard for REPs to make sure they are paying out the correct commissions: the prepayment and the Cancel-Rebill.  Let’s start with the prepayment. When signing up a new customer, the REP will request a usage history from the utility to determine the expected load. At this point, the REP has a good idea for existing customers of how much load to expect over the year and therefore, does have the ability to “prepay” the broker – usually at a discount” – for the value of the contract. Using our previous example of a 5 mill rate and 10,000 KWh per month, the REP and broker can estimate the value of the year’s commissions at $600 (10,000 KWh x 12 months x .005). The REP and broker can negotiate the commission and pay the entire sum at the beginning of the year at a discount, say 80% – meaning the REP will pay the broker $480.

But the prepayment comes with a true-up, meaning at the end of the year, the REP and broker will calculate the actual usage and what the actual commission payment would have been and make each other whole. Continuing on the example, let’s say the customer used double their expected volumes and the end commission payment would have been $1200. The REP would then pay the balance to the broker based on the non-discounted expected fees ($1200 – $600) resulting in a $600 payment to the broker for the true up. But remember, this can also work in reverse – if the customer only used half the expected amount then the broker may have to return some to the REP.

The other main complication with broker payments is the Cancel Rebill. Upon receipt of an invoice, if the customer does not believe the usage is correct, they can request that the utility re-reads the meter and recalculates the bill. In the REP billing system, this could result in multiple retracted invoices or invoice line items for a particular month, and new line items in a later month (after the re-read happens) that correspond to the flow from a previous month based on the new values. These processes take the processing cycles for residuals paid per invoice out of its customary cycle and can result in delayed payments to brokers while waiting for the new meter reads. Combine a Cancel Rebill at the end of the prepayment when a true up is due, and logic can get complicated.

Recommendations for REPs

Because broker related business can represent sometimes more than half of a REPs load, there are a few recommendations for best practices for REPs in handling the broker related business. First, give the brokers a portal to manage their data. Give them an easy way to search for customers data, for invoices and even export the data into spreadsheets for them to incorporate into their own systems for commission tracking. Second, find a standard set of commissions and resist any customization under the pressure of closing the sale. Most brokers are fine with the standard set of flat fees, residuals and prepayments and introducing new compensation models for individual brokers will result into complicate manual operations as the broker’s deals will have to be handled in one-offs. Last, create automated systems to pull contract events and invoicing events directly from the CRM and billing systems, and create commissions through software, not by spreadsheet. Spending development time now will pay dividends for years to come.

Let’s scrape ERCOT for some DAM data

One of the largest sources of complexity for power retailers, traders or anyone involved in the sourcing or delivery of power is pricing. Unlike the crude, refined products and NG markets, power prices are highly volatile and updated almost constantly. If you are responsible for delivering load on the grid, and have not fully sourced or hedged your price exposure, these swings can literally put you out of business.

Ercot_logo

To obtain the real time and day ahead power prices, you have two choices – you can find a data provider to do all the work or you can scrape the prices yourself. Web scraping is a delicate art, full of gotchas and issues as you are effectively building to a data endpoint not designed for being a data endpoint. Changes to web interfaces occur with little or no forewarning and even small adjustments to HTML structures, tag names or ordering can put you in a world of hurt. As a warning, if you choose to scrape, be ready for the possibility of some real-time production support and fast deployments if a provider changes their UI. And they will.

With all that said, let’s take a look at how someone might scrape. For this example, we will be scraping the Day Ahead Market (DAM) Settlement Point Prices. For the DAM Settlement Prices, they should only need to be scraped daily. First thing is to understand the ERCOT information and how it’s presented. First, there are two views available – one is a web view and then there are csv and xml reports available. We are staying away from the web views (see above warnings) and we are going to concentrate on the csv and xml reporting views.

damscreen

Let’s start with the url. Here is the url for the ERCOT MIS reports app for DAM Settlement Point Prices.

http://mis.ercot.com/misapp/GetReports.do?reportTypeId=12331&reportTitle=DAM%20Settlement%20Point%20Prices&showHTMLView=&mimicKey

Here are the thoughts you should be having:

  1. Do I need all the parameters or just the reportTypeId? (Just the reportTypeId)
  2. Is the reportTypeId correlated to the DAM Settlement Point Prices? Will each type of report have its own integer based reportTypeId? And do those stay the same? (Not Sure about any of these)

So now we have a baseline. By looking at some of the other reports, you can quickly discover some fairly strong sources that each report type has a unique reportTypeId, and they stay the same. If I know the reportTypeId of the type of reports I want to scape, I can simply replace the url’s reportTypeId with the report id I want and theoretically, I should be able to pull any reports I want. Oh, wait. What is this? When I mouse-over the zip file for the xml, I get another url.

http://mis.ercot.com/misdownload/servlets/mirDownload?mimic_duns=&doclookupId=483750748

Here are the thoughts you should be having:

  1. Do I need all the parameters or just the docLookupId? (No) (By the way, the mimic_duns looks like something that, if you know the DUNS of an entity, might mock out their view)
  2. Is the docLookupId correlated to the particular day’s version and file type of the DAM Settlement Point Prices? (No) Will each report have its own integer based docLookupId? (Yes) And do those have any type of identifiable algorithm? (No) (this is not a great scenario)

Right now, there is a couple of options when you cannot programmatically discern the particular report you are looking for. You can:

  1. Scrape it all and backfill data gaps programmatically.
  2. Parse the HTML and find the nodes with the file names, parse the names finding the dates you want between the dots, and then find the zip link in the same html row.

I didn’t like either of these as they are both prone to the html change issue we described earlier. So I started poking around on the source of the list page and found something interesting.

ercotmishtml

You see that base href url? http://mis.ercot.com:80/misapp/ Well, I did. And I clicked it. And thank goodness I did because my life just got a ton easier.

icescreen

Looks like ICE has its own little version of the ERCOT MIS website. And if you dig in to the links, you get the first web service (http://mis.ercot.com/misapp/servlets/IceMktRepListWS) that provides us a list of the reportTypeId for each report. In this list, we find the ID for the report we are looking for.


<ns0:Report>
  <ns0:ReportTypeID>12331</ns0:ReportTypeID>
  <ns0:ReportName>DAM Settlement Point Prices</ns0:ReportName>
  <ns0:SecurityClassification>PUBLIC</ns0:SecurityClassification>
</ns0:Report>

Now, we can use these reportTypeIds in the other web service to give us a list of the docLookupIds with some supporting information. http://mis.ercot.com/misapp/servlets/IceMktDocListWS?reportTypeId=12331


<ns0:Document>
  <ns0:ExpiredDate>2015-07-11T23:59:59-05:00</ns0:ExpiredDate>
  <ns0:ILMStatus>EXT</ns0:ILMStatus>
  <ns0:SecurityStatus>P</ns0:SecurityStatus>
  <ns0:ContentSize>91581</ns0:ContentSize>
  <ns0:Extension>zip</ns0:Extension>
  <ns0:FileName/>
  <ns0:ReportTypeID>12331</ns0:ReportTypeID>
  <ns0:Prefix>cdr</ns0:Prefix>
  <ns0:FriendlyName>DAMSPNP4190_xml</ns0:FriendlyName>
  <ns0:ConstructedName>cdr.00012331.0000000000000000.20150610.131348757.DAMSPNP4190_xml.zip</ns0:ConstructedName>
  <ns0:DocID>483750748</ns0:DocID>
  <ns0:PublishDate>2015-06-10T13:13:47-05:00</ns0:PublishDate>
  <ns0:ReportName>DAM Settlement Point Prices</ns0:ReportName>
  <ns0:DUNS>0000000000000000</ns0:DUNS>
  <ns0:DocCount>0</ns0:DocCount>
</ns0:Document>

Nice. Two steps down, one to go. We can now parse the xml to identify the docID we need by date (PublishDate) and file type (IndexOf ‘xml’ on FriendlyName). And once we have the docId, we can then make the final call to retrieve the zip file using the previously known url:

http://mis.ercot.com/misdownload/servlets/mirDownload?doclookupId=483750748

Using these patterns and having the list of documents, it would be very easy to scrape the site based on dates, looking to fill in data or just parse data on a daily basis. Let’s review the steps.

  1. Get all the report types and their subsequent reportTypeId.
  2. Get all the docIDs for the reportTypeId by parsing the xml by date and/or file type.
  3. For each node, call the third url to pull in the zip file. Unzip. Parse. Save. Done.

With great power comes great responsibility! Be nice to the ERCOT folks and try to limit the scraping to only the data you need, in the lowest intervals you need and store the downloaded raw files in a document server (or amazon S3 bucket or azure blob storage) if you think you might need them again in the future (you will). Disk space is cheap. Also, write two different processes for pulling the data and then parsing it. That way, you can also have other processes pulling in the raw data.

Enjoy your scraping.

© 2017 Stephen Nimmo

Theme by Anders NorenUp ↑