Custom Solar Energy Solution for SMA WebConnect Inverters

sillygoose · January 28, 2021, 8:15pm

TL;DR

Moved to an electric home with a geothermal heat pump, decided I could be net-zero CO2, added solar panels with three inverters; found Home Assistant and you can imagine the rest.

Site

Located in Upstate New York, the site is two ground mounted arrays of 36 panels panels each, just shy of 25 kWp production. The panels are broken into eight strings, three strings each go to SMA Sunny Boy 7.7 inverters and the remaining two strings feed a Sunny Boy 5.0 inverter.

No real-time monitoring

SMA has a web site where I could see production data but it lags by a couple of hours, not very useful. I could however log into each inverter using the WebConnect interface but lacks a clear view of the entire site. Home Assistant with the SMA integration gave the initial insight I needed but it only supports a single inverter (let alone three) so I decided to go the custom route.

So I learned Python (and InfluxDB, Grafana, Docker, etc) and wrote my own utility that collects the data from all three inverters (or one if that is all you have), aggregates the data, and sends it to Home Assistant using MQTT or to an InfluxDB database. Without going into too many details, multisma2 grabs everything you see in a browser from multiple inverters so access to inverter production (AC/DC power, voltage, and current), status, grid, and more are available.

Results

A picture is worth a 1000 words, so I present my first Grafana dashboard for my solar site (go easy on me, its my first dashboard).

At the top right is the site real-time production and output of each inverter are in the graph at the top left. Below it is an estimate of the clear sky irradiance available for a tilted surface at my location (thanks Sandia National Laboratories), right now the area under the irradiance curve is about 110 kWh, this will go up as the days lengthen and the sun is higher in the sky. But January here has been very cloudy and my panels are constantly being soiled by the fluffy white stuff, easily seen in the clear sky chart or the daily production numbers at bottom left (along with the past monthly production).

On the right side below the current production is the inverter efficiencies, followed by some historical production data. Last but not least is the inverter status data, boring for now but perhaps more interesting when summer comes along and the DC power reaches the inverter maximums.

multisma2 project

I think I have everything here, code, dashboards, and Docker files. Probably too specialized for many but if you happen to have an SMA Sunny Boy inverter then there might be something here for you.

I’ll try to answer any questions but keep in mind I am a rookie at all of this and I am now off to work on the demand side using my CircuitSetup 24-channel power monitoring project (which will be another post in the not so distant future).

Painy · February 3, 2021, 11:01pm

That looks awesome. You’ve single-handedly put the Sunny portal to shame
Would love to replicate this, although it’s pretty advanced for me. I’ll give it a go when I find the time.

Does this require a Sunny connect admin account? My installer only configured a basic user account with limited functionality.
Will the irradiance work for other parts of the world as well?
Looking forward to you power monitoring project

sillygoose · February 4, 2021, 3:25am

You just need to be able to use the HA SMA integration, this requires you have WebConnect and a User account on your inverter(s).

Everything can be configured, including the irradiance. It needs your location (lat/long) and some site details (panel azimuth and tilt). In my case I convert the W/m² irradiance output for my location and site to an estimate of solar potential (panel area and efficiency from the panel data sheet).

sillygoose · February 4, 2021, 6:47pm

And a minor update to my Grafana Production dashboard, now with CO₂ avoided results and a slightly cleaner layout.

hunkyn · February 10, 2021, 6:28pm

Damn. This looks awesome. I will try to set this up tonight and will report back if any issues. Seems like I need to setup a quite a few things before I can do this. I do have another RPi for Openmediavault with docker installed I will have to do some testing before I can do it on Home assistant.

hunkyn · February 13, 2021, 4:08pm

@sillygoose I was trying to setup on RPi and then noticed HA has InfluxDB and Grafana as official add-ons. How do I setup in this case? In your GitHub it says clone repository but clone where in HA or a separate device or somewhere else?

sillygoose · February 13, 2021, 6:25pm

I run two RPIs, one with vanilla Home Assistant and the other running Ubuntu with all my Docker containers. This way I can control what versions of software I am running and play around, for example, I have both InfluxDB 1.8.4 and 2.0.3, old version for production and the new version to figure it all out before transitioning.

Certainly you can use the HA addons for InfluxDB and Grafana (HA uses the newest version of Grafana which broke the status map addon so waiting on that fix), it would be nice to multisma2 on the Home Assistant Pi, perhaps sneaking the image via Portainer and manage it there is an option.

For development I run/test multisma2 on my Mac mini, then build a Docker image on the Pi when ready to deploy a new version. It might pay for you to wait a day or two since I just added InfluxDB 2.0 support and will have an update that replaces the configuration settings with a YAML file. You can see a preview in the utility I just posted that downloads history from the Sunny Boy inverters to InfluxDB, I’ll migrate multisma2 to this YAML configuration in the next day or so.

