Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff. 		Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat. 		Nou pe simpatie:
loryci_2006 la Simpatie.ro
Femeie
21 ani
Bucuresti
cauta Barbat
21 - 49 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Parse Weather API Response using VBA to Range [JSON] Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2186
Found an Excel weather app tutorial online (see source 1). Wanted to test that app in my own Excel and for my own city, more exactly to see how I can extract data from API response results for other purposes and this seemed like great exercise.

Source 1 refers to Source 3 as a site that offers free weather API. So I went to Source 3 and loaded a page for my city.

I went to the API section of Source 3, then to API doc under Current Weather Data to find out that an API call can be made by using the following link with parameters:

https://openweathermap.org/data/2.5/weather?lat={lat}&lon={lon}&units=metric&mode=xml&appid={API key}


So I needed the latitude and longitude and API key for my home town. Went back to my town's current weather page and since I'm using Chrome, I right clicked the page and clicked Inspect. Clicked Network, in the search field I typed the word "lat" without quotes, and then I refreshed the page and got a link containing lat, lon and appid - the 3 parameters I needed in the above link.

Typed the obtained data into the link above to obtain my unique link that when ran in a browser address bar would return the following information:

{"coord":{"lon":24.5001,"lat":47.1332},"weather":[{"id":803,"main":"Clouds","description":"broken clouds","icon":"04n"}],"base":"stations","main":{"temp":8.88,"feels_like":7.55,"temp_min":8.88,"temp_max":8.88,"pressure":1006,"humidity":85,"sea_level":1006,"grnd_level":963},"visibility":10000,"wind":{"speed":2.42,"deg":65,"gust":2.53},"clouds":{"all":60},"dt":1650653872,"sys":{"country":"RO","sunrise":1650597692,"sunset":1650647951},"timezone":10800,"id":684657,"name":"Bistriţa","cod":200}


And I needed to extract the info I colored in aqua into Excel cells.

Below are the requirements and the code to extract said data into cells B1, B2 and B3 of my worksheet.

Requirements:
1. Download the archive VBA-JSON-master.zip from Source 2
2. Import the file JsonConverter.bas from that archive to VBA
3. In VBA, go to Tools - References and select Microsoft Scripting Runtime

Since I want to show temperatures in degrees Celsius, I simply selected cells B2 and B3 and went to Custom number formats and set this custom format (source 4): +###°C;-###°C;_+0°C

VBA Code

Public Sub Grab_Temp()

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

Const sURL As String = "https://openweathermap.org/data/2.5/weather?lat=YOURLATITUDE&lon=YOURLONGITUDE&units=metric&appid=YOURAPPID&mode=xml"
http.Open "GET", sURL, False
http.Send

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

' Debug.Print http.responseText

ActiveWorkbook.Worksheets("Sheet1").Cells(1, 2) = jsonResponse("name")
ActiveWorkbook.Worksheets("Sheet1").Cells(2, 2) = jsonResponse("main")("temp")
ActiveWorkbook.Worksheets("Sheet1").Cells(3, 2) = jsonResponse("main")("feels_like")

End Sub


This code returned the following in cells B1, B2 and B3:
Bistriţa
+9°C
+8°C

Note: with the APPID obtained through the method above I can extract temperatures for various cities around the world by knowing their latitude and longitude coordinates.

Sources:
1.
2.
3.
4.
5. - there are several more API link options in this page
6. - you can download various weather lists, including a JSON file with all latitudes and longitudes of cities in their database:

_______________________________________
pus acum 2 ani
   
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2186
A more advanced approach is to show weather for today but also for the next 8 days.

This is available with a link that is a little changed to show more information. Note the changed string in aqua (instead of the word weather):

https://openweathermap.org/data/2.5/onecall?lat={lat}&lon={lon}&units=metric&mode=xml&appid={API key}


Now the data obtained is much larger, but I'm going to trim it to what we need, that is the weather from NOW and the DAILY weather in the next 8 days. I am also going to add some new empty lines to separate today, from what was in the middle that I removed and from daily data.
In the excerpt below I will mark the data that I need to extract in color.


{"lat":47.1332,"lon":24.5001,"timezone":"Europe/Bucharest","timezone_offset":10800,"current":{"dt":1654364279,"sunrise":1654309737,"sunset":1654366316,"temp":17.39,"feels_like":17.48,"pressure":1016,"humidity":88,"dew_point":15.38,"uvi":0,"clouds":87,"visibility":10000,"wind_speed":0.91,"wind_deg":16,"wind_gust":0.93,"weather":[{"id":804,"main":"Clouds","description":"overcast clouds","icon":"04d"}]},

"minutely": .... REMOVED BY MRRRR ....

"hourly": .... REMOVED BY MRRRR ....

"daily":

[

{"dt":1654336800,"sunrise":1654309737,"sunset":1654366316,"moonrise":1654323480,"moonset":1654293000,"moon_phase":0.15,"temp":{"day":23.94,"min":14.25,"max":23.94,"night":17.47,"eve":18.41,"morn":16.12},"feels_like":{"day":24.11,"night":17.54,"eve":18.55,"morn":16.11},"pressure":1018,"humidity":66,"dew_point":16.83,"wind_speed":3.09,"wind_deg":71,"wind_gust":5.17,"weather":[{"id":501,"main":"Rain","description":"moderate rain","icon":"10d"}],"clouds":93,"pop":1,"rain":7.15,"uvi":6.8},

{"dt":1654423200,"sunrise":1654396108,"sunset":1654452766,"moonrise":1654413840,"moonset":1654381260,"moon_phase":0.18,"temp":{"day":21.72,"min":15.22,"max":23,"night":15.22,"eve":22.01,"morn":16.77},"feels_like":{"day":21.91,"night":15.17,"eve":22.2,"morn":16.96},"pressure":1016,"humidity":75,"dew_point":16.53,"wind_speed":3.36,"wind_deg":305,"wind_gust":4.51,"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}],"clouds":94,"pop":0.92,"rain":5.23,"uvi":6.6},

{"dt":1654509600,"sunrise":1654482481,"sunset":1654539213,"moonrise":1654504380,"moonset":1654469100,"moon_phase":0.21,"temp":{"day":21.93,"min":12.73,"max":23.49,"night":14.38,"eve":21.91,"morn":14.31},"feels_like":{"day":21.85,"night":14.04,"eve":21.98,"morn":14.22},"pressure":1015,"humidity":64,"dew_point":14.53,"wind_speed":4.65,"wind_deg":99,"wind_gust":6.05,"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}],"clouds":11,"pop":0.96,"rain":8.38,"uvi":7.52},

{"dt":1654596000,"sunrise":1654568857,"sunset":1654625659,"moonrise":1654594920,"moonset":1654556760,"moon_phase":0.25,"temp":{"day":22.43,"min":12.06,"max":22.89,"night":16.71,"eve":22.89,"morn":14.1},"feels_like":{"day":22.16,"night":16.55,"eve":22.8,"morn":13.76},"pressure":1016,"humidity":55,"dew_point":12.63,"wind_speed":2.48,"wind_deg":62,"wind_gust":3.4,"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}],"clouds":2,"pop":0.22,"rain":0.13,"uvi":7.43},

{"dt":1654682400,"sunrise":1654655235,"sunset":1654712102,"moonrise":1654685520,"moonset":1654644240,"moon_phase":0.27,"temp":{"day":18.69,"min":13.89,"max":19.63,"night":16.84,"eve":19.63,"morn":15.08},"feels_like":{"day":18.55,"night":16.51,"eve":19.5,"morn":14.47},"pressure":1014,"humidity":74,"dew_point":13.52,"wind_speed":2.15,"wind_deg":106,"wind_gust":4.39,"weather":[{"id":804,"main":"Clouds","description":"overcast clouds","icon":"04d"}],"clouds":100,"pop":0,"uvi":6.71},

{"dt":1654768800,"sunrise":1654741615,"sunset":1654798544,"moonrise":1654776240,"moonset":1654731720,"moon_phase":0.31,"temp":{"day":21.73,"min":14.04,"max":23.5,"night":18.09,"eve":22.91,"morn":14.04},"feels_like":{"day":21.6,"night":18.02,"eve":22.98,"morn":13.59},"pressure":1011,"humidity":63,"dew_point":14,"wind_speed":1.89,"wind_deg":117,"wind_gust":2.28,"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}],"clouds":38,"pop":0.86,"rain":2.2,"uvi":7},

{"dt":1654855200,"sunrise":1654827998,"sunset":1654884983,"moonrise":1654867200,"moonset":1654819200,"moon_phase":0.34,"temp":{"day":15.21,"min":12.02,"max":17.54,"night":12.02,"eve":13.25,"morn":14.35},"feels_like":{"day":15.21,"night":11.68,"eve":13.01,"morn":14.22},"pressure":1011,"humidity":93,"dew_point":13.64,"wind_speed":5.86,"wind_deg":281,"wind_gust":11.47,"weather":[{"id":501,"main":"Rain","description":"moderate rain","icon":"10d"}],"clouds":100,"pop":1,"rain":12.05,"uvi":7},

{"dt":1654941600,"sunrise":1654914384,"sunset":1654971421,"moonrise":1654958400,"moonset":1654906800,"moon_phase":0.38,"temp":{"day":17.17,"min":9.39,"max":18.3,"night":13.92,"eve":16.86,"morn":9.39},"feels_like":{"day":16.72,"night":13.64,"eve":16.59,"morn":8.96},"pressure":1015,"humidity":68,"dew_point":10.71,"wind_speed":4.3,"wind_deg":328,"wind_gust":4.93,"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}],"clouds":95,"pop":0.49,"rain":0.57,"uvi":7}

],

"alerts":[{"sender_name":"Administrația Națională de Meteorologie","event":"awareness_type=3, awareness_level=2","start":1654333200,"end":1654387200,"description":"Thunderstorms, severe wind gusts and hail. Water amounts will exceed 20...30 mm and sparsely 40 mm.","tags":[]}]}


Now, between the [ ] in daily, there are 8 items which we are going to loop through by using a variable called i.

The link under Const sURL As String should be Const sURL As String = "https://openweathermap.org/data/2.5/onecall?lat=YOURLATITUDE&lon=YOURLONGITUDE&units=metric&appid=YOURAPPID&mode=xml"

In the code from post #1, after this line: ' Debug.Print http.responseText add the following code that will extract the whole data I made in color in the previous quote:

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
    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

On Error GoTo ErrorHandling
ActiveWorkbook.Worksheets("Sheet1").Cells(9 + i + 1, 2) = jsonResponse("alerts")(1)("description")

Exit Sub

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

End Sub


Below is a picture that shows how my sheet looks like.
In B1 I have a dropdown list for countries, in B2 for cities (which is dependent on country).
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 Const sURL As String = to G2.
I have 2 more sheets beside this: one contains the data from the current city list in the JSON file, the other contains the lists for the dependent dropdowns in B1 and B2.



_______________________________________
pus acum 1 an
   
Pagini: 1  

Mergi la