Storing yearly R^2 in rolling regression

[Nihar Singh]

Hi Stata users,

I have daily total return index data from January 01, 1990, for 40 countries. From this, I have calculated a biweekly return for each country, where the biweek variable is created from Wednesday to Wednesday.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 date str12 country_name byte country_no float(ddate datebw biweek_total_ri_return biweek_total_world_ri_return lag_biweek_total_ri_return)
"01-01-1990" "aus" 1 10958 10946   .004992723  -.003499508            .
"03-01-1990" "aus" 1 10960 10960    .01420617   -.03235364   .004992723
"17-01-1990" "aus" 1 10974 10974   .013351202  -.004710376    .01420617
"31-01-1990" "aus" 1 10988 10988   -.03840905  .0022166967   .013351202
"14-02-1990" "aus" 1 11002 11002   -.03061688   -.05593592   -.03840905
"28-02-1990" "aus" 1 11016 11016  -.007649541  -.015755475   -.03061688
"14-03-1990" "aus" 1 11030 11030   .014085412  -.024517417  -.007649541
"28-03-1990" "aus" 1 11044 11044  -.028669715   -.02476758   .014085412
"11-04-1990" "aus" 1 11058 11058  -.031349182  -.013661623  -.028669715
"25-04-1990" "aus" 1 11072 11072    .02133918    .04133677  -.031349182
"09-05-1990" "aus" 1 11086 11086  -.009497046    .04605412    .02133918
"23-05-1990" "aus" 1 11100 11100    .05099225   .016429901  -.009497046
"06-06-1990" "aus" 1 11114 11114   -.01463008   -.02109033    .05099225
"20-06-1990" "aus" 1 11128 11128   .014203668   .016453981   -.01463008
"04-07-1990" "aus" 1 11142 11142     .0862434    .02945137   .014203668
"18-07-1990" "aus" 1 11156 11156  -.030489683   -.02189821     .0862434
"01-08-1990" "aus" 1 11170 11170  -.011194587   -.08022314  -.030489683
"15-08-1990" "aus" 1 11184 11184   -.03756678  -.022066057  -.011194587
"29-08-1990" "aus" 1 11198 11198   .007586718  -.015966415   -.03756678
"12-09-1990" "aus" 1 11212 11212  -.068493724   -.05977333   .007586718
"26-09-1990" "aus" 1 11226 11226  -.012047946    .04065561  -.068493724
"10-10-1990" "aus" 1 11240 11240   .013201118    .05473936  -.012047946
"24-10-1990" "aus" 1 11254 11254    -.0288012  -.019114494   .013201118
"07-11-1990" "aus" 1 11268 11268    .04471409   -.01049012    -.0288012
"21-11-1990" "aus" 1 11282 11282  -.032266438   -.02621943    .04471409
"05-12-1990" "aus" 1 11296 11296  -.015798151    .04610276  -.032266438
"19-12-1990" "aus" 1 11310 11310  -.004942775   -.02219385  -.015798151
"02-01-1991" "aus" 1 11324 11324   -.04579997   -.04285741  -.004942775
"16-01-1991" "aus" 1 11338 11338    .08335578    .06149638   -.04579997
"30-01-1991" "aus" 1 11352 11352     .0641799    .09566844    .08335578
"13-02-1991" "aus" 1 11366 11366    .02462232    .00649023     .0641799
"27-02-1991" "aus" 1 11380 11380   .012413383     .0073843    .02462232
"13-03-1991" "aus" 1 11394 11394   .009890914  -.019466937   .012413383
"27-03-1991" "aus" 1 11408 11408   .009047151   .025621176   .009890914
"10-04-1991" "aus" 1 11422 11422    .04835117  -.010984778   .009047151
"24-04-1991" "aus" 1 11436 11436   .021472096   .004578829    .04835117
"08-05-1991" "aus" 1 11450 11450   .011117458  -.010183692   .021472096
"22-05-1991" "aus" 1 11464 11464  -.014169037   .009105206   .011117458
"05-06-1991" "aus" 1 11478 11478    .01424384  -.030667424  -.014169037
"19-06-1991" "aus" 1 11492 11492    .01963985  -.009581447    .01424384
"03-07-1991" "aus" 1 11506 11506  .0027394295    .01024902    .01963985
"17-07-1991" "aus" 1 11520 11520   .025245786   .017113328  .0027394295
"31-07-1991" "aus" 1 11534 11534  -.010611892  -.005313277   .025245786
"14-08-1991" "aus" 1 11548 11548  -.007034123  -.012850463  -.010611892
"28-08-1991" "aus" 1 11562 11562   .019621134    .01815617  -.007034123
"11-09-1991" "aus" 1 11576 11576   .001413703   .018018961   .019621134
"25-09-1991" "aus" 1 11590 11590   .007764578   .011551976   .001413703
"09-10-1991" "aus" 1 11604 11604    .05060589   .008115053   .007764578
"23-10-1991" "aus" 1 11618 11618    .02896571   .011047244    .05060589
"06-11-1991" "aus" 1 11632 11632    -.0138793   -.02649492    .02896571
"20-11-1991" "aus" 1 11646 11646   -.02890104  -.016493678    -.0138793
"04-12-1991" "aus" 1 11660 11660 -.0022078156   .009835005   -.02890104
"18-12-1991" "aus" 1 11674 11674   .036242247     .0545646 -.0022078156
"01-01-1992" "aus" 1 11688 11688 -.0029102564   -.02019119   .036242247
"15-01-1992" "aus" 1 11702 11702  -.014740765  .0006113052 -.0029102564
"29-01-1992" "aus" 1 11716 11716  -.003567219   .003632426  -.014740765
"12-02-1992" "aus" 1 11730 11730   .020943165  -.026663065  -.003567219
"26-02-1992" "aus" 1 11744 11744   -.01998788   -.01906967   .020943165
"11-03-1992" "aus" 1 11758 11758  -.012070358   -.01532501   -.01998788
"25-03-1992" "aus" 1 11772 11772   .003612757  -.035612702  -.012070358
"08-04-1992" "aus" 1 11786 11786   .004726052   .015393734   .003612757
"22-04-1992" "aus" 1 11800 11800     .0506196    .02626872   .004726052
"06-05-1992" "aus" 1 11814 11814   .005459905   .032672286     .0506196
"20-05-1992" "aus" 1 11828 11828   .008245468 -.0017470717   .005459905
"03-06-1992" "aus" 1 11842 11842   -.02295977  -.017488956   .008245468
"17-06-1992" "aus" 1 11856 11856  .0012634993  -.013139307   -.02295977
"01-07-1992" "aus" 1 11870 11870  -.002913654    .02306521  .0012634993
"15-07-1992" "aus" 1 11884 11884    -.0211004   -.03963751  -.002913654
"29-07-1992" "aus" 1 11898 11898  -.011272192  -.017940879    -.0211004
"12-08-1992" "aus" 1 11912 11912    -.0411796   .032431483  -.011272192
"26-08-1992" "aus" 1 11926 11926   -.00820142    .04136622    -.0411796
"09-09-1992" "aus" 1 11940 11940   .013631463  -.016584992   -.00820142
"23-09-1992" "aus" 1 11954 11954   -.04306704   -.02864033   .013631463
"07-10-1992" "aus" 1 11968 11968  -.015547216  .0020933151   -.04306704
"21-10-1992" "aus" 1 11982 11982  -.007673979   .003033161  -.015547216
"04-11-1992" "aus" 1 11996 11996   -.03207332   -.02139169  -.007673979
"18-11-1992" "aus" 1 12010 12010    .06534648   .035867214   -.03207332
"02-12-1992" "aus" 1 12024 12024    .05026817   .005258203    .06534648
"16-12-1992" "aus" 1 12038 12038    .02151203   .010923147    .05026817
"30-12-1992" "aus" 1 12052 12052    -.0200994  -.018343627    .02151203
"13-01-1993" "aus" 1 12066 12066   .015740871    .01940787    -.0200994
"27-01-1993" "aus" 1 12080 12080    .03726518   .012726545   .015740871
"10-02-1993" "aus" 1 12094 12094   .014974236    .00696516    .03726518
"24-02-1993" "aus" 1 12108 12108   .016030312    .03413737   .014974236
"10-03-1993" "aus" 1 12122 12122   .017621636    .01319313   .016030312
"24-03-1993" "aus" 1 12136 12136  -.003087759    .03589213   .017621636
"07-04-1993" "aus" 1 12150 12150   .028218985    .02267611  -.003087759
"21-04-1993" "aus" 1 12164 12164   -.02628714    .02089286   .028218985
"05-05-1993" "aus" 1 12178 12178 .00004041195  -.015269876   -.02628714
"19-05-1993" "aus" 1 12192 12192   .027874947   .036840796 .00004041195
"02-06-1993" "aus" 1 12206 12206   .003413677  -.006413817   .027874947
"16-06-1993" "aus" 1 12220 12220  -.005833209  -.010300934   .003413677
"30-06-1993" "aus" 1 12234 12234    .04377139    .00648284  -.005833209
"14-07-1993" "aus" 1 12248 12248  .0080708265  .0044178963    .04377139
"28-07-1993" "aus" 1 12262 12262   .027489305    .02733493  .0080708265
"11-08-1993" "aus" 1 12276 12276    .03449428     .0190773   .027489305
"25-08-1993" "aus" 1 12290 12290    .03056133   .016024709    .03449428
"08-09-1993" "aus" 1 12304 12304  -.014700055   -.02419752    .03056133
"22-09-1993" "aus" 1 12318 12318    .03868282    .01604104  -.014700055
"06-10-1993" "aus" 1 12332 12332    .03921437   .011395693    .03868282
end
format %td ddate
format %td datebw

