Function: WEBSERVICE

by | Jul 17, 2019 | Excel Formulas

excel webservice

The Excel WEBSERVICE function is a Web formula that can return data from a web service on the Internet or an Intranet. This function is available for 2013 and later versions of Excel for Windows. In this guide, we’re going to show you how to use the Excel WEBSERVICE function and also go over some tips and error handling methods.

Supported versions

  • Excel 2013 and later versions for Windows

Excel WEBSERVICE Function Syntax

WEBSERVICE(url)

Arguments

url The URL of the web service

Example

The WEBSERVICE function returns the XML based data from a website or web service on the Internet or an Intranet. The only argument it requires is the URL of the website or web service. If the connection is successful, the formula cell will show the XML output.

In the example below, we used a web service that returns the current stock prices when you enter the stock symbol (i.e. “MSFT” for Microsoft Corp.).

http://dev.markitondemand.com/Api/Quote/xml?symbol=MSFT

Here, we copy and paste the URL into any cell (i.e. B2) and use the WEBSERVICE function with the reference of the URL cell.

=WEBSERVICE(B1)

excel webservice

You can use FILTERXML function to simply parse an XML value. For example, to get the Name value under the QuoteApiModel and Data branches, use a formula like this:

=FILTERXML(B2,"//QuoteApiModel/Data/Name")

Note: The cell B2 is where the WEBSERVICE function is.

excel webservice

Download Workbook

Tips

  • Web services are a great way to send and receive data over the internet. The object based data moves between web-based applications and software, in XML or JSON forms. Stock prices, weather forecasts, and geographical data are common examples of utilizing web services.
  • The FILTERXML function can help parse values from the returned XML data.
  • You can use the ENCODEURL function to generate valid URLs.
  • Microsoft states that the WEBSERVICE function may appear in the Excel for Mac function gallery, but it doesn't work on the Mac version, because of the function's dependencies on Windows operating system features.

Issues

  • The WEBSERVICE function returns the #VALUE! error  under these conditions:
    • Invalid URL or missing protocols such as ftp:// or file://
    • Invalid return value
    • If return string is longer than the cell limit of 32,767 characters
  • If the URL is longer than the GET request limit of 2,048 characters