昨天，我写了一篇关于使用Alexa 控制WeMo 开关的文章(WeMo switch)，今天(Alexa and today)我想写一篇关于如何将WeMo Insight 开关中的(WeMo Insight switch)能源使用(energy usage)数据导出到Excel的文章。WeMo Insight 开关会(WeMo Insight switch)生成大量详细信息，如果您想了解某些设备或电器在您的家庭或办公室(home or office)中使用了多少能源，这些信息非常有用。
即使应用程序数据很棒，您也可以将 45 天的(’ worth)更详细的数据导出到Excel，然后用它做任何您喜欢的事情：创建图表和图形，显示哪些天使用的能源最多，设备使用了多少小时处于待机模式与(mode vs)开启等。
在这篇文章中，我将讨论导出数据的过程，然后介绍如何在Excel 电子表格(Excel spreadsheet)中组织数据。我还将向您展示如何制作一个快速图表来绘制能源使用情况(energy usage)。
从 WeMo 导出能源数据
默认情况下，WeMo 应用程序(WeMo app)将为您提供高级能源使用(energy usage)数据，例如设备今天和平均一天的使用时间、估计的每月成本以及平均和当前的瓦数使用情况(wattage usage)。
这对大多数人来说已经足够了，但是如果您是数据极客(data geek)并且喜欢使用Excel，您会喜欢这样一个事实，即您可以导出如此多的额外数据以自行分析。为此，我们需要点击WeMo 应用(WeMo app)右上角的编辑(Edit)按钮。这些开关将改变它们的显示方式，您应该点击WeMo Insight 开关(WeMo Insight switch)。
在下一个屏幕上，您会在底部看到几个选项，但我们感兴趣的是Data Export。稍后我还将解释其他一些选项，因为它们会影响导出的数据。
最后，在最后一个屏幕上，输入您的电子邮件地址(email address)，然后点击立即导出(Export Now)。这将立即向您发送一封带有CSV 附件(CSV attachment)的电子邮件，其中包含该设备在过去 45 天内的所有数据。
如果您愿意，您还可以设置每天、每周或每月的计划导出。由于数据仅保留 45 天，因此最好每月导出一次数据，以免忘记手动导出时丢失任何数据。
我还建议使用每月频率，因为它现在的工作方式是每次执行导出时都会发送过去 45 天的所有数据。这不像是数据被重置或类似的东西。最后，如果您想在单个电子表格中包含超过 45 天的数据，您还必须创建一个主工作表并将新电子表格中的数据复制并粘贴到旧电子(’ worth)表格中。(master worksheet and copy)
在电子邮件中，文件的名称将是Export for devicename。由于它是CSV 文件(CSV file)，因此您可以根据需要在Google 表格(Google Sheets)、Excel或许多其他程序中打开它。为简单起见，我将使用Excel。现在让我们看一下电子表格。
在顶部，您将获得一些基本信息，例如此能源报告的上次更新(energy report)日期和时间(date and time)、设备名称(device name)、一些MAC ID、WeMo 设备(WeMo device)的信号强度，然后是每千瓦时的能源成本(Energy Cost per kWh)和阈值（瓦特） )(Threshold (Watts)) . 这最后两个值可以在您点击Data Export的屏幕上的应用程序中更改。因此，WeMo为您提供的每月预估成本基于每千瓦时 0.111 的值(kWh value)，但您的能源成本可能会有所不同。
解决这个问题的最佳方法是查看您的能源账单(energy bill)，然后将总电量( Electric Amount)除以使用的总千瓦时(kWh Used)。您应该只查看账单中的Electric Details部分，而不是 Gas 详细信息。这是我的电费单的一个例子，只是为了清楚起见：
所以在这里，我会拿 267.46 美元除以 1830 kWh Used，得到大约0.146(.146)，即每千瓦时 14.6 美分(14.6 cents per kWh)的成本。所以现在我进入WeMo(.111)应用程序并将(WeMo app and change)其从0.111 更改为 0.146，这将给我一个非常准确的估计我这个设备的每月成本。
此外，您可以调整阈(Threshold)值值，该值基本上决定了设备是开启(On)还是处于待机(Standby)模式。默认情况下，此值设置为 8瓦(Watts)。如果设备使用少于该值，则将其视为处于待机模式。我不是电气工程师，我不太确定这样做的最佳价值是多少，所以我把它留在了 8w。
下面是每日使用摘要部分，按天细分关键统计数据。您可以查看开机时间、待机时间、一天的平均功耗(power consumption)以及一天的成本。如果您想查看当天的总功耗，只需创建一个公式并为每一行添加功耗（开启）(Power Consumption (On))和功耗（待机）(Power Consumption (Standby))。最后，在底部，您可以按半小时获得使用情况细分。
根据您拥有多少天的数据，这可能会增加相当多的数据行。这也将允许您创建漂亮的图表来查看一段时间内的能源使用情况(energy usage)。在我看来，查看此类数据的最佳图表是 X Y 散点图(Y Scatter plot)。如果你选择了两列数据，包括标题，点击Insert – Chart，然后选择XY Scatter，你会得到这样的结果：
如您所见，我的冰箱在不运行时使用大约0.006 千瓦时(.006 kWh)，而在冷却时则跃升至大约0.012 千瓦时。(.012 kWh)它还在这些状态之间昼夜(day and night)交替。其他设备的使用可能会生成不同的图表。
请注意，如果您决定将 WeMo 移动到另一个位置以跟踪有关不同设备的数据，则最好重置WeMo 。如果不这样做，您将从两个不同的设备获取数据，这将使数据变得无用(data useless)，除非您记得移动开关的确切日期。
总体而言，如果您是数据迷，那么(data junkie)WeMo Insight 交换机(WeMo Insight switch)是物有所值的。在我看来，你真的只需要在房子周围使用其中一个来跟踪不同的设备。一旦您为一台设备获得了几周的(’ worth)数据，它并不会真正改变太多，因此您可以重置数据并开始跟踪另一台设备。如果您有任何问题，请随时发表评论。享受！
How to Export WeMo Energy Usage Data to Excel
Yesterdaу, I wrote about controlling a WeMo switch using Alexa and today I wanted to write about how you can export the energy usage data from a WeMo Insight switch to Excel. The WeMo Insight switch generates a lot of detailed information that is really useful if you’re trying to figure out how much energy certain devices or appliances are using in your home or office.
Even though the app data is great, you can export 45 days’ worth of far more detailed data to Excel and then do whatever you like with it: create charts and graphs showing which days the most energy is being used, how many hours the device is in standby mode vs on, etc.
In this post, I’ll talk about the process for exporting the data and then go over how the data is organized in the Excel spreadsheet. I’ll also show you how to make a quick chart to graph out the energy usage.
Export Energy Data from WeMo
By default, the WeMo app will give you high-level energy usage data like how long the device was on today and on an average day, the estimated monthly cost and the average and current wattage usage.
This is good enough for most people, but if you are a data geek and you love using Excel, you’ll love the fact that you can export so much extra data out to analyze on your own. To do this, we need to tap on the Edit button at the top right of the WeMo app. The switches will change how they are displayed and you should tap on the WeMo Insight switch.
On the next screen, you’ll see a couple of options at the bottom, but the one we are interested in is Data Export. Some of the other options I’ll also explain later on as they can affect what data is exported.
Finally, on the last screen, type in your email address and then tap on Export Now. This will immediately send you an email with a CSV attachment containing all data for the device over the last 45 days.
If you like, you can also setup scheduled exports on a daily, weekly or monthly basis. Since data is only kept for 45 days, it’s a good idea to export the data once a month, so that you don’t lose any data if you forget to export manually.
I also suggest using the monthly frequency because the way it works right now, ALL the data for the last 45 days is sent every time you perform an export. It’s not like the data is reset or anything like that. Eventually, you’ll also have to create a master worksheet and copy and paste the data from the newer spreadsheets into the older ones if you want more than 45 days’ worth of data in a single spreadsheet.
In the email, the name of the file will be Export for devicename. Since it’s a CSV file, you can open it in Google Sheets, Excel or a host of other programs if you like. For simplicity, I will be using Excel. Now let’s take a look at the spreadsheet.
At the top, you’ll get some basic information like the last updated date and time for this energy report, the device name, some MAC ID, the signal strength of the WeMo device and then the Energy Cost per kWh and the Threshold (Watts). These last two values can be changed in the app on the screen where you tapped on Data Export. So the estimated monthly cost that WeMo gives you is based on this .111 per kWh value, but your energy costs may be different.
The best way to figure this out is to look at your energy bill and divide the total Electric Amount by the total kWh Used. You should only be looking at the Electric Details part of your bill, not the gas details. Here’s an example of my electric bill just to make it clear:
So here, I would take $267.46 and divide it by 1830 kWh Used and that gives me around .146, which is 14.6 cents per kWh as the cost. So now I go into the WeMo app and change it from .111 to .146 and that will give me a very accurate estimate of my monthly cost for this device.
In addition, you can adjust the Threshold value, which basically dictates whether the device is On or in Standby mode. By default, this value is set to 8 Watts. If the device uses less than that, it is considered in standby mode. I’m no electrical engineer and I’m not really sure what the best value for that is, so I just left it at 8w.
Below that is the daily usage summary section, which breaks down key stats by day. You can see time on, time in standby, average power consumption over the day, and the day’s cost. If you want to see total power consumed for the day, just create a formula and add Power Consumption (On) and Power Consumption (Standby) for each row. Finally, at the bottom, you get a breakdown of usage by half-hour.
Depending on how many days’ worth of data you have, this can add up to quite a lot of rows of data. This will also allow you to create nice graphs to see energy usage over time. In my opinion, the best chart for viewing this type of data is an X Y Scatter plot. If you select the two columns of data, including the headings, click on Insert – Chart, and choose X Y Scatter, you’ll get something like this:
As you can see, my fridge uses around .006 kWh when not running and jumps to about .012 kWh when cooling. It also alternates between these states all day and night. Usage by other devices will probably generate different looking charts.
Note that it’s a good idea to reset the WeMo if you decide to move it to another location to track data about a different device. If you don’t, you’ll get data from two different devices, which will make the data useless unless you remember the exact day you moved the switch.
Overall, the WeMo Insight switch is worth the cost if you are a data junkie. In my opinion, you really only need one of these around the house to track different devices. Once you have a couple of weeks’ worth of data for one device, it’s not really going to change all that much, so you can then reset the data and start tracking another device. If you have any questions, feel free to comment. Enjoy!
