Automating the Henning Technical-Momentum Trading System

In the August 2010 CI Online Exclusive article, we discussed the technical-momentum stock trading model of Grant Henning, which he outlined in his book “The Value and Momentum Trader” (John Wiley & Sons, 2010). This was the first in a series of three articles on the three trading models Henning introduces in his book (the third and final article in this series begins on page 16 while the September Online Exclusive article with the second part is available at the Computerized Investing website).

Initial Watchlist

For all three of his trading models, Henning employs a set of qualifying variables to winnow down the stock universe to a watchlist of roughly 200 companies. These filters are as follows:

• Stock price has at least doubled over the last 52 weeks;
• Stock price has risen at least 30% off of the low price of the last three months;
• Stock price is over $5; and
• Average daily trading volume is over 10,000 shares.

For the August Online Exclusive article, we used AAII’s Stock Investor Pro fundamental stock screening and research database program to screen for this initial watchlist of companies. Looking for an online screening source that also allows you to export your screening results proved to be more difficult than anticipated. In the end, we used the SmartMoney Select screener, which allowed us to screen for the 52-week price change as well as minimum share price and average daily volume. We were then able to export our screening results as an Excel spreadsheet file, a function that few other online screeners provide. As of the close of August 27, 2010, 122 stocks met these qualifying criteria to form our initial watchlist.

First:    Last:    Email:

Once Henning has his initial watchlist, he begins analyzing several different variables, all of which are based on current and historical price data. To automate the collection of the necessary data and the calculation of these variables, we created a spreadsheet template. You can download this template from the Computerized Investing website at the online version of this article.

XLQ

Today investors can access a wealth of free data from the Internet. While many websites, such as Google Finance and Yahoo! Finance offer free historical quotes for stocks, mutual funds and ETFs, they are not readily accessible for “batch processing”—collecting a range of historical data for a group of companies. However, there are relatively inexpensive utilities available that do just that. One that we use extensively is XLQ from Q-Matix (www.qmatix.com).

XLQ delivers live and historical stock, index, mutual fund, option, future, or currency data pulled from either free or subscription-based Internet data services (the type of instruments and data provided varies from vendor to vendor). Beyond retrieving data, it provides you with a set of formulas that you can use to design your own portfolio manager and stock analysis spreadsheets.

XLQ comes in two versions—XLQlite and XLQplus. The biggest difference between XLQlite and XLQplus is the support of third-party software—including AAII’s Stock Investor Pro—and additional data vendors. If you are looking to use Yahoo! or MSN as your data source, you will be fine with XLQlite. But XLQlite also lacks the more advanced formulas found in XLQplus.

The versions cost $84 and $139, respectively; however, you can download and use the more robust XLQplus for free for 45 days. A paid subscription to XLQ entitles you to a year’s worth of program updates. After the first year, you can renew your license each year at a discount (currently $54 for XLQplus and $31 for XLQlite). Also, AAII members receive a $25 discount the first time they order XLQplus by using the code AAII-SIP.

For a more detailed review of XLQ, see the Spreadsheet Corner column in the Fourth Quarter 2009 issue of Computerized Investing, available online.

Building Our Spreadsheet

Once we ran our screen in SmartMoney Select, we exported the tickers of the 122 passing companies in Excel format. We then pasted these tickers in the Input Data tab in our technical-momentum spreadsheet. XLQ uses these tickers to retrieve the data we need for the companies in our watchlist. Therefore, if you are using this spreadsheet for your own analysis, be sure to enter your watchlist tickers in Column A of the Input Data worksheet, beginning with Row 1. Once you do this, XLQ will populate the rest of the spreadsheet for you. However, we will walk you through the various worksheets of this spreadsheet and the underlying functions.

Data Sheet

The raw data we use in calculating the technical-momentum model variables are retrieved within the Data Sheet worksheet, which is shown in Figure 1. Here XLQ has pulled the company name, ticker, and exchange along with the current, 52-week high and low, and three-month low (3-mo low) prices.

Here are the functions we used in Cells A5 through H5, respectively:

A5: =xlqName(‘Input Data’!A2,”msn”)
B5: =‘Input Data’!A2
C5: =xlqExchange(‘Input Data’!A2)
D5: =xlqPrice(‘Input Data’!A2)
E5: =xlq52WeekHigh(‘Input Data’!A2)
F5: =xlq52WeekLow(‘Input Data’!A2)
G5: =xlqhLowestClose(‘Input Data’!A2,’Input Data’!$D$1)
H5: =xlqhLowestCloseDate(‘Input Data’!A2,’Input Data’!$D$1)

Each of these functions relies on the tickers we entered in the Input Data worksheet. So whenever the tickers in that worksheet change, the impact flows through the other worksheets in the spreadsheet.

The functions for cells A5 through F5 are referred to as “day quote formulas” in XLQ-speak. While the formulas may look intimidating, they do not require any programming knowledge. If you know your way around a spreadsheet, you shouldn’t have any problems with the XLQ formulas. With a little patience, you will come to understand the logic.

Looking at the formula for cell D5, which is pulling the latest price quote, we can break it down into its component parts. As with any other functions or formulas in Excel, you begin with the equals sign (=):

=xlqPrice(‘Input Data’!A2)

This formula then retrieves the last trade price for the ticker in cell A2 of the Input Data worksheet. The data provider used—Yahoo! or MSN—is set in the XLQ preferences. Both these services offer delayed intraday quotes.

However, you can also specify the source in the formula, perhaps overriding the program default:

=xlqPrice(‘Input Data’!A2, “MSN”)

This formula would then display the latest trade price provided by MSN for the ticker in cell A2 of the Input Data worksheet.

Cells G5 and H5 use “historical quote formulas” that retrieve prior-period data for a symbol from a specific source.

With the historical formulas, you can retrieve historical data from specific dates or from a specific number of periods ago. You can also identify the highest or lowest intraday price (as well as the highest closing price) for a symbol over a specified period of time.

In XLQ, historical quote formulas differ slightly from the day quote formulas:

=xlqhFunction(“symbol”, “date reference”, “source”)

Looking at cell G5’s formula:

=xlqhLowestClose(‘Input Data’!A2,’Input Data’!$D$1)

This formula displays the lowest closing price for the ticker symbol in cell A2 of the Input Data worksheet, with the look-back period being the number entered in cell D1 of the Input Data worksheet. We look at the last 65 trading days when looking for the lowest price, as this roughly equates to the 90-day period Henning uses for his analysis. Therefore, we have entered -65 into cell D1 of the Input Data worksheet (where we also paste the watchlist tickers).

Technical-Momentum Calculations

Once the data has been pulled into the Data Sheet worksheet we can start calculating the variables Henning uses in his technical-momentum model. For this, we created yet another worksheet in our spreadsheet—Technical-Momentum Calculations—where we entered the functions to calculate the five variables underlying this technical-momentum model (Figure 2):

• 52-week multiple;
• % lag;
• investment value;
• three-month price gain; and
• rank.

52-Week Multiple

The 52-week multiple represents the number of times the stock price has “multiplied” from the 52-week low to its current level or:

Current Price ÷ 52-Week Low Price

In the Data Input worksheet, which is again where the underlying data for these variables is being pulled, the current price is in Column D of the Data Sheet and the 52-week low price is in Column F of the same worksheet, so that the function for the 52-week multiple in the Technical-Momentum Calculations worksheet is:

B4: =’Data Sheet’!D5/’Data Sheet’!F5

Percentage Lag

The percentage lag variable in Henning’s technical-momentum model measures the degree to which the current stock price is below, or lags, the 52-week high price. Similar to the more common percentage of 52-week high that many momentum strategies use, it is calculated as follows:

[52-Week High Price – Current Price – $0.02] ÷ 52-Week High Price

Henning discovered that if the current price equaled the 52-week high, the resulting 0.0 value for percentage lag would interfere with other variables in the model that use the percentage lag value in their calculation. As a result, Henning arbitrarily deducts $0.02 from the current price when he calculates the percentage lag. However, we also discovered that, since the 52-week high is updated in most databases only after the end of each trading day, there is the potential for the current price to exceed the 52-week high during the course of the trading day. As a result, it is also possible for the percentage lag to be negative, thereby disrupting some of the other variables in the model that use this variable. Therefore, we used an if-then statement in our percentage lag function in the Technical-Momentum Calculations worksheet that returns a percentage lag value of 0.001 if the current price (Column D of the Data Sheet) exceeds the 52-week high (Column E of the Data Sheet):

C4: =IF(((‘Data Sheet’!E5-’Data Sheet’!D5-0.02)/’Data Sheet’!E5)<=0,0.001,((‘Data Sheet’!E5-’Data Sheet’!D5-0.02)/’Data Sheet’!E5))

