相關系數矩陣計算與計算機實作
How to Calculate the variance-Covariance Matrix using Excel and Visual Basic for Applications Aim We have seen, in the previous lectures on multi-asset portfolios, that the variance/covariance matrix can be used to calculate the variance of a multi-asset portfolio. However, it was not shown how to calculate the variance/covariance matrix in the previous section (just how to use it when provided). Therefore, in this lecture, our aim is to show how to calculate the variance/covariance matrix. Learning objectives To understand the equivalence between the excess matrix and the covariance formula. To be able to calculate the co/variance matrix in Excel using the excess matrix and the transpose function. To be able to calculate the co/variamce matrix in VBA. (1) The Equivalence between using an excess matrix and the covariance formula (1.1) The Excess Matrix. (1.2) The Covariance Formula. (1.3) An Example to show they are equivalent. (1.1) The Excess Matrix The Matrix of Excess returns: (1.2) The Covariance Formula How is the excess return matrix used in the calculation of the co/variance matrix? To answer this question lets consider the definition of covariance and apply a simple case when M=2. Calculate the Variance-Covariance Matrix using the Excess Return Matrix The co/variance formula is calculated by (where A is the excess matrix): Calculating the covariance matrix in Excel using the Excess Matrix and the Transpose function Variance/Covariance Matrix Calculating the covariance matrix in Excel using VBA Introduction to VBA in Excel: Defining names in Excel Basic programming constructs VBA Excel specific commands The VBA debugger The parts of the VBA program: Average Return Calculation of Excess Return Matrix Transpose of Excess Return Matrix Calculation of Variance Covariance Defining names in Excel Defining names in Excel cont’d Basic VBA constructs This should be revision for most of you. The readings at the end of the chapter have more details about bas