Tuesday, April 8, 2008

Regression outlier trading via Excel

Now that we know how to import data into Excel, let me explain a simple technique for an edge the public crowd largely remains clueless of. I used to apply it myself but not today as I now utilize strategies that require less patience, and hence release of this information does not conflict with my own trading interests.

Basic Concept

This analysis basically exploits statistical outliers. Exponential growth does not sustain (but decay does), both in nature and financial markets. Fundamentally, exponential growth of stock prices occur when the public crowd drifts off to fantasy land and keeps buying while institutional traders await to take profit.

To exploit this phenomenon, you search for growing stock(s) and make downside bets as prices surpass exponential levels. A second degree polynomial regression analysis makes this possible. Profit taking, or short-covering, occurs when price returns to the regression line. See below for steps.


1) I have decided to use data for SPY from 2001 to today, basically from the beginning of the last bull market.

2) With daily “High” prices studied and highlighted, click on the “Chart Wizard” button. (Because the strategy calls for downside bets, the optimal entry levels would lie in the historical-high statistics.)

3) Choose XY (Scatter Graph)

4) Keep clicking “next” until you see “As New Sheet” or “As object in”, and choose As New Sheet, and name it whatever, in this case “SPY Regression”, and press “Finish”.

5) Once the chart becomes opened, click on “Tools”, then “Add Trendline”, and pick Polynomial, order “2”, and then hit “OK”.

6) Now the chart holds the regression line nicely for the historical data, woohoo!

Applying This Edge

  • The regression line must curve upwards
  • Take short position(s) as price jumps up way above the regression line
  • Cover position(s) as price reverts back to regression line

As I’ve stressed in the past, do NOT apply a hard stop loss. That would seriously hurt performance. Remember that this strategy only works on the downside, not upside. If you do not understand why betting on failure has a higher expectancy, read this post.

Good trading.

12 Reflections:

alexandroid said...

Interesting idea. =)

Do I understand correctly that a) this works mostly for bull markets only and b) the best way to apply this is to plot a chart on some time window and not on the entire available data?

Rocko Chen said...

Yes this occurs more often in bull markets, but not always. The time-step you apply vary, but when I used it I began each period at a recent "bottom".

(You can get more accurate entries and exits using a professional statistical software like Minitab, but it takes a lot more work than Excel)

alexandroid said...

>> You can get more accurate entries and exits using a professional statistical software like Minitab, but it takes a lot more work than Excel

Excel should be enough -- LINEST + x^2 and x as inputs will do the same approximation. =)

Rocko Chen said...

Well, by accuracy of entries and exits, I meant with standard deviations drawn from the mean and Excel does not hold these functions.


Lot of global tensions is going on at this time. Japan is expected to pull out its money from the global market as they want to revamp their country now. In current scenario anything can happen in the Share market Investors are advised not to panic and stay invested only safe traders and investors should exit their long positions on every high and one can use every decline as an opportunity to enter market again.

India Advisory Stock Research said...

hi ,
First Thanks for post this nice and useful blog. As many events are about to happen soon. So we just want to share few things with all visitors as it might be helpful for everyone.

NSE and BSE are trading in range and we are expecting breakout in the market after budget. One should buy quality stocks at every decline and should exit long positions at every rise.

stock tips said...

great post for stock market investors and i suggest opt2wealth for stock tips, wealth management and wealth creation in different other ways
stock tips

share tips said...

Hello There. I found your blog using msn. This is a really well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. Ill definitely return.

share tips


Global recession fear is once again here and its impact can be felt in almost all stock exchanges. Mostly all global market are under correction phase however we strongly suggest investors not to invest money in NSE and BSE stocks at current levels. Investors should wait for some more time before jumping in the Indian stock market but if we talk about day traders well this is the best time for trading. Swing traders can really mint lot of money in current market situations.
Sharetipsinfo Team

Stock Tips said...

Visiting this blog is our real pleasure. Should like to thank admin for sharing such a useful information and starting this thread in addition to that we suggest traders not to panic when market is in profit booking state.
Stock Tips

vishal said...

Thanks so much for this! I haven't been this moved by a blog for a long time! You’ve got it, whatever that means in blogging. Anyway, You are definitely someone that has something to say that people need to hear. Keep up the good work. Keep on inspiring the people!

stock tips,nifty tips,commodity tips | mcx india,mcx tips,intraday tips

Unknown said...

It's glad to see good information being convey. Its a very nice written, and I really like this blog. Thank you for sharing this info.

Regards: Share Market Tips