Tariff Trade-off Analyzer using Sense Data

[james_reilley]

@kevin1

I’m gonna try to build an R-based calculator that reads in a few key things, including Sense export files and produces a bill-by-bill comparison for all active, residential tariffs from any given power company.

@kevin1, this would be really awesome. I commend your interest and dedication for such endeavors.

Are you thinking also of layering on support for the various net metering programs that various utilities provide?..does the OpenEI Utilities Rates Database include these? e.g. for PG&E, are you planning to handle the NEM1* and NEM2* net metering programs?

What about handling the various options/rates that Community Choice Aggregation (CCA) providers for electricity generation? E.g., even though I have the EV-A tariff from PG&E (and NEM2 program too, though I’m ignoring that for this paragraph), my electricity charges is not based on PG&E’s EV-A tariff table, rather it is slight less because the CCA’s (Silicon Valley Clean Electric’s) EV-A GreenStart program’s electricity generation is a bit cheaper that what PG&E’s generation would be.

There is a utility provider in Arizona that has been testing out a residential tariff that includes demand charges. Over the years, I could see demand charges slip more and more into residential rates. Such tariffs could require 15-minute energy usage intervals, rather than just 1-hour intervals.

Anyway, “perfect is the enemy of done”, and any progress towards getting more accurate energy cost info is welcomed. I hope it is a priority for Sense to provide some additional support for more accurate energy cost within the Sense app; I believe it is one of the highest voted “wishlist items” (e.g. TOU pricing). Perhaps your efforts could serve as a catalyst for Sense development of something similar. Good luck.

[kevin1]

Thanks @james_reilley,
Haven’t started coding yet, but poking through the OpenEI database. You have hit on another level of complexity - CCA/utility combo pricing. I’m on a similar combo plan between PG&E for distribution and transmission, with Peninsula Clean Energy for generation. The OpenEI database doesn’t include mixes like this, just the base bundled PG&E tariffs. My thinking is that the base bundled PG&E plans are a good proxy for the best rate structure regardless of the generator since the PG&E base plan imposes the structure ( tiered, NEMs, TOU) on the CCA pricing.

Here’s a distilled version of the 67 PG&E active, residential rate plans, once I collapsed all the different flavors of the same plan for different PG&E “Regions” to just unique names. I got rid of all but my Region T, except in the cases where there were special plans for Region P. Not sure why Region P, which is the Sierra Foothills and around Clear Lake, is special.

The three parameters that contribute to the hourly prices are broken out.

• Net Metering - indicates whether a plan is eligible for Net Metering. But there’s also a hidden factor here of whether a customer is on NEM1 or NEM2. If NEM1, the net is calculated and billed yearly via the true-up. With NEM2, folks have to be on a TOU plan and usage is netted hourly. In this case, I think users are paid wholesale prices against any hourly net to-grid. Anyone with more knowledge than me on this one, let me know.
• Periods - the number of pricing periods the plan divides the year into. Can be divisions within days and also within years.
• Tiers - number of pricing tiers within a period

Fixed Monthly is the minimum monthly charge. The other columns are data that might indicate something important, or not, beyond the fact that all rates are per kWh.

[ixu]

@james_reilley

Such tariffs could require 15-minute energy usage intervals, rather than just 1-hour intervals.

This is an unofficial dare (@kevin1?) to port pricing to, say, a spare Solar CT input or a “device” so we can graph it at at least 1Hz!

[kevin1]

@2uskiwis, try querying the data to see if the tariffs you expect are in there. Each tariff has a 4 web page set of tabs covering all possible pricing components.

Utility Rate Database | Open Energy Information

[kevin1]

@ixu, right now, I’m just challenged to wade through all the hourly tariff craziness.

[kevin1]

OK - starting to analyze all the active, residential plans in the entire database, since I have unpacked the database a bit more. I’m trying to take a look at all the plans, but focusing in on the utilities with the most plans for discussion here:

Top 25 Utilities - Distribution of ToU periods/time domains
Here’s a view of the top 25 utility and their distribution of rate plans by period. Zero periods means the rate plan relies on something other than time to calculate rates. Many utilities have plans that are mostly just 1 rate period - a straight tariff, though they might also have tiering on top of that (next section). My PG&E EV rate plan has 6 different pricing periods, 3 time periods per day x 2 seasons (winter/summer). Based on this matrix, there are some rate plans, not associated with the top 25 utilities, that have 9 periods.

