Tesla Powerwall – Early Differences In Grid Consumption

It’s been just over 5 months since we had our Tesla Powerwall installed and I thought I’d give an early update on the difference it’s made to our grid consumption so far. It is still early days and I will provide another update once I have a few more readings – probably late next year.

Our electricity billing periods are:

  • Jan 16th
  • April 16th
  • July 16th
  • October 16th

Our Tesla Powerwall was installed on June 1st 2018, which means the July bill should have showed some difference in kWh grid consumption (especially given the lovely summer we had this year!) even though it was a partial month of having the Powerwall, whereas the October bill was a complete month of having the Powerwall in operation.

I think the graph says it all! Even on the July bill, we saved 266* kWh or a 37.5% drop and on the October bill we saved a massive 560* kWh or 70.3% drop!

I just wish we’d had the battery installed 6 weeks earlier to benefit even more from the fantastic summer weather we had this year! 🙂

* based on the average of units consumed 2013 to 2017

For those interested in the figures:

July October
2013 663 814
2014 694 778
2015 783 847
2016 724 792
2017 681 754
2018 443 237

Ubiquiti mFI mPower Project – Part 1

This year I upgraded the home monitoring suite in my house from a Wattson to a Splunk dashboard running on a tablet in the kitchen. The primary reason for doing this was that after getting the Tesla Powerwall, Wattson no longer was able to correctly state how much power was being imported/exported from the home until the battery was completely charged. The downside of getting rid of Wattson was that we relied on the Optiplugs to run the immersion and a radiator when there was excess power being sent to the grid. So I needed another solution to running the immersion on the rare days when the Powerwall will be completely charged and we’re exporting to the grid.

After a fair amount of time searching for a wireless plug with an API, I found the following plug https://wifiplug.co.uk/ that ticked all the boxes, but being the little bit paranoid I am, I didn’t really want a plug that could theorietically be controlled by anyone from the internet. Also, if that company went bust, how would I control the plug without their API gateway?

I then spent another handful of hours searching and came across the following blog https://blog.linitx.com/ubiquiti-mfi-mpower/ which happened to use a wifi plug that doesn’t rely on an internet connection and is from the same company that I use for my WiFi access points! The only problem is that unlike the wifiplug, this one doesn’t have an official API and there isn’t a UK plug version… But what the heck, at least it’d be an interesting project!

So I bought the European single socket version from wifi-stock.co.uk/. The next day I got a call from wifi-stock to check “I hand’t made a mistake and that I did realise it wasn’t a UK power socket” – that was nice and a good idea! 🙂

I also needed a UK to European adaptor and a European to UK adaptor to be able to use it in a UK socket and power a UK device. This makes the plug stick out quite far from the socket, but it is secure and doesn’t drop over time.

A couple of days later my plug arrived and I set about logging into the device as mentioned on the blog using ssh. That was suprisingly easy as it uses the default username and password that’s used on some of the other Ubiquiti devices – username:ubnt, password:ubnt. I spent a couple of hours poking around on the file system, making the lights blink on and off and tested out that it was possible to turn the plug on and off by echoing 1/0 to the relay

echo 1 > /proc/power/relay1
echo 0 > /proc/power/relay1

I also found the code for the web GUI on the plug /usr/www/mfi/power.cgi (the code behind this: http:///mfi/power.cgi). And that got me thinking…

I was originally going to control the plug from my server via the home monitoring application using ssh, but that means the Java app will have to invoke the ssh command and I’d prefer to have an API on the plug that I could call. I could write another cgi file like the power.cgi file, remove the session aspect and control the plug as Ubiquiti have done, but why bother! Ubiquiti has done all the leg work – I can just use their web app! 🙂

So I began playing with the power GUI and found that it was making POST and GET requests from the GUI to http:///sensors/1/ to fetch the current status and turn the plug on and off. Well that’s easy to control! After a bit of experimentation, these are the four curl commands I need.

Login

You will get an AIROS_SESSIONID back which needs to be put in as a cookie header in the following curl requests.

Get plug data

curl -v \
    http://<device ip>/sensors/1/ \
    -H "Cookie: AIROS_SESSIONID=015152b0143a0267f8f0ae30fcb42f6f"
{"sensors":[{"state":{"output":0,"power":0.0,"energy":0.0,"enabled":0,"current":0.0,"voltage":0.0,"powerfactor":0.0,"relay":0,"lock":0}}],"status":"success"}
{"sensors":[{"state":{"output":1,"power":0.0,"energy":0.0,"enabled":0,"current":0.0,"voltage":0.0,"powerfactor":0.0,"relay":1,"lock":0}}],"status":"success"}

Switch the plug off

curl -v \
    -X PUT \
    http://<device ip>/sensors/1/ \
    -H "Cookie: AIROS_SESSIONID=015152b0143a0267f8f0ae30fcb42f6f" \
    -H "Content-Type: application/x-www-form-urlencoded" \
    -d "output=0"
{"status":"success"}

Switch the plug on

curl -v \
    -X PUT \
    http://<device ip>/sensors/1/ \
    -H "Cookie: AIROS_SESSIONID=015152b0143a0267f8f0ae30fcb42f6f" \
    -H "Content-Type: application/x-www-form-urlencoded" \
    -d "output=1"
{"status":"success"}

Output and enabled on

The above is only controlling the output field but on the GUI you will notice there are two fields, output and enabled. Switching enabled on gives stats about the plugs energy usage but it’s not required to control the device.

To switch the plug on with output and enabled on, simply run

curl -v \
    -X PUT \
    http://<device ip>/sensors/1/ \
    -H "Cookie: AIROS_SESSIONID=015152b0143a0267f8f0ae30fcb42f6f" \
    -H "Content-Type: application/x-www-form-urlencoded" \
    -d "output=1&enabled=1"
{"status":"success"}

If you then request the plug data, you would get something similar to the following response:

curl -v \
    http://<device ip>/sensors/1/ \
    -H "Cookie: AIROS_SESSIONID=015152b0143a0267f8f0ae30fcb42f6f"
{"sensors":[{"state":{"output":1,"power":958.341251254,"energy":44.0625,"enabled":1,"current":4.023638963,"voltage":238.905914783,"powerfactor":0.99695206,"relay":1,"lock":0}}],"status":"success"}

 

In the next part I’ll go through how I controled the plug from Java using the APIs above.

Home Monitoring Re-Write Number Four!

Since getting the Tesla Powerwall installed, our trusted Wattson has not been able to display correct figures as it can’t tell if we are importing or exporting until the Powerwall is full.  The Wattson displays a relatively static value of +150W indicating that we’re importing, yet the data from the various other devices in the house contradicts that figure.

So it’s time to say goodbye to Wattson and hand it on to a neighbour and hope they get some use out of it.

Wattson’s demise is a great excuse to upgrade to a tablet and display a lot more information than just whether we’re importing or exporting, so I’ve gone out and bought a Samsung Galaxy Tab A from JL to replace Wattson.

In order to display more information on the tablet, I needed to re-write the home monitoring application and start graphing the data at home rather than relying on PVOutput.  PVOutput is a great website, but it’s limited to a 5 minute picture of what’s going on and I’ve run out of fields to upload data, even though I donate to get extra fields! Wattson has gotten us used to being able to see what’s going on instantly rather than waiting for a snapshot 5 minutes later.

The second re-write I did of the home monitoring application in 2015 has been running well for a few years, but despite what I wrote back then about it being maintainable, it was a pain to add in a new datasource and it was written in my least favourite framework – Mule.

Since then I’ve tried re-writing it in Node.js, but that code was less than elegant and not tested at all… It also relied on a heavy weight MySQL database which I wanted to avoid if possible. HSQLDB may be a bit basic, but it’s served me well for many years and allows me to make changes to the files in a text editor if required.

I did learn something valuable from the Node.js re-write – consolidate the five tables I had before into one large table. I’ve changed the following five tables

to a single table for ease of storing the data and to save space.

The previous database file size was 640MB (note that’s more than 200MB per year as I blogged about the database being 400MB only last year) vs. the new single table layout file size of 240MB. Every field in the database except the composite primary keys are nullable. This allows the data to be stored into the table in any order, after all I can’t guarantee which Arduino will send it’s data first.

The next step was to work out how to convert the database from the original layout to the new layout without having my pc running at 100% for over 2 hours (the first time I loaded the data from the old tables to the new table, this is exactly what happened!). The trick was to not insert based on a select union, but to use the HSQLDB merge functionality. The two hour ETL turned into a three minute ETL. This much improved ETL time allows me to take a copy of the old database (the in use one) at any time, transform it and check the new app is compatible with the schema and can write data into the new layout correctly.

As I’ve mentioned above, the new application is no longer based on Mule and instead is a Spring Boot app.   The home monitoring application receives input using Spring MVC controllers and persists the data to the database against the date and time (rounded to the minute).

At the service layer, there’s also three separate scheduled services, one for uploading PVOutput data once a minute, one for requesting the EE addons status page and scraping the data every hour and one for calling the Tesla Powerwall API every five seconds.

EE addons status page scraping I hear you say… “what’s that for?”  We no longer have fixed line internet and rely on EE 4G internet, which is great until we run out of data two days before the end of the month!  The EE addons status page displays how much data you have used, how much is remaining and how long until the next period.  Since I’ve now got the option to display a lot of different data on the tablet, it seemed sensible to display the EE data allowance too!

For anyone interested in doing something similar, here’s a class I’ve written to read the HTML and trim it to extract the right bits of information. The fields aren’t accessible as I don’t store the information – I simply pass it straight to Splunk via toString.

package uk.co.vsf.home.monitoring.service.ee;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import static org.apache.commons.lang3.StringUtils.*;

public class EeDataStatus {

	private static final String ALLOWANCE_LEFT = "allowance__left";
	private static final String ALLOWANCE_TIMESPAN = "allowance__timespan";
	private static final String BOLD_END = "</b>";
	private static final String BOLD_START = "<b>";
	private static final String SPAN_END = "</span>";
	private static final String SPAN_START = "<span>";
	private static final String DOUBLE_SPACE = "  ";

	private final String allowance;
	private final String remaining;
	private final String timeRemaining;

	public EeDataStatus(final String response) {
		String allowance = response.substring(response.indexOf(ALLOWANCE_LEFT) + ALLOWANCE_LEFT.length());
		allowance = allowance.substring(0, allowance.indexOf(SPAN_END));

		Pattern pattern = Pattern.compile("(\\d+.*\\d*GB)");
		Matcher matcher = pattern.matcher(allowance);

		matcher.find();
		this.remaining = matcher.group();
		matcher.find();
		this.allowance = matcher.group();

		String timespan = response.substring(response.indexOf(ALLOWANCE_TIMESPAN) + ALLOWANCE_TIMESPAN.length());
		timespan = timespan.substring(0, timespan.indexOf(SPAN_END));
		timespan = timespan.substring(timespan.indexOf(SPAN_START) + SPAN_START.length());
		timespan = timespan.replaceAll(BOLD_END, EMPTY).replaceAll(BOLD_START, EMPTY);
		timespan = timespan.replaceAll(CR, EMPTY);
		timespan = timespan.replaceAll(LF, EMPTY);
		timespan = timespan.replaceAll(DOUBLE_SPACE, SPACE);
		timespan = StringUtils.trim(timespan);
		this.timeRemaining = timespan;
	}

	@Override
	public String toString() {
		return new ReflectionToStringBuilder(this).toString();
	}
}

When I tried writing the home monitoring application in Node.js I gave Prometheus a go to see whether that would be a good tool for graphing at home.  It worked well when graphing small sets of data, but when I tried to graph over a years worth of data, it either errored because there was too much data coming back from the query, or took a vast amount of time to refresh the graph.  It’s possible I wasn’t using the tool correctly, but I decided it wasn’t for me in this use case because of the inability to graph large amounts of data and because it’s not as intuitive as the graphing tool I’ve chosen to go with.

So what graphing tool have I chosen?  Splunk 🙂

I chose Splunk for a number of reasons:

  1. I’ll be sending less than 500MB to Splunk a day, so it’s free 😀
  2. It’s incredibly intuitive to search through data in Splunk, so I should be able to give my dad a basic lesson and he can create graphs for himself. I had considered the ELK stack, but the searching language isn’t quite as intuitive…
  3. Splunk doesn’t care about the schema of the data you throw at it.  This makes it easy to work with as I can add/remove fields when required and not have to change a schema.

Writing the data to Splunk uses the ToStringBuilder JSON format and a Log4j socket appender.  The ToStringBuilder format is configured at bootup via the following component.

package uk.co.vsf.home.monitoring;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import org.springframework.stereotype.Component;

@Component
public class ToStringBuilderStyleComponent {

	public ToStringBuilderStyleComponent() {
		ToStringBuilder.setDefaultStyle(ToStringStyle.JSON_STYLE);
	}
}

And I chose the Log4j socket appender because it doesn’t require the use of tokens to talk to Splunk.

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="warn">
    <Appenders>
        <Socket name="socket" host="SERVER NAME" port="9500">
            <PatternLayout pattern="%m%n"/>
        </Socket>
        <Console name="STDOUT" target="SYSTEM_OUT">
        </Console>
    </Appenders>
    <Loggers>
        <Logger name="uk.co.vsf.home.monitoring" level="info" additivity="false">
            <AppenderRef ref="socket" />
            <AppenderRef ref="STDOUT" />
        </Logger>

	...

</Configuration>

Bringing it all together, we’ve gone from Wattson which displayed only one figure – house load – as shown in the (albeit not great) picture below:

To this 😀

And this complicated device/application diagram

Hopefully this incarnation of the home monitoring application will last a few years, but I suspect I’ll be re-writing it all again at some point 🙂

References
Tesla Powerwall 2 API https://github.com/vloschiavo/powerwall2/
Log4j2 Socket Appender https://logging.apache.org/log4j/2.x/manual/appenders.html#SocketAppender