How to calculate returns without counting delisted symbols

[cvarealo]

Hi guys, I'm trying to calculate the total profitability and volatility of a watchlist, and I'm not sure how to exclude delisted stocks.

I have created an example watchlist with two stocks: AAL and AMBD, where AMBD was delisted in December 2022.

I calculate returns from 11/05 for 120 days and sum the two values, even though AMBD doesn't have data since December.

TimeFrameSet( inDaily);

wl1= Param("Num. Watch List",67,0,100,1);
symlist   = CategoryGetSymbols( categoryWatchlist, wl1 );
datetim6= Ref(DateTime(),-120);
delistd= GetFnData("DelistingDate");

if ( Status("stocknum") == 0 )

{

StaticVarRemove( "x6*" );
StaticVarRemove( "~Symb*" );
StaticVarRemove( "~SymbolCount*" );
StaticVarRemove( "svSymbolCount*" );
StaticVarRemove( "n1*" );
StaticVarRemove( "sumax6*" );
StaticVarRemove( "d0" );
StaticVarRemove( "~sumx6*" );
StaticVarRemove( "~symbolcount2"  );

for( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ )
{
SetForeign(sym );


Data=C;
x1=!IsNull(delistd< datetim6);

stocksRet6m=(Nz(( -1 + Ref(Data,-1) / Ref( Data, -120  ) )))*100;
StaticVarAdd ("~sumx6", stocksRet6m);
StaticVarAdd ("~symbolcount2", stocksRet6m != 0) ;

RestorePriceArrays();


}
}


sumx6=StaticVarget("~sumx6");
countx6=StaticVarget("~symbolcount2");

Filter=1;

  AddColumn( sumx6, "Sumax6", 1.5 );
  AddColumn( countx6, "Symbol Count2", 1.0 );

In the exploration, only AAL appears, but it adds up the returns of both values and counts them.

I have tried adding a line in the loop to prevent it from including values with a delisting date later than the date from which I want to calculate the returns, but it generates null values.

type or paste code here

I have tried adding a line in the loop to prevent it from including values with a delisting date later than the date from which I want to calculate the returns, but it generates null values.

I introduce a variable x1 that should give 0 if the date on which the value is delisted is later than the date -120.

for( i = 0; ( sym = StrExtract( symlist, i ) ) != ""; i++ )
{
SetForeign(sym );


Data=C;

//
x1=!IsNull(delistd< datetim6);
///


stocksRet6m=(Nz(( -1 + Ref(Data,-1) / Ref( Data, -120  ) *x1)))*100;
StaticVarAdd ("~sumx6", stocksRet6m);
StaticVarAdd ("~symbolcount2", stocksRet6m != 0) ;

RestorePriceArrays();

I would greatly appreciate if someone could lend me a hand.

[Cougar]

Quoting from DateTimeDiff():

It is important to understand that DateTime is not a simple number but rather bitset and two datetime values can only be reliably compared for equlity or inequality using == or != operators. Any other comparisions (less than/greater then), using normal operators > < may sometimes lead to wrong results (if one of dates compared is pre-1964), therefore to compare two datetime numbers reliably you should use DateTimeDiff.

---

@cvarealo

x1=!IsNull(delistd< datetim6);

Instead use this expression:

x1 = DateTimeDiff( datetim6, delistd ) <= 0;

---

And instead:

@cvarealo

stocksRet6m=(Nz(( -1 + Ref(Data,-1) / Ref( Data, -120 ) *x1)))*100;

use:

stocksRet6m = x1 * Nz( 100 * ( ( Ref( Data, -1 ) / Ref( Data, -120 ) ) - 1 ) );

for the sake of tidiness and code readability.

[cvarealo]

Thank you very much, Cougar. I wasn't familiar with the DateTimeDiff() function.
With the modifications you sent me, there is only one small change that needs to be made since the variable "deslistd" often has a null value.

delistd= GetFnData("DelistingDate");
delistd_b= IIf (IsNull (delistd),0,delistd);

Thanks for the help!.

[Cougar]

Instead:

@cvarealo

delistd= GetFnData("DelistingDate");
delistd_b= IIf (IsNull (delistd),0,delistd);

One liner using Nz():

delistd = Nz( GetFnData( "DelistingDate" ) );