Getting Real-Time Data from the Web in Excelby Matthew MacDonald, author of Excel: The Missing Manual
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 Research Task
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:
- In the "Search for" box of the Research task pane, type a ticker symbol (like
- In the listbox below it, select the MSN Money Stock Quotes service.
- Click on the green arrow to start searching, and wait until the stock quote appears.
- Before going any further, you need to update your version of Excel so that it has the latest support for the MSN Money service. To do so, click on the arrow on the Insert Price button, and choose Check for New Actions. Your web browser will open with a page where you can download and install an Excel update. Once you're finished, close and restart Excel, and repeat steps 1 to 3.
- To insert the full stock quote into the current worksheet, click on the arrow on the Insert Price button, and choose Insert Refreshable Stock Data. You'll see the detailed information shown in Figure 2.
- Save your workbook. At any later time you can open the workbook and choose Data -> Refresh Data from the menu to update the information so it shows the current stock price. (All stock quotes are delayed 20 minutes.)
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.
Calling a Web Service
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:
- You need to perform this task from the Visual Basic editor that's built into Excel, so start by choosing Tools -> Macro -> Visual Basic Editor from the menu.
- In the Visual Basic editor, choose Tools -> Web Services References, which shows a dialog box where you can connect to your web service (see Figure 3).
- Click on the Web Service URL option at the bottom of the window, and then type in the URL for the currency exchange service and click on Search. It's always easiest to use this technique to go straight to the web service you want. Although the window also has options for hunting for web services, most web services aren't registered in public catalogs and so won't show up in a keyword search.
- An entry for the currency exchange service will appear in the tree on the right side of the dialog box. Click to place a check mark next to it.
- Click on Add to complete the process.
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:
- The first line defines the function. In this case, the name
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.
- The second line creates the web service object. At this point, all the initialization you need is performed automatically. Note that the name you use is
clsws_CurrencyExchangeServi, because that's the name of the file Excel generated when you added the web reference.
- The third line does the hard work of calling the web service (with the supplied country information) and it then returns the result to whoever called the
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.
Showing messages 1 through 7 of 7.
Excel example logic is upside down
2005-02-19 09:18:35 dhlundy [View]
Couldnt get it working here
2005-03-22 17:05:29 claudiogoems [View]
2005-07-19 05:03:09 m_r_t_y_l_m_z [View]
I am getting #NAME error !!
2005-09-27 07:29:25 pankys [View]
2010-02-09 18:35:06 Fdesmond [View]