Do the regression for only one company each and saving them to an excel/doc (ASDOC)

[Marius Bauer]

Hi everybody,

I do have a panel dataset where every company has a unique ID (-company-) and their returns (-ri). I do run my regression with STATA and fix my ID over the time period with this code:

xtset company monthly_date, monthly
xtreg ri smb_5 hml rmw cma mktminusrf logdiff_fintech_funding,fe

However, is there a method where I can run each regression by single ID. For example I want only ID == 1 and then 2 and so on

I guess/know that I have to use the normal reg command for this because holding one company fix would not make sense. The normal pooled OLS regression (with robust) would be this

reg ri smb_5 hml rmw cma mktminusrf logdiff_fintech_funding, vce(robust)

Is it now possible to run for each company a single regression and more preverably save the coef. and standard error to an doc/excel? I know ASDOC but as fas as I know ASDOC saves the whole regression to a word document, I would only need the coef. and standard error of logdiff_fintech_funding.

I could upload the data in wide format and run each regression for each company but maybe there is a way to do it in long format.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double logdiff_fintech_funding float monthly_date double(mktminusrf smb_5 hml rmw cma) byte company double ri
-2.428878818318338 601   3.4  1.53  2.74  -.55  1.43 1  1.079242090318634
 4.750675543873377 602  6.31  1.85  2.01   -.9  1.67 1   7.86393930364869
-1.547824874555269 603     2  5.03  3.12   .49  1.69 1  7.512007394694056
-2.377775945715615 604 -7.89  -.08 -2.32  1.38  -.18 1 -7.985979824222703
 2.033946504409595 605 -5.56 -2.59 -4.27  -.34 -1.48 1 -6.335626764257626
 1.866966321771243 606  6.93   .13   .04   .32  2.03 1  2.881855746293185
-6.230064946559605 607 -4.77 -3.07 -1.51   .34 -2.13 1  -9.47372995462771
 5.650530448089943 608  9.54  3.71 -2.94  -.01   .39 1  6.938139934459893
  .453532778924937 609  3.88   .72 -2.23  1.46  -.16 1  -1.05813682753868
-2.051943886966359 610    .6  3.54  -.58   -.1  1.76 1 -.2466604777739863
 2.453635974122106 611  6.82  1.03  3.47 -3.44  3.44 1  14.13006373515285
-2.555251514231911 612  1.99 -2.38   .68 -1.07    .8 1 -.7969297791341908
-.3940574407002404 613  3.49  1.76  1.73 -1.76   .72 1  .5468279827189715
 .9019179029163964 614   .45  2.66 -1.16  1.21  -.03 1  .9523648032545907
-3.366182863817053 615   2.9  -.41 -2.15   .96 -1.28 1  .5892439640544306
 3.801343710610448 616 -1.27  -.69 -2.12  2.02 -1.46 1  -1.49001583687659
 .5375060083539553 617 -1.75   .09  -.26  2.16  -1.4 1 -2.242232233723338
-.9872492784416207 618 -2.36 -1.38 -1.18  2.41 -1.75 1 -3.803201280158997
 .6191782687064706 619 -5.99 -3.39 -1.58  2.79  -.23 1 -10.01027367894514
  .263701731990106 620 -7.59  -3.9  -.98  1.71   .24 1 -9.691976652945483
-1.255992779152897 621 11.35  3.72  -.96 -1.42  -.86 1  14.10180765195524
-7.328765598425764 622  -.28  -.34  -.18  1.46  1.52 1 -.7010521563359489
 6.918695219020471 623   .74  -.36  1.57   .59  2.44 1   2.80698102953636
 1.664472961319299 624  5.05  2.35 -2.14 -1.05 -1.41 1  4.812300227465119
-4.488823618117669 625  4.42 -1.54   .01  -.17  -.03 1  4.929256079524911
 2.808398174936492 626  3.11   -.3  -.06   .25   .77 1  6.134441796438536
-.1262357447864098 627  -.85  -.66   -.2   .96   .72 1 -.6589440873023984
 .4387329863579144 628 -6.19   -.2   .08  1.98  2.37 1 -5.232003502156303
 .6944331180936318 629  3.89   .99   .54 -1.48   .37 1  3.161005632020767
