Step 1: Get the data
The getSymbols function in quantmod makes this step easy if you can use daily data from Yahoo Finance. There are also "methods" (not in the strict sense) to pull data from other sources (FRED, Google, Oanda, R save files, databases, etc.). You could also use them as a template to write a custom function for a particular vendor you use.

Step 3: Construct your trading rule
Since this trading rule is simple--we're long 100% if the DVI is below 0.5 and short 100% otherwise--it can be written in a single line. More elaborate rules and/or position sizings can be done as well, but require more code (RSI(2) with Position Sizing is an example of more complex position sizing rules). Also notice that the signal vector is lagged, which avoids look-ahead bias.

# create signal: (long (short) if DVI is below (above) 0.5)

# lag so yesterday's signal is applied to today's returns

sig <- Lag(ifelse(dvi$dvi < 0.5, 1, -1))

Step 4: The trading rules/equity curve
As in Damian's example, the code below is a simplified approach that is frictionless and does not account for slippage. The code below takes today's percentage return and multiplies it by yesterday's signal / position size (always +/- 100% in this example). I also subset the system returns to match the results in the Excel file.

# calculate signal-based returns

ret <- ROC(Cl(GSPC))*sig

# subset returns to match data in Excel file

ret <- ret['2009-06-02/2010-09-07']

Step 5: Evaluate strategy performance
Damian mentioned the importance of evaluating your strategy. Fortunately for R users, the PerformanceAnalytics package makes this easy. With a few lines of code we can view the drawdowns, downside risks, and a performance summary.

# use the PerformanceAnalytics package

# install.packages("PerformanceAnalytics")

require(PerformanceAnalytics)

# create table showing drawdown statistics

table.Drawdowns(ret, top=10)

# create table of downside risk estimates

table.DownsideRisk(ret)

# chart equity curve, daily performance, and drawdowns

charts.PerformanceSummary(ret)

That's all there is to backtesting a simple strategy in R. It wasn't that intimidating, was it? Please leave feedback if you're moving your backtesting from Excel to R and there's something you're hung up on or you have an awesome tip you'd like to share.

Here's a succinct version of the code in the above post if you want to be able to copy / paste it all in one block:

Thursday, March 17, 2011

I will be attending Ralph Vince's risk-opportunity analysis workshop in Houston this weekend. I'll be in town Friday-Monday. Drop me a note if you're in the area and would like to meet for coffee / drinks.

Monday, March 14, 2011

Let me start by saying that I’m not an expert in backtesting in Excel – there are a load of very smart bloggers out there that have, as I would say, “mad skillz” at working with Excel including (but not limited to) Michael Stokes over at marketsci.com, Jeff Pietch over at etfprophet.com and the folks (David and Corey) over at cssanalytics.wordpress.com. All of these guys have been gracious enough, over the years, to share with me how to do backtests – so I am indebted to them. And I want to thank Josh here at FOSS Trading as well – because he’s been kind enough to help me in learning how to use R for testing.

With all that in mind, I thought I’d walk through what I consider the four basic steps in producing a backtest in Excel. Note that the core Excel file wasn't created by me - it was created by Jared over at CondorOptions (another must read if you're not following him).

Step 1: Get the data

The first step is to get your market data into Excel. There are two basic approaches to this – the first involves going to Yahoo Finance and downloading historical data directly as CSV and then loading it into Excel. This is nice, but does require a manual update of that data as you go forward – meaning, you’ll need to re-download that historical data and then copy and paste either the entire dataset or a subset to update your strategy.

The second approach is to use code to go grab data automatically from Yahoo Finance. Plenty of people have written VBA for doing just this – I have not written it myself so I don’t feel comfortable republishing the code. A quick search on Google will provide some examples to work with. There are also 3rd party tools that make the job simple – I’d recommend AnalyzerXL as it provides the most flexibility and options.

