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.