Scanning Technically

The Max() Conundrum

Quick!  Without thinking about it very long - how would you write an advanced scan to find all stocks that are closed at a new 200-day high?  Wouldn't you write something like this?

[type = stock] and [today's high > max(200, high)]


That looks pretty good.  Let's run it and see what we get...

Picture 1  
Curses!  The dreaded "Count: 0" scan result has foiled me again.  But where did I go wrong?

The first possibility is that no stocks are hitting new 200-day highs.  While possible (especially in today's economy), that's not the problem I'm trying to point out.  There's a functional problem with the way I defined the scan - can you spot it?

The problem is that "today's high" is included in the calculation of "max(200, high)".

Imagine a stock that really is hitting a new 200-day high of, let's say 100, right now.  In that case "today's high" equals 100 and "max(200,high)" also equals 100.  Substituting those values into the scan criteria, we're left with "100 > 100" which will always be false.  That's why we're getting zero results back all the time.

There are two ways to fix the problem:

1.) Change the ">" to "=":

[type = stock] and [today's high = max(200, high)]

2.) Modify the max() clause to start "yesterday":

[type = stock] and [today's high > yesterday's max(200, high)]

(Really observant people will notice that #2 needs a minor adjustment to fully match #1.  I'll leave that adjustment up to the reader as an exercise.)
 
 

Other StockCharts Blogs