Stock listed in Exploration when IF condition not met

watchbird · March 30, 2019, 9:27pm

I have written the following exploration as a learning exercise.
However, when a stock does NOT meet the listing condition the Exploration still lists every quote for that stock (ticker)in the Date Range - Ticker & (Quote) date.
When I run the code using the Debugger for a stock NOT meeting the condition, the AddColumn code (dependent on If & Filter condition) is not executed but appears to be being generated as the code Exits the While loop.....I am mystified as to how/why the list is being generated for those stocks.
Would someone please explain?

//List those stocks which Closed <= 52 week Low +10%
//within the last 31 days

Liquidity = MA(C*V,21);
Filter = Liquidity >= 1000000; 

LastX = LastValue(BarIndex());	//is number
StartX = (LastX - 31);			//is number		//set index for 1 month earlier than BarCount -1
if (StartX < 0)
	StartX =0;
	
// get the 52 week (260 days) Low
Low52 = LLV(Low,260);							//is array
//Add 10% to the 52 week Low							 
Low5210 = Low52 + ((Low52 * 10) / 100);			//is array 
//Now get the Index of the 52 week Low			
BarsSinceLow52 = LLVBars(Low,260);				//is array
//Get the index of the 52 week Low
Low52X = LastX - BarsSinceLow52[LastX];
LowDate = ValueWhen(Low[Low52X],DateTime()); //is array		 	//get date of 52 week low
L52X = NumToStr(Low52X,1.0);

// get the lowest Close in the last 31 days
CloseLow = LLV(Close,31); 						//is array
BarsSinceLowClose = LLVBars(Close,31);			//is array
//Get the Index of the 31 day lowest Close
CloseLowX = LastX - BarsSinceLowClose[LastX];
CloseLowDate = ValueWhen(Close[CloseLowX],DateTime()); //is array	//get the date of the lowest Close
CLX = NumToStr(CloseLowX,1.0);			 	 

StartDate = ValueWhen(Close[StartX],DateTime());//is array	 	//get the period start date
EndDate = ValueWhen(Close[LastX], DateTime()); //is array		//get the date of the last Close

//List the most recent Close which is <= 52 week Low + 10%
X = LastX;
Ok = 0;
while (X >= StartX)
{
	Ok = IIf(Close[X] <= Low5210[LastX],1,0);
	if (Ok==1)
	{
		Filter = (X == BarIndex()); 

		AddColumn(Close[X],"Close",format = 1.2);
				
		AddColumn(LowDate[Low52X],"Low52Dte",formatdateTime);
		AddColumn(Low52[Low52X],"52Wk$",format=1.2);
		AddColumn(Low5210[Low52X], "+10%$", format = 1.2);
		AddTextColumn(L52X,"Low52X",1.0);
					
		AddColumn(CloseLowDate[CloseLowX],"CloseLowDte",formatdateTime);
		AddColumn(CloseLow[LastX],"CloseLow",format = 1.2);
		AddTextColumn((NumToStr(BarsSinceLowClose)),"BarsSinceCloseLow");
		AddTextColumn(CLX,"CloseLowX",1.0);
		AddColumn(X,"X",format = 1.0);
					
		AddColumn(StartDate[StartX],"StartDte",formatdateTime);
		AddColumn(StartX,"StartX",format = 1);
		AddColumn(EndDate[LastX],"EndDte",formatdateTime);
		AddColumn(LastX,"LastX",format = 1.0);
						
		AddColumn(BarIndex(),"BarIndex",format = 1);
		X = -1;
	}
	else
	{
		X = X -1;
	}
	
	if (X == 198)			//this If for debugging
		Y = 0;
	
}	
Y = 1;	//this for debugging
//AddColumn(BarIndex(),"BarIndex-NBG",format=1); //This for debugging

Moderator comment: The post was missing required CODE TAGS. Added them

fxshrat · March 31, 2019, 12:59pm

@watchbird, You should think in arrays not in loops and elements.

