Ranking numeric fields

I'm a newbie to AFL or C language and embarrassed to even enter this question.
I'm converting a trading system created in Microsoft Excel to AmiBroker.
In Excel, the command =RANK is used throughout the system.
I have started coding the system in AmiBroker and am stumped at RANK.
I've read the AmiBroker Guide on Ranking and it doesn't appear to address my problem.
Basically, I have 3 numeric location and I want to rank them 1,2,3 with the highest number being 1, middle =2, and lowest value =3
I've read Position Ranking, Exploration, StaticVarGenerateRanks, and the function Numeric Rank.
I'll enter the unfinished code and you can read my comments in the code describing what I'm attempting.

//VVIVV2.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Start Date = 1/3/2007  **//


SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
PrevIVV105 = ( Ref( IVV, -105 ) );
PrevIVV20 = ( Ref( IVV, -20 ) );
IVVCalcReturnA = IVV - ( Ref( IVV, - 105 ) );
IVVCalcReturnA_Final = ( IVVCalcReturnA / PrevIVV105 ) * 100;
IVVCalcReturnB = IVV - ( Ref( IVV, - 20 ) );
IVVCalcReturnB_Final = ( IVVCalcReturnB / PrevIVV20 ) * 100;
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
PrevSHY105 = ( Ref( SHY, -105 ) );
PrevSHY20 = ( Ref( SHY, -20 ) );
SHYCalcReturnA = SHY - ( Ref( SHY, - 105 ) );
SHYCalcReturnA_Final = ( SHYCalcReturnA / PrevSHY105 ) * 100;
SHYCalcReturnB = SHY - ( Ref( SHY, - 20 ) );
SHYCalcReturnB_Final = ( SHYCalcReturnB / PrevSHY20 ) * 100;
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
PrevVV105 = ( Ref( VV, -105 ) );
PrevVV20 = ( Ref( VV, -20 ) );
VVCalcReturnA = VV - ( Ref( VV, - 105 ) );
VVCalcReturnA_Final = ( VVCalcReturnA / PrevVV105 ) * 100;
VVCalcReturnB = VV - ( Ref( VV, - 20 ) );
VVCalcReturnB_Final = ( VVCalcReturnB / PrevVV20 ) * 100;
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "GLD", True, True );
GLD = C;
PrevGLD105 = ( Ref( GLD, -105 ) );
PrevGLD20 = ( Ref( GLD, -20 ) );
GLDCalcReturnA = GLD - ( Ref( GLD, - 105 ) );
GLDCalcReturnA_Final = ( GLDCalcReturnA / PrevGLD105 ) * 100;
GLDCalcReturnB = GLD - ( Ref( GLD, - 20 ) );
GLDCalcReturnB_Final = ( GLDCalcReturnB / PrevGLD20 ) * 100;
GLDCalcVolatility = StDev( log( GLD / Ref( GLD, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "TIP", True, True );
TIP = C;
PrevTIP105 = ( Ref( TIP, -105 ) );
PrevTIP20 = ( Ref( TIP, -20 ) );
TIPCalcReturnA = TIP - ( Ref( TIP, - 105 ) );
TIPCalcReturnA_Final = ( TIPCalcReturnA / PrevTIP105 ) * 100;
TIPCalcReturnB = TIP - ( Ref( TIP, - 20 ) );
TIPCalcReturnB_Final = ( TIPCalcReturnB / PrevTIP20 ) * 100;
TIPCalcVolatility = StDev( log( TIP / Ref( TIP, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();


//** Converting from a Microsoft EXCEL system, I'm struggling to find a corresponging AFL for RANK **//
//** From the above code, I'd like to RANK IVVCalcReturnA_Final, SHYCalcReturnA_Final, and VVCalcReturnA_Final **//
//** The highest numerical value of the 3 locations would be 1, middle would be 2, lowest would be 3 **//
//** The Excel code is RANK(H3362,H3362:J3362) where H3362 holds the value from IVVCalcReturnA_Final, **//
//** the H3362:H:JI3362 cover the range of the rank; I3362 holds the value from SHYCalcReturnA_Final, **//
//** J3362 holds the value from VVCalcReturnA_Final.
//** An Explore run on 5/1/202 shows IVVCalcReturnA_Final value is -9.15, SHYCalcReturnA_Final value is 3.01, **//
//** VVCalcReturnA_Final value is -8.82. Ranking would have SHYCalcReturnA_Final =1, VVCalcReturnA_Final =2, **//
//**  IVVCalcReturnA_Final =3.



Filter = 1;
AddColumn( IVV, "IVV close" );
AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
AddColumn( IVVCalcVolatility, "IVV Vol" );
AddColumn( SHY, "SHY close" );
AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
AddColumn( SHYCalcVolatility, "SHY Vol" );
AddColumn( VV, "VV close" );
AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
AddColumn( VVCalcReturnB_Final, "VV RET B" );
AddColumn( VVCalcVolatility, "VV Vol" );
AddColumn( GLD, "GLD close" );
AddColumn( GLDCalcReturnA_Final, "GLD RET A" );
AddColumn( GLDCalcReturnB_Final, "GLD RET B" );
AddColumn( GLDCalcVolatility, "GLD Vol" );
AddColumn( TIP, "TIP close" );
AddColumn( TIPCalcReturnA_Final, "TIP RET A" );
AddColumn( TIPCalcReturnB_Final, "TIP RET B" );
AddColumn( TIPCalcVolatility, "TIP Vol" );



RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);


The sorting or ranking code is cleanly described here:

To avoid repeating the same, all you have to do is assign your last values from the three arrays to the input matrix in the code from by @beppe

// Code extracted from Beppe's post
// https://forum.amibroker.com/t/working-with-matrices-ranking/7880/2

rows = 3;
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] = LastValue( IVVCalcReturnA_Final );
mat[1][COL_VALUES] = LastValue( SHYCalcReturnA_Final );
mat[2][COL_VALUES] = LastValue( VVCalcReturnA_Final );

// then the rest of the code for sorting / ranking.

Your 3 variables are Arrays and i am assuming that you need the last value of each array to be ranked.

This method would scale up well, you could use a crude way of comparing the largest values and assigning for 3 variables also.

Edit: Are you looking for a bar-by-bar ranking of the 3 Arrays (each array index)?
StaticVarGenerateRanks with "rank" would be the way.

1 Like

@suncoastbill I think I would simplify the process by putting the Symbols you are interested in into one Watch List. Then I think your calculations could be simplified too. Each place that you have these 3 lines can be replaced by the Rate-of-Change indicator.

PrevIVV105 = ( Ref( IVV, -105 ) );
IVVCalcReturnA = IVV - ( Ref( IVV, - 105 ) );
IVVCalcReturnA_Final = ( IVVCalcReturnA / PrevIVV105 ) * 100;

is really

ROC(IVV, 105);

And run a simple StaticVarGenerateRanks over your WL and generate this type of result
image

2 Likes

And to just get rank output you don't even need StaticVarGenerateRanks.

You would just have to add AddRankColumn to Explorer code and run it over your watchlist.

Filter = Status("lastbarinrange");

AddColumn( ROC(C,20), "ROC20", 1.2, -1, -1, 80 ); 
AddColumn( ROC(C,105), "ROC105", 1.2, -1, -1, 80 ); 

/// @link https://www.amibroker.com/guide/afl/addrankcolumn.html
SetSortColumns( -4 ); 
AddRankColumn(); // rank according to 4th column (descending) 
SetSortColumns( -3 ); 
AddRankColumn(); // rank according to 3rd column (ascending)

12

2 Likes

My thanks to all responders. AmiBroker support is a world class operation. #1 in my opinion.
Based on suggestions, I've reduced the code in the system and have attempted the Exploration method.
Unfortunately, I've been unable to make it work correctly. When it does work correctly, will I be able to reference the Exploration Rank (1, 2, 3) in my code. Reason is that I actually will use the rank value in future calculations.
To answer another question, YES, I would prefer a bar by bar ranking, so maybe the StaticVarGenerateRanks is the best option.
Any help with that would be appreciates as well.
Following is my updated code:

//VVIVV2.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Start Date = 1/3/2007  **//

listname = "Rank";
category = categoryWatchlist;
listnum = CategoryFind( listname, category );
list = CategoryGetSymbols( category, listnum);

SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
IVVCalcReturnA_Final = ROC (IVV, 105);
IVVCalcReturnB_Final = ROC (IVV, 20);
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
SHYCalcReturnA_Final = ROC (SHY, 105);
SHYCalcReturnB_Final = ROC(SHY, 20);
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
VVCalcReturnA_Final = ROC(VV, 105);
VVCalcReturnB_Final = ROC(VV, 20);
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();



//** Converting from a Microsoft EXCEL system, I'm struggling to find a corresponging AFL for RANK **//
//** From the above code, I'd like to RANK IVVCalcReturnA_Final, SHYCalcReturnA_Final, and VVCalcReturnA_Final **//
//** The highest numerical value of the 3 locations would be 1, middle would be 2, lowest would be 3 **//
//** The Excel code is RANK(H3362,H3362:J3362) where H3362 holds the value from IVVCalcReturnA_Final, **//
//** the H3362:H:JI3362 cover the range of the rank; I3362 holds the value from SHYCalcReturnA_Final, **//
//** J3362 holds the value from VVCalcReturnA_Final.
//** An Explore run on 5/1/202 shows IVVCalcReturnA_Final value is -9.15, SHYCalcReturnA_Final value is 3.01, **//
//** VVCalcReturnA_Final value is -8.82. Ranking would have SHYCalcReturnA_Final =1, VVCalcReturnA_Final =2, **//
//**  IVVCalcReturnA_Final =3.

rows = 3;

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] = LastValue( IVVCalcReturnA_Final );

mat[1][COL_VALUES] = LastValue( SHYCalcReturnA_Final );

mat[2][COL_VALUES] = LastValue( VVCalcReturnA_Final );



// then the rest of the code for sorting / ranking.

Filter = Status("lastbarinrange");



AddColumn( IVVCalcReturnA_Final, "IVVCalcReturnA_Final", 1.2, -1, -1, 80 ); 

AddColumn( SHYCalcReturnA_Final, "SHYCalcReturnA_Final", 1.2, -1, -1, 80 );

AddColumn( VVCalcReturnA_Final, "VVCalcReturnA_Finall", 1.2, -1, -1, 80 );


SetSortColumns( -5 ); 

AddRankColumn(); // rank according to 4th column (descending) 

SetSortColumns( -4 ); 

AddRankColumn(); // rank according to 3rd column (ascending)

SetSortColumns( -3 ); 

AddRankColumn(); // rank according to 2nd column (descending) 









//Filter = 1;
//AddColumn( IVV, "IVV close" );
//AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
//AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
//AddColumn( IVVCalcVolatility, "IVV Vol" );
//AddColumn( SHY, "SHY close" );
//AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
//AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
//AddColumn( SHYCalcVolatility, "SHY Vol" );
//AddColumn( VV, "VV close" );
//AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
//AddColumn( VVCalcReturnB_Final, "VV RET B" );
//AddColumn( VVCalcVolatility, "VV Vol" );
//AddColumn( mat[0][COL_VALUES], "mat 0" );
//AddColumn( mat[1][COL_VALUES], "mat 1" );
//AddColumn( mat[2][COL_VALUES], "mat 2" );



RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);

Following is my Exploration output:
image

Thanks again for your assistance

Perhaps this screenshot will be easier to read>
image

@suncoastbill if you are looking for examples of using StaticVarGenerateRanks there are many on this forum, and in the User Guide
https://www.amibroker.com/guide/afl/staticvargenerateranks.html

I generated the exploration output in the earlier post with this

// in AA window run this analysis on a WL that contains the symbols you are testing
wlnum = GetOption( "FilterIncludeWatchlist" ); 
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

StaticVarRemove( "values*" );
for( n = 0; ( Symbol = StrExtract( List, n ) )  != "";  n++ )
{
    SetForeign( symbol );
    values = ROC( C, 105 );
    RestorePriceArrays();
    StaticVarSet( "values"  +  symbol, values );
}

StaticVarGenerateRanks( "rank", "values", 0, 1224 );

symbol   = Name();
svValues = StaticVarGet( "values" +  symbol );
svRank   = StaticVarGet( "rankvalues" +  symbol );

// explore the calculations //
Filter = 1;
AddColumn( svValues, "ROC(105)" );
AddColumn( svRank, "rank", 1.0 );
SetSortColumns( 4 );
2 Likes

I've run into another problem and have been unable to solve it.
With the following code I expected that the ROC-105 and ROC-20 columns would have a ranking of 1, 2, or 3. The values for the ROC-105 and ROC-20 are correct and the ranking for ROC-20 is correct. The ROC-105 ranking should have the highest of the values as 1, middle as 2, lowest as 3, similar to the ranks for ROC-20. See screenshot after the code.

//VVIVV2.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Start Date = 1/3/2007  **//

wlnum = GetOption( "FilterIncludeWatchlist" );
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
IVVCalcReturnA_Final = ROC( IVV, 105 );
IVVCalcReturnB_Final = ROC( IVV, 20 );
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
SHYCalcReturnA_Final = ROC( SHY, 105 );
SHYCalcReturnB_Final = ROC( SHY, 20 );
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
VVCalcReturnA_Final = ROC( VV, 105 );
VVCalcReturnB_Final = ROC( VV, 20 );
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();


if( Status( "stocknum" ) == 0 )
    StaticVarRemove( "ValuesToSort*" );

for( n = 0; ( Symbol = StrExtract( list, n ) )  != "";  n++ )
{

    SetForeign( symbol );
    Value = ROC( C, 105 );    
    Value2 = ROC( C, 20 );
    RestorePriceArrays();    
    StaticVarSet( "ValuesToSort"  +  symbol, Value );
    StaticVarSet( "ValuesToSort2"  +  symbol, Value2 );
}

StaticVarGenerateRanks( "rank", "ValuesToSort", 0, 1224 );
StaticVarGenerateRanks( "rank2", "ValuesToSort2", 0, 1224 );


symbol   = Name();

ROC105Values = StaticVarGet( "ValuesToSort" +  symbol );
ROC105Rank   = StaticVarGet( "rankValuesToSort" +  symbol );
ROC20Values2 = StaticVarGet( "ValuesToSort2" + symbol );
ROC20Rank2   = StaticVarGet( "rank2ValuesToSort2" +  symbol );


// explore the calculations //

//Filter = 1;
Filter = Status("lastbarinrange");

AddColumn( ROC105Values, "ROC105_Values",1.2, -1, -1, 80 );
AddColumn( ROC105Rank, "ROC105_Rank", 1.0 );
AddColumn( ROC20Values2, "ROC20_Values",1.2, -1, -1, 80 );
AddColumn( ROC20Rank2, "ROC20_Rank2", 1.0 );

//SetSortColumns( -4 );
//AddRankColumn();
//SetSortColumns( -3 );
//AddRankColumn();
//SetSortColumns( -5 );
//AddRankColumn();

//Filter = 1;
//AddColumn( IVV, "IVV close" );
//AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
//AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
//AddColumn( IVVCalcVolatility, "IVV Vol" );
//AddColumn( SHY, "SHY close" );
//AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
//AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
//AddColumn( SHYCalcVolatility, "SHY Vol" );
//AddColumn( VV, "VV close" );
//AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
//AddColumn( VVCalcReturnB_Final, "VV RET B" );
//AddColumn( VVCalcVolatility, "VV Vol" );




RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);


image

Thanks for all your assistance.
Stay safe... Bill

You are likely confusing AmiBroker by trying to use static variable names that are too similar, specifically
ValuesToSort and ValuesToSort2. Try calling them ValuesToSort1 and ValuesToSort2 instead, or use completely different names that are more descriptive and where one is not a substring of the other.

2 Likes

While it is good idea to have distinct names for humans, from program perspective as long as identifiers are unique it is OK.

1 Like

@Tomasz my assumption was that when someone makes a call like this:

StaticVarGenerateRanks( "rank", "ValuesToSort", 0, 1224 );

Then AmiBroker will look for all static variables that start with the string "ValuesToSort". But in the example from @suncoastbill, that would include all the static variables that start "ValuesToSort" as well as the ones that start with "ValuesToSort2". Perhaps this is not actually a problem for AmiBroker.

2 Likes

@suncoastbill I haven't figured out what is wrong with your code but I used my own code below

// in AA window run this analysis on a WL that contains the symbols you are testing
wlnum = GetOption( "FilterIncludeWatchlist" );
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

if( Status( "stocknum" ) == 0 )
{
    StaticVarRemove( "values*" );

    for( n = 0; ( Symbol = StrExtract( List, n ) )  != "";  n++ )
    {

        SetForeign( symbol );
        
        Values105 = ROC( C, 105 );
        Value20	  = ROC( C, 20 );
        
        RestorePriceArrays();
        
        StaticVarSet( "Values105"  +  symbol, Values105 );
        StaticVarSet( "values20"  +  symbol, Value20 );

    }

    StaticVarGenerateRanks( "rank", "Values105", 0, 1224 );
    StaticVarGenerateRanks( "rank", "values20", 0, 1224 );
}

symbol   	= Name();
svValues105 = StaticVarGet( "Values105" +  symbol );
svValues20  = StaticVarGet( "values20" +  symbol );

svRank105	= StaticVarGet( "rankValues105" +  symbol );
svRank20	= StaticVarGet( "rankvalues20" +  symbol );

// explore the calculations //

Filter = Status("lastbarinrange");

AddColumn( svValues105, "svValues105" );
AddColumn( svRank105, "Rank105", 1.0 );
AddColumn( svValues20, "svValues20" );
AddColumn( svRank20, "rank20", 1.0 );
SetSortColumns( 4 );

To produce this output,
image

2 Likes

You are right. Spot on.

2 Likes

Thanks again for the terrific assistance.
I have managed, using all the support here, to get the code to work.
However, there is one problem that I have been unable to make work.
The last of the 3 items I am ranking (VolaRank) should be ranked in ascending order. I'd like the lowest value to be ranked 1, the highest value to be ranked 3.
The first 2 items are ranked correctly as I want them in descending order.
I've tried using StaticVarGenerateRanks as well as AddRankColumn without success.
I'm sure I'm overlooking something but need help.
Thank you for all the great assistance.
Stay safe... Bill
Following is my code:

//VVIVV2_Rank.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Start Date = 1/3/2007  **//

wlnum = GetOption( "FilterIncludeWatchlist" );
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
IVVCalcReturnA_Final = ROC( IVV, 105 );
IVVCalcReturnB_Final = ROC( IVV, 20 );
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
SHYCalcReturnA_Final = ROC( SHY, 105 );
SHYCalcReturnB_Final = ROC( SHY, 20 );
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
VVCalcReturnA_Final = ROC( VV, 105 );
VVCalcReturnB_Final = ROC( VV, 20 );
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();


if( Status( "stocknum" ) == 0 )
    StaticVarRemove( "ValuesToSort*" );


for( n = 0; ( Symbol = StrExtract( list, n ) )  != "";  n++ )
{

    SetForeign( symbol );
    Values105 = ROC( C, 105 );
    Values20 = ROC( C, 20 );
    ValuesStd = StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100;
    RestorePriceArrays();
    StaticVarSet( "ValuesToSort105"  +  symbol, Values105 );
    StaticVarSet( "ValuesToSort20"  +  symbol, Values20 );
    StaticVarSet( "ValuesToSortStd"  +  symbol, ValuesStd );
}

StaticVarGenerateRanks( "rank", "ValuesToSort105", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSort20", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSortStd", 0, 1224 );
//StaticVarGenerateRanks( "bot", "ValuesToSortStd", -3, 1224 );


symbol   = Name();

ROC105Values = StaticVarGet( "ValuesToSort105" +  symbol );
ROC20Values = StaticVarGet( "ValuesToSort20" + symbol );
VolaValues   = StaticVarGet( "ValuesToSortStd" +  symbol );
ROC105Rank   = StaticVarGet( "rankValuesToSort105" +  symbol );
ROC20Rank   = StaticVarGet( "rankvaluesToSort20" +  symbol );
VolaRank     = StaticVarGet( "rankValuesToSortStd" +  symbol );
//VolaRank     = StaticVarGet( "botValuesToSortStd" +  symbol );


// explore the calculations //

//Filter = 1;
Filter = Status( "lastbarinrange" );

AddColumn( ROC105Values, "ROC105_Values" );
AddColumn( ROC105Rank, "ROC105_Rank" );
AddColumn( ROC20Values, "ROC20_Values" );
AddColumn( ROC20Rank, "ROC20_Rank" );
AddColumn( VolaValues, "VolaValues" );
AddColumn( VolaRank, "VolaRank" );
//SetSortColumns( -7 );
//AddRankColumn();


//SetSortColumns( -6 );
//AddRankColumn();
//SetSortColumns( -3 );
//AddRankColumn();
//SetSortColumns( -5 );
//AddRankColumn();

//Filter = 1;
//AddColumn( IVV, "IVV close" );
//AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
//AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
//AddColumn( IVVCalcVolatility, "IVV Vol" );
//AddColumn( SHY, "SHY close" );
//AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
//AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
//AddColumn( SHYCalcVolatility, "SHY Vol" );
//AddColumn( VV, "VV close" );
//AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
//AddColumn( VVCalcReturnB_Final, "VV RET B" );
//AddColumn( VVCalcVolatility, "VV Vol" );




RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);


@suncoastbill one method is that you can manipulate the value of Std Dev for ranking purposes by creating a variable that will inversely rank them. For example 100 - ValuesStd and rank that new variable. Or 1/ValuesStd should also work.

    ValuesStd =100-( StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100);

image

Also the third parameter in the function (topranks) I believe can allow you to rank inversely, but I have never tried that.
StaticVarGenarateRanks( "outputprefix", "inputprefix", topranks, tiemode )

3 Likes

Thanks so much.
Creating another variable with the changes you provided solved the problem.
What a relief!
Below is my completed code to this point:

//VVIVV2_Rank.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Start Date = 1/3/2007  **//

wlnum = GetOption( "FilterIncludeWatchlist" );
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
IVVCalcReturnA_Final = ROC( IVV, 105 );
IVVCalcReturnB_Final = ROC( IVV, 20 );
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
SHYCalcReturnA_Final = ROC( SHY, 105 );
SHYCalcReturnB_Final = ROC( SHY, 20 );
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
VVCalcReturnA_Final = ROC( VV, 105 );
VVCalcReturnB_Final = ROC( VV, 20 );
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

if( Status( "stocknum" ) == 0 )
    StaticVarRemove( "ValuesToSort*" );

for( n = 0; ( Symbol = StrExtract( list, n ) )  != "";  n++ )
{
    SetForeign( symbol );
    Values105 = ROC( C, 105 );
    Values20 = ROC( C, 20 );
    ValuesStd = StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100;
    ValuesStd3 = 100 - ( StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100 ); 
    RestorePriceArrays();
    StaticVarSet( "ValuesToSort105"  +  symbol, Values105 );
    StaticVarSet( "ValuesToSort20"  +  symbol, Values20 );
    StaticVarSet( "ValuesToSortStd"  +  symbol, ValuesStd );
    StaticVarSet( "ValuesToSortAscending"  +  symbol, ValuesStd3 );
}

StaticVarGenerateRanks( "rank", "ValuesToSort105", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSort20", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSortStd", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSortAscending", 0, 1224 );

symbol   = Name();

ROC105Values = StaticVarGet( "ValuesToSort105" +  symbol );
ROC20Values = StaticVarGet( "ValuesToSort20" + symbol );
VolaValues   = StaticVarGet( "ValuesToSortStd" +  symbol );
ROC105Rank   = StaticVarGet( "rankValuesToSort105" +  symbol );
ROC20Rank   = StaticVarGet( "rankvaluesToSort20" +  symbol );
VolaRank     = StaticVarGet( "rankValuesToSortAscending" +  symbol );


// explore the calculation values //

Filter = 1;
//Filter = Status( "lastbarinrange" );

AddColumn( ROC105Values, "ROC105_Values" );
AddColumn( ROC105Rank, "ROC105_Rank" );
AddColumn( ROC20Values, "ROC20_Values" );
AddColumn( ROC20Rank, "ROC20_Rank" );
AddColumn( VolaValues, "VolaValues" );
AddColumn( VolaRank, "VolaRankAscending" );


//Filter = 1;
//AddColumn( IVV, "IVV close" );
//AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
//AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
//AddColumn( IVVCalcVolatility, "IVV Vol" );
//AddColumn( SHY, "SHY close" );
//AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
//AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
//AddColumn( SHYCalcVolatility, "SHY Vol" );
//AddColumn( VV, "VV close" );
//AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
//AddColumn( VVCalcReturnB_Final, "VV RET B" );
//AddColumn( VVCalcVolatility, "VV Vol" );




RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);


3 Likes

Thanks again for all the assistance.
The output is exactly what I was after.
I could actually just use it as is for trading.
But, if I could add Buy/SELL criteria it would be preferable.
I've added the line 'SumRank_Weight line which gives me the values I need to trade
I've tried to find in the guide examples of how to extract from this output just the SHY ticker and it's associated data without any success.
The logic to this is that if IVV or VV are ranked 1 (ascending ordering this case), then BUY.
If SHY is ranked 1 (in ascending order), then SELL.
So, on 5/15/2020, IVV was 2.7, SHY 1.35, VV 1.95. Since SHY is lowest, it is #1, so SELL
This is not a pressing issue with me but it would help me with my AFL education as well.
In any case, I'm thrilled to be able to execute this in AmiBroker with lightning speed compared to the snail pace of Microsoft Excel.
Thanks once again to you guys. Bill

//VVIVV2_Rank.afl
//** Based on ETFReplay function **//
//** StDev from https://alvarezquanttrading.com/amibroker-sample-code **//
//** Run Exploration to see final ranks **//
//** If IVV or VV ranks above SHY, BUY. Sell when SHY ranks ahead of IVV and VV **//
//** Ranks are ascending, so 1 = highest, 2 = second, 3 = last **//
//** Start Date = 1/3/2007  **//

wlnum = GetOption( "FilterIncludeWatchlist" );
List  = CategoryGetSymbols( categoryWatchlist, wlnum ) ;

SetTradeDelays( 0, 0, 0, 0 );
SetForeign( "IVV", True, True );
IVV = C;
IVVCalcReturnA_Final = ROC( IVV, 105 );
IVVCalcReturnB_Final = ROC( IVV, 20 );
IVVCalcVolatility = StDev( log( IVV / Ref( IVV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "SHY", True, True );
SHY = C;
SHYCalcReturnA_Final = ROC( SHY, 105 );
SHYCalcReturnB_Final = ROC( SHY, 20 );
SHYCalcVolatility = StDev( log( SHY / Ref( SHY, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

SetForeign( "VV", True, True );
VV = C;
VVCalcReturnA_Final = ROC( VV, 105 );
VVCalcReturnB_Final = ROC( VV, 20 );
VVCalcVolatility = StDev( log( VV / Ref( VV, -1 ) ), 10 ) * sqrt( 252 ) * 100;
RestorePriceArrays();

if( Status( "stocknum" ) == 0 )
    StaticVarRemove( "ValuesToSort*" );

for( n = 0; ( Symbol = StrExtract( list, n ) )  != "";  n++ )
{
    SetForeign( symbol );
    Values105 = ROC( C, 105 );
    Values20 = ROC( C, 20 );
    ValuesStd = StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100;
    ValuesStd3 = 100 - ( StDev( log( C / Ref( C, -1 ) ), 10 ) * sqrt( 252 ) * 100 );
    RestorePriceArrays();
    StaticVarSet( "ValuesToSort105"  +  symbol, Values105 );
    StaticVarSet( "ValuesToSort20"  +  symbol, Values20 );
    StaticVarSet( "ValuesToSortStd"  +  symbol, ValuesStd );
    StaticVarSet( "ValuesToSortAscending"  +  symbol, ValuesStd3 );
}

StaticVarGenerateRanks( "rank", "ValuesToSort105", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSort20", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSortStd", 0, 1224 );
StaticVarGenerateRanks( "rank", "ValuesToSortAscending", 0, 1224 );

symbol   = Name();

ROC105Values = StaticVarGet( "ValuesToSort105" +  symbol );
ROC20Values = StaticVarGet( "ValuesToSort20" + symbol );
VolaValues   = StaticVarGet( "ValuesToSortStd" +  symbol );
ROC105Rank   = StaticVarGet( "rankValuesToSort105" +  symbol );
ROC20Rank   = StaticVarGet( "rankvaluesToSort20" +  symbol );
VolaRank     = StaticVarGet( "rankValuesToSortAscending" +  symbol );

SumRank_Weight = ( ROC105Rank * 0.35 ) + ( ROC20Rank * 0.35 ) + ( VolaRank * 0.30 );


// explore the calculation values //

//Filter = 1;
Filter = Status( "lastbarinrange" );

AddColumn( ROC105Values, "ROC105_Values" );
AddColumn( ROC105Rank, "ROC105_Rank" );
AddColumn( ROC20Values, "ROC20_Values" );
AddColumn( ROC20Rank, "ROC20_Rank" );
AddColumn( VolaValues, "VolaValues" );
AddColumn( VolaRank, "VolaRankAscending" );
AddColumn( SumRank_Weight, "SumRank_Weight" );




//Filter = 1;
//AddColumn( IVV, "IVV close" );
//AddColumn( IVVCalcReturnA_Final, "IVV RET A Final" );
//AddColumn( IVVCalcReturnB_Final, "IVV RET B" );
//AddColumn( IVVCalcVolatility, "IVV Vol" );
//AddColumn( ROC105Values, "ROC105 Values" );
//AddColumn( SHY, "SHY close" );
//AddColumn( SHYCalcReturnA_Final, "SHY RET A Final" );
//AddColumn( SHYCalcReturnB_Final, "SHY RET B" );
//AddColumn( SHYCalcVolatility, "SHY Vol" );
//AddColumn( VV, "VV close" );
//AddColumn( VVCalcReturnA_Final, "VV RET A Final" );
//AddColumn( VVCalcReturnB_Final, "VV RET B" );
//AddColumn( VVCalcVolatility, "VV Vol" );




RestorePriceArrays();

//BuyPrice = C;
//SellPrice = C;

//OnBuy = Flip(BuySig,SellSig);
//OnSell = Flip(SellSig,BuySig);


@suncoastbill what your strategy intends is somewhat unclear to me. Are you using the calculations to buy one security otherwise remain in cash? Or are you planning a rotational strategy between the 3 different ETF's depending upon their final weighted rank?

If you plan on rotating between multiple ETF's there are many posts on this forum with examples of code for rotational trading. Also a starting point in the official Knowledge Base, and guidance in the User Guide/Manual
http://www.amibroker.com/kb/2016/04/17/long-only-rotational-back-test/
https://www.amibroker.com/guide/afl/enablerotationaltrading.html

May I also point out that two of your symbols are ETF's that are almost identical in both their performance and their constituents so there is not likely much diversification to be gained (see relative performance chart of IVV and VV almost perfectly overlap)
image

2 Likes

Thanks again for the incredibly fast response and sorry for not being more specific about the intent of the code.
I use this system to trade a conservative balanced mutual fund.
Before I purchased AmiBroker I coded the logic into Microsoft Excel. It worked but was very slow.
I thought it would be educational for me to code it in AmiBroker.
The logic is this:
It is a bi-weekly system, checking on the 15th and last day of the month to see which of the 3 ETFs are ranked '1'. If VV or IVV are '1', BUY or stay on a BUY. If SHY is ranked '1', SELL.
This system triggered a SELL on 2/28/2020 which was, I thought, good.

I could use this system as it is now, just running an Exploration on the 15th and end of the month to check the ranking.
But, I thought it would be nice to actually code BUY/SELL triggers to enhance it even more.
My problem is that I don't know how to extract the symbol with their ranking from the SumRank_Weight statement.
Any help in pointing me in the right direction to investigate this would be great.
Thanks also for providing the chart. Perhaps I can play with different Etf's now that the system runs lightning fast.
As stated before, I'm thrilled to have it working as is in Exploration and could certainly live with it.
You guys have been magnificent in getting me this far and I've learned a lot.
Hopefully, I have explained my little system better.
Thanks again.... Bill