Trading Systems On Excel

                             
Why MS Excel? You may ask.  I know, it has its limitations and fair shares of ridicule such as “it is meant for amateurs”, “poor man’s tool”, “it is unreliable, especially the rand function is not really random” etc.

In my opinion MS Excel has grown and improved quite a lot.  In fact, the benefit of using it now as a fast starter tool for beginners and average users outweighs its shortcomings.  It is good enough for most of us.  Most importantly a lot of users can start immediately without paying extra for other software.  That is a huge cost savings as some of the trading systems development and testing software cost easily few thousand dollars not to mention you have to pay yearly subscriptions.  In addition, there are plenty of reference books or online help available.  However, if you are planning to back-test a portfolio consist of multi-market and multi-timeframe strategies then Excel is not for you.

The files we have created using Excel are also very portable and we have less worry that the company behind it would stop supporting it suddenly.  On top of that, we can also check and verify the coding or scripting logic easily.  Anyway, if you are a rocket scientist or advance trader, this blog is not meant for you.

There are many different steps that beginners can follow and learn to create their own simple trading and back-testing systems.  Usually different “professionals” would stress their own unique terms to differentiate from others but nevertheless the overall process is quite similar among each other.  Here is mine in summary (I will explain each step in more detail in my future postings):

*please note that my focus is for futures systems traders holding overnight positions and are trading their own account.

1. Collect all the relevant historical data on the markets and products that you are interested in, as many years as possible, the longer the better.  It is better to have it when you don’t need it than to need it and you don’t have it.  This step can be done parallel with other steps below.  This step would also help you to know more markets and products.

2. Read and know as many trading strategies as possible.  Of course in order to do that you’ll also have to know as many market indicators as possible. Two good books to start is The Encyclopedia of Technical Market Indicators and The Encyclopedia of Trading Strategies.

3. Decide on a few trading strategies (preferably different styles as well e.g. trend-following and counter-trends) that you like better and are comfortable with, even after knowing very well with their strengths and weaknesses.  You can even mix and match or create your own if you want to.  You can forget about finding a secret trading strategy that wins all the time or can catch all the highs and lows, it does not exist.  I am sorry for destroying your dream.

4. Write down the strategies in a step-by-step, clear, detail and non-conflict (such as buy and sell at the same time or holding long and short at the same time) format.  Make sure that even a 16 year old teenager can follow and will execute at the same price (forget about slippage first).  This is the part where you transform  from being trading based on pure chart pattern reading but vague in implementation such as double top, head-and-shoulders, cup-and-handle, support and resistance, doji reversal etc. into something accurate and precise!

5. Program them in MS Excel complete with signal for buy and sell (beside a portion of historical data of a product).  By this stage you have your simple trading system already.  If you can't program it and get precise entry signals on Long or Short, you still don't have a mechanical trading system!

6. Extend the program to include the exact entry prices for Long and Short.  You have a stop-and-reverse system already.

7. Add in the Stop-loss and Exit for Long and for Short complete with their prices.  Now, you have a simple complete trading system already.

8. Start adding in the calculations for the profit and loss for each trade in points and in the traded currency, separating the longs and shorts and you now have a simple complete back-testing system!  It is that easy.

9. Create a summary sheet referencing each calculation that you have and you have a performance summary!

10. Try changing the values used in your indicators as well as stops and exits and record them down then do a comparison and now you are doing an optimization!

11. After you have got the result that you are satisfied, change the first portion of data with another portion of the same product but different period.  This time see if the result is as good. If not, don’t optimize further, you can refine your strategy and start the testing process again or just repeat step 4 & 5 with different strategy then repeat step 7 & 10 & 11 for the 1st portion of data until you get both in-sample and out-sample result that you are satisfied.  Trust me, it is fun and challenging.

12. Verify if you have enough startup capital to trade (see previous posting).  If yes, you can start trading, with strict discipline to stick to your trading system through thick and thin!  You are supposed to do all the thinking, discretionary, artistic and imagination exploration while doing the development and testing.  If not just save until you have more than enough capital or find another startegy or market that meets your capital requirement.

13. Record each trade and monitor your trading result closely.  If the result deviates from your back-testing result, do a Monte Carlo simulation to verify if the deviation is significant.  If it is, stop trading (remember you are the master, not your urge to trade) and go back to the drawing board.  If the deviation is not significant, continue trading and repeat this step again.

*money management can be added once the basic system is done correctly.

There you go, simple guidelines for would be beginners on systematic trading.  I will begin sharing and discussing the individual steps in my future postings.  Some of the good reference books for beginners and intermediate traders would be Quantitative Trading Strategies, The Evaluation and Optimization of Trading Strategies and Trading Systems That Work.  Just enjoy the journey!