The Excel Platform

Some years ago I had bought this options trading system where I had to manually calculate the signal for the next day. After a while it became tedious and decided to put the formula on Excel. I had so much fun doing this that I got out my back copies of the magazine "Technical Analysis of Stocks and Commodities” and looked for indicators with the Excel formulas and started working it on Excel. Indicators such as Bandpass indicator, Polarized Fractal Efficiency, there was an article on how to create sophisticated indicators using ideas from Physics. I was very excited about this and started working on an “ultimate indicators”, combining the various indicators and using conditional formatting on Excel to flash buy/sell signals.

I have also bought various investment books and working papers over the years. These were mostly on technical topics, like Chaos Theory, Neural Networks, Game Theory, GARCH, etc. Most of the time I didn't really had the technical know-how but was interested and managed to get some ideas. The most useful book for me was "The New Technical Trader" by Tushar Chande and Stanley Kroll which have lots of Excel formulas.

In the end, I decided that I wanted to create my own stuff and started dropping all the other indicators. My background came in handy. I had studied Electrical and Electronic Engineering before I dropped out after a year to study Economics. In my Economics course I had gone for electives such as Econometrics and Mathematical Model. It was time to put it to use. Combining theory with real world applications I set upon developing my own trading model, using mostly my ideas. I called it Logical Reasoning, which is basically using the logical functions in Excel and to model the market.

I used to create some sophisticated worksheets back then and remembered use to run out of columns when Excel stop at IV. Sometimes I didn't had the computer power to continue with a project. I must have developed over 100 models over the years. One of my favourite one was where I had to define a quantitative pattern and then search historical data for the given pattern. It returns the percentage of the match and indicates what the market did in the next 1-3 days for each of the day in the history. For example I can sort and filter for 90% and above and if majority shows an up day tomorrow, I can then look at the current indicators to see if it supports the result. There were some interesting result such as seeing a particular day of the month in history matching the search day and month. Still work in progress as it is not very reliable.

Getting data into excel is another challenge. I was analysing Forex data but noticed chunks of data were missing in the historical data. For the type of quantitative model I was developing data integrity is paramount. I decided to switch to Indices and Stocks with data from Yahoo.

 I get to see many data vendors advertising streaming quotes in Excel but I don't know how they expect you to use it. I am assuming most who use Excel for trading have some kind of trading indicator, where you require historical prices and today's prices to be able to see your indicator live. When you are trading there is no time for cutting and pasting data. You want something fast and something you can use with ease.

 I found eSignal OnDemand Qlink to be the best for this. The quotes are on fixed cells (rolling basis), automatically merging the historical data with today's prices. All you have to do is change the ticker and up comes the quotes. I was on the delayed data plan which wasn't very convenient as I can't afford the real time data plan for now. With the real time data plan I should be able to spot the opportunities quickly. I am sure it will be good for trading weekly options on stocks.

The next step will then be to automatically route the trade to the broker. Mexcel Trader does this for Forex. It will be nice to see something similar and affordable for stocks and options. I will have to look into Interactive Brokers TWS and see how that can work with my model.

I enjoy using Excel for my trading as it gives me the freedom to be innovative. Over the years I have developed lots of know-how and I am quite comfortable using it. I have plans to add a real time data plan in the near future which will bring the model to live. When the model is live I should be able to spot more opportunities and act on them quickly.

Project SPY options

One of my goals this year is to achieve consistency with my trading. It has always been boom bust, stop and go. I thought I was on my way to achieving that in March when my account was up 1830% ytd, but things started going downhill. It was mostly due to frustration. I was more or less trading blindly as I had a full time job. Sometimes at lunch time I want to place a trade but break is over and in order not to miss the trade, I just place the trade at the prevailing price, usually not the best price. Sometimes I can't protect my position and know that I making losses but can't do anything while I am back at my desk. By the way I was trading S&P500 index with a spreadbetting company.

One problem I had with spreadbetting was it follows both the spot and the futures market. My model only follows the spot market.Once the US market close at 9:00pm till it opens at 2:30pm (UK time), I am just a spectator. Sometimes it does works to your advantage as you can get in good price. What I find quite irritating about spreadbetting is sometimes when you place an order (limit or stop) and this get triggered but when you check the high or low prices the price never reach that level. This make a mockery out of my model. So the bid or ask did touch the price but I am not there 24 hours to verify that. I don't know how they can justify that. There are some US brokers that allow you to place limit or stop order but the order is only triggered when it registered as the last price. This is one of the reason why I find it reluctant to use stops with the spreadbetters.  

Anyway, as the frustration mounted, I knew lessons had to be learned. At least I am happy that the model is working, but still need more work on the psychology of trading. I have also decided to trade with a real broker and I am giving the SPY options a go. I think this would help. I will be trading the SPY options at least until a time when I am rich enough to quit my job, then I can add more tickers. I will also be comfortable using risk management. Things like contingency orders could be very useful. It is not going to be easy but I am devoting the rest of the year to achieving consistency. I have upgraded my options strategy model which now have heatmap to spot the best options. Hopefully it will work!