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:

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(“” & 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():




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.

UPDATE #2: Since writing this post, it’s become clear that Microsoft’s Power Query add-in provides much better functionality for working with web services in Excel – it handles most forms of authentication, for a start, and it’s much easier to work with any XML or JSON that a web service returns.

For more information, see my blog posts here:

16 thoughts on “Using the WebService() function in Excel 2013

  1. 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.

  2. 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 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

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


    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……

    • 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.

  4. I have an excel sheet with data that I need to use to make a web service call– does this let you do that or only pull the data to the excel sheet?

Leave a Reply

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

You are commenting using your 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