fastender · February 13, 2021, 6:29pm

Awesome! Many thanks.

hunkyn · February 13, 2021, 9:41pm

Thanks. I tried to git clone and setup the file but running into an error in IDLE when I run the multisma2.py

Here is the error I am getting.

sillygoose · February 13, 2021, 10:35pm

You need the load the packages used in the project, these are listed in the README file:

paho-mqtt
aiohttp
asyncio
astral
python-dateutil
jmespath
influxdb
pvlib (which requires pandas, numpy. scipy, and tables)

pip3 install pvlib tables
pip3 install aiohttp astral python-dateutil
pip3 install paho-mqtt jmespath influxdb

sillygoose · February 14, 2021, 3:36pm

Did some reading and learned how I failed to create a setup.py file to properly install the software, a failure on my part but that is what happens with a Python rookie that uses Docker to run the software.

I am in the process of correcting this and once tested on a clean Linux install I’ll post the updates.

sillygoose · February 15, 2021, 2:48am

Made some progress and hopefully I have this setup thing sorted out. I tested this on a clean install of Ubuntu and afterward all the packages needed were installed and the software ran as intended.

The first one up is the utility that reads inverter history and writes it to an InfluxDB bucket. If you have InfluxDB setup you can use it but if not just disable it in the sbhistory.yaml file and it will access the inverter(s) without writing to InfluxDB. If I did it right you just need to do:

git clone https://github.com/sillygoose/sbhistory
cd sbhistory
pip3 install -e .
python3 sbhistory/sbhistory.py

I shall now turn my attention to the multisma2 software since it needs the same setup.py treatment and the new YAML-based configuration file. I also will write my own solar radiation code since I got fed up with the bloated pvlib code that is overkill for this code.

sillygoose · March 4, 2021, 10:38pm

I have posted updates to both multisma2 and sbhistory repos, now using the new YAML configuration files. They are much smaller now as the clear sky code hs been rewritten to use the pvsolar package. Also new are updated InfluxDB2 and Grafana dashboards, I have made the switch and only using InfluxDB2 from now but the scripts should work in the InflixDB 1.8.x with minor changes.

Here is screenshot of the updated Grafana dashboard which includes a nice 30-day running average of the daily production:

crazydane · July 25, 2022, 11:41am

Great work! Any chance you can share the queries for each of the following Grafana panels:

Efficiency
12 Month Production
30 day Production
Operating Status
Condition
Derating

I’m using the regular SMA Solar Integration, but am struggling with getting my InfluxDB queries just right, especially in my kWh production panels. I have 8 SMA inverters in a 60 kW system.

Thanks!

sillygoose · July 25, 2022, 4:00pm

All the Flux queries are in the Grafana dashboard Solar Production Overview.json, if you load this file into Grafana you can see the Flux queries embedded in each panel. Here is the 12 month production which is back to two queries (the previous 12 months and the current month which can have a custom color):

from(bucket: "multisma2")
  |> range(start: -13mo)
  |> filter(fn: (r) => r._measurement == "production" and r._inverter == "site" and r._field == "month")
  |> limit(n:12, offset: 0)
  |> map(fn: (r) => ({ _time: r._time, "Previous Month": r._value }))

  from(bucket: "multisma2")
  |> range(start: -1mo)
  |> filter(fn: (r) => r._measurement == "production" and r._inverter == "site" and r._field == "month")
  |> last()
  |> map(fn: (r) => ({ _time: r._time, "Current Month": r._value }))

Hope you find what you need. I just updated the dashboard so be sure to grab the latest version, undid some Grafana bug workarounds they fixed and have two new panels that track the production difference from a year ago.

crazydane · July 26, 2022, 9:48am

Wow, those queries look WAY different than mine. This is what I have for a single inverter:

SELECT spread("value")
FROM "Wh"
WHERE ("entity_id" = 'sb7_7_1sp_us_41_306_daily_yield')
AND $timeFilter
GROUP BY time(24h) fill(null)

Which gives me this:

My challenge is that I can’t figure out the FluxDB way to do a SELECT sum(spread)… to sum multiple inverters together in my query. I’m a SQL guy and FluxDB is way different and doesn’t seem to support a lot of what I’m used to. I tried adding more inverters using a UNION as well, but that function does not seem to be fully implemented in FluxDB yet.

I got around it for now by adding this entry to sensors.yaml:

# kWh Total
  - platform: template
    sensors:
      total_yield:
        friendly_name: 'Total Yield'
        unit_of_measurement: "kWh"
        value_template: "{{ (states('sensor.sb7_7_1sp_us_40_622_total_yield') | float) + (states('sensor.sb7_7_1sp_us_41_404_total_yield') | float) + (states('sensor.sb7_7_1sp_us_41_306_total_yield') | float) + (states('sensor.sb6_0_1sp_us_41_376_total_yield') | float) + (states('sensor.sma_sb_60_hillside_1_total_yield') | float) + (states('sensor.sma_sb_60_hillside_2_total_yield') | float)}}"

