Simon Benninga. Financial Modelling 3-rd edition
Подождите немного. Документ загружается.
1078 Chapter 41
Here is the macro being run:
1079 Information from the Web
Once we click OK, a new worksheet is added. Notice that the active
worksheet in the screen capture is Sheet1, the newly added worksheet,
and not Volatility Ver5 as before.
If we go back to Volatility Ver5 and rerun the macro, the same thing
will happen again. For brevity, only the fi rst and last screen captures are
shown.
1080 Chapter 41
Notice that a new worksheet, Sheet2, is added to the workbook and is
the active worksheet.
Obviously, once we have made use of the data, the added worksheet
should be disposed of, but this and more is the subject of the next
subsection.
41.8.7 Get Returns
The next version of the macro uses a temporary worksheet for the query,
calculates the returns, copies the returns to the current worksheet, and
deletes the temporary worksheet.
Sub GetReturns()
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
Dim Cws As Worksheet ‘To hold our current
‘worksheet
Dim Nrows As Integer ‘the number of rows in the
‘results
Set Cws = ActiveCell.Worksheet
Url = _
1081 Information from the Web
“URL;” & “http://ichart.fi nance.yahoo.com/table.csv?” & _
GetTTS() & _
GetDates() & _
“g=d&” & _
“ignore=.csv”
‘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 A2; 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
‘Number of rows minus 1 = number of returns
Nrows = Wq.ResultRange.Rows.Count - 1
‘insert returns formula in cloumn H down from H3
Ws.Range(“H3”, _
Ws.Cells(Nrows, 8)).FormulaR1C1 =
“=LN(R[-1]C[-1]/RC[-1])”
‘Returns are in column H down from cell H3
‘and for Nrows; so copy it
Ws.Range(Ws.Cells(3, 8), Ws.Cells(Nrows, 8)).Copy
Cws.Select ‘this is the worksheet we started from
‘Paste the values to the worksheet we started
‘from
1082 Chapter 41
The formula that calculates the returns in this line
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
‘Uncomment the next line to stop Excel from
‘verifying
‘the deletion of the temporary worksheet
‘Application.DisplayAlerts = False
‘Clean up; delete the worksheet and the query
‘with it
Ws.Delete
Application.DisplayAlerts = True ‘DO bother me
‘with questions now
End Sub
Ws.Range(“H3”, _
Ws.Cells(Nrows, 8)).FormulaR1C1 = _
“=LN(R[-1]C[-1]/RC[-1])”
uses the R1C1 style of reference. A complete explanation of this style of
reference may be found in appendix 2.
And here are some screen captures of the macro in action:
1083 Information from the Web
1084 Chapter 41
1085 Information from the Web
If we want to prevent Excel from querying the deletion of the tempo-
rary worksheet, we have to uncomment the following line near the end
of the macro:
‘Application.DisplayAlerts = False
41.8.8 Get Volatility
The next version of the macro calculates the historical volatility using
the array function from Chapter 19:
Sub GetVolatility()
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
Dim Cws As Worksheet ‘To hold our current
‘worksheet
1086 Chapter 41
Dim N As Integer ‘A temporary number
Set Cws = ActiveCell.Worksheet
Url = _
“URL; “http://” & _
“ichart.fi nance.yahoo.com/table.csv?” & _
GetTTS() & _
GetDates() & _
“g=d&” & _
“ignore=.csv”
‘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 A2; 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
N = Wq.ResultRange.Rows.Count - 4
‘Insert volatility formula as an array formula in
‘H3
Ws.Range(“H3”).FormulaArray = _
“=SQRT(250)*STDEVP(LN(R[-1]C[-1]:R[“ & _
N - 1 & “]C[-1]/RC[-1]:R[“ & N & “]C[-1]))”
Ws.Range(“H3”).Copy
Cws.Select ‘This is the worksheet we started from
1087 Information from the Web
‘Paste the values to the worksheet we started
‘from
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.DisplayAlerts = False
‘Clean up; delete the worksheet and the query
‘with it
Ws.Delete
Application.DisplayAlerts = True ‘DO bother me
‘with questions now
End Sub