Now, I want to calculate the market integration of a country year-wise. Thus, I want to store the R^2 country and year-wise. I want to use the previous two-year biweekly returns as the estimation period. I am running the following regression, however, I am unsure how to get yearly values. In the code, the window specifies 52 bi-weeks (2 years) as the estimation period.

tsset country_no datebw
rolling r2 = e(r2), window(52) saving(results, replace): reg biweek_total_ri_return lag_biweek_total_ri_return biweek_total_world_ri_return

I am getting the following result after running the regression:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(country_no start end r2)
1 10946 10997          1
1 10947 10998          1
1 10948 10999          1
1 10949 11000          1
1 10950 11001          1
1 10951 11002   .6753538
1 10952 11003   .6753538
1 10953 11004   .6753538
1 10954 11005   .6753538
1 10955 11006   .6753538
1 10956 11007   .6753538
1 10957 11008   .6753538
1 10958 11009   .6753538
1 10959 11010   .6753538
1 10960 11011   .6753538
1 10961 11012          1
1 10962 11013          1
1 10963 11014          1
1 10964 11015          1
1 10965 11016   .0787571
1 10966 11017   .0787571
1 10967 11018   .0787571
1 10968 11019   .0787571
1 10969 11020   .0787571
1 10970 11021   .0787571
1 10971 11022   .0787571
1 10972 11023   .0787571
1 10973 11024   .0787571
1 10974 11025   .0787571
1 10975 11026          1
1 10976 11027          1
1 10977 11028          1
1 10978 11029          1
1 10979 11030 .023285225
1 10980 11031 .023285225
1 10981 11032 .023285225
1 10982 11033 .023285225
1 10983 11034 .023285225
1 10984 11035 .023285225
1 10985 11036 .023285225
1 10986 11037 .023285225
1 10987 11038 .023285225
1 10988 11039 .023285225
1 10989 11040          1
1 10990 11041          1
1 10991 11042          1
1 10992 11043          1
1 10993 11044   .3430872
1 10994 11045   .3430872
1 10995 11046   .3430872
1 10996 11047   .3430872
1 10997 11048   .3430872
1 10998 11049   .3430872
1 10999 11050   .3430872
1 11000 11051   .3430872
1 11001 11052   .3430872
1 11002 11053   .3430872
1 11003 11054          1
1 11004 11055          1
1 11005 11056          1
1 11006 11057          1
1 11007 11058   .9974914
1 11008 11059   .9974914
1 11009 11060   .9974914
1 11010 11061   .9974914
1 11011 11062   .9974914
1 11012 11063   .9974914
1 11013 11064   .9974914
1 11014 11065   .9974914
1 11015 11066   .9974914
1 11016 11067   .9974914
1 11017 11068          1
1 11018 11069          1
1 11019 11070          1
1 11020 11071          1
1 11021 11072   .3488987
1 11022 11073   .3488987
1 11023 11074   .3488987
1 11024 11075   .3488987
1 11025 11076   .3488987
1 11026 11077   .3488987
1 11027 11078   .3488987
1 11028 11079   .3488987
1 11029 11080   .3488987
1 11030 11081   .3488987
1 11031 11082          1
1 11032 11083          1
1 11033 11084          1
1 11034 11085          1
1 11035 11086    .778163
1 11036 11087    .778163
1 11037 11088    .778163
1 11038 11089    .778163
1 11039 11090    .778163
1 11040 11091    .778163
1 11041 11092    .778163
1 11042 11093    .778163
1 11043 11094    .778163
1 11044 11095    .778163
1 11045 11096          1
end
format %td start
format %td end

