Hardware for real time monitoring of electricity meter

This is my hardware for real time monitoring of a electricity meter. A standard European electricity meter has a led that flashes 1000 or 10.000 times per kWh, my hardware has a photo diode that counts the pulses and transfers the results over an USB serial link to a computer.

The hardware and software are published under GNU General Public License v3. If you find any of this useful or write any code, please send an email to daniel at vindevag dot com, or post a comment.

Hardware

There are two versions of the hardware; one integrated in one piece if the computer is located near the electricity meter and a two piece version if the electricity meter is located far from the server. The two piece version is connected with a standard (straight) CAT5 network cable and can be far apart, I currently use it with a cable length of 30m. The maximum cable length for USB is 5m. The connector on the board is a mini-USB type B.

The hardware use a MCU board, MT-DB-U2, ATmega32U2 from Mattairtech. The board should be ordered with Atmel DFU (FLIP) bootloader.
The PCB's can be ordered from OSH Park in a set of three.

Budget

The total cost for the hardware is as follows:

One-piece Version

R1 1 KΩ R2 180 Ω R3 330 Ω, dependent on LED (optional). R4 10 KΩ for the LED transistor (optional). R5 10 KΩ pullup for the MCU HWB.   Schematics Order the PCB from OSH Park, $12.30 for three boards.

Two-piece Version

R1 330 Ω or 470 Ω, dependent on LED (optional). R2 1 KΩ R3 10 KΩ for the LED transistor (optional). 180 Ω R4 180 Ω 10 KΩ for the LED transistor (optional).   Schematics Order the PCB from OSH Park, $7.55 for three boards.

R1 10 KΩ pullup for the MCU HWB.   Correction:  Connect a 10 KΩ pullup between D1 and VCC on the header.   Schematics Order the PCB from OSH Park, $13.60 for three boards.

Common components

Running Examples

I run the two-piece version at my country house, the electricity meter is located outside on the facade and connected with a 30m network cable. The web scripts can be seen here http://power.nattsjo.se/nattsjo_power/
My brother has the one-piece version installed since his electricity meter is located inside his house, the hardware is connected to a RaspberryPi with a WiFi stick on top of the meter. For respecting his privacy, I will not link to his web scripts.

Hardware firmware

Flash the firmware for the first time

This description is for Linux, to flash the hardware under Windows, use the official Atmel FLIP software.

Install dfu-programmer:

sudo apt-get install dfu-programmer

Download the the firmware from here lcd_temp.hex. Short-circuit the two pins on the MCU board with a jumper and connect it to a computer. lsusb should show:

Bus xxx Device xxx: ID 03eb:2ff0 Atmel Corp. atmega32u2 DFU bootloader

Flash the firmware, sudo might not be necessary.

sudo dfu-programmer atmega32u2 erase
sudo dfu-programmer atmega32u2 flash lcd_temp.hex
sudo dfu-programmer atmega32u2 start

Remove the jumper and press the white reset button, the device should now show up as following with lsusb:

Bus xxx Device xxx: ID 0403:9136 Future Technology Devices International, Ltd Pulsecounter

The device should also appear as a virtual serial USB device (CDC), /dev/ttyACM[n]. If the udev-rule is installed it should also show up as /dev/pulsecounter:

[josefk@muaddib gcc]# ls -la /dev | grep ACM
lrwxrwxrwx  1 root root           7 apr 17 18:59 pulsecounter -> ttyACM0
crw-rw-rw-  1 root dialout 166,   2 apr 17 18:59 ttyACM0

Compiling the source code

The source code can be downloaded here: pulsecounter_fw.tar.gz. The code is very generic and contains code that is not used here, replace ./lcd_temp/gcc/makefile with this makefile.

To compile you need to install:

sudo apt-get install gcc-avr avr-libc binutils-avr dfu-programmer

Compile

cd ./lcd_temp/gcc/
make clean
make

Upload firmware, for installing for the first time, see Flash the firmware for the first time:

make flash

Server software and install

The device is a standard USB Communication Class Device (CDC), it requires no drivers under linux.
The device will work on Windows with this driver file: pulsecounter.inf, it will then turn up as a regular COM port. note that I have not written any programs for Windows. Feel free to write one and share it, see below under Write your own code

The ideal platform is a RaspberryPi with Raspbian.

Database

The program uses MySQL to store the database, the program can also report to a remote database using the web-scripts. Log in to MySQL as root

mysql -u root -p

Create the database, here called power:

CREATE DATABASE IF NOT EXISTS power DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Create a user, here called power_user with password power_pass and give full access to the database;

CREATE USER 'power_user'@'localhost' IDENTIFIED BY 'power_pass';
GRANT ALL PRIVILEGES ON power.* TO 'power_user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

By default, MySQL database server remote access is disabled for security reasons. You can edit /etc/mysql/my.cnf and open port 3306 in the firewall to allow remote access.
To grant access from a remote host or any host:

CREATE USER 'power_user'@'192.168.1.100' IDENTIFIED BY 'power_pass';
GRANT ALL PRIVILEGES ON power.* TO 'power_user'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'power_user'@'%' IDENTIFIED BY 'power_pass';
GRANT ALL PRIVILEGES ON power.* TO 'power_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your server located at 192.168.1.100:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.100 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

The actual database table could be created by the program, described below. To manually create the table power1, run the following:

CREATE TABLE IF NOT EXISTS power1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
time TIMESTAMP NULL, counter INTEGER UNSIGNED, power MEDIUMINT UNSIGNED, PRIMARY KEY(id));

The table uses TIMESTAMP to store time and date, in order to translate that to a human readable format, timezone data must be installed to MySQL. Run the following from the shell, not MySQL.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

If You wish to use spot prices from Nordpool, you need to create the following table as well:

CREATE TABLE IF NOT EXISTS nordpool_hour (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
time TIMESTAMP NULL, SE1 MEDIUMINT UNSIGNED, SE2 MEDIUMINT UNSIGNED, SE3 MEDIUMINT UNSIGNED, 
SE4 MEDIUMINT UNSIGNED, PRIMARY KEY(id));

Debian/Ubuntu install

Install the daemon:

sudo sh -c 'echo "deb http://www.rotary.nu/debian/ stable main # Pulsecounter" > /etc/apt/sources.list.d/pulsecounter.list'
sudo sh -c 'wget -O - http://www.rotary.nu/debian/daniel@vindevag.com.gpg.key |apt-key add -'
sudo apt-get update 
sudo apt-get install pulsecounter
sudo service pulsecounter stop

The .deb package could be downloaded from here: http://www.rotary.nu/debian/pool/stable/ and installed manually.
GPG Key: http://www.rotary.nu/debian/daniel@vindevag.com.gpg.key.

Edit /etc/pulsecounter.conf with database and other configuration, e.g. report interval and number of flashes per kWh. Create the database:

/usr/bin/pulsecounte.pl -createdb

Calibrate the hardware with the current reading on the electricity meter in Wh, i.e kWh * 1000. if your meter reads 50123.5 kWh:

/usr/bin/pulsecounter.pl -set 50123500

Start the daemon:

sudo service pulsecounter start

To check the status:

/usr/bin/pulsecounter.pl -status

Or; if you open port 26868 in your firewall, you can read the current status remotely:

nc localhost 26868

The daemon logs to syslog, run the following to list:

 sudo cat /var/log/syslog | grep pulsecounter | tail -n 20 

Calibrate

The hardware keeps track of the current value on the electricity meter, it is not dependent on the daemon to do this. It will save the current value to an EEPROM every 30 minutes, it also saves the current value when the daemon disconnects. If it looses power it has to be calibrated, otherwise it may loose data from the past 30 minutes.

Stop the daemon:

sudo service pulsecounter stop

Calibrate the hardware with the current reading on the electricity meter in Wh, i.e kWh * 1000. if your meter reads 50123.5 kWh:

/usr/bin/pulsecounter.pl -set 50123500

Start the daemon:

sudo service pulsecounter start

To check the status:

/usr/bin/pulsecounter.pl -status

The daemon will read the config file and reconnect to the hardware if it receives a SIGHUP, kill -1.

sudo service pulsecounter reload

Manual install for other platforms

The program is dependent on the following Debian packages.

perl
libdevice-serialport-perl 
libconfig-simple-perl 
libdbi-perl 
libdbd-mysql-perl 
libsys-syslog-perl 
libwww-perl
libio-socket-timeout-perl

The daemon could be downloaded from here, copy it to /usr/bin/pulsecounter.pl.
A color coded display of the program is here pulsecounter.pl.

This is the SysV Init script I used in Debian.

Copy this udev file to /etc/udev/rules.d and run

sudo udevadm control --reload-rules

Contents of /etc/udev/rules.d/95-pulsecounter.rules

Copy the configuration file pulsecounter.conf to /etc. Edit the database credentials.
The program will look for the config file in the following order:

1. pulsecounter.conf in the same directory as the program
2. ~/.pulsecounter
3. /etc/pulsecounter.conf

Contents of the default pulsecounter.conf file:

Web Scripts and graphs

The web scripts are written in PHP, you need a web server with PHP and MySQL database support i.e. an regular LAMP setup.

Web Scripts

The web scripts for the power consumption can be downloaded here power-scripts.tar.gz, unzip them somewhere in your web folder. Copy config.sample.php to config.php and edit the credentials for the database. Create a sub directory for the plots i.e. plots/

Example

The web scripts for the spot prices can be downloaded here spotprices-scripts.tar.gz, unzip them somewhere in your web folder. Copy config.sample.php to config.php and edit the credentials for the database. Create a sub directory for the plots i.e. plots/

Example

Graphs

The graphs are generated with Gnuplot using scripts.
If you added my debian repository, you can install them with:

sudo apt-get install powerplot-scripts

The installer will place the three scripts in /usr/bin, a sample config file in /etc/power_db.conf and a sample crontab file (all entries commented out) in /etc/cron.d/power

To manually install, the scripts have the following dependencies:

sudo apt-get install libconfig-simple-perl libdbi-perl libdbd-mysql-perl libwww-perl gnuplot

Date could be in the format YYYY-MM-DD or a negative number, 0 or "now()" for today (optional). Plotspotprices.pl defaults to tomorrow if no parameter is given
Timezone is in the format like "Europe/Berlin" or "America/Los_Angeles" (optional), the default is the server's timezone.

All three program use the same config file, the programs look for it in the following order:

1. Same directory as the program, ./power_db.conf
2. ~/.power_db
3. /etc/power_db.conf

A sample config file would look like this:

DBHOST  localhost
DBNAME  power
DBUSER  power_user
DBPASS  power_pass

The scripts should be run regularly through crontab, the power plotting script every hour and at one o'clock to get the last minutes for yesterday.
Nordpool publicizes next days prices at approximately 12:30 Central European Standard Time.
Here is an example of a crontab file:

*/60 * * * * www /usr/bin/powerplot.pl "power1" "/var/www/power/power1/plots" 

# Run yesterdays plot after midnight
10 1 * * *   www /usr/bin/powerplot.pl "power1" "/var/www/power/power1/plots" "-1"


# Nordpool Spot Prices, publicized every day at approximately 12:30 CEST
#
5 15 * * *   www /usr/bin/nordpool.pl

9 15 * * *   www /usr/bin/spotpricesplot.pl "nordpool_hour" "/var/www/power/spotprices/plots"

You need to create the following table in MySQL to store the spot prices:

CREATE TABLE IF NOT EXISTS nordpool_hour (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
time TIMESTAMP NULL, SE1 MEDIUMINT UNSIGNED, SE2 MEDIUMINT UNSIGNED, SE3 MEDIUMINT UNSIGNED, 
SE4 MEDIUMINT UNSIGNED, PRIMARY KEY(id));

Writing your own code

Web server

By accessing by accessing the report.php script you can get the last value in database as plain text

[Unix timestamp] [Hardware meter value in Wh] [Power in Watt] [Human readable date time]

1397886410 76548200 480 2014-04-19 07:46:50

You can add entries to the database through report.php using HTTP POST with the following parameters:

Database

mysql> DESCRIBE nattsjo_power1;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id      | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| time    | timestamp             | YES  |     | NULL    |                |
| counter | int(10) unsigned      | YES  |     | NULL    |                |
| power   | mediumint(8) unsigned | YES  |     | NULL    |                |
+---------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM nattsjo_power1 ORDER BY -ID LIMIT 5;
+-------+---------------------+----------+-------+
| id    | time                | counter  | power |
+-------+---------------------+----------+-------+
| 42327 | 2014-04-03 09:37:25 | 60425810 |   433 |
| 42326 | 2014-04-03 09:36:02 | 60425800 |   470 |
| 42325 | 2014-04-03 09:15:39 | 60425640 |   507 |
| 42324 | 2014-04-03 09:14:28 | 60425630 |   555 |
| 42323 | 2014-04-03 09:06:54 | 60425560 |   600 |
+-------+---------------------+----------+-------+
5 rows in set (0.04 sec)

