referencing arrays using statistical functions

[Scott Dobbs]

I have generated a database of company returns which is
put into arrays by company (companies are assigned a
number each) and the returns are labelled in the worksheet
as "dataset1", "dataset2" etc. I am now trying to generate
a covariance matrix where all I need to do is substitute
the numbers for the company on an array across the top and
side of the matrix, and I want excel to refer to the
dataset which coincides with the number. In short, I
substitute numbers across the top and down the left side
such as 1,4,5,7,9,13, etc. (ten wide and ten deep) and I
want to write the function something like:
=covar(dataset(R[-2]),dataset(C[-4]))for each cell which
should contain the covariance in a markowitz covariance
matrix and have the references go to the respective
datasets to input the arrays. I am wondering if I should
write another sub which tries to recognise that the inputs
(as integers) should be set against the datasets. Should I
use ranges as pure cell references? I haven't worked with
excel extensively since about 1996 and I am forgetting
most of what I know.
Any help?

thanks
Scott

 

[JMay]

How about a Pivot Table? It might be just the ticket...

 

[Harlan Grove]

I have generated a database of company returns which is
put into arrays by company (companies are assigned a
number each) and the returns are labelled in the worksheet
as "dataset1", "dataset2" etc. . . .

Labeling is nice, but may not be directly useful in referencing. Do you mean
that company results are stored in single column, multiple row ranges with a
company code in the topmost row? I'll assume so later in this response. If this
isn't the case, please provide details of the *exact* data layout.

. . . I am now trying to generate
a covariance matrix where all I need to do is substitute
the numbers for the company on an array across the top and
side of the matrix, and I want excel to refer to the
dataset which coincides with the number. In short, I
substitute numbers across the top and down the left side
such as 1,4,5,7,9,13, etc. (ten wide and ten deep) and I
want to write the function something like:
=covar(dataset(R[-2]),dataset(C[-4])) for each cell which
should contain the covariance in a markowitz covariance
matrix and have the references go to the respective
datasets to input the arrays. . . .

You need an 11-by-11 range to store the covariance matrix augmented by ID codes
in top row and left column (with the top-left cell's contents immaterial). You
only need to enter the ID codes once, either in the top row or the left column,
then use an array formula calling TRANSPOSE to populate the other.

I'll assume the output range is in A1:K11 in a different worksheet; that your
data is in tabular format spanning several columns (>10) of 11 rows each, with
ID code in the top row and same year results in the next 10 rows, all named
Data; and that you enter ID codes for the covariance matrix in left column,
A2:A11. Then the matching codes for the top row of the covariance matrix can be
given by entering the array formula

=TRANSPOSE(A2:A11)

into B1:K1. The top-left covariance formula in the matrix, cell B2, would be

=COVAR(OFFSET(Data,1,MATCH($A2,INDEX(Data,1,0),0)-1,10,1),
OFFSET(Data,1,MATCH(B$2,INDEX(Data,1,0),0)-1,10,1))

Copy B2 and paste into B2:K11. If the top row of Data really does contain labels
like 'dataset5' and you only enter numbers like 5 in A2:A11, then change the
formula immediately above to

=COVAR(OFFSET(Data,1,MATCH("dataset"&$A2,INDEX(Data,1,0),0)-1,10,1),
OFFSET(Data,1,MATCH("dataset"&B$2,INDEX(Data,1,0),0)-1,10,1))