Web Queries

If you are planning to retrieve data from a web page to use and analyze it in Excel, you can open Excel's Data menu and select Import External Data | New Web Query. Web queries allow you to retrieve data from the web directly into Microsoft Excel. After placing data in a worksheet, you can use Excel tools for performing data analysis. Using a web query, you can retrieve into a worksheet a single table, a number of tables, or all the text that a particular web site contains.

Tip 16-1: Ready-to-Use Web Queries

Microsoft Excel 2002 comes with several built-in web queries. They are installed in the C:\Program Files\Microsoft Office\Office10\ Queries folder, and can be loaded in Excel via the Open command on the File menu. The names of these queries are: MSN MoneyCentral Investor Currency Rates.iqy MSN MoneyCentral Investor Major Indicies.iqy MSN MoneyCentral Investor Stock Quotes.iqy

If your C:\Program Files\Microsoft Office\Office10\Queries folder is empty, you may need to update your current installation of Excel (use the Control Panel's Add/Remove Programs dialog box) and indicate that you want these features to be installed.

Figure 16-11: TDK corporation stock quotes were retrieved from the web using the built-in web query MSN MoneyCentral Investor Stock Quotes.iqy after typing TDK in a dialog box displayed upon activating this query.
To run web queries, you must have an active connection to the Internet.

[New Web Query

Address; |http;/Mww. msn.com/

Ml [jö] *■ © 0 9 g Options...

£kk S next tothe table? youwontto setect, then click Import.

MSN Home | My MSN I Hotmail | Search | Shopping | Money | People & Chat 'search the Web: Ejfffffil

Saturday, Aug 10

£kk S next tothe table? youwontto setect, then click Import.

MSN Home | My MSN I Hotmail | Search | Shopping | Money | People & Chat 'search the Web: Ejfffffil

wiiHi s nie best sleeping position?

Saturday, Aug 10

From digital cameras to diamonds, find what you're looking for at MSN eShop

Drought survival tips for your lawn

How to make sure your money outlives you

Your Guide eteorShower

Toikiy on MSN

■ Fat-fighter hormone ID'd - The scoop on kitty litter

' 9 annoying male habits

Autos

Back to School

Business

Careers

City Guides

Entertainment

Games

Figure 16-12: This dialog box in the user interface allows you to create a web query without knowing anything about programming.

Web queries can be static or dynamic. Static queries always return the same data, while dynamic queries allow the user to specify different parameters to narrow down the data returned from the web page. Web queries are stored in text files with the .iqy extension. The content of the .iqy file can be viewed after opening the file in any text editor (for instance, Windows Notepad).

Figure 16-13: The web query file references the page from which you want to retrieve data and specifies parameters to define how data should be imported and any special instructions for the web server.

The .iqy files contain the following parts:

Section name

Description/Example

Query Version

(optional section)

(required)

Query Type

(optional section)

Set to WEB when you use the version section:

Allows you to set the version number of a web query. For example: 1

The URL of the web page where you will get your data. For example:

http://investor.msn.com/external/excel/quotes.asp http://www.um.lublin.pl/oswiata/sz_pdst.htm

POST Parameters You can send parameters to the web server using the POST or (optional section) GET method. This section is used for sending parameters with the

POST method. These parameters need to be entered on a separate line, as in the following example:

http://www.xe.net/cgi-bin/ucc/convert From=USD&Amount=1&To=CAD

From, Amount, and To are the names of parameters, while the values that follow the equal (=) sign are the parameter settings. Parameters are separated from one another with the & sign.

Note: When sending parameters using the GET method, the parameters are attached to the URL address using the question mark, as shown below:

http://uk.finance.yahoo.com/quote?symbols=met

Symbols is a parameter name, while met is a stock symbol (a parameter value), which you want to retrieve from the specified URL.

0 0

Post a comment