Excel – Extract Weather Information to Excel Ranges using VBA

Lately I’ve tested some web scraping of weather information from a website that offers free API (Application Programming Interface) in JSON format that can be parsed to Excel with some simple VBA code. Simple as in you don’t really need to know much VBA in order to adapt the code for your needs. I will try to explain this as best as possible, as always.

Requirements:

a. Download the archive called VBA-JSON-master.zip from this github page. Direct link here: https://github.com/VBA-tools/VBA-JSON/archive/refs/heads/master.zip

b. Extract the archive to a folder of your choice

c. Open Excel and go to Visual Basic (VBA) in the Developer ribbon (or press ALT+F11)

There are 2 options to approach this:

c.1. Save your current Excel file as a Microsoft Excel Macro-Enabled file (.XLSM extension) and do the next steps in that file – weather extraction will work exclusively in this file

Advantage: you can pass that file to another person and it will work for them as well

or

c.2. Use the PERSONAL.XLSB file for everything – weather extraction can work in any file

Disadvantage: it’s more complicated to make it work on another person’s computer

d. In VBA, right click under the file you chose at letter c, and select Import File

e. Navigate to the VBA-JSON-master folder you extracted at letter b

f. Import the JsonConverter.bas file

 

Now, the method itself:

1. Make a new Module and name it, for example, Weather_Extraction.

2. Click in that empty module and go to Tools – References, look for Microsoft Scripting Runtime and select it, then click OK.

3. Find your town’s latitude and longitude, and also your unique appid:

3.1. Navigate to https://openweathermap.org/

3.2. Search for your city, for example Florence, IT and click it so the page loads

3.3. To the right of the Search field, choose either Metric or Imperial units

3.4. If you use Chrome, right click the page and select Inspect / If you use Firefox, right click the page and select Inspect Element

3.5. Under Inspect / Inspect Element click on the Network tab, then reload / refresh the page

3.6. In Chrome, type the string onecall into the Filter field / In Firefox, at the bottom of the Network tab click on Other and look for the json type that contains onecall

3.7. Right click that line and choose Open in new tab, for Florence the link should be: https://openweathermap.org/data/2.5/onecall?lat=43.7667&lon=11.25&units=metric&appid=YOUR_APP_ID

Replace lat and lon values with the ones corresponding to your town.

Under YOUR_APP_ID should be the 32 character string specific to you. Whatever and whenever you check weather on this site, the appid should be the same for you, on your current computer.

3.8. The link will show a bunch of data stored in JSON format. If you look at it you will notice it shows some information about your location (lat, lon, timezone), then will start and show moments of the day/week (dt, probably meaning daytime – those are seconds) together with weather information for that moment of the day/week (temp, feels_like, pressure, humidity etc.)

4. Regardless of the option you chose between c.1 and c.2, in VBA in the Weather_Extraction module, paste the following code. Upon showing you the code, I will explain how to understand the lines that extract the information from the JSON data (specifically the ones in red, blue and pink). The ActiveWorkbook.Worksheets(“Sheet1”).Cells(X, 2) will insert the data in various rows in column 2.

Sub Grab_Temperature()

Dim http As Object
Set http = CreateObject(“MSXML2.XMLHTTP”)

Dim sURL As String
sURL = “https://openweathermap.org/data/2.5/onecall?lat=43.7667&lon=11.25&units=metric&appid=YOUR_APP_ID“
http.Open “GET”, sURL, False
http.Send

Dim jsonResponse As Dictionary
Set jsonResponse = JsonConverter.ParseJson(http.responseText)

ActiveWorkbook.Worksheets(“Sheet1”).Cells(3, 2) = jsonResponse(“current”)(“temp”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(4, 2) = jsonResponse(“current”)(“feels_like”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(5, 2) = jsonResponse(“current”)(“weather”)(1)(“main”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(6, 2) = jsonResponse(“current”)(“weather”)(1)(“description”)

For i = 1 To 8 ‘ COMMENT: since the information is shown for 8 days in advance
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i, 2) = jsonResponse(“daily”)(i)(“temp”)(“day”) ‘ COMMENT: this is day temperature; there are more options like night, evening, morning
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i, 3) = jsonResponse(“daily”)(i)(“weather”)(1)(“description”) ‘ COMMENT: column 3 here, to the right of temp
Next i ‘ COMMENT: i is now at its last value from the for loop, which is 8; from now on i = 8

On Error GoTo ErrorHandling ‘ COMMENT: for some cities (like Florence, today) there will be no alerts in JSON, so this ErrorHandling snippet prevents an error in the code
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i + 1, 2) = jsonResponse(“alerts”)(1)(“description”) ‘ COMMENT: since Florence has no alert, can’t show this in JSON; but code should remain like this

Exit Sub

ErrorHandling:
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i + 1, 2) = “No alerts” 

End Sub

5. Now to see the raw JSON data we used to extract all the information above. I used Notepad to look through it, and shortened it a bit removing the minutely and hourly data from JSON, as we don’t need them. I’ve also arranged the data in such a way that it’s easier to follow. This data in Notepad is needed only for study purposes, so we know which JSON tags/names we should address to get their data. I’ve also highlighted the data we extracted using the code above.

{

“lat”:43.7667,
“lon”:11.25,
“timezone”:”Europe/Rome”,
“timezone_offset”:7200,
“current”:{“dt”:1654375423,”sunrise”:1654313693,”sunset”:1654368721,“temp”:26.18,“feels_like”:26.18,”pressure”:1017,”humidity”:66,”dew_point”:19.33,”uvi”:0,”clouds”:0,”visibility”:10000,”wind_speed”:2.06,”wind_deg”:220,
“weather”:[{“id”:800,“main”:”Clear”,“description”:”clear sky”,”icon”:”01n”}]},

“minutely”: … REMOVED …
“hourly”: … REMOVED …
“daily”:

[

{“dt”:1654340400,”sunrise”:1654313693,”sunset”:1654368721,”moonrise”:1654327620,”moonset”:1654295400,”moon_phase”:0.15,“temp”:{“day”:35.12,”min”:20.79,”max”:37.55,”night”:26.18,”eve”:31.13,”morn”:22.17},”feels_like”:{“day”:33.44,”night”:26.18,”eve”:30.8,”morn”:21.54},”pressure”:1017,”humidity”:22,”dew_point”:9.62,”wind_speed”:5.05,”wind_deg”:273,”wind_gust”:6.83,”weather”:[{“id”:803,”main”:”Clouds”,”description”:”broken clouds”,”icon”:”04d”}],”clouds”:76,”pop”:0,”uvi”:9.17},

{“dt”:1654426800,”sunrise”:1654400069,”sunset”:1654455165,”moonrise”:1654417860,”moonset”:1654383720,”moon_phase”:0.18,“temp”:{“day”:31.64,”min”:21.86,”max”:34.22,”night”:21.92,”eve”:28.62,”morn”:23.11},”feels_like”:{“day”:31.45,”night”:22.02,”eve”:28.31,”morn”:22.63},”pressure”:1016,”humidity”:38,”dew_point”:15.02,”wind_speed”:6.4,”wind_deg”:275,”wind_gust”:9.2,”weather”:[{“id”:804,”main”:”Clouds”,”description”:”overcast clouds”,”icon”:”04d”}],”clouds”:92,”pop”:0,”uvi”:9.33},

{“dt”:1654513200,”sunrise”:1654486447,”sunset”:1654541608,”moonrise”:1654508220,”moonset”:1654471800,”moon_phase”:0.21,“temp”:{“day”:31.61,”min”:18.91,”max”:32.96,”night”:23.03,”eve”:28.75,”morn”:20.48},”feels_like”:{“day”:30.49,”night”:23.16,”eve”:28.81,”morn”:20.67},”pressure”:1014,”humidity”:31,”dew_point”:11.89,”wind_speed”:5.6,”wind_deg”:243,”wind_gust”:5.02,”weather”:[{“id”:801,”main”:”Clouds”,”description”:”few clouds”,”icon”:”02d”}],”clouds”:14,”pop”:0,”uvi”:9.16},

{“dt”:1654599600,”sunrise”:1654572828,”sunset”:1654628049,”moonrise”:1654598580,”moonset”:1654559640,”moon_phase”:0.25,“temp”:{“day”:30.62,”min”:19.17,”max”:31.28,”night”:21.79,”eve”:25.34,”morn”:20.28},”feels_like”:{“day”:30.18,”night”:21.88,”eve”:25.29,”morn”:20.45},”pressure”:1010,”humidity”:38,”dew_point”:13.98,”wind_speed”:5.57,”wind_deg”:236,”wind_gust”:5.43,”weather”:[{“id”:500,”main”:”Rain”,”description”:”light rain”,”icon”:”10d”}],”clouds”:49,”pop”:0.49,”rain”:0.23,”uvi”:8.31},

{“dt”:1654686000,”sunrise”:1654659210,”sunset”:1654714488,”moonrise”:1654689000,”moonset”:1654647300,”moon_phase”:0.28,“temp”:{“day”:26.81,”min”:19.08,”max”:27.54,”night”:21.15,”eve”:24.3,”morn”:21.13},”feels_like”:{“day”:27.08,”night”:21.23,”eve”:24.27,”morn”:21.02},”pressure”:1011,”humidity”:47,”dew_point”:13.78,”wind_speed”:4.82,”wind_deg”:5,”wind_gust”:6.01,”weather”:[{“id”:500,”main”:”Rain”,”description”:”light rain”,”icon”:”10d”}],”clouds”:37,”pop”:0.45,”rain”:0.23,”uvi”:7.08},

{“dt”:1654772400,”sunrise”:1654745595,”sunset”:1654800925,”moonrise”:1654779540,”moonset”:1654734900,”moon_phase”:0.31,“temp”:{“day”:30.52,”min”:19.21,”max”:30.52,”night”:22.42,”eve”:26.7,”morn”:22.23},”feels_like”:{“day”:29.83,”night”:22.44,”eve”:26.99,”morn”:22.15},”pressure”:1012,”humidity”:36,”dew_point”:12.92,”wind_speed”:5.89,”wind_deg”:25,”wind_gust”:6.6,”weather”:[{“id”:801,”main”:”Clouds”,”description”:”few clouds”,”icon”:”02d”}],”clouds”:24,”pop”:0.41,”uvi”:8.33},

{“dt”:1654858800,”sunrise”:1654831982,”sunset”:1654887361,”moonrise”:1654870260,”moonset”:1654822560,”moon_phase”:0.35,“temp”:{“day”:33.39,”min”:20.24,”max”:33.43,”night”:24.24,”eve”:29.46,”morn”:24.47},”feels_like”:{“day”:31.93,”night”:24.13,”eve”:28.89,”morn”:24.25},”pressure”:1016,”humidity”:26,”dew_point”:10.89,”wind_speed”:4.37,”wind_deg”:13,”wind_gust”:5.37,”weather”:[{“id”:801,”main”:”Clouds”,”description”:”few clouds”,”icon”:”02d”}],”clouds”:16,”pop”:0,”uvi”:9},

{“dt”:1654945200,”sunrise”:1654918371,”sunset”:1654973794,”moonrise”:1654961280,”moonset”:1654910340,”moon_phase”:0.38,“temp”:{“day”:35.17,”min”:21.34,”max”:35.17,”night”:24.6,”eve”:29.95,”morn”:25.07},”feels_like”:{“day”:33.8,”night”:24.6,”eve”:29.12,”morn”:24.73},”pressure”:1016,”humidity”:24,”dew_point”:10.84,”wind_speed”:5.16,”wind_deg”:259,”wind_gust”:3.79,”weather”:[{“id”:800,”main”:”Clear”,”description”:”clear sky”,”icon”:”01d”}],”clouds”:3,”pop”:0.08,”uvi”:9}

]

}

Now let me explain the few lines of code and how they are made:

“current” is followed only by curly bracket { (and not by square bracket), which means the data is coming straight up and is addressed simply as in the following 2 rows below:

ActiveWorkbook.Worksheets(“Sheet1”).Cells(3, 2) = jsonResponse(“current”)(“temp”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(4, 2) = jsonResponse(“current”)(“feels_like”)

Under “current”, before the closing curly bracket, there is also “weather” which is followed by a straight bracket and a curly bracket. The number of curly bracket pairs inside the square brackets are, let’s call them items. Under “weather” there is only 1 pair of curly brackets, therefore 1 item, hence when we address “weather” we add the 1 item after it – (“weather”)(1), as shown below in pink, and then we address the tag/name containing the data we need to extract.

ActiveWorkbook.Worksheets(“Sheet1”).Cells(5, 2) = jsonResponse(“current”)(“weather”)(1)(“main”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(6, 2) = jsonResponse(“current”)(“weather”)(1)(“description”)

Under “daily” you can notice the square bracket opening, then a few sets of curly brackets for the first dt, then 7 more dt to a total of 8 (weather info for 8 days). Instead of writing a line of code for each of the 8 items, stating them as (1), (2) etc., we run a For loop and pass through them to obtain the “temp” of “day”.

“weather”, similar as above, has square brackets which contain 1 pair of curly brackets, therefore for “description” we need to add (“weather”)(1) as shown below in pink

For i = 1 To 8
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i, 2) = jsonResponse(“daily”)(i)(“temp”)(“day”)
ActiveWorkbook.Worksheets(“Sheet1”).Cells(9 + i, 3) = jsonResponse(“daily”)(i)(“weather”)(1)(“description”)
Next i

 

That’s it, I think it’s fairly simple. Just copy the JSON from the link and do a similar data evaluation as shown at no 5, and it should easy to comb through the data and extract what you need.

 

Below is a picture of how my sheet looks like. I made a .XLSM separate file I use for weather extraction.

In B1 I have a dropdown list for countries, in B2 for cities (which is dependent on country).

I downloaded the file shown in cell H4 and made 2 more sheets beside the one shown: first contains the data from the city list by country from the JSON file in H4, the second contains the lists I created for the dependent dropdowns in B1 and B2, from the JSON file data of cities.
I added the link to open in G2, and it’s a dynamic link based on Lat and Lon in cells C7 and C8. So my sURL = the value of G2. This way I can check the weather for any city dynamically just by pressing the green button. For this to work, I made a simple SUBSTITUTE formula in C7 and C8, to replace comma with dot =SUBSTITUTE(B7;”,”;”.”)

Pressing the blue button will show weather for today only – it is accessing link in G1.