Shelly Plus PM: So holst du deine Messdaten in Excel

Wie du die Messdaten in der Shelly Smart Control App exportierst und in Excel visualisierst, habe ich dir bereits in einem letzten Beitrag gezeigt. Hier soll es nun darum gehen, wie du diese Messdaten in Excel abrufen und in einer Tabelle sammeln kannst.

Inhaltsverzeichnis

• Welchen Vorteil habe ich in Excel?
• Aufbauen einer HTTP-Verbindung zum Shelly
  • Abrufen der Daten per Postman
  • Aktivieren des angeschlossenen Verbrauchers per HTTP-Request
• Abrufen der Daten aus Microsoft Excel
  • Schritt 1 – erzeugen von zwei Tabellen
  • Schritt 2 – Anlegen der Verweise in VBA & importieren der BAS-Datei zum parsen von JSON
  • Schritt 3 – Absenden eines HTTP-Request mit VBA
  • Schritt 4 – Auswerten des JSON-Reponse
  • Schritt 5 – Formatieren der Datenreihen
  • Schritt 6 – Aktivieren / Deaktiven des Relais vom Shelly per HTTP-Request
  • Schritt 7 – löschen der Messergebnisse und zurücksetzen des Zeilenindexes
  • Schritt 8 – Erzeugen von Schaltflächen
• Fertige Excel-Mappe zum Download
• Der VBA Quellcode
• Quellenangaben
• Fazit & Abschluss

Welchen Vorteil habe ich in Excel?

Das Programm Microsoft Excel bietet die Möglichkeit, mit wenigen Klicks große Daten zu visualisieren. Des Weiteren kannst du mit der Sprache Visual Basic for Applications (kurz VBA) in deiner Excel-Mappe Code erzeugen, welcher dir bei bestimmten Aufgaben behilflich ist.

In unserem Fall möchten wir per HTTP Request & Response die Daten auslesen und in eine Tabelle schreiben. Dazu benötigen wir ein paar Daten, welche wir in einer separaten Tabelle speichern und so unsere Arbeitsmappe dynamisch halten. Die fertige Arbeitsmappe kannst du dir am Ende von diesem Beitrag herunterladen.

Aufbauen einer HTTP-Verbindung zum Shelly

Zunächst müssen wir eine HTTP-Verbindung zum Shelly aufbauen. Über diese Verbindung (auch als Request genannt) kannst du Daten abfragen und auch das Relais steuern.

Eine ausführliche Dokumentation zum Shelly Plus 1PM findest du unter https://shelly-api-docs.shelly.cloud/gen2. Uns soll besonders der Abschnitt unter https://shelly-api-docs.shelly.cloud/gen2/ComponentsAndServices/Switch#switchgetstatus-example interessieren.

Zunächst möchte ich dir erläutern, wie du die Daten per Postman abrufen kannst, denn zunächst muss man ja erstmal herausfinden, wie man sich zum Shelly verbindet.

Mein Shelly Plus 1PM Mini ist unter der IP-Adresse 192.168.178.35 erreichbar, solltest du also nachfolgend Skripte, Bilder etc. mit dieser Adresse finden, so musst du diese für dein Gerät anpassen.

Messdaten vom Shelly Plus PM Mini in Microsoft Excel via VBA laden

Dieses Video auf YouTube ansehen.

Abrufen der Daten per Postman

Mit der nachfolgenden Adresse kann man den Status des Shellys abrufen:

http://192.168.178.35/rpc/Switch.GetStatus?id=0

Als Antwort erhält man hier ein JSON mit den Daten zum aktuellen Verbrauch.

{
    "id": 0,
    "source": "init",
    "output": false,
    "apower": 0.0,
    "voltage": 224.6,
    "freq": 50.0,
    "current": 0.000,
    "aenergy": {
        "total": 0.000,
        "by_minute": [
            0.000,
            0.000,
            0.000
        ],
        "minute_ts": 1696259728
    },
    "temperature": {
        "tC": 49.3,
        "tF": 120.7
    }
}

Aktivieren des angeschlossenen Verbrauchers per HTTP-Request

Das Relais und somit den angeschlossenen Verbraucher kannst du ebenso per Postman ganz einfach per HTTP-Request aktivieren und auch deaktivieren.

Dazu benötigen wir nur eine Adresse, um das Relais umzuschalten:

http://192.168.178.35/rpc/Switch.Toggle?id=0

Um einen sicheren Status zu setzen, kann man auch die Funktion „Switch.Set“ mit dem Parameter „on“ aufrufen. Dieser Parameter erhält dann ein Boolean (true/false) für den neuen Status des Relais.

http://192.168.178.35/rpc/Switch.Set?id=0&on=true
http://192.168.178.35/rpc/Switch.Set?id=0&on=false

Als Antwort erhält man wiederum ein JSON mit dem Zustand des Relais zuvor:

{
    "was_on": true
}

Abrufen der Daten aus Microsoft Excel

Nachdem wir nun herausgefunden haben, wie man die Daten per einfachem HTTP-Request abrufen können, möchten wir einen Schritt weiter gehen und diese nun in Excel anzeigen. Dazu öffnen wir Excel und erzeugen eine neue Arbeitsmappe, welche wir sogleich als *.xlsm speichern.

Schritt 1 – erzeugen von zwei Tabellen

Für unsere Messdaten und die Verbindungs- und Konfigurationsdaten benötigen wir jeweils eine Tabelle. Diese legen wir an und benennen diese mit „Messdaten“ und „Daten“. Hier kannst du natürlich auch andere Titel wählen.

Schritt 2 – Anlegen der Verweise in VBA & importieren der BAS-Datei zum parsen von JSON

Mit der Tastenkombination Alt+F11 wechselt man in die Ansicht VBA und dort müssen wir zunächst zwei zusätzliche Verweise laden und eine BAS-Datei importieren.

Die Verweise sind:

• Microsoft Scripting Runtime (für das Objekt Dictionary), sowie
• Microsoft XML, v6.0 (für den HTTP Request)

Für das spätere Parsen des JSON-Response vom Shelly benötigen wir hier eine zusätzliche BAS-Datei diese können wir uns vom Git Repository VBA-tools/VBA-JSON als ZIP-Datei herunterladen.

Schritt 3 – Absenden eines HTTP-Request mit VBA

Nachfolgend der Code zum Absenden des HTTP-Request an den Shelly:

Dim request As MSXML2.ServerXMLHTTP60   
Dim ipAdresse As String  
 
'auslesen der Werte aus der Tabelle "Daten"
ipAdresse = Tabelle4.Range("B1").Value 
 
'Absenden eines HTTP-Request an den Shelly
Dim httpRequest As String
httpRequest = "http://" & ipAdresse & "/rpc/Switch.GetStatus?id=0"
Set request = New MSXML2.ServerXMLHTTP60
request.Open "GET", httpRequest, False
 
'Wenn ein Fehler wie Timeout geschieht dann soll zum Abschnitt "FehlerSendRequest"
'gesprungen werden!
On Error GoTo FehlerSendRequest
request.send

Schritt 4 – Auswerten des JSON-Reponse

Wenn die Antwort vom Shelly ein HTTP-Code 200 also ‚OK‘ enthält dann wurden Daten gesendet und wir können diese dann in ein Dictionary parsen. Dieses Dictionary hat den Vorteil das wird recht einfach auf die Schlüssel/Werte Paare zugreifen können.

'Auslesen der Antwort und ablegen in ein Dictionary
'von einem Dictionary kann man einfacher auf die Schlüssel/Werte Paare zugreifen
Dim jsonObject As Dictionary
Set jsonObject = ParseJson(request.responseText)
   
'Schreiben der aktuellen Daten in die Tabelle "Messdaten"
'es wird dazu der letzte inkrementierte Index für die Zeile genutzt
'Umwandeln des Zeitstempels welcher die Minuten seit dem 01.01.1970 repräsentiert in ein
'lesbares Format plus dem Offset für die Zeitzone
Tabelle3.Range("A" & lastIndex).Value = DateAdd("s", CDbl(jsonObject("aenergy")("minute_ts") + timeZoneOffset), "1/1/1970")
'Speichern der Spannung
Tabelle3.Range("B" & lastIndex).Value = jsonObject("voltage")
'Speichern der Frequenz
Tabelle3.Range("C" & lastIndex).Value = jsonObject("freq")
'Speichern der Ampere
Tabelle3.Range("D" & lastIndex).Value = jsonObject("current")
'Speichern der Leistung
Tabelle3.Range("E" & lastIndex).Value = jsonObject("apower")

Schritt 5 – Formatieren der Datenreihen

Wenn wir eine neue Zeile gelesen und in die Tabelle „Messwerte“ geschrieben haben, möchten wir diese nun auch vernünftig formatieren dieses machen wir mit einer Funktion und drei zusätzliche Unterfunktionen damit wir die eigentliche Funktion kurz halten.

