Your findings will be most robust if your look-back period (the period that you are drawing data from) includes a variety of market conditions: rising, falling, range bound, high volatility, low volatility, and so on.
In general, my dependent variable will consist of prospective price change, because that is what I’m interested in as a trader. The independent variable(s) will consist of whatever my observations tell me might be meaningfully related to prospective price change. Normally, I look at dependent variables with respect to the next day’s return (to help with day trading ideas) and the next week’s return (to help with formulating swing hypotheses). If I want a sense of the market’s possible bigger picture, I’ll investigate returns over the next 20 trading days. Traders with different time frames may use different periods, including intraday. Overall, I’ve found the 1 to 20 day framework to be most useful in my investigations.
Once again, practice makes perfect. I would encourage you to become proficient at downloading your data and assembling your spreadsheets into variables before you try your hand at the actual historical investigations. Your results, after all, will only be as valid as the data you enter and the transformations you impose upon the data.
COACHING CUE
Note how, with the Practice Sheet assembled as in the above example, you can easily look at the next day’s average returns following opening gaps. Your independent variable would be the opening gap, which would be written as =((b3-e 2)/e2)*100 (the difference between today’s open and yesterday’s close as a percentage). The day’s price change would be =((e3-b3)/b3)*100 (the difference between today’s close and today’s open as a percentage). You would need to use stock index futures data or ETF data to get an accurate reflection of the market open; the cash index does not reflect accurate opening values, as not all stocks open for trading in the first minute of the session.
LESSON 96: CONDUCT YOUR HISTORICAL INVESTIGATIONS
Once you have your data downloaded and your independent and dependent variables calculated, you’re ready to take a look at the relationship between your two sets of variables. In the last lesson, you saved your spreadsheet of the S&P 500 Index data with the prior day’s price change in column F and the next day’s change in column G. Open that sheet, and we will get started with our investigation.
Your first step will be to copy the data from the sheet to a fresh worksheet. We will first copy the data to the Windows clipboard, then paste into the new sheet. This eliminates all formulas from the sheet, because the clipboard saves only alphanumeric text data. This process is necessary for the data manipulations that will be required for our investigation.
So, highlight all the cells in your sheet with the exception of the last row (the most recent day’s data). We don’t include that row in our analysis because there won’t be any data for the next day’s return. With the cells highlighted, click the Excel menu item for Edit, then select Copy. You’ll then exit out of the spreadsheet and instruct Windows to save the data to the clipboard. Open a fresh, blank sheet; click on cell A1; click the Excel menu item for Edit; and select Paste. Your data will be transferred to the new sheet, with no formulas included.
Once you’ve done this, you’ll delete the first row of data below the data labels, because there will be no data for the change from the prior day. When you delete the row by highlighting the entire row and clicking Edit and selecting Delete, the rows below will move up, so that there are no empty rows between the data labels and the data themselves.
You’ll now highlight all the data (including the first row of data labels), select the Excel menu item for Data; then select Sort; and select the option SP(1) in the Sort By drop down menu. You can click the button on the drop down menu for Descending: this will place your largest daily gain in the S&P 500 Index in the first row, the next largest in the second row, etc. The last row of data will be the day of the largest daily drop in the S&P 500 Index.
The Sort function separates your independent variables into high and low values, so that you can see how the dependent variables are affected.
Now we’re ready to explore the data. For the purpose of the illustration, I’ll assume that your data labels are in row 1 and that you have 999 rows of data (998 days of S&P data plus the row of labels). Below your bottom row in column G (say cell G1002), type in “=average(g2:g500)” (without the quotation marks) and hit Enter. In the cell below that (G1003), type in “=average(g501:g999)” and hit Enter. This gives you a general sense for whether next day returns have been better or worse following the half of the days in the sample that were strongest versus the half of days that were weakest. Note that you could analyze the next day returns roughly by quartiles simply by entering “=average(g2:g250)”; “=average(g251:g500)”; “=average(g501:g750)”; and “=average(g751:g999)”.
What your data will show is that next day returns tend to be most positive following weak days in the S&P 500 Index and most restrained following strong days in the Index. How much of a difference makes a difference for your trading? As I emphasized earlier in the chapter, I am not using this information to establish a statistically significant mechanical trading system. Rather, I’m looking qualitatively for differences that hit me between the eyes. These will be the most promising relationships for developing trading hypotheses. If the difference between average next day returns following an up day and a down day is the difference between a gain of 0.01 percent and 0.03 percent, I’m not going to get excited. If the average returns following the strong days are negative and those following the weak days are positive, that’s more interesting.
As you conduct many sortings, you’ll gain a good feel for differences that may form the basis for worthwhile hypotheses.
So how might I use the information? Perhaps I’ll drill down further, examine those quartiles, and find that returns are particularly muted following strong up days. If that’s the case, I will entertain the hypothesis of range-bound trading the morning following a strong daily rise in the S&P 500 Index. If I see that particularly weak days in the S&P 500 Index tend to close higher the next day, I may entertain the notion of an intraday reversal the day following a large drop. The data provide me with a heads up, a hypothesis—not a firm, fixed conclusion.
The data might also help sharpen some of my trading practices. If I’m holding positions for intermediate-term swing positions, I might be more likely to add to a long position after a daily market dip than after a strong daily rise. I might be more likely to take partial profits on a short position following toward the end of a weak market day than toward the end of an up day.
And suppose we find no apparent differences whatsoever? This, too, is a finding. It would tell us that—at this time frame, for this time period—there is no evidence of trend or countertrend effects. This would help us temper our expectations following strong and weak market days. We would not assume that trends are our friends; nor would we be tempted to fade moves automatically. We would also know to look for potential edges elsewhere.
Keep a record of the relationships that you examine and what you find; this will guide future inquiries and prevent you from duplicating efforts later on.
If your analysis does not identify a promising relationship within the data, you’re limited only by your own creativity in exploring alternate hypotheses. For instance, you might look at how prior returns affect next returns for weekly or monthly data, rather than daily data. You might explore next day returns for a different instrument or market. Perhaps you’ll see greater evidence of trendiness in commodities or small stocks than in the S&P 500 Index.
Where your creativity can really kick in is in your selection of independent variables. The same basic spreadsheet format outlined above could be used to examine the relationship between the current day’s put-call ratio and the next day’s S&P 500 returns; the current day’s volume and next day returns; the current day’s financial sector performance and next day S&P 500 returns; the current day’s bond yield performance and next day ret
urns. Once you have the spreadsheet analysis process mastered, it’s simply a matter of switching one set of variables for another. This way, you can investigate a host of candidate hypotheses in a relatively short period of time.
The key to making this work is the Sort command in Excel. This sorts your independent variable from high to low or low to high so that you can see what happens in your dependent variable as a result. Along with visualizing data in charts, sorting is a great way to get a feel for how variables may be related, highlighting important market themes. But save your original spreadsheet with the formulas—the one you had titled Practice Sheet in the previous lesson. We’re not finished with Excel tricks!
COACHING CUE
Here is one fruitful line of investigation: Take a look at next day returns as a function of weak up days versus strong up days. You can define weak versus strong with indicators such as the daily advance/decline ratio or the ratio of up volume to down volume. Limit your sort to the rising days in the sample and sort those based on market strength. What you’ll find for some markets is that very strong markets tend to continue their strength in the near term; weaker rising markets are more likely to reverse direction. Later, you can limit your sorting to the declining days and sort them by very weak and less weak markets. Many times, the patterns you see among the rising days are different from those that show up among the falling days.
LESSON 97: CODE THE DATA
Sometimes the independent variable you’re interested in is a categorical variable, not a set of continuous values. If I wanted to investigate the relationship between a person’s weight (independent variable) and their lung capacity (dependent variable), all of my data would be continuous. If, however, I wanted to investigate the relationship between gender (male/female) and lung capacity, I would now be looking at a categorical variable in relationship to a continuous one. Conversely, if I wanted to simply identify whether a person had normal versus subnormal lung capacity, I would wind up with a categorical breakdown for my dependent variable.
There are times in market analysis when we want to look at the data categorically, rather than in a continuous fashion. In my own investigations, I routinely combine categorical views with continuous ones. Here’s why:
If you reopen the spreadsheet we created, Practice Sheet, that examined current day returns in the S&P 500 Index as a function of the previous day’s performance, you’ll see that we had Date data in column A; open-highlow-close data in columns B-E; the present day’s price change in column F; and the next day’s price change in column G. For the analysis in the last lesson, we sorted the data based upon the present day’s price change and then examined the average price change for the next day as a function of strong versus weak days. Our dependent measure, next day’s price change, was continuous, and we compared average values to get a sense for the relationship between the independent and dependent variables.
Averages, however, can be misleading: a few extreme values can skew the result. These outliers can make the differences between two sets of averages look much larger than they really are. We can eliminate this possible source of bias by changing our dependent variable. We’ll keep the next day’s price change in column G, but now will add a dummy-coded variable in column H. This code will simply tell us whether the price change in column G is up or down. Thus, in cell H2, I would type in (without quotation marks): “=if(G2>0,1,0)” and hit Enter. This instructs the cell at H2 to return a “1” if the price change in cell G2 is positive; anything else—a zero or negative return—will return a “0.” I will then click on H2; click the Excel menu item for Edit; click Copy; click cell H3 and drag all the way down the length of the data; and then click Enter. The 0,1 dummy code will populate each of the column H cells.
We want to know whether the independent variable is associated with greater frequency of up/down days, as well as the magnitudes of change across those days.
Now we go through the same sorting procedure described in the previous lesson. We highlight all the cells in the worksheet—including the new column H—and click Edit and Copy. We exit the spreadsheet, instructing Excel to save changes and to save the highlighted data. We open a new sheet; click Edit; click Paste; and all the spreadsheet data—again minus the formulas—will appear on the sheet. Once again we sort the data by column F (current day’s price change) in descending order, as described in the previous lesson. Again we divide the data in half and, below the last entry in column G, we type in “=average(g2:g500)” and, below that, “=average(g501:g999).”
This, as noted in the previous lesson, shows us the magnitude of the average differences in next day’s returns when the current day is relatively strong (top half of price change) versus relatively weak (bottom half of the price change distribution).
In column H, next to the cells for the two averages in column G, we enter the formula “=sum(H2:H500)” and, below that, “=sum(H501:H999).” This tells us how many up days occurred following relatively strong days in the market and how many up days occurred following relatively weak days. Because we’re splitting the data in half, we should see roughly equal numbers of up days in the two sums if the current day’s performance is not strongly related to the next day’s price change. On the other hand, if we see considerably fewer up days following the strong market days than following the weak ones, we might begin to entertain a hypothesis.
If the average next day changes in column G look quite discrepant, but the number of winning days in the two conditions in column G are similar, that means that the odds of a winning day may not be significantly affected by the prior day’s return, but the size of that day might be affected. In general, I like to see clear differences in both criteria. Thus, if the average size of the next day’s return are higher following a falling day than a rising one and the odds of a rising day are higher, I’ll be most likely to use the observation to frame a possible market hypothesis.
Note that we can dummy code independent variables as well. If, for example, I wanted to see whether an up or down day (independent variable) tended to be followed by an up or down day (dependent variable), I could code column F (current day’s price change) with a code as above in column H and also code column G (next day’s price change) identically in column I. I would then copy the spreadsheet to a fresh sheet and sort the data based on column H, so that we’d separate the 1s from the 0s. We’d then examine the column I sum for the cells in column H that were 1s and compare with the column I sum for the cells in column H that were 0s.
Dummy coding is especially helpful if we want to examine the impact of events on prospective returns. For instance, we could code all Mondays with a 1; all Tuesdays with a 2, etc., and then sort the next day’s return based on the codings to tell us whether returns were more or less favorable following particular days of the week. Coding is also useful when we want to set up complex conditions among two or more independent variables and examine their relationship to future returns. This kind of coding gets a bit more complex and will form the basis for the next lesson.
COACHING CUE
If you include volume in your spreadsheet, you can code days with rising volume with a 1 and days with falling volume with a 0. This would then allow you to compare next day returns as a function of whether today’s rise or decline were on rising or falling volume. All you’d need to do is sort the data once based on the current day’s price change and then a second time separately for the rising and falling occasions as a function of the rising and declining volume.
LESSON 98: EXAMINE CONTEXT
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 Alaskan wilderness.
We can code market data for contexts and then investigate patterns specific to those contexts. What we’re really asking is, “Under the set of conditions that we find at present, what is the distribution of future expectations?” We’re not pretending that these will be universal expectations. Rather, they are contextual—applicable to our current situation.
Many of the most fruitful trading hypotheses pertain to certain kinds of markets—not to all markets, all the time.
Let’s 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 day’s 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:
= if(E22>average(E3:E21),1,0)
This will return to cell G23 a “1” if the current price is above the prior day’s 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 day’s 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, we’ll have to eliminate rows 2 to 21, since they don’t have a value for the 20-day moving average. We’ll 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.
The Daily Trading Coach Page 44
