Correlation Matrix

Hi,

I am using the correlation matrix code posted above, amarjit's code, on a watchlist of 600 plus securities. The code runs correctly in Explorer and produces the matrix. When I select all and copy-paste to excel, all rows are produced correctly except for the first row which seems to abruptly stop and end with ",,,,".

Thanks,

Mike

imageMCDFX

MCEMX	MCHFX	MCHI	MDIIX	MDY	MEASX	MECFX	MELIX	MEOIX
0.742427	0.910101	0.808445	0.9211	0.688269	0.513533	...			
0.587688	0.768152	0.673008	0.748231	0.899174	0.791314	0.286097	0.916097	0.632079	0.577439
0.782474	0.77336	0.825344	0.815789	0.483168	0.350167	0.320215	0.556474	0.643448	0.676263
0.371448	0.529167	0.417871	0.474861	0.848407	0.434829	0.276045	0.718584	0.553492	0.426029
0.513086	0.680173	0.598885	0.636018	0.820376	0.8121	0.287302	0.884252	0.579213	0.548123

If all other rows are correct, can I run the script twice on the first symbol so it is duped and then I could delete the first row or can a parameter be changed to allow the first row to be produced and copied to Excel? Also, I tried pasting the matrix directly into a text file and the first row has the same issue.

Thanks,

Mike

@reds sorry but it works fine for me. I used Edit --> Copy and then pasted into Excel spreadsheet on the SP500 and all 505 stocks appear.
image

And top right hand corner of spreadsheet looks like this
image

thanks for taking a look! I have tried several times and always with the first line truncated but all other rows ok. I have also tried shorter watchlists and it all rows were produced. Maybe something with my data? I will check further. Thanks again!

Mike

portfoliobuilder,

Could your script be modified to produce a table that lists the primary symbols in column A and then the top 3 or 4 correlated symbols(not correlation coefficient), listed by correlation score, in columns in B,C,D,E…?

Thanks,

Mike

@reds i think the current script would not be of much use for what you are asking for. I believe you probably need to rank the correlations ( StaticVarGenarateRanks), and then for that output that you are looking for perhaps using AddMultiTextColumn would produce the result. I have not attempted that as it is a big job and I am too busy. But if you are able then please come back and share with us here on the forum. Good luck.

Thank you Larry for the suggestions. I will post back if I am able to find a solution but it may be out of my limited abilities…Thanks…Mike

@reds, this can be done by storing the corr. table to matrix at the end of exploration (using matrix functions). The sorted top N get picked from that matrix and output via Addrow.

It is a bit tricky (but not difficult). I'm saying tricky because you have to think a little bit.

