Thread: Covariance-Variance Stock Prices - VBA code

Hello,

I try to find an optimized portfolio strategy. This work need a covariance matrix between different stock prices in a period of time.

For example , see the file attached, I need a covariance matrix ( I have prepared it on the sheet2) between the 37 different stock prices (sheet1) during a period of time.
For example since the 25/08/2011 to the 27/10/2011, but what is pretty diffcult is that I need a general code where I cStockMarketPrices.xlsxould enter the dates I want (For example by an InputBox) and the matrix appear.

Why I need that ? Because I will have to change the period many times.

I find approximately how to do the matrix, typing directly the equation on excel but I can't find the VBA code to do it , and I need a VBA code?


If someone could help, It would be very kind of him,

I just let you see the file attached


Thank you

Does someone understand my question ?

Please I very need help

up !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! sorry but I need it very rapidly . Is there any good mathematician ?

may be i Could help from this :

Sub Test()
Dim dArrData(1 To 100, 1 To 10) As Double
Dim dAutoCoVar() As Double
Dim j As Long, k As Long

' fill the array with some bogus data
Randomize
For j = 1 To 100
For k = 1 To 10
dArrData(j, k) = Rnd
Next k
Next j

' write to the worksheet, for debug
Range("A1:J100").Value = dArrData

' calculate the autocovariance matrix
dAutoCoVar = Autocovar(dArrData)

' write to the worksheet, for debug
Range("L1:U10").Value = dAutoCoVar
End Sub

Function Autocovar(dArrData() As Double) As Double()
Dim dArrResult() As Double
Dim j As Long, k As Long

' redim the result array as a square array.
ReDim dArrResult(1 To UBound(dArrData, 2), 1 To UBound(dArrData, 2))

' calculate the autocovariance matrix
For j = 1 To UBound(dArrData, 2)
For k = 1 To UBound(dArrData, 2)
With Application.WorksheetFunction
dArrResult(j, k) = .Covar(.Index(dArrData, 0, j), .Index(dArrData, 0, k))
End With
Next k
Next j
Autocovar = dArrResult
End Function

Are you asking for something like a pop up form into which the user would enter two dates, then the macro populates the differences for each stock's price into your list?

I am looking for a pop up form into which the user enter the date of the end of the période because the beginning of the period is always the same : 25/08/2011. After that the code would calculate a covariance matrix between the 37 stocks