Investment Value

The next variable Henning uses in his technical-momentum model is one that he created—investment value. It is a weighted rate-of-ascent (price change) value—52-week price multiple—divided by a weighted percent lag value. Specifically, the formula is as follows:

(3 × 52-Week Multiple) ÷ (2 × % Lag)

Since we calculate these variables in Columns B and C, respectively, of the Technical-Momentum Calculations worksheet, we do not have to pull data from any of the other worksheets in the spreadsheet:

D4: =(3*B4)/(2*C4)

Three-Month Price Gain

Henning uses a unique three-month price change figure for both his technical-momentum model and when generating his initial watchlist. He looks at the percentage change from a stock’s low price over the last three months to its current level:

[(Current Price – Three-Month Low Price) ÷ Three-Month Low Price] × 100

When we were discussing the Data Sheet items, we covered how we were able to retrieve the lowest closing price over the last 65 trading days (a rough equivalent of 90 calendar days) using the historical quote formulas. Now that we have the three-month low price (cell G5 in the Data Sheet worksheet), we can use it to calculate the percentage difference between it and the current price (cell D5 in the Data Sheet worksheet):

E4: =((‘Data Sheet’!D5-’Data Sheet’!G5)/’Data Sheet’!G5)*100

Rank

The last variable Henning uses for this technical-momentum model involves ranking the watchlist from lowest to highest based on the investment value and then comparing this adjusted rank to the three-month price gain. He does this using the rank function in the Microsoft Excel spreadsheet program.

In Excel, Henning uses the rank function to first order and smooth the investment value for each stock in the watchlist. The results of this ranking are then divided by each stock’s three-month price gain to give priority to those stocks experiencing strong upward price momentum over the last three months.

The rank formula used in our technical-momentum spreadsheet is as follows:

F4:=IF(E4<=0,”na”,(RANK(D4,$D$4:$D$125,0)*2+100)/E4)

Once again, we use an if-then statement that first looks to see if the three-month gain is negative. If not, a null or “na” is returned for that cell’s rank value. If the three-month price gain is positive, the rank components of this formula are as follows:

• RANK is the Excel spreadsheet ranking function;
• D4 is the first data cell of the investment value data column;
• $D$4:$D$125 is the fixed range of investment value cells used by the rank function;
• 0 signifies that the ranking order will be in ascending order (smallest to largest);
• E4 is the first data cell of the three-month price gain column.

Tally & Recommendations

The final step in our Henning technical-momentum analysis is to assign weightings to each of the four variables, tally these weightings, and then assign a recommendation based on the final tally. For this, we created one last worksheet in the spreadsheet—Tally & Recommendations (Figure 3).

For the technical-momentum variables in columns B through E, we set up a series of if-then formulas that assign numerical weighting of 0 or 1, depending on their value:

A3: =’Technical-Momentum Calculations’!A4
B3: =IF(‘Technical-Momentum Calculations’!C4<=0.03,1,0)
C3: =IF(‘Technical-Momentum Calculations’!D4>=100,1,0)
D3: =IF(‘Technical-Momentum Calculations’!E4>100,1,0)
E3: =IF(‘Technical-Momentum Calculations’!F4<1.5,1,0)

After assigning the weighting to these four variables, we then tally these values:

F3: =SUM(B3:E3)

Lastly, we assign recommendations using Henning’s ratings scale and a nested if-then function:

G3: =IF(F3=4,”Strong Buy”,IF(F3=3,”Buy”,IF(F3=2,”Hold”,”Sell”)))

In this formula, if a company has a rating of four, as calculated in cell F3, it is awarded a Strong Buy recommendation; if a company has a rating of three, it is awarded a Buy recommendation; if a company has a rating of two, it is awarded a Hold recommendation; otherwise, a company is awarded a Sell recommendation.

Figure 3 ranks the companies in the Tally & Recommendations worksheet in order by highest total score to lowest. As of September 1, 2010, only two companies from our watchlist of 122 companies received any type of buy recommendation—Wainwright Bank & Trust (Strong Buy) and HSW International Buy.

Conclusion

This article was intended to show you how you can use a relatively inexpensive Excel plug-in to retrieve and manipulate free price data from the Internet. As your familiarity with both Excel and XLQ grows, the limits to which you can craft your own financial models are virtually boundless.