Recall:
These are some of the important group statistics for portfolio optimization.
1. Averages - a $1$ x $n$ matrix whose inputs are the average of each stocks.
2. Covariance Matrix - the vector product of the mean returns transpose and the mean return itself
3. Correlation Matrix - $ {cov_{ij}} / {\sqrt{cov_{ii}cov_{jj}}} $
Using functions in VBA
Sub cov()
Dim str As String
str = Range("D3")
'calling returns
ret = Range(str)
'counting columns
c = UBound(ret, 2)
'Writing Output
Range("B5") = "Averages"
'writing answer to averages
Range("B6").Select
ActiveCell.Range(Cells(1, 1), Cells(1, c)) = AveM(ret) 'it calls for the function Ave and paste it to a
Range("B8") = "Covariance Matrix"
'writing answer to covariance
Range("B9").Select
ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CovarianceM(ret)
Range("B" & 10 + c) = "Correlation Matrix"
'writing answer to correlation
Range("B" & 11 + c).Select
cv = CovarianceM(ret)
ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CorrelationM(cv)
End Sub
Function AveM(ret)
'summing up all values in each stock and then dividing it by the total number of returns
'counting rows and columns
r = UBound(ret)
c = UBound(ret, 2)
'creates 1xc array
a = Range(Cells(1, 1), Cells(1, c))
'summing up all values in row, then divide it by the row
For j = 1 To c
s = 0
For i = 1 To r
s = ret(i, j) + s
Next i
a(1, j) = s / r
Next j
'returns a as the average
AveM = a
End Function
Function CovarianceM(ret)
'covariance matrix = (mean returns transpose x mean returns)/(n-1)
'count rows and columns
r = UBound(ret)
c = UBound(ret, 2)
'create dummy range for mean returns
mret = Range(Cells(1, 1), Cells(r, c))
'create dummy range for average and run the function Ave
a = Range(Cells(1, 1), Cells(1, c))
a = AveM(ret)
'solving for mean returns
For i = 1 To r
For j = 1 To c
mret(i, j) = ret(i, j) - a(1, j)
Next j
Next i
'solving for transpose and the covariance matrix
trans = Application.WorksheetFunction.Transpose(mret)
covm = Application.WorksheetFunction.MMult(trans, mret)
For i = 1 To c
For j = 1 To c
covm(i, j) = covm(i, j) / (r - 1)
Next j
Next i
'returning the covariance matrix
CovarianceM = covm
End Function
Function CorrelationM(covm)
'Correlation Matrix cor_ij = cov_ij / [sqrt(cov_ii)*sqrt(cov_jj)]
'count rows only since covm is an nxn matrix
r = UBound(covm)
'creating dummy range for cor_ij
cor = Range(Cells(1, 1), Cells(r, r))
'solving for correlation matrix
For i = 1 To r
For j = 1 To r
cor(i, j) = covm(i, j) / (covm(i, i) * covm(j, j)) ^ (1 / 2)
Next j
Next i
CorrelationM = cor
End Function
Showing posts with label Covariance. Show all posts
Showing posts with label Covariance. Show all posts
Monday, February 4, 2013
VBA: Covariance Matrix 3
applying what we learned in class to the covariance matrix
This code contains: averages, mean returns, covariance matrix and changing price to returns.
Sub Cov3()
Dim ws As Worksheet
Set ws = Sheets.Add
ws.Range("A1") = "Covariance Matrix"
ws.Range("A3") = "Prices:"
'prices
p = Worksheets("Sheet1").Range("B2:K100")
'counting rows and columns
r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
'pasting price to ws
ws.Range("A5").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
ans.Value = p
'Returns
ws.Range("M3") = "Returns"
'rows of return
r = r - 1
ret = Range("M6:V103")
For i = 1 To r
For j = 1 To c
ret(i, j) = p(i + 1, j) / p(i, j) - 1
Next j
Next i
Range("M6:V103") = ret
'getting the averages and mean returns
ws.Range("X3") = "Average"
ws.Range("AI3") = "Mean Returns"
mret = ws.Range("AI6:AR103")
For j = 1 To c
'average
s = 0
For i = 1 To r
s = s + ret(i, j)
Next i
ave = s / r
Range("X6").Offset(0, j - 1).Value = ave
'mean returns
For kount = 1 To r
mret(kount, j) = ret(kount, j) - ave
Next kount
Next j
ws.Range("AI6:AR103") = mret
'getting the covariance matrix
ws.Range("AT3") = "Covariance"
trans = Application.WorksheetFunction.Transpose(mret)
covm = Application.WorksheetFunction.MMult(trans, mret)
For i = 1 To c
For j = 1 To c
covm(i, j) = covm(i, j) / (r - 1)
Next j
Next i
Range("AT6").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
ans.Value = covm
End Sub
This code contains: averages, mean returns, covariance matrix and changing price to returns.
Sub Cov3()
Dim ws As Worksheet
Set ws = Sheets.Add
ws.Range("A1") = "Covariance Matrix"
ws.Range("A3") = "Prices:"
'prices
p = Worksheets("Sheet1").Range("B2:K100")
'counting rows and columns
r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
'pasting price to ws
ws.Range("A5").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
ans.Value = p
'Returns
ws.Range("M3") = "Returns"
'rows of return
r = r - 1
ret = Range("M6:V103")
For i = 1 To r
For j = 1 To c
ret(i, j) = p(i + 1, j) / p(i, j) - 1
Next j
Next i
Range("M6:V103") = ret
'getting the averages and mean returns
ws.Range("X3") = "Average"
ws.Range("AI3") = "Mean Returns"
mret = ws.Range("AI6:AR103")
For j = 1 To c
'average
s = 0
For i = 1 To r
s = s + ret(i, j)
Next i
ave = s / r
Range("X6").Offset(0, j - 1).Value = ave
'mean returns
For kount = 1 To r
mret(kount, j) = ret(kount, j) - ave
Next kount
Next j
ws.Range("AI6:AR103") = mret
'getting the covariance matrix
ws.Range("AT3") = "Covariance"
trans = Application.WorksheetFunction.Transpose(mret)
covm = Application.WorksheetFunction.MMult(trans, mret)
For i = 1 To c
For j = 1 To c
covm(i, j) = covm(i, j) / (r - 1)
Next j
Next i
Range("AT6").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
ans.Value = covm
End Sub
Saturday, February 2, 2013
VBA: Covariance Matrix 2
Here is the second attempt in programming the covariance matrix. I still have problems with getting the returns and dynamic averages.
Sub COV2M()
Dim ws As Worksheet
Set ws = Sheets.Add
ws.Range("A1") = "Covariance Matrix"
ws.Range("A3") = "Prices:"
'prices
p = Worksheets("Sheet1").Range("B2:K100")
'counting rows and columns
r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
'pasting price to ws
ws.Range("A5").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
ans.Value = p
'Returns
ws.Range("M3") = "Returns"
'rows of return
r = r - 1
ret = Range("M6:V103")
For i = 1 To r
For j = 1 To c
ret(i, j) = p(i + 1, j) / p(i, j) - 1
Next j
Next i
Range("M6:V103") = ret
'getting the averages
ws.Range("X3") = "Average"
ave = ws.Range("X6:AG6")
For j = 1 To c
s = 0
For i = 1 To r
s = ret(i, j) + s
Next i
ave(1, j) = s / r
Next j
ws.Range("X6:AG6") = ave
'getting the mean Returns
ws.Range("AI3") = "Mean Returns"
mret = ws.Range("AI6:AR103")
For i = 1 To r
For j = 1 To c
mret(i, j) = ret(i, j) - ave(1, j)
Next j
Next i
ws.Range("AI6:AR103") = mret
'getting the covariance matrix
ws.Range("AT3") = "Covariance"
trans = Application.WorksheetFunction.Transpose(mret)
covm = Application.WorksheetFunction.MMult(trans, mret)
For i = 1 To c
For j = 1 To c
covm(i, j) = covm(i, j) / (r - 1)
Next j
Next i
Range("AT6").Select
Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
ans.Value = covm
End Sub
Friday, February 1, 2013
VB: Covariance Matrix
This is getting the Covariance Matrix using the Record Macro. It is to analyze how it is being done in VBA.
Sub CovMatrix()
'
' CovMatrix Macro
' Fixed, Relative References is not used
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "Covariance Matrix given Prices"
'Prices
Range("B4").Select
ActiveCell.FormulaR1C1 = "1. Get the Prices"
Range("B6").Select
Sheets("Sheet1").Select
Range("K100").Select
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("VBA").Select
ActiveSheet.Paste
Range("N4").Select
Application.CutCopyMode = False
'Returns
ActiveCell.FormulaR1C1 = "2. Change it into Returns"
Range("N7").Select
ActiveCell.FormulaR1C1 = "=RC[-12]/R[-1]C[-12]-1"
Range("N7").Select
Selection.Copy
Range("N8:W104").Select
ActiveSheet.Paste
Range("O7:W7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Averages
Range("Y4").Select
ActiveCell.FormulaR1C1 = "3.Get the Averages"
Range("Y7").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11]:R[97]C[-11])"
Range("Y7").Select
Selection.Copy
Range("Z7:AH7").Select
ActiveSheet.Paste
Range("Y7:AH7").Select
Application.CutCopyMode = False
'Mean adjusted Return
Range("AJ4").Select
ActiveCell.FormulaR1C1 = "4.Mean Adjusted Returns"
Range("AJ7").Select
ActiveCell.FormulaR1C1 = "=RC[-22]-R7C[-11]"
Range("AJ7").Select
Selection.Copy
Range("AJ8:AJ104").Select
ActiveSheet.Paste
Range("AK7:AS104").Select
ActiveSheet.Paste
Range("AU5").Select
Application.CutCopyMode = False
'covariance Matrix
Range("AU4").Select
ActiveCell.FormulaR1C1 = "5. Get the Covariance matrix"
Range("AU7:BD16").Select
Selection.FormulaArray = _
"=MMULT(TRANSPOSE(RC[-11]:R[97]C[-2]),RC[-11]:R[97]C[-2])/(COUNT(RC[-11]:R[97]C[-11])-1)"
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
End Sub
Thinking of a dynamic way to program this Covariance. Somehow copy and paste seems to be the easier way. Though when reading the code by itself and applying it using relative references, it doesn't make sense. So I have to go back to arrays and matrices.
Sub CovMatrix()
'
' CovMatrix Macro
' Fixed, Relative References is not used
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "Covariance Matrix given Prices"
'Prices
Range("B4").Select
ActiveCell.FormulaR1C1 = "1. Get the Prices"
Range("B6").Select
Sheets("Sheet1").Select
Range("K100").Select
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("VBA").Select
ActiveSheet.Paste
Range("N4").Select
Application.CutCopyMode = False
'Returns
ActiveCell.FormulaR1C1 = "2. Change it into Returns"
Range("N7").Select
ActiveCell.FormulaR1C1 = "=RC[-12]/R[-1]C[-12]-1"
Range("N7").Select
Selection.Copy
Range("N8:W104").Select
ActiveSheet.Paste
Range("O7:W7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Averages
Range("Y4").Select
ActiveCell.FormulaR1C1 = "3.Get the Averages"
Range("Y7").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11]:R[97]C[-11])"
Range("Y7").Select
Selection.Copy
Range("Z7:AH7").Select
ActiveSheet.Paste
Range("Y7:AH7").Select
Application.CutCopyMode = False
'Mean adjusted Return
Range("AJ4").Select
ActiveCell.FormulaR1C1 = "4.Mean Adjusted Returns"
Range("AJ7").Select
ActiveCell.FormulaR1C1 = "=RC[-22]-R7C[-11]"
Range("AJ7").Select
Selection.Copy
Range("AJ8:AJ104").Select
ActiveSheet.Paste
Range("AK7:AS104").Select
ActiveSheet.Paste
Range("AU5").Select
Application.CutCopyMode = False
'covariance Matrix
Range("AU4").Select
ActiveCell.FormulaR1C1 = "5. Get the Covariance matrix"
Range("AU7:BD16").Select
Selection.FormulaArray = _
"=MMULT(TRANSPOSE(RC[-11]:R[97]C[-2]),RC[-11]:R[97]C[-2])/(COUNT(RC[-11]:R[97]C[-11])-1)"
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
End Sub
Thinking of a dynamic way to program this Covariance. Somehow copy and paste seems to be the easier way. Though when reading the code by itself and applying it using relative references, it doesn't make sense. So I have to go back to arrays and matrices.
Subscribe to:
Posts (Atom)