Cointegration Calculation in Excel
- Thread starter robbo
- Start date
I've never used this link or excel really for this kind of application. There's plenty of examples of augmented dickey fuller tests (ADFs) and Engle Granger tests online, but I feel like in my experience the Engle Granger depends on which variable you choose to regress onto the other, so I recommend the johansen test which can examine multiple cointegration relationships at a time. Simply put, it's my belief that simple pairs trading of only two assets isn't going to get it done with a suitably low amount of drawdown...it's too easy. I recommend you target lower timeframes than excel can really handle and try to investigate "creative" relationships. Since you probably arent interested in doing that, I think the "creativity" of your portfolios will likely make or break you. I recommend reading the prospectuses of some exchange traded assets and regressing weighted linear combinations of their holding onto the ETF/P/N (which gives you in effect a position in the remaining holdings of the ETF, but it can still work very well). Which holdings and weights to choose is an optimization problem that's too involved for this thread (genetic optimization algorithms work well here because the number of possibilities is a combinatorics problem with millions of solutions often + the fact you need to select the best weights too, making it completely unbounded). You also obviously want to analyze multiple relationships at a time, because you spend very little time in the market by only trading when their is suitable divergence. Note on that: if all your pairs seem to want to trade at the same time you likely are not beta neutral, which is okay sometimes, but just realize you have that exposure. There's also a bunch of techniques for deciding if pairs are beginning to converge or not, but I don't think they're all that valid for me because I target much lower timeframes. This technique has shown some interesting results in the past for me. Here's a link I found, again, if the link doesn't pan out the technique I recommend is called the johansen cointegration test. This topic is stat arb 101: lecture 2, so enjoy the journey!
http://www.spiderfinancial.com/supp...-guide/statistical-testing/cointegration-test
Yes @1245 , it can produce a high win rate and often has substantially lower volatility of returns (which is really the key here because this strategy takes a lot of leverage to make substantial returns with unless you trade lower timeframes, which is why I made the above recommendation), but you have to engineer protfolios that have legitimate fundamental relationships (such as the example I gave) or else the spread will undoubtably diverge to unsuitable levels invalidating the strategy and likely costing you a lot of money. Cointegration also does not necessarily imply that a profitable trading relationship exists. Note: I don't know if you're trolling or not because this is a very common technique, if not I apologize for asking. Hope all is well with you!
http://www.spiderfinancial.com/supp...-guide/statistical-testing/cointegration-test
Yes @1245 , it can produce a high win rate and often has substantially lower volatility of returns (which is really the key here because this strategy takes a lot of leverage to make substantial returns with unless you trade lower timeframes, which is why I made the above recommendation), but you have to engineer protfolios that have legitimate fundamental relationships (such as the example I gave) or else the spread will undoubtably diverge to unsuitable levels invalidating the strategy and likely costing you a lot of money. Cointegration also does not necessarily imply that a profitable trading relationship exists. Note: I don't know if you're trolling or not because this is a very common technique, if not I apologize for asking. Hope all is well with you!
I see. I'll watch out for you on Fridays
Yes, cointegration addresses a lot of the issues with correlation based trading. The concept is super easy, you basically just regress one variable onto another and select a scalar beta value for the one you regressed such that their linear combination forms a stationary process.
S = X - beta*Y
Where S is (hopefully) a stationary process, X and Y are timeseries, and beta is a scalar result of your regression.
Sounds good in theory, in practice it's rather tough because some limitations exist. I know this technique came into and left fashion in the prop trading and hedge fund industry some time ago, but I believe it can still be viable. There are a lot of ways to permute this strategy, and I'm confident a subset of them are profitable (I believe Ive found of one or two), im not convinced the standard technique that pops up all over as a rudimentary/introductory example is optimal though. I'm sorry if my last post was insulting!
Yes, cointegration addresses a lot of the issues with correlation based trading. The concept is super easy, you basically just regress one variable onto another and select a scalar beta value for the one you regressed such that their linear combination forms a stationary process.
S = X - beta*Y
Where S is (hopefully) a stationary process, X and Y are timeseries, and beta is a scalar result of your regression.
Sounds good in theory, in practice it's rather tough because some limitations exist. I know this technique came into and left fashion in the prop trading and hedge fund industry some time ago, but I believe it can still be viable. There are a lot of ways to permute this strategy, and I'm confident a subset of them are profitable (I believe Ive found of one or two), im not convinced the standard technique that pops up all over as a rudimentary/introductory example is optimal though. I'm sorry if my last post was insulting!
Last edited:
Just curious, how low of a time frame are you talking about? I've been working on a stat arb model for the past few months (going live Monday) and was initially aiming for something high-ish frequency but was surprised to see that it performed better on nearly every measure if I slowed it down a bit. I refused to believe it, so I re-coded everything in a different language and tested against a different data set...with no meaningful change in results.
Yeah @jtrader33 , I've definitely seen that to be the case in some instances. I'm saying "more than excel can handle" just because dealing with tick or minute data in excel seems like a nightmare to me. There are definitely pairs trading opportunities at higher timeframes, and there's probably some happy medium specific to each pairs trading opportunity where the marginal utility of trading lower timeframes is outweighed by costs of trading etc. and where trading higher timeframes subjects you to drawdown and constrains you to smaller positions due to margin requirements. If life were as pretty as textbooks this would be a classic convex optimization problem haha. The ideal trading frequency is probably variable though, as our different approaches highlight. Either way, I applaud you for taking that extra step to of recoding and testing on different data to validate your strategy. Good luck going live, I'm not there yet, but it sounds like you're taking all the right steps.
That was the odd part, the "slower" performance metrics were better even without factoring in costs. I'd always expect there to be more inefficiencies at the micro-structure level. Anyhow, this model is an offshoot of a pure arb model I've been trading for a year or two. It's done okay, but it's very much in HF realm and I've noticed that those guys are pretty quick to adjust. So, the number of opportunities has decreased slowly but steadily over time (some vol helps of course).