Added new file for Live trading with Interactive Brokers

In addition to the free files located under “Instructions and Downloads”, there is now a new file under “Products and Services” for live trading via Interactive Brokers. This Excel VBA based file is an all in one solution for testing, trading and charting.

This is very similar to a version I have been using for my personal trading for years. However this is a much improved version so I am pretty excited to have it completed and am in the process of converting my personal system over to this version. I hope that others find if useful.

The Youtube video below shows both live trading mode and backtesting mode.




Excel still slow with SP2, despite claims of improved charting.

I have high hopes for performance improvements each time MSFT releases an new version or service pack.  This time the claim with Office 2007 service pack 2 that had me curious was:

Improves the charting mechanism in Excel 2007. This includes better parity with Office 2003, improved robustness, and targeted performance improvement”

Benchmark.xls is a file I created to keep track of Excel’s performance in various O/S and versions. Benchmark.xls makes heavy use of Excel’s charting capabilities and manipulates data in a way typical for handling live market data/trading.

With SP2, Excel still took roughly two and a half hours to complete what takes Office 2003 five minutes.

Office 2003       : Backtest results: 00:04:56
Office 2007 SP1: Backtest results: 02:29:48
Office 2007 SP2: Backtest results: 02:35:38


See Benchmark.xls for more detailed results or to download benchmark.xls.


The following excel file is an example of how to calculate the MACD indicator. This example shows the 5, 13, 6, setting. the MACD is in column N.  Over time I may add more excel indicator examples. As I do they will be most easily found in the right sidebar under the “How to” – “Indicators” category.

MACD example in excel.

Excel question

This post answers the following question.

“I have recently been trying to chart historical data in Excel in a bar chart format and found the “canned” graph allows only for high, low, and close, but not the open.  Do you have a simple way to add the open tick to a bar chart in Excel?”

There are four types of stock charts included in excel.


High Low Close

Open High Low Close

Volume High Low Close

Volume Open High Low Close

Select the Open High Low Close option.

If you want to also plot volume which I suspect may be the case, I always just used the Open High Low close chart then add in the volume as a new bar chart on the stock chart. To do this:

first setup your stock chart with open high low close. It should look something like this.



click on the outer edge of the chart so that it is highlighted, click on “chart” from the menu bar and select “add data”. Select the volume column as your data.


Now your chart will look messed up because volume is plotted on the primary axis (along with price).

To change this just right click on the volume bar series (on the chart itself) and select “format data series”. next click on the “axis tab” and change to “secondary axis”. Next you may want to change the secondary axis scale so that volume stays way down below price. Once that is done your chart should look something like this.