Directory of Open Source for Quantitative Finance and Trading
Username: Password: Not registered?
Quick Search:    (AJAX based: No need to press button)

Main : Excel : 

Category: Excel View Full Details
Download Now!Cointegration analysis using Johansen procedure
Submitter: vanna Date: 2011/1/30
This spreadsheet uses Johansen's methodology to calculate likelihood ratio statistics for :
1.Testing null hypothesis of h cointegrating relations against alternative of no restrictions. ->Trace statistic is computed
2.Testing null hypothesis of h cointegrating relations against alternative of h+1 relations ->maximum eigenvalue statistic is computed

To use this spreadsheet:

Step 1 : Enter input data in inputdata tab -> time series as rows and variables as columns
Step 2 : select main tab. Specify no of lags for regression model
Step 3 : Click on button
Step 4 : verify results of hypotesis testing in the tabs johansen_results_eigen and johansen_results_trace

Note : johansen test statistics used in this code are for constant and no time trend

Formulas are based on paper User Guide for Johansen's Method

The data in the inputdata currently has historical currency prices of the following in order : USDEUR,USDCAD,USDAUD,USDJPYUSDGBP

For more information, please refer FAQs:
Downloaded 11755 times  11755  File Size 0 bytes  Supported Platforms Excel VBA  Home Page
Rating: 0.00 (0 votes)
Rate this File | Modify | Delete | Report Broken File | Tell a Friend | Comments (186)

Poster Thread
Posted: 2009/3/4 15:05  Updated: 2009/3/4 15:05
 Re: Cointegration analysis using Johansen procedure
Dear vana,

First of all thank you for posting your brilliant excel on cointegration. Your work will probably help me finish my master thesis and graduate..
I a student at the Erasmus university of rotterdam in the netherlands.
I just entered my dataset in your spreadsheet and can now successfully view the results.
The only think is that I'm a statistics dummy and have not a clew about these results.
My research needs to end in a table as the one below:
My question is where can I find these numbers in your spreadsheet.
Your help would mean ALOT to me..

Data series 1 2 3 4 5

Variable 1 -
Variable 2 20.4 -
Variable 3 9.4 11.4 -
Variable 4 15,8 18.9 16.6 -
Variable 5 15,0 22,7 10,5 18,3 -
Notes: this table reports pairwise cointegration tests based on the trace statistics of the Johansen maximum likelihood procedure. The 5 and 1 per cent critical values are 15.4 and 20.0 respectivaly. Test statistics above the critical values indicate that the null hypothesis of no cointegration must be rejected.

Poster Thread
Posted: 2009/4/22 9:08  Updated: 2009/4/22 9:08
Just popping in
Joined: 2007/8/28
Posts: 2
 Re: Cointegration analysis using Johansen procedure
Would it be easy to adapt this code to test granger casuality between variables in Excel?



Poster Thread
Posted: 2009/7/30 12:09  Updated: 2009/7/30 12:09
 Re: Cointegration analysis using Johansen procedure
Hi vana,

Do you still have a copy of "User Guide for Johansen's Method" by Kyungho Jang and Masao Ogaki that goes with your VBA?

The link to Prof Ogaki's paper is broken.

Poster Thread
Posted: 2010/1/3 8:15  Updated: 2010/1/3 8:15
 Re: Cointegration analysis using Johansen procedure
Vanna, or someone else,

how do I interpret the result from the Excel run. I do not understand the resulting calculations.

How do I know if two price series are cointegrated using the Excel App?

Poster Thread
Posted: 2011/2/3 11:54  Updated: 2011/2/3 11:54
 Cointegration analysis using Johansen procedure
if test statistic is less than crikticla value, the rank is <= rank column

Poster Thread
Posted: 2011/4/1 14:08  Updated: 2011/4/1 14:08
 Cointegration analysis using Johansen procedure
how can i get a result in stata 10 regarding standardized beta eigenvectors and alpha adjustment coefficient in co integration analysis?
example results like this i am using different data show me how can i do it.
standardized \beta' eigenvectors
lwp u lpq lpr disp Constant Trend
1.0000 0.021531 0.48839 -1.0251 -0.0069453 0.60454 0.0017509
121.87 1.0000 128.55 -9.2104 -10.910 597.36 -0,67599
0.69914 0.015608 1.0000 -0.39300 0.069711 1.8739 -0.0038503
1.1047 -0.026567 -0.58767 1.0000 -0.054941 9.9006 -0.0036223
12.020 0.62023 -18.534 -1.6242 1.0000 43.305 0.035317
standardized \alpha coefficients
lwp 0.12411 -0.0014037 -0.27240 -0.028016 -0.0039689
u 2.8183 -0.0067621 4.6708 0.32737 -0.15964
lpq 0.29730 -0.0018186 0.10495 0.075423 0.0027409
lpr 0.73246 -0.00092115 0.073468 -0.11650 0.0014080
disp 0.41849 0.0058612 -0.55182 0.33119 -0.0052317

Poster Thread
Posted: 2011/6/3 3:00  Updated: 2011/6/3 3:00
 Cointegration analysis using Johansen procedure
Is it possible to reject null when lower rank can no reject null

Poster Thread
Posted: 2012/6/4 4:12  Updated: 2012/6/10 12:21
 Cointegration analysis using Johansen procedure
Hi, I just wonder where I can find the p value in this test?


Poster Thread
Posted: 2012/11/4 22:31  Updated: 2012/11/18 13:34
 Cointegration analysis using Johansen procedure
what is the johanson cointegration equation and its hypotheses for cointegration test

Poster Thread
Posted: 2012/12/13 14:18  Updated: 2012/12/14 22:22
 Cointegration analysis using Johansen procedure
how do i determine the significance of a variable used
Similar Links:
Guided tour on Johansens cointegration analysis (Paper)
Cointegration analysis and complex eigen values (Forum)
Lecture notes on Introduction to Fixed Income Analysis (Paper)
Cointegration - Error correction model or Johansen method? (Forum)
Pricing Asian Options by Contour Integration (Software)

Subscribe to RSS or daily email updates of latest quantitative finance code listings
Email address :
Copyright 2011 QuantCode Inc. All rights reserved.