
How To Calculate Variance Covariance Matrix?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
How To Calculate Variance Covariance Matrix?  Excel

View Answers


Hi board,
I want to calculate the yearly standard deviation of a portfolio with a few hundred stocks. I plan to first calculate the variance covariance matrix for the portfolio's stocks and then use matrix algebra (like here http://www.fenews.com/fen39/back_to_...nformulas.htm) to arrive at the porfolio's standard deviation.
I have a large array with stock returns that looks like this:
StockID;Year;Month;Return;Portfolio
123456;1990;1;0.05;1
234567;1990;1;0.06;1
345678;1990;1;0.02;2
...
If there would be 100 different stock IDs, I would like to calculate a 100x100 matrix that contains the covariances of the returns of the stocks of that portfolio for a specific year.
I did some experiments with array formulas like {=cov(if(stockID=...);if(stockID=...))} but that didn't work. I couldn't find any plugins that would help. Any ideas?
Thanks alot in advance!
Similar Excel Video Tutorials
MMULT function Matrix Algebra
 See how to use the array function MMULT to multiple Matrices in Matrix Algebra. This is an array function that requires the Ctrl + Shift + Enter trick ...
Sample Standard Deviation (Variability)
 Calculate Deviations, Variance and Standard Deviation for a sample and a population using Excel tables and the VAR, STDEV, AVERAGE, VARP, STDEVP, COU ...
Stock Portfolio Analysis
 See how to create array formulas for Portfolio Return & Standard Deviation. See the array functions: MMULT, COLUMN, TRANSPOSE. See how to calculat ...
Similar Topics
Hi guys,
I need to calculate standard deviation for a portfolio with 31 stock. I have a column with the stock names (column B), their mean return (column F), standard deviation (column G) and 31*31 correlation matrix.
Is there a convenient way to calculate this stuff?
Thanks a lot in advance!
Hello,
I have calculated stock returns, but now I want to calculate the portfolio returns with the same stocks. In the attachment I have added my stocks and returns.
I want to invest in one stock at a time. Buying a stock should only happen at moments that my excel sheet has a return in the same row as an open. I want to buy stocks when the returns are positive but also when they are negative.
When a stock is bought, I want to sell it at the first close. Then, another (or the same) stock can be bought.
Can anyone help me with the calculation of portfolio returns?
Hello,
I have calculated stock returns, but now I want to calculate the portfolio returns with the same stocks. In the attachment I have added my stocks and returns.
I want to invest in one stock at a time. Buying a stock should only happen at moments that my excel sheet has a return in the same row as an open. I want to buy stocks when the returns are positive but also when they are negative.
When a stock is bought, I want to sell it at the first close. Then, another (or the same) stock can be bought.
Can anyone help me with the calculation of portfolio returns?
Good Morning,
I am currently using solver in my vba code to solve for optimal weights on a portfolio of assets. All of the calculations are currently being performed on an excel worksheet. I am looking to take the calculation off of the worksheet and just handle it within arrays in VBA. I am wondering how this can be done as it seems that arrays in VBA don't automatically recalculate when a value changes in one of the arrays, which will happen as Solver begins iterating through the different portfolio weights. Is it possible to have an array that will recalculate?
Just for some background on what I have going on in the worksheet that I would like to move to VBA Arrays. I have a vector of mean returns, a variance/covariance matrix, a vector of portfolio weights. The formula for mean return is:
{=MMULT(TRANSPOSE(Vector of weights),Vector of mean returns)}
and for portfolio variance:
{=MMULT(MMULT(TRANSPOSE(Vector of Weights),Covariance Matrix), Vector of Weights)}
Solver sets variance to Min by changing the vector of portfolio weights.
Hey everyone I am new to programing in Excel and I do not know enough to wright a function that finds variance of a portfolio. This is what i have so far its messy and does not work.
Function VARPORT(Sa,Wa,Sb,Wb,Sc,Wc,Sd,Wd,Se,We)
va=VAR(Sa)
vb=VAR(Sb)
vc=VAR(Sc)
vd=VAR(Sd)
ve=VAR(Se)
cab=Covar(va,vb)
cac=Covar(va,vc)
cad=Covar(va,vd)
cae=Covar(va,ve)
cbc=Covar(vb,vc)
cbd=Covar(vb,vd)
cbe=Covar(vb,ve)
ccd=Covar(vc,vd)
cce=Covar(vc,ve)
cde=Covar(vd,ve)
VAPORT=((Wa^2)*va)+((Wb^2)*vb)+((Wc^2)*vc)+((Wd^2)*vd)+((We^2)*ve)+(2*Wa*Wb*cab)+(2*Wa*Wc*cac)+(2*Wa *Wd*cad)+(2*Wa*We*cae)+(2*Wb*Wc*cbc)+(2*Wb*Wd*cbd)+(2*Wb*We*cbe)+(2*Wc*Wd*ccd)+(2*Wc*We*cce)+(2*Wd*W e*cde)
End Function
The S stands for a list of stock returns.
The W is what percentage of the portfolio is spent on that stock.
The Covar and Var functions are to calculate the variance of each stock and the covariance between the five.
The three letters are C for covariance and then what stock will be looked at.
This is very confusing to me and I know I just made a big mess, can anyone help or point me int he right direction.
Hi, i'm trying to plot the Markowitz efficient frontier in Excel. I have returns and other data for 20 stocks.
I use solver to find the optimal weights of the 20 stocks for the the lowest variance portfolio.
I need to then find the weights of the stocks from this point to the highest possible return portfolio.
Finding the portfolio with the smallest variance and with the highest return is fine with solver, i just use the min and max functions.
How do i find the points in between?
For example, the return on my minimum variance portfolio is 1, the return on my max return portfolio is 100. Right now, i would need to run optimizer with minimum variance for the returns of 2,3,4....all the way to 99 to find those points.
Any shortcuts or add ins?
Thanks
I am trying to calculate a covariance matrix in VBA. I have managed to get my data into an array "datarng" which has 10 columns and 100 rows. I want to calculate the covariance matrix of my dataset so that I will have a new array 10x10 by calculating the covariance between each column in the original array. can anyone help?
Dear Most Amazing Excel Team,
I have data for calculating the Standard Deviation for a Portfolio of stocks. My data is in cells A1 to E6 and looks like this:
....... Weight 0.3 0.4 0.3
....... P( ) E(R) A E(R) B E(R) C
Boom 0.15 0.3 0.45 0.33
Good 0.45 0.12 0.1 0.15
Poor 0.35 0.01 0.15 0.05
Bust 0.05 0.2 0.3 0.09
My Array formula, that is entered in cell B8, looks like this:
{=SQRT(((SUM(C1:E1*C3:E3)SUM(C3:E6*C1:E$1*B3:B6))^2)*B3+((SUM(C1:E1*C4:E4)SUM(C3:E6*C1:E1*B3:B6))^2)*B4+((SUM(C1:E1*C5:E5)SUM(C3:E6*C1:E1*B3:B6))^2)*B5+((SUM(C1:E1*C6:E6)SUM(C3:E6*C$1:E$1*B3:B6))^2)*B6)}
This works, but there must be a more elegant and compact way to do this?
I am trying to calculate the covariance of a stocks return with the entire portfolio using the COVAR() command... is there a way to exclude text from an arraywithout altering the data ? It would be particlularly useful when using the MMULT command as well.
cheers for any ideas
anybody know.. if you already have a mutual funds' 3 or 5year standard deviation, how to calculate the overall portfolio's standard deviation?
Hi, new to this forum, so I guess I'm probably going over well trodden ground here  sorry. I couldn't find anything in the recommended search.
starting with
one (n,n) matrix (A)
one (n,1) array (Z)
For info,
A is correlations between different asset classes
Z is the standard deviation of different asset classes
my end goal is the covariance matrix between different asset classes
I would like to multiply A by the standard deviation values of the corresponding asset classes.
eg
a1*z1*z1 , b1*z2*z1 , c1*z3*z1
a2*z1*z2 , b2*z2*z2 , c1*z3*z1
a3*z1*z3 , b3*z2*z3 , c1*z3*z1
I have done this by transposing Z, [Z(T)] in to a (1,n) matrix and then multiplied A by Z by Z(T).
Is there a way of bypassing the creation of the Z transposed?
Shyam
Hi all!
First of all, I apologize if I'm unclear and for potential abuse of the English language.
I want to test a trading strategy called momentum where you buy/sell stocks based on their past performance. Using historical data, I'm supposed to "buy/sell" the previously best/worst 10% performing stocks, a procedure which is repeated each month. These portfolios are then held for a period whereafter I calculate the portfolio return.
Since the number of available stocks vary, I'm struggling to come up with a good method.
Do you have any suggestions as to how I can include 10% of the stocks into a portfolio when the number of stocks vary? I have to use integers, so rounding off to the closest integer is neccessary.
I hope you are able to understand my problem and that you can offer me some help.
Regards
Hello,
I want to test results of a portfolio of stocks in the past. I have uploaded an example sheet.
I want to invest in one stock at a time and I want the portfolio to buy when the sheet says OPEN (buy) en sell we the sheet says CLOSE (sell).
If in a row of stock there is an OPEN and a CLOSE, the share should not be bought. If it only says OPEN, the stock should be bought.
When a stock is bought, a new stock can only be bought when the previous stock is sold. (stocks should be sold at the next CLOSE of that stock).
In the example first stock one is bought and sold at row 23. After row 23 a new stock can be bought. Stocks should only be bought if next to open is a positive or negative return (<>0). In the example the second positions should be taken for stock 1 in row 32.
Can anyone help me with this problem?
Hello, I am new to VBA and I want to write a code that computes the semicovariance matrix of my return data..
The semicovariance between two stocks (i and j) is calculated as below:
average of: (min(Ri  avg(Ri), 0) * (min(Rj  avg(Rj), 0)
where Ri and Rj are the return observations of stocks i and j respectively..
Using Excel functions, I have calculated the semicovariance between stocks i and j using the following formula:
=AVERAGE((IF(Riavg(Ri)
' I need to implement another calculation into this code but fail to 'understand exactly what part needs adjusting and how to write new code 'for the new calculation. Any?
Option Explicit 'Forces the need to declare every variable
Option Base 1 'Forces arrays to start at 1 instead of 0 (vba default)
Sub ValueAtRisk()
'Declarations. Notes dynamic arrays declared by simply adding () after the name
Dim returns() As Double, Covar() As Double, dSubReturns() As Double, vol As Double
Dim iDays As Long, iNames As Long, i As Long, j As Long, n As Long, iWindow As Long
Dim Exp As Double, T As Double, CI As Double
Dim stocks As Range, weights As Range, cel As Range
On Error GoTo ErrHandler 'On error skips to end of subroutine and does something sensible
Application.ScreenUpdating = False 'Massively speeds up big loops as not constantly update the screen
'Read in data from spreadsheet to the variables used here
Exp = Range("exp").Value 'Note a named range is used instead of Range("B7").Value
T = Range("t").Value
CI = Range("ci").Value
Set stocks = Range("stocks") 'Note the Set word is necessary to assign ranges
Set weights = Range("weights")
iWindow = Range("window").Value
'Note: add code here to check that input values are sensible (ie no negative exposures etc)
'Set array sizes using a count of number of days and names in portfolio (in the stocks named range)
iDays = stocks.Rows.Count
iNames = stocks.Columns.Count
ReDim returns(iDays, iNames) 'Redimension the dynamic array to the correct size
ReDim Covar(iNames, iNames) 'Redim the Covariance matrix to by NxN matrix
ReDim dSubReturns(iWindow, iNames) 'Also Redim the window array
'Loop through stocks range and calc the daily returns and place answer in returns array
'Note the generic bounds of the i and j loops means that it does not matter how many
'days of data or names in the stocks range, so 10 names over 20 years would also be handled.
'Just remember to change the stocks named range in the spreadsheet to include all the new data.
For i = LBound(returns, 1) + 1 To UBound(returns, 1)
For j = LBound(returns, 2) To UBound(returns, 2)
returns(i, j) = (stocks.Cells(i, j).Value  stocks.Cells(i  1, j).Value) _
/ stocks.Cells(i  1, j).Value
Next j
Next i
'The n loop starts at the end of the stocks range (today) and steps back one day at a time
'calculating the rolling iWindow (eg 250) day ValueatRisk for all days in the data.
For n = iDays To (iWindow + 1) Step 1
'Find the cell to return the VaR to. This will return the var in the next column to the left.
Set cel = Range("stocks").Cells(n, iNames).Offset(0, 1)
'Fill an additional array with data from the part of returns() we are interested in.
'We do this because the user defined functions (UDF) below calculate the VaR over ALL the data they
'are passed, and if we passed them the returns matrix you would get a 10yr VaR instead of 250days
'hence we create a subarray that contains only the data we are interested in.
For i = LBound(dSubReturns, 1) To UBound(dSubReturns, 1)
For j = LBound(dSubReturns, 2) To UBound(dSubReturns, 2)
dSubReturns(i, j) = returns(n  i + 1, j)
Next j
Next i
'Pass data to UDF
Covar = VCVMatrix(dSubReturns)
Range("vcv").Value = Covar 'write data to spreadsheet so we can see the vcv matrix
'Calc VaR and write the cell called cel
vol = Sqr(PortfolioVariance(weights, Range("vcv"))) 'the UDF takes two ranges
cel.Value = Exp * Application.NormSInv(1  CI) * Sqr(T) * vol 'the standard calculation
Next n
'Tidy up
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "Error in ValueAtRisk subroutine!"
Application.ScreenUpdating = True
End Sub
Function VCVMatrix(retsmat)
' Returns nxn sample VarianceCovariance Matrix
Dim i As Integer, j As Integer, k As Integer, nc As Integer, nr As Integer
Dim r1vec() As Variant, r2vec() As Variant, Vmat() As Double
'nc = retsmat.Columns.Count
nc = UBound(retsmat, 2)  LBound(retsmat, 2) + 1
ReDim Vmat(nc, nc)
'nr = retsmat.Rows.Count
nr = UBound(retsmat, 1)  LBound(retsmat, 1) + 1
ReDim r1vec(nr)
ReDim r2vec(nr)
For i = 1 To nc
For k = 1 To nr
r1vec(k) = retsmat(k, i)
Next k
Vmat(i, i) = Application.Var(r1vec)
For j = i + 1 To nc
For k = 1 To nr
r2vec(k) = retsmat(k, j)
Next k
Vmat(i, j) = Application.Covar(r1vec, r2vec) * (nr / (nr  1))
Vmat(j, i) = Vmat(i, j)
Next j
Next i
VCVMatrix = Vmat
End Function
Function PortfolioVariance(wtsvec, vcvmat)
' Returns the portfolio variance
Dim v0 As Variant, v1 As Variant
If Application.Count(wtsvec) = vcvmat.Rows.Count Then
If wtsvec.Columns.Count > wtsvec.Rows.Count Then wtsvec = Application.Transpose(wtsvec)
v0 = Application.Transpose(wtsvec)
v1 = Application.MMult(v0, vcvmat)
PortfolioVariance = Application.SumProduct(v1, v0)
Else
PortfolioVariance = 1
End If
End Function
I'm obtaining values of a portfolio by performing a matrix multiplication of asset prices and asset quantities/volumes.
[Let's say the Prices matrix is of dimension 500 (rows) X 10 (columns), so essentially 500 days of data for 100 different assets. The quantities matrix is of dimension 1 (row) X 10 (columns). So the matrix multiplication will be mmult(prices,transpose(quantities)) Making the vector of portfolio values of dimension 500 (rows) X 1 (column).]
The problem is that the Prices matrix will be continuously growing, as more and more data become available. I want to update the vector of portfolio values with a macro, so that the portfolio values vector grows if the Prices matrix have grown. I can make sure I have all price values in a matrix by making a named range using offset. BUT, how can i make the matrix multiplication when I don't know the size of the matrix?
I was thinking you might be able to use "Evaluate", but i'm not quite sure how that works, any ideas?
I've attached an example workbook. I would like to make a macro for the button (in the portfolio values sheet) that will update the portfolio values and the number of dates in the same sheet to match the number of rows in the prices sheet (you'll have to simulate the addition of values to the prices matrix yourself).
There might be a much easier solution to this problem that i haven't thought of.. Any ideas are welcome. Thank you for your help!
HI all, any help is greatly appreciated. I have a bloomberge excell question. I have created an automated equity portfolio that updates monthly. So at the end of every month new stocks will enter the portfolio and be held till the end of that month when it will update agian. I want to backtest this model and I have completed the hypothetical stock selection going back to '92. Right now I have date (all end of month dates) in one collumn and then in the three columns next two it, the tickers for the aplicable stocks.
my question is how do I now actually simulate the portfolio given its choices and also given the price of all the possible equities going back to '92 in a seperate worksheet.
Thank YOU
Michael
Hello everyone,
I hope someone can be of assistance in this problem:
I have an m X n matrix. I have the mean and standard deviation of this matrix. Without writing code, how can I look through the matrix and pull (and copy to a cell) every value above one standard deviation.
If anyone has suggestions, your help would be highly appreciated.
Thank you,
Isaac
I can calculate the covariance matrix from a series of numbers nxm with the adin formula (covar) but need to fill the places above the diagonal, any sugestion?
I need to calculate standard deviation (which I can handle) plus the alpha and beta of the returns on an investment portfolio. I have the actual returns for both the subject portfolio and the benchmark. Any thoughts on how to handle this?
Thanks in advance.
jim
Hi
I have a list of 107 stocks and have generated a correlation matrix between them (i.e. I have a 107x107 matrix of results).
Next step I have used LARGE() to generate an ordered list of the highest results.
How can I map those results back to the names of the stocks?
i.e. generically, I have two 107x107 matrices, how can I take a result from one matrix and return the analogouslypositioned result from the other matrix?
or another way of looking at it is how to return a result's grid ref {X,Y} from a matrix?
The only thing i've thought of is for each row or column take a slice of the 1st, 2nd, 3rd highest results but that relies on me not looking to return that many results as it isn't scalable (i'm probably looking at the top 400 results from this ~11000 result matrix).
Please help...
Hi everyone,
I have one question regarding the cost of buying stock.
Please see the link for example in my worksheet.
If I buy 100 stocks with price p1 at time t1, then sell 50 stocks. Then I just have got 50 stocks left. Now I buy another 100 stocks with price p2 at time t2, which means I get 150 stocks in my portfolio. Now, I try to sell 70 stocks, which include 50 stocks from buy time t1 and 20 stocks from buy time t2. Consequently I have to pay 50*p1 + 20*p2.
Let's take stock SNE in my worksheet for example.
I bought 100 stocks SNE with price of 650, and sell 20, then 30, then 40, and I got 10 stocks left (which is represented in H23). Now I bought 20 more stocks with price of 599. And I sold 20, which included 10 stocks with price of 650 and 10 stocks with price of 599. Hence, I had to pay 10*650+10*599=12490 as in cell H25 which represents the cost I have to pay.
Actually I hardcoded column H. Does anyone know the formula for column H? Requirements a
Regarding a stock (for example SNE):
if G25=buy,
then I25=D25*E25
else {
if the remaining stocks from the first buy is greater than D25, I25=D25*(price of the the first buy time)
else I25= Remaining stocks * price of the first buy + (D25remaining stocks) * price of second buy
}
The work should be done in generally and I have no idea to such a complicated problem. I would very appreciate your help at this moment.
Hi everyone,
I have one question regarding the cost of buying stock.
Please see the link for example in my worksheet.
If I buy 100 stocks with price p1 at time t1, then sell 50 stocks. Then I just have got 50 stocks left. Now I buy another 100 stocks with price p2 at time t2, which means I get 150 stocks in my portfolio. Now, I try to sell 70 stocks, which include 50 stocks from buy time t1 and 20 stocks from buy time t2. Consequently I have to pay 50*p1 + 20*p2.
Let's take stock SNE in my worksheet for example.
I bought 100 stocks SNE with price of 650, and sell 20, then 30, then 40, and I got 10 stocks left (which is represented in H23). Now I bought 20 more stocks with price of 599. And I sold 20, which included 10 stocks with price of 650 and 10 stocks with price of 599. Hence, I had to pay 10*650+10*599=12490 as in cell H25 which represents the cost I have to pay.
Actually I hardcoded column H. Does anyone know the formula for column H? Requirements a
Regarding a stock (for example SNE):
if G25=buy,
then I25=D25*E25
else {
if the remaining stocks from the first buy is greater than D25, I25=D25*(price of the the first buy time)
else I25= Remaining stocks * price of the first buy + (D25remaining stocks) * price of second buy
}
The work should be done in generally and I have no idea to such a complicated problem. I would appreciate your help at this moment.
Hi, I have a function where a sorting of an Array takes place. It is supposed to sort in ascending order  the sort algo first checks to see if is sorted and if not sort. The macro works fine when it is already in ascending order but when it should sort I get an error in excel say wrong data type?
Here is the partial code:
ReDim Portfolio(1 To n, 1 To 5)
For j = 1 To n
Portfolio(j, 1) = Option_Type(j)
Portfolio(j, 2) = Strike(j)
Portfolio(j, 3) = expiry(j)
Portfolio(j, 4) = lamda(j)
Next j
x = True
For i = 1 To UBound(Portfolio, 1)  1
If Portfolio(i, 3) Portfolio(i + 1, 3) Then
x = False
Exit For
End If
Next i
If x = False Then
'Sort Portfolio Array based on Expiry
SortColumn1 = 3 'Sort on Expiry
For i = LBound(Portfolio, 1) To UBound(Portfolio, 1)  1
For j = LBound(Portfolio, 1) To UBound(Portfolio, 1)  1
Condition1 = Portfolio(j, SortColumn1) < Portfolio(j + 1, SortColumn1)
If Condition1 Then
For y = LBound(Portfolio, 2) To UBound(Portfolio, 2)
t = Portfolio(j, y)
Portfolio(j, y) = Portfolio(j + 1, y)
Portfolio(j + 1, y) = t
Next y
End If
Next
Next
End If
Thanks!
Hi,
I have a workbook containing numerous stocks' monthly returns, and their monthly media coverage. What I am trying to do is picking "media winners" and adding these to a portfolio to evaluate the strategy's return against the market.
In the attached workbook I am having trouble to build a formula that will pick out the "n" (I want this to be a changeable number, it could also be a percentile if that is easier?) largest values (in "∆media") and return the subsequent data (from "returns iferror") to the sheet "portfolio". It has to pick stocks on a monthly basis, as the portfolio is to be rebalanced every month, based on the numerous stock's media coverage in the prior month.
I am clueless as of now all help would be greatly appreciated, thanks in advance

