How to use lagged data to predict next quarter return

[Jae Li]

Hi Statalists,

I would like to find out how to use the last quarter's data to predict the average return for next quarter and my codes are at following. However, it gives me very identical results as the actual average returns. Can you possibly spot out any issues with the codes? Many thanks for your time in advance!

Here is my partial data;

----------------------- copy starting from the next line -----------------------
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(quarter bygroup exret_q mktrf_q)
      84  1    .08590318 -.011087168
      84  2    .17209125 -.011087168
      84  3    .05766841 -.011087168
      84  4   .015939811 -.011087168
      84  5   -.01977126 -.011087168
      84  6     .1233931 -.011087168
      84  7    .09358002 -.011087168
      84  8    .14408113 -.011087168
      84  9    .14963517 -.011087168
      84 10    .09070672 -.011087168
      84 11     .1130465 -.011087168
      84 12  -.011868538 -.011087168
      84 13    .10994432 -.011087168
      84 14 -.0001912009 -.011087168
      84 15   .026585907 -.011087168
      84 16   -.05942601 -.011087168
    84.5  1    .05371221  -.02707004
    84.5  2   -.02868214  -.02707004
    84.5  3    .13927232  -.02707004
    84.5  4   .064015485  -.02707004
    84.5  5    .07615793  -.02707004
    84.5  6  -.026039254  -.02707004
    84.5  7    .06555213  -.02707004
    84.5  8   .032332473  -.02707004
    84.5  9     .1002892  -.02707004
    84.5 10   -.00951099  -.02707004
    84.5 11   -.02960668  -.02707004
    84.5 12    .09703401  -.02707004
    84.5 13   .002914127  -.02707004
    84.5 14    -.2539006  -.02707004
    84.5 15    .10240318  -.02707004
      85  1     .0569546  -.05559152
      85  2    .04265504   -.0530838
      85  3    .22243753   -.0530838
      85  4    .12870449  -.05021784
      85  5  -.010038707    -.052698
      85  6  -.004677856  -.04723245
      85  7  .0041046953  -.04663537
      85  8   -.13193248   -.0639506
      85  9   -.04614898  -.05559152
      85 10    .13520205  -.04663537
      85 11    -.0661721  -.05021784
      85 12     .1068162  -.05559152
      85 13   -.01922232 -.068130136
      85 14    -.0877021  -.05977106
      85 15  -.028843585 -.068130136
      85 16    .16235334  -.05559152
85.33334  5    .06810419 -.003089834
85.33334  7    .13875547 -.003089834
85.33334  8   .018073361 -.003089834
85.33334 11    .08067591 -.003089834
85.33334 12    .25473818 -.003089834
    85.5  1  -.071386494  -.07772773
    85.5  2    .01040912  -.07394295
    85.5  3   -.10528743  -.09665161
    85.5  4  -.022837397  -.06826578
    85.5  5   -.13816494  -.06421067
    85.5  6   -.07573078  -.08718967
    85.5  7   -.08642183  -.06826578
    85.5  8   .014082927 -.011494124
    85.5  9    -.0260128  -.02852562
    85.5 10  -.034601748  -.07772773
    85.5 12    .09964185 -.011494124
    85.5 13    .04836208  -.03987996
    85.5 14    -.1963301  -.05880384
    85.5 16   -.10116123  -.03987996
85.66666  1    .05991086  -.05342508
85.66666  2  -.014532907   -.0388223
85.66666  4   .019898146   -.0388223
85.66666 15   .014424063   -.0388223
   85.75  1    .11896713   -.0776314
   85.75  6     .1138752   -.0776314
   85.75  9   -.12332216   -.0776314
      86  1    -.2565459   -.1106719
      86  2   -.15351735  -.10443074
      86  3    -.1149177  -.10019606
      86  4   -.09041315  -.09986393
      86  5     -.199297  -.11455728
      86  6   -.10960802  -.10519774
      86  7   -.11579135  -.09986393
      86  8    -.0495661  -.13082646
      86  9 -.0006912335  -.09423164
      86 10    -.1576418   -.1106719
      86 11    -.1861748   -.1166594
      86 12  -.014756548   -.1178772
      86 13   -.09887826   -.0855041
      86 14   -.05815456  -.11053155
      86 15   -.21315956  -.12988606
    86.2  3  -.035441846  -.03973235
    86.2  6    .02619724  -.03973235
    86.2 15   .017663904  -.03973235
    86.2 16   -.02505945  -.03973235
   86.25  3    .10454059  -.01210286
   86.25 10   -.12598273  -.05565052
86.33334  1   -.07782044  -.04395874
86.33334  3    -.0694222   .01189532
86.33334  5    -.1541924   -.0998128
86.33334  7  -.024051307   .01189532
86.33334 10   -.10774209   -.0998128
    86.4  7    .08607323 -.069933504
end
format %tq quarter
label values bygroup bygroup

* bygroup is the double sorts portfolios built up by using last quarter's data
egen bygroup = group(P1 P2), label

levelsof bygroup, local(bygroup)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

gen yhat = .

foreach i of local bygroup {
   foreach j in quarter {
      reg exret_q mktrf_q if bygroup == `i' & quarter == `j'
  replace yhat = _b[_cons]+ _b[mktrf_q]*mktrf_q if  bygroup == `i' & quarter == `j'
  }
 }

[Phil Bromiley]

First, use predict instead of calculating yhat yourself.

Look at the sample sizes. It seems like you're estimating for each bygroup and quarter, but you only have one bygroup per quarter.

[Jae Li]

@Phil Bromiley Hi Phil, thank you for your reply! I am estimating for each bygroup within quarters, so I generally have one quarter for all available bygroups, such as in May, I have 16 bygroups of portfolios respectively.

With respect to the -predict-, do you mean something like this? Also, may I ask why not calculate yhat by myself?

* bygroup is the double sorts portfolios built up by using last quarter's data
egen bygroup = group(P1 P2), label

levelsof bygroup, local(bygroup)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

gen yhat = .

foreach i of local bygroup {
   foreach j in quarter {
      reg exret_q mktrf_q if bygroup == `i' & quarter == `j'
  predict yhat if  bygroup == `i' & quarter == `j'
  }
 }

Many thanks for your help! Hope to hear from you soon!

[Robert Picard]

As Phil has tried to explain, you only 1 observation per bygroup quarter group in your data example. Furthermore, your quarterly dates are wrong, these should not have a fractional part. Here are the first 10 by-groups of bygroup quarter:

. bysort bygroup quarter: gen N = _N

. gen q = quarter

. list in 1/10

     +----------------------------------------------------------+
     | quarter   bygroup     exret_q     mktrf_q   N          q |
     |----------------------------------------------------------|
  1. |  1981q1         1    .0859032   -.0110872   1         84 |
  2. |  1981q1         1    .0537122     -.02707   1       84.5 |
  3. |  1981q2         1    .0569546   -.0555915   1         85 |
  4. |  1981q2         1   -.0713865   -.0777277   1       85.5 |
  5. |  1981q2         1    .0599109   -.0534251   1   85.66666 |
     |----------------------------------------------------------|
  6. |  1981q2         1    .1189671   -.0776314   1      85.75 |
  7. |  1981q3         1   -.2565459   -.1106719   1         86 |
  8. |  1981q3         1   -.0778204   -.0439587   1   86.33334 |
  9. |  1981q1         2    .1720912   -.0110872   1         84 |
 10. |  1981q1         2   -.0286821     -.02707   1       84.5 |
     +----------------------------------------------------------+

.

Another problem is that you are trying to loop over quarters but these are stored in a variable. When you refer to a variable in an expression that evaluates to a single value, Stata will use the value of the first observation stored in the variable. Here's an example:

. foreach j in quarter {
  2.         dis `j'
  3. }
84

.

I'm not going to suggest how to fix this as your quarter variable is not a valid Stata quarterly date variable.

[Jae Li]

@Robert Picard Hi Robert, thank you very much for spotting out the error! I didn't realise the wrong format of quarterly variable early and the following is the corrected one for your review:

----------------------- copy starting from the next line -----------------------
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(quarter bygroup exret_q mktrf_q)
85  5  .027878605 -.04305292
85  7   .03264809 -.04305292
85 10   .04756279 -.04305292
85  6    .0486968 -.04305292
85 14  -.05973545 -.04305292
85  1   .09016822 -.04305292
85  2   .03825995 -.04305292
85 12   .04566905 -.04305292
85  9  .013468714 -.04305292
85 15 -.010703514 -.04305292
85  8   .06767512 -.04305292
85 16  .008111307 -.04305292
85  3   .05067743 -.04305292
85 13 -.018189823 -.04305292
85  4  .031343367 -.04305292
85 11 -.008222572 -.04305292
86  2  -.23517492  -.1502503
86 16   -.1631239  -.1502503
86  8  -.20188244  -.1502503
86 11    -.183737  -.1502503
86  7     -.19156  -.1502503
86  3  -.19537213  -.1502503
86 10  -.19801624  -.1502503
86  5   -.2264514  -.1502503
86  4   -.1666988  -.1502503
86  1  -.20616083  -.1502503
86 13  -.20917475  -.1502503
86 15  -.14643401  -.1502503
86 14  -.20081855  -.1502503
86  6   -.1870938  -.1502503
86 12   -.2021136  -.1502503
86  9   -.2120779  -.1502503
87  8  .035800643  .04487058
87 14   .07524122  .04487058
87  1   .05407148  .04487058
87  6   .08963605  .04487058
87 13   .07326926  .04487058
87 11   .09841517  .04487058
87  4   .08535978  .04487058
87 15   .08169516  .04487058
87  5  .012162737  .04487058
87  9    .0909082  .04487058
87 10   .05657104  .04487058
87 16   .04110672  .04487058
87 12  .065055594  .04487058
87  2  .020439627  .04487058
87  7   .09451184  .04487058
87  3   .05324217  .04487058
88 11  -.13319242 -.10613517
88  4  -.11058555 -.10613517
88 12  -.05778973 -.10613517
88  5  -.13412364 -.10613517
88 10  -.07943472 -.10613517
88  2  -.06887147 -.10613517
88 13  -.11888693 -.10613517
88  3  -.08032569 -.10613517
88 15  -.10304222 -.10613517
88  6  -.08990448 -.10613517
88  1  -.09236052 -.10613517
88 14  -.10218386 -.10613517
88  7  -.12808754 -.10613517
88 16  -.11779118 -.10613517
88  8  -.05159155 -.10613517
88  9  -.14769147 -.10613517
89 16  -.00692957 -.03914193
89  5  -.03069363 -.03914193
89  1  -.05170161 -.03914193
89 10  -.04105692 -.03914193
89 11 -.018624242 -.03914193
89  6  .015042865 -.03914193
89 14  -.01145895 -.03914193
89  2  -.05222995 -.03914193
89  8  -.01992485 -.03914193
89  3  .014073294 -.03914193
89  9  -.04995449 -.03914193
89 15 -.063926965 -.03914193
89 12   .01337484 -.03914193
89  4  -.04105905 -.03914193
89 13   -.0537366 -.03914193
89  7 -.031206956 -.03914193
90  1   .07452438  .08982605
90  4   .06875257  .08982605
90 12   .10078052  .08982605
90  9    .0541617  .08982605
90  7   .10284933  .08982605
90  5   .04372568  .08982605
90 13   .10863723  .08982605
90 16   .09129372  .08982605
90  2 -.034418512  .08982605
90  6   .11054723  .08982605
90  8   .08494767  .08982605
90 14   .13848758  .08982605
90 15   .14440021  .08982605
90 10   .11624818  .08982605
90  3   .05529123  .08982605
90 11   .08856349  .08982605
91 16   .21415834  .17138447
91  6    .2990949  .17138447
91  1     .305841  .17138447
91  8    .1729947  .17138447
end
format %tq quarter

With respect to the looping over quarters, the results turned to be "no observations" after the corrections as follows:

preserve
collapse (mean) exret_q mktrf_q, by(quarter bygroup)

levelsof quarter, local(q)
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 11
> 1 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 13
> 4 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 15
> 7 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 18
> 0 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 20
> 3 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223

 
levelsof bygroup, local(bygroup)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

foreach i of local bygroup {
 foreach j of local q {
   reg exret_q mktrf_q if bygroup == `i' & q == `j'
   replace alpha = _b[_cons] if bygroup == `i' & q == `j'
   replace  t_alpha = alpha / _se[_cons] if bygroup == `i' & q == `j'
   replace se_alpha =  _se[_cons] if  bygroup == `i' & q == `j'
}
}
no observations
r(2001);

Do you possibly know how to adjust it in order to have portfolio alphas? Many thanks for your help in advance!

[Robert Picard]

Again, with your adjusted dataset you have only one observation per bygroup quarter:

. bysort bygroup quarter: gen N = _N

. tab N

          N |      Freq.     Percent        Cum.
------------+-----------------------------------
          1 |        100      100.00      100.00
------------+-----------------------------------
      Total |        100      100.00

Your collapse will change nothing since there's only one observation per by-group.

You can't perform a regression on a single observation so you get a no observations r(2001) error. Why are you under the impression that there are multiple observations with the same value for bygroup and quarter? I do not know how this relates to your mention of portfolios.

[Jae Li]

@Robert Picard Hi Robert, thanks for your reply!

What I want is to regress exret_q on mktrf_q to calculate the portfolio alphas based on the double-sorted portfolio variable called bygroup. Then based on the current double-sorted portfolio, I'd like to predict the next quarter's portfolio alphas. Thanks for your comments! Based on that, I've made some adjustments and here is the updated code. However, the following code will stop at the second regression and said variable yhat already defined.

Afte running regressions, I wish to have one column for portfolio alphas and one column for predicted portfolio alphas for next quarter, do you possibly know how to achieve that? Many thanks for your help indeed!

  foreach j of local bygroup {
     reg exret_q mktrf_q if bygroup ==`j'
     replace alpha = _b[_cons] if bygroup ==`j'
    predict yhat if bygroup ==`j'
 }