VBA Function for Correlation Matrix

[mikes_r]

Hi,

I'm new to using VBA, but looking to learn. I'm having trouble with the code for a correlation matrix function. The code is below and I keep getting a #NAME? error but I can't figure out where the issue is.



Function CorrelationMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim NumColumns As Integer

NumColumns = rng.Columns.Count - 1

Dim matrix() As Double
ReDim matrix(NumColumns, NumColumns)

For i = 0 To NumColumns
For j = i To NumColumns
matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i
CorrelationMatrix = matrix
End Function


Any help/advice would be greatly appreciated!

 

[shg]

Your function works OK for me:

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	2​	9​	2​	5​		1​	-0.34013​	-0.32348​	0.159371​		F1:I4: {=CorrelationMatrix(A1:D20)}
2​	2​	4​	5​	5​		0​	1​	0.012126​	0.065559​
3​	3​	7​	9​	3​		0​	0​	1​	0.168794​
4​	1​	2​	8​	3​		0​	0​	0​	1​
5​	3​	4​	8​	9​
6​	7​	4​	9​	3​
7​	8​	1​	3​	7​
8​	4​	4​	8​	7​
9​	8​	3​	3​	6​
10​	6​	7​	4​	9​
11​	8​	6​	2​	3​
12​	2​	3​	2​	1​
13​	4​	4​	8​	7​
14​	2​	7​	6​	3​
15​	1​	2​	6​	6​
16​	5​	3​	4​	2​
17​	9​	2​	3​	7​
18​	1​	9​	4​	5​
19​	5​	5​	6​	9​
20​	2​	8​	8​	9​

What version of Excel are you using?

 

[mikes_r]

Hi shg,

I'm using Excel 2016. I didn't even think of testing with a random number generator before, I have now on the relevant document and its still giving the #NAME? error.

 

[pgc01]

Hi
Welcome to the board

Where is the code? Is it in a standard module?

 

[mikes_r]

Hi pgc01,

I've deleted and then readded the module. It is now part of the workbook as a standard module and it is now sort of working, but not for what I need it for. It works for creating a correlation matrix of closing prices of stocks, but I'm trying to use it for a correlation matrix of the percentage change of closing prices. Using it on percentage change data is giving returning a #VALUE! error. (the percentage data is calculated from the closing price data, so there shouldn't be anything funny about it)

 

[shg]

Can you provide an example that returns an error?

Can you write a formula for the result you're trying to achieve?

 

[mikes_r]

I figured it out! It was an issue with the data set, one stock had incomplete/NA values and that was screwing with the entire array.

Thanks for everyone's help!

 

[shg]

You're welcome.