And then build a panel in my dashboard using that sensor instead. But my preference would be to figure out a way to sum the total yields for all the inverters in my FluxDB query. Adding sensors for all my sums just creates a bunch of duplicate data in the database.

I’ll have to look into how to import that json as a Grafana dashboard. Never done that before as I’ve only played with Grafana for a couple of days now, lol.

Nice updates to your dashboard, btw!

Also, I have a weather station with solar radiation and UV sensors, so I’m able to track actual irradiance, which is pretty neat:

sillygoose · July 26, 2022, 4:07pm

I opted to use Flux since it looked more modern and I didn’t want to learn SQL so I might of limited help.

Your query seems to collect the last 24 hours of production where the spread is the difference between the first and last Wh meter measurements. I assume your graph is mislabeled and these are Wh values and not kWh values.

I have the inverter Wh meter data in my database so I can write a similar Flux query and extend it to sum all inverters in my system:

first = from(bucket: "multisma2")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "production")
  |> filter(fn: (r) => r._field == "total_wh")
  |> first()
  |> yield(name: "first")

last = from(bucket: "multisma2")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "production")
  |> filter(fn: (r) => r._field == "total_wh")
  |> last()
  |> yield(name: "last")

union(tables: [first, last])
  |> pivot(rowKey:["_time"], columnKey: ["_inverter"], valueColumn: "_value")
  |> drop(columns: ["_start", "_stop"])
  |> difference(nonNegative: true, columns: ["sb51", "sb71", "sb72"])
  |> map(fn: (r) => ({ r with daily_production: float(v: r.sb51 + r.sb71 + r.sb72) * 0.001 }))
  |> yield(name: "result")

The results of this query are:

create a one row table of inverter Wh readings from the start of the period (‘first’)
create a one row table of inverter Wh readings from the end of the period (‘last’)
merge these tables (union operation) and pivot to get the inverters into columns and take the difference and the sum over all inverters

where ‘daily_production’ is the sum of the three inverters. This would work fine in a Grafana panel called ‘24 hour production’ but we need to do more work to produce data for a 30 day production graph.

P.S. I like the weather station results, what are you using to collect that data?

crazydane · July 27, 2022, 11:47am

I’m kinda following your code, but I’m not clear on what this means exactly:

 from(bucket: "multisma2")

Bucket is completely alien to me coming from the SQL world. Is that like the “database name/schema” and “multisma2” is the Flux database that contains all the data?

In my InfluxDB Admin tab, I created a database called homeassistant that contains all my data. I don’t don’t have to implicitly reference that when doing my selects as its the only database there.

I have a Davis Vantage Pro 2 weather station with the optional solar radiation and UV sensors. I also have the Weatherlink Live device, which gets the data from the weatherstation wirelessly, and in turn plugs into my LAN.

So in sensors.yaml, I have the following:

# WeatherLinkLive API - Local
  - platform: rest
    resource: 'http://10.0.20.240/v1/current_conditions'
    scan_interval: 60
    name: wll
    json_attributes:
      - data
    value_template: 'Vantage Pro 2'
  - platform: template
    sensors:
      wll_temp:
        friendly_name: 'Outside Temp'
        device_class: temperature
        unit_of_measurement: "°F"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['temp'] }}"
      wll_hum:
        friendly_name: 'Outside Humidity'
        device_class: humidity
        unit_of_measurement: "%"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['hum'] }}"
      wll_dewpoint:
        friendly_name: 'Dewpoint'
        device_class: temperature
        unit_of_measurement: "°F"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['dew_point'] }}"
      wll_heatindex:
        friendly_name: 'HeatIndex'
        device_class: temperature
        unit_of_measurement: "°F"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['heat_index'] }}"
      wll_windchill:
        friendly_name: 'WindChill'
        device_class: temperature
        unit_of_measurement: "°F"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['wind_chill'] }}"
      wll_wind_speed:
        friendly_name: 'Wind Speed'
        unit_of_measurement: "mph"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['wind_speed_avg_last_1_min'] }}"
      wll_wind_degrees:
        friendly_name: 'Wind Degrees'
        value_template: >-
          {% if states('sensor.wll_wind_speed') != '0' %}
            {{ state_attr('sensor.wll', 'data')['conditions'][0]['wind_dir_scalar_avg_last_1_min'] }}
          {% endif %}
      wll_rain_day_in:
        friendly_name: 'Daily Rain'
        unit_of_measurement: "in"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['rainfall_daily'] | float / 100 | round(2) }}"
      wll_rain_rate_in:
        friendly_name: 'Rain Rate'
        unit_of_measurement: "in/hr"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['rain_rate_last'] | float / 100 | round(2) }}"
      wll_rain_month_in:
        friendly_name: 'Monthly Rain'
        unit_of_measurement: "in"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['rainfall_monthly'] | float / 100 | round(2) }}"
      wll_rain_storm_in:
        friendly_name: 'Storm Rain'
        unit_of_measurement: "in"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['rain_storm'] | float / 100 | round(2) }}"
      wll_rain_year_in:
        friendly_name: 'Yearly Rain'
        unit_of_measurement: "in"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['rainfall_year'] | float / 100 | round(2) }}"
      wll_solar_rad:
        friendly_name: 'Solar Radiation'
        unit_of_measurement: "w/m²"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['solar_rad'] }}"
      wll_uv_index:
        friendly_name: 'UV Index'
        unit_of_measurement: "index"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][0]['uv_index'] }}"
      wll_pressure:
        friendly_name: 'Barometer'
        device_class: pressure
        unit_of_measurement: "in"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][2]['bar_sea_level'] | round(2) }}"
    # Inside
      wll_temp_in:
        friendly_name: 'Inside Temp'
        device_class: temperature
        unit_of_measurement: "°F"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][1]['temp_in'] }}"
      wll_hum_in:
        friendly_name: 'Inside Humidity'
        device_class: humidity
        unit_of_measurement: "%"
        value_template: "{{ state_attr('sensor.wll', 'data')['conditions'][1]['hum_in'] }}"
      wll_observation_time:
        friendly_name: 'Observed'
        value_template: "{{ state_attr('sensor.wll', 'data')['ts'] | timestamp_custom('%m/%d/%y %H:%M.%S') }}"

