# How to sum the closing prices of the top X ranked stocks for each day?

I know how to create scores and ranks. Now I need to sum the closing prices of the top X ranked stocks for each day. How would I accomplish this?

@vjsworld try this:

``````listNum = GetOption( "FilterIncludeWatchlist" );
list = CategoryGetSymbols( categoryWatchlist, listNum );
topN = Param("Top # to rank", 5, 2, 10, 1);

if( Status( "stocknum" ) == 0 )
{
StaticVarRemove( "valuesToSort*" );
StaticVarRemove( "rank*" );
StaticVarRemove( "top*" );

// Fill input value (for ranking) into Static arrays
for( i = 0; ( sym = StrExtract( list, i ) ) != ""; i++ )
{
SetForeign( sym );
value = ROC( C, 1 );
RestorePriceArrays();
StaticVarSet( "valuesToSort" + sym, value );
}

// Perform Ranking
StaticVarGenerateRanks( "rank", "valuesToSort",   0, 1224 );
StaticVarGenerateRanks( "top", "valuesToSort", topN, 1224 );
}

// A possible solution to SUM the close price of the topN ranked stocks
dt = DateTime();
sumClose = 0;
bir = Status("BarInRange");
for (j = 0; j < BarCount; j++)
{
if (bir[j])
{
topRanked = StaticVarGetRankedSymbols( "top", "valuesToSort", dt[j]);
for( i = 0; ( sym = StrExtract( topRanked, i ) ) != ""; i++ )
{
SetForeign( sym );
sumClose[j] += C[j];
RestorePriceArrays();
}
}
}
// Exploration
rank = StaticVarGet( "rankvaluesToSort" + Name() );
Filter = rank <= topN;
values = StaticVarGet( "valuesToSort" + Name() );
AddColumn( ROC( C, 1 ), "ROC(1)" );
// Verify that the rank values are correct
AddColumn( values, "R. values == (ROC(1))" );
AddColumn( sumClose, StrFormat("Sum of Top %2.0f Close", topN) );
SetSortColumns( 2, 6 );
``````

I hope someone will provide you with a more efficient way.

2 Likes

@beppe
Thank you for this. While it accomplishes what I need, it is extremely slow when placed on a watchlist of 600 stocks. Even when I add the condition to only sum once a month it still takes +25min to run a 10 year test.

Does anyone else know of a way to accomplish this more efficiently?

Instead of a bar-by-bar loop after the ranking, I would just loop through all the symbols one more time and sum the relevant closes that way. Something similar to this untested snippet should work.

``````sumClose = 0;
for( i = 0; ( sym = StrExtract( list, i ) ) != ""; i++ )
{
SetForeign( sym );
rank = StaticVarGet("rankvaluesToSort"+sym));
sumClose += IIf(rank < topN, Close, 0);
RestorePriceArrays();
}
``````
4 Likes

@mradtke, nice. Just a small change to get the correct sums since Rank values are 1 based:

``````sumClose = 0;
for( i = 0; ( sym = StrExtract( list, i ) ) != ""; i++ )
{
SetForeign( sym );
rank = StaticVarGet("rankvaluesToSort"+sym);
sumClose += IIf(rank <= topN, Close, 0); // modified to <=
RestorePriceArrays();
}
``````
4 Likes

While playing with a similar thing, I realized that it is possible to get a lot a quicker analysis result1 doing an extra bit of refactoring in addition to just replacing my original loop with the faster solution by @mradtke.

Maybe some users already got it right but here is it anyway for anyone else that will need to do a similar thing in the future.

It is enough to calculate the "sum" array once, moving the sum loop code inside the stocknum == 0 block, saving it to a static var and reassigning it later, replacing the calculation.

For each date the topN names (and the associated sums) are always the same - independently of the name() that is currently processed by the exploration (I mean that all the subsequent ones will use the same "sums" that are calcualted for the first stock),

``````listNum = GetOption( "FilterIncludeWatchlist" );
list = CategoryGetSymbols( categoryWatchlist, listNum );
topN = Param( "Top # to rank", 5, 2, 10, 1 );

if( Status( "action" ) == actionExplore )
{
if( Status( "stocknum" ) == 0 )
{
StaticVarRemove( "valuesToSort*" );
StaticVarRemove( "rank*" );

// Fill input value (for ranking) into Static arrays
for( i = 0; ( sym = StrExtract( list, i ) ) != ""; i++ )
{
SetForeign( sym );
value = ROC( C, 1 ); // You criteria to Rank
RestorePriceArrays();

StaticVarSet( "valuesToSort" + sym, value );
}

// Perform Ranking
StaticVarGenerateRanks( "rank", "valuesToSort",   0, 1234 );

// generate the "sums" for the first stock
sumClose = 0;

for( i = 0; ( sym = StrExtract( list, i ) ) != ""; i++ )
{
SetForeign( sym );
rank = StaticVarGet( "rankvaluesToSort" + sym );
sumClose += IIf( rank <= topN, Close, 0 ); // your field to Sum
RestorePriceArrays();
}

// Save to a static var and re-use it for every Name()
StaticVarSet( "topSumOfClose", sumClose );

// Free some static var memory no longer used
StaticVarRemove( "valuesToSort*" );
}

// Exploration
rank = StaticVarGet( "rankvaluesToSort" + Name() );
Filter = rank <= topN;
sumClose = StaticVarGet( "topSumOfClose" );