Working with matrices - Ranking

AFL Programming
#1

I have some calculated values that I have combined into a matrix. I wish to rank the values, and the output should be the corresponding text value, ranked by the calculated value.

I have looked at the online help, and the forum, and I can not piece together enough to be able to find a solution. Any suggestions as always very much appreciated.

Here is as far as I got.

mat = Matrix( 6, 2, 0 );

mat[0][0] = -127;
mat[1][0] = 54;
mat[2][0] = 73;
mat[3][0] = 19;
mat[4][0] = -57;
mat[5][0] = 0;

mat[0][1] = "Straddle";
mat[1][1] = "Strangle";
mat[2][1] = "Butterfly";
mat[3][1] = "Bull Spread";
mat[4][1] = "Collar";
mat[5][1] = "LongCall";

printf("\nMatrix");
printf(MxToString( mat ) );

This shows what I trying to do using Excel
(upload://yV4ldsEBc9ziYZvPnk5N6sQetK1.png)

#2

@tanholm, unfortunately, the upload of the image did not work.

Moreover, I remember you that when you post some code you should always use the code tags:

Anyway here is a snippet to try (not sure it does what you want since the Excel snapshot is missing):

Version( 6.28 ); // tested version

rows = 6;
cols = 2;
COL_VALUES = 0;
COL_SINDEX = 1;

// create the matrix and fill it with zeros
mat = Matrix( rows, cols, 0 );

// Matrix columns rows are accessed with a row index (0... number of rows-1)
// and columns via a column index (0... number of columns-1) 

// first Matrix column is used to store you custom "ranking" values - Column 0
mat[0][COL_VALUES] = -127;
mat[1][COL_VALUES] =  54;
mat[2][COL_VALUES] = 193;
mat[3][COL_VALUES] =  19;
mat[4][COL_VALUES] = -57;
mat[5][COL_VALUES] =   0;

// second matrix column is used as an index to associate the following list of strategies  - Column 1
for( row = 0; row < rows; row++ )
    mat[row][COL_SINDEX] = row; // the "strategy index" is simply the progressive row number

// third "virtual" column since Matrices DO NOT support strings!
// create a list separating each item with a comma
strategies = "Straddle,Strangle,Butterfly,BullSpread,Collar,LongCall";

printf( "ORIGINAL Matrix" );
printf( MxToString( mat ) );
printf( "\n" );

// Sort column 0 descending - i.e. Rank values)
matSorted = mxSortRows( mat, False, 0 );
printf( "SORTED Matrix" );
printf( MxToString( matSorted ) );
printf( "\n" );

for( row = 0; row < rows; row++ )
{
    strategyIndex = matSorted[row][COL_SINDEX]; // Retrieve the orignal order to associate the strategy
    printf( "#%2.0f - Rank value: %8.2f - Associated strategy: %s\n",
            row + 1, matSorted[row][COL_VALUES], StrExtract( strategies, strategyindex ) );
}

Before using something along this lines, be sure to take note of the method that the MxSortRows() function uses to handle the ties (equally ranked values).
Do a test the code using 2 or more equal values: the final rank will be based on the COL_INDEX values.

5 Likes
Question regarding sorting arrays
Ranking numeric fields
OSAKA sort -- replace with matrix
#3

Thank you very much. I have had a quick look and that seems like the trick. I apologise for not making this easier for you with the missing screen shot. I will review in detail. Once again thank you.

1 Like
#4

Thank you Beppe. I have worked through what you have created so I understand exactly how it is constructed. My next step seems relatively simple but I can not work it out. As a disclaimer, I struggle with loops and that it what the internet seems to be pushing me towards.

My next question is this,
I would like to know which position in the ranking, IE which row number, is the strategy

For example, the Straddle is the first strategy, row zero, and ends up in the sorted matrix as row 5.

How do I code, in English: find the 0 row entry and return its row position in the sorted matrix (as an integer).

Very much appreciative in anticipation.

#5 
// code of before ....

EnableTextOutput(0);
getpos = 0;// position in unsorted matrix
getposname = StrExtract(strategies, getpos);// find strategy name of unsorted position
printf( "\nStrategy to be found: <b>%s</b>, its <b>unsorted</b> index: %g", getposname, getpos );
// extract sorted position 
for( idx = 0; idx < rows; idx++ ) {
	if( matsorted[idx][COL_INDEX] == getpos )
		break;
}

printf( "\n<b>Sorted</b> index of looked up strategy <b>%s</b>: %g", getposname, idx);

// get value to check
printf( "\nValue of lookup strategy in unsorted matrix: %g", mat[getpos][0] ); 
printf( "\nValue of lookup strategy in sorted matrix: %g", matSorted[idx][0] );

121

2 Likes
#6

Thank you fxshrat. I was expecting something like that.
I am very grateful for your assistance, once again if I am not mistaken.
That's seems to work quite well. Hopefully I can share what I have created in a few weeks.
:relieved:

1 Like