-.8876966737515692 630   .79 -2.74   .01   .68   .12 1 -2.194379715301951
 1.840011420252095 631  2.55   .61    .6  -.77  -.69 1  3.209782285250071
-.6554042290035031 632  2.73   .69  1.56 -1.14  1.57 1  3.744035044489593
-.2497564922306408 633 -1.76   -.8  4.16 -1.35  2.28 1 -.0112728353654932
-2.997485122381406 634   .78   .41 -1.12   .94   .93 1 -1.707135468131021
 2.968343045907073 635  1.18  1.91  3.26 -1.75   .88 1  3.042648155235065
 .2701289866054237 636  5.57   .57  1.34 -1.88  1.47 1  4.353588346029981
 .1509605075700726 637  1.29  -.35   .28  -.96   .49 1  1.795811335142117
-1.062582825088892 638  4.03    .9  -.07   .13  1.21 1  4.731694854308732
 2.455217987516834 639  1.56 -2.32   .35   .04   .39 1 -1.228710327935455
-.6101666516017357 640   2.8  2.27  1.33  -.71  -.83 1  4.940060638818191
-.8187397318572058 641  -1.2  1.33   -.4  -.47   .01 1  2.524169010641897
 .1706158923222114 642  5.65  1.81   .71 -1.43   .53 1  7.810874448103843
-.7756070164821449 643 -2.71  -.03 -2.48   .85 -2.13 1 -3.497821482444668
 1.726891305749435 644  3.77  2.72 -1.57   -.1 -1.32 1  1.662269053409684
 .3957065823815764 645  4.18 -1.57  1.36  2.83   .89 1  3.530331927754367
-1.548639224836185 646  3.12  1.47  -.38   .77   .12 1  5.958017535781948
-1.181443691265908 647  2.81  -.44   -.2  -.57   .07 1  1.758179600545098
-.0883624242566112 648 -3.32   .56 -1.88  -4.5 -1.42 1 -3.509387301462624
 1.654120853093416 649  4.65   .16  -.49  -.49   -.4 1  2.497577726394331
 .7675383150229624 650   .43 -1.23   4.6  1.76  1.91 1  4.418488481502361
 .7337002959205572 651  -.19 -4.21  1.62  2.85  1.09 1 -5.144931450627418
-1.300090055657543 652  2.06 -1.83  -.38   .45 -1.09 1 -.1524946883270472
 3.226876440418037 653  2.61  3.04   -.6  -1.9  -1.9 1  5.056250663074426
-1.639863325627885 654 -2.04 -4.16   .04  1.48   .44 1 -3.606526297369947
-1.073779734353744 655  4.23    .3  -.76  -.91  -.65 1  2.334665259623833
-.7707598557843998 656 -1.97  -3.8 -1.68  1.28  -.62 1 -2.045957417917509
 .1447530739194685 657  2.52  3.79 -1.81  -.78  -.18 1  3.921821214508064
 .1178356353986567 658  2.55 -2.27 -3.37  1.69   .15 1  .2556962153253985
 2.635474229565091 659  -.06  2.85  1.56 -1.52   .81 1  1.699910054766814
-3.078890081227631 660 -3.11  -.91 -3.06  1.09 -1.67 1 -9.063475876310159
 .4817352432663817 661  6.13   .35 -2.16   .06 -1.62 1  7.821358748819074
 .2211978151041967 662 -1.12  3.07  -.73   .16  -.54 1  1.178161237891585
 1.991202305673274 663   .59 -2.99  2.13   .41  -.49 1  1.169923120498791
-1.329517653461937 664  1.36   .85  -1.9 -1.54  -.68 1  2.282959490396854
   .01391606423699 665 -1.53  2.88 -1.04  1.03 -1.51 1  4.042932941219418
 .3699056815049282 666  1.54  -4.5 -4.49   .31  -2.6 1 -.4668957188768061
-.1181539396678257 667 -6.04   .38  2.88   .75  1.14 1 -5.438906417704657
 .1582819462867526 668 -3.07 -2.81   .73  1.66   -.5 1  -1.03380138198852
 1.965128759761292 669  7.75 -2.05  -.32  1.19   .45 1  4.103701862358857
-2.397516434496359 670   .56  3.35 -1.23 -2.11    -1 1  4.884271693531828
-2.359402434240175 671 -2.17    -3 -2.07   .45   .17 1 -5.998214832502895
 1.646352401779502 672 -5.77 -3.56  3.13  2.27     3 1 -9.570953046717952
