Rainfall radar in Excel with JSON
Table of contents
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:
- 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:
|Cell Range||Dates||Font style||Font size||drawing style||Text color||cell format||Type||Formula||Ride height|
|A2:C2||date||California||11||Default||Grey, Accent 3||Amended||dd-mm||15|
|A4:C4||Max. temperature||California||11||Default||Red||Amended||#,0 C||15|
|A5:C5||min. Temperature||California||11||Default||sky blue||Amended||#,0 C||15|
|A6:C6||Expected precipitation||California||11||Default||Dark blue||Amended||0.0 "mm"||15|
|A7:C7||Chance of rain||California||11||Default||Dark blue||Percentage||No decimals||15|
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:
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
If you can't figure it out with the above guide alone, download the sample file or ask a question by leaving a message.