Use Webservice functions to automatically update Excel 2013 spreadsheets with online data

This post on exploring Webservice functions is brought to you by Lee Bizek, a Program Manager in the Excel Team.

Have you ever wanted to incorporate data from online resources into your Excel spreadsheets, such as stock quotes, weather, Bing Search results or even Twitter feeds? With new Webservice functions, now you can.

I used anonymous editing in Web Excel to collaborate with a group to coordinate a 20-person camping trip that required some people to sleep outdoors in tents. Being aware of online weather updates helped us to prepare for the elements and also predict high and low tides because we wanted to go crabbing as part of the adventure.

With Excel 2013 for the desktop, we pulled the latest weather and tide information from the internet into Excel using the =WEBSERVICE(url) function--and the best part is the information updates automatically!

To learn how to use the Webservice function, we'll do 2 things:

• Use a =WEBSERVICE(url) function to get the data
• Use the =FILTERXML(xml, xpath) function to extract a single piece of data from the XML string

Use a =WEBSERVICE(url) function to get the data

First, find a web service. For this example with weather updates, go to http://www.wunderground.com/weather/api to create your free account. Complete the form, then click Signup for API Key.

To set up your API Key, follow these steps:

1. Select either the Cumulus Plan or the Anvil Plan, whichever you prefer.
2. Choose whichever option you prefer for the History add-on. Either option will work for this example because we're not using historical information.
3. Select Developer. Note: The other available options also will work for this example, but note that there is a fee associated with them.
4. Click Update Plan.
5. At the top of the page, click Documentation.
6. On the left navigation bar titled API Table of Contents, find the Data Features heading, then under that heading, click conditions. (You can also go to http://www.wunderground.com/weather/api/d/docs?d=data/conditions)
7. Scroll to the bottom of the page, then copy the URL shown in the box labeled Examples. (The URL format will look like this: http://api.wunderground.com/api/[APIKey]/conditions/q/CA/San_Francisco.json). The sample URL will include your unique API Key.

Now that you have a unique API Key, open your Excel spreadsheet and follow these steps to create the =WEBSERVICE(url) function for the current weather conditions:

1. In cell B5, enter =WEBSERVICE(url). Then replace url with the unique URL including your API Key that you copied a moment ago.
2. Add quotation marks to both sides of the URL. The format will look like this: "http://api.wunderground.com/api/[APIKey]/conditions/q/CA/San_Francisco.json"
3. Replace the state and city in the URL with a zip code, then add .xml to the end of the URL. The formula in cell B5 should look like this: =WEBSERVICE("http://api.wunderground.com/api/[APIKey]/conditions/[ZipCode].xml") The[APIKey] will be your unique API Key, and the [Zip Code] will be for the location where you want weather updates.
4. Press Enter or Return. The formula will return an XML string from the web service.

You can also use cell references in the Webservice function to update URL parameters, such as your zip code.  Here is how to set it up:

1. In cell B1, paste your API Key. In the Name Box, type APIkey to name the cell.  
2. In cell B2, enter the zip code. In the Name Box, type ZipCode to name the cell.
3. Create your WEBSERVICE function with cell references. The formula should be in this format:  =WEBSERVICE("http://api.wunderground.com/api/" & APIkey & "/conditions/q/" & ZipCode & ".xml") 
4. Copy and paste the entire formula into cell B5.
5. Update your zip code and then you will see the update to your WEBSERVICE Function URL.

Use the =FILTERXML(xml, xpath) function to extract single pieces of data from the XML string

Now that we have the information from the web service in the Excel spreadsheet, we need to extract the pieces of data we want out of the XML, including the name of the city and current temperature and current weather conditions. To extract the data, follow these steps:

1. In cell B8, enter the =FILTERXML(B5,"//full") function. This will give you the city name associated with the zip code.

2. In cell C8, enter =FILTERXML(B5, "//temp_f") to extract the current temperature in Fahrenheit.

3. In cell D8, enter =FILTERXML(B5, "//weather") to see the current weather condition, such as Light Rain.

With the online weather updates, now our camping trip planning collaboration spreadsheet looks like this:

A note on refreshing data

Please note that WEBSERVICE Functions are "non-volatile", which means they refresh only when:

• A referenced cell is edited
• The entire workbook is refreshed (CTRL+ ALT + F9)

Remember that you can use this functionality for many different web services over the internet that you can then analyze using Excel.

-- Lee Bizek, Excel Program Manager

Learn more about the WEBSERVICE function

Learn more about the FILTERXML function

Learn more about XPATH