Sum all ranks in this formula

HI all, I slightly modified this formula below. I simply need to sum all the colums ranks. Is there a
simple way to do that? I was able to create the correct ranks colums for my purpose and my objective is to obtain the same score result as this excel below (last blue column):

picture

here is the formula below. Thanks in advance

//Alpha, Beta, R2 Formula
Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600  ALLSTOXX50=EURO STOXX 50
MKT = Foreign(Ticker,"C",1);
AP=Param("Alpha Period",200,100,300,5);
/*AP=Optimize("Alpha Period",200,100,300,5);*/
Beta=((AP * Sum(ROC( C,1) * ROC(MKT,1),AP))
- (Sum(ROC(C,1),AP) * Sum(ROC( MKT,1),AP)))
/ ((AP * Sum((ROC(MKT,1)^2 ),AP)) - (Sum(ROC(MKT,1 ),AP)^2 ));
Alpha=AP*(Sum(ROC( C,1) ,AP) - (Beta) * Sum( ROC(MKT,1),AP )) / AP;
R2=Correlation(MKT,C,AP)^2;
//Chart Settings
dynamic_color = IIf( ALPHA > 0, colorDarkGreen, colorDarkRed );
Plot(Alpha, "Alpha", dynamic_color,
styleHistogram | styleThick );
Title= FullName() +
" vs. "+ ticker + " Period = "+ WriteVal(AP,format=1.2) + " ALPHA = "+ WriteVal(Alpha,format=1.2) +
" BETA = "+ WriteVal(Beta,format=1.2) + " R~SQUARED = "+ WriteVal(R2,format=1.2);
//System AND Backtest Settings
EnableRotationalTrading();
SetOption("worstrankheld",150);
SetOption("Maxopenpositions",52);
SetOption("allowpositionshrinking",True);
PositionSize = -2;
PositionScore = 100 + alpha ;
PositionScore = Max( PositionScore, 0 );
//Exploration Report
Filter = 1;
P=Param("Period",65,10,200,5,1);
Z= (C - MA(C,P)) / (StDev( C,P));
AddTextColumn(FullName(),"Description");
//AddTextColumn( IndustryID(1), "Industry" );
AddColumn(Close,"Close",1.2);
AddColumn(Alpha,"Alpha",1.2);
SetSortColumns( -5 );
AddRankColumn();// rank according to 5rd column (descending)
AddColumn(Beta,"Beta",1.2);
SetSortColumns( 6 );
AddRankColumn();
AddColumn(R2,"R-squared",1.2);
SetSortColumns( 7 );
AddRankColumn();
AddColumn(Z,"Z-Score 3 month", 1.2);
SetSortColumns( 8 );
AddRankColumn();
AddColumn(StDev( ROC(C,1),30)*sqrt(256),"Volatility",1.2);
SetSortColumns( 9 );
AddRankColumn();
AddColumn(C/HHV( High, 256 ), "% 52 week high", 1.2);
AddColumn( ROC( Close, 256 ), "ROC(256)", 1.1 );
AddColumn(StochK( 256 ),"Stochastic(1yr)", 1.2); 
Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600  ALLSTOXX50=EURO STOXX 50
MKT = Foreign(Ticker,"C",1);
//IBD RS RANKING
rs01 = 0.4 * (C- Ref(C, -12)) / Ref(C, -12) * 100;
rs02 = 0.2 * (C - Ref(C, -25)) / Ref(C, -25) * 100;
rs03 = 0.2 * (C - Ref(C, -38)) / Ref(C, -38) * 100;
rs04 = 0.2 * (C - Ref(C, -50)) / Ref(C, -50) * 100;
RSW =rs01 + rs02 + rs03 + rs04;

Buy =BuyIBD=RSW>JurikJMA(RSW,10);
Sell =SellIBD=RSW<JurikJMA(RSW,10);

Filter=BuyIBD OR SellIBD;
RANKIBD=IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0));
AddColumn( IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0)) ,"RANKIBD",1.0,colorWhite,IIf(BuyIBD,colorDarkGreen,IIf(SellIBD,colorRed,colorWhite)));

Use StaticVarGenerateRanks six times
(6 lines per setting static vars and ranking-> "ValuesToSort1", ..., ..., "ValuesToSort6").

Then sum up the ranks via loop:

rank_sum = 0;
for( i = 1; i <= 6; i++ )
	rank_sum += Nz(StaticVarGet("rankValuesToSort" + i + Name()));

Note: Apply StaticVarGenerateRanks at stocknum == 0. In addition it may be applied via Scan (then output and further (repeated) processing of results is done via Explore or Backtest or ...).


BTW,

this

rs01 = 0.4 * (C- Ref(C, -12)) / Ref(C, -12) * 100;
rs02 = 0.2 * (C - Ref(C, -25)) / Ref(C, -25) * 100;
rs03 = 0.2 * (C - Ref(C, -38)) / Ref(C, -38) * 100;
rs04 = 0.2 * (C - Ref(C, -50)) / Ref(C, -50) * 100;

is this one

rs01 = 0.4 * Roc(C, 12);
rs02 = 0.2 * Roc(C, 25);
rs03 = 0.2 * Roc(C, 38);
rs04 = 0.2 * Roc(C, 50);

Thanks for your prompt reply, so I added accordingly the following strings below but something is missing here, sorry but I'm a newbie. Please note that last column RANKIBD is without the setsortcolums because I need to not rank it but simply sum it at the end, so the last StaticVarGenerateRanks( "rank", "ValuesToSort6", 0, 1224 ) is it correct/necessary? I also used the parameter 1224 for a normal rank, correct?

//Alpha, Beta, R2 Formula
Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600 ALLSTOXX50=EURO STOXX 50
MKT = Foreign(Ticker,"C",1);
AP=Param("Alpha Period",200,100,300,5);
/AP=Optimize("Alpha Period",200,100,300,5);/
Beta=((AP * Sum(ROC( C,1) * ROC(MKT,1),AP))

  • (Sum(ROC(C,1),AP) * Sum(ROC( MKT,1),AP)))
    / ((AP * Sum((ROC(MKT,1)^2 ),AP)) - (Sum(ROC(MKT,1 ),AP)^2 ));
    Alpha=AP*(Sum(ROC( C,1) ,AP) - (Beta) * Sum( ROC(MKT,1),AP )) / AP;
    R2=Correlation(MKT,C,AP)^2;
    //Chart Settings
    dynamic_color = IIf( ALPHA > 0, colorDarkGreen, colorDarkRed );
    Plot(Alpha, "Alpha", dynamic_color,
    styleHistogram | styleThick );
    Title= FullName() +
    " vs. "+ ticker + " Period = "+ WriteVal(AP,format=1.2) + " ALPHA = "+ WriteVal(Alpha,format=1.2) +
    " BETA = "+ WriteVal(Beta,format=1.2) + " R~SQUARED = "+ WriteVal(R2,format=1.2);
    //System AND Backtest Settings
    EnableRotationalTrading();
    SetOption("worstrankheld",150);
    SetOption("Maxopenpositions",52);
    SetOption("allowpositionshrinking",True);
    PositionSize = -2;
    PositionScore = 100 + alpha ;
    PositionScore = Max( PositionScore, 0 );
    //Exploration Report
    Filter = 1;
    P=Param("Period",65,10,200,5,1);
    Z= (C - MA(C,P)) / (StDev( C,P));
    AddTextColumn(FullName(),"Description");
    //AddTextColumn( IndustryID(1), "Industry" );
    AddColumn(Close,"Close",1.2);
    AddColumn(Alpha,"Alpha",1.2);
    SetSortColumns( -5 );
    AddRankColumn();// rank according to 3rd column (descending)
    StaticVarGenerateRanks( "rank", "ValuesToSort1",0, 1224 );
    AddColumn(Beta,"Beta",1.2);
    SetSortColumns( 6 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort2", 0, 1224 );
    AddColumn(R2,"R-squared",1.2);
    SetSortColumns( 7 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort3", 0, 1224 );
    AddColumn(Z,"Z-Score 3 month", 1.2);
    SetSortColumns( 8 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort4", 0, 1224 );
    AddColumn(StDev( ROC(C,1),30)*sqrt(256),"Volatility",1.2);
    SetSortColumns( 9 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort5", 0, 1224 );
    AddColumn(C/HHV( High, 256 ), "% 52 week high", 1.2);
    AddColumn( ROC( Close, 256 ), "ROC(256)", 1.1 );
    AddColumn(StochK( 256 ),"Stochastic(1yr)", 1.2);
    Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600 ALLSTOXX50=EURO STOXX 50
    MKT = Foreign(Ticker,"C",1);
    //IBD RS RANKING
    rs01 = 0.4 * Roc(C, 12);
    rs02 = 0.2 * Roc(C, 25);
    rs03 = 0.2 * Roc(C, 38);
    rs04 = 0.2 * Roc(C, 50);
    RSW =rs01 + rs02 + rs03 + rs04;

Buy =BuyIBD=RSW>JurikJMA(RSW,10);
Sell =SellIBD=RSW<JurikJMA(RSW,10);

Filter=BuyIBD OR SellIBD;
RANKIBD=IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0));
AddColumn( IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0)) ,"RANKIBD",1.0,colorWhite,IIf(BuyIBD,colorDarkGreen,IIf(SellIBD,colorRed,colorWhite)));
StaticVarGenerateRanks( "rank", "ValuesToSort6", 0, 1224 );
rank_sum = 0;
for( i = 1; i <= 6; i++ )
rank_sum += Nz(StaticVarGet("rankValuesToSort" + i + Name()));

When posting the formula, please make sure that you use Code Tags (using </> code button) as explained here: How to use this site.

Using code button

Code tags are required so formulas can be properly displayed and copied without errors.

1 Like
//Alpha, Beta, R2 Formula
Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600 ALLSTOXX50=EURO STOXX 50
MKT = Foreign(Ticker,"C",1);
AP=Param("Alpha Period",200,100,300,5);
/AP=Optimize("Alpha Period",200,100,300,5);/
Beta=((AP * Sum(ROC( C,1) * ROC(MKT,1),AP))

    (Sum(ROC(C,1),AP) * Sum(ROC( MKT,1),AP)))
    / ((AP * Sum((ROC(MKT,1)^2 ),AP)) - (Sum(ROC(MKT,1 ),AP)^2 ));
    Alpha=AP*(Sum(ROC( C,1) ,AP) - (Beta) * Sum( ROC(MKT,1),AP )) / AP;
    R2=Correlation(MKT,C,AP)^2;
    //Chart Settings
    dynamic_color = IIf( ALPHA > 0, colorDarkGreen, colorDarkRed );
    Plot(Alpha, "Alpha", dynamic_color,
    styleHistogram | styleThick );
    Title= FullName() +
    " vs. "+ ticker + " Period = "+ WriteVal(AP,format=1.2) + " ALPHA = "+ WriteVal(Alpha,format=1.2) +
    " BETA = "+ WriteVal(Beta,format=1.2) + " R~SQUARED = "+ WriteVal(R2,format=1.2);
    //System AND Backtest Settings
    EnableRotationalTrading();
    SetOption("worstrankheld",150);
    SetOption("Maxopenpositions",52);
    SetOption("allowpositionshrinking",True);
    PositionSize = -2;
    PositionScore = 100 + alpha ;
    PositionScore = Max( PositionScore, 0 );
    //Exploration Report
    Filter = 1;
    P=Param("Period",65,10,200,5,1);
    Z= (C - MA(C,P)) / (StDev( C,P));
    AddTextColumn(FullName(),"Description");
    //AddTextColumn( IndustryID(1), "Industry" );
    AddColumn(Close,"Close",1.2);
    AddColumn(Alpha,"Alpha",1.2);
    SetSortColumns( -5 );
    AddRankColumn();// rank according to 3rd column (descending)
    StaticVarGenerateRanks( "rank", "ValuesToSort1",0, 1224 );
    AddColumn(Beta,"Beta",1.2);
    SetSortColumns( 6 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort2", 0, 1224 );
    AddColumn(R2,"R-squared",1.2);
    SetSortColumns( 7 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort3", 0, 1224 );
    AddColumn(Z,"Z-Score 3 month", 1.2);
    SetSortColumns( 8 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort4", 0, 1224 );
    AddColumn(StDev( ROC(C,1),30)*sqrt(256),"Volatility",1.2);
    SetSortColumns( 9 );
    AddRankColumn();
    StaticVarGenerateRanks( "rank", "ValuesToSort5", 0, 1224 );
    AddColumn(C/HHV( High, 256 ), "% 52 week high", 1.2);
    AddColumn( ROC( Close, 256 ), "ROC(256)", 1.1 );
    AddColumn(StochK( 256 ),"Stochastic(1yr)", 1.2);
    Ticker = ParamStr("MKT Ticker", "ALLSPMIB"); //ALL$SXXP=stoxx 600 ALLSTOXX50=EURO STOXX 50
    MKT = Foreign(Ticker,"C",1);
    //IBD RS RANKING
    rs01 = 0.4 * Roc(C, 12);
    rs02 = 0.2 * Roc(C, 25);
    rs03 = 0.2 * Roc(C, 38);
    rs04 = 0.2 * Roc(C, 50);
    RSW =rs01 + rs02 + rs03 + rs04;

Buy =BuyIBD=RSW>JurikJMA(RSW,10);
Sell =SellIBD=RSW<JurikJMA(RSW,10);

Filter=BuyIBD OR SellIBD;
RANKIBD=IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0));
AddColumn( IIf(BuyIBD,-abs(RSW),IIf(SellIBD,abs(RSW),0)) ,"RANKIBD",1.0,colorWhite,IIf(BuyIBD,colorDarkGreen,IIf(SellIBD,colorRed,colorWhite)));
StaticVarGenerateRanks( "rank", "ValuesToSort6", 0, 1224 );
rank_sum = 0;
for( i = 1; i <= 6; i++ )
rank_sum += Nz(StaticVarGet("rankValuesToSort" + i + Name()));