How to display a correlation matrix in ONE column

malierta · July 9, 2019, 5:42pm

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,
thanks in advance

fxshrat · July 9, 2019, 6:19pm

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 );  
	}
}

malierta · July 9, 2019, 6:30pm

thanks again fxshrat, cheers for the fast and accurate response

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

fxshrat · July 9, 2019, 6:33pm

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

malierta · July 9, 2019, 6:47pm

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

Eddie · June 9, 2021, 11:36am

fxshrat:

/// 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 );  
	}
}

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.

Thanks a lot

fxshrat · June 9, 2021, 12:58pm

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 );  
	}
}

Eddie · June 10, 2021, 6:37am

Thanks a lot for your help . Best

EJSM · June 15, 2021, 5:17pm

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

Thanks a lot!!