Stephen Nimmo

Energy Trading, Risk Management and Software Development

Postgresql Partitioning Trigger and Function

I’ve been working on a project that needs a time series database and we are building in Postgresql so I needed to figure out how to partition data. Having done this a number of times on Oracle, I was curious about the comparison and after an hour or poking around the internet, I settled on an implementation that was fairly elegant.

First the time series table.

CREATE TABLE main.meter_usage_ts
  production_meter_ts_id bigserial PRIMARY KEY,
  meter_id bigint NOT NULL references main.meter(meter_id),
  start_timestamp timestamp NOT NULL,
  end_timestamp timestamp NOT NULL,
  timezone text,
  value numeric(10, 4),
  uom text  

We will want to create partitions for this data by the month to help with query performance. From here, there are two choices: create all of your partitions from the start and run a maintenance job that ensures there are partitions available to hold data. Or you can write a stored proc that checks for the presence of the partition before inserting and creates it if needed. I chose the second way.
CREATE OR REPLACE FUNCTION main.create_production_meter_ts_partition_and_insert()
 partition_date TEXT;
 partition TEXT;
 partition_date := to_char(NEW.start_timestamp, 'YYYY_MM');
 partition := TG_TABLE_NAME || '_' || partition_date;
 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition)
 RAISE NOTICE 'A partition has been created %', partition;
 EXECUTE 'CREATE TABLE ' || TG_TABLE_SCHEMA || '.' || partition || ' () INHERITS (' || TG_TABLE_SCHEMA || '.' || TG_RELNAME || ');';
 EXECUTE 'INSERT INTO ' || TG_TABLE_SCHEMA || '.' || partition || ' select $1.* ' USING new;
COST 100;

CREATE TRIGGER create_production_meter_ts_partition_and_insert_trigger
BEFORE INSERT ON main.production_meter_ts
FOR EACH ROW EXECUTE PROCEDURE main.create_production_meter_ts_partition_and_insert();

The cool thing about this is that there are no maintenance jobs to run (and to forget to run) to create the partitions. Bad thing is the IF check runs on every insert.

Extra bonus – this is fairly generic if you are partitioning by date.

Retail Power – You Can’t Manage What You Don’t Measure

The legend W. Edwards Deming said it best – “You Can’t Manage What You Don’t Measure”. When it comes to booking retail power deals it’s always good do so with the end measurements in mind. Without the proper organization of costs and the ability to allocate costs back to contracts on a per-kWh basis (or per kW if the cost is demand-driven), then the business can’t answer the fundamental questions it needs to be profitable.

  • When pricing a contract, how do we make sure the pricing estimates are both complete and accurate?
  • When servicing a contract, how do we identify events which could have a negative financial impact on the actuals and make adjustments to reduce or eliminate that risks?
  • How can we use past performance to help us identify ways to improve?

Let’s dive into a few particulars of the retail power industry to better understand how to organize the data. As with any industry, there are two types of associated costs: fixed and variable costs. The fixed costs are the obvious ones, such as rent or hardware. However, the variable costs have some different flavors that have correlations to how the power physically works. First, some assumptions needs to be verified. Energy delivery is componentized, and the components are ISO specific. There are themes across most ISOs, such as Energy or Transportation, but there are also ISO-specific components, such as Capacity, which need to be accounted for in some ISOs, but not others. For the sake of simplicity, we are going to assume that each ISO will be modeled independently, as trying to merge all ISOs into a single model doesn’t provide much benefit, it terms of data usefulness.

Next, whenever costs are discussed, it’s rarely a simple cost, like $.03 per kWh. Most usage charge estimates are modeled as an hourly price curve meaning the costs associated with a particular component of the energy are unique to the hour in which the costs happened, and can be different for every hour. Modeling a single usage cost for an entire year, with hourly granularity, creates 8,760 possible values. Some shops summarize at higher levels, such as on-peak/off-peak values per day, but that can lead to issues as spikes in a particular hour can be lost. When data is summarized, data granularity is compromised. However, keeping hourly granularity also has its costs due to the data management required. If a power product is broken down into 30 different components with hourly granularity, there is now 262,800 individual price points to deal with and doing multiplication for 8,760 estimated volumes across the 262,800 price points may have a performance impact.

