## Thursday, November 15, 2012

### Excel data analysis 2

I will go over using excel to back-test a very simple mean reversion pattern in this part. It will utilize the If() function.

About the data
DIA (SPDR Dow Jones Industrial Average) will be used for this example. We have the data going from 1/20/98 to 11/14/2012. We will look at the change between daily opening and closing prices, using logged returns:
dX(O,C) = ln(C/O)

Where
dX(O,C) : change in price from open to close
O : Opening Price
C : Closing Price

Excel formula :
*The Count() function gives the number of rows of data we have, in this case 3,734.
and we end up seeing this:

So we can see that the average price move from open to close has been pretty close to 0 where a few more ups days had occurred than down days.

Theory around mean reversion

Since we know that the average Open to Close moves have been about 0, then would an up day follow a down day, and vice versa?

We want the spreadsheet to give each day's dX(O,C) if the previous dX(O,C) was negative, and -dX(O,C) (to similate a short position) if the previous dX(O,C) was positive. Two columns are created for the Longs-> MR(Longs), and Shorts-> MR(Shorts)
*MR stands for Mean Reversion

The If() function get this done.

and we end up seeing this:
We can see that for both Long and Short signals, a slight edge had existed. Whether this would have overcome transaction costs is however uncertain.