Excluding delisted stocks from a ranking

[jptrader]

Hello,

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);
  	RestorePriceArrays();
  	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.

Regards.

[mradtke]

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().

[Peter2047]

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;

[jptrader]

mradtke,

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

Thanks for your help.

Regards

[jptrader]

Peter2047,

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.

Regards.

[mradtke]

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.