//EOD code
//List those stocks which Closed <= 52 week Low +10%
//within the last 31 days
/// @link https://forum.amibroker.com/t/stock-listed-in-exploration-when-if-condition-not-met/12214/2
Liquidity = MA(C*V,21);
Filter = Liquidity >= 1000000;

bi = BarIndex();
dt = DateTime();
LastX = LastValue(bi); //is number
StartX = Max(0, LastX - 31); //is number //set index for 1 month earlier than BarCount -1

lowCond = bi == LastX-260;

// get the 52 week (260 days) Low
Low52 = LowestSince(lowCond, Low);//LLV(Low,260); //is array
//Add 10% to the 52 week Low
Low5210 = Low52 + ((Low52 * 10) / 100); //is array
//Now get the Index of the 52 week Low
BarsSinceLow52 = LowestSinceBars(lowCond, L);//LLVBars(Low,260); //is array
//Get the index of the 52 week Low
Low52X = LastX - LastValue(BarsSinceLow52);//LastX - BarsSinceLow52[LastX];
LowDate = Ref(dt,-BarsSinceLow52);//wrong: ValueWhen(Low[Low52X],DateTime()); //is array //get date of 52 week low
L52X = Low52X;

// get the lowest Close in the last 31 days
CloseLow = LowestSince(lowCond, Close);//LLV(Close,31); //is array
BarsSinceLowClose = LowestSinceBars(lowCond, C);//LLVBars(Close,31); //is array
//Get the Index of the 31 day lowest Close
CloseLowX = LastX - LastValue(BarsSinceLowClose);//LastX - BarsSinceLowClose[LastX];
CloseLowDate = Ref(dt,-BarsSinceLowClose);//wrong: ValueWhen(Close[CloseLowX],DateTime()); //is array //get the date of the lowest Close
CLX = CloseLowX;

StartDate = ValueWhen(bi == startX, dt);//is array //get the period start date
EndDate = LastValue(dt);//wrong: ValueWhen(Close[LastX], DateTime()); //is array //get the date of the last Close

//List the most recent Close which is <= 52 week Low + 10%
//within the last 31 days
Filter = Filter AND Close <= Low5210 AND bi >= startX;

AddColumn(Close,"Close",format = 1.2);

AddColumn(LowDate,"Low52Dte",formatdateTime);
AddColumn(Low52,"52Wk$",format=1.2);
AddColumn(Low5210, "+10%$", format = 1.2);
AddColumn(L52X,"Low52X",1.0);

AddColumn(CloseLowDate,"CloseLowDte",formatdateTime);
AddColumn(CloseLow,"CloseLow",format = 1.2);
AddColumn(BarsSinceLowClose,"BarsSinceCloseLow", 1);
AddColumn(CLX,"CloseLowX",1.0);
//AddColumn(X,"X",format = 1.0);

AddColumn(StartDate,"StartDte",formatdateTime);
AddColumn(StartX,"StartX",format = 1);
AddColumn(EndDate,"EndDte",formatdateTime);
AddColumn(LastX,"LastX",format = 1.0);

AddColumn(bi,"BarIndex",format = 1);
AddColumn(LastX-bi,"Days back",format = 1);

//AddColumn(BarIndex(),"BarIndex-NBG",format=1); //This for debugging

As for Valuewhen... please read docu about ValueWhen again.
Expression has to be true/false array in order to make sense of result. You inserted price (element) instead.

BTW, before sending post please check in post preview whether code is inserted between code tags properly.

Watch animation again showing where code has to be placed. It has to be in between tags but not in same line as tags.

watchbird · April 3, 2019, 3:22am

I will look at that - thank you.

watchbird · April 4, 2019, 12:47am

Apologies and noted.

watchbird · April 5, 2019, 4:46am

