Simon Benninga. Financial Modelling 3-rd edition
Подождите немного. Документ загружается.
1068 Chapter 41
Note that the lines from GetPricesVer2()
‘Parse the text to columns;
‘this is the approved Microsoft way
Wq.ResultRange.TextToColumns _
Destination:=Wq.ResultRange.Range(“A1”), _
DataType:=xlDelimited, _
TextQualifi er:=xlDoubleQuote, _
Comma:=True
‘Fit the column width to the data based on the
‘fi rst 99 values
Ws.Range(“A2:G100”).Columns.AutoFit
End Sub
“a=3&b=27&c=2007&” & _
“d=4&e=6&f=2007&” & _
were replaced by
GetDates() & _
The function GetDates() follows:
Function GetDates()
Const p As String = _
“Please enter last date to be retrieved”
Dim Temp
Dim ED ‘ end day here
Dim EM ‘ end month here
Dim EY ‘ end year
Dim SD ‘ start day here
Dim SM ‘ start month here
1069 Information from the Web
Points of note:
•
The VBA function IsDate returns TRUE if its argument is or can be
converted to a Date Value; see appendix 1 for a full description.
•
The VBA functions Day, Month, and Year return the appropriate part
of their argument as a number; see appendix 1 for a full description.
•
The variable Title used in the call to InputBox is defi ned as a global
constant at the beginning of the module; it is going to be used as the title
of all the input and message boxes in the application. Here is the defi ni-
tion line:
Dim SY ‘ start year
‘Ask for a date; use current date as a default
Temp = InputBox(p, Title, Date)
‘Did we get the correct answer?
‘If so, use its date value; if not, use current
‘date value
If IsDate(Temp) Then
Temp = DateValue(Temp)
Else
Temp = Date
End If
‘Compute end and start day, month, and year;
‘Start date is 30 days before end date
ED = Day(Temp)
EM = Month(Temp) - 1 ‘ Yahoo month is 0-11
EY = Year(Temp)
SD = Day(Temp - 30)
SM = Month(Temp - 30) - 1 ‘ Yahoo month is 0-11
SY = Year(Temp - 30)
‘Compile the result string from the parameters
GetDates = “a=” & SM & “&b=” & SD _
& “&c” & SY & “&d=” & EM & “&e=” _
& ED & “&f” & EY & “&”
End Function
1070 Chapter 41
Here is the worksheet before the macro is run:
‘This global constant
‘is used by more than one function
Const Title As String = _
“An Application - Get Historical Volatility”
When we run the macro we are presented with an input box, into which
we have inserted the date.
3
Following is the worksheet after we enter a date and select OK in the
input box.
3. The input box accepts the following date versions: 22 May 2005, 22-May-2005, 22-5-
2005, 22/5/2005, and more.
1071 Information from the Web
If you rerun this macro, you will see that it does not completely solve
the problems of data duplication and other deviant behavior. We will
deal with these problems in the following pages.
41.8.5 Get Prices: The Penultimate Approximation
This version adds input routines for the number of samples and the
ticker-tape symbol; once again the additions and alterations from version
3 are set in bold type on a shaded background.
1072 Chapter 41
Sub GetPricesVer4()
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.fi nance.yahoo.com/table.csv?” & _
GetTTS() & _
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 fi 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, _
TextQualifi er:=xlDoubleQuote, _
Comma:=True
‘Fit the column width to the data based on the
‘fi rst 99 values
Ws.Range(“A2:G100”).Columns.AutoFit
End Sub
1073 Information from the Web
Function GetDates()
Const p As String = _
“Please enter Date of last sample”
Dim Temp
Dim ED ‘ end day here
Dim EM ‘ end month here
Dim EY ‘ end year
Dim SD ‘ start day here
Dim SM ‘ start month here
Dim SY ‘ start year
Dim NDays ‘the number of calendar days to sample
‘Ask for a date; use current date as a default
Temp = InputBox(p, Title, Date)
‘Did we get the correct answer?
‘If so, use its date value; if not, use current
‘date value
If IsDate(Temp) Then
Temp = DateValue(Temp)
Else
Temp = Date
End If
‘Get the number of calendar days to sample
NDays = GetDays()
ED = Day(Temp)
EM = Month(Temp) - 1 ‘ Yahoo month is 0-11
EY = Year(Temp)
SD = Day(Temp - NDays)
SM = Month(Temp - NDays) - 1 ‘ Yahoo month is
‘ 0-11
SY = Year(Temp - NDays)
GetDates = “a=” & SM & “&b=” & SD _
& “&c” & SY & “&d=” & EM & “&e=” _
& ED & “&f” & EY & “&”
End Function
1074 Chapter 41
And here are the two new functions GetTTS ( ) and GetDays ( ):
Function GetTTS()
Const p As String = _
“Please enter” & “Ticker-Tape Symbol”
Dim Temp
‘Ask for a TTS; use QQQQ as a default
Temp = InputBox(p, Title, “QQQQ”)
‘Did we get an answer? If so, use it; if
‘not, use “QQQQ”
If Temp = “” Then
Temp = “QQQQ”
End If
GetTTS = “s=” & Temp & “&”
End Function
Function GetDays()
Const p As String = _
“Please enter number of” & _
“calendar days to sample”
Dim Temp
‘Ask for the number of samples; use 15
‘as a default
Temp = InputBox(p, Title, 15)
‘Did we get the correct answer? If so,
‘use it; if not, use 15
If IsNumeric(Temp) Then
GetDays = Temp
Else
GetDays = 15
End If
End Function
Points of note:
•
The VBA function IsNumeric returns TRUE if its argument is or can
be converted to a numeric value; see appendix 1 for a full description.
1075 Information from the Web
•
The variable Title used in the call to InputBox in all three user inter-
action functions is defi ned as a global constant at the beginning of the
module.
Here is the macro when run:
1076 Chapter 41
Here is the worksheet once the macro has run:
41.8.6 Get Prices: Using a New Worksheet for the Data
The time has come to deal with the “interesting behavior” of our macros
when they are rerun. The reason behind this behavior is that Excel is
trying not to overwrite information already in the worksheet. The easiest
way to guarantee consistent results from our macro is for it to create and
use its own worksheet. Here is the fi rst version of a modifi ed macro:
Sub GetPricesVer5()
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.fi nance.yahoo.com/table.csv?” & _
GetTTS() & _
GetDates() & _
“g=d&” & _
“ignore=.csv”
1077 Information from the Web
Once we run the macro, a new worksheet is added to the workbook, and
the data are deposited and formatted in it. Here is the workbook before
the macro is run (notice that the active worksheet is Volatility Ver5):
‘Create a new worksheet for the data; store
‘reference in Ws
Set Ws = Worksheets.Add
‘Add our query to the worksheet; results should
‘go to column A
‘down from A1; store reference in wq
Set Wq = Ws.QueryTables.Add(Connection:=Url, _
Destination:=Ws.Range(“A1”))
‘Run our query in the foreground, i.e., wait
‘for the query to fi 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, _
TextQualifi er:=xlDoubleQuote, _
Comma:=True
‘Fit the column width to the data based on the
‘fi rst 100 values
Ws.Range(“A1:G100”).Columns.AutoFit
End Sub