Dear All,
thanks to Mike Lacy who provided a useful suggestion in another thread I was able to create my own program. It aims at checking whethere two stocks are cointegrated (and so they can be treated as a pair) or not. The program, still in its development, is below:
Below is a subset of my data:
I have the following issue I cannot solve at all. The code above will check any possible not repeated combination of two stocks to check if they are cointegrated (and therefore they could be treated as a pair) or not. As you can see from the code, at the end of each check a statement is displayed. For instance if I type:
I have the following three statements at the end of each check:
AMG and AFL are not a pair
AMG and A are not a pair
AFL and A are cointegrated and can be treated as a pair
Notice that another possible result (not displayed in this example) is:
X and Y are not cointegrated and cannot be trated as a pair
My question: Is there any way I can transfer those statements above in a table dsplayed in Stata or even better to an Excel file in three columns named Stock 1 Stock 2 Result like the following?
If possible, any hint on this?
Thanks in advance,
Dario
thanks to Mike Lacy who provided a useful suggestion in another thread I was able to create my own program. It aims at checking whethere two stocks are cointegrated (and so they can be treated as a pair) or not. The program, still in its development, is below:
Code:
capture program drop pairs
program define pairs, rclass
version 9
syntax varlist(min=2 max=1000 numeric), [SIg(numlist min=1 max=1)]
// Construct the list and count the items.
local k: word count `varlist'
// Loop over distinct pairs of variables:
// 1,2; 1,3, ... 2,3, ...; k-1, k
local km1 = `k' - 1 // k minus 1
forval i = 1/`km1' {
local ip1 = `i' + 1 // i plus 1
forval j = `ip1'/`k' {
local v1: word `i' of `varlist'
local v2: word `j' of `varlist'
di as result _newline "working with `v1' and `v2'"
reg L(0/2).`v1' L(1/2).`v2'
testparm L(1/2).`v2'
local p1=r(p)
reg L(0/2).`v2' L(1/2).`v1'
testparm L(1/2).`v1'
local p2=r(p)
if `p1'<0.10 & `p2'>=0.10 {
reg `v1' `v2'
tempvar res_`v1'_`v2'
predict `res_`v1'_`v2'', res
dfuller `res_`v1'_`v2'', nocon
local dfstat=r(Zt)
local cv1=r(cv1)
local cv5=r(cv5)
local cv10=r(cv10)
if "`sig'"=="" {
if `dfstat'<`cv10' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv10' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="5" {
if `dfstat'<`cv5' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv5' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="1" {
if `dfstat'<`cv1' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv1' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
}
if `p1'>=0.10 & `p2'<0.10 {
reg `v2' `v1'
tempvar res_`v2'_`v1'
predict `res_`v2'_`v1'', res
dfuller `res_`v2'_`v1'', nocon
local dfstat=r(Zt)
local cv1=r(cv1)
local cv5=r(cv5)
local cv10=r(cv10)
if "`sig'"=="" {
if `dfstat'<`cv10' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv10' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="5" {
if `dfstat'<`cv5' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv5' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="1" {
if `dfstat'<`cv1' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv1' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
}
if `p1'<0.10 & `p2'<0.10 {
reg `v1' `v2'
tempvar res_`v1'_`v2'
predict `res_`v1'_`v2'', res
dfuller `res_`v1'_`v2'', nocon
local dfstat=r(Zt)
local cv1=r(cv1)
local cv5=r(cv5)
local cv10=r(cv10)
if "`sig'"=="" {
if `dfstat'<`cv10' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv10' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="5" {
if `dfstat'<`cv5' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv5' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
else if "`sig'"=="1" {
if `dfstat'<`cv1' {
di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
}
else if `dfstat'>=`cv1' {
di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
}
}
}
else if `p1'>=0.10 & `p2'>=0.10 {
di as result _newline "`v1' and `v2' are not a pair"
}
}
}
end
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float period double(AMG AFL A) float time 14612 24.117994 5.491572 44.694592 1 14613 22.190193 5.345844 41.280415 2 14614 21.656973 5.3151649999999995 38.71978 3 14615 22.313243999999997 5.422543999999999 37.245483 4 14616 22.846467999999998 5.52992 40.349281 5 14619 23.420702 5.200119 42.79351 6 14620 23.338669 5.092741999999999 42.211543999999996 7 14621 23.379687999999998 5.008374 41.358017 8 14622 24.56918 5.123422 41.97876 9 14623 25.102397999999997 5.2691479999999995 42.444323999999995 10 14627 26.04579 4.9086669999999994 44.384209 11 14628 25.717653 4.617214 43.45306 12 14629 25.676636 4.993034 42.289142999999996 13 14630 24.282057 4.755269999999999 42.677124 14 14633 24.241039 4.701581 42.521946 15 14634 23.420702 4.900996999999999 42.017562999999996 16 14635 23.830875 5.146431 42.405536999999995 17 14636 23.871889 5.054393999999999 42.483131 18 14637 23.584774 5.1540989999999995 42.250347 19 14640 22.723415 5.3305039999999995 41.08643 20 14641 22.928497 5.437882999999999 44.073822 21 14642 23.338669 5.445551 47.487998999999995 22 14643 24.035959 5.51458 48.263943 23 14644 24.897316 5.391863 47.332809 24 14647 24.979349 5.246138 49.350266 25 14648 24.651211 5.146431 49.039885999999996 26 14649 26.332908999999997 5.069731 47.022422999999996 27 14650 26.086809 5.062063 47.604378 28 14651 26.291892999999998 4.8933279999999995 46.789642 29 14654 26.168844 4.79362 47.4492 30 14655 25.79969 4.8307199999999995 50.824574 31 14656 25.635621999999998 4.792072999999999 50.281406 32 14657 25.307485999999997 4.676136 60.213539 33 14658 24.487140999999998 4.660677 58.196079 34 14662 24.035959 4.606574999999999 56.799369999999996 35 14663 23.99494 4.5292829999999995 61.455054999999994 36 14664 25.512573 4.4056169999999995 66.265907 37 14665 25.430536 4.336055 67.119461 38 14668 25.143418999999998 4.56793 62.696571 39 14669 25.79969 4.521554 64.48123199999999 40 14670 26.414944 4.56793 67.74022699999999 41 14671 26.455961 4.482907 65.36383099999999 42 14672 27.727487999999997 4.5292829999999995 67.041878 43 14675 27.071213 4.475178 98.700531 44 14676 26.579012 4.3051379999999995 88.147644 45 14677 26.209858 4.312867 94.355232 46 14678 25.922739 4.35924 94.97599799999999 47 14679 25.717653 4.343782 88.147644 48 14682 25.471552 4.251034 81.474503 49 14683 25.594604 4.428802999999999 75.344528 50 14684 25.348505 5.081913999999999 70.921623 51 14685 27.112233999999997 5.44132 73.28826099999999 52 14686 28.260707999999998 5.170802999999999 75.18933899999999 53 14689 28.793927999999998 5.108968 70.611229 54 14690 29.32715 5.364031 71.38718399999999 55 14691 31.172912999999998 5.45678 74.141792 56 14692 32.813587 5.557259999999999 74.296982 57 14693 32.198338 5.680924 74.490967 58 14696 30.475626 5.425863 70.921623 59 14697 30.229525 5.402678 73.947823 60 14698 30.885796 5.5340739999999995 69.524902 61 14699 30.311557999999998 5.503155 65.179604 62 14700 31.172912999999998 5.634551 64.558853 63 14703 29.532238 5.812321 60.834300999999996 64 14704 27.112233999999997 5.758217999999999 58.040893999999994 65 14705 26.209858 5.680924 60.174751 66 14706 25.184433 5.719573 65.179604 67 14707 27.153247999999998 5.626822 75.732506 68 14710 26.332908999999997 5.696383 66.49872599999999 69 14711 26.948162 5.820050999999999 62.657779999999995 70 14712 26.579012 5.827783 53.385200999999995 71 14713 27.317318 5.812321 55.868229 72 14714 24.241039 5.696383 51.32893 73 14717 24.282057 5.642278999999999 49.350266 74 14718 24.815281 5.634551 54.005955 75 14719 25.430536 5.518612999999999 54.161139999999996 76 14720 26.332908999999997 5.8818839999999994 55.829426 77 14724 26.332908999999997 5.990091 56.876976 78 14725 26.620026 6.036466 55.868229 79 14726 26.702063 6.206505 57.032162 80 14727 26.579012 5.997821999999999 56.333797 81 14728 26.332908999999997 6.036466 55.014689999999995 82 14731 26.127823 5.974632 59.204806999999995 83 14732 24.610197 5.9205309999999995 62.551052 84 14733 24.446127 5.665468 56.799369999999996 85 14734 23.830875 5.634551 56.023422 86 14735 24.076977 5.626822 56.760593 87 14738 22.723415 5.688656 52.609249 88 14739 22.108159999999998 5.781406 49.777038999999995 89 14740 21.164768 5.8818839999999994 47.798362999999995 90 14741 20.672563999999998 5.820050999999999 47.526793999999995 91 14742 21.451885 5.905069999999999 56.217411 92 14745 21.985108999999998 6.036466 54.743114 93 14746 22.969514999999998 6.102157 54.937079999999995 94 14747 22.682392 6.008757 48.419135999999995 95 14748 23.092565999999998 6.226693999999999 44.073822 96 14749 23.010534 6.047674 41.358017 97 14752 22.395279 6.281178 38.71978 98 14753 21.000704 6.351228 36.003966999999996 99 14754 21.123751 6.312310999999999 36.624728999999995 100 14755 20.590529999999998 6.257827 42.677124 101 14756 21.369856 6.335659 40.349281 102 14760 22.477311999999998 6.397926 46.789642 103 14761 21.985108999999998 6.4368419999999995 45.703316 104 14762 23.051548 6.608078 45.315342 105 14763 24.733245999999998 6.576942 50.746967 106 14766 23.912905 6.374575999999999 49.039885999999996 107 14767 24.610197 6.4446259999999995 44.073822 108 14768 24.897316 6.491326 41.590796999999995 109 14769 24.856295 6.421278999999999 42.909904 110 14770 24.938332 6.304525 43.996230999999995 111 14773 24.774265 6.250039999999999 40.970031999999996 112 14774 25.020369 6.320093 42.871100999999996 113 14775 25.471552 6.265611 42.056357999999996 114 14776 25.676636 6.133293999999999 40.931239999999995 115 14777 25.676636 5.954276999999999 38.874981 116 14780 27.809521 5.946492 42.211543999999996 117 14781 27.235281 5.720772999999999 45.703316 118 14782 28.588846 5.860874 48.729515 119 14783 28.424774 5.860874 47.022422999999996 120 14784 27.194267 5.892008 46.634457 121 14787 27.522403999999998 5.814176 46.634457 122 14788 27.604435 5.9309259999999995 46.828444999999995 123 14789 27.932575 5.952322 49.350266 124 14790 28.506807 5.728555999999999 47.100029 125 14791 29.850071 5.720772999999999 45.780902999999995 126 14794 28.711893 5.845308999999999 45.742107 127 14796 27.891554 5.611806 43.608261 128 14797 28.834946 5.650723999999999 42.909904 129 14798 29.368167999999997 5.923139 41.90118 130 14801 29.532238 6.016541 42.521946 131 14802 29.860370999999997 6.17221 41.784779 132 14803 30.803760999999998 6.296746 42.832321 133 14804 30.844782 6.4446259999999995 44.073822 134 14805 31.090881 6.4601939999999995 47.837173 135 14808 31.460033 6.250039999999999 48.574329 136 14809 29.573252 6.055459 47.56559 137 14810 28.588846 6.102157 45.974903 138 14811 30.147489999999998 6.2422569999999995 33.520939 139 14812 30.434607999999997 6.195561 29.835196 140 14815 29.368167999999997 6.359011 30.727528 141 14816 29.983421 6.421278999999999 27.778923 142 14817 29.696303999999998 6.553595 26.6926 143 14818 30.475626 6.678128999999999 25.295893 144 14819 31.829189 6.654777999999999 25.606272 145 14822 31.131898999999997 6.467976999999999 25.295893 146 14823 31.377997999999998 6.826010999999999 25.140703 147 14824 32.198338 6.779312999999999 24.907923 148 14825 31.829189 6.88828 25.451079999999997 149 14826 33.223766 6.91941 24.86912 150 14829 34.249187 7.036163999999999 24.675133 151 14830 35.069527 6.911632999999999 24.093173999999998 152 14831 34.454281 6.857142 24.403554999999997 153 14832 33.96207 6.779312999999999 24.209567999999997 154 14833 34.208176 7.0128129999999995 25.218297999999997 155 14836 34.864444999999996 7.324146 27.002976999999998 156 14837 35.520717999999995 7.2258189999999995 26.382219 157 14838 35.438679 6.897017 27.002976999999998 158 14839 35.110546 6.920506 28.865251999999998 159 14840 34.618342999999996 6.850045 34.995235 160 14843 35.069527 6.904845 37.555866 161 14844 36.464115 6.912674 37.555866 162 14845 36.956314 6.818733 37.555866 163 14846 37.079357 6.740444 37.090298 164 14847 38.063773999999995 6.724786 36.314341999999996 165 14850 36.997333999999995 6.795246 36.391929999999995 166 14851 36.710217 6.5682149999999995 39.107758 167 14852 35.930884999999996 6.787417 38.099033 168 14853 36.505123 6.765885 37.866253 169 14854 36.915290999999996 6.693472 38.409409 170 14858 36.956314 6.77176 38.370613 171 14859 38.309868 6.87353 37.245483 172 14860 38.268848 7.202334 38.409409 173 14861 38.063773999999995 7.546793 36.779922 174 14864 40.278687 7.640738 35.228007999999996 175 14865 39.417331999999995 7.648567 34.141701 176 14866 38.227837 7.554621 34.141701 177 14867 42.16547 7.601595 34.607265 178 14868 39.171234 7.625076 35.383202 179 14871 37.079357 7.538964999999999 33.520939 180 14872 39.786484 7.554621 33.443348 181 14873 39.294281 7.664223 32.589802 182 14874 38.145801999999996 7.67205 29.796388999999998 183 14875 37.161395999999996 7.867769999999999 31.03791 184 14878 36.341057 7.985196999999999 29.990375999999998 185 14879 34.864444999999996 8.267026999999999 28.050503 186 14880 34.290203 8.196568 28.554871 187 14881 35.766815 8.086969 29.767274999999998 188 14882 37.366478 8.094795999999999 30.378339999999998 189 14885 36.669188999999996 8.196568 31.387081 190 14886 35.930884999999996 8.392284 32.977776 191 14887 34.249187 8.306167 35.926376 192 14888 34.372242 8.462743 34.219288 193 14889 34.454281 8.157429 32.395828 194 14892 33.633938 8.07131 31.464675999999997 195 14893 32.977665 7.914739 30.766329 196 14894 32.157322 7.812968 28.438488 197 14895 31.706132999999998 7.546793 27.778923 198 14896 34.044106 7.726849 27.546141 199 14899 34.208176 7.664223 28.826452 200 14900 32.157322 7.538964999999999 26.886582999999998 201 14901 31.336985 7.4058779999999995 25.373483999999998 202 14902 34.864444999999996 7.625076 27.158168999999997 203 14903 35.930884999999996 7.97737 28.904051 204 14906 36.833259999999996 7.820796 31.69747 205 14907 36.833259999999996 7.828625 29.835196 206 14908 35.602753 8.251368 26.459809999999997 207 14909 34.782406 8.149595999999999 27.313353 208 14910 37.694618 8.517545 28.399684999999998 209 14913 38.720036 8.963778 28.24449 210 14914 39.458344 9.151660999999999 28.748856 211 14915 39.212246 8.916806 29.05924 212 14916 40.688854 8.768056 29.873993 213 14917 41.755299 8.760231 29.05924 214 14920 41.755299 8.963778 28.787664 215 14921 39.745467999999995 8.799373 28.516081 216 14922 38.555973 8.736742999999999 27.546141 217 14923 37.735634 9.143830999999999 27.041773 218 14924 36.79224 9.065546 24.481147999999997 219 14927 35.684784 8.791547 24.403554999999997 220 14928 36.176991 9.038874999999999 26.459809999999997 221 14929 38.145801999999996 8.700603 26.925385 222 14930 37.94072 8.747805 25.839056 223 14931 37.735634 8.559008 29.408407 224 14934 37.12038 8.535404999999999 27.701328 225 14935 32.362408 8.425267 30.184365999999997 226 14936 30.844782 8.173534 29.796388999999998 227 14938 32.403423 8.267935 31.619874999999997 228 14941 33.30579 8.425267 31.348285999999998 229 14942 31.829189 8.739939 31.464675999999997 230 14943 30.229525 8.802873 33.443348 231 14944 28.999012 8.857939 32.395828 232 14945 31.172912999999998 8.779273 32.900185 233 14948 30.516641999999997 8.700603 31.658666999999998 234 14949 33.100716 8.747805 34.180489 235 14950 33.223766 8.763542 32.279427 236 14951 33.633938 8.716339 33.520939 237 14952 36.176991 8.677007 36.896313 238 14955 37.079357 8.606204 35.848777999999996 239 14956 33.756989 8.795003 35.538406 240 14957 33.921051 8.551136 36.120365 241 14958 33.059692 8.732071999999999 36.779922 242 14959 32.813587 8.826469 35.305614 243 14962 33.592922 9.141142 35.383202 244 14963 31.460033 9.101808 35.422001 245 14964 31.501049 9.023144 32.589802 246 14965 32.116306 8.889407 31.891439 247 14966 34.618342999999996 8.842205 33.947716 248 14970 34.618342999999996 8.802873 33.21056 249 14971 34.864444999999996 8.983806999999999 34.723663 250 14972 37.735634 9.054608 34.335678 251 14973 36.01292 9.086077 33.986504 252 14977 32.936645999999996 8.763542 31.581079 253 14978 38.063773999999995 8.527538 34.840046 254 14979 38.063773999999995 8.197136 36.159157 255 14980 36.997333999999995 8.102737 34.180489 256 14983 36.587165999999996 8.102737 33.055369999999996 257 14984 35.602753 8.197136 32.900185 258 14985 35.274612 8.275803 34.335678 259 14986 36.79224 8.1342 35.73238 260 14987 36.710217 7.795933 34.840046 261 14991 36.669188999999996 7.835267 36.120365 262 14992 38.022755 7.795933 38.448204 263 14993 38.309868 7.733002 41.939976 264 14994 37.530544 7.135126 40.698451999999996 265 14997 38.555973 7.520594 38.952568 266 14998 40.196644 7.835267 38.952568 267 14999 40.688854 7.819526 36.003966999999996 268 15000 38.638008 7.858864 34.29689 269 15001 37.817661 7.8038 33.908913 270 15004 38.720036 7.679188 33.781658 271 15005 40.557601999999996 7.671639 33.986504 272 15006 40.360721999999996 7.423674999999999 33.86235 273 15007 39.573195999999996 7.5080089999999995 33.396789999999996 274 15008 37.013732999999995 7.514303 32.403576 275 15011 36.580597 7.912045 30.541306 276 15012 36.390274 7.816383 31.640034 277 15013 36.849669999999996 7.973719999999999 31.124814999999998 278 15014 35.41243 7.992598999999999 33.651306 279 15015 34.880856 7.954839 32.589802 280 15018 35.464935 7.963654 31.733159999999998 281 15019 35.635567 7.96998 29.40531 282 15020 35.208981 7.883926 29.796388999999998 283 15021 34.585529 7.649811 33.427825999999996 284 15022 33.837379 7.700429 31.03791 285 15026 33.469868 7.521997 27.313353 286 15027 33.765181999999996 7.246123 24.954480999999998 287 15028 33.732372 7.2967439999999995 24.253017 288 15029 34.638031 7.303069 23.775032 289 15032 36.259022 7.424556 23.775032 290 15033 35.373058 7.763706 23.681921 291 15034 33.798004 7.613112999999999 22.347296 292 15035 33.883312 7.65994 23.781243999999997 293 15036 34.782406 7.835837 23.607431 294 15039 33.994884 7.890249 23.619844 295 15040 35.537124999999996 7.8016689999999995 25.202783999999998 296 15041 35.819317 7.845962999999999 25.426251999999998 297 15042 34.716785 7.792808999999999 24.830322 298 15043 34.283642 7.637156999999999 22.999088 299 15046 33.010470999999995 7.472645999999999 21.732744 300 end format %td period
Code:
pairs AMG-A
AMG and AFL are not a pair
AMG and A are not a pair
AFL and A are cointegrated and can be treated as a pair
Notice that another possible result (not displayed in this example) is:
X and Y are not cointegrated and cannot be trated as a pair
My question: Is there any way I can transfer those statements above in a table dsplayed in Stata or even better to an Excel file in three columns named Stock 1 Stock 2 Result like the following?
Code:
Stock 1 Stock 2 Result AMG AFL not a pair AMG A not a pair AFL A pair X Y not a pair
Thanks in advance,
Dario
Comment