The complications really begin when the model starts to enter into the actual power delivery. When pricing a contract, we are taking each one of the pricing components, multiplying those price estimates by the associated hourly measure (usage or demand), and coming out with the total estimated costs for delivery 2016-02-22(adding in fixed costs, expected profit, etc). The problem happens when the power is actually delivered because the granularity is lost. The load scheduled and subsequent invoice amounts for each of these components can come back in a aggregated fashion, which means I will need to break them down to get back to the same level of granularity used during the estimations to get actual feedback on how accurate the estimates were. If I have 12,000 contracts that represented 4 mWh of usage at 9:00 AM on Feb 22, 2016, I will want to break those invoice amounts to a per-kWh cost, and apply those costs back to each contract based on the individual meters actual usage (load ratio allocation). For simplicity sake, we won’t discuss specific schedules for individual large C&I contracts muddled into the measurements, or how some of the costs coming back may not be applicable to all contracts.

Now that some of the complications are known, let’s talk about some of the best practices to keep in mind when modeling retail power.

  • Loss of Granularity – whenever there is an aggregation, you are making a choice to possibly miss the ability to make adjustments in operations. To provide another example of aggregation, some shops will try to shrink the model by aggregating multiple components together in a single representation, such as a blanket “Ancillary Services” bucket or “Energy” category. If you price at a bucket level, the only thing you can compare is the buckets so if the “Ancillary Services” charge all of the sudden comes back double what the estimate is, there may be a lot of work in store for you if you want to determine what caused the issue.
  • Separation of Load – When possible, try to tag and categorize load as much as possible. At a bare minimum, every contract/meter can be tagged with its corresponding ISO, Utility, State, LoadZone and CapacityZone (if NYISO). From there, shops can continue to break down the load into smaller groupings but it’s usually unique to the organization. There are usually some breakdown between Residential/SMB contracts and Large contracts, because Resi/SMB tend to be a fixed price deal where larger contracts might fix or passthrough associated charges. The last tagging possibility is at the scheduling entity level, where REPs can schedule their loads by different scheduling entities or legal entities, resulting in multiple ISO invoices coming back related to each separate entity.
  • Flexibility – the model and all systems using the model need to be able to make adjustments without little cycle time. There are some cases where new, significant charges come up (we didn’t model that because it’s usually not relevant) or changes to how the charges are allocated. If a new customer wants to be able to price at a component level that you currently don’t support but your competitors do, then you will be forced to pass on the deal or make it work, making flexible models very important to larger deals.

Power To Choose needs an Upgrade

If you live in Texas, you know how important a good price is for your power. August temperatures can send your bills through the roof. A few years ago, the Public Utility Commission of Texas launched a website called PowerToChoose to serve as a centralized hub for all retail energy providers to post their offerings and give consumers the ability to shop around for their electricity. What started out as a great idea has now morphed into a pricing comparison site based on fine print tricks, giving the average customer little or no value at all when trying to actually to compare on price.

500, 1000, 2000

The original intent of the 500/1000/2000 metrics was to allow customers a quick glance into what the average price would be if they chose that particular deal. Simply, the 500/1000/2000 metric breaks down to apartment/small house/large house, and most consumers can quickly put themselves in the right bucket based on these metrics, pool owners notwithstanding. The real reason behind the 500/1000/2000 metric was due to normal pricing policies at the time, most retail energy providers charged a flat fee for consumers who used less than a certain amount of electricity. The retailers profits are normally built into the energy charge, but economic realities of the retailers fixed costs (people, office space, IT systems, etc) requires that for each contract processed, if a threshold of volume isn’t meant, they could literally lose money on the deal.

The flat services fees were nominal, but they had an impact on the 500/1000/2000 prices, as the flat fees were spread across the volumes in these prices. For example, if you got charged $10 as a flat fee and had 500 kWh of usage, then the $10 would look like $0.02 per kWh. However, if you use 1000 kWh, then it looks like $0.01 per kWh. If the energy charge was $0.05, then the 500/1000/2000 would be $0.07/0.06/0.065 respectively. However, the 500/1000/2000 prices don’t really give the consumer a good picture of what the costs of the entire contract will look like.

