Stocks – Backtest & Data Downloader

*stockbacktest.xls updated Sept 8, 2012

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.trailing
      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. stockbtchoose1
      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:

stocksimch

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).

[*added Sept 8 2012] Your Indicator formulas that are in columns J-AE on the Data worksheet will be autofilled from the row number that you enter in cell B14 on the Settings worksheet.

For Example, if your indicator needs 50 days of data, then you must enter the last row number where your indicator is valid. In this example it would be row 51 (add a row to account for headings).

Change Log

Comments

  1. Hi,
    my compliments to you for this great file! It's a very good point to start.
    I'm reading the code but I can't understand the exact meaning of "AC=0" (or "AC=1", etc) in the "btest" subroutine.
    Could you explain us?
    Thank you very much!

    Andrea

  2. Murray Cooper says:

    Started using this model today and have made a few cosmetic changes.
    This will form the basis of a new testing module for a program in current development to remove my "emotive override impulse button", which will include acknowledgement to XL Trader and is not intended for sale.
    These are my changes. Happy to email book to anyone with genuine interest.
    Note Autofill requirement on "Settings B14" is now updated with formula =data!J2 + 1

    Fomulae in Cols J and K are now dynamic
    Simply change J2 and K2 and everything else will update
    Added a few extra test results (Col I) for comparison and built a table on Summaries for review
    Amended formulae to include todays EOD results so trading result will be executed tomorrow rather than day after
    File can now be saved as an xlsm type ... Hat tip to Hans

    Col I stuff:

    Total Profit
    $1,720.49
    Total Days
    1583
    Total Longs
    62
    Best Profit
    $204.50
    Total Shorts
    61
    Total Long Profits
    $1,203.50
    Total Short Profits
    $516.99
    Worst Loss
    -$258.50
    Total Wins
    39
    Total Losses
    21
    W / L Ratio
    1.857
    Profit / Day
    $1.09 Basis 50 units of stock "xyz.ax"

  3. Andrew Bannerman says:

    Hello

    Wondering if someone can help here:

    I ran the back test and was given this VBA error... its located at:

    .AutoFill .Resize(LR - LastGoodRow)

    the last row in my data is row: 4544

    So i entered this into the box to the AutoFill Row Number: 4544

    What do you think it is?

    Thanks
    Andrew

  4. Andrew Bannerman says:

    Do I need to manually enter the buy and sell price for this to work? Or when I run 'backtest' this is automatically populated?

Speak Your Mind