|
4.1. Perform Web QueriesWeb 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 itTo perform a web query:
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 inserted4.1.2. How it worksIf you choose Tools Macro Record New Macro, then perform the preceding web query, youll 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 dataSome key properties and methods above shown in bold bear mention here:
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 |
|