Reality of Energy Use2016-02-16 (2)

If you have a smart meter, you have access to all of your usage data, down to 15 minute increments (which no one uses, but that’s a story for another day). If you go to and register, you can access the data and print nice excel reports of your monthly usage, which is actually needed for a consumer to determine what the best deal is. Let’s take a look at my families’ usage last year.

Quite a range, from 508 kWh in Feb/Mar to a whopping 1824 in July/Aug. No pool, smallish home, built in 2005. The problem is the pricing – the 500/1000/2000 kWh prices are useless to me! Some months, the 500 kWh price is close to what I am paying, but for 5 months out of the year, I am using more than 1000 kWh. Without pulling my actual usage, and sitting down to do the math, the 500/1000/2000 representative prices don’t truly reflect what I am going to be charged.

1000 kWh Manipulation

The problem comes from the way the products are organized. When you put in your zip code and hit enter, the deals come up sorted by the 1000 kWh prices, lowest to highest. The retailer with the lowest 1000 kWh price is the winner, so to speak. They are the “top result” which is commonly gold when it comes to marketing. Using a parallel from Google, the result positioning for search can have a dramatic effect on “click through”, meaning the users will click on the top result 32.5% of the time and the top three get a whopping 61.5% of traffic share on Google. As a retailer, the PowerToChoose results that pop up after the user enters their zip code should hold the same value, where the top result is highly coveted, the top three a must and the first page essential. As a retailer, how do I go about getting these positions? Having the lowest 1000 kWh value wins and that’s where bill credits rear their ugly head.

Let’s look at the top three spots on Feb 16, 2016 at 7:00 AM for a downtown Houston zip code, 77002. The first result from Infuse Energy has a 3 month contract with the 1000 kWh price of $0.013 per kWh. Less than 2 cents for my power, let’s sign up immediately! Slow down, buddy. Let’s look at the reality of the contract otherwise known as the fact sheet. The fact sheet gives you what you really need to know about the contract you are signing. The fact sheet, in combination with your historical usage, can give you a very nice picture into what you’ll actually be paying. First, let’s assume you are free and clear of your previous contract and can sign with whoever you want. If I signed up for this three month contract on Feb 19 and used last year’s usage as the estimate, I would assume I would use 508, 725, and 909 kWh respectively over the next three months. But looking at the fact sheet, the price has a full paragraph with the use of bill credits – “$90 Usage Credit per billing cycle if your usage is greater than 999 and less than 1,501 kWh; $40 Usage Credit per billing cycle if your usage is greater than 1,500 and less than 2,001 kWh”. So according to their bill credit structure and assuming my usage holds true based on historical, then I wouldn’t get any bill credits and would end up paying closer to $0.11 cents per kWh – not anywhere close to $0.013 advertised by the 1000 kWh price. If over the next three months, you are using a little more than 1000 kWh every month, then this deal is awesome for you and you’ll get a rate closer to the 1.3 cent advertised price.

I hear what you are saying and I agree with you – caveat emptor. I should know what I am buying and ultimately, it’s up to me to understand the contract but for me to properly gauge the pricing and impact of the contract I am purchasing requires me to know my monthly usage for the past year and to take each and every prospective deal and map out the bill credits, minimum usage fees, service fees and other fine print items based on my usage patterns to determine what the best price is. If this is reality, then the only value PowerToChoose is to me is an aggregated place to find all the fact sheets.

What needs to change

The 500/1000/2000 values used for the ranking are ineffective and can be manipulated by retailers. They need to be replaced with a value in which consumers can do apples-to-apples comparisons across all the retailers in their service area. Assuming we are leaving out the one-off scenarios, such as contract termination fees or service cut fees, there are two different ways consumers can compare deals in an effective manner.

The more complicated but most effective way to accomplish this is to get PowerToChoose and SmartMeterTexas to create an interface to share usage data. As a retail customer, I would love to be able to log into PowerToChoose, give my ESIID or my service address and authorize PowerToChoose to pull my historical usage and apply that usage to the contracts available to show a total cost of the contract. If this is possible, then consumers would be able to compare contracts based on term (3, 6, 9, 12, 24 months) and do a true comparison between retailers. However, this is easier said than done. First, the interface to pull data from SmartMeterTexas would be difficult based on the current processes in place as SmartMeterTexas has some tight and unwieldy security requirements on sharing data outside of retailers themselves. Additionally, all of the fine print in the contracts would have to be standardized in a way to allow the software to do the math without having to parse the text of the contracts, which would be impossible using the current structures. Both would create fairly large changes to the ecosystem and without financial incentives to do so.

The second way would be to produce a standardized set of contracts for 3, 6, 12 and 24 months, where all of the baseline information is exactly the same across retailers except for the energy charge component of the price. Doing this would allow the multiple variable spreadsheet math needed by consumers today to be reduced down to comparing a single number between retailers, but to get a monthly estimate of the bill would still require some manual math using your historical usage.

Here’s an example of a standardized contract. Every retailer can offer the exact same contract terms and compete only on the highlighted energy charge.


2016-02-16 (3)


I don’t blame the retailers for the prices they publish. The residential and small business retail energy market is highly competitive and the ability to get a consumer to even click is highly sought after. As a retailer, you are almost forced to play the game, even if you don’t agree with the practice of using bill credits to manipulate the 1000 kWh price because of the value of being the “top 3” or “first page”. In a sometimes cutthroat and non-differentiable market, such as retail power, airlines, cars and others, companies are forced down to the lowest common denominator of marketing because not doing so, holding the proverbial moral high ground, brings the possibility of loss of market share, revenue and profitability – something that gets marketing people updating their resumes. Until the PUC decides to make some changes, the PowerToChoose website will continue to be a land of caveat emptor. It’s up to you to do the math.

Definition of Done

done When a developer says they are done, what does that mean? I’ve worked on many different client projects and it’s been slightly different every time. Developers are often left to take responsibility on ensuring a task is complete, however many times they are left with subjective, nuanced and changing job descriptions based on the whims of project managers or the business. A good Definition of Done not only brings value by ensuring completeness of work, but it also shields the developer from the random, tedious requests. Here’s a few principles on creating your team’s definition of done.

  • Write it down, communicate it out and revisit it every sprint. Do not assume the team members know and understand what it means to complete a task.
  • Use it during estimations and sprint planning as a baseline for what needs to be done for each task, but don’t get too rigid with it. It’s a guideline, not a mandate.
  • Don’t try and get too enterprisy with it. While a similar pattern or template could be used across teams, make it unique for the technical team with the details needed to make the process easy and complete. Need to update the user docs? Put a link directly to the document in the checklist. Again, make the right thing to do the easiest thing to do.
  • Peer review the work and create an incentive for the team members who have the most reviews per month. It doesn’t have to be big, but giving out a $25 gift card to Chili’s or buying them a funny tech t-shirt can do a long way to encouraging the behavior.
  • Good task flow is a balance between executing on parallel work streams and not wasting people’s time. Key checkpoints can save the developer time and effort.

Now that we have a good baseline on how to use a DOD, let’s take a look at a basic example. This is the template I take to most projects as a starting point and adjust according to the particular companies details.

  1. Working Code – This should go without saying, but delivering quality code satisfying the tasks or issues is the highest priority.
  2. Unit Tests – Good unit tests covering the core functionality or bug fix goes a long way not only for the current effort, but for use throughout the life of the code base.
  3. Integration Tests – Does the code changes have any effects on external interfaces or data integration? If so, a decent set of integration tests to cover the functionality goes a long way.
  4. Automated Regression Tests – update or add new automated regression tests to cover the functionality. These should be plugged into the existing automated regression set and should serve as the baseline of how developers prove their code works and meets the requirements of the task.
  5. Checkpoint 1: Peer Review – At this point, the developer has completed the code, written the tests and tested the change in the larger context by running the accepted regression suite. Prior to contact with the business, the changes should be reviewed and accepted by a peer or the team as a whole. This peer review should be handled in less than 10 minutes but goes a long way to ensure people’s work is holding to team standards and expectations.
  6. Checkpoint 2: Business Review – After the peer review is complete, the business owner should be notified and provided with the documentation to justify why the task should be considered complete. Getting a signoff from a user prior to deployment to downstream systems (QA, UAT, etc) saves huge amounts of time related to feedback loops. This business review should be a final checkpoint, not the only checkpoint. The technical resource should be communicating with the user as much as needed throughout the development process, whether it be UI design, data validation requirements, or other questions.
  7. Updating Technical Documentation – take 15 minutes and update the docs. In the team’s DOD, you could even have a list of the docs to be updated. ERD, process diagram, screen validation documentation, etc.
  8. Updating User Documentation – take 15 minutes and update the docs. In the team’s DOD, you could even have a list of the docs to be updated. If the UI changed, update the screenshots and provide the new instructions.
  9. Update Task – Once all is completed, the final step is updating the task. Again, it doesn’t take long (15 minutes) to write a good explanation of what was done, what was touched, what tests were written or changed, signoff info and getting the documentation updated. At this point, the task’s actuals should be updated to show the time spent and document any anomalies. Use this time to also push anomalies to the project manager to be reviewed and possibly get them addressed, if they might become longer term issues.

