#messdaten

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.

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. https://youtu.be/jkoJ0EqjVac 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": , "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.

Auf der Seite wird auch erläutert wie du diese installierst, das ist jedoch recht einfach, denn du musst hier lediglich im VBA Editor auf Datei > Datei importieren, klicken und dann die Datei "JsonConverter.bas" öffnen.

Du solltest diese Datei dann unter dem Knoten Module finden (siehe Grafik). 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.

Dazu navigieren wir über Datei zu den Optionen.

Wählen hier den Eintrag "Menüband anpassen" aus und setzen dann auf der rechten Seite den Haken bei "Entwicklertools".

In dem neuen Reiter Entwicklertools können wir dann aus dem Bereich "ActivX Steuerelemente" die Befehlsschaltfläche wählen und platzieren.

Dieses machen wir für jeweils für die Funktion zum Togglen des Relais, zum lesen der Daten und zum löschen der Messdaten.

Fertige Excel-Mappe zum Download

Nachfolgend findest du die fertige Excel Arbeitsmappe mit dem VBA Code zum Download. Microsoft Excel Arbeitsmappe - ShellyPM_readdata.xlsm zum lesen der Daten vom Shelly Plus PM

Der VBA Quellcode