I can then access those sensors in Grafana, or anywhere else. Buddy of mine build this dashboard as he has a similar setup, that I adopted to my needs:

Once I start to figure out Flux and Grafana a bit more, I’ll play with overlaying the weather station data with my solar production. I hope to be able to determine when to clean my panels and after cleaning them, how much of an improvement I got.

sillygoose · July 27, 2022, 4:55pm

TL;DR
I would think all of this could be done in InfluxQL, I just don’t know how to do it.

sillygoose:

first = from(bucket: "multisma2")
  |> range(start: -24h)
  |> filter(fn: (r) => r._measurement == "production")
  |> filter(fn: (r) => r._field == "total_wh")
  |> first()
  |> yield(name: "first")

Let’s break down this query.

first = from(bucket: "multisma2")

Buckets are the individual databases within an InfluxDB instance, I have a few and all Flux queries start by selecting the bucket. Everything else is winnowing down the data by selecting the fields we want (I am using the InfluxDB dashboard Explore feature to walk through this).

|> range(start: -24h)

Selects all the data in the last 24 hours, everything else is excluded.

|> filter(fn: (r) => r._measurement == "production")
|> filter(fn: (r) => r._field == "total_wh")

My bucket contains different measurements, the production measurements contain the Wh meter from the Sunnyboy inverters so filter is used to grab those. Within production there are a few choices so we filter for all the total_wh entries.

This will yield three tables that looks like this (this is just the last minute of production to keep it small):

|> first()

Selects the first row of each table (this is the data at the start of the period):

And we are done, we have the Wh meter for each inverter at the start of the period (the yield line just stores the result).

This query repeated but using last() to get the inverter Wh meter values at the end of the period. So there are two query results, one for the start of the period and one for the end of the period:

Now we can merge the two queries, here is the Flux code to do this:

union(tables: [first, last])
  |> sort(columns: ["_value"], desc: false)
  |> pivot(rowKey:["_time"], columnKey: ["_inverter"], valueColumn: "_value")
  |> difference(nonNegative: true, columns: ["sb51", "sb71", "sb72"])
  |> map(fn: (r) => ({ r with daily_production: float(v: r.sb51 + r.sb71 + r.sb72) * 0.001 }))
  |> yield(name: "result")

This will merge the two queries into one.

union(tables: [first, last])

Next we need to pivot to get the Wh meter values into columns by inverters (I added the sort() to make sure the ordering is correct):

|> sort(columns: ["_value"], desc: false)
|> pivot(rowKey:["_time"], columnKey: ["_inverter"], valueColumn: "_value")

With the pivot everything is in that same table so we can use the difference() function to get the production for the period by inverter:

|> difference(nonNegative: true, columns: ["sb51", "sb71", "sb72"])

Finally map() is used to add them all up and convert the result to kWh:

|> map(fn: (r) => ({ r with daily_production: float(v: r.sb51 + r.sb71 + r.sb72) * 0.001 }))

sillygoose · July 27, 2022, 5:02pm

Love the weather dashboard, something to look into.

When my panels are solid white they are soiled.
At 60kWp that is a lot of panels to clean.