Drawing data from specific columns of a dynamic range

[Guest]

Hi,

I have historical data; 40 columns of vertically displayed data according to
date which I use to create volatility and correlation matrices. I would like
to look at the volatilities and correlations of the 40 contracts for
different dates and different time periods. (For example, I would like to use
data from 2/6/06 to 25 days past that date, or, I would like to use data from
12/28/05 to 55 days past that date to find out what the differences were for
those specific dates and time periods).

To do this I have created a dynamic range name using input cells; one for
the date, one for the # of days out from that date. The name (DynamicRange)
refers to
=OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)-1,1,EDspdvolscorr!$K$3,145)

Where EDspdvolscorr!$K$2 is the Desired date to start from and
EDspdvolscorr!$K$3 is the desired number of days to go out from.

Now what I would like to do is take a specific column(s) to find the
volatilities and correlations of the data points within that range. Is there
someway to do =CORREL(“column2 of DynamicRangeâ€, “column 4 of DynamicRangeâ€)
or =SQRT(SUMSQ(“column2 of DynamicRangeâ€)/EDspdvolscorr!$K$3) for example?

I think another option I have is to create names for each individual column
and link them all to the input cells. Which is easier/more efficient?

Thanks for your help,

-Darren

 

[Max]

.. what I would like to do is take a specific column(s) to find the

volatilities and correlations of the data points within that range.
Is there someway to do
=CORREL("column2 of DynamicRange", "column 4 of DynamicRange")
or =SQRT(SUMSQ("column2 of DynamicRange")/EDspdvolscorr!$K$3)
for example?

Perhaps one way to set it up ..

A sample construct is available at:
http://www.savefile.com/files/4522354
Drawing data from specific cols of a dynamic range_Darren_setup.xls

Create 3 defined / named ranges
(via Insert > Name > Define):

Col_1:
=OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)
-1,EDspdvolscorr!$K$4-1,EDspdvolscorr!$K$3)

Col_2:
=OFFSET(dailychange!$A$2,MATCH(EDspdvolscorr!$K$2,dailychange!$A$2:$A$506,0)
-1,EDspdvolscorr!$K$5-1,EDspdvolscorr!$K$3)

Dates:
=OFFSET(dailychange!$A$2,,,COUNTA(dailychange!$A:$A)-1)

(Dates is a dynamic range for use in the DV below to ease selection of the
date input)

Then in sheet: EDspdvolscorr,

Assuming the inputs for Date, Days, Column 1, Column 2
will be made in K2:K5, eg:

Date: 01-Dec-05
Days: 5
Column 1: 2
Column 2: 4

we could put in say, K7:K8
=CORREL(Col_1,Col_2)
=SQRT(SUMSQ(Col_2)/$K$3)
to return the reqd calcs

where K2 contains a DV to select the date input
(created via Data > Validation, Allow: List, Source: Dates)

---

 

[Guest]

Thanks for your help Max, that was very thorough.

-DMS

 

[Max]

You're welcome, Darren !