• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Get Data from the Web >  Perform Web Queries

4.1. 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.


4.1.1. 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


4.1.2. 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


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint