Yes and No… Once things are working smoothly I/you can easily compare any two rates as long as they fit in the OpenEI tables cleanly. The only issue is that, as you have determined, the OpenEI tables aren’t as up-to-date as they could be. The good news is that OpenEI tables are fairly well designed so it would be easy to dump out an existing set of tables into Excel (3 different relative small tables - Weekend TOU periods, Weekday TOU periods and TOU/tier rates. Then you could modify the tables to the new shifted rates and periods.
Give me a couple days and I would like to work on this one with you, because access to actually bills is crucial to getting things right. I would like to do a couple things first.
Categorize other problematic tariffs - the too highs and too lows and the NA (see above)
Figure out how to add the sellback calculation, it’s tricky because in California even for a single TOU or other net metering rate, there is a different calculation for folks with NEM1 vs NEM2. I may push this out if it gets too ugly.
Two things seem wonky about the biggest offender, the Southern California Edison TOU-D-4-9PM rate.
First, it’s not in the website version of the database, but is in the downloadable one. I’ll have to poke around to understand why.
It also has a typo in it… When I push down into the rates per period, there is clearly a pricing problem in the database - period 5 has 185$/kWh pricing ! That could give one a 91K$ monthly bill.
In sorting this out, I have also discovered that there are two tariffs in the database with this rate name and utility name ! Time for a uniqueness check before using the database.
Turns out I have 186 duplicated utility/ratename combos. Time to go back to my database preprocessor to figure out how to only pull the most recent and remove any bad duplicates.
Update
I made a poor assumption when I filtered out just the active, residential tariffs from all the tariffs ! I assumed that if the tariff had a start date but no end date, it was active. Turns out that the Southern California Edison TOU-D-4-9PM tariff was never approved. That also explains why it wasn’t displayed in the web-based database.
Because of the way I did the filtering and data reduction, I can’t easily pull the offending costs out of my results data, so I have to rerun all the calculations on a newly filtered version of the active, residential tariff database. But I get to test the speed of the run on the new reduced number of tariffs (has dropped from 6460 to 5983). I’ve pulled out all the file reads and writes except for one status print statement. Seems to be taking about 1 sec per tariff for my yearly billing.
Update 2
Here’s what the jitter graph looks like once all the remaining “unapproved” tariffs are pulled out. Better, but still lots of very expensive monthly bills with some rate plans…
Next step: I took a page out of @aj.vandenberghe 's suggestion on another thread to focus in on the top 10 utilities to see if that helps reduce the cost outliers in my calculations.
Of the “top 10” above, I was able to automatically corral 9 of them into their constituent companies, but I can’t find Dominion residential rates in the database. For those 9, I get the results below:
Much less scatter, but still more than I would expect, plus some “negative costs”. Bottom line - Utility rate tables are strewn with some real corner case rates, or at least the OpenEI database is.
When I look into the tariff database, Dakota Valley Elec Coop Inc, quite literally has negative rates for those special situations. And if I look through the rate structures for most of the tariffs that result in negative or zero costs, I see either negative rates, or zero rates up to a very large first breakpoint (that first one is free electricity up to 600kWh !) :
It is not uncommon for some Coops and Munis to have high base customer charges compared to IOUs, which guarantee they’ll get about equal money from all users up to a given size. I wouldn’t call that “free electricity”, they’re more than paying for it through other months or through other meters on the same premises that aren’t as specialized in their intention and application.
Looking at the rate names (“grain drying off-season”?) shown in your image, these are highly specialized for farm (“houses”, sure) rates, and are likely to have incentives related to balancing their loads around agricultural seasons (emphasis “off-season”) processes to not overwhelm their systems. This is rural North Dakota, the utility has 6000 customers. Small farm grain drying is going to be their typical “industrial” user even if they call them “residential” rates.
My suggestion, if you want to consider only the IOUs to start, toss the Coops and Munis and their often weird rates aside for a moment, and just focus on IOUs.
Edit: The other key suggestion is to eliminate those highly process-specific specialized rates where you find them also, in the example of the grain drying, I can all but guarantee that that is not the same meter as their primary residence meter where one would actually care to install something like a Sense Home Energy Monitor.
I suspect a number of what you’re calling “high cost” outliers you’ll never “eliminate”, the dirty truth about utility rate schedules is just because you fit or qualify for a given rate schedule, doesn’t mean that is necessarily the BEST rate schedule among other options available for giving you the best rate, and on top of that many areas do not have regulations to force utilities to try to make any attempt to make their customers aware that they are overpaying because they aren’t on the most optimal rate schedule for their use case.
@aj.vandenberghe,
Thanks ! Your suggestion did help, because it did reduce the scale of the outliers, and will give me a way to look at more “normal” outliers. And thanks for your thoughts on coops, and optimality of rates. I’m mainly running all the rates against my calculation to pick off the corner cases and determine whether I have to do fixes for any of them, plus to learn about the associated rate structures… I’m really only going to focus on the most “normal” rates, but even there I’m learning how much variety there is.
My second reason is because IOUs are more heavily regulated and are much less likely to have such “odd” rate schedules for such specialized use cases that are also perhaps more likely to not well-represent primary household service. That IOUs are more heavily regulated should also mean that finding detail on their rate structures is easier as they are more likely to be required to provide such detail. I went to the Dakota Valley Elec Coop website and they don’t even have their own rate schedules on their websites. Another sad fact about coops is even though they are member-owned and therefore considered to be “self-regulating”, the level of self-dealing and lack of transparency from their boards of directors can often make it easier for them to rip off their member customers. Local to me there are even groups - e.g. https://mnlocalenergyproject.org/ - that are trying educational campaigns and transparency score cards to try to show Coop customers that, in many cases, “self-regulating” is failing them, and they’re paying for it.
Pull out the coops, and you may see a dramatic drop in “high cost” outliers.
Thanks for the thoughts… @aj.vandenberghe,
Here’s how things look with utilities with “Coop” or “Co-op” in their name removed. One thing that I have learned from them and you, is that the meaning of “minimum payment” in the OpenEI databases means very different things in different calculations. For instance, for my bill, the minimum monthly payment is the minimum amount I pay PG&E each month (10$) against my net electrical energy bill for the year, with a big true-up at the end of the years, but the monthly payment is not a fixed charge, but rather a payment against my total bill based on kWh usage. in many coop cases, the minimum charge is a fixed monthly base payment, with per kWh charges above various caps added on.
Looking down the list of the largest billing cycle costs, it looks like most of the large ones are from Alaskan native region power companies. The largest major IOU rate comes in 1200$ or so.
If you find a list of just IOUs and their operating companies (some go by many names), LMK. I found a list of 132 of the 168 the EIA refers to in Wikipedia, but not all 168. I was able to build a list of 105 of those IOUs that are also in the OpenEI database… With just those 105 IOUs, the spread narrows.
Aha ! Finally found a good list of electrical utilities from the EIA that separates the utilities by ownership. There are more than 168 Investor Owned entities (202), but that’s mainly because entities that operate across state lines have different rate database entries for each.
If I plot my monthly billing cost for all active residential tariffs by month and by ownership, I get a lot of dots, but it’s fun to see.
The dots get pretty dense for some types of ownership, so one thing I can do is the stretch the vertical access by getting rid of outlier above 2000$, plus I can move to a “violin plot” that shows density in a little more analytic way.
Fascinating that the overall distribution patterns are very similar between the months… Now it’s time to focus in on mainly the Investor Owned companions and fix up and improve.
BTW - Here’s the lookup table should anybody want it… Four columns, the EIA utility ID, the OpenEI database utility name, the EIA name, and the EIA ownership classification.
The evolution continues… Now I’m just looking at Investor Owned utility rates - there are only 942 active residential tariffs for that category - jitter and violin plots of the cost distributions per billing period for all 942 rates.
Wow on the high end ! When I investigated most of the costs on the high end, I found that there was a per kWh rate, plus a per kWh adjustment and I wasn’t even adding/subtracting the adjustment. The ugliest cost was for McGrath Light and Power in Alaska that charges 71c per kWh, but has a -38c per kWh adjustment up to 500kWh (or an effective rate of a mere 33c per kWh.)
With the adjustments added in, the 7K McGrath Light and Power bill reduces substantially. And bills as a whole begin to moderate - fewer outliers.
Added in one more fix based on McGrath - some tiers are based on monthly/bill period maximums, others are daily maxes that are accumulated to the end of the month. Things look even better. Biggest effect from that change is that NONE of the monthly bills go to zero now.
And if I look at the most expensive bills, I’m seeing what I would somewhat expect as well, except I don’t see any Hawaiian power companies at the top - all Alaska and California.
FYI - That PG&E E1-Residential Service is the default residential service for single family homes in PG&E territory. That 48c/kWh top tier will get you every time if you have AC and EVs !
I have finally chased out almost all the sources of negative costs for all TOU periods. There were a number of “Delivery” tariffs that had negative rates or adjustments, often at off hours, so I removed all the Delivery-only tariffs.
Now for a little fun - I plotted where PG&E’s default E-1 tariff and my current EV-A tariffs fall in the distributions.
The triangles represent the PG&E E-1 costs, the circles are the EV-A costs for the same exact usage, both assuming NEMS1 net-metering. This chart reveals three things:
There are two flavors of the E-1 tariff for my region (Region T) - the upper one is for a normal household, the lower cost is for an “all-electric” household !
The spread in the distribution around the triangles is most likely all the other similar PG&E E-1 tariffs. There are something like 10 PG&E regions and each region has 2 flavors (normal and all-electric) so there are probably 20 similar costs in that region just from the PG&E E-1.
I’m seeing nice savings from the EV-A tariff vs. E-1 ! But my costs are still well above the mainstream.
If I’m going to see the real benefits of my solar install though, I’m going to need to do two cost calculations, the based on my Sense Total consumption and the second based on my Net (Total Consumption - Solar Production). Plus that net calculation will have to be done differently for tariffs that allow yearly Net Metering (buy=sell pricing) vs. TOUs with a different sellback rate. This is where I hope the OpenEI database has good guidance, to know which way to calculate. Time to look at that.
The next step was easy - add a second cost based on the same exact calculation but using Total Usage instead of Net. This represents what the cost would have been if I hadn’t had Solar Production coupled with yearly true-up net metering…
You will have to click on the graphic below to really see the details, but this is a violin plot of the full cost for electricity had I not had solar, across every active residential tariff I have been comparing against. Note that the max cost have gone much higher. The 4 points with different shapes represent my rate plan with and without solar, plus the E-1 residential plan, with an without solar, compared to all tariffs.
Solar helps far less in Dec vs Jun-Aug because there is far less solar produced.
High usage months spread out the cost distribution in a fairly non-linear way. Things like tiered pricing have a big contribution. FYI - for the partial billing periods, the tier maxes stay at the full billing period values so they show what the distribution looks like for very low usage billing months.
I save 200-300$/month in the summer month thanks to solar. And save between 500$ and 1000$ thanks to the combo of the right tariff and solar.
One more fun graph - the effect of solar/net metering on distributions. The distribution with the lighter lines when overlapped is the distribution with solar, while the darker is based on total consumption (no solar). as expected, solar plays a smaller role in shaving costs during the deep winter months and the partial months where tiering has yet to kick in (plus solar is lower).
Finally have most of the tariffs working and am ready to do a fun comparison. Below, I look at the $ savings between no solar and solar with net metering for all the applicable tariffs (tariffs that are marked as net metering applicable in the OpenEI database). This is all based on my Sense data (Total Usage and Solar Production) for 2019. Monthly savings vary as one would expect, as solar increases in the summer and drops off in the winter. The bars of dots are likely similar E-1 PG&E tariffs, 1 for every one of 10 regions perhaps x 2 for normal and all-electric households.
Please note that I haven’t been able to do California NEM2, only NEM1 pricing, because the OpenEI database doesn’t yet include sell prices that reflect the non-bypassable charges. Also now know I need to rewrite my code to resolve costing at an hourly level in order to do NEM2 calculation.
Still investigating the weirdities and possible update to the OpenEI database. In the meantime, I have a couple more full months of Sense data to apply to my rate/cost savings calculator. Along the way, I had to add the capability to deal with multiple Sense years of data.
Savings with Solar plus Net Metering vs No Solar
for all active, residential, investor-owned utility tariffs that allow net metering
For the intrepid, the R script is here along with some of the support files. I would need to email you the AllRates.rds database that contains all the rates. Them just add your own Sense hourly export file.
@2uskiwis,
I’m finally taking aim at your question - how to calculate gross monthly billing changes in a rate plan that is shifting, without actually knowing the new rate table. I have kind of finished my first go with the calculator and am taking stock of some of the TOU tariff “fine print” related to cost calculation resolution periods, the things that make the calculations very different from one TOU rate to the next.
In the next couple days, in my copious spare time, I’ll be finishing a calculator that takes in 2 current “period tables” (weekday and weekend) and 2 “new period tables”, as well 1 or more Sense yearly exports, and produces the TOU period usage for each monthly billing, along with the deltas for each period. I can pull the existing (or slightly outdated) period tables from the OpenEI database, then put in Excel, then hand modify to the new period tables (no grid editor - sorry).
I have a ulterior motive in this. I was also shifted in Nov’ 19, and I want to see if I am net positive or negative after the shift.
Here’s a taste - I have it working for just the weekday table data, when used for both weekday and weekend.
Update: Full year cost comparisons based on my rate calculator
Now that I have accumulated a full year of data with my resent Sense, I can do my yearly cost calculations, with and without solar, between rates in the OpenEI database.
First off, the same charts I did earlier. For every rate that allows Net Metering, what would the monthly benefit be from adding solar.
Savings with Solar plus Net Metering vs No Solar
for all active, residential, investor-owned utility tariffs that allow net metering
Monthly costs for my most recent plans vs. the rest of the plans
for all active, residential, investor-owned utility tariffs
But where do my results come in ? When I first installed solar in 2013, I was on a standard PG&E residential E1 tiered plan. With solar, I stayed on that plan, but moved to net metering (circle to triangle). About 2 years ago I moved to the EV-A plan for EVs, though I already had several EVs and the plan had been around for a few years (from triangle to plus). The square shows where I would be if I didn’t have solar. The big long violins show the the full distribution of what bills would look like across all the public company power plans in the OpenEI database. You’ll notice that even with the solar benefits and the EV plan, my costs are are the top edge of the distribution hump (CA PG&E is expensive), though there are some plans that are still far above in cost.
But I imagine everyone’s question is how much in savings did I see ? First a couple of caveats -
This is all done in terms of today’s rate plans, not “at the time” rate plans. Costs have likely gone up over time and there might have been other underlying changes.
I’m not really on the EV-A rate plan anymore, due to a variety of changes in both PG&E plans, plus my move to a Community Choice Aggregation energy supplier. But EV-A is still close to my current rate plan.
So it looks like my first move from non-solar to solar cut my bill by about 3K/year, offset by a 15K install cost back in 2013. But the second move, charging at night and moving to a ToU plan saved me an additional 5K/year.
Here’s the log scale. It does distribute things better, and does highlight the log scale of energy pricing… Plus it shows that even the lowest cost is not free (except for partial months). Does it work better ?? IDK. Would be interested in the opinions of others.
