More Tools, CZ13 Example

The purpose of this website is to provide a learning experience that is understandable for newcomers to trend analysis.  Up until now, focus has been on a simple concept of two moving averages and the crossover to signal a change in trend.  The main point was to demonstrate that the process can be relatively simple and effective using methods that are easy to understand and calculate. 

With this article, I want to do two things.   First, I want to introduce you to a whole family of indicators based around MA(5) and MA(35) that we started with.  Second, I want to demonstrate how you can use spreadsheets to do most of the work to back test different indicators to get a better feel for how they perform.  It is easy to talk about how indicators perform and yet be difficult to really get a hold of what is going on.  Hopefully this piece will bring some of that into focus. 

Earlier I was asked if it is necessary to buy a software charting package or service to do trend analysis, and my answer was no for two main reasons.  There are various sites which offer basic charting services without charge, and those should be adequate for most beginners.  The other option is to create your own charts and analysis within a spreadsheet format.  Certainly, neither of these choices allow for easy application of very sophisticated and complex technical indicators, but I would again remind you that it is not necessary to go there if your primary objective is only to identify the main trend so that you can put price protection in place when prices are falling and not be locked in when prices are rising. 

Some of you are already monitoring the trends with spreadsheets and other software, and others are still struggling with how to get started at minimum cost until they have a chance to see how these things work in real time.  I have put together a spreadsheet for Dec 13 corn (which I have attached to the post by similar title on the Discussion page) for your use as a learning aid.  The goal was to give beginners a quick start and to demonstrate several different things that can be done with the spreadsheets.  The first page of the spreadsheet (Base) includes a data table which you will need to keep current by entering the date and closing price.  The table will then compute the moving average for 5 and 35 days in the third and fourth columns.  In the fifth column, I have introduced a concept known as an oscillator, which is based on the actual difference between MA(5) and MA(35).  I will elaborate more when I explain the Oscillator graph.  

The sixth column is programmed to check whether MA(5) is above or below MA(35) and record the position as an UP trend or DOWN trend.  The seventh column checks to see if the trend changed from UP to Down or from DOWN to UP and records the appropriate sale or purchase price so we can keep a running total of gain or loss on the hedging activity.  Since we are looking for hedging opportunities, the system is programmed to enter the market with a sale when the trend changes from UP to DOWN and to record an exit price when the trend changes from DOWN to UP.  The cumulative gain or loss is calculated at the bottom of the column.  Note that since the system has entered an entry point without a corresponding exit point, it is necessary to manually enter a matching exit value to get an accurate calculation of the gain or loss up to the point of last entry. 

The eighth column calculates the closing price for the next day that would be required to cause the two moving averages to cross.  Normally, this number will not be in play, but if you are tracking the market closely, it gives you the number in advance that would be required to change the trend signal.  If you are following the system exactly, you would know in advance if the market was trading above or below your critical value so that you could take action before the market closes. 

For those of you who are more visually oriented, the graph at the top of the page and to the right of the table is set up to post MA(5) and MA(35) so that you can see where the crossovers take place.  As you add data to the table by inserting rows, the graph is set up to extend the range of date presented.  Obviously the graph may eventually get so populated that it will be hard to read.  At that point, I would suggest copying the graph to another space on the spreadsheet and changing the starting point for the data sets so that only the most current data will be posted to the graph.  Alternatively, you can just change the starting point for the data sets on this graph if you don’t want to retain the graph for the whole data series. 

The graph just below MA(5,35) is a graph of the MA(5) and MA(35) Oscillator.  The vertical distance between the two lines is an indication of the strength of the trend.  As MA(5) diverges (moves away) from MA(35), the trend is getting stronger.  As MA(5) converges (gets closer) to MA(35), the trend is losing strength.  The Oscillator can be used as a stand alone signal, or in combination with other indicators.  As you compare the Oscillator graph to the one above, you will note that the Oscillator comes to zero when MA(5) is going to cross MA(35).  In reality, there is no new information, but it may be easier to see the trend gaining and losing strength on the Oscillator graph. 

Moving on to the second page (More) of the spreadsheet, let me introduce you to a whole family of related indicators, but with each doing something a little different.  We have worked with MA(5) and MA(35), and used MA(5) crossover MA(35) as a signal that the trend is changing.  Let’s add MA(17) and the Closing price to the mix.  Immediately you have an expanded list of potential signals for trend change.  We will look at MA(5) crossing MA(17) which you can expect to respond a bit quicker to price movement, and MA(17) crossing over MA(35) which you would expect to be slower than MA(5) crossing MA(35).  Some folks prefer using the Close with the moving averages instead of two moving averages, so the signal could be the Close crossing over MA(5) or MA(17) or MA(35).  Normally, using price directly will get a signal quicker than using a moving average for the crossover signal. 

The first five columns of More are the date, closing price, and the three moving averages (5, 17, & 35).  The rest of the table is paired columns to determine the trend based on the indicator at the head of the column and to post the entry and exit price when there is a trend change.  The cumulative hedging gain or loss is calculated at the bottom of the column.  Again, note that it is necessary to manually enter an exit price if the system has entered an entry price but not yet closed that trade. 

By comparing the performance of various indicators side by side, it is much easier to begin to get a better feel for how a particular indicator reacts and how you would expect the results of that indicator to differ from the other indicators.  With the previous data and quantitative decision rules, we can summarize how an indicator has performed in the past.  The process is called back testing.  Certainly past performance is no guarantee of future performance, but it does give you insight into how an indicator reacts to different price moves. 

I have summarized some of the key performance measures of the different indicators on the third page (Summary) of the spreadsheet.  It is important that I emphasize a word of caution here.  The data that has been summarized here is only since September 2012, and no allowances have been made for brokerage charges, exchange fees, or slippage.  The formula for the exit price could easily be modified to include commissions and fees, but they differ so much from individual to individual that I have opted to leave them out.  You may want to adjust the formulas to your situation or just make manual entries or adjustments to account for these charges. 

What are some of the key performance indicators that you want to compare?  Obviously, cumulative gain or loss is important.  MA(17,35) jumps out with a gain through 6/28/13 of just over $0.65.  It should be noted that is the only indicator that had not already signaled a down turn in the trend before or on the day of the Planted Acres report, and in fact was still open after Monday’s close.  The number of trades are a direct indication of how closely you have to monitor the market.  With the six indicators that are summarized here, the number of sell signals range from three to 22 over approximately nine months.  The relative size of the gains and losses on individual trades are an indication of how timely the signals are.  Finally, the net hedge is the goal we want to focus on.  Net hedge is calculated here as the price at the last sell signal plus or minus the gain or loss in the hedge account.  

Needless to say, there are tradeoffs that are the reason that I have indicated that you need to first learn how indicators work, and then study their performance to decide which one comes closes to providing signals where you would want to make decisions.  Obviously, there are hundreds, if not thousands, of indicators you could choose to use.  Even with these three moving averages, some folks choose to use a combination of the price and two of the averages as a signal, or three averages as a signal.  These are all refinements that might improve performance slightly, but focus here is on finding an indicator that provides you with timely alerts when the trend is changing, and corrects itself quickly when it gives a false signal. 

I can’t tell you that any of these indicators will meet your needs or be right for you.  What I can tell you is that the performance of several of these indicators can be improved significantly by adding a qualifying rule to the signal.  For example, if you are using MA(35) as the overall trend indicator, and the crossover of MA(5) as a signal, several of the false signals that had to be reversed can be eliminated by ignoring them if the MA(35) trend is down when MA(5) crosses up through MA(35).  You can find these points on the MA(5,35) graph and delete the entries in the gain or loss column.  These are the kinds of subjective modifications you can make, and still maintain an objective system.  With a little more modification of the formulas, the spreadsheet could be programmed to make that adjustment. 

The focus here is not to say one indicator is better than another for all purposes, but to allow new users to begin to understand what is happening behind the scenes rather than blindly following someone’s or some systems recommendations.  With a little work with the spreadsheets, you should be able to program your favorite indicator and create the paired columns to summarize its performance.  If you need help, don’t hesitate to contact me with a Reply to the email with similar title or through the Contact Us page on the website. 

Coming soon, similar summaries for soybeans and wheat. 

Posted by Keith D. Rogers on 2 July 2013.