Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

How To Calculate Variance Covariance Matrix?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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_...n-formulas.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!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

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?




I have a list of trades I have made with the buy/ sell dates and values. This portfolio holds 2 stocks and at month's end it will have 1 or 2 stocks and/ or all Cash. Cash is prices at $1 initial positions are in Cash for $10,000 each.

I now need to calculate on a monthly basis the portfolio value as well as yearly as these trades go back to 2007. I have a vlookup to find the month end stock price*shares:

Please Login or Register  to view this content.


where Prices is a worksheet which has all the prices for all securities in portfolio. Col A is date Col B on are stock prices and Row 1 has the stock symbol. (FYI CASH is not a stock symbol and is always $1.

I can use sumifs to add the buy or sell values, which is incorrect but don't know how to insert the code above into the sumifs. See attached spreadsheet for formula.

monthend calculations.xlsx

Any help is greatly appreciated.
Doug

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 5-year 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 semi-covariance 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(Ri-avg(Ri)



Hi there,

This is my first post (hopefully of many).

I need assistance in creating 100 random portfolios from a list of 10 assets. I have the return history of the 10 assets and I'm able to calculate the mean and standard deviation of each asset as well as the covariance/correlation between each of the 10 assets. I want to choose to portfolio with the least variance as well the portfolio with the lowest and highest return.

Would it also be possible to introduce a limit that constrains the maximum allocation to each asset at 25%?

Many thanks for any assistance on this matter.

Regards,
Grant Hogan

' 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) 'Re-dimension 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 Value-at-Risk 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 sub-array 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 Variance-Covariance 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 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


I can calculate the covariance matrix from a series of numbers nxm with the ad-in formula (covar) but need to fill the places above the diagonal, any sugestion?


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 analogously-positioned 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 hard-coded 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 + (D25-remaining 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 hard-coded 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 + (D25-remaining 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.