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:
image

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:
image

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:

image

I do not know how to arrange all the correlations in one column,
hope anybody can help,
thanks in advance

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
/// @link https://forum.amibroker.com/t/how-to-display-a-correlation-matrix-in-one-column/13687/2
/// derived from 
/// @link https://forum.amibroker.com/t/correlation-matrix/1184
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period
AddRow_Mode = ParamToggle( "AddRow Mode", "OFF|ON", 1);

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

if ( AddRow_Mode ) {
	Filter = 0;
	SetSortColumns(1, -4); 
	SetOption( "NoDefaultColumns", True);
	AddTextColumn( "","Ticker", 1, -1, -1, 90 );
	AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
	AddTextColumn( "", "Symbol", 1);
	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

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.

5

1 Like

Me quito el cráneo :wink: , 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;
Could you please help me too solve this two issues??

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.

image
Thanks a lot

Either use

/// Output Correlation as matrix view or in single column via Addrow
/// @link https://forum.amibroker.com/t/how-to-display-a-correlation-matrix-in-one-column/13687/2
/// derived from 
/// @link https://forum.amibroker.com/t/correlation-matrix/1184
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period
AddRow_Mode = ParamToggle( "AddRow Mode", "OFF|ON", 1);

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

if ( AddRow_Mode ) {
	Filter = 0;

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

	AddTextColumn( "","Ticker", 1, -1, -1, 90 );
	AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
	AddTextColumn( "", "Symbol", 1);
	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 ( AddRow_mode ) {
		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
/// @link https://forum.amibroker.com/t/how-to-display-a-correlation-matrix-in-one-column/13687/2
/// derived from 
/// @link https://forum.amibroker.com/t/correlation-matrix/1184
/// by fxshrat@gmail.com
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period
AddRow_Mode = ParamToggle( "AddRow Mode", "OFF|ON", 1);

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

if ( AddRow_Mode ) {
	Filter = 0;

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

	AddTextColumn( "","Ticker", 1, -1, -1, 90 );
	AddColumn( Null, "Date", formatDateTime, -1, -1, 120 );
	AddTextColumn( "", "Symbol", 1);
	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 ( AddRow_mode ) {
		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 );  
	}
} 

26

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