[Nick Cox]

I don't know what the question is, but I can see a problem. You've tsset in terms of daily dates but you have not specified delta(14) to account for the spacing of data. So, it's unlikely that you're getting the regressions you want.

I have a note in press on this pitfall at the Stata Journal 24(4) which will appear later this year. That is no use to you now, but this thread may be revisited later when the paper is accessible. But

 
 tsset country_no datebw, delta(14) 

is the advice in a nutshell. I've not looked at the rest of your code.

[Nihar Singh]

Hi Nick,

My query is how to get the yearly R-squared value from the regression.

Thank you for pointing out delta(14); it was a silly mistake on my side. Looking forward for your publication.

[Nick Cox]

You did mention that in the middle. If you want yearly regressions, i.e. a separate regression for each year, then they aren't rolling. You can loop over years, or statsby as an offficial command or rangestat from SSC will do it for you.

[Nihar Singh]

Nick, I want yearly R-squared values for each country. But, I want to run the regression on the last two years' data. I used the following statsby command:

statsby e(r2), by(country_no cal_year): reg biweek_total_ri_return lag_biweek_total_ri_return biweek_total_world_ri_return

However, this command does not run the regression on the last two years' data.

[Nick Cox]

That sounds contradictory to me, but this may help:

forval y = 1991/2023 {
     regress .... if inlist(year, `y', `y'-1)
}

[Nihar Singh]

Nick, the code in #6 gives the yearly values. However, I need the yearly values country-wise, which it does not provide. Also, is there a way to store the R-squared value of each country and year?

[Nick Cox]

Sure, loop over countries too.

[Nihar Singh]

Hi Nick,

I looped over countries as well; it is working. Is there a command in loop that can store the R-squared country and year-wise?

[Nick Cox]

Indeed. Show your code to date and there will likely be suggestions on how to extend it.

[Nihar Singh]

I have used the following code:

forval x = 1/41 {
forval y = 1991/2023 {
reg biweek_total_ri_return lag_biweek_total_ri_return biweek_total_world_ri_return if inlist(cal_year, `y', `y'-1)
}
}

[Nick Cox]

That's going to produce the same regression for each country, as nothing inside your loop restricts calculation to a particular country.

You need to correct that. Otherwise you have a problem in saving R-sq in the same observations as with exceptions at the ends of the data each year's observations is used twice.

I would look into postfile as a solution, i.e. post your results elsewhere. This is more general advice as otherwise how are you going to manage the results of 41 * 33 regressions?

[Nihar Singh]

Thank you, Nick for the solution. It worked.

Here is the code that I run:

postfile handle country_no cal_year r_squared using results, replace

forval x = 1/41 {
folval y = 1991/2023 {
reg biweek_total_ri_return lag_biweek_total_ri_return biweek_total_world_ri_return if country_no == 'x' & inlist (cal_year, 'y' 'y'-1)
local r2 = e(r2)
post handle ('x') ('y') ('r2')
}
}

postclose handle