Averages, Covariance and Correlation

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