C
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
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