Use Web services to automatically update Excel spreadsheets with online data

This post on the research of Web service functions is brought to you by Lee Bizek, Program Manager on the Excel team.

Have you ever wish, data from online resources in your Excel spreadsheets, or even Twitter RSS feeds such as such as stock quotes, weather, Bing search results include? With new Web Services features, now you can.

I used to coordinate anonymous editing in Excel Web , which some people outdoors to sleep in tents required collaboration with a group, a 20-person camping trip. Aware online weather updates being helped to prepare for the elements and also make predictions because we crabbing as part of adventure wanted to go high and low tides.

With Excel-2013 for the desktop, we moved weather and tide information from the Internet to Excel using the =WEBSERVICE(url)--function and the most beautiful is automatically updated information!

Learn how to use the WebService function, we will do 2 things:

A =WEBSERVICE(url)-Funktion that use to get the DataUse = FILTERXML (XML, XPath) function to extract a piece of data from the XML string

First, find a Web service. In this example with weather updates, go to http://www.wunderground.com/weather/api to create your free account. Fill the form, and click sign up for an API key.

To set up your API key, follow these steps:

Select either the Cumulus to plan or the anvil plan, depending on what you prefer. Choose which option you prefer for the addon history. Works in this example since we no historical information use either option.Select Developer. Note: The other options also work for this example, but there is a fee, the associated with them.Click on Refresh plan.Click on the top of the page, documentation.Found on the left navigation bar, titled API table of contents of data features heading, then click under the heading conditions.(You can go also http://www.wunderground.com/weather/api/d/docs?d=data/conditions)Scroll to the bottom of the page, then copy the URL in the box shown examples. (The URL format will look like this: http://api.wunderground.com/api/[APIKey]/conditions/q/CA/San_Francisco.json). The example URL will contain your unique API key.

Now have a unique API key, open your Excel spreadsheet file and follow these steps create the =WEBSERVICE(url)-Funktion for the current weather conditions:

In cell B5, type = WebService(URL). Then, replace the URL with the unique URL, including your API key, which you have just copied. Both sides of the URL add quotation marks. The format will look like this: to replace, then at the end of the URL "http://api.wunderground.com/api/[APIKey]/conditions/q/CA/San_Francisco.json" country and the city in the URL with a zip code .xml to. The formula in cell B5 should look like this: =WEBSERVICE("http://api.wunderground.com/api/[APIKey]/conditions/[ZipCode].xml") [apischluessel] your will be unique API key, and the [ZIP code] for the desired location weather updates. Press enter or return. The formula returns an XML string from the Web service.

You can use also the cell references in the Web service function to URL parameter, such as your zip code, for example, to update.  Here's how to set it up:

Add your API key in cell B1. Enter in the name fieldapischluessel to the cell to name a few.  In cell B2, type the zip code. Type in the name ZipCode field to mention the cell. Create your WEB SERVICE function with cell references. ("The formula should be in this format: = WEBSERVICE("http://api.wunderground.com/api/ "& APIkey &"/ conditions/q/ZipCode & ".xml") copy and paste the entire formula in cell B5. Update your zip code and then you will see the update of your WEB SERVICE function URL.

Now we have the information from the Web service in the Excel spreadsheet, we need to extract the data, what, who want we from the XML, including the name of the city and current temperature and current weather. To extract the data, follow these steps:

1. The =FILTERXML(B5,"//full")-Funktion type cell B8. This will give you the city name is associated with the zip code.

 

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

 

3. In cell D8, type = FILTERXML (B5, "/ / weather") to see the current weather conditions, such as light rain.

Our camping trip planning collaboration table looks now with the online weather updates:

Please keep in mind that WEB services are "non-volatile", which means that they only have to update:

A referenced cell is updated EditedThe entire workbook (CTRL + ALT + F9)

Remember that you can use this functionality for many different Web services over the Internet, which you can then analyze by using Excel.

--Lee Bizek, Excel program manager

Learn more about the WEB SERVICE function

Learn more about the FILTERXML function

Learn more about XPATH

View the original article here