Stocks - Backtest & Data Downloader

http://exceltrader.net/stockbacktestxls-stock-backtest-and-data-downloader/

Stocks - Backtest & Data Downloader

*file updated Sept 12, 2009 – Change Log

Stockbacktest.xls is an end of day OHLC backtesting Excel/VBA program.

No VBA knowledge is needed to use this file however a basic understanding of excel formulas is needed.

The features are as follows:

• Downloads data for the entered symbol. Alternatively you can insert your own OHLC data.
• Four separate Conditions: Buy to open, Sell to Close, Sell to Open (short entry), Buy to Close (cover short).
• After running “backtest” the chart will display a green dot to indicate a buy and a red dot to indicate a sell as shown below.
• No passwords to view/edit VBA
• completely free but donations appreciated
  1. Download and open stockbacktest.
  2. Columns I – AG (on data sheet) are reserved for you to enter in your own indicators. As an example, I have moving average indicators in Columns I and J.
  3. Columns AI – AL(on data sheet) are reserved for you to enter in your Entry/Exit Signals based on the indicators you have entered in Columns I – AG. Don’t worry about consecutive signals. It will only take an entry signal if there is no position already open. You never have to check for open orders. You can enter dumb formulas that generate exit signals for example when there is not an open position and the VBA will ignore them. This allows users to keep entry exit formulas simple.
  4. The box show below is on the data sheet. It always displays the last opened position and price into a static cell. This allows the use of formula based trailing stops based on the entry price. If you do not want to use trailing stops then you can just ignore this box.
  5. As an example, I have a long entry when fast moving average is > the slow. =IF(I13>J13,”BUY”,0). You will want to keep the “BUY”,0 portion of this formula but change the I13>J13 to work with your indicators.
  6. Columns AM– AN (on data sheet) is for the price to use when a BUY or SELL is triggered. (currently you need to account for commissions here) For example you may want to buy on the open, Buy on the close, or use a midpoint. As an example I have both Buy and Sell prices linked to the Close.
  7. Enter Stock Symbol, Start Date, End Date, Starting Account Balance and Percent of account balance where indicated on the “Settings” tab.
  8. Choose either a set number or shares per trade or a percent of account balance. Choosing % of account balance allows compounding.
  9. Press “Download Data” to get the Symbol’s Daily Open High Low Close data. (or import your own data into columns A-F on “Data” sheet.

  

  Instructions are below:

  

  The results are shown in two ways:

  Cell B8 on Settings page will show the final balance.

  The chart on the Chart Sheet will display all stock and volume data with buys and sell plotted in red and green.

  *There is one quirk that I will improve at some point. Bring down your indicators and signals to at least row 50 (VBA will bring it down the rest of the way) (Columns I-AN).

  Change Log