Simon Benninga. Financial Modelling 3-rd edition

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

1048 Chapter 41

change the value in this pair from ^DJI to GM, the Web page returns

the information for General Motors, and it is in a slightly different

format.

This “?name1=value1&name2=value2. . .” arrangement is not unique

to Yahoo.com. Here is another example taken from MSN

MoneyCentral:

http://moneycentral.msn.com/investor/charts/chartdl.aspx?

Symbol=$INDU&PT=2

1049 Information from the Web

As before, the URL of this page has two parts separated by a question

mark:

1. The actual address: http://moneycentral.msn.com/investor/charts/

chartdl.aspx

2. Information for the page to act on: Symbol=$INDU&PT=2

The information in the second part of the URL is part of the HTML

standard and is called a get parameter structure. It consists of name=value

pairs separated by &. In this case,

•

Symbol=$INDU: This pair selects the ticker-tape symbol, in this case

the Dow Jones Industrial Average.

•

PT=2: This pair selects the display period for the chart: PT=0 is intraday,

PT=1 is ﬁ ve-day, PT=2 is 10-day (and is the current selection), and so on

up to PT=10 for a 10-year chart.

41.6 Web Queries: Parameters

The interface presented by the Web query wizard has no provision for

parameter information to be passed to the query. We can, however, use

a roundabout way to force the issue.

Here is an iqy ﬁ le querying a Web document that expects one numeri-

cal parameter called s.

We make a small change to the iqy ﬁ le, replacing s with [“Ticker-Tape

Symbol?”]. This step results in a parametric query:

1050 Chapter 41

We can now activate the query by double-clicking the ﬁ le DjiParam.iqy

in Windows Explorer.

Once we click Open to activate the query, the parameterization of the

query gets to work and we are presented with the following:

1051 Information from the Web

Notice that the question on the dialogue is taken from the quoted string

in the iqy ﬁ le ([“Ticker-Tape Symbol?”]). Once we provide the value

(^DJI) and click OK, the query is run with the value provided.

1052 Chapter 41

Every time we try to update the query, Excel will ask for a value for

the ﬁ rst parameter. If we want to avoid the repeated queries (pun

intended), we can direct Excel to take the value of a parameter from a

cell in the worksheet. Some preparation is necessary, and the query itself

needs to be activated using the data menu. The sequence of events is

depicted in the following screen shots.

First we prepare a worksheet with a cell containing the value we want

to give the query:

1053 Information from the Web

Then we open the ﬁ le “DjiParam.iqy” using the menu selection

Data|Import External Data|Import Data:

When presented with the import-data dialogue, we provide a destination

($A$1) for the data and select Parameters to provide information about

the parameter:

1054 Chapter 41

1055 Information from the Web

Having set up cell $A$1 to hold the value of the parameter, we choose

the third option, supply the address, tick Refresh automatically when cell

value changes, and click OK. This step gets us back to the import-data

dialogue, and all we have to do now is click the OK button. Here is what

we get:

1056 Chapter 41

41.7 Web Queries: CSV Files and Postprocessing

A number of ﬁ nancial sites provide the option to download the informa-

tion in a text format called CSV (comma separated values). For example,

if we surf to the URL http://ichart.yahoo.com/table.csv?s=^FTSE using

Firefox, we are presented with the option to save the ﬁ le or open it.

1057 Information from the Web

If we choose Open, then a new workbook with all the daily historical

prices for the Financial Times FTSE100 index will open:

As we can see, Excel can read this kind of ﬁ le directly, but, sadly, there

is no apparent way to link to such information and have it automatically

updated. We can, however, use the Web query mechanism to achieve such

integration with a bit of postprocessing.

Here is a URL that we can use to retrieve the daily prices for the FTSE

100 index for 2005 from Yahoo:

http://ichart.yahoo.com/table.csv?

s=^FTSE&a=00&b=1&c=2005&d=11&e=31&f=2005&g=d

If we copy the URL we used to get the information to a ﬁ le and save it

with the sufﬁ x iqy, we get a query ﬁ le. Here is a screen shot of that ﬁ le:

If we activate it (by double-clicking in the explorer) we get the

following: