Need a little help understanding the data export from the web

Technical Questions
,
3

Here is what I’ve found useful:

  1. I export all my sense data by the hour interval into a giant CSV file.
  2. Have Excel read in the CSV file, then save as .xlsx (so that auto saves capture the following edits within Excel).
  3. Put a data filter on the table contents.
  4. Add several new columns after the “DateTime” column, with the following headers and formulas (pasted down the entire huge table):
    1. Column header: “Year”
      1. Formula: =TEXT(A2, “yyyy”)
      2. Comment: cell will contain {…, 2018, 2019}
    2. Colum header: “Month”
      1. Formula: =TEXT(A2, “mmmm”)<\li>
      2. Comment: cell will contain {January, February, ... , December}
    3. Column header: “Day of Month”
      1. Formula: =TEXT(A2, “dd”)<\li>
      2. Comment: cell will contain {01, 02, … , 31}<\li>
    4. Column header: “Hour”
      1. Formula: =TEXT(A2, “hh”)
      2. Comment: cell will contain {01, 02, … , 23}
    5. Column header: “Day of Week”
      1. Formula: =TEXT(A2, “dddd”)
      2. Comment: cell will contain {Sunday, Monday, … , Saturday}
    6. Column header: “Billing Cycle” (similar to “Month”), or “True Up Interval” (similar to “Year”)
      1. Formula: (figure this out yourself if you care; I haven’t)
  5. Since Sense exports the “Solar Production” data with an apostrophe before the negative (production) number for both “Avg Wattage” and “kWh”, fix them by adding 2 new columns with the following headers and formulas (pasted down the entire huge table):
    1. Column header: Avg Wattage (fixed)
      1. Formula: =VALUE(IF(LEFT(G2,1)="'",RIGHT(G2,LEN(G2)-1),G2))
      2. Comment: strips away a leading apostrophe and makes the resulting cell contain a value rather than text.
    2. Column header: kWh (fixed)
      1. Formula: =VALUE(IF(LEFT(H2,1)="'",RIGHT(H2,LEN(H2)-1),H2))
      2. Comment: strips away a leading apostrophe and makes the resulting cell contain a value rather than text.
  6. Select the massive table, then insert a pivot table on a new worksheet.
  7. For the pivot table “Filters”, add “Name”. Certainly {Total Usage, Solar Production} are 2 good filter values are good ones to use. (Sense device identification problems make the others not useful in my opinion.) You can also select both “Total Usage” and “Solar Production” at the same time in the filter, if you want the pivot table to display energy “exported to grid” (negative numbers) or “imported from grid” (positive numbers).
  8. For the pivot table “Values”, add “kWh”. Depending on what kind of report you want, you’ll need to change the Value Field Settings to {Average, Sum}…maybe even “Max”.
  9. Now you can slice and dice the data in many different ways. I keep the “Grand Total” rows and columns, as they provide additional insight without having to even more pivot table variants:
    1. {Average,sum} energy {consumed,produced,grid_imported,grid_exported} by row=”Day of Week” for each column=”Month”
      1. Interesting trends by day of week can learned, as seasons change (monthly) throughout the year.
    2. {Average,sum} energy {consumed,produced,grid_imported,grid_exported} by row=”Day of Month” for each column=”Month”
      1. The output of this pivot table I can export to another Excel spreadsheet where I’ve modeled different tariff options to see which is best, and where I can play around with the historical consumption data like “what if I were to run my pool pump during “off-peak” hours rather than ‘peak’ hours?” or “what if I get another electric car and charge at night?”
    3. {Average,sum} energy {consumed,produced,grid_imported,grid_exported} by row=”Hour” for each column=”Month”
      1. Interesting trends by hour can be learned, as seasons change (monthly) throughout the year.
    4. {Average,sum} energy {consumed,produced,grid_imported,grid_exported} by row=”Hour” for each column=”Day of Week”
      1. Interesting trends by hour can be learned, for different days of the week.
    5. (lots more ways the pivot table can be sliced and diced>)
  10. I wish Sense developers would actually add this kind of report/metric generation natively into the app/web “trends” data visualizer. Heck, just the “imported from grid” and “exported to grid” would be highly desired…like the Enphase Enlighten visualization.
5 Likes