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. Maurice says:

    Thanks very much!
    Just returned from Holiday - will look at it asap !
    greetings, Maurice

  2. Maurice says:

    Just had a chance to take a look at the beta version stockbacktest. In the example data (goog) it seems to show a Sell to Open before a Buy to Open was closed.

    Furthermore, is it possible to enter an inputparameter in which a constant amount of traded shares can be given?

    An endversion of this product could be very marketable, ever considered this?

    thanks very much so far for your great efforts!,
    Maurice

  3. Thanks for the input. I think I have fixed the problem and the new version has been uploaded. I have also added the constant amount of shares option.

    Your continued input is appreciated as it often takes more time to properly test than to program.

  4. Maurice says:

    In my view, 2 additions would be needed to create an endproduct, first, as already incorporated in the futures backtest, a full detailed list of all trades after backtest would be an essential feature of the application.
    Secondly, is it possible to designate some cells to dynamically show if an open buy or open sell position is currently open, and also 2 cells to show last buyprice and last sellprice? This would facilitate adding trailing stop exiting strategies based on last trading price.
    Based on my very limited vba knowledge, i would guess this would only work if vba pushes the data al the way trough one row (row 50?) and calculation of exit point is based on a calculation only on that particular row. But ofcourse you are the expert! Thanks - and greetings from a cloudy Amsterdam.

  5. Maurice says:

    clarification to above:

    Secondly, is it possible to designate some cells to dynamically show if an open buy or open sell position is currently open, and also 2 cells to show last openbuy price and last opensell price?

  6. "a full detailed list of all trades after backtest would be an essential feature of the application."
    All trades are already listed at the end of (columns AS and AT) each backtest and also plotted on chart.

    "is it possible to designate some cells to dynamically show if an open buy or open sell position is currently open, and also 2 cells to show last buyprice and last sellprice?"
    As the code runs from top to bottom, the last open or buy price is always displayed (during code execution) in column AT or AU. However trailing stops are calculated based on the last HIGH price and could be handled in the indicator area (If current exit price is less than the high minus the trailing amount then exit).

    If you still want to always show last price in a single cell that gets overwritten with each new entry add the following line (change cell B11 to whatever you want)
    Workbooks("stockbacktest.xls").Worksheets("settings").Range("B11") = PR
    underneath these lines
    .Cells(row, 45) = "Buy to Open"
    .Cells(row, 46) = Sh
    .Cells(row, 47) = PR

    also put the same line under these lines>
    .Cells(row, 45) = "Sell to Open"
    .Cells(row, 46) = Sh
    .Cells(row, 48) = PR
  7. Maurice says:

    Indeed, a detailled tradelist is already incorporated,
    my apologies for incorrectly pointing this out.

    I can't figure out were to put the code though - getting errors.

    My idea is this; for my exits i want to check whether an open buy or open sell is now open.
    When i have an open buy open i want to exit either trailing or when current price is let's say 1% lower then buyprice. - and i want the trailing stop loss to kick in after current price is at least 1% higher then buyprice.

    In order to do this i figure i need designated cells to dynamically show if an open buy or open sell position is currently open, and also to show last open buyprice and last open sellprice

    I realise i'm maybe asking a lot but i hope this feature can be added.

    Thanks very much for all your efforts so far!
    Maurice

  8. Maurice says:

    also- in the example, the first trade(open sell -buy close) results in a loss but the amount is added to the account.

  9. Fortunately 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 the vba will ignore any formula generated exit signal. This allows users to keep entry exit formulas simple.

    I have added a box in column I on the data sheet that contains the requested info for trailing stops based on last price and the new file is now uploaded. also the P/L is fixed when exiting a short position.

  10. Maurice says:

    Thanks a lot!
    I found one more hick-up. The last backtested line of data (current date) is closed with a 'sell to close'even if no position is open.

  11. Maurice says:

    Not in the current example, but tetsed it with my own indicators

  12. ah, good catch. I have uploaded the fixed version. since you have your indicators entered you may find it easier to fix your current version rather than download the new and re-enter your formulas.

    To fix the last line exiting when there is no open position. Open the file, Press Alt-F11 to open the VBA console. Find Module4. under "btest" add the Bold text to the following lines.

    ElseIf .Cells(row, 38) = "BUY" And AC = 3 Or AC = 3 And LR - 1 = row Then
    ElseIf .Cells(row, 37) = "SELL" And AC = 1 Or LR - 1 = row And AC = 1 Then

  13. Maurice says:

    Thanks, although something fishy is now happening with the datadownload, when i expand the data it does not seem to work anymore

  14. I have not been able to duplicate this problem. It's possible the problem was temporary from the data source, Yahoo.com. What symbol and dates are used when the problem happens?

  15. Maurice says:

    That's odd, i still have this problem. I downloaded stockbacktest again, altered startdat to 12/01/05 (still with GOOG). I get 'runtime error 9 - subscript out of range.

  16. Normally this error would occur if you were to change the name of either the workbook itself or a sheet but it sounds like you are getting this error on an unaltered version. If that is the case since I am unable to duplicate the error with goog and start date of 12/01/05, when the error occurs you should see an option to debug. Press debug and post the exact line highlighted in yellow.

  17. Maurice says:

    Seems ok now that i run it again - can't explain the hick-up. Everything seems to work fine - will be testing more intensly. Great product!!

  18. Hi Admin,

    do you have a messenger to add you and exchange ideas about the sheet?

    Great product

    Thanks,

  19. I May add a forum at some point so users could discuss/share/modify my files but for now rather than IM, feel free to submit bugs/feature requests etc via comments. Thanks!

  20. g0ne,

    Long Entry = Buy to Open a New Long Position
    Short Entry = Sell to Open a New Short Position
    Long Exit = Sell to Close a previously opened long postion
    Short Exit = Buy to Close(cover) a previously opened short postion

  21. Maurice says:

    I have run a few dozen backtests so far and the whole system works like a charm except for the added box which contains info about the last position opened.
    The way i tested this: added a colomn to check on last position, if current low < last buyprice - 2* moving av of ATR then mark 1.
    The result after a run is all are marked 1.

    =IF(AND(I$4="Buy to Open",D149<(I$6*(2*V149))),1,0)

    Maybe i am wrong - hope you have time to check it..
    from a sunny Amsterdam,
    thanks! Maurice

  22. Maurice,

    Your formula (which references the dynamically updating last position info), may not have been "1" during the backtest. In other words you cannot review those formulas (that use I6 and I4 ) after running backtest and expect to see the result used during the backtest since I6 and I4 will have changed.

    If the results do not seem correct, check your formulas and indicators for accuracy, make sure calculation is set to automatic in tools>Options>calculation. If things still don't seem right click on Tools>Macros> and then highlight "btest". Next press the "step into" button. put the Visual basic window in front of the excel sheet and make it smaller so you can see the cells behind it changing. Now Press F8 (or hold it down) and take note of the changing formula results. They should be changing. If you see a condition where you formulas should be showing a 1 (or 0) and they are not you can look at your formulas to see why they are not working right.

    Let me know if this helps.

  23. Maurice says:

    Excel trader - i can't discover what causes the problem, stepping into the vba and running it with F8 i did see the formulas changing but i think i'm always a step behind.
    When there is an sell to open, i want sellprice to be day low,
    When there is an sell to close, i want it to be day high
    My formula is:
    =IF(I$4="Sell to Open",D38,C38)

  24. Exceltrader says:

    If I am understanding you correctly then it is not designed to work this way. However you can accomplish what you have described with your formulas.

    "When there is an sell to close, i want it to be day high"

    the "sell to open" in i4 is simply reporting back the last signal generated (and should only be used for the next signal), so the next order in that case would be "Buy to cover" and the price can be set for "day low" over in the formula (columns AM and AN).

    The box is designed mostly for creating exit signals based on last entry price. For example, an exit formula like.... If last signal was "Buy to Open" and current price is less than 95% of Entry price then exit/Sell to Close. etc etc.

  25. Maurice says:

    THe box indeed works fine as a way to create exit signals based on last entry price (as was intended and requested), however to use it to create entry/exit price would be an additional usefull feature.
    Thing is i tried it the way you described it and used last postion opened as a proxy for determining entry/exitprice, but with only last postion opened as information, instead of last trade, i fail to cover all options.

    When last postion opened was a buy to entry, then sellprice is day high, otherwise (sell to open) sellprice is day low.

    Now, if right after a sell to close, which would correctly use day high an open sell is created, which should be closed on day high, this does not happen as last opened postion still is buy to open.

    This problem would be solved i figure, if the box 'last entry type' would be replaced by last trade. Is it possible to adjust this?

  26. Exceltrader says:

    I have updated the stockbacktest.xls to show last order type (entry and exits) and last price.

  27. Maurice says:

    Thanks very much!

  28. Hi , i'm trying to learn from your hlpful work. But i cannot go through may be because i am bigger with vba.
    when i arrived to
    Set ws = Workbooks("stockbacktest.xls").Worksheets("Chart")
    it makes erreur 1004 can you help me
    thanks

  29. Exceltrader says:

    Abdoulaye,
    It sounds like the file was renamed. It needs to be stockbacktest.xls.

  30. I tried it with OpenOffice as I don't have excel. It loads up but wont download stock data, just displays into June. Has anyone tried this with OpenOffice?
    Thanks

  31. Exceltrader says:

    Hi, Although Open Office can run some macros it is not 100% compatible with VBA. Since I am not a OO user, my files are not tested to run with Open office and would need to be converted or rewritten to be 100% compatible with Open Office.

  32. Tried the program and have a couple of questions.
    1.) If I open the program as you had it, then hit the down load data. Then hit the back test button I get a value of 6884.31. SHould I be getting the 7144.62 that you already had?
    2.) If I open the program and change the symbol to say "SWHC" hit hte down load and then backtest. I get a result of 7180.3. The result doesn't matter, but if I try to view the graph the bottom format is all screwed up? Any suggestions or am I doing something wrong?
    Looking forward to using this
    Sean

  33. Exceltrader says:

    Hi, sean, I think you must be using excel 2000?. I recently realized that the method I used to autoscale the chart only works for 2003 and above.

    You should be be getting a different value like you are because you are downloading newer data.

    If you can confirm your excel version, I will look into making a change. Thanks

  34. Hi Excel trader,
    Thanks for the quick responce. Yes I am using excell 2000. Hope its not to big of a deal to fix. Program looks real cool besides that
    Thansk again Sean

  35. Exceltrader says:

    The file (Stocks – Backtest & Data Downloader) has now been updated. It is now tested and completely compatible with Excel 2000.

  36. Looks great thanks for the help. I'll start playing with it to see what else I can find
    Sean

  37. Philipp says:

    Hi there,
    cool tool howevet i'm getting an error with excel 2007: (run time error '13')...any suggestions?
    thanks
    phil

  38. ExcelTrader says:

    Philipp, Make sure that the file name is not changed and the file type is xls. (not .xlsm). Also make sure that after downloading data, you have received valid data from yahoo. If the data is ok, run the backtest.

  39. Runtime Error 1004

    in module 2
    error is
    .Range("ai51:an65536").ClearContents

    where is wrong?
    please help....

  40. ExcelTrader says:

    This error would occur if the worksheet or workbook has been renamed. Please use the workbook name and worksheet names in the original file.

  41. My chart date label begins with 1 Jan 1900 for GOOG and all other stocks I enter. The actual chart displays correctly. What do I need to do to correct the date label display?

    VERY nice workbook.

    Thank you

  42. Hi ExcelTrader,

    great product, congratulations!

    Is there any particular reason to hardcode file names in your code rather than use ThisWorkBook? People seem to stumble upon errors due to this (so did I).

  43. ExcelTrader says:

    There is no good reason other than when I first used vba programs to autotrade I was paranoid of there being some quirky vba glitch that would take data from the wrong workbook. In reality, thisworkbook works fine and there is no glitch. I also left some files that way to discourage people from renaming and then reposting the files. The code can be easily modified with Find/replace.

  44. Just wonder how to make the stockbacktest work if the downloaded historical data (and hence the indicators) are in descending order, which is my preferred format.

  45. ExcelTrader says:

    It would require an almost total rewrite of the code.

  46. Not sure what I'm missing, but I don't see a "download" link... was the model taken down?

  47. ExcelTrader says:

    Nate, the 11th line down, click the word "stockbacktest"

  48. Very nice example. Great starting point to make your own indicators.

    One drawback, I corrected quickly is that it doesn't allow other filenames rather the stockbacktest.xls. So to keep a copies of different filed versions, suggest to use string containing the filename instead of stockbacktest.xls" So modify in the three VBA codes the following:

    old:
    With Workbooks("stockbacktest.xls").Worksheets("settings")

    new:
    Dim workbookname As String

    workbookname = ThisWorkbook.Name
    With Workbooks(workbookname).Worksheets("settings")

    Thanks, Han

  49. Deepankar Mitra says:

    Nice stuff folks, helps a lot to create own indicators

  50. color datapoint in chart based on vba macro
    i have created a chart where X axis holds values in column A (showing dates) and Y axis holds values in column B (showing prices). the chart has been plotting on a separate sheet.

    I am trying to write a excel vba code where for specific values of Y based on if/than statement in column C, the corresponding datapoint in the chart is resized/colored (essentially highlighted on chart).

    I am struggling here a bit.

    I appreciate your input.

    thanks.

    nqh

Speak Your Mind