Download the exercise file here.
Requires Excel 2013 or greater. These are new functions in Excel.
You can import .XML data straight from the web into Excel with the WEBSERVICE( ) and FILTERXML( ) functions.
In this lesson we will get real-time updates from Weather.gov. Data source: http://w1.weather.gov/xml/current_obs/
Looking to automate daily historical weather data collection? Check out this post.
View the YouTube tutorial here. Be sure to subscribe to my channel for more Excel tips.
The briefest introduction to XML
It’s crazy to think that even the most artistic websites (mine included, of course) are built on code.
If you’re running Chrome, you can peek into the code of any web page using Ctrl + U. Here, for example, is the insides of the weather report in Akron, Ohio:
URL: http://w1.weather.gov/xml/current_obs/KAKR.xml
Turns out this page is written in XML. Here’s an in-depth introduction to XML, but almost everything you need to know is in the name: Extensible Markup Language.
From Wikipedia: Extensible Markup Language (XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable.
As we will see in the example, XML provides a set of rules for reading and writing information onto a website.
Turns out Excel offers some built-in tools for utilizing this functionality. In particular, we will use two functions to retrieve real-time weather information from Weather.gov.
1. WEBSERVICE( )
From Microsoft documentation, WEBSERVICE( ) “returns data from a web service on the Internet or Intranet.”
It takes one argument — the URL of the page you want to parse. So, let’s put the Akron weather URL into Cell A1 and =WEBSERVICE(A1) into Cell A2. (Again, the URL is http://w1.weather.gov/xml/current_obs/KAKR.xml)
This is going to be a lot of information so to make it easier to read I wrapped the cell using Home | Wrap Text and zoomed out by holding down Control and spinning my mouse’s clickwheel down.
Enclosed in tags, we have some good information here. Weather description in <weather>. Temperature in F/C in <temperature_string>. The great thing about XML is the information comes consistently wrapped in these strings, so we just need a way to parse out that information and we will have a spreadsheet that updates the weather.
Make way for…
1. FILTERXML( )
From Microsoft: FILTERXML() returns specific data from the XML content by using the specified XPath. (Note: This function is not available on Excel for Mac!)
FILTERXML( ) takes two arguments:
- The XML to parse (our large block of text)
- The XPath to parse it.
XPath is a language used to describe locate elements and attributes in XML. This is how we can pull that desired information out of the huge block of text.
To do this, we will find the tags which enclose the information we want. Then we will place them without the tags and exactly as spelled in the XML somewhere in our workbook.
Then I will use FILTERXML(URL, XPath) to get our information. Note that XPath requires two backslashes “//” before the tag to work. So I add that text to the formula using &”//”.
This successfully parsed the location, observation and weather information for Akron. When new information is available on this web page, simply refresh your workbook for the latest weather.
Excel’s ability to work with real-time data from the web is incredibly powerful — not the least of which is you know when you’ll need an umbrella now!
I’m trying to auto-fill weather information that is based on a column date. for example: column 1 = 08/01/2018, column 2 = (weather recorded @ noon on the date listed in column 1). Does that make any sense? My team and I go from construction site to construction site within the same city, Irvine, CA. We fill out a Microsoft Form which two questions address weather conditions and temperatures. The 15 question form is exported to an .xls file. I also make changes to the list in excel and import back to sharepoint for adjustments and to eliminate the time it takes to create a custom list hoping to one day make active for our use. These reports are rarely completed on the same day as the report data.
If I can auto-fill the weather (column) per date (column) that would eliminate one more step and data entry for a bunch of construction guys who type with one finger, which in turn won’t make me crazy listening to, therefore, they can work more and type less. Do you feel my pain?
Please help. Thank you.
dptaylor@uci.edu
Hi Dan, Thanks for your question — if I understand you are looking to record historical weather info for a location? The current workbook is best for getting real-time information. Looks like there is not much of a go-to for historical records (https://www.quora.com/Where-can-you-get-a-spreadsheet-of-historical-weather-data). One thing that could possibly work is to schedule a macro to open the workbook and record whatever real-time information is being reported at the time (https://www.officenewb.com/single-post/2017/09/14/Schedule-Microsoft-Excel-Tasks-with-Windows-Task-Scheduler). Does that make sense at least in theory? Too bad it is not simpler!
Hi Dan, Perhaps you saw this update, getting a daily .csv file of hourly weather updates for a given location. I am using R instead of VBA for this. If you need any help getting it set up you are welcome to contact me: https://georgejmount.com/contact/.
Thank you George. sorry for the delayed response. This hustle and bustle world is passing me by. I will try it today.
Thanks again, Dan
Sure things Dan, keep us posted.
How can I do this same thing but with a 10 day forecast?
Hello Kim, Thank you for reading. Unfortunately this service only includes real-time weather. In general accessing forecasted data through Excel is going to be a paid service. For that I recommend the Weather for Excel tools by Visual Crossing (https://www.visualcrossing.com/excel-weather).
You can get a free trial there and use promocode `GEORGEJMOUNT` to get 10% off the subscription. This is a seven-day forecast, if you do need 10 probably going to a Weather.com or similar’s website and pulling into Excel manually will be the best bet as these sites are often hard to pull data into Excel without paying.
Please note that I am an affiliate of Visual Crossing and will receive a portion of any sales made using my promo code.
Hi George,
How could we do the same thing for Canadian cities?
Thank you.
Hi Alex, thank you for reading. I will try to get a full post on this but here is what I have for now. Here is the readme for the Canadian Government’s XML weather service.
Based on that doc, I have set up a similar workbook as the example which will work for Canadian cities here. To change the site location you will look up that City code here. Also note that you will want to change the two-letter Province code when you change the City code.
Hope that helps, feel free to follow up on anything not clear. Unlike the Weather.gov files these do include a weather forecast — there is a lot of good information here.
Hi Georges,
When I try to use your link for city code, I get:
<>
Is there an error in the URL?
Thank you.
Ooops! It did not take the “copy paste”
I meant that I get <>
Sorry, it doesn’t like the quote signs I was using:
Again, the error is: “Page not found (HTTP 404)”
Hi Alex, Try this link: http://dd.weather.gc.ca/citypage_weather/docs/site_list_en.csv It should also be fixed on the above link.
I will also note that you can access the same City codes in the French language using this link: http://dd.meteo.gc.ca/citypage_weather/docs/site_list_provinces_fr.csv. Changing any City lookup code suffix from `_e` to `_f` will change all output to French language, for example from the docs:
“`
Examples of file name:
* s0000001_e.xml – English citypage weather XML for Athabasca
* s0000001_f.xml – French citypage weather XML for Athabasca
“`
I saw you write Georges so figured maybe you might want French! 🙂
Hi George,
Yes, you are quite right, I was used to write “Georges” in French ! Sorry about that.
Actually, it was to my benefit, because I need the weather results both in English AND in French 🙂
It works fine in English. I am going to do it in French now. The fact that we also have forecast is a big plus.
Thank you very much for your time and your help. It is quite appreciated.
Best regards.
Alex
Ce n’est pas grave Alex, I took some French in school :). Great, I am glad it is working for you. Drop me a line if you have more questions on it, or if you come up with something really cool you are welcome to do a guest post. Take care.
Merci George(s) !! 🙂
It is working for me, but I do have a question: I am not XML (not even HTML) knowledgeable. I used to be an Ms-Access and Ms-Excel developer (VBA, etc), retired now, and I kind of “gestimated” with a certain success, how to use “FILTERXML” based on your example.
But where I stumbled, is when I found many “forecast” and “/forecast” tags. It will always go to the results concerning the first “forecast” tag. How do you tell excel to consider the second one, the third one, etc?
Sorry if my question is a bit trivial for specialists like yourself…
Thank you in advance.
Alex
Very cool, thank you for sharing and for checking out the blog, Alex. Yes, there is a lot of data coming from this weather service, I hope to do a complete blog post/tutorial on it in the future. Good question on how to index the “nodes” to get the second, third forecast, etc.
You can use bracket notation to index XML notes in `FILTERXML()`. So, in this weather service, `//forecastGroup//forecast[1]` will pull the first forecast, `//forecastGroup//forecast[2]` the second, and so forth. I have mocked up an example of this here, pulling the forecast description for each available time period. Make sense? Please do not hesitate to keep the conversation going if you have more questions. This is dense material but fun and useful. I wish the American XML weather service had this much data!
Hi George,
Sorry for the late response, I’ve been busy lately.
Just want to let you know that forecast[2] etc… did work fine for me.
Thank you so much for your help. Very appreciated.
Take care.