Need Rank Function, equivalent to Excel's "Rank"?

AFL Programming
1

The recent post on looking for the Nth highest had stimulated my curiosity to look for a Rank function as they have in Excel. I have not found the equivalent in AmiBroker, though we have Percentile, PercentRank, Sort, and for generating ranking on securities there is PositionScore and StaticVarGenarateRanks and for explorations AddRankColumn.

But how do we rank for example the High today compared to the past 20 High's ?

I am sure there must be a more elegant way but so far I have come up with the code below. Anyone who can help confirm or improve upon it is welcome to do so. (Please do!)

// rank the InputArray from 1 to "periods"
// 1st, 2nd ... 20th etc. So for example
// today's high is where amongst the past 10 highs

periods = Param( "Periods", 10, 2, 100, 1 );

function Ranker( InputArray, Periods )
{
    Count = periods;

    for( i = 1; i <= Periods - 1 ; i++ )
    {
        Count -= InputArray > Ref( InputArray, -i );
    }

    return Count;
}

for( n = 1; n < periods; n++ )
{
    Ranker( High, n );// input InputArray array here
}


Filter = 1;
AddColumn( H, "High" );
AddColumn( Ranker( H, periods ), "Rank of past(" + periods + ")", 1.0, colorDefault, colorLightYellow );

I seem to be getting correct output upon my preliminary testing.
image

2

Tomasz has once posted a such function at old Yahoo forum.
https://groups.yahoo.com/neo/groups/amibroker/conversations/messages/185528

/// this function returns numeric rank of current value within last N-bars
/// with 1 being the HIGHEST rank, N being the lowest rank
/// @link https://groups.yahoo.com/neo/groups/amibroker/conversations/messages/185528
function NumericRank( array, N ) {
  // by Tomasz Janeczko
  pn = N - (N-1) * PercentRank( array, N-1 ) / 100;  
  return pn;
}

Filter = 1;
AddColumn( H, "High" );
AddColumn( NumericRank( H, periods ), "Rank of past(" + periods + ")", 1.0, colorDefault, colorLightYellow );
5 Likes
3

@fxshrat Thanks!! I had been searching assuming this had already been solved, but I couldn't find it. Then when I saw your post I almost screamed "I can't believe how similar mine is to TJ's", until I realized that you had adapted some of my Explore lines into his code :open_mouth:

I had attempted to use PercentRank somehow knowing I could convert that to the numeric rank, but just could not get to TJ's solution.

Happily, my solution results seem to match up with TJ's.

I wonder if there is a problem dealing with "ties". If two prices in the lookback period are identical, the ranking should be the same (though I know there are different conventions dealing with "ties").

But one example of what appears wrong I found quickly below. A picture of 10 bars with High prices where the most current equals a bar 2 days earlier. But the initial one gets a rank of "2" and the next one gets a rank of "4" ? that doesn't seem correct way to do it to me.

image

Here they are tied for 4th, but listed as 5th ?

image