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.

Excel data analysis 5.1 (Realized Variance, Future Return Regression)

So in the last post (Excel Data Analysis 5.0) we looked at deriving Robust Realized Variance (RRV), today let's look at if we could use RRV to estimate a bias for future returns on the S&P500 off regression based interpolations.

So moving on with the S&P500 RRV worksheet, next to the 60day (business day) realized variance, we will create a column for the average future 60day log return of the S&P500, "dX.f60",

Here we can see that I took the average of the following 60days of S&P500 log returns. So naturally this column must end 60days prior to the date of our last available data.

Realized variance vs. future average returns regression fit

Next we highlight the columns "RVar(60)", "dX.f60" down to the last value in "dX.f60" and draw a scatter plot. On the plot, we then add a trendline with the highest R-squared (coefficient of determination) value.

The Polynomial Regression had the highest R-squared at 0.0307, it is not statistically significant enough to mean anything. However that doesn't mean this is the end of the road. We can see that the bulk of average daily returns have been increasingly negative about a quarter (60business days) following high realized variance. So what if we analyze only periods of high realized variance?

Here a new column is created for RVV above a certain threshold. The column is named "RVV+", and I chose cell G9 to place the threshold. Next to "RVV+", whenever the realized variance is below the threshold, this column gives a 0. Another new column is created for the following average 60day S&P500 daily returns, "dX.f60+" (matching "RVV+" values). The column "dX.f60+" gives a NA() whenever "RVV+" = 0.

I picked a random point in the realized variance range, 800 as a threshold for this example. Now, let's apply regression between "RVV+" and "dX.f60+",

It looks like a much better fit from the get go. The linear regression gave an R-squared of 0.7373, which is light years better than the earlier regression involving lower realized variance. This crude model could be extended, refined to estimate future index returns or develop risk management tactics, when the realized variance is relatively high.

Relevant reading is important (not just for financial trading)

1/4 of Americans did not read a single book  in 2007, with active readers in decline, according to The Washington Post. This is good, it implies an increasingly lazy, less productive population of competition against those who constantly read, learn, and improve to remain the best of industries.

Learning by doing only?

Sure I've also learned a whole lot from actual trading, too. While technically, I could have learned everything I needed to trade profitably without the reading; it would have taken a significantly longer period. All the additional reading has seriously helped shorten my learning curve, and made the learning stage much less expensive than they could have been.

Personal habit

I spend at least 30minutes each day going through academic research literature (Google Scholar), new books (Amazon), or exchange tutorial literature. It's with this wealth of information, that one become capable of recognizing money making opportunities when they arise in financial economics. This applies to other industries as well, naturally.