How you store this data in Excel is up to you – most people I know have a single sheet where they keep all the data, and then have a separate worksheet for the rest of system. For systems with a single instrument (such as the SPY), it’s not a problem to integrate the data and the system, but as the number of instruments goes up, you’ll want to have them on a separate worksheet to minimize scrolling and make it easy to update.

Step 2: Create your indicator

Now that we’ve got the data, we can use that data to construct an indicator or indicators. In this example, Jared constructed the DVI indicator (originally created by David over as CSS Analytics). You’ll see that we used 5 different columns to create the indicator – each one taking part of the calculation. One nice thing about working with Excel is that it really makes you think about how an indicator is constructed. It can be far too simple, these days, to throw down and indicator without understanding how it actually works.

The final indicator column, DVI, is a weighted sum of the DVI magnitude and DVI stretch columns. I’d also note that AnalyzerXL also contains a large number of indicators predefined to make backtesting easier, and there are other add-ons for Excel that provide similar functionality.

Step 3: Construct your trading rule

Now that you have an indicator, you need to construct your trading rules. In this example (calculation is in the “Signal” column), our trading rule is simple – we’re long if DVI is below 0.5 and short if above. Obviously you could have more complex rules – a neutral state where you’re not long or short, or variable position sizing as opposed to just all-in long or short.

Step 4: The trading rules/equity curve

There are many different approaches here, but what you can see in this example is a simple way to do it. Assume a starting cash value of $10,000 and then increment or decrement that by whether or not we are long or short on the close of the prior day, and whether we were correct or not. In function form, we represent this by saying: if long, then multiple the prior day’s equity by the ratio of today’s close to yesterday’s close, otherwise multiple the prior day’s equity by ratio of yesterday’s close to today’s close. We can then, obviously, graph the results. Note also that we’re using cash here, but you could easily do raw percentages in place of a cash value.

What’s missing here can be important for deciding whether to trade or not trade a system. First of all, the results here are frictionless – they assume there is no cost/commission for the trade. In high frequency swing systems like this one, the commissions could have a major impact on the viability of a given strategy.

Second, we don’t have any statistics on the performance of the strategy – just a graph. Generally we want to know stats like CAGR and the Sharpe ratio to compare it with other strategies. We also don’t have monthly or yearly reporting. All of these things can be constructed in Excel with a bit of work – and again, AnalyzerXL provides a large number of reporting options as part of the package.

That's a basic overview of backtesting in Excel - hope that you all find it useful!

The registration for R/Finance 2011--which will take place April 29 and 30 in Chicago--is NOW OPEN!

Building on the success of the two previous conferences in 2009 and 2010, we are expecting more than 250 attendees from around the world representing both industry and academia to join a record 30+ presentations covering all areas of finance with R.

This year we are excited to have longer tutorial sessions and an optional full-day workshop on the Thursday before the conference. In addition, we have worked hard to extend the great networking opportunities on both days with longer breaks and more hallway time between sessions.

New for 2011 is a special conference dinner that is held on Friday evening. Overlooking the river from the famed Chicago Mercantile Exchange, we have designed it to be a great way to continue the conversations from the first day, as well as offering a chance to dine and drink in Chicago style.

Saturday, March 5, 2011

This first post of the Backtesting in Excel and R series will provide some resources to help smooth the transition from the familiarity and comfort of Excel to the potentially strange and intimidating world of R.

I made my voyage from Excel to R more than 5 years ago and learned mostly by trial and error (and reading the R manuals). Most people don't prefer my approach of "keep at it until you figure it out", so I don't have a lot of personal advice to share. My main piece of advice is that the best way to learn R is to use it, so most of the resources below focus on "how-to" do certain things in R.

GUIs

While R for Windows comes with a very basic GUI (I'm not familiar with the R for Mac OS X GUIs), most people will want and benefit from something more elaborate. The GUIs below were taken from the R GUI Projects page (visit the page for more information / alternatives) and I added the newcomer, RStudio: