Automatically plot multiple pieces of online data in your Excel spreadsheet

AppId is over the quota
AppId is over the quota

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

If you’ve ever wanted to incorporate data from online resources into Excel spreadsheets, now you can with the new Webservice function. With this new feature, you can add information such as stock quotes, weather, Bing Search results or even Twitter feeds to your spreadsheets—and you can make the information update automatically. In this post, we'll discuss some of the more advanced aspects of the Webservice function.

I’m starting with a spreadsheet I created to organize a group camping trip that includes the latest weather information from the internet. In this example, 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. I’ll show you how to take this spreadsheet a step further to use the =FILTERXML(xml, xpath) to extract multiple records of data from the XML string with an array. This will allow you to automatically plot multiple pieces of data in your spreadsheet.  

Note: Before you start the exercise, be sure to review how to use Webservice functions to automatically update Excel 2013 spreadsheets with online data so that you know how to:

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

We’re going to set up a Webservice function call to the URL for the 10-day weather forecast and then pull out the details from the XML string. To do this, follow these steps: 

1. Open the example file Kamping 2013 – Start and Complete Sample Files.zip located at the end of this post (click the icon next to Attachment).

2. Go to http://www.wunderground.com/weather/api/d/docs?d=data/forecast10day to get the URL for the 10-day forecast. At the bottom of the page, you’ll see an example URL.

3. Copy the example URL and paste it into your Webservice function in cell B11 of your spreadsheet. (Note: Be sure to replace the API key and location information in the URL to the cell reference we used to incorporate data from an external source so that you can control the API key and zip code for both Webservice functions in the same place.)

4. In your Excel spreadsheet, select cell range B14:B23.

5. Click the Formula Editor Box and then type: 

=DATEVALUE(CONCATENATE(FILTERXML(B11, "//date//month"), "/", FILTERXML(B11, "//date//day")))

In this example, I am pulling out the month and day, CONCATENATING them together and then converting the string to a DATEVALUE.

6. Press CTRL + SHIFT + ENTER to execute the array. This action adds the brackets { } that indicate it is an array. Find more information about array formulas here.

7. Select cell range C14:C23, then type: =FILTERXML(B11, "//high//fahrenheit")

8. Press CTRL + SHIFT + ENTER for the high temperature.

9. Select cell range D14:D23, then type: =FILTERXML(B11, "//low//fahrenheit")

10. Press CTRL + SHIFT + ENTER for the low temperature.

11. Select cell range E14:E23, then type: =FILTERXML(B11, "//conditions")

12. Press CTRL + SHIFT + ENTER for the conditions.

 

Note: To edit the formula, select all of the cells in the array, then click the Formula Editor. Press CTRL + SHIFT + ENTER to execute the new formula.

WEBSERVICE Functions are “non-volatile”, which means they refresh only when:

A referenced cell is edited

The entire workbook is refreshed (CTRL + ALT + F9)

Some text strings include spaces or non-URL-friendly characters instead of URL-friendly characters such as a zip code. You can still use the URL in the WEBSERVICE function, but it’s best to encode the string first. Use the =ENCODEURL(text) function to include text searches into your WEBSERVICE function URL. Learn more about ENCODEURL function here.

 

Now we are better prepared for packing and planning our crabbing activities!

--Lee Bizek, Excel Program Manager

Download the full example file Kamping 2013 – Start and Complete Sample Files.zip attached below. The example file also utilizes the tide data and includes XPATH filters in the WEBSERVICE Functions. Remember you can use this functionality for many different web services to pull in data that you can then analyze using Excel.

To use the web service in this example, go to www.wunderground.com and register for their Weather API to get your own API key. Once you have your API key, add it to the Weather tab. All WSF calls in the file will use this cell reference in the URL.

View the original article here