Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using the WebService() function in Excel 2013

with 14 comments

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:

image

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:

image

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

image

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:

image

 

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.

Written by Chris Webb

July 31, 2012 at 2:29 pm

Posted in Excel

14 Responses

Subscribe to comments with RSS.

  1. Does it work in SkyDrive?

    Jamiet

    July 31, 2012 at 3:49 pm

  2. However, it does work! No other published example I had seen so far did.

    David Hager

    July 31, 2012 at 11:51 pm

    • You don’t know how much trial and error was involved in getting it to work, though…!

      Chris Webb

      August 1, 2012 at 8:14 am

  3. There was a Microsoft web services tool kit add-in for XL2003 that used VBA classes. After pointing it at the WSDL it generated all the methods. It was quite neat and after some effort I consumed data from Amazon Web Services. Interesting that the 2013 version does this without VBA code.

    Philip Taylor

    August 1, 2012 at 4:21 pm

  4. [...] saw this blog post from Chris Webb with an example of the new WEBSERVICE function.   As it happened I was recently looking [...]

  5. [...] few weeks ago, when I was playing around with the new WebService() function in Excel 2013, I was disappointed to learn that it didn’t work with services that require [...]

  6. [...] summer I wrote a post about the new WebService() function in Excel 2013 and showed how it could be used to display data [...]

  7. For downlevel versions of Excel, an addin that enables most of the new worksheet functions introduced in Excel 2013 to your current downlevel version of Excel. The examples above would work the same, except the function names would be “pwrWEBSERVICE” and “pwrENCODEURL”. Reading thru these comments, I would say that pwrWEBSERVICE currently has the same limitations regarding authentication. I’ve made a mental note for a potential enhancement to the function tho! There’s a download button at the top of officepowerups.com to get it if you were curious.

    Disclosure: I created the addin mentioned above, so this is a bit self-promoting. But I still think that its cool to be able to similarly extend downlevel versions if that’s what you’re working with. More details can be found at http://officepowerups.com/2013/04/10/how-you-can-add-the-50-new-functions-in-excel-2013-to-your-copy-of-excel-2010-or-excel-2007/.

    Charlie Nichols

    June 28, 2013 at 7:15 pm

    • Just a quick update, I’ve updated the pwrWEBSERVICE function to work with both GET and POST methods.

      Charlie Nichols

      September 27, 2013 at 7:03 pm

  8. Thanks a lot !!! :)

    alex

    September 6, 2013 at 10:01 pm

  9. Here is the WEBSERVICE function for grabbing stock information from the YAHOO site:

    =WEBSERVICE(“http://quote.yahoo.com/d/quotes.csv?s=KO&f=sl1d1t1c1ohgv&e=.csv”)

    KO is the symbol for Coke. Use reference to a cell if you want to use it on several rows with different stocks.

    I don’t think that you would really like it to work with sites that require a login and password. The function could be used to hack your accounts with automated ID and password generators if it did. Be careful what you wish for……

    Van

    October 15, 2014 at 10:00 pm

    • Since writing that post, I’ve discovered Power Query, and it’s a far superior way to get data from web services into Excel. It also supports authentication and stores credentials securely.

      Chris Webb

      October 18, 2014 at 8:47 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: