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

 

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 situation today (March 16, 2009).  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.)

Comments

[Gord Greer]

Great example Chip.

Of course it always depends on whether you run the scan for EOD "end of day" or "intraday" data, and when the scan was run, before or after the scan data base was updated.

In answer to the exercise we should change the "greater than >", to, "greater than or equal to >=".
ie: [type = stock] and [today's high >= yesterday's max(200, high)]

For today (March 17), if I run the scan under the following criteria I get;
for ">" 21 hits
for "=" 21 hits
for ">=" 42 hits total (correct, as it should be)

Now we have to look a little closer, under the equal to "=" category we see several stocks with no trading volume, thus the scan engine picks up the same data as for the previous day. A closer look reveals many of these stocks haven't traded in several weeks or months, ie the last trade day data is the same 1 day ago, or 2 days ago or 14 days ago etc.. This is another good reason to add a volume or liquidity criteria to your scans which will filter out these non trading stocks.