Here is proof that it is possible to do that (output of sorted table (from largest to lowest together with standard correlation table below of it). As aside Addrow does not offer cell coloring (yet). I have output all but of course you can pick just a top N via MxGetBlock also.
230136

Here is another example just outputting overall top five pairs + corr.

174133

So as always endless possibilities of output.


BTW, instead of using Foreign for default correlation table it is much much faster using StaticVarAdd to store second array of Correlation function. In fact that method is lightning fast compared to using Foreign. Milliseconds per each exploration run.

3 Likes

@reds here is a possible way to do it (a bit unorthodox approach):

/****** Constants *****/
							// Change these as needed
MAX_ALLOWED_TICKERS = 1000; // Over this size probably it is too slow...
MAX_PAIRS_IN_TABLE = 250;   // To avoid using too many columns and hit a Windows limit

/***** Parameters *****/

// Number of the watchlist to use to create the correlation matrix
wlNumber = Param("Watchlist Number", 1, 0, 63, 1);         
// Number of columns pair to display in resulting table
maxColRes = Param("Number of (pair) columns in results (0 = max)", 5, 0, MAX_PAIRS_IN_TABLE, 1);  
// Daily correlation look-back period
corrLB = Param("Daily correlation lookback period", 252, 50, 300, 1);         
// Send trace messages for debugging purpose on progess
debugTrace = ParamToggle("Enable _TRACE()", "No|Yes", 1); 


/***** Functions *****/

function _TRACE_(msg) {
    if (debugTrace) 
		_TRACE(msg);
}		


/***** Exploration *****/

actionStatus = Status("action");
if (( actionStatus == actionExplore ) OR (actionStatus == actionBacktest)) {
	symlist = GetCategorySymbols( categoryWatchlist, wlNumber);
	Filter = 0;
	stockNum = Status("stocknum");
	if (stockNum == 0)
	{
		// Do this only ONCE
		if (symList != "") {
			EnableTextOutput( 0 ); 
			size = 1 + StrCount( symlist, ",");
			if (maxColRes >= size) {
				maxColRes = size-1;
			}
			if (maxColRes <= 0) {
				maxColRes = size-1;
			}
			if (maxColRes >= MAX_PAIRS_IN_TABLE) {
				maxColRes = MAX_PAIRS_IN_TABLE; // 
			}
			
			_TRACE_("Selected watchlist: " + WriteVal(wlNumber, 2.0) + " - Size: " + WriteVal(size, 3.0));
			_TRACE_("Symbols: " + symList);
			if (size < MAX_ALLOWED_TICKERS+1) {
				Filter = 0;
				SetOption("NoDefaultColumns", True );	
				AddColumn(Null, "Ticker", 1.2, -1, -1, 60);
				for (i = 1; i <= maxColRes; i++) {
					AddColumn(Null, "T" + WriteVal(i, 1.0), 1.2, -1, -1, 60);
					AddColumn(Null, "C" + WriteVal(i, 1.0), 1.2, -1, -1, 60);	
				}
				
				// Get Tickers and do calculations ONCE 
				_TRACE_("Parsing tickers");				
				for( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ ) 
				{
					VarSetText("T_" + i, sym);
					CFrgn = Foreign( sym, "C" );
					logCFrgn = log( CFrgn / Ref( CFrgn, -1 ) );
					VarSet("L_" + i, logCFrgn);					
				}	
				_TRACE_("Parsing tickers. Done");
				// Add top row used for ticker index 
				mx = Matrix(size+1, size, 0);		
				_TRACE_("Created matrix. " + WriteVal(size+1, 3.0) + "*" +  WriteVal(size, 3.0));						
				
				_TRACE_("Filling matrix with correlations.... ");
				// First row is reserved to ticker indexes
				for( col = 0; col < size; col++ )
				   mx[0][col] = col;
				
				// Filling a matrix with correlation values
				startCol = 0;
				for( row = 0; row < size; row++ ) 
				{
					symRow = VarGetText("T_" + row);
					// CRow = Foreign( symRow, "C" ); 
					// logCRow = log( CRow / Ref( CRow, -1 ) ); 
					if (row % 10 == 0) 
						_TRACE_("Row " + WriteVal(row, 3.0));
					for( col = startCol; col < size; col++ )
					{
						symCol = VarGetText("T_" + col);
						// _TRACE_("R " + WriteVal(row, 2.0) + " C " + WriteVal(col, 3.0) + " " + symRow + " * " + symCol);
						// CCol = Foreign( symCol, "C" ); // store Foreign to variable since it is called multiple times
						// LogCCol = log( CCol / Ref( CCol, -1 ) ); 
						// corrARray = Correlation( LogCRow, LogCCol, corrLB );
						// corr = LastValue(corrArray);
						corr = LastValue(Correlation( VarGet("L_" + row), VarGet("L_" + col), corrLB ));
						// corr is a columm 
						mx[col+1][row] = corr;
						// Filling the symmetrical cell
						mx[row+1][col] = corr;
					}
					startCol += 1; // reducing number of cells to fill thanks to to this matrix symmetry
				}    

				_TRACE_("Matrix done. Sorting - Ignoring header");

				
				_TRACE_("Coupling elements");		
				// Print result in the Analisys window using AddRow (to output too if needed)
				for( row = 0; row < size; row++ ) {

					// we need to transpose the matrix to sort by columns - then restore
					// each iteration we sort on a different row for each ticker (rows headers)
					// and row 0 will have the orders of correlated tickers (columns headers)
					mx2 = MxTranspose(mx); 
					mx3 = MxSortRows(mx2, False, row+1); // sort descending
					mx2 = MxTranspose(mx3);  
					symRow = VarGetText("T_" + row);				
					s = symRow + "\t";
					// Skip col 0 since it is 1.0 for the ticker own correlation.... 
					for( col = 1; col <= maxColRes; col++ )
					{
						symIdx = mx2[0][col]; // get the index from row 0
						symCol = VarGetText("T_" + symIdx);				
						corr = mx2[row+1][col];
						s = s + symCol + "\t" + WriteVal( corr, 3.3 ) + "\t";
					}		
					AddRow(s);
				}
				_TRACE_("Coupling elements/Add rows done");	
			} else {
				_TRACE_("Too many tickers. Skipping");	
			}
		} else {
			_TRACE_("Empty watchlist. Skipping");	
		}
	} 
	_TRACE_("Script completed for stock " + WriteVal(stockNum, 4.0));					
} else {
	_TRACE_("Script ignored. Status " + WriteVal(actionStatus, 1.0));					
}

w = 0; // used to set a breakpoint when using the debugger

I too used matrices and addRow().

(In this sample I applied the correlation logic used by @portfoliobuilder - using only the last value of the correlation arrays; you may want to change it as needed).

I use a matrix to store the correlations and then sort it multiple times; one for each ticker row - using the first row of my original unsorted matrix as an index for the tickers to display in the correlation table.

Seems to work well enough for medium/sized watchlists (I used a max of 500 tickers)

To display the resulting data in the Analysis window I used the AddRow() function with the filter set to 0 (search here in the forum other examples about its usage). Unfortunately as said also in the previous message no colors…

For the exploration, you have to choose a watchlist (selecting its corresponding number as a parameter).
In order to get the table a little faster, I suggest setting the analysis filter to “current” (it is actually ignored but the process will be done only once).

This sample raw code (where I left on purpose many commented lines to show how some code was developed/moved/replaced) is provided “as it is” (it is not fully tested); take it mainly as a starting point for your own further development.

(All suggestions to do it in a better way are welcome!)

9 Likes

P.S. Note that all the relevant code happens when the Status(“stocknum”) == 0 so it is run in a SINGLE THREAD (I did it on purpose to be able to easily follow the code logic tracing/debugging).

1 Like

Portfoliobuilder, fxshrat, and beppe…Thank you for all you recommendations and direction! I will consider all and move forward…Happy New Year! Best…Mike

Two questions about this matrix:

(1) Why are the correlation values on both sides of the diagonal sometimes different for the same pair of symbols? Which correlation is then the good one?

(2) If we want to do a correlation analysis using the weekly or monthly returns instead of the daily returns, would changing the "periodicity" in the settings window be the right way to do it? (Assuming that we also change the AFL number of bars lookback also obviously)

Thanks,

Oscar

How do i get Full Name of the ticker in the above matrix
i'm able to get in vertical lines but not on horizintal lines

Good-day Amibroker community,

I would like to seek your valuable help in resolving my challenge. I'm trying to sum up the correlation of each and every symbol for later use in a ranking scheme.
Having browsed through the entire forum it seems there is a discussion about this topic on the old yahoo group but I can't access it... I'm able to sum up the correlation in exploration mode but can't seem to be able to store them into a static variable which is pretty frustrating :slight_smile:

I attempted several ways but nothing I've tried seems to work.. the values I'm after are highlighted in yellow on the screenshot below.

Any help or pointer would be greatly appreciated.

Thanks in advance for your time on this -

ArnaudP.

image

@ArnaudP do you want to store each total in a single staticVar with a name based on the column header (for instance "CorrelationMatrixTotal_APPL", "CorrelationMatrixTotal_AXP", ..., "CorrelationMatrixTotal_TRV") or in some other ways?

Moreover, do you want to include in the totals also the "autocorrelation" value (1.0) as displayed in the screenshot, probably done using a AddSummaryRows(1, 1.2) line?

If you've calculated all the correlations and summed them up, then the hard word is already done. What issue are you having with Static Variables? Could you post some code to help us help you find your error?

@beppe & @mradtke. I appreciate that two legends of the amibroker community are looking into my issue :slight_smile:
@beppe , yes this is exactly what I intend to do, I would like to have the ability to retrieve the ColSum correlation of each asset to add this component to a bigger weighting scheme. Basically what I'm trying to achieve is the FAA (flexible asset allocation) scheme as detailed in the following SSRN paper: https://papers.ssrn.com/sol3/papers.cfm?abstract_id=2193735
While I have no problem coding it with R (as per this example: http://systematicinvestor.github.io/strategy/Strategy-FAA )
I encounter an issue when it comes to getting the sum of the correlation to include to my three components weighting scheme.
And yes you are right I used AddSummaryRows to obtain the top line on my screenshot.
@mradtke the code I generated is essentially the same as the one posted by portfolio builder in the same post, my only trouble is to generate & store the cummulated correlation values of each asset.
I know it may sound trivial but I'm only beginning my journey with AFL and facing my first roadblock -
I intend to share the full FAA implementation to this forum (which is quite close to GTAA by trendXplorer) once successfully succesffuly coded.
Thanks !
ArnaudP

We don't need the full FAA code at this point. Just a simplified version that shows how you're calculating the sum of correlations and attempting to store them in static variables. Without seeing your code, we really have no way to point you in the right direction other than to implement our own solution from scratch. That's not a good use of our time nor the best way for you to improve your AFL skills.

Here is code example

/// @link https://forum.amibroker.com/t/correlation-matrix/1184/4
/// @link https://forum.amibroker.com/t/correlation-matrix/1184/5
CorrLB = Param("Lookback", 252, 50, 300, 1); // daily correlation look-back period//

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

if ( Status( "action" ) == actionExplore ) {

	Filter = Status( "lastbarinrange" );
	SetSortColumns(1); 
	Tickers = Name();

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

	/// added by fxshrat to remove stored values for summary rows
	if( Status( "stocknum" )  == 0 )
		StaticVarRemove( "cs_*" );

	cs = 0;/// added by fxshrat
	// iterate through symbols
	for( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ )
	{
		Frg = Foreign( sym, "C" ); 
		FrgLog = log( Frg / Ref( Frg, -1 ) );
		Corr = Correlation( logC, FrgLog, CorrLB );
		corrcond = Corr>0.6;
		tickercond = Tickers == sym;
		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 );   
		
		cs += SelectedValue(corr);  /// added by fxshrat
	}

	/// @ link https://forum.amibroker.com/t/correlation-matrix/1184/26
	/// added by fxshrat to store summary rows to matrix
	i = Status( "Stocknum" );
	StaticVarAdd( "cs_" + i, cs );
	mat = Matrix( 1, StrCount( symlist, "," ) + 1 );
	for( i = 0; i < MxGetSize(mat,1); i++ )
		mat[0][i] = StaticVarGet( "cs_" + i );	
	StaticVarSet( "SummaryRowsMat", mat );
}

_TRACE( symlist );

/// added by fxshrat to call "summary rows"-matrix
mat = StaticVarGet( "SummaryRowsMat" );
if( typeof(mat) == "matrix" ) {
	mstr = StrReplace(StrReplace(MxToString(mat), "{", ""), "}", "" );
	_TRACE( mstr );
}

418

10 Likes

@fxshrat Thanks a lot this is definitely very helpul. Hopefully it will be of some use for other users too. Cheers