Die Hintergrundfarbe der Zelle sowie die Schriftart kannst du nach deinem belieben anpassen, die Farbwerte kannst du zbsp. unter https://www.denisreis.com/excel-vba-alle-farbindizes-colorindex-auflisten/ entnehmen.

'Formatieren der Datentabelle nachdem eine neue Zeile hinzugefügt wurde
Sub formatiereTabelle()
    Dim lastIndex As Integer
    'lesen des Zeilenindexes für die anschließende For-Schleife
    lastIndex = Tabelle4.Range("B3").Value
    
    Dim colorIndexOdd As Integer
    Dim colorIndexEven As Integer
        
    'ColorIndex für die Hintergrundfarbe
    colorIndexOdd = 43
    colorIndexEven = 50
    
    Dim fontColorBlackIndex As Integer
    Dim fontColorWhiteIndex As Integer
    
    fontColorBlackIndex = 1
    fontColorWhiteIndex = 2
    
    Dim colorIdxBg As Integer
    Dim colorIdxFont As Integer
    'Über alle erzeugten Zeilen iterieren
    For i = 1 To lastIndex
        'Wenn der Rest der Division des aktuellen Index ungleich 1 ist dann soll
        'der colorIndexOdd gesetzt werden ansonsten colorIndexEven
        colorIdxBg = IIf((i Mod 2) = 0, colorIndexOdd, colorIndexEven)
        Call setCellBackgroundColor(colorIdxBg, i)
        
        colorIdxFont = IIf((i Mod 2) = 0, fontColorBlackIndex, fontColorWhiteIndex)
        Call setCellFontColor(colorIdxFont, i)
        
        Call setCellBorder(i)
    Next
End Sub

Sub setCellBackgroundColor(bgcolor As Integer, ByVal currentCellIndex As Integer)
        'Wenn der Rest der Division des aktuellen Index ungleich 1 ist dann soll
        'der colorIndexOdd gesetzt werden ansonsten colorIndexEven
        Tabelle3.Range("A" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("B" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("C" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("D" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("E" & currentCellIndex).Interior.colorIndex = bgcolor
End Sub

Sub setCellFontColor(fontcolor As Integer, ByVal currentCellIndex As Integer)
        Tabelle3.Range("A" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("B" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("C" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("D" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("E" & currentCellIndex).Font.colorIndex = fontcolor
End Sub

Sub setCellBorder(ByVal currentCellIndex As Integer)
        Tabelle3.Range("A" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("B" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("C" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("D" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("E" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
End Sub

Schritt 6 – Aktivieren / Deaktiven des Relais vom Shelly per HTTP-Request

Das Relais des Shellys läßt sich eigentlich über die Shelly Smart Control App steuern, jedoch möchte ich dieses auch über die Excelmappe machen und daher implementiere ich eine zusätzliche funktion dafür. Hier gibt es einen HTTP-Request welcher einfach den Zustand des Relais togglet d.h. aus dem Zustand „AN“ wird „AUS“ und umgekehrt.

Dim ipAdresse As String
'lesen der IPAdresse aus der Zelle B1
ipAdresse = Range("B1").Value
 
'Absenden eines HTTP-Request an den Shelly
Dim request As MSXML2.ServerXMLHTTP60
Dim httpRequest As String
httpRequest = "http://" & ipAdresse & "/rpc/Switch.Toggle?id=0"
Set request = New MSXML2.ServerXMLHTTP60
request.Open "GET", httpRequest, False
'Wenn ein Fehler auftritt dann soll eine Fehlermeldung ausgegeben werden!
On Error GoTo FehlerSendRequest
request.send

Schritt 7 – löschen der Messergebnisse und zurücksetzen des Zeilenindexes

Wenn wir die aufgezeichneten / empfangenen Messdaten löschen wollen, dann möchten wir diese ggf. auch löschen. Ein Weg wäre hier manuell alle Zeilen zu markieren und zu entfernen und den Zeilenindex auf 2 zu setzen. Jedoch geht dieses kompfortabler über eine Schaltfläche.

Dim lastIndex As Integer
'lesen des Zeilenindexes für die anschließende For-Schleife
lastIndex = Range("B3").Value

'Schleife über alle erzeugten Zeilen
For i = 2 To lastIndex
       Tabelle3.Range("A" & i).Value = ""
       Tabelle3.Range("B" & i).Value = ""
       Tabelle3.Range("C" & i).Value = ""
       Tabelle3.Range("D" & i).Value = ""
       Tabelle3.Range("E" & i).Value = ""
Next
        
'Zurücksetzen des Zeilenindexes auf 2
Range("B3").Value = 2

Schritt 8 – Erzeugen von Schaltflächen

Diese Funktionen können wir nun entweder in einem eigenen Ribbon bereitstellen oder wir platzieren auf der Tabelle ein paar Schaltflächen. In meinem Fall möchte ich die Schaltflächen wählen.

Fertige Excel-Mappe zum Download

Nachfolgend findest du die fertige Excel Arbeitsmappe mit dem VBA Code zum Download.

Der VBA Quellcode

Hier nun der komplette VBA-Code mit Kommentaren:

' Wenn die Schaltfläche "Daten vom Shelly lesen" geklickt wird...
Private Sub CommandButton1_Click()
    Dim request As MSXML2.ServerXMLHTTP60
    
    Dim ipAdresse As String
    Dim lastIndex As Integer
    Dim timeZoneOffset As Integer
    
    'auslesen der Werte aus der Tabelle "Daten"
    ipAdresse = Tabelle4.Range("B1").Value
    lastIndex = Tabelle4.Range("B3").Value
    'Wert für die Zeitzone von Stunden in Minuten umrechnen
    timeZoneOffset = Tabelle4.Range("B4").Value * (60 * 60)
    
    'Absenden eines HTTP-Request an den Shelly
    Dim httpRequest As String
    httpRequest = "http://" & ipAdresse & "/rpc/Switch.GetStatus?id=0"
    Set request = New MSXML2.ServerXMLHTTP60
    request.Open "GET", httpRequest, False
    'Wenn ein Fehler wie Timeout geschieht dann soll zum Abschnitt "FehlerSendRequest"
    'gesprungen werden!
    On Error GoTo FehlerSendRequest
    request.send
            
    'Wenn ein HTTP-Code 200 "OK" empfangen wurde, dann kann ein JSON ausgewertet werden.
    If request.Status = 200 Then
        'Auslesen der Antwort und ablegen in ein Dictionary
        'von einem Dictionary kann man einfacher auf die Schlüssel/Werte Paare zugreifen
        Dim jsonObject As Dictionary
        Set jsonObject = ParseJson(request.responseText)
    
        'Schreiben der aktuellen Daten in die Tabelle "Messdaten"
        'es wird dazu der letzte inkrementierte Index für die Zeile genutzt
        'Umwandeln des Zeitstempels welcher die Minuten seit dem 01.01.1970 repräsentiert in ein
        'lesbares Format plus dem Offset für die Zeitzone
        Tabelle3.Range("A" & lastIndex).Value = DateAdd("s", CDbl(jsonObject("aenergy")("minute_ts") + timeZoneOffset), "1/1/1970")
        'Speichern der Spannung
        Tabelle3.Range("B" & lastIndex).Value = jsonObject("voltage")
        'Speichern der Frequenz
        Tabelle3.Range("C" & lastIndex).Value = jsonObject("freq")
        'Speichern der Ampere
        Tabelle3.Range("D" & lastIndex).Value = jsonObject("current")
        'Speichern der Leistung
        Tabelle3.Range("E" & lastIndex).Value = jsonObject("apower")
        
        Call formatiereTabelle
                
        'inkrementieren des Zeilenindexes
        Range("B3").Value = lastIndex + 1
                
        'eine MessageBox anzeigen das die Daten geladen wurden
        MsgBox "Daten wurden geladen!", vbInformation, "Hinweis..."
    End If
    Exit Sub
FehlerSendRequest:
        'eine Fehlermeldung ausgeben
        MsgBox "Fehler beim lesen der Daten von " & ipAdresse, vbCritical, "Fehler..."
End Sub

'Formatieren der Datentabelle nachdem eine neue Zeile hinzugefügt wurde
Sub formatiereTabelle()
    Dim lastIndex As Integer
    'lesen des Zeilenindexes für die anschließende For-Schleife
    lastIndex = Tabelle4.Range("B3").Value
    
    Dim colorIndexOdd As Integer
    Dim colorIndexEven As Integer
        
    'ColorIndex für die Hintergrundfarbe
    colorIndexOdd = 43
    colorIndexEven = 50
    
    Dim fontColorBlackIndex As Integer
    Dim fontColorWhiteIndex As Integer
    
    fontColorBlackIndex = 1
    fontColorWhiteIndex = 2
    
    Dim colorIdxBg As Integer
    Dim colorIdxFont As Integer
    'Über alle erzeugten Zeilen iterieren
    For i = 1 To lastIndex
        'Wenn der Rest der Division des aktuellen Index ungleich 1 ist dann soll
        'der colorIndexOdd gesetzt werden ansonsten colorIndexEven
        colorIdxBg = IIf((i Mod 2) = 0, colorIndexOdd, colorIndexEven)
        Call setCellBackgroundColor(colorIdxBg, i)
        
        colorIdxFont = IIf((i Mod 2) = 0, fontColorBlackIndex, fontColorWhiteIndex)
        Call setCellFontColor(colorIdxFont, i)
        
        Call setCellBorder(i)
    Next
End Sub

Sub setCellBackgroundColor(bgcolor As Integer, ByVal currentCellIndex As Integer)
        'Wenn der Rest der Division des aktuellen Index ungleich 1 ist dann soll
        'der colorIndexOdd gesetzt werden ansonsten colorIndexEven
        Tabelle3.Range("A" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("B" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("C" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("D" & currentCellIndex).Interior.colorIndex = bgcolor
        Tabelle3.Range("E" & currentCellIndex).Interior.colorIndex = bgcolor
End Sub

Sub setCellFontColor(fontcolor As Integer, ByVal currentCellIndex As Integer)
        Tabelle3.Range("A" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("B" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("C" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("D" & currentCellIndex).Font.colorIndex = fontcolor
        Tabelle3.Range("E" & currentCellIndex).Font.colorIndex = fontcolor
End Sub

Sub setCellBorder(ByVal currentCellIndex As Integer)
        Tabelle3.Range("A" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("B" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("C" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("D" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        Tabelle3.Range("E" & currentCellIndex).BorderAround LineStyle:=xlContinuous, Weight:=xlThin
End Sub

'Wenn die Schaltfläche "gelesene Messdaten löschen" geklickt wird, dann sollen die Daten aus der
'Tabelle "Messdaten" entfernt werden und der Zeilenindex auf 2 gesetzt werden
Private Sub CommandButton2_Click()
    Dim lastIndex As Integer
    'lesen des Zeilenindexes für die anschließende For-Schleife
    lastIndex = Range("B3").Value
    'Schleife über alle erzeugten Zeilen
    For i = 2 To lastIndex
        Tabelle3.Range("A" & i).Value = ""
        Tabelle3.Range("B" & i).Value = ""
        Tabelle3.Range("C" & i).Value = ""
        Tabelle3.Range("D" & i).Value = ""
        Tabelle3.Range("E" & i).Value = ""
        
        'Formatierungen entfernen
        Tabelle3.Range("A" & i).ClearFormats
        Tabelle3.Range("B" & i).ClearFormats
        Tabelle3.Range("C" & i).ClearFormats
        Tabelle3.Range("D" & i).ClearFormats
        Tabelle3.Range("E" & i).ClearFormats
    Next
        
    'Zurücksetzen des Zeilenindexes auf 2
    Range("B3").Value = 2
End Sub
'Wenn die Schaltfläche "Verbraucher aktiv" geklickt wird, dann soll der Zustand des Relais getoggelt werden,
'd.h. aus dem Zustand "AUS" wird "AN" und umgekehrt!
Private Sub ToggleButton1_Click()
 Dim ipAdresse As String
 'lesen der IPAdresse aus der Zelle B1
 ipAdresse = Range("B1").Value
 
 'Absenden eines HTTP-Request an den Shelly
 Dim request As MSXML2.ServerXMLHTTP60
 Dim httpRequest As String
 httpRequest = "http://" & ipAdresse & "/rpc/Switch.Toggle?id=0"
 Set request = New MSXML2.ServerXMLHTTP60
 request.Open "GET", httpRequest, False
 'Wenn ein Fehler auftritt dann soll eine Fehlermeldung ausgegeben werden!
 On Error GoTo FehlerSendRequest
 request.send
 Exit Sub
FehlerSendRequest:
        MsgBox "Fehler beim lesen der Daten von " & ipAdresse, vbCritical, "Fehler..."
End Sub

Quellenangaben

Für diesen Beitrag habe ich nachfolgende Quellen verwendet:

• https://shelly-api-docs.shelly.cloud/gen2/
• https://github.com/VBA-tools/VBA-JSON
• https://codingislove.com/excel-json/

Fazit & Abschluss

In diesem Beitrag habe ich dir nun gezeigt wie du die Daten per HTTP-Request von einem Shelly Plus PM lesen und in eine Excel Tabelle eintragen kannst.

Im nächsten Schritt würde ich dann aus diesen Daten Diagramme erzeugen.