To give you one idea of one way of doing it - I've seen plenty of other approaches - you can view my spreadsheet for my S&P 500 trading setup.
By clicking on the links to open the spreadsheet below, you agree that you have read in entirety and agree with the following important Disclaimer information:
I created this blog to record my personal, subjective observations about the markets, solely for my entertainment and that of interested readers and to foster research. Nothing on this blog should be construed as financial advice or an offer or recommendation to purchase or sell any security. I encourage anyone interested in the markets to do your own homework and/or consult a professional advisor. I am not a certified financial advisor and am still refining my trading system. I reserve the right to modify or stop publishing aspects of my system at any time. My system has resulted in very large drawdowns in some trades. Past backtested and real-time results are no guarantee of future performance. I will not be liable for any losses or damages of any kind that result from the content of this site. Although I consider the data, calculations and information on this blog to be reliable, I can't make any guarantees and won't be held liable or responsible for anything erroneous on this website. You are solely responsible for implementing safeguards of your data and system when you use this site and its content and links. It is up to the user of this site and content to protect yourself from worms, trojan horses, viruses and the like. I may hold positions in some of the securities mentioned in this blog.
This information is also on my spreadsheet. If the entire message is not clear, see it by clicking on the word "Disclaimer" in cell A1 at the upper-left corner of the spreadsheet.
Click here [link disabled; setup being updated] to view my S&P 500 trading setup spreadsheet, or download it into Excel by clicking here [link disabled; setup being updated]. Column EF shows this setup's combined signals. My S&P 500 signal is based on combining the signals from two setups based on trading alongside the commercial traders and fading (trading opposite to) the small traders (the "non-reportable" category in the COT data) in S&P 500 futures and options. The signal goes to cash when both setups don't concur.
Setup 1: The first setup goes long when the commercial trader net percentage-of-open-interest position hits 0.05 standard deviations above its 16-week moving average or higher. It goes short when the net position hits 0.45 standard deviations below the moving average or lower. This setup uses a three-week trade delay. [Parameter values being updated. New ones to be announced soon. Thank you for your patience.]
Setup 2: The second setup goes long when the small trader net percentage-of-open-interest position hits 0.6 standard deviations below its 16-week moving average or is lower than that. It goes short when the net position is 0.55 standard deviations above the moving average or is higher. This setup also uses a three-week trade delay. [Parameter values being updated. New ones to be announced soon. Thank you for your patience.]
The spreadsheet should be fairly up to date, but I don't plan to update it on a weekly basis, so if it's missing recent data you'll need to download that from the Commodity Futures Trading Commission website yourself. Go to CFTC.gov and follow the easy downloading instructions. (You'll need to get the entire year's historic dataset and update the spreadsheets with the most recent data.) The CFTC issues new data each week Fridays at 3:30 p.m. (EST). To update the spreadsheet, follow these instructions courtesy of the CFTC.
Some other tips:
1) Futures and options. Remember to download the combined futures and options combined futures-and-optionscombi data — not the futures-only data. Very important! This is the biggest mistake readers make. Many have written to me in frustration saying they didn't get the same signals. It's almost always because they used the futures-only data by mistake. Why use the combined data? My testing showed that in most markets, it led to a more statistically robust and profitable setup. (The exception is my U.S. dollar index setup, which relies on the futures-only data.)
2) Separate spreadsheets. I like to create a separate spreadsheet for each commodity, market or index. As the S&P 500 spreadsheet shows, I calculate the net position of a group of traders by subtracting its short position from its long position.
3) Net percentage. I also prefer to use the net percentage-of-open-interest position, not the net absolute number of contracts. Those numbers are found in columns AW through BE.
4) Excel functions. Calculate the moving averages and standard deviations of the net positions by using Excel's AVERAGE and STDEV functions. My setups work by generating a buy or sell when the net position equals or exceeds a specific number of standard deviations from the moving average. One invaluable function is "control-D." This allows you to extend a calculation in one cell to an entire column. Click the first cell, then while holding down "shift," click the last cell to which you want to extend the calculation and press "control-D." Microsoft offers great searchable Excel how-to info here, including enough financial and stats formulas to make you say "Uncle."
5) Charting. Visually examining historic COTs data can be fascinating and illuminating. It could lead you to new avenues to explore for using the data. To turn your data into a chart, click “Insert” in the upper Excel toolbar, go to “Charts”
Also remember:
Each of my setups is unique. The S&P 500 setup values are good only for signals for this index. Other indexes and securities require different moving average and standard deviation values. For details, check the "Latest Signals & Results" page. If the setup details aren't there, it means I haven't published them yet.
In order to create a spreadsheet for the other setups for which the details are provided, you'll have to download the data for that market into a fresh spreadsheet. Then make the appropriate changes to the formulas with the new moving average and standard deviation values.
The risks. Take a good look at the wins/losses results and drawdown information on the table on my "Latest Signals & Results" page. Let there be no mistake: Trading mechanically isn't for everyone. Using my setups causes me to see a high chance of large losses in some trades and requires disciplined use of stops. Moreover, my system often experiences significant short-term volatility. The goal is to attempt to catch long moves over the long-term, not to win with every single trade. Of course, there has never been a trading system that had a 100-percent win ratio. (If you know of one, I'd love to hear about it.)
See my "Glossary" and "How It Works" pages for an explanation of how I use stops and position sizing to control risk. I'm providing this information for educational and entertainment purposes only, not as a recommendation to use this system or to buy or sell anything. Also be careful to read the entirety of my Disclaimer information.