Some developers and team members may look at this list and see nothing but busy work. While it can sometimes seem tedious and boring, following these processes actual protect the development team from volatile business expectations and gives a kind of social contract allowing the developers to properly cover all needed aspects of delivery. It’s also a great tool to have in hand during sprint planning or general task estimations – a small change in code may take 2 hours, but updating the documentation, writing tests and updating the users to impact changes may double that time to 4 hours. Having the DOD helps everyone baseline their tasks and estimate their throughout more accurately.

The Best Tool in Software Development: Automated Regression Testing

When I think of automated testing, I think of the old proverb: “The best time to plant a tree was 20 years ago. The second best time is now.” Automated regression testing can be your greatest timesaver: more than good architecture, more than good requirements and more than hiring overpriced genius talent. It’s the thing that reduces downtime, eliminates cycle time, and gives you the sense of comfort your cell phone won’t be ringing at dinner tonight. And it doesn’t take significantly longer to write an automated test than it does to perform a similar manual test only once or twice. Writing automated regression tests are like buying a tool for $20 once instead of renting a tool every month for $15. But even that analogy doesn’t work, because the benefits are actually exponential due to the applicability of the tests on normal and critical releases.

Let’s take a case where a team of 5 QA resources perform manual regression testing on an application and the full testing cycle can be completed in two weeks. They receive a release candidate and perform work on Monday with a goal of releasing the software for the next Friday. Sounds simple, right? During the 4th day (Thursday) of testing, a significant bug is found. The QA team sends the bug back to the development team who fix the bug in 2 days (Monday morning), resulting in changes across significant portions of a core workflow, with resulting changes affecting the database, application and even changing the tests themselves as part of the bug fix involved changing the workflow slightly.

  • What is the QA team supposed to do after the bug is submitted? Do they continue to work on testing other parts of the application? They really don’t know at that point what parts of the application are going to be affected by the code changes for the bug fix.
  • The QA team get the fixed release. Does the team need to start testing from scratch? To perform a full regression and make sure the bug fix didn’t inadvertently break something they already tested, they will need to start from scratch. Any other choice creates unknowns and that is where downtime lives. Some may make an argument that the first 2 days of testing where on completely unrelated modules, however if a developer accidentally checked in something wrong or a configuration change didn’t get redeployed with the newer version, then the opportunity for failure is there.
  • The newly delivered software starts testing again and on day 4 (Thursday), two significant bugs were found. At this point, the software that should be delivered tomorrow is now not even going to start their full 2 week regression test until at least the next Monday (based on developer estimates) so the release is already two weeks late. Any other bugs could cause similar delays. At this point, the business users are forced to make a decision: release the software with incomplete testing and hope the changes made during the bug fixes don’t affect previously tested modules. Hoping for best cases is a terrible place to live.

Let’s take a case for a critical bug fix. A recently released upgrade is broken due to a bug in the order processing workflow for your largest client. Your largest client cannot perform their business! The ticket is opened and the development team works 20 hour days for 3 days to fix the issue. By the time the fix is provided, it’s been coded by developers who haven’t slept and have been given permission by management to do anything regardless of what it is to get this fixed. Overworked and under intense pressure, with code commits coming from 4 different developers, the development team gives a new release that they say “should fix the problem” even though they only tested it once or twice. The QA team receives the new release of the software.

  • Are they supposed to perform a full regression test? Two weeks until your largest client can process their orders? Do you want to lose them?
  • What part of it are they supposed to test? Obviously they would test the particular client can process their orders but do they test other clients? How many other clients? What processes were touched by the code changes? At this point, the business users are forced to make a decision: release the software with incomplete testing and hope the changes made during the bug fixes don’t affect previously tested modules. Hoping for best cases is a terrible place to live and eventually, hope does not survive.

How to Start?

First, make sure there is emphasis on both parts of the term “automated” and “regression”. If the process is not complete, consistent and idempotent – meaning the results should cover all functionality and should be the same given the same inputs every time – and able to be run in a short amount of time, then it’s not going to be of value. Most of the time, these automated regression tests can be linked to an existing build or release process but don’t get bogged down with automating your entire release flow – even an automated regression test that requires a user to go and point it at an environment and click a button is better than the alternative.

If you are starting a greenfield application, it’s really easy to get this done. However, most of the time the application that needs an automated regression the most is the lumbering technical debt wasteland of your most antiquated legacy systems. The goal should be totally a kaizen approach – every time a developer commits code, make a new regression test. If 5 regression tests are written every week, you will end up with over 65 regression tests in less than a quarter and after a year, with 250+ regression tests, you should have significant coverage. Don’t care about where you start, just recognize the value and get it done. Even if you can automate half of the regression tests needed to release, it still creates 24 weeks of slack to allow your QA team to work on writing more tests or even more regression tests.

Where to Start?

Dave Ramsey. The guy is a genius not because his total money makeover is complex, but because it is simple. Take all your debts (in this case, a full list of everything you need to get tested) and line them up in terms of value. What one test can you write today that would allow you to sleep better at night after a deployment because you know that piece works? That’s what you start with. Make a team goal to write 5 tests a week and before you know it, you’ll sleep like a baby. The added benefit is the snowball effect this will bring to the team. More automated regression means less critical bugs which means more throughput for the team which means more time allocated to writing regression tests.

A small note for the business, if you are always wondering why it takes so long to release software, this is usually the bottleneck. If bugs are consistently being released or you ever have the same bug happen that was supposedly fixed in previous releases, then go ahead and throw the automated regression tests into your backlog.


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 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.


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.


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.


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

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.

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.


You see that base href url? Well, I did. And I clicked it. And thank goodness I did because my life just got a ton easier.


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 ( 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:ReportName>DAM Settlement Point Prices</ns0:ReportName>

Now, we can use these reportTypeIds in the other web service to give us a list of the docLookupIds with some supporting information.

  <ns0:ReportName>DAM Settlement Point Prices</ns0:ReportName>

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:

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.

Four Keys to Flow – Creating Developer Productivity

river-89180_1280The flow state is the key to developer productivity. Getting into flow, where the developer seems to enter a state of hyper productivity by removing context switching related to distractions, has been determined to be one of the best ways to achieve high throughput from development team. To create a flow state, the environment in which the developer is working needs to be clear of distractions allowing them to focus on the task at hand, and get really engulfed in the now of the problem. Several studies have shown the costs of context switching to be huge, sometimes resulting in delays of 15-45 minutes while the person can get back “into” a problem or algorithm they were working on after an interruption.

Here are four ways your organization can create greater opportunities for your employees to enter into a state of flow.

  • Remove Electronic Stimulus – if flow is the hero of the story on developer productivity, then the bad guy has to be email. Most email is worthless and a drain on time, but even worse is the perception that email needs a timely response. Change your organizational attitude towards email by eliminating the perceived requirement of being available to email 24×7. Instead, allow your developers to turn off that email and check it at regular intervals instead, perhaps for 15 minutes in the morning, before you leave and perhaps once after lunch. If your employees are sending or reading more than 45 minutes of email every day, it’s too much. Also turn off the IM – make yourself Do Not Disturb. If they need you for a priority 1 type emergency, they will call your cell phone. Don’t worry.
  • Create No Meeting Blocks – the organization needs to create windows of long, uninterrupted time for the developers to get into flow. Back to back meetings with one hour windows between is almost worse than all day meetings on developer productivity, because the code resulting from those 45 minute blips will be disjointed and result in a ton of rework. Block out time for the developers, start with Mon-Wed-Fri between 9-2 and see what happens to your productivity. Oh, and no meetings on Friday.
  • Make Flow Visible to Others – back when everyone had an office, if you needed to get into flow, you just shut your door. With open offices and floor plans, it’s now difficult to shut yourself off from interruptions. The organization needs to setup some identifiers to allow potential interrupters the ability to stop themselves. The headphone rule is a good one, where if someone has headphones in, do not disturb them. Some workplaces have flags at their desk to tell everyone if they are open to interruptions. Whatever methods you choose, make them visible and well known to all so instead of breaking someone’s flow, they can read your email later.
  • Empower Pushback – for all but the highest priority items, such as outages, the team members should be able to rebuff interruptions. Let’s be honest, some players in your office don’t feel like the rules apply to them and will often break protocol at any time to fit their needs. The organization needs to empower the developers to push back. A polite response of “I’m in flow, please send me an email” to these interruptions gives the developer the ability to get back to the task without issue. But persistent violators will need to be handled by management and the organization must empower their managers to call out violators and get them to respect the rules.

The suggestions above are a few ways to quickly gain productivity in your workplace but implementing these are sometimes difficult to manage, as non-IT folks who use email as their work queues and are used to getting immediate responses may revolt at the perceived delays in their own productivity. This organizational behavior change must be something that not only comes from the top down, but is also something needing respect from every level in the organization. If the VP decides to break protocols, it’s difficult to enforce it on the directors and so on. So remember, unless it’s an outage send an email and they will get back to you after they are finished. I promise.

Identity Crisis

A few months ago, I was having a conversation over coffee with an IT operations manager with a very large company about their lack of standards in managing deployments and dependency on manual processes. As these conversations usually go, what started as a very targeted discussion about optimization of a single aspect of software development turned into a fantastic philosophical journey across the entire spectrum of issues, from everything to managing executive expectations and what software development will look like ten years from now. Keep in mind, this is a multinational company with hundreds of programmers, ops, and IT support employees managing millions of lines of both completely custom software packages but also millions of lines of code related to customization of vendor software packages with software budgets in the 100’s of millions. That’s when the statement was dropped:

“We aren’t a software company.”happygilmore

Much like Happy Gilmore would never admit he’s a golfer, but rather a hockey player, there are so many large companies out there having this identity crisis and refusing to acknowledge reality. If the product produced is not software, this does not exempt the operations from failing to adopt good processes and standards related to software. For example, if an employee of a widget manufacturing company was asked what the company did, the answer would be making widgets. In the same line, if you asked their CFO if the accounting department followed GAAP standards, the answer would certainly be “yes”. However, when you walk into the IT departments and ask the developers or software managers whether or not they do things like unit testing, regression testing or work towards automated security controls, you would be very surprised how often the answer is no. Why is there a perceived difference?

The main difference is visibility. Auditors don’t come in to make sure good software development standards are in place, but they do come in and pour through the ledgers to ensure accounting is happening according to standards. If standards aren’t in place for your accounting department, the pain can be immense, especially for public companies. However, the reality of following accounting standards is not simply because people will be checking. The standards are in place because of the collective experience in business of understanding that following standards is a good thing from a productivity standpoint. In the long term, standards create efficiencies and reduce costly errors. These same companies choose to implement rigid standards in one aspect of their business, yet leave other departments with hundreds of employees and millions of budgeted dollars free of standards.

Yet leaving the basic standards, like unit testing and automated builds, out of the software development processes will be visible, but just not in the same way. That production outage last week because of a last minute code change? That was because there weren’t any unit tests. That year long million dollar software upgrade that turned into a 3 million dollar two year effort? That’s because their weren’t any requirement standards, performance testing or architectural standards in place. The lack of standards in software development processes are very visible and are equally as painful as an accounting department not following GAAP, but the difference is the inability to draw the correct conclusions and root cause. The employees ultimately responsible will attribute the outage to the bad code or the poor requirements, but the answer is actually a lack of effective standards and best practices.

Just as GAAP standards won’t fix every accounting woe, great software development practices won’t catch every issue but it sure will stop a lot of them. So the next time there is a big production outage, ask a different set of questions:

  • Can you provide me a list of all the unit tests that were run for this?
  • Did the regression tests fail for this release?
  • When the rollback scripts were run, why weren’t they effective?

The answers might surprise you.

Choosing an Enterprise Trading Communication Protocol

In building modular software for energy trading organizations, each module is designed to perform a particular set of tasks, usually correlated to the offices: front, mid and back. The main purpose of creating modular, independent systems is the ability to target a particular set of features without having to account for everything upstream and downstream the IT value chain. Building software for exchange order management requires a different set of behaviors and characteristics than creating invoices for your residential power customers. Building the entire value chain of an energy trading shop into a single application may work for startups and limited scope, but will quickly decay in the face of  scale. To create scalable systems, modularity is required but with modularity comes the requirement of data interfaces.giff30eNVU7z7

Building data interfaces and APIs is an important part of building these types of software. For many different reasons, architects and developers involved will spin their wheels creating a new, completely proprietary way to describe the nouns of their system, such as trades or positions, instead of using one of the multiple, proven protocols already available for free. Usually the arguments against using the industry standard protocols internally are around perceived unique requirements or considering these fully baked protocols to be overkill. However, the long term implications of building a proprietary communications protocol will create an ongoing and expensive technical debt within the organization as the scope of the protocol expands and disagreements between system owners about the “right” way to model a particular interaction. It’s much easier to stand on the shoulders of industry giants and leverage years of trading experience by using proven and widely adopted protocols, while also creating an externally managed mediation point for disagreements regarding how and what gets communicated.

Right now, there are three main standards for modeling communication for energy trading. These standards have gotten quite a face lift in the past 4 years, due to their expansion related to changes created by Dodd-Frank and other regulatory legislation. These standards includes both format but also some standardization of content, allowing reuse of enumerations to describe certain trade values, such as settlement types or asset classes.

  • FIX / FIXML – Financial Information eXchange – This protocol is the most mature, as it was born from equities trading and other models that are purely financially settled. However in the recent years, the protocol has expanded into several different venues of commodities trading including being the protocol of choice for ICE’s Trade Capture and Order Routing platforms, as well as almost all of CME’s entire portfolio of electronic connectivity. This model is more normalized, in the sense of having multiple reference points to different related data. Instrument definitions could be communicated using different timing or venues while trades may just refer to those instruments using codes, allowing for faster communication.
  • FpML – Financial Product Markup Language – This protocol was recently used by DTCC to implement their SDR and while it’s much more robust in it’s implementation, it has quite a bit of a learning curve. The communications structure lends itself more toward each unit being a complete and total description of a transaction, duplicating standard data across transactions such as product or counterparty information. This protocol is XML based and much more verbose, but allows finer grained controls around things like date logic for instruments. The protocol also has multiple versions, tailored to specific needs of the organization.
  • CpML – Similar to FpML, this protocol is named directly for commodities descriptions and although it’s more widely adopted across the pond in Europe around EFET, it’s value holds for US based organizations.

But picking a standard protocol is only the first step. There are some additional concerns one should keep in mind when implementing these protocols to reduce the amount of headaches later.

  • Treat internal data consumers exactly the same way you would treat external data consumers – like customers.
  • Create a standards committee that has power to define and model the system interactions, but make sure the committee can make quick decisions through informal processes.
  • Always force any requests to extend the model for proprietary requirements through a rigorous standard to ensure those designing the interfaces aren’t simply doing the easy thing, rather than the right thing. I am always amazed about how quickly an organization can throw out the need for technical standardization when faced with a small decision to adjust an existing business process.
  • Broadcast the event generically, allowing listeners to determine what they need and allowing them to throw away what they don’t need. All else being equal, it’s easier to broadcast everything rather than open up individual data elements one by one.
  • Create and use common patterns for interfacing. Having one system be request-response and another be messaging based will create just as many issues as proprietary XML protocols.
  • As always, make the right thing to do the easy thing to do for developers. Make an investment in tooling and training for the stakeholders involved to ensure success.
« Older posts

© 2018 Stephen Nimmo

Theme by Anders NorenUp ↑