Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

1038 Chapter 41

Clicking on an arrow changes it to a green tick mark to indicate that the

item has been selected.

1039 Information from the Web

Once all the tables we are interested in have been selected, we can click

the “Import” button.

The wizard now asks for a destination for the data; in the next example

we put it in cell A1:

1040 Chapter 41

We can now update the data whenever we want by right-clicking on any

cell in the query data range (cell B7 in the following screen capture) in

Excel and selecting “Refresh Data” from the menu:

If you use an up-to-date version of Excel, you might get the following

message; click OK to make it go away:

1041 Information from the Web

The data will now be updated.

We can also set the data to refresh automatically by selecting the

appropriate response from the “Data Range Properties . . .” on the right-

click menu. While you are on that dialogue you may want to deselect the

annoying “Adjust column width” option (circled and deselected on the

screen cap):

41.4 Web Queries: The iqy File

Information about a Web query can be kept in a simple text ﬁ le with the

extension iqy. You may create it using a text editor (like Notepad), or

1042 Chapter 41

you may save a query created by the wizard as demonstrated in the next

screen shot:

Here is the resulting ﬁ le:

1043 Information from the Web

The ﬁ rst four lines of the iqy ﬁ le are mandatory; the third line is, of

course, the full URL of the Web document from which the query is

created. Note that the fourth line (mandatory, indicated with an arrow)

is blank. In our case the only other necessary line is the ﬁ fth line—it

selects the 14th element on the Web page as the target of our import.

41.4.1 Running a Web Query

Web query ﬁ les (having an iqy extension) can be run by Excel without

going through the full procedure illustrated in the ﬁ rst part of this section.

When you open a Web query ﬁ le in Excel, the query is run.

To illustrate, we change the ticker symbol ^DJI in the previous Web

query to GM, the symbol of General Motors. We save the resulting ﬁ le

as gm.iqy; when we open this ﬁ le in Excel, this is what happens:

Clicking on Open imports the data for GM:

2. This number is dependent on the structure of the Web page as recognized by the Web

query.

1044 Chapter 41

If we compare the relevant Web pages, notice that the data retrieved

are not the same as in the case of ^DJI, since Yahoo produces differently

shaped pages for indexes and stocks.

To use an iqy ﬁ le in the current worksheet, make the menu selection

Data|Import External Data|Import Data; this should open the select

data-source window. By default the window opens to “My Data

Sources.”

1045 Information from the Web

Navigate to the location of the iqy ﬁ le and select it. Once the correct

ﬁ le is selected, Click Open:

1046 Chapter 41

Now we select a location for the imported data:

And a few seconds later the data are in the worksheet. If we repeat the

process for the General Motors query (gm.iqy) and select the cell D1 as

a target for the imported data, we can have two queries side by side on

the same worksheet.

1047 Information from the Web

41.5 Parametric Web Pages

The most common way of supplying a Web page with information is

using parameters on the URL. We need to supply the information so the

Web page can return the information we are interested in. Here is an

example we used earlier:

The URL of this page has two parts separated by a question mark:

1. The actual address: http://ﬁ nance.yahoo.com/q

2. Information for the page to act on: s=^DJI

The information in the second part of the URL consists of name=value

pairs separated by “&.” In this case we have only one such pair, “s=^DJI,”

and the separator is not necessary. As was demonstrated earlier, if we