READ THIS FIRST:
This is a post from August 2012 (ages ago).
Things have changed since then.
I am no longer using this Raspberry Pi 1 for this job, changed from SMA-Bluetooth to SBFSpot on a Rpi Zero. This info is provided “as is”. Please don’t mail me with questions like “I’m trying to get this to work on a converter Y but I get error X”
I will not be able to help you.
Introduction
It took me quite some time to get it to work, so I thought it would be useful to document the setup in case I would ever have to redo it. And of course, maybe it can help others that want to setup the same configuration. Note that I have very little Linux knowledge, I’m just using what others have created, so there are going to be plenty of questions for other configurations that I do not know the answer to.
I have written the post in English because I figured it would be more accessible that way, but keep in mind that the setup focuses on my specific situation. If you can read Dutch, you can also read more about the setup here.
Also, I am assuming you are a Windows user when describing the setup.
What are we going to do?
Last March a total of 8 solar panels got installed on the roof of my house as part of a group-buy project called “Zon Zoekt Dak” (Sun in Search of a Roof). Great, but how was I going to be able to tell how much electricity was being produced? Sure, I could go up to the attic every day and look on the inverter, but that was not what I wanted. I wanted to be able to see a nice graph of the production and history on my iPad of PC.
There are a number of online sites that offer that functionality, one of them is PVOutput.org, but how would I be able to get the data from my inverter to the website?
First “problem” was that the inverter that was chosen by Zon Zoekt Dak, the Sunny Boy 1600TL, is one without build in Bluetooth . So, to get data out, you need to buy and install (have installed) the Bluetooth Piggy Back. Here in the Netherlands the Piggy back costs about €115,-
Without that you won’t be able to read the data using the Raspberry Pi. You may have an inverter that does come with build in Bluetooth, then you are in luck and don’t have to install extra hardware.
The Raspberry Pi
The Raspberry Pi is a tiny computerboard that you can buy and have shipped to you from the UK. You can buy the Raspberry Pi through Premier Farnell/Element 14 and RS Components. You won’t get it shipped overnight though, currently it takes about 15 weeks (!) due to the high demand for this tiny computer. You will probably want a case, you need a power supply (micro-USB), and a SD-card to store the OS on. All in all, it costed me €84,02 including the Tesco ‘Technika’ Nano Bluetooth Adaptor (I happened to be at Tesco’s this summer). If you live in the Netherlands, you won’t be able to order that Bluetooth Adapter, see this list for other verified working adapters.
The Raspberry Pi is small, energy efficient, and after the initial setup it does not need a keyboard, mouse or monitor.
The initial Raspberry Pi setup
You might have already setup your Raspberry Pi, but in case you haven’t, here are the steps to do so (adapted from elinux.org):
- Download the image from a mirror or torrent. The remainder of this assumes you are using the Raspbian “wheezy” download 2012-07-15-wheezy-raspbian.zip
http://www.raspberrypi.org/downloads - Extract the image file 2012-07-15-wheezy-raspbian.img from the downloaded .zip file.
- Insert the SD card into your SD card reader and check what drive letter it was assigned. You can easily see the drive letter (for example G:) by looking in the left column of Windows Explorer. If the card is not new, you should format it; otherwise Win32DiskImager may hang.
- Download the Win32DiskImager utility from: https://launchpad.net/win32-image-writer
The download links are on the right hand side of the page, you want the binary zip. - Extract the executable from the zip file and run the Win32DiskImager utility. You may need to run the utility as Administrator (right click on it and choose Run as administrator).
- Select the 2012-07-15-wheezy-raspbian.img image file you extracted earlier
- Select the drive letter of the SD card in the device box. Be careful to select the correct drive; if you get the wrong one you can destroy your computer’s hard disk!
- Click Write and wait for the write to complete.
- Exit the imager and eject the SD card.
- Insert the card in the Raspberry Pi.
- Connect the Raspberry Pi to a screen using HDMI, connect a USB-keyboard (don’t need a mouse) and connect a power source.
The Raspberry Pi will boot now.
The Raspberry Pi will display a setup tool. You can make all the changes first and then boot up the Raspberry Pi
Select “Expand_rootfs” so that the complete SD-card will be used.
Change the time zone (in my case to Amsterdam)
Select SSH > enable SSH server
Select Boot behaviour > NO to boot straight to desktop
Select Finish
Select Reboot
Resizing is going to take some time, so have some tea.
As soon as you see the Raspberry Pi logon screen, you can disconnect the keyboard and monitor.
Connect a network cable to connect the Raspberry Pi to you network.
You can also plugin the Bluetooth adapter.
SSH connection
To connect to the Raspberry Pi we will be using SSH. We will use two different methods:
We will use Putty to get a command prompt and WinSCP to upload en download files to the Raspberry Pi.
Download Putty from this page:
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
Download the portable version of WinSCP (no need to instal) from this page:
http://portableapps.com/apps/internet/winscp_portable/
Connect to the Raspberry Pi
If you only have one Raspberry Pi connected to the network, you can simply use the machine name to connect to the Raspberry Pi. If you have multiple devices, disconnect the others for now, so you can connect to the one that you are setting ups.
Start Putty and connect to the IP-address for your Raspberry Pi
Username: pi
Password: raspberry
Change the password for Pi to something more secure using:
> Passwd
Update apt-get:
> sudo apt-get update
Install the Bluetooth driver
The bluetooth driver is not installed by default, so we need to do that:
> sudo apt-get install bluetooth
(go drink some more tea, this is going to take some time)
Check for Bluetooth connectivity with the bluetooth piggyback in the SMA converter:
> hcitool scan
You should see something like this:
Make a note of the Bluetooth address of the piggyback.
Install SMA-Bluetooth
Now it is time to install the prerequisites of SMA-bluetooth, the source and to compile the application.
Install the prerequisites:
> sudo apt-get install build-essential mysql-server libbluetooth-dev libmysqlclient-dev curl libcurl3-dev mercurial
During the install you will be asked to set a password for the MySQL server. Make sure you store it somewhere!
Now let’s get the source code. In Putty (at the commandprompt) type:
> mkdir bin
> cd bin
> hg clone https://sma-bluetooth.googlecode.com/hg/ sma-bluetooth
> cd sma-bluetooth
Now compile:
> make
Create smatool.conf based on the supplied template:
> cp smatool.conf.new smatool.conf
Now you can edit the file using nano or vi, but I prefer Notepad++, so that is why I connect to the Raspberry Pi using WinSCP using the pi-account and then edit the file from there.
So connect using WinSCP first using the ip-address of the Raspberry Pi, username: Pi; password: raspberry
Open the smatool.conf file.
Enter your inverter code (for Zon Zoekt Dak customers that is: 1600TL)
Enter the Bluetooth address that you got from hcitool scan
Enter as password for the inverter: 0000
Lookup your latitude and longitude on this map: http://www.gorissen.info/Pierre/maps/ and enter them.
Adding this makes sure that the Raspberry Pi only tries to connect to the inverter when it is actually operational, meaning when the sun is up.
For MySQL enter:
MySqlHost localhost
MySqlDatabase smatool
MySqlUser root
MySqlPwd [the password you used during setup earlier]
Save the file.
If you are using a Sunny Boy 1600TL you also need to edit invcode.in and add this to the top of the file:
Inverter 1600TL
Code1 0x36
Code2 0xE1
Code3 0x38
Code4 0x3B
InvCode 0x63
Save the file.
Install the MySQL database by running:
> ./smatool --INSTALL
Because it is not advisable to have your MySQL root password in the smatool.conf file, we will create a new MySQL user and assign that user rights to the database:
> mysql -h localhost -u root –p
(enter your root password when requested)
Then enter:
Mysql> GRANT select,update,delete,insert ON smatool.* TO ‘smatool'@'localhost’ IDENTIFIED BY ‘mysecret’;
(replace mysecret with your own password)
Press CTRL+D to exit
Update smatool.conf with the new username and password.
Now run smatool for the first time:
./smatool
For some people it now just works, but in my case, nothing was stored in the database. It turns out, I needed to make a couple of changes (hacks) to smatool.c
Fixing things
If you just want to download a copy of my smatool.c that is also possible.
# DateError – fixes in smatool.c
I understand there are a lot of problems concerning Date Errors. I don’t understand the nature of them completely, but I understand that by default, when a Data Error occurs, nothing gets stored in the database. Even though in my case, often completely valid data is restored.
So that is why I removed the check.
On lines 2078 and 2079 from smatool.c, in the “case 18: // $ARCHIVEDATA1” section I commented out the “error=1;” and “break;” statements
if( idate != prev_idate+300 ) {
printf( "Date Error! prev=%d current=%d\n", (int)prev_idate, (int)idate );
// error=1;
// break;
}
That didn’t completely solve the problem, because now also some bad data was stored in the database from time to time. I solved that by removing that data every time when the application was run again.
In auto_set_dates() I added this:
int auto_set_dates( ConfType * conf, int * daterange, int mysql, char * datefrom, char * dateto )
/* If there are no dates set - get last updated date and go from there to NOW */
{
MYSQL_ROW row;
char SQLQUERY[200];
time_t curtime;
int day,month,year,hour,minute,second;
struct tm *loctime;
if( mysql == 1 )
{
OpenMySqlDatabase( conf->MySqlHost, conf->MySqlUser, conf->MySqlPwd, conf->MySqlDatabase);
// Gor - Remove illegal values first!
sprintf(SQLQUERY,"DELETE FROM DayData WHERE CurrentPower < 0 or ETotalToday = 9999999.999" );
if (debug == 1) printf("%s\n",SQLQUERY);
DoQuery(SQLQUERY);
//Get last updated value
sprintf(SQLQUERY,"SELECT DATE_FORMAT( DateTime, \"%%Y-%%m-%%d %%H:%%i:%%S\" ) FROM DayData ORDER BY DateTime DESC LIMIT 1" );
if (debug == 1) printf("%s\n",SQLQUERY);
DoQuery(SQLQUERY);
While we are editing smatool.c we can also make the final edit. The Sunny Boy 1600TL invertor starts logging data at different times each morning. The first value for each day is not 00:00 (midnight), but could be any time between 6AM-7AM. The current code relies on 00:00 being the first entry for a day.
We need to replace this code:
sprintf(SQLQUERY,"SELECT DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d\'), DATE_FORMAT(dd1.DateTime,\'%%H:%%i\'), ROUND((dd1.ETotalToday-dd2.EtotalToday)*1000), dd1.CurrentPower, dd1.DateTime FROM DayData as dd1 join DayData as dd2 on dd2.DateTime=DATE_FORMAT(dd1.DateTime,\'%%Y-%%m-%%d 00:00:00\') WHERE dd1.DateTime>=Date_Sub(CURDATE(),INTERVAL 29 DAY) and dd1.PVOutput IS NULL and dd1.CurrentPower>0 ORDER BY dd1.DateTime ASC" );
with:
sprintf(SQLQUERY,"SELECT DATE_FORMAT(t1.DateTime,\'%%Y%%m%%d\'), DATE_FORMAT(t1.DateTime,\'%%H:%%i\'), ROUND((t1.ETotalToday - d1.MinVal)*1000), t1.CurrentPower, t1.DateTime FROM DayData t1 INNER JOIN (SELECT DATE(DateTime) ts_date, MIN(ETotalToday) AS MinVal FROM DayData GROUP BY ts_date) d1 ON DATE(t1.DateTime) = d1.ts_date WHERE t1.DateTime>=Date_Sub(CURDATE(),INTERVAL 10 DAY) and t1.PVOutput IS NULL and t1.CurrentPower>0 ORDER BY t1.DateTime ASC" );
It should be in the file in 3 different locations.
After this is done, save smatool.c
Now compile again:
> make
Run ./smatool again:
> ./smatool
Now, there might be some weird dates that get inserted into the database, but don’t worry, they will be removed again during the next run and PVOutput simple will ignore them.
# Fixes in the database
Depending on how long the Bluetooth piggyback was already installed before you are doing this setup, you need some extra changes to the database.
Because PVOutput does not accept data older than 30 days, and if you have data older than that, you won’t be able to upload the new data without these changes.
Connect to MySQL and select the smatool database:
> mysql -h localhost -u root –p
(enter the password)
mysql> use smatool;
Check to see if there are dates that are older than 30 days:
mysql> select * from DayData where DATEDIFF(curdate(), DateTime) > 30;
If you get an empty set, you can close MySQL, otherwise you have to update the “NULL” value for PVOutput so that smatool no longer tries to upload those data to PVOutput.
mysql> update DayData set PVOutput = “2012-08-19 00:00:00” where DATEDIFF(curdate(), DateTime) > 30;
Another problem is that sometimes there are rows with a higher value for CurrentPower than is possible for your inverter / panels. For example, I have 8 panels with a panel max power of 240Watts. PVOutput then calculates the System Size as 1920Watts and won’t accept any values higher than that. Worse: if smatool tries to batch upload data and one of the records has a value higher than 1920, it will simply not add any of the records in the batch.
To see if the problem exists in your database use:
mysql> select * from DayData where PVOutput is NULL and CurrentPower > 1920;
You should of course replace 1920 with the System Size of your system.
If that is the case, it is best to simply remove those records:
mysql> delete from DayData where PVOutput is NULL and CurrentPower > 1920;
Now exit MySQL.
PVOutput setup
Goto the PVOutput website, create an account and add your device.
Edit smatool.conf and make sure that PVOutputURL is set to: http://pvoutput.org/service/r2/addstatus.jsp
Enter the API-key for your account into PVOutputKey and the system ID in PVOutput.
Save smatool.conf and rerun ./smatool
Note: it may be that you have to rerun the tool a number of times before the first data is uploaded. As mentioned before, the inverter sometimes returns bogus data and PVOutput won’t accept that. This is not a problem since we will be setting the cron job so that this is done every 5 minute.
Also, the first time it could take quite some time to upload all the data to PVOutput, depending on the amount of records that needs to be uploaded.
If it still doesn’t work
Try running smatool with the debug option while outputting to a logfile:
> ./smatool –d > log.txt
You can view the file using nano, or using WinSCP. At the end of the log, you will find one or more lines like this:
url = http://pvoutput.org/service/r2/addbatchstatus.jsp?data=20120812,06:25,0,334620;20120812,07:05,1,12;20120812,07:15,2,12;20120812,07:20,6,48;20120812,07:25,10,48;20120812,07:30,15,60;20120812,07:35,21,72;20120812,07:40,28,84;20120812,07:45,39,132;20120812,07:50,55,192;20120812,07:55,73,216;20120812,08:00,95,264;20120812,08:05,144,300;20120812,08:10,157,156;20120812,08:15,188,372;20120812,08:20,222,408;20120812,08:25,260,456;20120812,08:30,300,480;20120812,08:35,345,540;20120812,08:40,390,540;20120812,08:45,441,612;20120812,08:50,494,636;20120812,08:55,550,672;20120812,09:00,610,720;20120812,09:05,673,756;20120812,09:10,738,780;20120812,09:15,806,816;20120812,09:20,877,852;20120812,09:25,949,864;20120812,09:30,1025,912&key=4e7xxxxxxxxx&id=7799
result = 22
The result code is rather meaningless, but if you select the line and past it in the addressbar of your browser, you get a more detailed response. For example, this error occurs when you failed to remove the high power values, you would get this error:
Bad request 400: Power value [334620] too high for system size [1920]
Cronjob
All that remains now is to setup a cronjob so that the smatool is run every 5 minutes.
Create a file name smatool_run with this contents:
#!/bin/bash
cd ~/bin/sma-bluetooth
./smatool
Upload the file to the sma-bluetooth folder and set the execution rights.
Test the script first by running from the command prompt:
> ~/bin/sma-bluetooth/smatool_run
Check that it runs without an error.
Then from the command prompt type:
> crontab –e
Add this line:
*/5 * * * * ~/bin/sma-bluetooth/smatool_run >/dev/null 2>&1
Press CTRL+X
Choose Y (yes)
Press enter
You should see the message “crontab: installing new crontab”
Keep an eye on PVOutput during the day. You should see the values being updated.
All done. 🙂
A next step would be to make the Raspberry Pi send you an e-mail with an image of the daily production every evening when the inverter shuts down. See these instructions for that. The instructions also explain how to automatically get PVOutput to retrieve and store the current temperature alongside the inverter readings.