calculating weights for each year (panel data)

[Bob van Alphen]

Hi,

I am currently writing my thesis but unfortunately ran into some problems with calculating a variable in stata.

My (panel)dataset consists of 48,000 observations all data of firms from year 2006 to 2014

The variable i am trying to create is efwamb which is denoted by this formula:

e and d are equity issues and debt issues respectively, M/B is the market to book ratio in any given year. For every year the M/B ratio is multiplied by the weight of equity and debt issues divided by the sum of total equity and debt issues of the starting years until the year i am calculating the efwamb for. So for example 2006 and 2008:

I have calculated the e and d issues in every year, as well as the M/B ratios and cumulative sum of e and d for every year. So I now have the nominator and denominator available but am really struggling with calculating these weights.

Would anyone know an easy way to calculate this? Would be much appreciated!

Bob

[Clyde Schechter]

It is almost pointless to ask for help with code without showing example data, because the correct code will depend on the details of the data.

Please post back and use the -dataex- command to show example data. If you are running version 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.

[Bob van Alphen]

Sorry for not posting it initially but here is an example of the data, I updated the dataset from 2006-2014 to 2010-2018, but the idea remains the same.

netdebteq= equity and debt issued in respective year (numerator in formula above)
MarketBook= market to book ratio in respective year
cum_netdebteq= cumulative sum of netdebteq from year of first observation to respective year (denominator in formula above)

* Example generated by -dataex-. To install: ssc install dataex
clear
input long CompanyName1 int DataYearFiscal float(netdebteq MarketBook cum_netdebteq)
 1 2010              .  .9922873              0
 1 2011    .0006564935  1.405166    .0006564935
 1 2012    .0018718906 1.3929102     .002528384
 1 2013     .000985044  2.335562     .003513428
 1 2014    .0020060048 1.9899875     .005519433
 1 2015     .003608831  2.689314     .009128264
 1 2016     .003222239  2.115996     .012350503
 1 2017     .014190722 2.0091772     .026541226
 1 2018   -.0042017363   2.37807      .02233949
 2 2018              .  2.813932              0
 3 2014              . 1.0011997              0
 3 2015     .019149605   .987772     .019149605
 3 2016    .0005896641 1.0023211      .01973927
 3 2017     -.00364818   .927111      .01609109
 3 2018     .016866533   .534229      .03295762
 4 2010              .   .920953              0
 4 2011   -.0008120936  .7362041   -.0008120936
 4 2012    -.006706106  .7995056    -.007518199
 4 2013   -.0021118517  .7576654     -.00963005
 4 2014   -.0046296627  .8415466    -.014259714
 4 2015    -.007528572  .7649608    -.021788286
 4 2016    -.009080296  .5902281     -.03086858
 4 2017     .018311717  .7565162    -.012556866
 4 2018     .003714438  .6639056    -.008842427
 5 2010              .  .8489286              0
 5 2011    .0014586886  .6959407    .0014586886
 5 2012    .0019119126  .8280895     .003370601
 5 2013     .002274628  1.223896      .00564523
 5 2014     .002461499 1.0437002     .008106728
 5 2015     .004748509 1.0970386     .012855237
 6 2010              .  .7880968              0
 6 2011   -.0001871562  .6157629   -.0001871562
 6 2012    .0001954813  .8049817   8.325107e-06
 6 2013  -.00053757575  .9682407  -.00052925065
 6 2014   -.0001599809  .8918524   -.0006892316
 6 2015   -.0001099996 1.0120573   -.0007992311
 6 2016  -.00015139507 1.4263967   -.0009506262
 6 2017   -.0002133652  1.324809   -.0011639914
 6 2018   -.0001887086 1.3496288      -.0013527
 7 2010              .  .9943006              0
 7 2011  -.00009179132 1.1391702  -.00009179132
 7 2012  -.00005283642   .947748  -.00014462773
 7 2013  -.00002781178 1.3271366   -.0001724395
 7 2014  -.00005124119 1.3272592   -.0002236807
 7 2015   .00008940152 1.2431585  -.00013427918
 7 2016 -.000029220084 1.7129337  -.00016349927
 7 2017 -.000027553104 1.7783864  -.00019105237
 7 2018 -.000028469476 1.3621193  -.00021952184
 8 2010              .  .9843127              0
 8 2011    .0011746637  .7818771    .0011746637
 8 2012    .0005931185  .8996472     .001767782
 8 2013    -.004963987 1.1339861   -.0031962045
 9 2011              .   1.88453              0
 9 2012    .0009379745 1.7903743    .0009379745
 9 2013    .0012957883 3.7002745    .0022337628
 9 2014   .00028800193 1.8583316     .002521765
 9 2015    .0001307527 2.2854784    .0026525175
 9 2016  .000032074015  .7739354    .0026845916
 9 2017   -.0007853082 1.0773405    .0018992834
 9 2018    .0000493334 1.2097464    .0019486167
10 2014              .  6.947454              0
10 2015     -.04173122  8.475556     -.04173122
10 2016      .04336713  4.062662    .0016359165
10 2017      .04548045 4.5871873      .04711637
10 2018   -.0033373674 4.3541117        .043779
11 2014              .  9.099432              0
11 2015      .00800354    6.5418      .00800354
11 2016   -.0008623226  7.281575     .007141218
11 2017     .003597554   8.51219     .010738771
11 2018    .0011679733 4.0482287     .011906745
12 2018              . 3.4424274              0
13 2011              .  5.034015              0
13 2012    -.017458703   7.60891    -.017458703
13 2013    -.000489085  7.615463     -.01794779
13 2014  -.00002706332  2.650361    -.017974852
13 2015  -.00051967136  7.314975    -.018494522
14 2010              .    5.5212              0
14 2011      .00944108  1.890085      .00944108
14 2012     .007443868  5.435432     .016884949
14 2013   -.0029722245   9.94075     .013912724
14 2014     .000872692  2.748639     .014785416
14 2015      .03043786  1.431067      .04522328
14 2016   -.0003038596  2.311705      .04491942
14 2017  -.00005073808  1.565035      .04486868
14 2018  -.00039323195 1.9908835      .04447545
15 2010              .  3.737224              0
15 2011 -1.5536787e-06 3.5250976 -1.5536787e-06
15 2012   -6.50341e-07  3.503954 -2.2040197e-06
15 2013  -5.747816e-06  5.032309  -7.951836e-06
15 2014 -.000010109246  7.857064 -.000018061082
15 2015  -5.621983e-06  7.463772 -.000023683066
15 2016  -5.195046e-06  10.15993  -.00002887811
15 2017   5.050624e-06 11.808876 -.000023827486
15 2018  -7.940165e-06 10.848287 -.000031767653
16 2010              . 1.9232403              0
16 2011    -.002801785 1.1399693    -.002801785
16 2012    -.002603118 1.3705093    -.005404903
17 2013              .  8.696279              0
17 2014     .013273846  3.385373     .013273846
17 2015      .13946879   2.90248      .15274264
end
label values CompanyName1 CompanyName1
label def CompanyName1 1 "1-800-FLOWERS.COM", modify
label def CompanyName1 2 "111 INC -ADR", modify
label def CompanyName1 3 "1347 PROPERTY INS HLDGS INC", modify
label def CompanyName1 4 "180 DEGREE CPTL CORP", modify
label def CompanyName1 5 "1ST CENTURY BANCSHARES INC", modify
label def CompanyName1 6 "1ST CONSTITUTION BANCORP", modify
label def CompanyName1 7 "1ST SOURCE CORP", modify
label def CompanyName1 8 "1ST UNITED BANCORP INC", modify
label def CompanyName1 9 "21VIANET GROUP INC", modify
label def CompanyName1 10 "22ND CENTURY GROUP INC", modify
label def CompanyName1 11 "2U INC", modify
label def CompanyName1 12 "360 FINANCE INC - ADR", modify
label def CompanyName1 13 "360 SECURITY TECHNOLOGY INC", modify
label def CompanyName1 14 "3D SYSTEMS CORP", modify
label def CompanyName1 15 "3M CO", modify
label def CompanyName1 16 "3SBIO INC", modify
label def CompanyName1 17 "500.COM LTD  -ADR", modify

[Clyde Schechter]

by CompanyName1 (DataYearFiscal), sort: gen all_terms_numerator ///
    = sum(netdebteq*MarketBook)
gen efwamb = all_terms_numerator/cum_netdebteq

[Bob van Alphen]

thanks a lot!

[Bob van Alphen]

Hi,

To make sure that I am forming a weighted average, I have to exclude any negative weights. This requires an explicit formula for the 'weight- part' of the efwamb formula.

The calculation of the efwamb is correct but I now just need a way to represent this part of the formula in stata so I can set the minimum weight to 0.
Or would anyone know a way to exclude any negative values of this part of the formula, without stating the weights explicitly?

Help would be much appreciated!

Bob

[Clyde Schechter]

The weight part in your formula is just netdebteq/cum_netdebteq.

To make sure that I am forming a weighted average, I have to exclude any negative weights.

It may well be that in this application, there is a reason, related to substantive financial concerns, for excluding terms where the weight is negative. I wouldn't know about that. But as a statement about weighted averages generally, it is wrong.