Trouble saving regression coefficients

[Surya Ramanathan]

I have stock return data that contains the variable PERMNO (a 5 digit number that identifies a stock uniquely), RET_n_perc (individual stock's return in percentage), and MktRF (portfolio return less risk free rate). There is also a date variable, and there are multiple date observations for each PERMNO (i.e. a stock with PERMNO 10314 may have 130 different date, RET_n_perc, and MktRF observations). I am attempting to regress RET_n_perc onto MktRF for each PERMNO, and I am able to successfully do this by doing by PERMNO: regress RET_n_perc MktRF.

However, the issue arises when I try to save the coefficients from each regression. No matter what I try, I can only either save the last regression's coefficients or it will be some random values like 0's and 1's.

Any help on this would be greatly appreciated, been racking my mind over this for hours now.

[Clyde Schechter]

levelsof PERMNO, local(stocks)
gen b_MktRF = .
foreach s of local stocks {
    regress RET_n_perc MktRF if PERMNO == `s'
    replace b_MktRF = _b[MktRf] if PERMNO == `s'
}

As no example data was provided to work with, this code is untested and my contain typos or other errors. It also may prove incompatible with your actual data. Nevertheless, it contains the gist of a solution. If you are not able to adapt it to your data, be sure to provide example data when posting back for further advice. Then, and always in the future when requesting help with code, show example data.

The helpful way to do that is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Surya Ramanathan]

Hi Clyde,

Thank you for your response. It runs through a few thousand observations but then runs into an "insufficient observations error." It looks like the place where it stops only has one observation for that specific PERMNO. Including an example of the data below. There are 115,953 observations total.

* Example generated by -dataex-. For more info, type help dataex
clear
input long PERMNO float RET_n_perc double MktRF
10104    -5.7888 -3.36
10104     6.8951   3.4
10104     4.3002  6.31
10104      .8071     2
10104   -12.7477 -7.89
10104     -4.918 -5.57
10104    10.3914  6.93
10104    -7.5931 -4.77
10104    22.9114  9.54
10104     9.6089  3.88
10104    -7.9476    .6
10104     15.733  6.82
10104      2.492  1.99
10104     2.7162  3.49
10104     1.6185   .46
10104     7.7395   2.9
10104    -4.8387 -1.27
10104    -3.8282 -1.75
10104    -6.8976 -2.35
10104     -8.208 -5.99
10104     2.3869 -7.59
10104     14.231 11.35
10104    -4.3332  -.28
10104 -18.181799   .74
10104    10.2144  5.05
10104     3.7044  4.42
10104     -.3247  3.11
10104     1.0288  -.85
10104  -9.966001 -6.19
10104    12.2025  3.89
10104     1.8855   .79
10104     4.8013  2.55
10104     -.6003  2.73
10104    -1.0172 -1.76
10104     3.5232   .78
10104     4.1181  1.18
10104     6.5726  5.57
10104    -3.5764  1.29
10104    -5.5783  4.03
10104     1.3919  1.55
10104     3.0506   2.8
10104  -9.088201  -1.2
10104      5.731  5.65
10104    -1.5147 -2.71
10104     4.1117  3.77
10104     1.3567  4.18
10104     5.3433  3.13
10104      8.416  2.81
10104     -3.241 -3.32
10104     5.9892  4.65
10104     4.6024   .43
10104        .22  -.19
10104     2.7886  2.06
10104    -3.5459  2.61
10104     -.0493 -2.04
10104     2.8225  4.24
10104    -7.8257 -1.97
10104      2.325  2.52
10104     8.6044  2.55
10104     6.0363  -.06
10104    -6.5822 -3.11
10104  4.6073003  6.14
10104     -1.529 -1.12
10104     1.4368   .59
10104      -.298  1.36
10104     -7.335 -1.53
10104     -.5211  1.54
10104    -7.1357 -6.04
10104    -2.6153 -3.07
10104     7.9457  7.75
10104      .3347   .56
10104    -6.2612 -2.17
10104     -.1916 -5.77
10104     1.2944  -.08
10104    11.2289  6.96
10104    -2.1999   .92
10104       .853  1.78
10104     1.8159  -.05
10104      .6352  3.95
10104      .4386    .5
10104    -4.7065   .25
10104    -1.8075 -2.02
10104      4.607  4.86
10104    -4.3294  1.82
10104  4.7074003  1.94
10104      6.183  3.57
10104     4.7429   .17
10104     1.2105  1.09
10104      .9564  1.06
10104    10.4649   .78
10104     -.0399  1.87
10104      .8011   .16
10104     -3.934  2.51
10104      5.667  2.25
10104    -3.6149  3.12
10104    -3.6282  1.06
10104     9.5178  5.58
10104    -1.7833 -3.65
10104    -9.7099 -2.35
10104      .2404   .29
end

Thanks.

[Clyde Schechter]

Yes, that is a commonly encountered difficulty with this kind of analysis. The following code will skip over those PERMNO's where there are insufficient values for carrying out the regression, continuing on to the next PERMNO. Any other kind of error arising during the regression, however, will result in an error message and termination of execution.

levelsof PERMNO, local(stocks)
gen b_MktRF = .
foreach s of local stocks {
    capture noisily regress RET_n_perc MktRF if PERMNO == `s'
    if c(rc) == 0 { // SUCCESSFUL REGRESION; SAVE THE RESULT
        replace b_MktRF = _b[MktRF] if PERMNO == `s'
    }
    else if !inlist(c(rc), 2000, 2001) { // UNANTICIPATED ERROR; NOTIFY & QUIT
        display as error "Unexpected error in regress: PERMNO == `s'"
        exit c(rc)
    }
}

If your data set is large, running all these regressions will produce a lot of output that you probably don't have any use for. You can suppress that by removing -noisily- from the -regress- command.

[Surya Ramanathan]

Got it, that worked well!

Now, I'm trying to regress monthly returns on mktrf. Here's what I have.

gen market_beta = .
levelsof year, local(years)
foreach year of local years {
     forvalues month = 1/12 {
          reg RET_n_perc MktRF if year == `year' & month == `month'
          replace market_beta = _b[MktRF] if year == `year' & month == `month'
     }
}

For some reason when I do this, my coefficients are coming out as zero.

[Clyde Schechter]

Well, depending on your data, there is nothing obviously wrong with your code. It is more likely that the data are either wrong or simply not suited to the code or to the problem you are trying to solve. Your earlier example data does not have month and year variables, so it is impossible to say anything more specific, but it is likely that the difficulty is arising from them. Please post back with a new data example (using -dataex-, of course) that includes all variables that are mentioned in your code.

[Surya Ramanathan]

Apologies, data example included below.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(year month RET_n_perc) double MktRF
2010  1    -5.7888 -3.36
2010  2     6.8951   3.4
2010  3     4.3002  6.31
2010  4      .8071     2
2010  5   -12.7477 -7.89
2010  6     -4.918 -5.57
2010  7    10.3914  6.93
2010  8    -7.5931 -4.77
2010  9    22.9114  9.54
2010 10     9.6089  3.88
2010 11    -7.9476    .6
2010 12     15.733  6.82
2011  1      2.492  1.99
2011  2     2.7162  3.49
2011  3     1.6185   .46
2011  4     7.7395   2.9
2011  5    -4.8387 -1.27
2011  6    -3.8282 -1.75
2011  7    -6.8976 -2.35
2011  8     -8.208 -5.99
2011  9     2.3869 -7.59
2011 10     14.231 11.35
2011 11    -4.3332  -.28
2011 12 -18.181799   .74
2012  1    10.2144  5.05
2012  2     3.7044  4.42
2012  3     -.3247  3.11
2012  4     1.0288  -.85
2012  5  -9.966001 -6.19
2012  6    12.2025  3.89
2012  7     1.8855   .79
2012  8     4.8013  2.55
2012  9     -.6003  2.73
2012 10    -1.0172 -1.76
2012 11     3.5232   .78
2012 12     4.1181  1.18
2013  1     6.5726  5.57
2013  2    -3.5764  1.29
2013  3    -5.5783  4.03
2013  4     1.3919  1.55
2013  5     3.0506   2.8
2013  6  -9.088201  -1.2
2013  7      5.731  5.65
2013  8    -1.5147 -2.71
2013  9     4.1117  3.77
2013 10     1.3567  4.18
2013 11     5.3433  3.13
2013 12      8.416  2.81
2014  1     -3.241 -3.32
2014  2     5.9892  4.65
2014  3     4.6024   .43
2014  4        .22  -.19
2014  5     2.7886  2.06
2014  6    -3.5459  2.61
2014  7     -.0493 -2.04
2014  8     2.8225  4.24
2014  9    -7.8257 -1.97
2014 10      2.325  2.52
2014 11     8.6044  2.55
2014 12     6.0363  -.06
2015  1    -6.5822 -3.11
2015  2  4.6073003  6.14
2015  3     -1.529 -1.12
2015  4     1.4368   .59
2015  5      -.298  1.36
2015  6     -7.335 -1.53
2015  7     -.5211  1.54
2015  8    -7.1357 -6.04
2015  9    -2.6153 -3.07
2015 10     7.9457  7.75
2015 11      .3347   .56
2015 12    -6.2612 -2.17
2016  1     -.1916 -5.77
2016  2     1.2944  -.08
2016  3    11.2289  6.96
2016  4    -2.1999   .92
2016  5       .853  1.78
2016  6     1.8159  -.05
2016  7      .6352  3.95
2016  8      .4386    .5
2016  9    -4.7065   .25
2016 10    -1.8075 -2.02
2016 11      4.607  4.86
2016 12    -4.3294  1.82
2010  1    -7.5459 -3.36
2010  2     2.2001   3.4
2010  3     2.1538  6.31
2010  4     4.2595     2
2010  5   -15.0811 -7.89
2010  6    -10.814 -5.57
2010  7    12.1686  6.93
2010  8    -8.5819 -4.77
2010  9  4.3682003  9.54
2010 10     8.8812  3.88
2010 11    -4.6784    .6
2010 12    10.5018  6.82
2011  1     -.6628  1.99
2011  2    -3.5528  3.49
2011  3    -4.4771   .46
2011  4     2.0874   2.9
end

[Clyde Schechter]

So, in your example data, there are 16 year-month combinations in which you have more than one observation. The others will encounter the insufficient observations problem, and you cannot get a regression result for these. Now, for those 16, you should, in principle, be able to do a regression even though there are only two observations. BUT, at least in your example data, the variable MktRF is constant within the year-month combination. When you regress against a constant, the coefficient is, of course, going to be zero.

So the problem is that the data you are showing are simply not suitable for this kind of analysis. As I do not work in finance, I don't know whether this means you are doing an analysis that is inappropriate or whether the analysis is appropriate but the data you are using are wrong or unsuitable for it.

By the way, although it is not relevant for present purposes, when you want to do monthly data analysis, you are better off using a true Stata monthly date variable than separate year and month variables:

gen mdate = ym(year, month)
format mdate %tm
levelsof mdate, local(months)
foreach m of local months {
    capture reg RET_n_perc MktRF if mdate == `m'
    if c(rc) == 0 {
        replace market_beta = _b[MktRF] if mdate == `m'
    }
    else if !inlist(c(rc), 2000, 2001) {
        display as error "Unexpected error in regress: mdate = " %tm =`m'
    }
}

(But, of course, until you fix the problem with the data, this will only give you zero coefficients.)

[Surya Ramanathan]

So the next set of data that restarts with 2010/1 is for a different company. The data including tickers is like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input double MktRF float(RET_n_perc year month) str5 TICKER
-3.36    -5.7888 2010  1 "ORCL"
  3.4     6.8951 2010  2 "ORCL"
 6.31     4.3002 2010  3 "ORCL"
    2      .8071 2010  4 "ORCL"
-7.89   -12.7477 2010  5 "ORCL"
-5.57     -4.918 2010  6 "ORCL"
 6.93    10.3914 2010  7 "ORCL"
-4.77    -7.5931 2010  8 "ORCL"
 9.54    22.9114 2010  9 "ORCL"
 3.88     9.6089 2010 10 "ORCL"
   .6    -7.9476 2010 11 "ORCL"
 6.82     15.733 2010 12 "ORCL"
 1.99      2.492 2011  1 "ORCL"
 3.49     2.7162 2011  2 "ORCL"
  .46     1.6185 2011  3 "ORCL"
  2.9     7.7395 2011  4 "ORCL"
-1.27    -4.8387 2011  5 "ORCL"
-1.75    -3.8282 2011  6 "ORCL"
-2.35    -6.8976 2011  7 "ORCL"
-5.99     -8.208 2011  8 "ORCL"
-7.59     2.3869 2011  9 "ORCL"
11.35     14.231 2011 10 "ORCL"
 -.28    -4.3332 2011 11 "ORCL"
  .74 -18.181799 2011 12 "ORCL"
 5.05    10.2144 2012  1 "ORCL"
 4.42     3.7044 2012  2 "ORCL"
 3.11     -.3247 2012  3 "ORCL"
 -.85     1.0288 2012  4 "ORCL"
-6.19  -9.966001 2012  5 "ORCL"
 3.89    12.2025 2012  6 "ORCL"
  .79     1.8855 2012  7 "ORCL"
 2.55     4.8013 2012  8 "ORCL"
 2.73     -.6003 2012  9 "ORCL"
-1.76    -1.0172 2012 10 "ORCL"
  .78     3.5232 2012 11 "ORCL"
 1.18     4.1181 2012 12 "ORCL"
 5.57     6.5726 2013  1 "ORCL"
 1.29    -3.5764 2013  2 "ORCL"
 4.03    -5.5783 2013  3 "ORCL"
 1.55     1.3919 2013  4 "ORCL"
  2.8     3.0506 2013  5 "ORCL"
 -1.2  -9.088201 2013  6 "ORCL"
 5.65      5.731 2013  7 "ORCL"
-2.71    -1.5147 2013  8 "ORCL"
 3.77     4.1117 2013  9 "ORCL"
 4.18     1.3567 2013 10 "ORCL"
 3.13     5.3433 2013 11 "ORCL"
 2.81      8.416 2013 12 "ORCL"
-3.32     -3.241 2014  1 "ORCL"
 4.65     5.9892 2014  2 "ORCL"
  .43     4.6024 2014  3 "ORCL"
 -.19        .22 2014  4 "ORCL"
 2.06     2.7886 2014  5 "ORCL"
 2.61    -3.5459 2014  6 "ORCL"
-2.04     -.0493 2014  7 "ORCL"
 4.24     2.8225 2014  8 "ORCL"
-1.97    -7.8257 2014  9 "ORCL"
 2.52      2.325 2014 10 "ORCL"
 2.55     8.6044 2014 11 "ORCL"
 -.06     6.0363 2014 12 "ORCL"
-3.11    -6.5822 2015  1 "ORCL"
 6.14  4.6073003 2015  2 "ORCL"
-1.12     -1.529 2015  3 "ORCL"
  .59     1.4368 2015  4 "ORCL"
 1.36      -.298 2015  5 "ORCL"
-1.53     -7.335 2015  6 "ORCL"
 1.54     -.5211 2015  7 "ORCL"
-6.04    -7.1357 2015  8 "ORCL"
-3.07    -2.6153 2015  9 "ORCL"
 7.75     7.9457 2015 10 "ORCL"
  .56      .3347 2015 11 "ORCL"
-2.17    -6.2612 2015 12 "ORCL"
-5.77     -.1916 2016  1 "ORCL"
 -.08     1.2944 2016  2 "ORCL"
 6.96    11.2289 2016  3 "ORCL"
  .92    -2.1999 2016  4 "ORCL"
 1.78       .853 2016  5 "ORCL"
 -.05     1.8159 2016  6 "ORCL"
 3.95      .6352 2016  7 "ORCL"
   .5      .4386 2016  8 "ORCL"
  .25    -4.7065 2016  9 "ORCL"
-2.02    -1.8075 2016 10 "ORCL"
 4.86      4.607 2016 11 "ORCL"
 1.82    -4.3294 2016 12 "ORCL"
-3.36    -7.5459 2010  1 "MSFT"
  3.4     2.2001 2010  2 "MSFT"
 6.31     2.1538 2010  3 "MSFT"
    2     4.2595 2010  4 "MSFT"
-7.89   -15.0811 2010  5 "MSFT"
-5.57    -10.814 2010  6 "MSFT"
 6.93    12.1686 2010  7 "MSFT"
-4.77    -8.5819 2010  8 "MSFT"
 9.54  4.3682003 2010  9 "MSFT"
 3.88     8.8812 2010 10 "MSFT"
   .6    -4.6784 2010 11 "MSFT"
 6.82    10.5018 2010 12 "MSFT"
 1.99     -.6628 2011  1 "MSFT"
 3.49    -3.5528 2011  2 "MSFT"
  .46    -4.4771 2011  3 "MSFT"
  2.9     2.0874 2011  4 "MSFT"
end

Let me know if this helps in any way.

[Clyde Schechter]

You still have the same problem. Putting the data examples you have shown together, it's still true that for any given year and month, the value of MktRF is the same for all observations in that month. So when you regress anything on MktRF, the coefficient will be zero. That's what happens when you regress on a constant.

I think you need to consult with somebody in your discipline for help with this. Either it is inappropriate to try to do this kind of month-by-month regression, or, if it is appropriate, then you need some different MktRF variable to use--one that actually varies within months. Somebody who understands finance can tell you which it is. (If you wait a while, there are plenty of financially knowledgeable people in this Forum and one of them might chime in.)