-.8583285517720807 673  -.07   .87  -.03  2.44  2.09 1 -3.224477640041395
 1.201286271998179 674  6.96  1.01   1.3   .58   .07 1  7.358411454974729
-2.428878818318338 601   3.4  1.53  2.74  -.55  1.43 2  -14.9038461538462
 4.750675543873377 602  6.31  1.85  2.01   -.9  1.67 2  8.772742681047772
-1.547824874555269 603     2  5.03  3.12   .49  1.69 2  5.619839471199241
-2.377775945715615 604 -7.89  -.08 -2.32  1.38  -.18 2 -12.43110815111477
 2.033946504409595 605 -5.56 -2.59 -4.27  -.34 -1.48 2 -6.662716462092019
 1.866966321771243 606  6.93   .13   .04   .32  2.03 2 -18.01929469655549
-6.230064946559605 607 -4.77 -3.07 -1.51   .34 -2.13 2 -13.03805396069644
 5.650530448089943 608  9.54  3.71 -2.94  -.01   .39 2  12.93086977856836
  .453532778924937 609  3.88   .72 -2.23  1.46  -.16 2 -6.991575267138257
-2.051943886966359 610    .6  3.54  -.58   -.1  1.76 2 -2.587763505524829
 2.453635974122106 611  6.82  1.03  3.47 -3.44  3.44 2    25.826417302189
-2.555251514231911 612  1.99 -2.38   .68 -1.07    .8 2 -1.953753199899981
-.3940574407002404 613  3.49  1.76  1.73 -1.76   .72 2  1.908535113442489
 .9019179029163964 614   .45  2.66 -1.16  1.21  -.03 2 -2.394001810935897
-3.366182863817053 615   2.9  -.41 -2.15   .96 -1.28 2 -12.29777807612272
 3.801343710610448 616 -1.27  -.69 -2.12  2.02 -1.46 2 -5.240232404411508
 .5375060083539553 617 -1.75   .09  -.26  2.16  -1.4 2 -3.270575104270688
-.9872492784416207 618 -2.36 -1.38 -1.18  2.41 -1.75 2  9.105746602899874
 .6191782687064706 619 -5.99 -3.39 -1.58  2.79  -.23 2 -16.62784658201427
  .263701731990106 620 -7.59  -3.9  -.98  1.71   .24 2 -22.14350681319415
-1.255992779152897 621 11.35  3.72  -.96 -1.42  -.86 2  11.27604389951129
-7.328765598425764 622  -.28  -.34  -.18  1.46  1.52 2  .3072477726944403
 6.918695219020471 623   .74  -.36  1.57   .59  2.44 2  12.55089107389768
 1.664472961319299 624  5.05  2.35 -2.14 -1.05 -1.41 2   1.90504628247238
-4.488823618117669 625  4.42 -1.54   .01  -.17  -.03 2  5.432489451476787
 2.808398174936492 626  3.11   -.3  -.06   .25   .77 2   13.8513701295092
end
format %tm monthly_date

thanks

[Carlo Lazzaro]

Marius:
what follows is probably the first half of the advice you might be interested in:

. . statsby _b _se, by(company): reg ri smb_5 hml rmw cma mktminusrf logdiff_fintech_funding, vce(robust)

Then (second half of the advice) you may want to copy and paste on a spredsheet or -export-.

[Marius Bauer]

Thanks alot this saves a lot of time.

Regarding your second advice this is definietly a work around. I hope there might be another solution because I want to know if the coef is significant or not. I could also do this manually but would be nice if there would be a automatic solution for this.

[Attaullah Shah]

Group regressions with statsby can be time consuming if the data set large. asreg offers a significant speed advantage over statsby. Also, it adds the estimated coefficients to the same dataset, so there would be no need to merge back the results to the dataset. Here is an example using asreg

* Install asreg
ssc install asreg
bys company : asregf ri smb_5 hml rmw cma mktminusrf logdiff_fintech_funding, robust

And if you need to report the group regression results using asdoc, then:

duplicates drop company, force
asdoc list _Nobs _R2 _adjR2 _b_smb_5 _b_hml _b_rmw _b_cma _b_mktminusrf _b_logdiff_fintech_funding _b_cons, replace