Excluding delisted stocks from a ranking


I have some problems to exclude delisted stocks from a ranking, I have composed a simple code as an example:

// --- Norgate Data settings ---
#include_once "Formulas\Norgate Data\Norgate Data Functions.afl"
function IndexConstituentTimeSeries(IndexName)
  StaticVarKey = "IndexConstituentTimeSeries_" + IndexName + "_" + Name();
  Array = StaticVarGet(StaticVarKey);
  if (typeof(Array) != "Array")
    Array = NorgateIndexConstituentTimeSeries(IndexName);
    StaticVarSet(StaticVarKey, Array);
  return array;

// --- Trend Filter --- 

LongSymbol        = MA(Close, 8) >  MA(Close, 24);
ShortSymbol       = NOT LongSymbol;

// --- Entry Score --- 

WatchList  = GetOption("FilterIncludeWatchlist");
List       = GetCategorySymbols(categoryWatchlist, WatchList);

EntryVariable1   = 72;
EntryVariable2   = 2;

if (Status("Stocknum") == 0 )
  StaticVarRemove("Score*" );
  for (n = 0;(Symbol = StrExtract(List, n)) != ""; n++) 
    SetForeign (Symbol);
    OnLastTwoBarsOfDelistedSecurity   = !IsNull(GetFnData("DelistingDate")) AND (BarIndex() >= (LastValue(BarIndex()) -1) OR DateTime() >= GetFnData("DelistingDate") );
	SymbolSlope = LinRegSlope(Close, EntryVariable1);
	SymbolVol   = StDev(log(Close/Ref(Close,-1)), EntryVariable1) * 100 * sqrt(12);
	Score       = IIf(NorgateIndexConstituentTimeSeries("$OEX") AND NOT OnLastTwoBarsOfDelistedSecurity, 100* SymbolSlope/(SymbolVol^EntryVariable2), 0);
  	StaticVarSet ("Score" + Symbol, Score);
  StaticVarGenerateRanks("Ranking","Score", 0, 1224); 

Symbol        = Name();
SymbolScore   = StaticVarGet ("Score" + Symbol); 
SymbolRanking = StaticVarGet ("RankingScore" + Symbol);
PositionScore = SymbolScore;

// --- Trading Rules --- 

OnSecondLastBarOfDelistedSecurity = !IsNull(GetFnData("DelistingDate")) AND (BarIndex() == (LastValue(BarIndex()) -1) OR DateTime() >= GetFnData("DelistingDate") );
OnLastTwoBarsOfDelistedSecurity   = !IsNull(GetFnData("DelistingDate")) AND (BarIndex() >= (LastValue(BarIndex()) -1) OR DateTime() >= GetFnData("DelistingDate") );

Buy           = LongSymbol AND NorgateIndexConstituentTimeSeries("$OEX") AND NOT OnLastTwoBarsOfDelistedSecurity;
Sell          = ShortSymbol OR OnSecondLastBarOfDelistedSecurity;
BuyPrice      = Close;
SellPrice     = Close;
RoundLotSize  = 1;
SetPositionSize(10, spsPercentOfEquity);

// --- Exploration ---

Filter = 1;
AddColumn(SymbolRanking, "Rank", 1.0);
AddColumn(SymbolScore, "Score", 1.2);

When I run the exploration for 1 recent bar I get this:


If I ignore the delisted stocks, the ranking works fine (1 AMZN 4.70, 2 GOOG 3.18, 3 GOOGL 3.04,...). However I also have those in between, and I can' figure out how to get rid of them.

I have included a condition in the ranking to set to 0 the score of those stocks whose delisting date is previous to the present date, but apparently it doesn't work the way I thought.

I was unable to find previous entries about this subject, I should appreciate any help.


All of your delisted symbols have old dates, indicating that you probably don't have Pad & Align enabled in your Analysis Settings, which is recommended when you're using StaticVarGenerateRanks().

1 Like

There are 2 ways to tackle the delisted stocks.
The first is to skip stocks that have no bar in the latest date.

ymd = "2021-02-26";
if (IsNull(Lookup(BarIndex(),_DT(ymd),0))) Filter = 0;
else Filter = 1;

The second method is more robust. Rename at delisted stock by appending "_D" to the ticker symbol becuase old disused ticker symbols are reassign to new companies. For example, ticker symbol HP is no longer Hewlett-Packard but an Oil & Gas company. If you were to keep HP in your database, it's going to have two companies in one ticker.
Symbols with "_D" can be skipped with this statement.

if (StrRight(Name(),2) == "_D") Filter = 0;
1 Like


You are right, Pad & Align wasn't enabled. Once I enabled it, the ranking worked fine.

Thanks for your help.



Thanks for your answer.

I'm using a different way to identify delisted stocks. Since the data provider I use delivers the delisted date, I can get it from the symbol information. Otherwise, I would use the formula you have proposed.

I couldn't use the second method proposed because I need to trade the symbols before their delisting date. If I exclude them based on their ticker, I would't be able to trade the ever.


When you run a backtest or exploration that uses StaticVarGenerateRanks but you did not enable Pad & Align, you should see Notice 801 in the Info window as shown below. As usual, @Tomasz is giving you as many hints as possible on how to be successful.



This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.