Monday, December 24, 2012

Excel data analysis 6.0 (Buy Low/Sell High simlation)

If we bought a unit of an index at the end of each down day, and sold on an up day, how would it do?
In this post we look at the raw EV (statistically expected value) of this idea, with excel, in a theoretical trade where we toss all risk management principles out the window.

Buy Low/Sell High -> short gamma

This is an implicitly short gamma trade, since position accumulate with each unfavorable move, increasing risk. Therefore the payoff is concave, and requires significant effort in risk management to implement.

Historical data

We will look at IWM (iShares Russell 2000 Index) daily prices for the period 5/26/00 - 12/21/12. So after sorting the adjusted closing prices, we work out the close to close log returns.

Sample stats:
















Trade signals
We can create the next column for buy/sell signals with respect to the latest dIWM. Here I used a threshold of 0.09%, median dIWM of the past 12 years; i.e. if the latest dIWM is above 0.09%, we sell a unit of IWM, and vice versa, where buy signal = 1, sell signal = -1.


Buy/Sell price simulations
This is pretty straight forward, if the signal = 1,  "Buys" column gets the latest price, if not, blank. Same for the "Sells" column.


From the average prices, we can see that this concept had derived an average of $0.72/share with each round trip, before transaction costs. OK, so it has been +EV.

What about the risks of position accumulation?

Position simulation

I created a column "Position", and it's simply an accumulation of the signals.





We can graph it against the date:

So from this we can see that some trades require more work around risk management to implement in a practical manner.

0 Reflections: