Simon Benninga. Financial Modelling 3-rd edition

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

1058 Chapter 41

And if we select Open,

As seen, the output from the query is a single column of textual infor-

mation. We now need to convert it to usable information; this operation

is best done using the text-to-column conversion wizard already dis-

cussed in section 41.2.2.

1059 Information from the Web

41.8 A VBA Application: Importing Price Data from Yahoo

Up till now in this chapter we have discussed the direct import of Web

data to Excel. In this section we show how to write routines in VBA that

import the data and possibly manipulate them. As an example we show

how to get price data for a selected stock, for a selected time period. Our

stated purpose is to use the Yahoo price data for computing the historical

volatility for a stock that was illustrated in Chapter 19. But of course our

underlying motive is to illustrate an extensive use of the VBA connection

between Excel and ﬁ nancial Web data. If you understand this applica-

tion, you should be able to proceed on your own to more advanced

implementations.

For clarity we proceed in three stages: First we show how to import

data from Yahoo, and then we show how to use user interaction to supply

Yahoo with the needed details. In the ﬁ nal stage we show how to deal

with errors in user input and with Web connection problems.

41.8.1 Get Prices: A First Approximation

The ﬁ rst approximation retrieves some historical daily prices for the

Nasdaq index QQQQ from Yahoo. Note that no error checking is per-

formed and that all data are hardwired in the VBA code. The fact that

the macro includes no error checking means that it might not run or that

it might produce funny results if the Web connection misbehaves.

Sub GetPricesVer1()

Dim Ws As Worksheet ‘Our query is going to be

‘stored here

Dim Wq As QueryTable ‘This will be our query

Dim Url As String ‘Full Web address on Yahoo

Url = _

“URL;” & _

“http://ichart.ﬁ nance.yahoo.com/table.csv?” & _

“s=QQQQ&” & _

“a=3&b=27&c=2007&” & _

“d=4&e=6&f=2007&” & “g=d&” & _

“ignore=.csv”

1060 Chapter 41

Points of note:

•

The URL string is composed of several strings on consecutive lines

concatenated with the VBA concatenation operator &; also notice the

VBA line-continuation operator _ at the end of some of lines.

•

The ﬁ rst string—“URL;”—Sets the type of the query, in this case a

Web query.

•

The second string—“http://ichart.ﬁ nance.yahoo.com/

table.csv?”—provides the actual Web address for the data to be

retrieved. Notice that it ends with the separator ? used to separate the

Web address of a parametric Web page from its parameters.

•

The third string—“s=QQQQ&”—is the ﬁ rst parameter for the Web page

and speciﬁ es the ticker-tape symbol of the stock; notice that it ends with

the separator & used to separate one parameter of a parametric Web

page from another.

The rest of the strings making the URL will be explained later in this

section.

Here is the worksheet before the macro is run:

‘Use ActiveSheet for data; store reference in ws

Set Ws = ActiveSheet

‘Add our query to the worksheet; results should

‘go to column A

‘down from A2; store reference in Wq

Set Wq = Ws.QueryTables.Add(Connection:=Url, _

Destination:=Ws.Range(“A2”))

‘Run our query in the foreground and wait for the

‘query to ﬁ nish

Wq.Refresh BackgroundQuery:=False

End Sub

1061 Information from the Web

And here it is after the macro is run:

The data are retrieved as a single column of text, and the column width

is set to accommodate the data. If we rerun the macro, we get two

columns of data next to each other. Because the destination range was

not empty, Excel inserted a new column to the left of the destination

range, loaded the data into the column, and reset its width to accommo-

date the data.

41.8.2 Get Prices: A Second Approximation

Our ﬁ rst approximation left us with two problems: First, the data are

retrieved as one column of text, a conﬁ guration that is not very conducive

to further analysis as numbers. Second, as we have shown, rerunning

the macro causes duplication of the data in two parallel columns. We

now take a small detour to demonstrate an unofﬁ cial way to solve the

ﬁ rst problem and will return to the proper way shortly. The method

illustrated is “unofﬁ cial” in that it uses a nondocumented behavior in

Excel. Unlike regular queries, which can be set up using the query wizard,

the query we use here can only be set up from a VBA macro. This

1062 Chapter 41

“unofﬁ cial version” may be all you ever need to automate the import of

data from the Web into Excel. It is simple and seems to work with most

versions of Excel.

Here is our “unofﬁ cial” macro; differences from the ofﬁ cial version are

set in bold type on a shaded background for clarity:

Sub GetPricesVer1text()

Dim Ws As Worksheet ‘Our query is going to be

‘stored here

Dim Wq As QueryTable ‘This will be our query

Dim Url As String ‘Full Web address on Yahoo

Url = _

“TEXT;” & _

“http://ichart.ﬁ nance.yahoo.com/table.csv?” & _

“s=QQQQ&” & _

“a=3&b=27&c=2007&” & _

“d=4&e=6&f=2007&” & _

“g=d&” & _

“ignore=.csv”

‘Use ActiveSheet for data; store reference in ws

Set Ws = ActiveSheet

‘Add our query to the worksheet; results should

‘go to column A

‘down from A2; store reference in wq.

Set Wq = Ws.QueryTables.Add(Connection:=Url, _

Destination:=Ws.Range(“A2”))

‘Set the text inport query type to dilimited

Wq.TextFileParseType = xlDelimited

‘Set the text inport dilimiter type to comma

Wq.TextFileCommaDelimiter = True

‘Run our query in the foreground, i.e., wait

‘for the query to ﬁ nish

Wq.Refresh BackgroundQuery:=False

End Sub

1063 Information from the Web

Points of note:

•

The ﬁ rst part of the URL string, the part that sets the type of the query

is not: “URL;” as for a Web query but “TEXT;” as for a text retrieval

query.

•

We added two lines to the macro:

Wq.TextFileParseType = xlDelimited

Wq.TextFileCommaDelimiter = True

These lines set the text-import query type to delimited, and the delimiter

type to comma.

The end result of these changes is that we now have a Web query that

behaves somewhat like a text-import query.

Here is the worksheet before the macro is run:

And here it is after the macro is run:

Notice that the data are retrieved as multiple columns of the appropriate

type and width. If we rerun the macro, we see that the “data-doubling”

bug noticed in the ﬁ rst approximation still exists:

1064 Chapter 41

The data-doubling bug is not disastrous and is easily “hand ﬁ xed.” In

succeeding subsections we show how to automate the process using the

“ofﬁ cial” Microsoft data-import method.

41.8.3 The Second Approximation in the “Ofﬁ cial” Microsoft Way

Another approach to getting the data in columns is to postprocess the

data retrieved by the query. This seems to be the prescribed Microsoft

way and is shown in the following box. Again differences from version

1 are set in bold type on a shaded background for clarity.

Sub GetPricesVer2()

Dim Ws As Worksheet ‘Our query is going to be

‘stored here

Dim Wq As QueryTable ‘This will be our query

Dim Url As String ‘Full Web address on Yahoo

Url = _

“URL;” & _

“http://ichart.ﬁ nance.yahoo.com/table.csv?” & _

“s=QQQQ&” & _

“a=3&b=27&c=2007&” & _

“d=4&e=6&f=2007&” & _

“g=d&” & _

“ignore=.csv”

‘Use ActiveSheet for data; store reference in ws

Set Ws = ActiveSheet

‘Add our query to the worksheet;

‘results should go to column A

‘down from A2; store reference in wq

1065 Information from the Web

Notice the use of Wq.ResultRange; ResultRange is a property of a

QueryTable object that returns a range holding the returned data. Here

is the worksheet before the macro is run:

Set Wq = Ws.QueryTables.Add(Connection:=Url, _

Destination:=Ws.Range(“A2”))

‘Run our query in the foreground, i.e., wait

‘for the query to ﬁ nish

Wq.Refresh BackgroundQuery:=False

‘Parse the text to columns; this is the approved

‘Microsoft way

Wq.ResultRange.TextToColumns _

Destination:=Wq.ResultRange.Range(“A1”), _

DataType:=xlDelimited, _

TextQualiﬁ er:=xlDoubleQuote, _

Comma:=True

‘Fit the column width to the data based on the

‘ﬁ rst 99 values

Ws.Range(“A2:G100”).Columns.AutoFit

End Sub

And here it is after the macro is run:

1066 Chapter 41

Sadly, rerunning the macro is not without problems. The next screen

capture shows the results returned by rerunning the query. The rerun

deposits the data in column A, moving all the existing columns to the

right. The macro then tries to convert the text in column A to numbers,

but since the cells are already occupied by the results of the ﬁ rst run of

the macro, we get the following question:

When we click OK, the resulting worksheet has an extra column that

needs deleting (H) and one column that needs reformatting as numbers

and not dates (B):

We will deal with this annoying behavior later on in this section.

Both approaches (the “ofﬁ cial” and the “unofﬁ cial”) have their merits,

but for the remainder of this section we will use the “ofﬁ cial”

approach.

1067 Information from the Web

41.8.4 Get Prices: A Third Approximation

One improvement our application could deﬁ nitely do with is some

control over the data retrieved. The third approximation gives us control

of some of the relevant parameters for the Yahoo Web site from the user.

It makes use of the user interaction described in Chapter 38. The macro

gets the date of the last sample to be collected from the user. In order

to keep the macro readable and of a manageable size, the actual inter-

action with the user will take place in a function. Notice that six of the

parameters to the Yahoo Web site are affected by the end date and will

be replaced by the results of the function.

The difference between GetPricesVer3 and version 2 is the replace-

ment of two of the parameter strings by the function GetDates( ); the

function call is set in bold type on a shaded background for clarity:

Sub GetPricesVer3()

Dim Ws As Worksheet ‘Our query is going to be

‘stored here

Dim Wq As QueryTable ‘This will be our query

Dim Url As String ‘Full Web address on Yahoo

Url = _

“URL;” & _

“http://ichart.ﬁ nance.yahoo.com/table.csv?” & _

“s=QQQQ&” & _

GetDates() & _

“g=d&” & _

“ignore=.csv”

‘Use ActiveSheet for data; store reference in ws

Set Ws = ActiveSheet

‘Add our query to the worksheet;

‘results should go to column A

‘down from A2; store reference in wq

Set Wq = Ws.QueryTables.Add(Connection:=Url, _

Destination:=Ws.Range(“A2”))

‘Run our query in the foreground,

‘i.e., wait for the query to ﬁ nish

Wq.Refresh BackgroundQuery:=False