mysql> DESCRIBE nordpool_hour;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| time  | timestamp             | YES  |     | NULL    |                |
| SE1   | mediumint(8) unsigned | YES  |     | NULL    |                |
| SE2   | mediumint(8) unsigned | YES  |     | NULL    |                |
| SE3   | mediumint(8) unsigned | YES  |     | NULL    |                |
| SE4   | mediumint(8) unsigned | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM nordpool_hour ORDER BY -id LIMIT 5;
+------+---------------------+-------+-------+-------+-------+
| id   | time                | SE1   | SE2   | SE3   | SE4   |
+------+---------------------+-------+-------+-------+-------+
| 2016 | 2014-04-20 23:00:00 | 19995 | 19995 | 19995 | 19995 |
| 2015 | 2014-04-20 22:00:00 | 21396 | 21396 | 21396 | 21396 |
| 2014 | 2014-04-20 21:00:00 | 22651 | 22651 | 22651 | 22651 |
| 2013 | 2014-04-20 20:00:00 | 22979 | 22979 | 22979 | 22979 |
| 2012 | 2014-04-20 19:00:00 | 20595 | 20595 | 20595 | 20595 |
+------+---------------------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

Daemon

By accessing the daemon on TCP port 26868, it will respond with the current hardware meter status and the last values put into the database

nc localhost 26868

The first two lines are:
Hardware [Unix timestamp] [Meter value in Wh] [Power in Watt]
Database [Unix timestamp] [Meter value in Wh] [Power in Watt]

1400136543 60837080 250 
1400135377 60837000 242 
HW Timestamp: 1400136543 (2014-05-15 08:49:03) 143 seconds ago
HW Meter: 60837.08 KWh
HW Power: 250 W
HW Overflow: 10000, Report: 100, (10 Wh)
DB Timestamp: 1400135377 (2014-05-15 08:29:37) 1309 seconds ago
DB Meter: 60837 KWh
DB Power: 242 W
DB Report: 500 Wh, 7%

Hardware

The hardware is a standard USB Comminication Class Device (CDC), under Linux it doesn't require any drivers. If you installed the UDEV rule, it can be accessed under /dev/pulscounter otherwise under /dev/ttyACM[n]

Under Windows you need this driver file pulsecounter.inf, it will then show up as a regular COM[n] port.

To connect to the hardware with a serial terminal, use 115200 bps, 8 bits, no parity, 1 stop bit; this is the most standard setting. All responses will be sent with CR LF ending, but both LF and CR LF line-endings are accepted as input.

Major counter corresponds to meter value in kWh and minor counter to the flashes, i.e 1/1000 or 1/10.000 of a kWh.
The values are stored as follows, for more info see the source code.

volatile uint32_t counter_major;
volatile uint16_t counter_minor;
uint16_t counter_minor_overflow;
uint16_t counter_minor_report;
volatile uint16_t counter_freq;

volatile int32_t seconds_count;
int8_t timezone;
uint16_t timer_corr;

The following commands can be used, all in capital letters.

ATCNTGET will respond with
[Major counter] [Minor Counter] [Frequency in Hz for the last second] [Report interval] [Minor overflow] [Current timestamp]

0 939 0 50 10000 1397792822

When the hardware sends status reports it uses the format
Counter [Minor Counter] [Major counter] [Frequency in Hz for the last second] [Current timestamp]

Counter 1300 0 5 1397793911
Counter 1350 0 5 1397793921
Counter 1400 0 5 1397793931

Timezone is only used for ATRTCNOW, timestamps are independent of timezones.
ATRTCNOW will respond with [Weekday 0-7] [Day of year] [Date] [Time]

5 108 14-04-18 06:14:42

The following Unix shell one-liner can be used to set the clock:

echo `date +"ATRTCSET %s"` > /dev/pulsecounter

The hardware lacks a proper real time clock so time keeping is implemented in software using interrupts, this means that the clock will go to fast. To fix this the hardware will subtract 1 second every x seconds, this is set with the ATRTCOSET command, typical values are between 5000 and 20.000.

My daemon sets the clock every time it connects to the hardware (a sudo service reload will force this) and checks the time every three hours, when the time differential is more than 5s (10s if the offset is 0) and less than 30s a new offset is calculated. The error after calibrating the time is less than 1s a week.
The offset value is unique to every piece since the crystal has a margin of error between five and ten percent.

(c) Daniel Vindevåg 2014-2016