Happy New Years ! Time for a 2020 Excel Energy Audit

[kevin1]

We made it through 2020 ! Congrats ! This is the time when I look back at my electricity usage for the previous year, do some accounting and make changes for better analysis and reductions in the New Year. Usually I’ll do most analysis in R, but I typically do the high-level data assessment in Excel first, just to get my arms around the data I’m dealing with. And even though Sense has some very nice Trends analysis, I generally find I can sort things out better in Excel Pivot Table of Sense exported hourly data. Here’s how I do it.

Read in Sense hourly data for 2020 and convert to wide format

Sense data is exported in a long format, with hourly data for every device, plus Total Consumption (and optionally Solar Production) as separate time-stamped record. The first step is to elect it all an convert to a separate Pivot Table sheet.

The next step is configuring the Pivot Table to arrange your energy usage data in a wide format, with each device in its own column. Drag ‘DateTime’ into the Rows, ‘kWh’ into the Summation, and Device ‘Name’ into the columns and you will end up with the layout below. Excel automatically adds Quarter and Year grouping to the rows, but we’ll change that in a bit.

Sense actually carries over a little data fro 2021 even though I only exported data for the year 2020, so I use the Row Tables to limit the data to 2020.

The next step is to change the grouping for the DateTime data - I prefer to see Hours, Days and Months.

Once I have the Rows grouped the way I want, I can collapse all the Rows back to highest level, Months.

Time to clean the data
Once I have things grouped the way I want, I can get rid of the Pivot Table Field List, and begin to clean the data. One thing I notice is a column without a name, “(blank)”.

I can double-click on that 0 for “(blank)” and see all the hourly data that rolls up into that 0. It looks like this was a temporary device that Sense never converted into a real device. Can be removed.

To remove, I got to Column Labels and deselect “(blank)”.

Another thing that I’m going to want to do is to sort out all the Vacuum devices that have showed up over and disappeared over time. We have a Central Vacuum, which is used most of the time and do have at least one other plug-in vacuum. This shows me that I’ll either need to mask other out or roll into the Central Vacuum. Also, the Traveling HS110s have been on different devices through out the year. The first one has been on one of my right garage opener since about June. The other HS110 seen most of Oct. inflating a Halloween dragon in our front yard.

BTW - the “Grand Total” on the far right is pretty much a useless summation since it includes all detected devices plus Total Usage plus Solar Production.

[slac101]

@kevin1,

I appreciate the tutorial. Lots of people on this forum with my more Excel skills than me. I will try this out when I get a chance and report back on my usage. Granted I only have about 6 months of good data.

Thanks again.

[kevin1]

Thanks @slac101, I enjoy exploring my data and sharing my discoveries along the way. Here’s a second lesson on Data Cleaning

Making Sense of the Sense Data
While one could certainly puzzle through Sense data in the app, I find a wide Pivot Table better suited for doing comparisons and cleaning. Here’s how.

First I select just the “similar” or overlapping devices I want to resolve. In this case, I’m going to look at all “Vacuums”.

I know that our cleaning person comes and uses the Central Vacuum around 11AM or noon on Wednesdays. It looks like December has a shortfall in the Sense “Central Vacuum” category, so I can go searching for possible mis-categorization as another “Vacuum”. Dec 2nd, 9th, 16th and 23rd are all Wednesdays, so I can probably assume that “Vacuum 5” might be a mis-categorized “Central Vacuum”, with a perhaps another off-cycle running on the 18th.

But then, if I look at September, which has a larger “Vacuum 5” component, I can see that it does not fit the pattern. Bottom-line - inconclusive. But you get the idea. I can also see that the “Central Vacuum” does indeed mostly fir the Wednesday pattern, with usage on the 2nd, 9th, 16th and 23rd. I do have two other plug-in vacuum cleaners in my house, a ShopVac and a Hoover, plus my housecleaner has brought their vacuum on occasion. It might be that I never completely resolve this one.

I can also look at my EV charging detections to see how they look and if they appear to all occur during my appointed charging times between midnight and 6AM. The Electric Vehicle appears to correlate well with our Model S that starts charging at midnight. The Tesla Model 3 correlates well with the 3:30AM charges start I have that one programmed for. I did delete the Electric Vehicle device late in the year because the Sense detection was fairly accurate from a on/off perspective, but has only indicating about half the used energy.

[joel.s.c14]

Wow, looks like I have some reading and learning to do. Very interesting!

[kevin1]

Preview - for my next step, I’m going to try to create a pair of weekly usage charts like the ones below using Excel. For me, it requires merging of data from 3 sources, my main Sense, my Second Sense that has 2 DCM devices on it and from my own algorithm to detect and quantify EV charging. Sense has done some reasonable detection of both my EVs over past couple years, but never rock-solidly consistently for an entire year, so I tweaked my own.

These charts show absolute and relative weekly usage patterns over 2020 of the top 8 individual device, the rest of the identified devices combined and other, plus solar production (negative usage).

A few comments:

• I had a couple problems with my Sense monitor Oct 5-6. It was producing negative Total Usage and I had to reboot it.
• The Dryer category at the beginning of 2020 contains floor heating as well, because Sense conflated my dryer heating element with the floor heating loops. Later in the year, with some help from “Device is not On”, Sense seemed to get better about distinguishing. I added DCM for the entire floor heating subpanel in Oct. to separate out the floor heating, but it looks like a little floor heating is also still getting pulled into Dryer, but far less.