I would appreciate some advice re removing the loop. fxshrat example shows more than 1 quote per ticker. I require just one quote per ticker which shows only the most recent Close within the last 31 days which meets the condition.
Also....there is another problem with my code - viz. If the first ticker processed includes a quote which meets the condition, then the listing for the ticker shows one line with all the columns specified by AddColumn statements. BUT..if the first ticker processed does not include a quote which meets the condition then the listing shows a line for every quote within the specified date range, ticker and date only, and for those tickers which include a quote meeting the condition just one line for that quote with ticker and date only. Is the Filter statement the problem or something else?

fxshrat · April 5, 2019, 8:09am

In post #1 you did not say that you want to output most recent true occurrence only.

So below is update with output of most recent occurrence only.
(Once again you do not need any looping completely. But just array processing)
In addition that update contains a "fix" of post #2 code since in post #1 you were trying to get lowest Close of past 31 days but not of 260 days (see red marker in picture below). So I changed to 31 days too.

(BTW since you wrote "my code".... all codes after your original code are not your codes anymore. Remember that please.)

/// EOD code
/// List those stocks which Closed <= 52 week Low +10%
/// within the last 31 days
/// original incorrect code at:
/// @link https://forum.amibroker.com/t/stock-listed-in-exploration-when-if-condition-not-met/12214/
/// Fix of incorrect OP code by fxshrat@gmail.com at:
/// @link https://forum.amibroker.com/t/stock-listed-in-exploration-when-if-condition-not-met/12214/6
/// NOT OP code anymore
/// commercial use prohibited! 
Liquidity = MA(C*V,21);
Filter = Liquidity >= 1000000;

bi = BarIndex();
dt = DateTime();
LastX = LastValue(bi); //is number
StartX = Max(0, LastX - 31); //is number //set index for 1 month earlier than BarCount -1

// get the 52 week (260 days) Low
lowCond1 = bi == LastX-260;
Low52 = LowestSince(lowCond1, Low);//LLV(Low,260); //is array
//Add 10% to the 52 week Low
Low5210 = Low52*1.10;//Low52 + ((Low52 * 10) / 100); //is array
//Now get the Index of the 52 week Low
BarsSinceLow52 = LowestSinceBars(lowCond1, L);//LLVBars(Low,260); //is array
//Get the index of the 52 week Low
Low52X = LastX - LastValue(BarsSinceLow52);//LastX - BarsSinceLow52[LastX];
LowDate = Ref(dt,-BarsSinceLow52);//wrong: ValueWhen(Low[Low52X],DateTime()); //is array //get date of 52 week low
L52X = Low52X;

// get the lowest Close in the last 31 days
lowCond2 = bi == StartX;
CloseLow = LowestSince(lowCond2, Close);//LLV(Close,31); //is array
BarsSinceLowClose = LowestSinceBars(lowCond2, C);//LLVBars(Close,31); //is array
//Get the Index of the 31 day lowest Close
CloseLowX = LastX - LastValue(BarsSinceLowClose);//LastX - BarsSinceLowClose[LastX];
CloseLowDate = Ref(dt,-BarsSinceLowClose);//wrong: ValueWhen(Close[CloseLowX],DateTime()); //is array //get the date of the lowest Close
CLX = CloseLowX;

StartDate = ValueWhen(bi == startX, dt);//is array //get the period start date
EndDate = LastValue(dt);//wrong: ValueWhen(Close[LastX], DateTime()); //is array //get the date of the last Close

//List the most recent Close which is <= 52 week Low + 10%
//within the last 31 days
Filter = Filter AND Close <= Low5210 AND bi >= startX;

// output only most recent Filter
cumFilter = Cum(Filter);
Filter = Filter AND cumFilter == LastValue(cumFilter);

AddColumn(Close,"Close",format = 1.2);

AddColumn(LowDate,"Low52Dte",formatdateTime);
AddColumn(Low52,"52Wk$",format=1.2);
AddColumn(Low5210, "+10%$", format = 1.2);
AddColumn(L52X,"Low52X",1.0);

AddColumn(CloseLowDate,"CloseLowDte",formatdateTime);
AddColumn(CloseLow,"CloseLow (past 31d)",format = 1.2);
AddColumn(DaysSinceLowClose,"BarsSinceCloseLow", 1);
AddColumn(CLX,"CloseLowX",1.0);

AddColumn(StartDate,"StartDte",formatdateTime);
AddColumn(StartX,"StartX",format = 1);
AddColumn(EndDate,"EndDte",formatdateTime);
AddColumn(LastX,"LastX",format = 1.0);

AddColumn(bi,"BarIndex",format = 1);
AddColumn(LastX-bi,"Days back",format = 1);

Compare with picture of post #2.

Your code is the one of post #1 and that one is incorrect one. Please completely wipe that code out of your mind quickly and never look back -> it is leading to wrong track.

fxshrat · April 6, 2019, 12:59pm

In line 59 of previous post's code there is a small typo resulting from editing in forum editor.

Line 59 was supposed to be:

AddColumn(BarsSinceLowClose,"DaysSinceCloseLow", 1);

Here is cleaned up fixed code again

/// EOD code
/// List those stocks which Closed <= 52 week Low +10%
/// within the last 31 days
/// original incorrect code at:
/// @link https://forum.amibroker.com/t/stock-listed-in-exploration-when-if-condition-not-met/12214/
/// Fix of incorrect OP code by fxshrat@gmail.com at:
/// @link https://forum.amibroker.com/t/stock-listed-in-exploration-when-if-condition-not-met/12214/7
/// NOT OP code anymore
/// commercial use prohibited! 
Liquidity = MA(C*V,21);//is array
Filter = Liquidity >= 1000000;//is array

bi = BarIndex();//is array
dt = DateTime();//is array
LastX = LastValue(bi); //is number
StartX = Max(0, LastX - 31); //is number //set index for "1 month" earlier than BarCount -1

// get the 52 week (260 days) Low
lowCond1 = bi == LastX-260;//is array
Low52 = LowestSince(lowCond1, Low);//is array
//Add 10% to the 52 week Low
Low5210 = Low52*1.10;//is array
//Now get the Index of the 52 week Low
BarsSinceLow52 = LowestSinceBars(lowCond1, L); //is array
//Get the index of the 52 week Low
Low52X = LastX - LastValue(BarsSinceLow52);//is number 
LowDate = Ref(dt,-BarsSinceLow52);//is array //get date of 52 week low

// get the lowest Close in the last 31 days
lowCond2 = bi == StartX;//is array
CloseLow = LowestSince(lowCond2, Close);//is array
BarsSinceLowClose = LowestSinceBars(lowCond2, C);//is array
//Get the Index of the 31 day lowest Close
CloseLowX = LastX - LastValue(BarsSinceLowClose);//is number 
CloseLowDate = Ref(dt,-BarsSinceLowClose);//is array //get the date of the lowest Close

StartDate = ValueWhen(bi == startX, dt);//is array //get the period start date
EndDate = LastValue(dt);//is number //get the date of the last Close

//List the most recent Close which is <= 52 week Low + 10%
//within the last 31 days
Filter = Filter AND Close <= Low5210 AND bi >= startX;//is array

// output only most recent Filter
cumFilter = Cum(Filter);//is array
Filter = Filter AND cumFilter == LastValue(cumFilter);//is array

AddTextColumn( Interval(2), "Interval", 1);
AddColumn(Close,"Close", format = 1.2);

AddColumn(LowDate, "Low52Dte", formatdateTime);
AddColumn(Low52, "52Wk$", format);
AddColumn(Low5210, "+10%$", format);
AddColumn(Low52X, "Low52Bi", 1);

AddColumn(CloseLowDate, "CloseLowDte", formatdateTime);
AddColumn(CloseLow, "CloseLow (31 days)", format);
AddColumn(BarsSinceLowClose, "DaysSinceCloseLow", 1);
AddColumn(CloseLowX, "CloseLowBi", 1);

