ishus
11-13-2012, 08:03 AM
As this is my first post and related to an assignment from a class I am taking, I hope it is within all guidelines of the forum.
The requirements for the assignment were originally to create an efficient frontier from two stock assets using some VBA and Excel Formulas. To extend the assignment and my own learning I was looking to make the sheet more dynamic and reliant on VBA especially because I encounter similar problems at work with manipulating arrays.
The new sheet can accept data up to 10 assets with variable length of historical data. A named range "Assetsrng" was used to make the columns dynamic. I wrote the function "ReturnsCalcMatrix" below to receive the dynamic named range, check whether blank columns exist, and return a matrix of calculated returns for only the columns holding values.
The function "VarCovar" calculates a Covariance Matrix for which I will use Cntrl+Shft+Enter in the sheet to call and return the matrix. The problem is I am not able to use the output of function "ReturnsCalcMatrix" in "VarCovar".
I have tried many things but usually I receive an error stating that when I use the output of "ReturnsCalcMatrix" in the "VarCovar" function I always get an Invalid Qualifier error as the properties .Columns.Count,.Rows.Count,Columns(i) in "VarCovar" are not usable on the output from the other function.
I have tested both functions independently and seem to work when passing ranges from a sheet. Basically, now I want the output of my first function to be an array or format that I can use in the second function.
Have also attached the spreadsheet with some test data, although I have deleted all functions that are not related to this question.
Option Explicit
Function ReturnsCalcMatrix(Assetrng1 As Range) As Variant
Dim LastRow As Integer
Dim i, j As Integer
Dim Count As Integer
Dim ReturnMatrix()
LastRow = Assetrng1.Cells(Assetrng1.Count).Row - 1
Count = 0
For j = 2 To 11
If Worksheets("Sheet1").Cells(2, j).Value <> "" Then
Count = Count + 1
ReDim Preserve ReturnMatrix(LastRow - 3, Count - 1)
For i = 2 To LastRow - 1
ReturnMatrix(i - 2, j - 2) = Log((Worksheets("Sheet1").Cells((i + 1), j).Value) / (Worksheets("Sheet1").Cells(i, j).Value))
Next i
Else
End If
Next j
ReturnsCalcMatrix = ReturnMatrix
End Function
Function VarCovar(Assetrng2 As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
Dim numrows As Integer
numcols = Assetrng2.Columns.Count
numrows = Assetrng2.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(Assetrng2.Columns(i), Assetrng2.Columns(j)) * (numrows / (numrows - 1))
Next j
Next i
VarCovar = matrix
End Function
Thanks for your time!
The requirements for the assignment were originally to create an efficient frontier from two stock assets using some VBA and Excel Formulas. To extend the assignment and my own learning I was looking to make the sheet more dynamic and reliant on VBA especially because I encounter similar problems at work with manipulating arrays.
The new sheet can accept data up to 10 assets with variable length of historical data. A named range "Assetsrng" was used to make the columns dynamic. I wrote the function "ReturnsCalcMatrix" below to receive the dynamic named range, check whether blank columns exist, and return a matrix of calculated returns for only the columns holding values.
The function "VarCovar" calculates a Covariance Matrix for which I will use Cntrl+Shft+Enter in the sheet to call and return the matrix. The problem is I am not able to use the output of function "ReturnsCalcMatrix" in "VarCovar".
I have tried many things but usually I receive an error stating that when I use the output of "ReturnsCalcMatrix" in the "VarCovar" function I always get an Invalid Qualifier error as the properties .Columns.Count,.Rows.Count,Columns(i) in "VarCovar" are not usable on the output from the other function.
I have tested both functions independently and seem to work when passing ranges from a sheet. Basically, now I want the output of my first function to be an array or format that I can use in the second function.
Have also attached the spreadsheet with some test data, although I have deleted all functions that are not related to this question.
Option Explicit
Function ReturnsCalcMatrix(Assetrng1 As Range) As Variant
Dim LastRow As Integer
Dim i, j As Integer
Dim Count As Integer
Dim ReturnMatrix()
LastRow = Assetrng1.Cells(Assetrng1.Count).Row - 1
Count = 0
For j = 2 To 11
If Worksheets("Sheet1").Cells(2, j).Value <> "" Then
Count = Count + 1
ReDim Preserve ReturnMatrix(LastRow - 3, Count - 1)
For i = 2 To LastRow - 1
ReturnMatrix(i - 2, j - 2) = Log((Worksheets("Sheet1").Cells((i + 1), j).Value) / (Worksheets("Sheet1").Cells(i, j).Value))
Next i
Else
End If
Next j
ReturnsCalcMatrix = ReturnMatrix
End Function
Function VarCovar(Assetrng2 As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
Dim numrows As Integer
numcols = Assetrng2.Columns.Count
numrows = Assetrng2.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(Assetrng2.Columns(i), Assetrng2.Columns(j)) * (numrows / (numrows - 1))
Next j
Next i
VarCovar = matrix
End Function
Thanks for your time!