Automation

[Craig B.]

I have created five (5) grids within Excel consisting of correlations
comparing various commodities. Each correlation grid represents years
of data (i.e., 5yr, 10yr, 15yr, etc.).

Right now, I am manually checking each commodity across every grid to
see which other commodities are closely or loosely correlated. At the
same time, I check to see if these correlations are consistent across
all years of data. Once the correlation is manually validated, I type
the summary list into yet another worksheet.

Basically, here's an abbreviated example of what the grids look like:

AD BO BP C CC CD CL CON CT
AD 100% 79% 47% 50% 63% 94% 12% 34% 49%
BO 79% 100% 17% 66% 79% 76% 53% 62% 74%
BP 47% 17% 100% 14% 19% 42% 43% 53% 29%
C 50% 66% 14% 100% 44% 37% 59% 52% 86%
CC 63% 79% 19% 44% 100% 55% 40% 38% 46%
CD 94% 76% 42% 37% 55% 100% 3% 38% 44%
CL 12% 53% 43% 59% 40% 3% 100% 69% 74%
CON 34% 62% 53% 52% 38% 38% 69% 100% 75%
CT 49% 74% 29% 86% 46% 44% 74% 75% 100%


Given I have about 50 commodities to compare over 5 separate time
frames, I really want to automate this task as the manual approach is
incredibly labor intensive and prone to error. I'd like the output of
this task automation to generate a simple list such as what follows:

Closely Correlated (80-100%) Loosely Correlated (50-79%)
Commodity AD: BO, CD C, CC, KC
Commodity BO: PN CT, CON, CD, CC, C, AD
Etc.

I'm not sure if what I'm looking to accomplish is very straightforward
or if it may require some custom programming. Is there something built
into Excel which can help with this or might there be some add-ins or
tools which are available to help with this automation.

Any help or insight would be appreciated.

Thanks,

CB

 

[Biff]

Hi!

In your closely correlated critera you have the range 80-
100% yet in the sample you have:

Commodity AD: BO, CD

Where BO = 79. Is that just a typo?

Is it possible for any commodity to correlate to another
commodity (other than itself) at 100%?

Here's a general formula that will do what you want but
may need tweaked after all the fine details are worked out:

Assume the table is in the range A1:J10. In A12:A20 you
have the commodities listed.

A12 = AD

In B12 enter this array formula with the key combo of
CTRL,SHIFT,ENTER and copy across until you get #NUM!
errors. This is for your 80-100% correlation:

=INDEX($B$1:$J$1,SMALL(IF($B$2:$J$2>=0.8,IF
($B$2:$J$2<=1,COLUMN($A$1:$I$1))),COLUMN()-1))

The output will return this:

AD CD

You can use the same formula with changes to the %
arguments and the COLUMN()-1 argument (depending on what
column the formula is in) for the loosely correlated
criteria.

Biff

 

[Craig B.]

Hello Biff-

Thanks so much for your input. After a small bit of tweaking, your
formula worked like a charm.

BTW, the BO at 79% was a typo. It should have been loosely correlated
although it is borderline at 79.6%. There are some commodities that
converge very close to 100% but none that I know of that are exactly
100% except for a commodity pair which trades both 24hours and day
only on different exchanges (i.e., Australian Dollar (AD) day only vs.
AD 24 hours).

Now that I have the correlation multi-year table comparison automated,
I would like to automate each commodities multi-year correlation into
an overall correlation set.

What I have now is five sets of correlation rows/commodity
(representing the fives historical data sets) which look like the
following:

AD
======
1yr: C S PN SI SM
5yr: BO BP CD DX EU GC S SF SM
10yr: DX EU GC MP SF SI W
15yr: DX TU SP MP
20yr: CC GC MP
Overall: ???

I want to create a formula which simultaneously looks across the five
rows and provides one row (Overall) of the unique correlations for
each commodity. So, for example, in the above example, the Overall row
(minus the duplicates) for AD would look as follows:

AD Overall: BO, BP, C, CC, CD, DX, EU, GC, MP, PN, S, SF, SI, SM, SP,
TU, W

I will probably apply a logical filter to the above where the symbol
will need to show up at least x times over the entire historical data
set for it to qualify as a solid correlation. (e.g., If DX appears in
5, 10 and 15 years but TU only appears once, DX would be considered a
strong correlation whereas TU would be weak.)

Thanks again,

Craig B.

 

[CB]

Hello Biff-

Thanks so much for your input. After a small bit of tweaking, your
formula worked like a charm.

BTW, the BO at 79% was a typo. It should have been loosely correlated
although it is borderline at 79.6%. There are some commodities that
converge very close to 100% but none that I know of that are exactly
100% except for a commodity pair which trades both 24hours and day only
on different exchanges (i.e., Australian Dollar (AD) day only vs. AD 24
hours).

Now that I have the correlation multi-year table comparison automated,
I would like to automate each commodities multi-year correlation into
an overall correlation set.

What I have now is five sets of correlation rows/commodity
(representing the fives historical data sets) which look like the
following:


Australian Dollar (AD) Correlations
==============================
1yr: C S PN SI SM
5yr: BO BP CD DX EU GC S SF SM
10yr: DX EU GC MP SF SI W
15yr: DX TU SP MP
20yr: CC GC MP
Overall: ???

I want to create a formula which simultaneously looks across the five
rows of historical data and provides one row (Overall) of the unique
correlations for each commodity. So, for example, in the above example,
the Overall row (minus the duplicates) for AD would look as follows:

AD Overall: BO, BP, C, CC, CD, DX, EU, GC, MP, PN, S, SF, SI, SM, SP,
TU, W

I will probably apply a logical filter to the above where the symbol
will need to show up at least x times over the entire historical data
set for it to qualify as a solid correlation. (e.g., If DX appears in
5, 10 and 15 years but TU only appears once, DX would be considered a
strong correlation whereas TU would be weak.). So, in the end, the
overall row would be minimized to:
AD Overall: DX, EU, GC, MP, SF, SI, SM

Thanks again,

Craig B.

 

[Cachetona]

I need to copy various cells from an open worksheet into an anothe
worksheet which will be closed. I want to copy all the various cell
into a single unused sequential row using various columns that wil
always remain constant from sheet to sheet. The only thing that I wan
to change is the row on the second sheet. I want to keep a log of th
data used (the various cells) on the first sheet in a single row on th
second sheet.

For example: C4, F4, C6, F6, and L1 of sheet one (a sales order) wil
go into second sheet in a single row. The next time I enter new dat
(a new sales order) in the same fields of sheet one, I want the ne
data to go into next sequential single unused row of sheet two. Shee
two is a log book (foramtted diferent from the sales order