Philosopher Stephen Pepper coined the term contextualism to describe a worldview in which truth is a function of the context in which knowledge is embedded. A short-term price pattern might have one set of expectations in a larger bull market; quite another under bear conditions. A short-term reversal in the first hour of trading has different implications than one that occurs midday. To use an example from The Psychology of Trading, you understand Bear right! one way on the highway, quite another way in the
We can code market data for contexts and then investigate patterns specific to those contexts. What were really asking is, Under the set of conditions that we find at present, what is the distribution of future expectations? Were not pretending that these will be universal expectations. Rather, they are contextualapplicable to our current situation.
Many of the most fruitful trading hypotheses pertain to certain kinds of marketsnot to all markets, all the time.
Lets retrieve the Practice Sheet historical daily data for the S&P 500 Index that we used in our previous lessons. To refresh memory: Column A in our spreadsheet consists of the Date; columns B through E are open-highlow-close data. Column F is the independent variable, the current days price change; entered into cell F22, it would be: =((F22-F21)/F21)* 100.
Column G will serve as our contextual variable. In G22, we enter the following:
This will return to cell G23 a 1 if the current price is above the prior days simple 20-day moving average for the S&P 500 Index; a 0 if it is not above the average. The data label for cell G1 might be MA. Our dependent measure will be the next days price change. In cell H22, this would be =((E23-E22)/E22)* 100 and H1 would have the label SP+1. To complete the sheet, we would highlight cell G22 and H22; click the
Excel menu item for Edit; click Copy; highlight the cells below G23 for the full length of the data set; and hit Enter. We highlight and copy all the data in the sheet as before; save the sheet as Practice Sheet2; and instruct Windows to save the data to the clipboard for another application. We open a fresh spreadsheet; click on cell A1; click the Excel menu item for Edit; select Paste; and our sheet now fills with text data. Note that, in this case, well have to eliminate rows 2 to 21, since they dont have a value for the 20day moving average. Well also eliminate the last row of data, because there are no data for the next day. You eliminate a row simply by highlighting the letter(s) for the row(s) at left; clicking the Excel menu item for Edit; selecting Delete. The row will disappear and the remaining data below will move into place.
Note that using a moving average as a variable of interest reduces the size of your data set, since the initial values will not have a moving average calculated. You need to take this into account when determining your desired sample size.
We now double sort the data to perform the contextual investigation. Lets say that were interested in the expectations following a rising day in a market that is trading above versus below its 20-day moving average. We sort the data based on column F SP(1) as we did in our previous lesson, performing the sort on a Descending basis, so that the largest and positive price changes appear at the top of the sheet. Now we select only the data for the cells that show positive price change and copy those to another sheet. Our second sort will be based on column G (MA), again on a Descending Basis. This will separate the up days in markets trading above their 20-day moving average (those coded 1) from all other days.
As before, well examine the average next days return by calculating the average for the cells in column H that are coded in column G as 1 and comparing that to the average for the cells in column H that are coded in column G as 0.
Thus, lets say that there are 538 cells for up days; 383 of these are coded 1 in column G and 155 are coded 0. You would compare =average(H2:H384) and =average(H385:H539). You could also code
the cells in column H as either 1 or 0 in column I based on whether they are up or down =if(H20,1,0) and then compare =sum(I2:I384) and =sum(I385:I539) to see if there are notable differences in the number of up days following up days in markets that are above and below their moving averages.
Just for your curiosity, using cash S&P 500 data as the raw data, I found that the average next day change following an up day when were above the 20-day moving average to be ?0.04 percent; the average next day change following an up day when were below the 20-day moving average was ?0.18 percent. This is a good example of a finding that doesnt knock my socks off, but is suggestive. I would want to conduct other investigations of what happens after rising days in falling markets before generating trading hypotheses that would have me shorting strength in a broader downtrend.
Many times youll see differences in the sorted data that are strong enough to warrant further investigation, but not strong enough to justify a trading hypothesis by itself.
This combination of coding and sorting can create a variety of contextual views of markets. For example, if we type in, =if(E21=max(E2:E21),
1,0) we can examine the context in which the current day is the highest price in the past 20 and see how that influences returns. If we include a second independent variable, such as the number of stocks making new 52-week highs and lows, we can examine how markets behave when new highs exceed new lows versus when new lows exceed new highs. For instance, if new highs go into column F and new lows into column G, we can code for =if(F21G21,1,0 in column H, place our dependent measure (perhaps the next days price change) in column I and sort based on the new high/low coding.
As mentioned earlier, it is wise to not create too many contextual conditions, because you will wind up with a very small sample of occasions that fit your query, and generalization will be difficult. If you obtain fewer than 20 occasions that meet your criteria, you may need to relax those criteria or include fewer of them.
As your own trading coach, you can utilize these contextual queries to see how markets behave under a variety of conditions. The movement of sectors, related asset classesanything can be a context that affects recent market behavior. In exploring these patterns, you become more sensitive to them in real-time, aiding your selection and execution of trades.
If youre interested in longer-term trading or investing, you can create spreadsheets with weekly or monthly data and investigate independent variables such as monthly returns on the next months returns; VIX levels on the next months volatility; sentiment data on the next months returns; price changes in oil on the next months returns, etc. You can also code data for months of the year (or beginning/end of the month) to investigate calendar effects on returns.
Lets say you want to analyze intraday information for the S&P 500 Index futures. Now your spreadsheet will look different as you download data from sources such as your real-time charting application. Your first column will be date, your second column will be time of day, and your next columns will be open, high, low, and closing prices. If you so select, the next column can be trading volume for that time period (one-minute, fiveminute, hourly, and so on).
Suppose you want to see how the S&P 500 market has behaved at a certain time of day. What we will need to do is filter out that time of day from the mass of downloaded data and only examine that subset. Instead of sorting data, which has been a mainstay of our investigations to this point, we will use Excels filter function.
To illustrate how we might do this, well start with a simple question. Suppose we want to know how trading volume for the current first halfhour of trading compares with the average trading volume for that corresponding half-hour over the prior 20 days of trading. This will give us a rough sense of market activity, which correlates positively with price volatility. The volume also gives a relative sense for the participation of large, institutional traders. If, say, we observe a break out of a range during the first 30 minutes of trading, it is helpful to know whether or not these large market-moving participants are on board.
Volume analyses can help you identify who is in the market.
For this investigation, well examine half-hourly data for the S&P 500 emini contract. I obtain my intraday data from my quote platforms; in the current example, Ill use e-Signal. To do this, we create a 30-minute chart of the ES futures contract; click on the chart and scroll to the right to move the chart backward in time. When weve covered the last 20 days or so, we click on the menu item Tools; select Data Export; then uncheck the boxes for the data that we wont need. In this case, all well need is Date, Time, and Volume. We click the button for Copy to Clipboard and open a fresh sheet in Excel. Once we click on the Excel menu item for Edit and select Paste, with the cursor at cell A2, well populate the sheet with the intraday data. We can then enter names for the columns in row 1: Date; Time; and Volume. (If youre downloading from e-Signal, those names will accompany the data and you can download the data with the cursor at A1). Our next step is to highlight the entire data set that we want to cover. We click on the Excel menu item for Data; select Filter; and select AutoFilter. A set of small arrows will appear beside the column names. Click the arrow next to Time and, from the drop down menu, select the time that represents the start of the trading day. In my case, living in the Chicago area in Central Time, that would be 8:30 A .M . Youll then see all the volume figures for the half-hour 8:30 A .M . to 9:00 A .M . Click on Edit; select Copy; open a blank sheet; click on Edit; and select Paste. This will put the 8:30 A .M . data on a separate sheet. If you have 20 values (the past 20 days), you can enter the formula =average(c2:c21) and youll see the average trading volume for the first half-hour of trading. Of course, you can filter for any time of day and see that half-hours average volume as well.
When you know the average trading volume for a particular time period, you can assess institutional participation in real timeparticularly with respect to whether this volume picks up or slows down as a function of market direction.
The filter function is helpful when you want to pull out data selectively from a data set. Lets say, for instance, that you had a column in which you coded Mondays as 1; Tuesdays as 2; etc. You could then filter out the 1s in the historical data set and see how the market behaved specifically on Mondays. Similarly, you could code the first or last days of the month and filter the data to observe the returns associated with those.
In general, I find filtering most helpful for intraday analyses, when I want to see how markets behave at a particular time of day under particular conditions. Frankly, however, this is not where I find the greatest edges typically, and its not where Id recommend that a beginner start with historical investigations. Should you become serious about investigating such intraday patterns, I strongly recommend obtaining a clean database from a vendor such as Tick Data. You can use their data management software to create data points at any periodicity and download these easily to Excel. Serious, longer-term investigations of historical intraday data need tools far stronger than Excel. Limits to the size of spreadsheets and the ease of maneuvering them make it impossible to use Excel for long-term investigations of high frequency data.
Still, when you want to see how markets behave in the short runsay, in the first hour of trading after a large gap openinvestigations with intraday data and filtering can be quite useful. Youll find interesting patterns of continuation and reversal to set up day-trading ideas or to help with the execution of longer timeframe trades.
Filtering can be useful for examining patterns of returns as a function of time of day. For instance, say the market is down over the past two hours: how do returns compare if those two hours are the first versus the last two hours of the day? How are returns over the next few hours impacted if the day prior to those two hours was down? Such analyses can be very helpful for intraday traders, particularly when you combine price change independent variables with such intraday predictors as NYSE TICK.
MAKE USE OF YOUR FINDINGS
This chapter has provided only a sampling of the kinds of ways that you can use simple spreadsheets and formulas to investigate possible patterns in historical data. Remember: these are qualitative looks at the data; they are designed to generate hypotheses, not prove them. Manipulating data and looking at them from various angles is a skill just like executing trades. With practice and experience, you can get to the point where you investigate quite a few patterns all in the hour or two after market close or before they open.
The key is to identify what makes the current market unique or distinctive. Are we well below or above a moving average? Have there been many more new lows than highs or the reverse? Has one sector been unusually strong or weak? Have the previous days been strong or weak? It is often at the extremeswhen indicators or patterns are at their most unusualthat we find the greatest potential edges. But sometimes those unique elements are hard to find. Very high or low volume; strong or weak put/call ratios; large opening gapsall are good areas for investigations.
We find the greatest directional edges following extreme market events.
Once you have identified a pattern that stands out, this becomes a hypothesis that you entertain to start a trading day or week. If, say, I find that 40 of the last 50 occasions in which the market has been very weak with a high put/call ratio have shown higher prices 20 days later, this will have me looking for a near-term bottoming process. If, after that analysis, I notice that were making lower price lows but with fewer stocks and sectors participating in the weakness, this may add a measure of weight to my hypothesis. Eventually, I might get to the point where I think weve put in a price bottom and Ill buy the market, giving myself a favorable risk/reward should the historical pattern play out.
But equally important, consider the scenario in which we see good historical odds of bouncing over a 20-day period, leading us to search for a near-term bottoming process. My fresh data, however, suggest that the market is weakening further: more stocks and sectors are making lows, not fewer. The historical pattern does not appear to be playing itself out. This, too, is very useful data. When markets buck their historical tendencies, something special may be at work. Some very good trades can proceed from the recognition that markets are not behaving normally.
This is the value of considering patterns as hypotheses and keeping your mind open to those hypotheses being supported or not. A historical pattern in markets is a kind of script for the market to follow; your job is to determine whether or not its following that script.
Our analyses only inform us of historical tendencies. If a market is not behaving in a manner that is consistent with its history, this alerts us to unique, situational forces at work.
All of this suggests that historical investigations are useful logical aids, but my experience is that their greatest value may be psychological. Day after day, week after week, and year after year of investigating patterns and running market results through Excel have given me a unique feel for patterns. It also has given me a keen sense for when patterns are changing: when historical precedents may no longer hold.
One routine that has been very helpful has been to isolate the last five or so instances of a potential pattern. If the market has behaved quite differently in the last several instances than it has historically, I entertain the possibility that were seeing a shift in market patterns. If I see the last few instances behaving abnormally across many different variables and time frames, those anomalies strengthen my sense of a market shift.
When I see how results have played out over the years, I become a less nave trend follower. I dont automatically assume that rising markets will continue to skyrocket or that falling markets will continue to plunge. Ive developed tools for determining when trending markets are gaining and losing steam; these have been helpful in anticipating reversals. Seeing how these indicators behave under various market conditions over timeand actually quantifying their track recordhas provided me with a measure of confidence in the ideas that I would not have in the absence of intimacy with the data.
Much of the edge in trading comes from seeing markets in unique ways, catching moves before they occur or early in their appearance. It is easy to become fixed in our views, with vision narrowed by looking at too few markets and patterns. As your own trading coach, you need to keep your mind open and fresh. Read, talk with experienced traders, follow a range of markets closely, test patterns historically, and know whats happening globally: youll see things that never register on the radar of the average trader. Youll be at your most creative when you have the broadest vision.
When you examine historical patterns, go into your data set and specifically examine the returns from the occasions that didnt fit into the pattern. This will give you an idea of the kind of drawdowns you could expect if you were to trade
the pattern mechanically. Many times, the exceptions to patterns end up being large moves; for instance, most occasions may show a countertrend tendency with a relative handful of very large trending moves. If you know this, you can look for those possible exceptions, study them, and maybe even identify and profit from them.