Rainfall radar in Excel with JSON

The possibilities in Excel are virtually unlimited. You can even make your own rainfall radar and process the data in a sheet. We will do this using JSON (JavaScript Object Notation). To retrieve the weather data we use the free API of Weatherapi.com.

Step 1 – create an account

Create a free account on Weatherapi.com for a personal API key. With the free account you can make 1,000,000 API calls per month and receive forecasts up to three days in advance.

Step 2 – Install VBA-JSON

Download and install VBA-JSON from Tim Hall via GitHub. Tim Halll's GitHub page has a step-by-step installation guide on how to install it.

Step 3 – what data can you retrieve

The following items can be retrieved through the free API of Weatherapi.com.

• Current weather conditions;
• three-day forecast;
• Astronomical data.

Step 4 – Create Weather Dashboard

We start by creating the dashboard. This visually displays the data collected by us in a neat manner. Weatherapi.com's documentation page clearly states which parameters can be retrieved. In our example we limit ourselves to the following data:

• Date;
• Maximum expected temperature in degrees Celsius;
• Minimum expected temperature in degrees Celsius;
• Total expected precipitation in millimeters;
• Chance of rain in percentages;
• The weather icon.

The retrieved data is displayed as follows:

You can format the display of the dashboard to your own taste, but this dashboard is formatted as follows:

Nothing needs to be entered for the weather icon. This will be displayed in the correct cell via a VBA script.
Furthermore, a button has to be added on the sheet with which we can easily call the macro.

Step 5 – retrieving JSON data via VBA

The code for retrieving the data is quite simple. We start by declaring our variables. Then the subroutine DeleteAllIcons addressed. This removes all weather icons on our dashboard. If we wouldn't do this, every time weather data is retrieved, a new icon is placed over the previous icon.

In the string variable API_key enter your own API key which you received on Weatherapi.com.

To the variable strURL we then assign the value of the url to retrieve the desired parameters. READYSTATE_COMPLETE is set to 4. The XMLHttpRequest.readyState property returns the state in which an XMLHttpRequest client is located. A value of 4 indicates that the operation has been completed.

As soon as we have created an http object, a GET request is sent. When the http operation is completed, the variable MyResponse filled with the response text (JSON data) from the http object.

Then the JSON parser is called to parse the JSON data. We do this by using the function ParseJson(MyResponse) to invoke.

After the JSON data has been parsed, we can extract our data from it.

The full response body of the JSON is quite long and therefore not displayed. The response body is via the download link download at the bottom of this page.

The JSON is structured as follows. The root of the JSON contains the following objects/arrays:

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

As you can see, you can think of JSON as a folder structure in Windows explorer. Here is forecast day an array (set) of data contained in the object forecast. In the collection forecast day There are again different objects with names and corresponding values.

To remove all dates from the array forecast day we use a For Each Loop. As long as we meet the condition, all requested values are retrieved and placed in the correct cells.

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_keyxxxxxxxxxxxxxxx = "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") Sheet1.Cells(2, i + 1).Value = Item("date") Sheet1.Cells(4, i + 1).Value = Item("day")("maxtemp_c" ) Sheet1.Cells(5, i + 1).Value = Item("day")("mintemp_c") Sheet1.Cells(6, i + 1).Value = Item("day")("totalprecip_mm") Sheet1 .Cells(7, i + 1).Value = (Item("day")("daily_chance_of_ra in") / 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

If you can't figure it out with the above guide alone, download the sample file or ask a question by leaving a message.