Download the data from both sources and convert it to a common interval
Step 1 - Export the data from Sense using the Export icon in the upper right corner of the Trends > Usage page. You can select any interval, but I like to compare at a yearly level (or more).
Step 2 - One you initiate the download, you’ll be prompted for the data interval. I typically use hourly intervals for comparison, since that allows you to more precisely isolate the biggest mismatches.
Once you hit export, you’ll get a CSV (comma separated value) file that you can read into a spreadsheet or an analysis program in R or Python. Here’s a snippet of my downloaded CSV in Excel. There is usually an entry every hour for every device that is detected and using energy during that hour. The only two that matter in the case of a solar install, for the purposes of this exercise are the Total Usage and Solar Production.
Step 3 - Do the same for your utility. My utility company has a Green Button Usage on the webpage that reports energy usage. See it down on the lower right.
Step 4 - Pick your time period and format on the page that comes up after hitting the Green Button. Here are the settings I use. I don’t have a choice of reporting interval.
Here’s what my utility’s CSV ends up looking like in Excel. Notice that it breaks power usage out into 15 minute intervals.
So the next couple steps are all about aggregating the utility data to hourly data.
Step 5 - Create a calculated field for every row that spells out the hour in text format, so that I have a hour value to aggregate over. Note from the formula that I use that I’m using text rather than an actual date, since Excel treats dates differently and slightly annoyingly when aggregating.
Once I have added the hourly markers per row, I can use create Pivot Table to sum every hour.
Step 5 - Create and specify a Pivot Table - that summarizes hourly data. Here’s how to set it up.
Align and filter the Sense data
Now it’s time to reformat the Sense data using a Pivot Table as well, but mainly to filter and reshape the Sense data. But first I have to create another calculated field that is the text version of the DateTime again, for the same reason as earlier.
Step 6 - Add a “DateTime Text” column using the formula shown.
Once I have that extra column propagated down the entire spreadsheet, I can create and tune the Pivot Table. The goal this time is to end up with the hourly DateTime along with 3 other columns, Usage, Solar and Net Usage.
Step 7 - Create the Sense Pivot table using the specification below. Please note that I’m filtering out all but two of the Names, Total Usage and Solar Production. The Grand Total on the right is magically the Net Usage.
Merge the comparison data by interval
So now I have two spreadsheets with Pivot Tables that have all the formatted and calculated data I need for comparison, and it’s time to merge them. Unfortunately, Microsoft has let me down, and pulled a feature that was in Excel 2016. They’re adding the merge/join (GET/TRANSFORM) back into Excel but it’s not there yet for me, so I’m going to do a manual merge, but by copying and pasting the two together into a single spreadsheet.
Step 8 - Copy the Sense Pivot Table and paste alongside the Pivot Table for my utility. Make sure the DateTimes match up. Fortunately, there were no missing hours in either list so the manual merge was easy.
Analyze and compare the two measurement sources on a daily or hourly basis.
Step 9 - The final step is to plot the data. It really takes two steps. Copying the final table one more time to a spot right next to the aligned Pivot Tables, because you can’t chart a Pivot Table directly. Then converting to a X-Y Chart and tweaking. Here’s the result ! A straight line with only a few scattered points. These means Sense was very accurate for a high percentage of the time. But how accurate ? Sorry, but for that one I’m going to switch from Excel to R.
The x axis is my “golden” utility hourly measurement and the y axis is the Sense Net Usage measurement. They both go negative in the hours when my solar is outproducing my house usage.