Backtesting & Automating with or through MS Excel

Discussion in 'Strategy Building' started by CPTrader, Apr 30, 2004.

  1. How can you generate trading signals in MS Excel and then perform the backtesting in MS Excel and/or output/export the trading signals to another backtesting system for portfolio management level backtesting.

    In essence, the aim is to have Excel export the trade signals to another software that will create a portfolio (i.e. trade signals across various markets) and apply risk management rules and any other position management rules (exits, stops, etc) to the trade signals/portfolio and generate for you a final historical simulation.

    Can this be done? If so , how and with what software?

    Has any one done this?

    Finally, how can you FULLY AUTOMATE these trading signals. Ideally, I would want a way, where the trading signals are automatically converted to orders, sent to the exchanges and trade positions managed until exit. I would presume there should be some multiple level of redundacies and fail-safe mechanisms in this automation. Personally, I believe that systematic trading should by default (to ensure proper execution) be fully automated. I understand that IKOS, a hedge fund group in the UK has 100% or close to 100% automation of their systems.

    Thank you much.
  2. Dustin


    You need access to your brokers API if they have one.
  3. I know that... but how do I get to that stage.... see my questions in my earlier post.
  4. WarEagle

    WarEagle Moderator

    Is Excel already calculating your trade signals? I have used Excel for automation quite a bit, but much of that depended on the complexity of the system. For me, if the system was based on simple information like the bid or ask I have used cases where I used more complex calculations like indicators I generated the signal in something like TradeStation (but there are many others) and then imported the output to Excel where I used VBA to interface with my broker's API.

    If it is very complex, Excel may not be the tool for may want to step up to a higher programming language and write the code from scratch. So far for me, VBA has been sufficient, the only "multiple level of redundacies and fail-safe mechanisms" that I use are my eyes and my butt in my seat. No way I would leave Excel or TradeStation or even my broker's software running unattended.

    Another tool that might help you is the free automation software from Hypertrader that will interface between several charting packages and brokers. See this thread.
  5. cashonly

    cashonly Bright Trading, LLC

    It can be done, but based on what you're telling us, the software you need is what you have... Excel. You just need to do the VBA programming in it. You can get a service where you can download data into a file and then read it into your Excel SS. Then Excel can process it.

    But you didn't mention a timeframe or market. If you're talking about tick data on thousands of stocks, backtesting is going to take a while and Excel might not have the room to hold it or you'll have to use multiple workbooks to get around the limitations.

    Regarding automating the trading, that's not a problem. I already have a number of automated trading apps written in Excel. I just use the RediPlus API for quotes and data and it works fine. So if you use RediPlus, you should be able to do this also. I'm not familiar with other apps, but I know a number of them also allow this.