Top 25 Utilities - Distribution of Tiers
Here’s the same chart looking at the distribution of tiering across utilities. Yes there are some rate plans that have up to 9 tiers. As I have seen earlier in evaluating PG&E plans, there are a number that have both ToU and usage tiering components. In the database, tiering is expressed as a kWh/day breakpoint, but I know in the case of PG&E, the tiering calculation is only performed on a billing cycle basis (not daily) and the daily breakpoints are summed over the billing period, to form billing period breakpoints. This is a a good example of one of the places where the OpenEI database is probably not sufficient to get the calculation right.

Top 25 Utilities - Distribution of Net Metering Plans
Here’s the same view for distribution of Net Metering eligible plans. I say Net Metering eligible because, in some states/utiltiies, the number of solar installs might have surpassed certain limits. In those cases, not all Net Metering plans are available to post-limit customers. In California, the initial net meting plan, NEM1, has one set of rules, while newer customers entered under NEM2 rules which limited them to a smaller set of ToU plans.

Top 25 Utilities - Demand Charges
Here are the top 25 by the number of rate plans with demand charges.

I have also included my master spreadsheet here for anybody who wants to play around and examine.

ResRates.xlsx (482.6 KB)

[2uskiwis]

@kevin1 … it doesn’t seem to be current, at least to my untrained eye.

I’m on EV-TOU-2 (GF) and I don’t see it when I run the query. What I do see is this for EV-TOU-2

This looks to be about 18 months out of date…06/30/2018 specifically.

Here’s all the current and past SDG&E rates. I see 36 that do not have “Closed” or “Eliminated” next to them.
https://www.sdge.com/total-electric-rates

Click on this one and the rates match from the query…

[2uskiwis]

Because it affects me, I created and maintain a SS with the various rates so I can see how they’ve changed over time. The following shows the current and past rates for EV-TOU-2 and EV-TOU-2 (GF)

https://docs.google.com/spreadsheets/d/1YSvE53ufnHSFmqVZG3NDxvPlufscF5sTJkwagm6AkZA/edit?usp=sharing

[kevin1]

@2uskiwis,
I don’t think the OpenEI databases is completely current either… I was just switched from the EVA EV rate to an EV2A rate which isn’t listed… But that was fairly recent. Not sure how often this database is refreshed but it does include rates that only started in 2019. Even if it is out of date, I’m finding it instructive in how to do a massive comparison - how data needs to be structured to so I can run all the rates in the database.

[kevin1]

OK, I managed to muscle my way through a calculation using my Sense hourly net usage (Total Usage - Solar Production) for as much of 2019 as I have (Apr. 6 - Dec 31), for ALL 67 active PG&E tariffs. I have also done a comparison against my my matching 2019 billings, though there are still some ambiguities given I use a separate CCA (Community Choice Aggregation) supplier for generation.

So comparison first. For 2019, I was on the EV-A EV Time of Use plan, but with a different generation supplier so I was subject to PG&E’s Power Indifference Adjustment fee. I also chose a premium plan, ECO100, that is 100% renewable. That hybrid rate structure (separate generation + premium ECO100) isn’t in the OpenEI database so I had to emulate it by adding my PG&E NEM charges to my PCE charges, then subtracting off the ECO100 premium fees. Generally the numbers were close on a monthly basis though they occasionally did differ by more than 10% of the total bill. I’ll have to look a little bit more closely, but there isn’t an easy way to peek inside the highly variable PG&E “indifference charge”.

The upshot is that I think the calculator script is working though I’m going to have to do the hard work of confirming everything this weekend.

For those of you who want to see the broader comparison, here’s the whole spreadsheet kicked out by the R script. It’s clear that there are some rate plans that might be better for me, but I would really need to look over a whole year. I can see that my move from E1 for Region T, to EV-A has saved me around 1300$ for 2/3 of year, or maybe 2000$ over the course of year.

The Partial Start and Partial End columns reflect the partial months, in Apr’19 before the first full billing period, and the remainder of Dec’19, after the last full billing period in the year. The other thing that occurs to me is that this comparison is not very “diagnostic”. It really doesn’t give you a good view of why your bill is so high or so low. It probably would be useful highlighting billing periods where you spent the highest % of the time in the higher pricing tiers or pricier TOU periods.

Results.xlsx (20.1 KB)

Anyway, after doing a little more checking, it’s going to be time to expand to other utilities.

For anyone who wants to look at some junky but operational code, here’s a look. No attempt to functionalism, though I did work hard to use R parallel computations whenever possible.

PGEbilling.R (5.4 KB)

[ixu]

Excellent work @kevin1

