Retrieve Real-time Weather Data from Weather.gov in Excel using FILTERXML

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/

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, FILTERXML( ) “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:

  1. The XML to parse (our large block of text)
  2. 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!

Subscribe to my mailing list.

Leave a Reply

%d bloggers like this: