WeMo Insight Switch Automatic Data Logging and Plotting

The WeMo Insight Switch provides automatic energy data logging which great for us energy nerds, but the software tools provided are extremely limited. The interface to the WeMo Insight is only through an Android or IOS App that provide ZERO data logging functions. You can't see any graphs of the data logged or any history. Perhaps Belkin will provide that feature in the future, which would be great because this is really a nice device otherwise.
Data can only be exported by setting up a daily email. That data can be opened in Excel or Google Sheets and a graph created. This seems a little cumbersome and I wondered if there isn't a better way to do it.

The Vision

A web page that shows the energy usage history for the device plugged into the WeMo Insight that is automatically updated daily when WeMo sends out the daily email.

The Result

The result can be seen in the image above and in the following links for four WeMo Insight devices I have in my house.

        1) WeMo TV    2) Livingroom Light   3) Wife's Computer   4) My Computer

If you think you would like to do the same thing the steps are below.

The Steps

There are a number of steps that are needed to make this happen.
  Step 1: Configure the WeMo Insight to send out a daily email.
  Step 2: Automatically save the email attachment
  Step 3: Automatically update the data set daily with the new data
  Step 4: Automatically update the website graph
There are a number of different approaches that can be used to make this happen. I choose to use all tools from Google to execute the project from End to End.  1) The WeMo insight sends an email to my Gmail account, then 2) a custom Google script looks for new emails and automatically saves the attachments to my Google Drive, then 3) in Google drive, I have a Google Sheet (their equivalent of Excel) that contains a custom script that automatically updates the new data from the CSV file. And lastly, I have a Google Chart  that is embedded in a blog post that is connected to the Google Sheets file.

Step 1: Configuring the WeMo Insight to Export data

It is fairly straight forward to get the WeMo Insight to send out a daily email.

1. Click the menu button (three stacked squares)
2. Click "Edit Device"
3. Select the WeMo device of interest (there doesn't seem to be a way to do all at once.)
4. Select "Data Export"
5. Enter the email address
6. Turn on "Scheduled Export"
7. Select the interval, e.g. Daily
8. Optionally, you can test this setup by clicking "Export Now"

If all goes well, you should get an email with an attached CSV file just after midnight.  This should happen every day. One frustration I have with the app, is that if you later return to this screen, it might not show the correct settings like email address.  Don't worry about that, it seems to work fine.

   The CSV file contains three different types of data

1. Header showing the device information
2. Daily energy information including how long the device has been ON and OFF
3. Half Hourly information, which is just the KWH consumed.

Step 2: Automatically save the email attachment

This step was very easy thanks to Amit Agarwal's script that automatically saves attachments. I made two minor tweaks to his work and created my own version of the script .  I added one field to say who to look for emails from, which is "WeMoExport" in the case of the WeMo emails.  I also added a field to look for emails no older than 2 days.  Amit's script processes data every 15 minutes.  I really think it should just be done once a day at 1am, but I left this alone. If you like the script, kick over a couple of bucks to Amit, I couldn't have done this without him.

NOTE: Google drive allows multiple files with the same name.  So each day a new file will be added with the same name as the old file.  This is typically not allowed in the MS Windows World, but it looks like Google cares more about some randomly generated file number than the file name.  Which will actually be helpful later.

Step 3: Update the data set daily with the new data

Data will exist in two locations.  The historical data will be maintained in a Google SpreadSheet and the latest data will be on your Google Drive.  I built a custom Google SpreadSheet has a custom script that loads the new data (based on a filename) and combines it with existing data.  This is necessary because the Belkin Conserve only has a "rolling" 30 day window of  data, so to see older data it is necessary to log it into the Google Spreadsheet before it disappears.

   The script separates the data into "Header", "Daily" and "Half Hourly" information on separate sheets within the spreadsheet, as shown in the next three images.

There is an "Info" sheet that you must update with the filename and directory of your data.

When you copy this sheet, delete all the data on the Daily and HalfHour sheets, but leave the header and the first row of data.  I'm not sure if the script will fail if there is no data in the sheet.  You can kill the one line of old data later after it has been automatically update.  If you want to, you can add data to this sheet manually, but note that it will automatically sort the data when the script runs.

There is a new menu added to the spreadsheet called "Update".  The first time it is run, click on "Step 1" and authorize access and then "Step 2" to launch the script for automatic update.  You can even click on "Update from CSV file, now" to load the latest data into your spreadsheet.  But this should happen automatically within the next 15 minutes even if you do not do anything.

  Step 4: Update the website graph

The data can be displayed on any website using Google Charts, which has nothing to do with Google Sheets, it is completely different.  I used my existing eBlogger account as the host for my charts.

Google Charts is a very powerful tool.  To get the chart to work with a specific data set, there are two steps.

Step 1: The Google Spreadsheet from step 3 above needs to be shared

Step 2: The file number needs to be updated inside of the HTML code.

The javascript code that makes the charts is contained within each of the following examples.

        1) WeMo TV    2) Livingroom Light   3) Wife's Computer   4) My Computer

They are all the same except for the "key" number discussed next.

Updating the File Number

Embedded inside the HTML JavaScript is the name of the file which looks something like this one.

'https://docs.google.com/spreadsheet/ccc?key=1xhRJ2xOhO0GP4f3bH70kkExXkhzKLo8hvoxjMG1UsN0&usp=drive_web&gid=0#');

Just do a search for "docs" and you will find it. The important part is the section after "key=" and before "&usp".  In this particular case it is.

1xhRJ2xOhO0GP4f3bH70kkExXkhzKLo8hvoxjMG1UsN0

This number key needs to be updated with your number key for your file.

If your copy of the Google Spreadsheet is opened (the WeMo sheet that is to say), the number appears in the title bar.

There are four locations in the JavaScript code to update, one for each chart.

Sharing the Google Spreadsheet

In order to have Google charts (the graph on the webpage) use data from the Google spreadsheet, first the spreadsheet must be made publicly viewable. 

Open the Google Spreadsheet and in the upper right of the corner is a "share" button. Click it.

In the next dialog box click the "Advanced" button.

Then click the "Change" button on the next dialogue box.

On the final dialog box, click the button that says "On - Public on the web" and make sure the lower selector is on "Can view" so that people cannot modify your spreadsheet. 

 Click "Save" and "done" until you are returned to the spreadsheet.  Now the upper right corner should show that the file is "Shared" by showing a globe.

The Results

There are a couple of great results from this process.

Firstly there is now a Google spreadsheet with all your energy usage data that extends beyond the 30 rolling window of the Belkin device.  This can be a great resource to understand how much power is being consumed over longer periods of time, however it is not all that visual. I have purposely kept that spreadsheet very bare. It has only the data recorded by Belkin and no graphs. It doesn't even have the reduced data shown in the charts which are executed using a "query" command, like SQL.

Secondly, there are four graphs showing your data for a day, week, month, and year.  Let me explain each of these.

The day chart shows the energy consumed in half hour increments yesterday.  Don't look for the current data here as the Belkin only sends out data one a day just after midnight.  So you are always looking at old data.

The week chart shows last week's energy usage from Sunday to Saturday.  Here you can see my TV watching increases on the weekend.  If you place your mouse cursor on any bar it will have a tooltip with more information.  The graph shows both the standby power consumption and the power consumed when your device (in this case my TV) is on.  This is really interesting to me because you can see the phantom loads that are wasting energy and not providing any service.

 

The month chart shows a rolling 30 day window of energy usage again with both the standby and running power consumption.

The year chart shows a year's worth of data, errr, well it will anyway once I have the device recording data for a year.  This is a rolling year ending with last month.

Overall, I loved doing this project because I learned a lot about Google scripts and Google charts.  These will be useful on other projects.  But now it is time for me to digest information about the power I consume and hopefully reduce it.