Excel is commonly used to chew through historical data and extract meaningful totals, averages, and trends. However, one of the most exciting ways to use Excel is to deal with up-to-the-minute information. For example, you might want to create a spreadsheet that takes into account the latest sales figures, stock prices, or currency exchange rates. In this article, you'll see how you can use the Research task and web services in Excel 2003 to enable scenarios like these.
The simplest (and least flexible) way to get real-time results is to use the Excel Research task. The Research task is new in Excel 2003, and it allows all Office applications to pull information from dedicated services on the internet. The problem is, you won't necessarily find what you want; in fact, you're limited to a very small set of free services or a larger (but still modest) collection of premium services that require a subscription fee.
To see the Excel Research task in action, select Tools -> Research from the menu. The Research task will appear to the right of the Excel window (see Figure 1). Excel provides built-in access to a small set of Research services. These include MSN Money stock quotes; a dictionary, thesaurus, and encyclopedia; and the MSN internet search engine. (To search for fee-based, third-party services, click on the "Get services on Office Marketplace" link in the Research pane.)
What's important to understand about Research services is that whoever designs the Excel plug-in chooses the options you have. Some Research services don't do anything except display relevant information in the Research pane or provide links that launch external web pages in a browser. Other Research services let you drag and drop data directly into your spreadsheet. To try out an example with the MSN Money service, follow these steps:
The Research task is a nice tool for getting up-to-date information. But it won't help if you can't find a company that provides the service you need. In this case, you need a more flexible solution.
Another tool that allows Excel to extract information from the Web is a feature called web query. Unlike the Research task, which is limited to Excel 2003, web queries were introduced in Excel 2002. Essentially, a web query allows you to fetch data from a table in virtually any web page. For example, if you want stock quotes you can surf directly to Yahoo Financial, and if you want currency exchange rates you can try out a helpful site such as xe.com. The problem with web queries is that they're awkward to set up and they're fragile; in other words, very minor changes in the source pages can completely flummox your query, making it impossible to refresh your spreadsheet to get more recent information. For more information about web queries, refer to Excel: The Missing Manual.
An even more powerful approach is to create a spreadsheet that calls a dedicated web service. Essentially, a web service is a miniature program that you can call over the Web. Calling a web service is like calling a built-in Excel function, in that you supply some information (as arguments) and retrieve a result. The difference is that unlike an Excel function, the web service doesn't execute on your computer. Instead, it runs on a web server somewhere on the internet.
Clearly, there's more involved in calling a web service than there is in using a built-in Excel function. Fortunately, in Excel 2002 and Excel 2003, most of the work is managed for you behind the scenes. All you need to do is use a specialized web service add-in and write a small amount of macro code.
The first step is to download the Office Web Services Toolkit, a free add-in that gives you the ability to call remote web services from inside an Office document. To download the toolkit, surf to the Microsoft Download Center and search for Office Web Services Toolkit. You'll see two different versions: one that's designed for Office XP and another that works with Office 2003.
Once you install the toolkit, you're ready to web-enable your worksheets. However, there's still a gap; namely, you need to find a web service that provides the information you need. Fortunately, web services are a cross-platform standard, and there are hundreds of free web services scattered over the Web and many more fee-based options. Additionally, companies that have in-house development staff often create their own web services using a tool like Microsoft's .NET platform. In this article, you'll use a web service from XMethods that provides up-to-date currency exchanges rates.
In order to use any web service, you need to know its URL. The URL for the currency exchange service is http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl. You can try typing this URL directly into your browser, but all you'll see is an unhelpful XML document that describes, in computerese, how to communicate with the currency exchange web service. (Thankfully, Excel is able to read this XML document and configure your spreadsheet accordingly.)
Once you have the URL for your web service, the next step is to attach it to your workbook by following these steps:
Once you finish these steps, Excel generates a slew of extra code for calling the web service. With the currency exchange service, Excel places the code in a class named, rather unhelpfully,
clsws_CurrencyExchangeServi. (You'll see this class in the Visual Basic editor tree.) Fortunately, you don't need to understand this code. However, you should scroll through to find the
wsm_getRate() function. This is the important part; it calls the exchange web service and gets the exchange rate for the specified countries.
There's still an extra step you need to take before you can use the web service in your worksheet: you need to add a custom function that uses the generated web service class. You can then call this function at any point in a cell formula. Creating a custom function in Excel is fairly straightforward (and the process is explained in detail in Excel: The Missing Manual). The first step is to create a new module, by choosing Insert -> Module in the Visual Basic Editor window. Now you need to add a function inside the module that calls the web service. Here's the code you need to add:
Function GetRate(country1 As String, country2 As String) Dim WebService As New clsws_CurrencyExchangeServi GetRate = WebService.wsm_getRate(country1, country2) End Function
It's worth taking a line-by-line look at this function:
GetRate()makes sense, but you can use whatever you want. The next two parameters define two arguments that the person calling the function needs to supply. In this case, you need two country names. (The first country name is the currency you're changing to, while the second country name is the currency you're changing from.) If you're wondering how I knew to use two parameters, there's no magic involved; this function just has to match the
wsm_getRate()function that Excel generated for the web service.
clsws_CurrencyExchangeServi, because that's the name of the file Excel generated when you added the web reference.
GetRate() function is refreshingly easy. To try it out, type a cell formula like this:
This returns (at the time of this writing) an exchange rate of 0.7649 dollars to the euro (see Figure 4).
To convert your bank account balance (stored in cell B2) from dollars to euros, you use a formula like this:
=GetRate("usa","euro") * B2
For a full list of acceptable country names, refer to the detailed documentation for the currency exchange service at XMethods. While you're at it, you might find some other demonstration services that you'd like to try.
Matthew MacDonald is a developer, author, and educator in all things Visual Basic and .NET. He's worked with Visual Basic and ASP since their initial versions, and written over a dozen books on the subject, including The Book of VB .NET (No Starch Press) and Visual Basic 2005: A Developer's Notebook (O'Reilly). His web site is http://www.prosetech.com/.
Return to the Windows DevCenter.
Copyright © 2009 O'Reilly Media, Inc.