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

AFL Programming
1

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?

2

@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( Close, "Close" );
AddColumn( ROC( C, 1 ), "ROC(1)" );
// Verify that the rank values are correct
AddColumn( values, "R. values == (ROC(1))" );
AddColumn( rank, "Rank", 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
3

@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?

4

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
6

@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
7

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" );
    AddColumn( Close, "Close" );
    AddColumn( ROC( C, 1 ), "ROC(1)" );
    AddColumn( rank, "Rank", 1 );
    AddColumn( sumClose,  StrFormat( "Sum Top %2.0f Close", topN ), 1.2 );
    SetSortColumns( 2, 5 );
}

1) The performance difference is significative if you use large watchlists and long histories, like in the case of @vjsworld of 600 stocks and 10 years of data (by the way for this kind of analysis is paramount to have very clean data with no holes).
3 Likes