calculating beta of multiple firms per year

[Arnout Poelstra]

Dear Statawizards,

I would like to calculate the beta's of 1079 firms by year. The dataset contains daily stock returns from 2003 untill 2015. Below I uploaded a part of dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 company_id int date float year double(firm_return return_market)
"0013" 19780 2014    .32630000000000003                -.00685
"0013" 19781 2014                -.0291                -.00067
"0013" 19782 2014                -.0327    .011200000000000002
"0013" 19785 2014  -.005600000000000001                -.02511
"0013" 19786 2014                 .0113                 .01807
"0013" 19787 2014 -.0028000000000000004                -.00066
"0013" 19788 2014   .005600000000000001                  .0077
"0013" 19789 2014   .053100000000000015   -.011080000000000001
"0013" 19792 2014  -.018600000000000002  -.0035100000000000005
"0013" 19793 2014                -.0304  -.0008700000000000001
"0013" 19794 2014   .025099999999999997                -.00808
"0013" 19795 2014                -.0109                -.00904
"0013" 19796 2014  -.051500000000000004                -.00711
"0013" 19799 2014                 .0203    .010780000000000001
"0013" 19800 2014                -.0057                 .00549
"0013" 19801 2014                 .0171                -.00118
"0013" 19802 2014                -.0169                -.00868
"0013" 19803 2014                  -.05                 .00111
"0013" 19806 2014                -.0263                -.01034
"0013" 19807 2014                 -.017                 .01248
"0013" 19808 2014                -.0416                 .00738
"0013" 19809 2014                -.0016  -.0017200000000000002
"0013" 19810 2014                -.0074                 .00845
"0013" 19813 2014   .038900000000000004   .0035100000000000005
"0013" 19814 2014                 .0215   .0066500000000000005
"0013" 19815 2014                -.0031 -.00035000000000000005
"0013" 19816 2014                 .0063  -.0025800000000000003
"0013" 19817 2014                -.0365   .0040100000000000005
"0013" 19820 2014                -.0484                -.00872
"0013" 19821 2014                -.0508   .0017400000000000002
"0013" 19822 2014                 .0089    .005620000000000001
"0013" 19823 2014                -.0088                -.00121
"0013" 19824 2014                -.0438                -.01298
"0013" 19827 2014  -.028900000000000002                -.00058
"0013" 19828 2014                -.0692                -.00881
"0013" 19829 2014   .014499999999999999                 .01109
"0013" 19830 2014                -.0143   .0066500000000000005
"0013" 19831 2014                     0                      0
"0013" 19834 2014                     0  -.0018599999999999999
"0013" 19835 2014                  .064    .012410000000000001
"0013" 19836 2014                -.0019                -.00313
"0013" 19837 2014  -.038900000000000004   .0028200000000000005
"0013" 19838 2014  -.028300000000000002                -.00634
"0013" 19841 2014  -.013500000000000002   .0028100000000000004
"0013" 19842 2014                 .0137                 .01007
"0013" 19843 2014                -.0167                 .00309
"0013" 19844 2014                     0                  .0016
"0013" 19845 2014                     0                -.00252
"0013" 19848 2014                     0                -.00154
"0013" 19849 2014                -.0042                 .00119
"0013" 19850 2014                     0 -.00037999999999999997
"0013" 19851 2014                -.0085                 .00616
"0013" 19852 2014                -.0215   -.010409999999999999
"0013" 19855 2014                -.0351                 .00681
"0013" 19856 2014                  .017                -.00059
"0013" 19857 2014   .032400000000000005 -.00021999999999999998
"0013" 19858 2014                 .0195                -.00749
"0013" 19859 2014                -.0202   .0013700000000000001
"0013" 19862 2014                 .0054 -.00043000000000000004
"0013" 19863 2014  -.051699999999999996                -.00295
"0013" 19864 2014                 .0409   .0032500000000000003
"0013" 19865 2014                 .0775                 .00078
"0013" 19866 2014                -.0111                  .0001
"0013" 19869 2014                     0    .007020000000000001
"0013" 19870 2014  .0040999999999999995  -.0008700000000000001
"0013" 19871 2014   .022400000000000003                -.00216
"0013" 19872 2014                 .0559                 .00217
"0013" 19873 2014                 .0208                 .00114
"0013" 19876 2014                -.0037                -.00048
"0013" 19877 2014                -.0009                -.00333
"0013" 19878 2014                -.0279                 .00004
"0013" 19879 2014                -.0507                 .00333
"0013" 19880 2014                 .0111                 .00795
"0013" 19883 2014                 -.003                 .00034
"0013" 19884 2014                  .024                -.00016
"0013" 19885 2014   .015600000000000001                 -.0062
"0013" 19886 2014                  .024   .0017000000000000001
"0013" 19887 2014                 .0141                -.00319
"0013" 19890 2014                -.0111                -.00113
"0013" 19891 2014                -.0169                   .001
"0013" 19892 2014                -.0086                 .00111
"0013" 19893 2014                 .0067    .010130000000000002
"0013" 19894 2014                 .0105                -.00423
"0013" 19897 2014                -.0161                -.00364
"0013" 19898 2014                 .0111                -.00148
"0013" 19899 2014                 .0015   -.008020000000000001
"0013" 19900 2014                 .0197                -.00392
"0013" 19901 2014                 .0082                 .00412
"0013" 19904 2014  -.050499999999999996                 .00332
"0013" 19905 2014   .048500000000000015                  .0079
"0013" 19906 2014  -.009300000000000001                  .0003
"0013" 19907 2014                -.0052                 .00594
"0013" 19908 2014                -.0004                -.00426
"0013" 19911 2014   -.07520000000000002                -.00834
"0013" 19912 2014                -.0341                -.01224
"0013" 19913 2014                -.0168                  .0021
"0013" 19914 2014                 .0094                -.01324
"0013" 19915 2014                -.0288                 .00144
"0013" 19918 2014                 .0009                 .01056
"0013" 19919 2014                 .0087                -.00771
end
format %tdnn/dd/CCYY date

I think I have to regress something by company_id and year. Does anybody know which loop/command I should use?

Many thanks in advance,

Arnout

[Maarten Buis]

See help statsby

[Clyde Schechter]

Actually this will run much faster with -rangestat-. (-ssc install rangestat-)

Note: If you previously installed -rangestat-, check that you have the latest version, version 1.1.1. The original -rangestat- required you to write a short Mata program to do regression. But the current version has built-in regression. So if you installed -rangestat- before 9may2017, get the update.

As an aside:

I would like to calculate the beta's of 1079 firms by year.

This is finance jargon. I've seen it used in context enough times here on Statalist that I know what is meant by it now: regressing the firm's return against the overall market return. But this is a multi-disciplinary forum. While the finance community is heavily represented, there are plenty of people who might offer help who will pass this over because they don't know what you're talking about. So it's best to phrase your problem in statistical terms without relying on disciplinary jargon.

[Arnout Poelstra]

Hello Clyde,

I am sorry about my Finance jargon. In addition to my previous dataset I uploaded I created an id by: egen id = group(company_id year)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 company_id int date float year double(firm_return market_return)    float    id
"0013" 19780 2014    .32630000000000003                -.00685 1
"0013" 19781 2014                -.0291                -.00067 1
"0013" 19782 2014                -.0327    .011200000000000002 1
"0013" 19785 2014  -.005600000000000001                -.02511 1
"0013" 19786 2014                 .0113                 .01807 1
"0013" 19787 2014 -.0028000000000000004                -.00066 1
"0013" 19788 2014   .005600000000000001                  .0077 1
"0013" 19789 2014   .053100000000000015   -.011080000000000001 1
"0013" 19792 2014  -.018600000000000002  -.0035100000000000005 1
"0013" 19793 2014                -.0304  -.0008700000000000001 1
"0013" 19794 2014   .025099999999999997                -.00808 1
"0013" 19795 2014                -.0109                -.00904 1
"0013" 19796 2014  -.051500000000000004                -.00711 1
"0013" 19799 2014                 .0203    .010780000000000001 1
"0013" 19800 2014                -.0057                 .00549 1
"0013" 19801 2014                 .0171                -.00118 1
"0013" 19802 2014                -.0169                -.00868 1
"0013" 19803 2014                  -.05                 .00111 1
"0013" 19806 2014                -.0263                -.01034 1
"0013" 19807 2014                 -.017                 .01248 1
"0013" 19808 2014                -.0416                 .00738 1
"0013" 19809 2014                -.0016  -.0017200000000000002 1
"0013" 19810 2014                -.0074                 .00845 1
"0013" 19813 2014   .038900000000000004   .0035100000000000005 1
"0013" 19814 2014                 .0215   .0066500000000000005 1
"0013" 19815 2014                -.0031 -.00035000000000000005 1
"0013" 19816 2014                 .0063  -.0025800000000000003 1
"0013" 19817 2014                -.0365   .0040100000000000005 1
"0013" 19820 2014                -.0484                -.00872 1
"0013" 19821 2014                -.0508   .0017400000000000002 1
"0013" 19822 2014                 .0089    .005620000000000001 1
"0013" 19823 2014                -.0088                -.00121 1
"0013" 19824 2014                -.0438                -.01298 1
"0013" 19827 2014  -.028900000000000002                -.00058 1
"0013" 19828 2014                -.0692                -.00881 1
"0013" 19829 2014   .014499999999999999                 .01109 1
"0013" 19830 2014                -.0143   .0066500000000000005 1
"0013" 19831 2014                     0                      0 1
"0013" 19834 2014                     0  -.0018599999999999999 1
"0013" 19835 2014                  .064    .012410000000000001 1
"0013" 19836 2014                -.0019                -.00313 1
"0013" 19837 2014  -.038900000000000004   .0028200000000000005 1
"0013" 19838 2014  -.028300000000000002                -.00634 1
"0013" 19841 2014  -.013500000000000002   .0028100000000000004 1
"0013" 19842 2014                 .0137                 .01007 1
"0013" 19843 2014                -.0167                 .00309 1
"0013" 19844 2014                     0                  .0016 1
"0013" 19845 2014                     0                -.00252 1
"0013" 19848 2014                     0                -.00154 1
"0013" 19849 2014                -.0042                 .00119 1
"0013" 19850 2014                     0 -.00037999999999999997 1
"0013" 19851 2014                -.0085                 .00616 1
"0013" 19852 2014                -.0215   -.010409999999999999 1
"0013" 19855 2014                -.0351                 .00681 1
"0013" 19856 2014                  .017                -.00059 1
"0013" 19857 2014   .032400000000000005 -.00021999999999999998 1
"0013" 19858 2014                 .0195                -.00749 1
"0013" 19859 2014                -.0202   .0013700000000000001 1
"0013" 19862 2014                 .0054 -.00043000000000000004 1
"0013" 19863 2014  -.051699999999999996                -.00295 1
"0013" 19864 2014                 .0409   .0032500000000000003 1
"0013" 19865 2014                 .0775                 .00078 1
"0013" 19866 2014                -.0111                  .0001 1
"0013" 19869 2014                     0    .007020000000000001 1
"0013" 19870 2014  .0040999999999999995  -.0008700000000000001 1
"0013" 19871 2014   .022400000000000003                -.00216 1
"0013" 19872 2014                 .0559                 .00217 1
"0013" 19873 2014                 .0208                 .00114 1
"0013" 19876 2014                -.0037                -.00048 1
"0013" 19877 2014                -.0009                -.00333 1
"0013" 19878 2014                -.0279                 .00004 1
"0013" 19879 2014                -.0507                 .00333 1
"0013" 19880 2014                 .0111                 .00795 1
"0013" 19883 2014                 -.003                 .00034 1
"0013" 19884 2014                  .024                -.00016 1
"0013" 19885 2014   .015600000000000001                 -.0062 1
"0013" 19886 2014                  .024   .0017000000000000001 1
"0013" 19887 2014                 .0141                -.00319 1
"0013" 19890 2014                -.0111                -.00113 1
"0013" 19891 2014                -.0169                   .001 1
"0013" 19892 2014                -.0086                 .00111 1
"0013" 19893 2014                 .0067    .010130000000000002 1
"0013" 19894 2014                 .0105                -.00423 1
"0013" 19897 2014                -.0161                -.00364 1
"0013" 19898 2014                 .0111                -.00148 1
"0013" 19899 2014                 .0015   -.008020000000000001 1
"0013" 19900 2014                 .0197                -.00392 1
"0013" 19901 2014                 .0082                 .00412 1
"0013" 19904 2014  -.050499999999999996                 .00332 1
"0013" 19905 2014   .048500000000000015                  .0079 1
"0013" 19906 2014  -.009300000000000001                  .0003 1
"0013" 19907 2014                -.0052                 .00594 1
"0013" 19908 2014                -.0004                -.00426 1
"0013" 19911 2014   -.07520000000000002                -.00834 1
"0013" 19912 2014                -.0341                -.01224 1
"0013" 19913 2014                -.0168                  .0021 1
"0013" 19914 2014                 .0094                -.01324 1
"0013" 19915 2014                -.0288                 .00144 1
"0013" 19918 2014                 .0009                 .01056 1
"0013" 19919 2014                 .0087                -.00771 1
end
format %tdnn/dd/CCYY date

Now I am wondering about one last thing. I think I am pretty close with regressing the firm's return against the overall market return. I used the following command:

rangestat (reg) firm_return market_return, interval(id 0 0)

However, I am not sure if I should swap firm_return and market return so the command would look like this: rangestat (reg) market_return firm_return, interval(id 0 0).

I thought normally the dependent variable should be written down first?

Many thanks,

Arnout

[Clyde Schechter]

Yes, the dependent variable should be written first. I leave it to you as to which variable is which here.

I should point out that the -rangestat- command you propose will not do it by firm by year, it will just do it by firm. If you want a separate regression for each year in each firm, you have to add a -by(year)- option.