We installed additional solar panels on our roof. After the installation, we’ve been following about the dynamics of the production and it is already clear, that during the day, we are selling excessive energy back to the network with a really bad deal. It would be much nicer (and economic) to use that energy ourselves.
So I started to look into ways to inform our apartment complex’s residents when we are producing electricity in excess. Enter a excessive solar power slack -bot
Pushing the logs from the Energy meter
With the solar panel installation, we did an overhaul in our electrical main board. One of the biggest changes were the measurement and meters. We installed a Carlo Gavazzi VMU-C-EM logger, that has a web interface. In addition, it can push a log file to a FTP-server every five minutes.
So I set up a SFTP server to an extra Mac mini I had lying around. Finally after configuring the VMU-C-EM, I was able to receive csv-files with no headers.
I started to investigate which column represents which value by finding a certain date and time, and cross referencing the data to the web servers data with headers. I was able to identify, that column 22 in the csv represents the active energy used in our whole apartment complex.
Learning Python
print("Hello World")
I had outlined my simple program as follows:
- Scan the FTP folder every 5 minutes and open the most recent file
- Read the Total power consumption column and store it as a variable
- If the variable is less than zero
- Send a message, ELSE
- Do nothing
So this was a great opportunity to learn python!
I’ve wanted to learn python for quite some while now, but without an actual need for the code, the barrier to start seemed way too big. So now with a clear goal in mind, I decided to take a look.
I have a background with Basic, Visual Basic, VBScript, asp and asp.net – languages that are so obsolete. Nevertheless, I wasn’t a total newbie to coding. I picked up PyCharm as my independent development environment and started writing code.
I felt that python was really easy to grasp. A quick youtube video was all it took me get on my way. I found out that simple coding is so much easier nowadays as time has passed, and sites as stack overflow, github, etc. Sites that have people much smarter than me who have already solved way more complex issues that I have.
Reading a folder proved easy. Reading the csv, quite easy. Sending the messages was a bit more challenging – but still easy.
As python’s number sequences start from zero, the column number in python is 21, instead of 22. This took me some time to realize being new to the language. But here is the code for the file searches and variable storage:
# Find the latest file in the ftp folder
list_of_files = glob.glob('solar/*.csv')
latest_file = max(list_of_files, key=os.path.getctime)
#print(latest_file)
# find the latest momentary power consumption
with open(latest_file) as mycsv:
csv_reader = csv.reader(mycsv, delimiter=';')
data = [row for row in csv_reader]
Active = data[1][21]
Active = float(Active)
#remove the second latest file
secondlatest = sorted(glob.iglob('solar/*.csv'), key=os.path.getctime)[-2]
os.remove(secondlatest)
Initial WhatsApp trial
We have a whatsapp group in our apartment complex and I thought that would be the most obvious channel to start informing our residents about excessive solar energy production.
I googled around, and eventually bumped into Twilio, an API initially for automated SMS sending. They have a beta WhatsApp webhook sandbox that I started fooling arounf to see if I could work with it.
I initially got it to send WhatsApp messages using a great blog post, but it was quite clear that I can’t send messages to existing WhatsApp groups using Twilio. I found a workaround, but I didn’t want to set a browser based setup. So I decided to move to Slack – the messaging platform of coders!
Nevertheless, my code for the WhatsApp webhook is attached:
#WHATSAPP
TWILIO_SID = "YOUR SID HERE"
TWILIO_AUTHTOKEN = "YOUR TOKEN HERE"
TWILIO_MESSAGE_ENPOINT = "https://api.twilio.com/2010-04-01/Accounts/YOUR-ACCOUNT-ID-HERE/Messages.json".format(TWILIO_SID=TWILIO_SID)
TWILIO_NUMBER = "whatsapp:+14155238886"
def send_whatsapp_message(to, message):
message_data = {
"To": to,
"From": TWILIO_NUMBER,
"Body": message,
}
response = requests.post(TWILIO_MESSAGE_ENPOINT, data=message_data, auth=(TWILIO_SID, TWILIO_AUTHTOKEN))
response_json = response_json()
return response_json
print(line)
to_number = "whatsapp:++358------- YOUR NUMBER ----"
initial_msg = """we are now selling electricity to the network """
appointment_msg = initial_msg + str(Active) + """ kW"""
msg = send_whatsapp_message(to_number, appointment_msg)
Excessive solar power Slack-bot
However, the WhatsApp API through Twilio seemed a bit difficult, as I had to message all users separately and this would increase the amount of messages I need to send out pretty rapidly. And Twilio starts charging money once you reach a certain amount of messages.
So enter Slack – the coders go to messaging app. The instructions from various posts were quite straight forward. So I registered to slack, registered an app to enable interactions/bot posts in my channel and presto! I Had a working method of sending push notifications!
My code for the slack messaging is:
#SLACK
webhook_url = "https://hooks.slack.com/services/YOUR-SLACK-TOKEN"
slack_data = {'text': "Please go ahead and use electricity, we're now selling it to network for a total of " + str(abs(Active)) + " kW"}
response = requests.post(
webhook_url, data=json.dumps(slack_data),
headers={'Content-Type': 'application/json'}
)
if response.status_code != 200:
raise ValueError(
'Request to slack returned an error %s, the response is :\n%s'
% (response.status_code, response.text)
)
Results so far
The results have been great! My first fix was that since I’m actually listening to the total consumption of our apartment complex, I was looking for a negative consumption. For the bot’s message to make sense, I had to take the absolute value of the consumption to turn it positive.
The second learning was the python way of starting all counting from 0, thus changing the column in the csv file from 22 to 21. But all in all, the bot works now. The next changes will be somehow to reduce the message interval from 5 minutes to probably just informing when we are selling and when we are no longer selling electricity to the network.
The final code of the bot looks as follows:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import glob
import os
import csv
import requests
import json
# Find the latest file
list_of_files = glob.glob('solar/*.csv')
latest_file = max(list_of_files, key=os.path.getctime)
#print(latest_file)
# Find the latest consumption
with open(latest_file) as mycsv:
csv_reader = csv.reader(mycsv, delimiter=';')
data = [row for row in csv_reader]
Active = data[1][21]
Active = float(Active)
#Remove the second latest file
secondlatest = sorted(glob.iglob('solar/*.csv'), key=os.path.getctime)[-2]
os.remove(secondlatest)
# Decide to send a message or not to
if Active < -0.05:
print("Selling")
#SLACK
webhook_url = "https://hooks.slack.com/services/YOUR-SLACK-TOKEN-HERE"
slack_data = {'text': "We are now selling an excess of " + str(abs(Active)) + " kW"}
response = requests.post(
webhook_url, data=json.dumps(slack_data),
headers={'Content-Type': 'application/json'}
)
if response.status_code != 200:
raise ValueError(
'Request to slack returned an error %s, the response is :\n%s'
% (response.status_code, response.text)
)
else:
print("Buying")