Indeed, the “diagnostic” aspects add yet another layer of intractability, especially when you start to consider the real relevance (and realtime relevance especially especially) to (and of) Sense.

“Simple” case: Sense detects new EV and recommends “You should switch your TOU plan to X or I could delay charging until 3am or your gonna get a whopping bill”

“Hard” case: Optimistically, Utility EV plans are crafted, for obvious reasons, to distribute load throughout the day and across the months and seasons. Pessimistically, Utilities are often driven by profit, at best to improve infrastructure. Those optimizations (plan changes!) get faster and Sense gets in an optimization battle. Sense is also forced into a battle with opposing end-user requirements: lowering energy use and cost reduction vs. spending money to add solar AND storage capacity (Note: this is also at odds with said Utility infrastructure!). I say “AND” here because it seems like adding storage capacity really throws things into the realm of intractability.

You’ve taken a giant leap here. Props.

[kevin1]

Now it’s time for the detailed study of differences between my bill and my calculations, for my EVA plan… I have painstakingly pulled kWh data from my bills by TOU rate period for comparison against the same from my calculator. The top are kWh values from my calculator and middle is from my bills. The last table shows the % difference usage between comparable periods. Very weird ! Most periods are extremely close but a few have ugly differences !

Why the differences ? There are 3 possible reasons:

• Sense source data different than PG&E’s revenue data.
• Incorrect entries in the OpenEI databases tables (or bad matching on my program’s part)
• Some other systemic factor that would only affect a few months

On the first, differences between Sense and PG&E, I could try running the same calculation on my PG&E data (where’s the fun in that ?), but fortunately, I’ve already done some analysis of Sense vs. PG&E and found the time periods where I had the biggest differences, either due to dropouts or the monitor going crazy. That would explain some of the discrepencies in the 2019-04 and 2019-07 bills. But I haven’t had many errors in Nov.

Is there something wrong with the tables or my algorithm ? I don’t think so, because so many months look so good. The big issues seem to occur during billing periods that are split between seasons.

That leads me to my third possibility, some kind of systemic issue, that only affects a couple of billing periods in transition. It could be that the summer/winter periods don’t fall neatly on monthly boundaries as the OpenEI tables assume, but that would show issues on both sides of the season split. In the case here, it’s only the “from” season that is showing big errors. I’m betting that the issue is related to treatment of DST. If I look at my November billing period, my calculated Partial Peak is 25kWh too big, while my Peak is 16.5kWh too small, and my Off Peak is 8kWh too small. But everything looks good again in December… Any thoughts ?

[kevin1]

Time to investigate the usage differences between the calculator and my bill during a few select periods. As boring as it is, I’m feeding my PG&E hourly data into my R calculator to see how the usage compares per TOU period and per billing period. It should be nearly perfect, rounding error excluded. As near as I can tell, PG&E exports power information with 2 decimal places of kWh accuracy, while they keep around 8 decimal places internally and in their calculations.

When I do the comparison, though, three billing periods have significant differences and the rest are near perfect. What’s more, is that even for the months with the big deviation between calculated and bill usage in some periods, the total usage still remains accurate. That means that the usage is misdistributed within those 3 billing months, but accurate for the monthly total.

Looking closely at the calendar and those three billing months, 2/21-3/21, 3/22-4/22 and 10/22-11/19, I can see a pattern. Daylight saving time 2019 began on 3/10 and ended on 11/3, right in the middle of two of the months with accuracy issues. The official start of the summer schedule for the EVA rate plan per the table is 5/1 and the official start of winter is 11/1, both again in the middle of billing periods that have issues.

The weekday rate grid from the OpenEI database - rows are months, columns are hours and the numbers correspond to TOU periods.

Guess I’m going to have to poke deeper into those billing months and the rate table…

Edit: OK - one quick answer. A clause buried deep in the tariff document for EVA and EVB rate schedules points out a non-standard time adjustment…

“DAYLIGHT SAVING TIME ADJUSTMENT: The time periods shown above will begin and end one hour later for the period between the second Sunday in March and the first Sunday in April, and for the period between the last Sunday in October and the first Sunday in November.”

So for those two periods following the advent of DST, and prefacing the change back to standard time, PG&E is really using a different table.

Arrrrgh ! I have just been reminded why TOU pricing is so hard.

[kevin1]

I’m going to put the problems with my particular TOU rate schedule on hold (maybe an issue with all PG&E TOUs), and push on with running through all utility rates since my script seems to be working well within the limits of the OpenEI data. So I’m going to strip all the tariffs that I haven’t implemented yet, then try a near-full run. The two forms of tariffs I’m going to strip out for now are:

• Tariffs with demand charges - the code will be similar to the the TOU code, but applied a little differently.
• Tariffs without any TOU periods (53 of them below) - What the heck are those ? Looks like there are rates that are pure unlimited usage for fixed monthly fees in that category. Perhaps others.

Update
Cranking through the huge list of 6,460 tariffs has guided me make a few more changes in my program and rate database:

• Adjusted the code to handle tariffs that are a mix of tiers plus flat rates (some TOU periods have tiers, others are flat rates)
• Removed one tiered rate in the OpenEI database that had multiple rates, but was missing the breakpoints. Reported this to OpenEI.
• Cleaned up ratenames so they could be used as filenames - I store away each and every simulated “bill summary” for each rate.

Update 2
Slowly chugging through every active tariff in the database, discovering new things along the way.

• Removed all the tariffs that have ‘sell’ (sellback) prices but no buy prices (‘rate’) - what the heck are these ?
• Realize that I still have to add code for computing tariffs in solar sellback situations beyond yearly net metering.
• Have pulled another 11 rates with malformed pricing structures.

But more than halfway through the 6,400 or so tariffs…

[JustinAtSense]

Shared this with the larger team, @kevin1. Thank you for the amount of detail here, as always.

[kevin1]

Wahooo ! My calculator made it through all 6305 tariffs of the edited list after the removal of a few more rate-tables/rates that had problematic structure (at least for my calculator). Now time to look at the results. The first thing I’m going to do is do a sanity check by plotting all the billing period costs for all the rates, by billing period with as little jitter… This is the fun part.

Sanity check useful. Two things discovered:

• Some costs are way outside the realm of possibility on the high side. Either there are crazy rates, or some rates have other units besides kWh.
• I also a message during plotting that means at least 10 billing period costs were not available (NA), probably for a single tariff (10 billing periods). Somehow, some NA data sneaked in.

If I sort by billing periods costs, from highest to lowest, there are clearly problems, but this shows where I should start first.

The same on the low end. I love the “residential” tariff I moused over - Off season grain-drying rate ! Lots of negative an zero costs to be investigated.

Plus here are all the rates I had to remove because they were blowing up or crashing my computations.

[kevin1]

Thanks @JustinAtSense,
I have two reasons for doing this…

• It something that I really want to use. And some energy consultants have some similar tools, but from what I can tell, they are all swags because of the complexity.
• I also wanted to see what the complexity looks like. Just scratching the surface right now. I think a lot of people see TOU and tiering as simple, at least for their own rate structure, but when it comes time to implement a general solution, I’m guessing most would be dumfounded.

[2uskiwis]

Can you factor in what the bill would be if there’s a peak period shift coming up?

I’ll attempt to explain so you know what I’m referring too.

I’m with SDG&E but I assume both PG&E and SCE are similar so it will probably also apply to them. I’m almost 5 years since getting my PTO and I snuck in under on the NEM 1.0 rules. My “peak time” period is currently 11am to 6pm, but on the 5 year anniversary of my PTO, that’ll shift to 4-9pm. Obviously a lot of my generation today is during this peak period but in the future only a small part of it will be. SDG&E has a “pricing plan” feature on their site, that shows what your bill would be under the different plans but it doesn’t take into account this shift.

So, what I’ve been trying to do is estimate what my bill would be on my current plan, assuming the peak period has already shifted.

I download the 15 minute data from SDG&E daily and run a macro against it using the applicable rates today vs what they will be after my 5 year anniversary. I don’t know what the rates will be, but I know when the “load” occurred…I hope that makes sense.

Here’s an example from Feb 10:

This shows that my peak time usage is -4.015 kWh but after the Peak shift, I’ll actually be consuming 20.815 kWh @ the applicable peak rate. The impact of this in the winter is fairly small but when summer rolls around it’s another story.

At the end of the month I’m very close to my actual bill and have a pretty good handle on what it will be in the future but only because I run this macro to calculate it. From what I have seen so far, I’ll be switching back to the old tiered rates.

[kevin1]

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.

[kevin1]

Looking at the utilities / rates that appeared too big first - here’s a list in order:

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…

If I look at the highest costs this time, I see a whole different cast of characters.

If I look at the pricing for the biggest offender now, “Town of Ferdinand, Indiana (Utility Company)” Wholesale rate, I find energy costs 2.3$/kWh:

U.S. Utility Rate Database

There’s also a 7.9$/kWh Demand charge on the website, but not via the database… Hmmmm !