# How to display a correlation matrix in ONE column

hi, I am working with the correlation matrix, the one shared by fxshrat in this topic: Correlation Matrix

This is the formula by fxhrat:

``````CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period//

wlnumber = GetOption( "FilterIncludeWatchlist" ); // choose your WL in Analysis window
symlist = GetCategorySymbols( categoryWatchlist, wlnumber );

// display only last bar from the Analysis range
Filter = Status( "lastbarinrange" );
SetSortColumns(1); // I added this to keep the columns and rows in order
Tickers = Name();

// iterate through symbols
logC = log( C / Ref( C, -1 ) );// does not belong within loop
for( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ )
{
Frg = Foreign( sym, "C" ); // store Foreign to variable since it is called multiple times
FrgLog = log( Frg / Ref( Frg, -1 ) );
Corr = Correlation( logC, FrgLog, CorrLB );

// set color dynamically based on correlation values
// and display the output in exploration column
corrcond = Corr>0.6;// used several times
tickercond = Tickers == sym;// used several times
Clr = 32 + SelectedValue( Corr ) * 32;
Clr = IIf( tickercond, colorBlack, Clr );
fntcolor = IIf( tickercond, colorWhite, IIf(corrcond,colorWhite, -1 ));
cellcolor = IIf( tickercond, colorDarkBlue, IIf(corrcond, colorGreen, -1) );
AddColumn( Corr, sym, 1.3, fntcolor, cellcolor, width = 60 );
}

``````

Supposing that I use it in a list with 3 symbols: SPY,XLF, and XLY, this is what I get: What I want is to get all the information of the matrix but in just one column of Correlations, with another column for the second symbol of the correlation.This would be what I want: At the moment, I have only been able to do an intermediate step, which is to add an AddColumn to the original formula in order to insert a column with the second symbol of the correlation

``````AddTextColumn(  sym , "Sym");
``````

So now I get this: I do not know how to arrange all the correlations in one column,
hope anybody can help,

You can do so via AddRow.
Via below code you can change between Addrow mode and AddColumn mode.

Note: AddRow does not have coloring options (yet).

If you want to exclude Corr = 1 then uncomment `&& nm != sym` in line 38.

``````/// Output Correlation as matrix view or in single column via Addrow
/// derived from
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period

wlnumber = GetOption( "FilterIncludeWatchlist" ); // choose your WL in Analysis window
symlist = GetCategorySymbols( categoryWatchlist, wlnumber );

nm = Name();
dt = DateTime();
dt_str = DateTimeToStr(SelectedValue(dt));

logC = log( C / Ref( C, -1 ) );// does not belong within loop

Filter = 0;
SetSortColumns(1, -4);
SetOption( "NoDefaultColumns", True);
AddTextColumn( "","Ticker", 1, -1, -1, 90 );
AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
AddColumn( Null, "Correlation", 1.3, -1, -1, 110 );
} else {
SetSortColumns(1);
Filter = Status( "lastbarinrange" );
}

// iterate through symbols
for ( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ ) {
Frg = Foreign( sym, "C" ); // store Foreign to variable since it is called multiple times
FrgLog = log( Frg / Ref( Frg, -1 ) );
Corr = Correlation( logC, FrgLog, CorrLB );

if ( AddRow_mode /*&& nm != sym*/ ) {
AddRow( StrFormat( "%s\t%s\t%s\t%1.3f", nm, dt_str, sym, SelectedValue(Corr)) );
} else {
// set color dynamically based on correlation values
// and display the output in exploration column
corrcond = Corr>0.6;// used several times
tickercond = nm == sym;// used several times
Clr = 32 + SelectedValue( Corr ) * 32;
Clr = IIf( tickercond, colorBlack, Clr );
fntcolor = IIf( tickercond, colorWhite, IIf(corrcond,colorWhite, -1 ));
cellcolor = IIf( tickercond, colorDarkBlue, IIf(corrcond, colorGreen, -1) );
AddColumn( Corr, sym, 1.3, fntcolor, cellcolor, width = 60 );
}
}
`````` 5 Likes

thanks again fxshrat, cheers for the fast and accurate response

one last question... how do I get to the parameters window that you show ?

In Analysis tool bar go to below shown button telling you "Parameters" if you hover over it with your mouse. 1 Like

Me quito el cráneo , Thanks again

(I take off my skull), - in stead of the hat; it is a colloquial common expression in Spanish, regarding your useful help and your profile pic

1 Like

Hy Fxshrat, First thanks for the code. I tried to to exclude Corr = 1 doing what you say " then uncomment `&& nm != sym` in line 38." BUT it continues to Show the cases where Correlation =1.
I am also interested in seeing only pairs with correlation >0.8 and not =1;

I have tried to add what you see in the image but it gives me a message that I do not know How to fix. Thanks a lot

Either use

``````/// Output Correlation as matrix view or in single column via Addrow
/// derived from
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period

wlnumber = GetOption( "FilterIncludeWatchlist" ); // choose your WL in Analysis window
symlist = GetCategorySymbols( categoryWatchlist, wlnumber );

nm = Name();
dt = DateTime();
dt_str = DateTimeToStr(SelectedValue(dt));
logC = log( C / Ref( C, -1 ) );// does not belong within loop

Filter = 0;

SetSortColumns(1, -4);
SetOption( "NoDefaultColumns", True);

AddTextColumn( "","Ticker", 1, -1, -1, 90 );
AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
AddColumn( Null, "Correlation", 1.3, -1, -1, 110 );
} else {
SetSortColumns(1);
Filter = Status( "lastbarinrange" );
}

// iterate through symbols
for ( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ ) {
Frg = Foreign( sym, "C" ); // store Foreign to variable since it is called multiple times
FrgLog = log( Frg / Ref( Frg, -1 ) );
Corr = Correlation( logC, FrgLog, CorrLB );
Corr_value = SelectedValue(Corr);

if ( Corr_value < 1 )
AddRow( StrFormat( "%s\t%s\t%s\t%1.3f", nm, dt_str, sym, Corr_value) );
} else {
// set color dynamically based on correlation values
// and display the output in exploration column
corrcond = Corr>0.6;// used several times
tickercond = nm == sym;// used several times
Clr = 32 + Corr * 32;
Clr = IIf( tickercond, colorBlack, Clr );
fntcolor = IIf( tickercond, colorWhite, IIf(corrcond,colorWhite, -1 ));
cellcolor = IIf( tickercond, colorDarkBlue, IIf(corrcond, colorGreen, -1) );
AddColumn( Corr, sym, 1.3, fntcolor, cellcolor, width = 60 );
}
}
``````

or

``````/// Output Correlation as matrix view or in single column via Addrow
/// derived from
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period

wlnumber = GetOption( "FilterIncludeWatchlist" ); // choose your WL in Analysis window
symlist = GetCategorySymbols( categoryWatchlist, wlnumber );

nm = Name();
dt = DateTime();
dt_str = DateTimeToStr(SelectedValue(dt));
logC = log( C / Ref( C, -1 ) );// does not belong within loop

Filter = 0;

SetSortColumns(1, -4);
SetOption( "NoDefaultColumns", True);

AddTextColumn( "","Ticker", 1, -1, -1, 90 );
AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
AddColumn( Null, "Correlation", 1.3, -1, -1, 110 );
} else {
SetSortColumns(1);
Filter = Status( "lastbarinrange" );
}

// iterate through symbols
for ( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ ) {
Frg = Foreign( sym, "C" ); // store Foreign to variable since it is called multiple times
FrgLog = log( Frg / Ref( Frg, -1 ) );
Corr = Correlation( logC, FrgLog, CorrLB );
Corr_value = SelectedValue(Corr);

if ( nm != sym )
AddRow( StrFormat( "%s\t%s\t%s\t%1.3f", nm, dt_str, sym, Corr_value) );
} else {
// set color dynamically based on correlation values
// and display the output in exploration column
corrcond = Corr>0.6;// used several times
tickercond = nm == sym;// used several times
Clr = 32 + Corr * 32;
Clr = IIf( tickercond, colorBlack, Clr );
fntcolor = IIf( tickercond, colorWhite, IIf(corrcond,colorWhite, -1 ));
cellcolor = IIf( tickercond, colorDarkBlue, IIf(corrcond, colorGreen, -1) );
AddColumn( Corr, sym, 1.3, fntcolor, cellcolor, width = 60 );
}
}
`````` Thanks a lot for your help . Best

Where should I add the code to filter out the ones with a correlation greater than 0.8?

Thanks a lot!!