Insufficient data when trying looped regressions and storing betas

[Birger Johansson]

Hi,

I am trying to run a loop of regressions by an index called funddateid and then storing the betas from each regression as a matrix or as new variables. I want to run a cross-sectional regression of fund trades on past institutional trades for each fund(j) and quarter(t) so I created a variable called funddateid which is unique for each fund-quarter. The regression is specified below along with the code used.

Delta=IO(Institutional ownership)+logMC(Market cap)+logBM(Book-to-market)+Returns(Momentum)

The following code is used:

forvalues funddateid = 1(1)4788 {
xtreg std_delta_W l.std_LogMc l.std_logBm l.std_IO_98 l.std_Returns_W
matrix betas=nullmat(beta)
}

However, I keep getting the "insufficient data" error when trying to run the regressions.
The dependent variable(delta) is the percentage change in the number of shares of sock(i) in the portfolio of fund(j) during quarter(t). In some quarters, there is no change which gives delta the value of "0" and in some cases there are no observations of said stock at all since it may not be included in the fund in that particular quarter which will give delta a "." (Missing value). I suspect this is the reason for the "insufficient data" error since there are quite a lot of "missing" observations for delta but I do not know how to get around the problem. If the stock is not present in the particular quarter, I cant assign a 0 to it either. Can anyone provide any advice on how to solve this issue?

See first 100 observations below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(std_delta_W std_LogMc std_logBm std_IO_98 std_Returns_W)
 -.08436412 -.019862974     -.3200253   1.2639892    .20019694
  .05064744  .010193878    -.15792634 -.025369117    .52692515
  -.2992237   -.1692743      .0920907    1.371598       .48546
 -.16985036   .06018072     -.6145061  -.14668785      .800246
   .2162701   .02847719    -.22756325   .00375406    -.6904961
  4.4661713    .2355457     -.6178749  -.22152378    1.3600446
 -.10972418   .24799494     -.6843112   -.2404587    .48781285
  -.3217568   .13032532     -.4930049  -.19861546    -.6543404
 -.24006122   .14044602     -.5806749  -.19565043     .5423205
          .  -.08693593    -.13920262    1.933017    1.4659822
-.014236635  -.22284804     .18511248    2.084619   -1.6137875
 -.24924693    .1081663     -.4439734  -.19012913   -.06332681
  -.2021701   .02651491    -.46270245   -.2168303     .8460811
-.033579994   .05745066     -.3176963   -.1329641    -.8327176
 -.26426965   -.1790977     .08318415   1.5960423     .6210191
  -.1767197  -.13142912     .03676183    .7688901     .4243053
 -.26486173   .05073912    -.20902926   -.1765488     .6561047
  -.1698344  .002055234     -.3967392  -.23017097     .7369264
  .02158031  -.09981994    -.06231921    .3500392   -.59018606
 -.12089711   .04993459     -.3253091  -.06297561     .3225815
  -.9026852     .159684     -.5303013    -.240055    -.8998079
 -.35788155    .0478515    -.55603486  -.11851433    -.0798317
  .11438504  -.19279864      .1625038   1.7851228     -.407834
  -.2180872   .03732831    -.17707463   .02233156    .53683823
 -.22502415    .1183198    -.50608253   .03982426    1.5177295
   -.489066   .06251259    -.48739755 -.068284184     .3715165
   .2080083  -.21688573     .12193358   2.3437867    .13317217
  .07772464 -.005657548     -.2212266 -.025047496    -1.423227
 -.03770588   .11380477     -.3589811   -.1708785    1.2609015
 -1.0987867   .12406065     -.4313966  -.09260344      .682752
 -.21748057   .04514672    -.31970325  .014481218    -.1376224
   .3456481 -.015334507    -.30398515    .3943915    1.8803595
 -.12660344     .029301     -.3981392  -.14224868     .6349776
  -.3948699   .17039265     -.5011021  -.02285117     .6209945
 -.14395085  -.06478409     -.1628543    .6400483     1.277161
  .02772213   .04336702     -.4834564  -.26528367   -.19495553
          .   .04514672    -.31970325  .014481218    -.1376224
          .  .010193878    -.15792634 -.025369117    .52692515
          .    .1183198    -.50608253   .03982426    1.5177295
          .  -.13142912     .03676183    .7688901     .4243053
          .  -.19279864      .1625038   1.7851228     -.407834
          .  -.09981994    -.06231921    .3500392   -.59018606
          .   -.1692743      .0920907    1.371598       .48546
          .  -.08693593    -.13920262    1.933017    1.4659822
          .     .029301     -.3981392  -.14224868     .6349776
          .   .05073912    -.20902926   -.1765488     .6561047
          .   .06251259    -.48739755 -.068284184     .3715165
          .   .04993459     -.3253091  -.06297561     .3225815
          .    .1081663     -.4439734  -.19012913   -.06332681
          .     .159684     -.5303013    -.240055    -.8998079
          .   .04336702     -.4834564  -.26528367   -.19495553
          .   .13032532     -.4930049  -.19861546    -.6543404
          .   .12406065     -.4313966  -.09260344      .682752
          .   .05745066     -.3176963   -.1329641    -.8327176
          .   -.1790977     .08318415   1.5960423     .6210191
          .  -.22284804     .18511248    2.084619   -1.6137875
          .  -.21688573     .12193358   2.3437867    .13317217
          .   .11380477     -.3589811   -.1708785    1.2609015
          . -.019862974     -.3200253   1.2639892    .20019694
          .    .0478515    -.55603486  -.11851433    -.0798317
          .    .2355457     -.6178749  -.22152378    1.3600446
          .  .002055234     -.3967392  -.23017097     .7369264
          .   .03732831    -.17707463   .02233156    .53683823
          . -.015334507    -.30398515    .3943915    1.8803595
          .   .17039265     -.5011021  -.02285117     .6209945
          .   .02847719    -.22756325   .00375406    -.6904961
          . -.005657548     -.2212266 -.025047496    -1.423227
          .   .02651491    -.46270245   -.2168303     .8460811
          .  -.06478409     -.1628543    .6400483     1.277161
          .   .06018072     -.6145061  -.14668785      .800246
          .   .14044602     -.5806749  -.19565043     .5423205
          .   .24799494     -.6843112   -.2404587    .48781285
          .    .3013069     -.3031426   -.2458811    -.8189632
   -.489066    .5810111     -.1287914   -.3424408    -1.258042
          .    .4155392    -.52037567   -.2392435    -1.071275
          .    .7624493     -.3850458  -.51717556     .4311027
          .    .7474274    -.28644872     -.60361    -.5910043
  .05978851    .6582577     -.2454224  -.11071718   -.44066915
    3.85713    .6929567     -.3066609   -.3323993     .4323719
          .    .4163122    -.37317845  -.25870988 -.0045675314
          .    .3633049     -.3181784   -.1799226    -.3974456
          .    .7769827    -.27940208   -.2170247    -.9487463
   2.274123    .6646247     -.2282051  -.21360686   -.27441826
          .    .7943527     -.4606717   -.3241475    -.5874089
          .    .3983752    -.33996755   -.2039016     .6178427
          .    .4474668    -.09500497  -.24924213    -.9529543
          .    .7565222    -.28874764   -.4936907    -.7651106
          .    .3861609     -.4258011  -.24233766     .3661532
          .    .3667498     -.3582995  -.23735544    -.4927481
          .     .447699    -.15295595   -.1738346    -.1703405
  -.3189141    .6871001     -.3871126   .04885774     .6935948
          .    .6362026    -.16335775   -.4189915    -.9413581
 -.05638577    .6505797    -.20176312   -.3700999    -.7329817
          .    .8346604     -.4224465   -.3545304   .036658492
          .   .36996385      -.091672  -.20807937     .7178988
   .6130913    .3816887 -.00013731657  -.11239394    -.9962961
          .    .4151791     -.3586984   -.1680542     .7389817
          .    .8554429     -.4164061   -.4324653    .57129574
  -3.476443    .5704425    -.21022104   -.3436584     .8901415
          .    .8841048    -.51752275  -.37833935     .2154532
end

Kind Regards,
Birger

[Rich Goldstein]

your situation is not completely clear to me but here is a guess: use -capture- and then continue only if sufficient observations; some examples are given in the manual; to get there:

help capture

and click on the blue "remarks and examples" and look at the examples

[Birger Johansson]

Hey Rich, thank you for your reply, sorry if my post is somewhat unclear.

I am aware of the capture command and it works just fine. The problem is I get the "insufficient observations" message on every regression in the loop and I guess I am wondering if there is a way to force regressions with insufficient data?

Kind Regards,
Birger

[Nick Cox]

a way to force regressions with insufficient data

The results would be utterly useless to you even if a way existed.

More importantly even, the syntax in #1 makes no sense to me.

forvalues funddateid = 1(1)4788 {    
     xtreg std_delta_W l.std_LogMc l.std_logBm l.std_IO_98 l.std_Returns_W    
     matrix betas=nullmat(beta)
}

With this loop, exactly the same xtreg command will just be repeated 4788 times. Nor are you accumulating results. You are likewise making the same matrix assignment every time.

There is nothing in the forvalues syntax that makes xtreg know that it is supposed to use a different set of observations each time around the loop -- if that is your intention.

I would back up and tell us about the results of

xtset 

I didn't understand any of the finance stuff here, but isn't the point of a xtreg to combine data from different panels?

[Birger Johansson]

Yeah, that loop was very wrong. It all got a bit confusing since Stata would not let me run a normal regression with lags, hence the xtreg. I calculated the lags separately and used this code to run it and it works!

forv i=1/4788 {
capture reg std_delta_W lagstdMC lagstdBM lagstdIO lagstdReturns if funddateid==`i'
capture matrix b_`i' = get(_b)
capture noisily svmat b_`i', names(iter`i')
}

Now I just need to figure out a way to save the betas from lagstdIO in a more practical way (a column matched with funddateid).

Thanks.

[Nick Cox]

That is now a common problem. For example, with rangestat (SSC) you can go

rangestat (reg) std_delta_W lagstdMC lagstdBM lagstdIO lagstdReturns, int(funddateid  0 0)

and your coefficient estimates will automatically be put in new variables.

[Birger Johansson]

Very useful program, works perfectly.

Thanks a bunch!

[Birger Johansson]

Sorry for bothering you again but I am now trying to run a double loop with the betas retained from the previous regression using the following code:

gen FH=.
forv i=1/4788 {
forv j=1/"date" {
quietly replace FH= (sum(1/`j')*b_lagstdIO)/(sum(1/`j')) if funddateid==`i'-`j'+1
}
}

I attached a picture of the formula I am trying to run since I did not know how to write it in here directly.

My "i" is the funddateid from before while my "j" is a date variable spanning from 1-36 with one date corresponding to each funddateid. The reason why I need the corresponding date in my j-loop is that it will prevent funddateid from picking values of b_lagstdIO from other funds, i.e I cannot let "i"-"j"+1 go below 1 if the date corresponding to the funddateid is 1 (for example).

If could only make Stata understand that I want my "forv j" to change accordingly I think this code would work. Alas, it will not let me run this code...

Is there are any way to make this work or, better yet, is there a way to write the formula to make it sum from t to j? I have searched for ways to code sum equations in Stata but it seems this is not possible?

[Nick Cox]

You have 4788 distinct regressions but many more observations. You would do better to collapse your data to one observation per regression and then think what you want to calculate. It's probably going to yield to rangestat again.

[Birger Johansson]

I have collapsed the data and pulled some of it to use as a practice set. Using rangestat, I almost get to where I want, but not quite.

I started writing this post intending to pose a new question but in doing so I may have found the solution so if anyone runs into a similar problem in the future, this is how I solved it.

You will find my practice data at the bottom of the post. Please let me know if I got something wrong here. I decided to calculate the numerator and the denominator separately and then perform the division to get to where I wanted to be.

gen date2=date-21

gen date3=1/date2

gen datebeta=(1/date2)*b_lagstdIO


rangestat (sum) sumbeta=datebeta, by(fundid) int(date2 -36 0)


rangestat (sum) sumdate=date3, by(fundid) int(date2 -36 0)

gen FH=sumbeta/sumdate

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(date2 fundid funddateid) double b_lagstdIO float date3 double(sumbeta sumdate) float FH
 1 1  1          .          1                     0                  1             0
 2 1  2  .17728107         .5    .08864053338766098                1.5     .05909369
 3 1  3  -.3403375   .3333333  -.024805299937725067 1.8333333432674408   -.013530163
 4 1  4   .1063243        .25   -.08686475828289986  2.083333343267441    -.04169508
 5 1  5 -.04164928         .2    .01825121883302927  2.283333346247673    .007993234
 6 1  6 -.46817467  .16666667   -.08635896723717451 2.4500000178813934    -.03524856
 7 1  7 -.25247665  .14285715   -.11409720405936241 2.5928571671247482    -.04400443
 8 1  8 -.20491354       .125  -.061682285740971565 2.7178571671247482    -.02269519
 9 1  9  .43435011  .11111111   .022646931931376457 2.8289682790637016    .008005368
10 1 10 -.39379951         .1    .00888117402791977 2.9289682805538177    .003032185
11 1 11  .14004333   .0909091   -.02664873842149973 3.0198773741722107   -.008824443
12 1 12 -.20553526  .08333334  -.004396726377308369  3.103210709989071  -.0014168314
13 1 13 -.22879027  .07692308   -.03472718968987465 3.1801337897777557    -.01092004
14 1 14  .06455469 .071428575  -.012988201808184385  3.251562364399433  -.0039944495
15 1 15 -.04674175  .06666667  .0014949326869100332 3.3182290345430374   .0004505212
16 1 16  .02927918      .0625 -.0012861678842455149 3.3807290345430374   -.000380441
17 1 17  .54539517  .05882353     .0339120184071362  3.439552564173937    .009859427
18 1 18 -.03634882  .05555556     .0300626908428967 3.4951081201434135    .008601362
19 1 19  .05865438  .05263158  .0010676938109099865  3.547739699482918   .0003009504
20 1 20  .06069134        .05  .0061216396279633045  3.597739700227976   .0017015238
21 1 21 -.18402919  .04761905  -.005728727672249079 3.6453587487339973  -.0015715128
22 1 22 -.05784624  .04545455  -.011392669286578894  3.690813295543194   -.003086764
23 1 23  .14611274  .04347826   .003723353147506714 3.7342915572226048   .0009970708
24 1 24  .05906995  .04166667   .008813975611701608  3.775958225131035   .0023342355
25 1 25 -.00030555        .04  .0024490258447258384  3.815958224236965   .0006417853
26 1 26 -.05988191  .03846154 -.0023153724823714583 3.8544197641313076  -.0006007059
27 1 27 -.49932811 .037037037  -.020796784199774265  3.891456801444292   -.005344216
28 1 28  .09919791 .035714287  -.014950851211324334 3.9271710887551308   -.003807028
29 1 29 -.87672952  .03448276  -.026689270744100213  3.961653847247362   -.006736901
30 1 30 -.21805895 .033333335   -.03750068508088589 3.9949871823191643   -.009386935
31 1 31   .0806714 .032258064  -.004666328663006425  4.027245245873928    -.00115869
32 1 32   .0287111     .03125   .003499525017105043  4.058495245873928   .0008622716
33 1 33 -.00233083  .03030303   .000826590636279434  4.088798277080059   .0002021598
34 1 34 -.07604114 .029411765 -.0023071353207342327  4.118210041895509  -.0005602277
35 1 35 -1.1984652  .02857143   -.03647836670279503  4.146781470626593   -.008796791
36 1 36  .77734936  .02777778  -.012648824602365494  4.174559248611331  -.0030299784
 1 2 37          .          1                     0                  1             0
 2 2 38 -.03977741         .5  -.019888704642653465                1.5   -.013259136
 3 2 39 -.01472041   .3333333   -.02479550801217556 1.8333333432674408   -.013524823
 4 2 40 -.10118459        .25  -.030202951282262802  2.083333343267441   -.014497416
 5 2 41  .01446279         .2   -.02240358991548419  2.283333346247673   -.009811792
 6 2 42  .04917539  .16666667   .011088456492871046 2.4500000178813934   .0045259004
 7 2 43  .27084392  .14285715   .046887888573110104 2.5928571671247482    .018083483
 8 2 44  .19661323       .125     .0632686447352171 2.7178571671247482     .02327887
 9 2 45  .16557948  .11111111    .04297437518835068 2.8289682790637016     .01519083
10 2 46 -.13689435         .1   .004708285443484783 2.9289682805538177   .0016074894
11 2 47  .01251713   .0909091  -.012551514199003577 3.0198773741722107  -.0041562994
12 2 48   .0816563  .08333334   .007942612515762448  3.103210709989071    .002559482
13 2 49 -.47342901  .07692308   -.02961292304098606 3.1801337897777557   -.009311848
14 2 50  .67026921 .071428575   .011458758264780045  3.251562364399433   .0035240776
15 2 51 -.66006683  .06666667   .003871917724609375 3.3182290345430374   .0011668627
16 2 52  .40928203      .0625  -.018424328416585922 3.3807290345430374   -.005449809
17 2 53 -.45973055  .05882353 -.0014628469944000244  3.439552564173937 -.00042530155
18 2 54   .1523844  .05555556  -.018577173352241516 3.4951081201434135   -.005315193
19 2 55 -.20543804  .05263158 -.0023467279970645905  3.547739699482918  -.0006614713
20 2 56 -.09784934        .05   -.01570499548688531  3.597739700227976  -.0043652393
21 2 57 -.10974701  .04761905  -.010118515230715275 3.6453587487339973  -.0027757254
22 2 58  .04832151  .04545455 -.0030296158511191607  3.690813295543194  -.0008208532
23 2 59 -.01695201  .04347826   .001459388411603868 3.7342915572226048   .0003908073
24 2 60 -.10356078  .04166667  -.005052076536230743  3.775958225131035   -.001337959
25 2 61 -.05108355        .04  -.006358374608680606  3.815958224236965   -.001666259
26 2 62 -.05985317  .03846154  -.004345386987552047 3.8544197641313076  -.0011273777
27 2 63 -.03652343 .037037037  -.003654764615930617  3.891456801444292  -.0009391765
28 2 64  .10925353 .035714287  .0025491922860965133 3.9271710887551308   .0006491167
29 2 65  .06528204  .03448276   .006153016816824675  3.961653847247362   .0015531435
30 2 66 -.08315252 .033333335 -.0005206456407904625 3.9949871823191643 -.00013032474
31 2 67 -.05501976 .032258064  -.004546581534668803  4.027245245873928  -.0011289557
32 2 68 -.03117494     .03125 -.0027490478241816163  4.058495245873928  -.0006773564
33 2 69  -.0225343  .03030303 -.0016570744337514043  4.088798277080059  -.0004052717
34 2 70 -.01967308 .029411765  -.001261477591469884  4.118210041895509   -.000306317
35 2 71 -.46507168  .02857143   -.01386638218536973  4.146781470626593    -.00334389
36 2 72  1.0325619  .02777778   .015394512563943863  4.174559248611331    .003687698
end