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.