Buienradar in Excel met JSON

De mogelijkheden in Excel zijn nagenoeg onbeperkt. Je kunt zelfs je eigen buienradar maken en de gegevens verwerken in een sheet. Dit gaan we doen met behulp van JSON (JavaScript Object Notation). Om de weerdata op te halen maken we gebruik van de gratis API van Weatherapi.com.

Stap 1 – account aanmaken

Maak een gratis account aan op Weatherapi.com voor een persoonlijke API key. Met het gratis account mag je 1.000.000 API calls per maand maken en ontvang je vooruitzichten tot drie dagen vooruit.

Stap 2 – installeer VBA-JSON

Download en installeer VBA-JSON van Tim Hall via GitHub. Op de GitHub pagina van Tim Halll is een stap-voor-stap installatiehandleiding te vinden over de installatie.

Stap 3 – welke gegevens kun je ophalen

De volgende items kunnen worden opgehaald via de gratis API van Weatherapi.com.

• Huidige weersomstandigheden;
• Driedaagse weersverwachting;
• Astronomische gegevens.

Stap 4 – weerdashboard maken

We beginnen met het maken van het dashboard. Hierin wordt de door ons opgehaalde data op een nette manier visueel weergegeven. Op de documentatiepagina van Weatherapi.com staat duidelijk aangegeven welke parameters opgehaald kunnen worden. In ons voobeeld beperken we ons tot de volgende gegevens:

• Datum;
• Maximale verwachtte temperatuur in graden Celsius;
• Minimale verwachtte temperatuur in graden Celsius;
• Totaal verwachtte neerslag in milimeters;
• Regenkans in procenten;
• Het weer icoon.

De opgehaalde data wordt als volgt weergegeven:

De weergave van het dashboard kun je opmaken naar eigen smaak, maar dit dashboard is als volgt opgemaakt:

Voor het weer icoon hoeft niks te worden ingevuld. Deze wordt via een VBA-script weergegeven in de juiste cel.
Verder dient er een knop op het blad te worden toegevoegd waarmee we de macro eenvoudig kunnen aanroepen.

Stap 5 – het ophalen van JSON data via VBA

De code voor het ophalen van de data is vrij eenvoudig. We beginnen met het declareren van onze variabelen. Vervolgens wordt de subroutie DeleteAllIcons aangesproken. Deze verwijderd alle weer iconen op ons dahsboard. Zouden we dit niet doen, dan wordt elke keer als er weerdata wordt opgehaald een nieuw icoon over het vorige icoon geplaatst.

In de string variabele API_key voer je je eigen API key in welke je hebt ontvangen op Weatherapi.com.

Aan de variabele strURL kennen we vervolgens de waarde toe van de url om de gewenste parameters op te halen. READYSTATE_COMPLETE wordt ingesteld op 4. De eigenschap XMLHttpRequest.readyState retourneert de status waarin een XMLHttpRequest-client zich bevindt. De waarde 4 geeft aan dat de bewerking is voltooid.

Zodra we een http object hebben aangemaakt wordt een GET request verzonden. Indien de http bewerking is voltooid wordt de variabele MyResponse gevuld met de responsetekst (JSON data) vanuit het http object.

Vervolgens wordt de JSON parser aangesproken om de JSON data te ontleden. Dit doen we door de functie ParseJson(MyResponse) aan te roepen.

Nadat de JSON data is ontleed kunnen we onze gegevens hieruit extraheren.

De volledige response body van de JSON is vrij lang en daarom niet weergegeven. De response body is via de downloadlink onderaan deze pagina te downloaden.

De JSON is als volgt opgebouwd. In de root van de JSON staan de volgende objecten/arrays:

• location;
• current;
• forecast;
  • forecastday (array);
  • date;
  • date_epoch;
  • day
    • maxtemp_c;
    • maxtemp_f;
    • mintemp_c;
    • …
    • daily_chance_of_snow.

Zoals je ziet kun je JSON beschouwen als een mappenstructuur in de verkenner van Windows. Hierbij is forecastday een array (verzameling) van gegevens welke staat in het object forecast. In de verzameling forecastday staan weer verschillende objecten met namen en bijbehorende waarden.

Om alle datums uit de array forecastday te halen maken we gebruik van een For Each Loop. Zolang we aan de voorwaarde voldoen worden alle gevraagde waarden opgehaald en geplaatst in de juiste cellen.

Option Explicit

Sub getForecast()

Dim Item As Object
Dim READYSTATE_COMPLETE As Integer
Dim API_key As String
Dim http As Object, JSON As Object, i As Integer
Dim MyResponse As String
Dim strUrl As String
Dim strIconUrl As String

Call DeleteAllIcons

    API_key = "xxxxxxxxxxxxxxxxxxxxxxxxxxx"
    strUrl = "http://api.weatherapi.com/v1/forecast.json?key=" & API_key & "&q=Amsterdam&days=3&aqi=no&alerts=no"
    
    READYSTATE_COMPLETE = 4
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strUrl
    http.SetRequestHeader "Accept", "application/json"
    http.Send
    
    Do Until http.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    
    MyResponse = http.ResponseText
   
    Set JSON = ParseJson(MyResponse)
    
    For Each Item In JSON("forecast")("forecastday")
        Blad1.Cells(2, i + 1).Value = Item("date")
        Blad1.Cells(4, i + 1).Value = Item("day")("maxtemp_c")
        Blad1.Cells(5, i + 1).Value = Item("day")("mintemp_c")
        Blad1.Cells(6, i + 1).Value = Item("day")("totalprecip_mm")
        Blad1.Cells(7, i + 1).Value = (Item("day")("daily_chance_of_rain") / 100)
        strIconUrl = "http:" & Item("day")("condition")("icon")
        
        With Blad1.Pictures.Insert("http:" & Item("day")("condition")("icon"))
            With .ShapeRange
                .LockAspectRatio = msoTrue
            End With
            .Left = ActiveSheet.Cells(3, i + 1).Left
            .Top = ActiveSheet.Cells(3, i + 1).Top
            .Placement = 1
            .PrintObject = True
        End With

        i = i + 1
    Next Item
End Sub

Sub DeleteAllIcons()
Dim shape As Excel.shape

For Each shape In ActiveSheet.Shapes
    If shape.Name <> "CommandButton1" Then
        shape.Delete
    End If

Downloads

Kom je er met bovenstaande handleiding alleen niet uit, download dan het voorbeeldbestand of stel een vraag door een bericht achter te laten.