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:

30 responses

      • I struggled with the ” until I discovered this is html code for ” (a quotation mark). When I replaced it worked as you described. Thanks, very helpful example.

  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. Pingback: Excel 2013 Preview– Webservice Function and Flash Fill « The WorkerThread Blog

  3. Pingback: Importing Azure Marketplace Data into Excel 2013 with Web Queries « Chris Webb's BI Blog

  4. Pingback: Bringing Location Information Into Excel 2013 With The WebService() Function | Chris Webb's BI Blog

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

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

      • That was close, but this one works (where J3 is a cell reference to the stock code):
        There is a catch: it will not update itself – not even when the sheet is re-opened
        any ideas?

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

  8. I have a quick question in this regard:

    I am parsing (scraping) several websites at the same time:
    On cell A1 I have “100, 101, 102”
    I have a function that splits and creates the strings:

    There is a certain component from each of those rss that I want to extract, for which I have developed a function to do so as well. Say, p.e.:
    100 -> “Failed”,
    101 -> “Good”,
    102 -> “Good”
    This function is using Application.WorksheetFunction.Webservice() (which is the same as putting the =WEBSERVICE() in a cell)

    However, if the list on A1 is longer (100, 101, 102, 103, 104, 105, 106, 107) The server freaks out, or the excel freaks out, or both, and then the whole thing freezes.

    My question is:

    Is there a way to “break” the WEBSERVICE() request if it takes longer than, say, 2s?

    Thank you all for your time,

  9. I replicated the function by adding the reference to Winhttp and 6 lines of VBA

    Function webserv(url2)
    Set web2 = New WinHttpRequest
    Call web2.Open(“GET”, url2)
    Call web2.Send
    webserv = web2.ResponseText
    End Function

  10. Many thanks for this, Chris, but I’m not sure why you need the ENCODEURL, this worked fine for me:
    = “” & E2 & “/3dayforecast.rss”
    maybe there was some update since 2012 ? I did need to replace the fancy quotes that came from the copy & paste with ‘straight’ ASCII double-quotes

  11. Okay, then you would hope that a re-calc would refresh it like it does every other formula…
    but it doesn’t… sigh

  12. Pingback: Excel Dynamic Arrays And FilterXML « Chris Webb's BI Blog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: