C
Craig
Biff: Thanks so much for your input on the automation. After a small
bit of tweaking, your formula worked like a charm.
Now that I have the correlation multi-year table comparison automated,
I would like to consolidate each commodities multi-year correlation
into an overall correlation set.
What I have now is five sets of correlation rows/commodity
(representing the five 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
Any and all help would be appreciated.
Craig B.
bit of tweaking, your formula worked like a charm.
Now that I have the correlation multi-year table comparison automated,
I would like to consolidate each commodities multi-year correlation
into an overall correlation set.
What I have now is five sets of correlation rows/commodity
(representing the five 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
Any and all help would be appreciated.
Craig B.