AddColumn(StartDate, "StartDte", formatdateTime);
AddColumn(StartX, "StartBi", 1);
AddColumn(EndDate, "EndDte", formatdateTime);
AddColumn(LastX, "LastBi", 1);

AddColumn(bi, "BarIndex", 1);
AddColumn(LastX-bi, "Days back", 1);

Cougar · April 6, 2019, 2:20pm

@fxshrat,

Thanks for sharing such codes. It bridges many gaps and help clear AFL mind-blocks! These are certain explicit methods that create self-doubts before writing a Loop ever in AFL.

Keep those bells and whistles ringing and keep inspiring ...

watchbird · April 8, 2019, 4:06am

Thank you fxshrat for your code. In my language "my" does not mean "your". I WAS referring to my own code!
I wrote the code as a learning excercise and would have found it educational to be told the error(s) in my code rather than just being told to throw that away in exchange for something else.
I have run YOUR code but it appears to NOT retrieve the 52 week (260 day low) when that low occurs outside the array size. It seems that exploration shows array size is usually 249 and so line 19 may result in value zero. LLV function does retrieve 52 week (260 day) Low with array size 249 (due to pre-scanning?). Although I searched Google and AB forum I have not yet found what is determining array size loaded by Exploration.
Also I am still unable to identify the reason for results as noted in my reply of April 4 - "Also....there is another problem with my code - ...... ".

Tomasz · April 8, 2019, 9:27am

For what it is worth, that would work only if errors were subtle. What @fxshrat wrote you was more educational.
Imagine you are going from Rome to Venezia. Your code is doing this around the globe via Antarctica and Arctica, crossing oceans and several continents. @fxshrat has shown you much shorter, fuel-efficient, direct way via motorway in Italy.

fxshrat · April 8, 2019, 10:52am

Thank you fxshrat for your code.

"I wrote the code as a learning excercise and would have found it educational to be told the error(s) in my code rather than just being told to throw that away in exchange for something else."

As for why your code of post #1 is technically incorrect/flawed has been told to you already... You are using looping for exploration output... And you use subcripting and are mixing arrays with elements of array (e.g. in ValueWhen). It is 90% incorrect/flawed the way you did. As mentioned you should (change mindset) to think in arrays (since apparently you come from different platform). So there is nothing more to say other than wiping your code out of mind (like a novelist would throw sheet of paper going wrong into trashcan). It is simply heading to wrong track almost completely within your original code.

Also in post #2 I explained to you why your ValueWhen lines are incorrect specifically.

Then I replaced LLV with LowestSince since you want to look for single start condition.

So basically it is all there and covered.

"I have run YOUR code but it appears to NOT retrieve the 52 week (260 day low) when that low occurs outside the array size.
It seems that exploration shows array size is usually 249 and so line 19 may result in value zero.
LLV function does retrieve 52 week (260 day) Low with array size 249 (due to pre-scanning?)."

As for having only 249 bars in your DB... Simply load more data (or change from 260 to lower value or change to using start dates or....). If symbol does not have required amount of data to check for then it just fails set original condition and there isn't anything wrong with that. If LastX-260 becomes negative then equality check with BarIndex() array will not return (out of BarCount range) error (it is not array subscripting) but will just return array with false state (zero array). So it will simply fit into set original condition to be getting passed or not. If you want to change condition so that available bars shall be used if amount of data is less than set look-back period then simply change line #19 like so:


lowCond1 = bi == Max(0, LastX-260);//is array

So then it will start to check from index zero if there isn't enough data.

In my language "my" does not mean "your". I WAS referring to my own code!

It was not clear which code you referred to as you where first commenting on code of later posts returning more than one true output and then about "another problem with my code" immediately after. So I was just making clear which code is which one because believe it or not but there do exist people scanning/asking forum for code to go commercial with other peoples work and time spent afterwards. So it was just general statement of mine to clear up things.