How To Calculate Variance Covariance Matrix?

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 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)

' 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

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 ad-in 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 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.

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

hello.

I get this error when trying to calculate the matrix via VBA. Here's the code:

[code]
Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.Count
Dim matrix ( ) As Double
ReDim matrix (numCols - 1, numCols - 1)
For i = 1 To numCols
For j = 1 To numCols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i
VarCovar
End Function
[code]

Here are my returns:
22.90% 3.60% 4.10% 9.50%
37.40% 18.20% 8.60% 19.20%
1.20% 2.70% 0.10% 1.70%
0.50% 34.50% 65.40% 2.30%

Here is my error:
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!

I've poured through code and formula [=varcovar(c3:f6) and I've done the ctrl+shift+enter. I really need help on this basic program.

Thanks.