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.
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:
http://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/
http://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/
Does it work in SkyDrive?
No, it doesn’t… Not in the Office Web App either.
You can imagine my reaction.
However, it does work! No other published example I had seen so far did.
You don’t know how much trial and error was involved in getting it to work, though…!
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.
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.
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/.
Just a quick update, I’ve updated the pwrWEBSERVICE function to work with both GET and POST methods.
Thanks a lot !!! 🙂
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……
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):
=NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=”&J3&”&f=l1”))
There is a catch: it will not update itself – not even when the sheet is re-opened
any ideas?
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?
This only lets you pull data into the Excel sheet.
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:
“http://mywebsite.my/rss/100”
“http://mywebsite.my/rss/101”
“http://mywebsite.my/rss/102”
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,
No, there isn’t as far as I know. I recommend you look at Power Query – it has a lot more functionality for this kind of thing including waiting between requests (see http://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/).
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
Many thanks for this, Chris, but I’m not sure why you need the ENCODEURL, this worked fine for me:
= “http://open.live.bbc.co.uk/weather/feeds/en/” & 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
Why the pulled data could not be updated by pressing “Reflesh All” in Excel?
I guess because the WebService() function is used in a formula and not a data connection?
Thanks for your reply.
Okay, then you would hope that a re-calc would refresh it like it does every other formula…
but it doesn’t… sigh
In this case I would write a vba to reflesh it as a makeshift.
That sounds like a feasible work around…
thanks
Can we use web functions for https://exceljet.net/keyboard-shortcuts
and if yes, How to do it as I tried but failed everytime