SEMI-covariance matrix in VBA - Help needed!

[-Arsenal-]

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)<0;Ri-avg(Ri);0)*IF(Rj - avg(Rj)<0;Rj - avg(Rj);0))

Now I have 7 stocks and I would like to write a VBA code that calculates the semicovariance MATRIX between the 7 stocks. I know how to write the code for the regular covariance matrix, but that was easy because I can call the Excel function "Covar" directly...

This is my code for the regular covariance matrix:
---------------------------
Option Base 1

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

For i = 1 To numCols
For j = 1 To numCols
matrix(i, j) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j))
Next j
Next i
VarCovar = matrix
End Function
---------------------------

Can anyone help me with suggestions for a SEMIcovariance matrix VBA code??

Thank you in advance

 

[JackBean]

With data (4 series instead of 7) generated with help of RAND function in Cells B2:E11

Excel Workbook
	A	B	C	D	E
1	Series -->	1	2	3	4
2		1.242	3.397	4.862	4.92
3		1.867	3.138	4.041	5.7
4		2.396	3.659	3.175	6.527
5		3.129	2.127	6.201	6.292
6		1.482	5.155	4.273	5.933
7		1.349	5.36	6.828	6.125
8		2.841	4.355	5.244	7.739
9		4.835	4.212	8.495	7.197
10		4.768	6.726	8.876	10.406
11		1.256	7.671	8.526	5.65
12
13		0.232299		0.255529
14
15		0.552984	0.232299	0.455469	0.495077
16		0.232299	0.957305	0.649048	0.400123
17		0.455469	0.649048	1.596072	0.508207
18		0.495077	0.400123	0.508207	0.528775
...

The semi-covariance of Series 1 with Series 2 using your formula:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note that the result is different if the ",0" is left off of the IF function:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Then substituting :
INDEX(B:B,ROW(B$11)
for:
B2:B11
and substituting:
INDEX($B$2:$E$11,0,ROW(A1))
for:
C2:C11
results in:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
This formula is copied down and over.
You are probably only interested in the Cells with Bold font.

What are you trying to accomplish by converting this to CODE?
Do you need to do that?

 

[-Arsenal-]

Thanks a lot for your help , I appreciate the effort... However, I know how it's done manually.. I need to code it in Vba because I will have to do this 150 times as a rolling window analysis...

Can you help me?

Thanks

 

[JackBean]

This can be converted to code.

The semi-covariance matrix could be rearranged to fit in Row 2 to the right of the data.
The semi-covariance matrix could be calculated either with each Cell Formula pasted in and then converted to values, or by converting it to Code.

Say that a new row of data comes in at Row 2 and moves the existing Rows 2 to 11 down to 3 to 12, along with the semi-covariance matrix to the right in Row 2.
The semi-covariance matrix could be calculated again for Rows 2 to 11.

The same macro that brings in the new data could shift everything down a row and calculate the semi-covariances again.

If the number of series will change, this would be more easily adjustable if the semi-covariance matrix is in a row were entirely calculated with Code instead of Cell Formulas.

Alternatively, if you already have the complete series of data, you can rearrange the semi-covariance formulas into rows, and copy down the columns, and code is not really needed.

 

[-Arsenal-]

Thanks, actually there is more to it than the semicovariance matrix because I will have to optimize portfolio weights 150 times... That's why I would prefer to code it as it involves calling the solver 150 times...

However, I know how to call it from vba.. My problem is writing the code for the semicovariance matrix.. If you could show me, that would solve my problem right away

 

[JackBean]

It is simpler to paste a formula in than it is to convert the formula to Code.
This should get you started with the matrix.
The data, 7 series with 10 rows, is on Sheet1 (named "A" here).
The Semi-covariance matrix goes on Sheet2.
The Code would not paste this formula:

{=AVERAGE(IF(A!B$2:B$11-AVERAGE(A!B$2:B$11)<0,A!B$2:B$11-AVERAGE(A!B$2:B$11),0)*IF(INDEX(A!$B$2:$H$11,0,ROW(A1))-AVERAGE(INDEX(A!$B$2:$H$11,0,ROW(A1)))<0,INDEX(A!$B$2:$H$11,0,ROW(A1))-AVERAGE(INDEX(A!$B$2:$H$11,0,ROW(A1)))),0)}

But the Code does paste this formula, where A!B$2:B$11 is substituted with a Named Formula "Kolumn".

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
Kolumn	=A!B$2:B$11
Workbook Defined Names

The Named Formula is redefined every time the macro is run, in case a Row is inserted in the data.

Sub SemiCoVariance()
ActiveWorkbook.Names.Add Name:="Kolumn", RefersToR1C1:="=A!R2C:R11C"
Range("B2").FormulaArray = "=AVERAGE(IF(Kolumn-AVERAGE(Kolumn)<0,Kolumn-AVERAGE(Kolumn),0)*IF(INDEX(A!$B$2:$H$11,0,ROW(A1))-AVERAGE(INDEX(A!$B$2:$H$11,0,ROW(A1)))<0,INDEX(A!$B$2:$H$11,0,ROW(A1))-AVERAGE(INDEX(A!$B$2:$H$11,0,ROW(A1)))),0)"
Range("B2").AutoFill Range("B2").Resize(7, 1)
Range("B2").Resize(7, 1).AutoFill Range("B2").Resize(7, 7)
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H
1	Series -->	1	2	3	4	5	6	7
2		1.24	3.4	4.86	4.92	7.56	7.89	9.38
3		1.87	3.14	4.04	5.7	7.28	6.69	8.06
4		2.4	3.66	3.18	6.53	7.78	9.41	10.65
5		3.13	2.13	6.2	6.29	6.94	10.08	7.89
6		1.48	5.16	4.27	5.93	5.66	6.88	9.65
7		1.35	5.36	6.83	6.13	5.94	7.69	12.04
8		2.84	4.36	5.24	7.74	10.74	10.8	10.1
9		4.84	4.21	8.5	7.2	9.74	8.29	9.47
10		4.77	6.73	8.88	10.41	10.44	13.39	9.47
11		1.26	7.67	8.53	5.65	10.37	11.27	10.86
A

Excel Workbook
	A	B	C	D	E	F	G	H
1	Semi-CoV -->	1	2	3	4	5	6	7
2		0.553759	0.232164	0.456259	0.495765	0.630099	0.693968	0.153866
3		0.232164	0.956959	0.649297	0.400738	0.529981	0.5112	0.688849
4		0.456259	0.649297	1.597237	0.509524	0.791341	0.995145	0.368782
5		0.495765	0.400738	0.509524	0.529836	0.517018	0.659935	0.273956
6		0.630099	0.529981	0.791341	0.517018	1.392268	1.186574	0.418989
7		0.693968	0.5112	0.995145	0.659935	1.186574	1.562008	0.487182
8		0.153866	0.688849	0.368782	0.273956	0.418989	0.487182	0.607619
Sheet2

<<<<<<<<<<<<<<<<<<<<<<<<
In Post #2 it was not necessary to do this substitution:
Then substituting :
INDEX(B:B,ROW(B$11)
for:
B2:B11<<<<<<<<<<<<<<<<<<<<<<<<