Excel 2003 Programming: A Developer's Notebook

Chapter 4. Get Data from the Web

Today it is hard to remember a time when the Web didn’t matter, but it wasn’t that long ago that it didn’t even exist. Because Excel was created long before the Web, it has adapted as the Web evolved. There are now three main approaches to retrieving data from the Web:

• Web queries retrieve data directly from a web page and import that data into a query table on an Excel spreadsheet. Although this was one of the first web access features added to Excel (introduced in 1997), it is still very useful.

• Web services execute applications remotely over the Web to return results in XML format. The number of services available over the web is growing quickly as this standard is becoming broadly adopted. Web services provide a standardized way of exchanging parameters and retrieving results over the Web—something that is missing from web queries.

• Database access over the web is now available through most database software. This technique depends on the database provider and is not covered here.

This chapter describes how to use web queries and web services to retrieve data from the Web and import it into Excel. The samples in this chapter demonstrate a variety of programming tasks with these two approaches, including:

• Passing parameters

• Formatting results

• Getting data asynchronously

• Displaying results through XML Maps

Note

Code used in this chapter and additional samples are available in ch04.xls.

Perform Web Queries

Web queries are a quick way to import data from a web page into a worksheet using a QueryTable object.

Note

Even though web queries aren’t new, they are useful tool for getting data from the Web. Understanding their use (and limitations) is helpful for understanding the alternate approach: web services.

How to do it

To perform a web query:

1. Choose Data → Import External Data → New Web Query. Excel displays the New Web Query dialog (Figure 4-1).

   

   Figure 4-1. Use web queries to import data directly from a web page

2. Type the address of the web page you want to import data from in the Address bar and click Go to navigate to that page. It is usually easiest to find the page you want in your browser, then cut and paste that address into the New Web Query dialog box.

3. Excel places small yellow boxes next to the items you can import from the page. Click on the item or items you want to import and Excel changes the yellow box to a green check mark.

4. Click the Options button to set how Excel formats imported items. Formatting options are shown in Figure 4-2.

   

   Figure 4-2. Set formatting options for the query

5. Close the Options dialog box and click Import. Excel displays the Import Data dialog box, as shown in Figure 4-3.

   

   Figure 4-3. Choose the destination for the imported data

6. Click Properties to determine how the query is performed, such as how the data is refreshed. Figure 4-4 shows the query property settings.

7. Close the Properties dialog and click OK to import the data.

Figure 4-5 shows a real-time stock quote and quote history imported from the Yahoo! web site. Yahoo! is a good source for this type of web query because it is a free service and doesn’t require you to register or sign in.

Figure 4-4. Use Query properties to name the query, set how data is refreshed, and how cells are inserted

How it works

If you choose Tools → Macro → Record New Macro, then perform the preceding web query, you’ll get code that looks something like this:

	                 With ActiveSheet.QueryTables.Add(Connection:= _
	              
       "URL;http://finance.yahoo.com/q/ecn?s=SNDK", _
        Destination:=Range("C2")) 
       .Name = "Real-Time Quote"
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .BackgroundQuery = True	
       .RefreshStyle = xlOverwriteCells 
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .WebSelectionType = xlSpecifiedTables
       .WebFormatting = xlWebFormattingNone	
       .WebTables = "22" 
       .WebPreFormattedTextToColumns = True
       .WebConsecutiveDelimitersAsOne = True
       .WebSingleBlockTextImport = False
       .WebDisableDateRecognition = False
       .WebDisableRedirections = False	
       .Refresh BackgroundQuery:=False 
   End With	
With ActiveSheet.QueryTables.Add(Connection:= _
	              
       "URL;http://finance.yahoo.com/q/hp?a=01&b=5&c=2003" &_
       "&d=01&e=5&f=2004&g=d&s=sndk",  _
	              
       Destination:=Range("A9")) 
       .Name = "Price History"
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .BackgroundQuery = True	
       .RefreshStyle = xlOverwriteCells 
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0	
       .WebSelectionType = xlSpecifiedTables 
       .WebFormatting = xlWebFormattingNone	
       .WebTables = "30" 
       .WebPreFormattedTextToColumns = True
       .WebConsecutiveDelimitersAsOne = True
       .WebSingleBlockTextImport = False
       .WebDisableDateRecognition = False
       .WebDisableRedirections = False	
       .Refresh BackgroundQuery:=False 
   End With

Figure 4-5. Using a web query to get stock price data

Some key properties and methods above shown in bold bear mention here:

• The Add method creates the query and adds it to the worksheet.

• The RefreshStyle property tells Excel to overwrite existing data rather than to insert new cells each time the query is refreshed.

• The WebTables property identifies which item from the page to import. Excel assigns an index to each item on the page, and you can import one or more items or the entire page if WebSelectionType is set to xlEntirePage .

• The Refresh method imports the data onto the worksheet. Without this method, the query results are not displayed.

The query itself consists of the Connection , WebTables , and formatting properties. If you save the web query to a query file (.iqy), the data looks like this:

   WEB
   1
   http://finance.yahoo.com/q/hp?a=01&b=5&c=2003&d=01&e=5&f=2004&g=d&s=sndk
   
   Selection=30
   Formatting=None
   PreFormattedTextToColumns=True
   ConsecutiveDelimitersAsOne=True
   SingleBlockTextImport=False
   DisableDateRecognition=False
   DisableRedirections=False

When Excel updates a web query, a small green globe is displayed in the status bar at the bottom of the screen (Figure 4-6). This symbol indicates that the query is being refreshed from the Internet.

Figure 4-6. Excel is refreshing the query from the Internet