backtesting using Excel

T

truetrend

After 5 years of development, I am currently running live forward tests for my new swing trading system (over the past 2+ months), whose entries cannot be hard coded. There needs to be a human eye as the entry criteria compares 2 days data which can be anywhere from a few days to a year apart.

That said, the exit rules are fairly straight forward, for the most part dealing with straight percentages based on the entry or OHLC, besides the initial stop, recalculated each day at the close.

Is there a way a spread sheet which has the following 5 columns completed (symbol, entry date, entry price, size and initial stop) could read data from Yahoo or Worden's Stockfinder (or some other data source) and complete the spread sheet, posting the exits on the spread sheet or in a readable file (and how difficult would said coding be)? I would like to run it for the previous year and possible as far back as May 2011. Thanks for any advice.
 
actually it's not too difficult, from what you said.. there are a lot of exist helps that will hook up prices from yahoo/google to spreadsheet. the hard part is actually having your trading strategy implemented in VBA. and i'm sure it's not something you would want to share with whomever is developing for you.

don't forget QA and backtesting as well.
 
Thanks for the feedback. That said, I have little to no VBA coding experience. The exit strategy is fairly straight forward (although there are 9-10 OCO orders, 1 canceling/replacing another and usually we exit in 2 phases) in that it uses %s (for the most part).

EXAMPLES - Stop x% below yesterdays LOD OCO y% above entry. Once the position is up x%, move stop to +y% above entry OCO exit 1/2 at +x% above entry, once we are within x% of the 1st target, move stop to 1% below YLOD, etc (although these are basically generalizations the rules give you a basic idea of how it is built).

Do you know of a sit that can walk me through the coding of it? This is the 1st attempt of coding without my former partner. Thanks.
 
ok, lets break it down. which part do you need help the most? implementing your algorithm or getting the data from yahoo/google?

here is a excel plugin that will help, it's free! yay
http://code.google.com/p/finance-data-to-excel/

regarding writing vba in general. while it would be easy to just ask or hire help directly, but i find it much more rewarding, long term, to learn it one self.

here is a decent step by step tutorial
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

and you can just google for "vba tutorial".

i would focus on understanding the following topics:
1. how to create and execute a macro
2. whats the difference between sub and function (hint one is way more flexible)
3. how to traverse a column of data
4. how to write calculated value into any cell

i would totally ignore the following topics in tutorials:
1. formatting
2. pivotiing
3. graph

these are way more advance than you need. just focus on the basics. to get something functional.

good luck.
 
Thanks guys. My head is spinning lol.

I have been using this strategy for 10+ years in bits and pieces but now I need to segregate it and run a backtest so I can tweak the exit strategy. I have been toying around with eliminating the 2nd stop/target and using a trailing ATR stop which needs to be tested. Additionally, since I am usually pretty close to market neutral with 20+ positions (approx 10 long and 10 short on average), it made sense to test it as a Beta market neutral strategy.

So its more that just backtesting something. I want to perfect this project from a lot of ideas I have been toying around with for years.

I will look at the sites you both sent tomorrow and let you know what I think. Thanks for everything.

JJ
 
It's easy to download a premade excel spreadsheet that automaticly or based on button downloads historical data or 15 min delayed intraday data from yahoo.

I found multiple spreadsheets that did this when I googled.
 
You must log in or register to reply here.
Back
Top