Using the WebService() function in Excel 2013

One thing that piqued my interest when looking over the new functions in Excel 2013 were the new functions available that allow you to retrieve data from a web service directly into worksheet: EncodeURL(), Webservice and FilterXML(). Here’s a brief demo of how to use them.

First, find a web service that returns XML and doesn’t require any authentication (I say that because I haven’t worked out how to pass usernames and passwords with these functions yet – I hope it’s possible, and if/when I work out how to do it I’ll blog again). The example I’m going to use is the BBC weather web service, which allows you to subscribe to an RSS feed containing the weather forecast for a particular postcode (similar to a zip code for my US readers). Here’s an example URL which returns the forecast for my home, which has the postcode HP6 6HF:

http://open.live.bbc.co.uk/weather/feeds/en/hp66hf/3dayforecast.rss

Let’s now build an Excel 2013 spreadsheet that allows you to enter a postcode and then displays the weather forecast for it using this web service. First of all, I’ll specify cell E2 as the place to enter the postcode:

Next, in cell D4, I need to construct the URL for the web service and retrieve the data like so:

=WEBSERVICE(“http://open.live.bbc.co.uk/weather/feeds/en/” & ENCODEURL(E2) & “/3dayforecast.rss”)

Here, I’ve used the EncodeURL() function to URL encode the text entered in cell E2, and then dynamically generated the URL and passed it to the Webservice() function which simply retrieves the response from the web service. If it’s successful you should see the XML returned displayed in the cell:

If it’s not successful, you get a #Value error:

Finally, you can get values from the response and display them in cells by using the FilterXML() function, which allows you to query the response using XPath. Here are some examples of how you can use FilterXML():

=FILTERXML(D4,”//rss/channel/title”)

=FILTERXML(D4,”//rss/channel/item[1]/title”)

=FILTERXML(D4,”//rss/channel/item[1]/description”)

And here are what these three formulae return when placed in cells D6, D8 and D9:

 

UPDATE: I have had official confirmation from Microsoft that the WebService() function will only work with services that do not require any authentication, which is a shame – I was hoping to use it with things like the Bing Search API and the Microsoft Translator API. Hohum.