Generating Means

[Sartaj Hussain]

Below is monthly cross sectional data on idiovol and mcap. For this, I require a code that generates by mdate (monthly date) two measures: A weighted mean of idiovol (weighted by mcap) and also simple mean of idiovol (equally weighted).

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str51 stock float(mdate mcap idiovol)
1 "3I Infotech Ltd."           546   470.09   .0316652
1 "3I Infotech Ltd."           547   549.01  .14690167
1 "3I Infotech Ltd."           548   699.99   .1425231
1 "3I Infotech Ltd."           549   698.51  .10549582
1 "3I Infotech Ltd."           550   601.55  .06622392
1 "3I Infotech Ltd."           551   738.93  .08269537
1 "3I Infotech Ltd."           552  1010.53   .1527695
1 "3I Infotech Ltd."           553   958.49  .06494074
1 "3I Infotech Ltd."           554   899.97  .06511045
1 "3I Infotech Ltd."           555   968.86  .09622586
1 "3I Infotech Ltd."           556   997.06  .05170821
1 "3I Infotech Ltd."           557   908.57  .10416015
2 "3M India Ltd."              546    712.8   .0747804
2 "3M India Ltd."              547   894.67  .13923298
2 "3M India Ltd."              548   902.44  .04035586
2 "3M India Ltd."              549   986.82  .13015358
2 "3M India Ltd."              550   822.35   .1053295
2 "3M India Ltd."              551    875.3   .0648136
2 "3M India Ltd."              552   960.46  .09865496
2 "3M India Ltd."              553  1036.39  .04873206
2 "3M India Ltd."              554  1463.28  .14637086
2 "3M India Ltd."              555  1601.78  .10798793
2 "3M India Ltd."              556  1658.61  .12933055
2 "3M India Ltd."              557  1258.14  .12715587
3 "63 Moons Technologies Ltd." 546  2144.56  .14798808
3 "63 Moons Technologies Ltd." 547  3518.73   .2191684
3 "63 Moons Technologies Ltd." 548   4135.3  .13709934
3 "63 Moons Technologies Ltd." 549  5797.74   .2390417
3 "63 Moons Technologies Ltd." 550  4319.92  .09712362
3 "63 Moons Technologies Ltd." 551   5414.2  .11013233
3 "63 Moons Technologies Ltd." 552   5722.7 .066066995
3 "63 Moons Technologies Ltd." 553  5700.92  .07570697
3 "63 Moons Technologies Ltd." 554  5642.17  .07089136
3 "63 Moons Technologies Ltd." 555  7521.35  .11039127
3 "63 Moons Technologies Ltd." 556  8167.18  .13690199
3 "63 Moons Technologies Ltd." 557  7217.25   .0809899
4 "A B B India Ltd."           546  5611.55  .03572611
4 "A B B India Ltd."           547  6206.58  .09423798
4 "A B B India Ltd."           548  6752.88   .0626066
4 "A B B India Ltd."           549  7246.42 .069669716
4 "A B B India Ltd."           550  7111.43  .04674936
4 "A B B India Ltd."           551  8219.08  .09393455
4 "A B B India Ltd."           552  8175.21  .04007864
4 "A B B India Ltd."           553 10731.46  .09678438
4 "A B B India Ltd."           554 10782.75  .05612295
4 "A B B India Ltd."           555 12403.63  .06654813
4 "A B B India Ltd."           556 13202.32  .05945831
4 "A B B India Ltd."           557  9843.57   .0915469
5 "A B G Shipyard Ltd."        546        .          .
5 "A B G Shipyard Ltd."        547        .          .
5 "A B G Shipyard Ltd."        548        .          .
5 "A B G Shipyard Ltd."        549        .          .
5 "A B G Shipyard Ltd."        550        .          .
5 "A B G Shipyard Ltd."        551        .          .
5 "A B G Shipyard Ltd."        552  1465.27          .
5 "A B G Shipyard Ltd."        553  1805.94   .1645056
5 "A B G Shipyard Ltd."        554   1795.5  .12593968
5 "A B G Shipyard Ltd."        555  1924.33  .13250431
5 "A B G Shipyard Ltd."        556  2053.68   .1007655
5 "A B G Shipyard Ltd."        557     1547   .1644279
6 "A C C Ltd."                 546  6762.05  .04210848
6 "A C C Ltd."                 547  7689.84  .06167699
6 "A C C Ltd."                 548  8724.77  .05087649
6 "A C C Ltd."                 549  8921.02  .03509148
6 "A C C Ltd."                 550   8176.4  .05573912
6 "A C C Ltd."                 551  9377.73  .06117592
6 "A C C Ltd."                 552  9856.43  .06465606
6 "A C C Ltd."                 553 10611.45  .05284179
6 "A C C Ltd."                 554 11660.56  .06115739
6 "A C C Ltd."                 555 14578.24  .10851283
6 "A C C Ltd."                 556 18927.52  .10034332
6 "A C C Ltd."                 557 14245.38  .11580912
7 "A G C Networks Ltd."        546   524.71  .06772694
7 "A G C Networks Ltd."        547   505.35  .07908942
7 "A G C Networks Ltd."        548   525.78  .09037223
7 "A G C Networks Ltd."        549   501.58  .06612134
7 "A G C Networks Ltd."        550   521.51  .07958248
7 "A G C Networks Ltd."        551   623.84  .07208192
7 "A G C Networks Ltd."        552   699.07   .0816282
7 "A G C Networks Ltd."        553   584.42    .101523
7 "A G C Networks Ltd."        554    640.5  .04156522
7 "A G C Networks Ltd."        555   651.74  .07577085
7 "A G C Networks Ltd."        556   603.99  .05828489
7 "A G C Networks Ltd."        557   459.73  .11022209
8 "A I A Engineering Ltd."     546        .          .
8 "A I A Engineering Ltd."     547        .          .
8 "A I A Engineering Ltd."     548        .          .
8 "A I A Engineering Ltd."     549        .          .
8 "A I A Engineering Ltd."     550        .          .
8 "A I A Engineering Ltd."     551        .          .
8 "A I A Engineering Ltd."     552    957.7          .
8 "A I A Engineering Ltd."     553   1101.6  .16533577
8 "A I A Engineering Ltd."     554  1032.63   .0538882
8 "A I A Engineering Ltd."     555  1093.07  .12286268
8 "A I A Engineering Ltd."     556  1233.24  .15682384
8 "A I A Engineering Ltd."     557   969.43  .08363298
9 "Aarti Drugs Ltd."           546   185.99  .17199454
9 "Aarti Drugs Ltd."           547   189.56  .10319953
9 "Aarti Drugs Ltd."           548   177.15  .09320924
9 "Aarti Drugs Ltd."           549    162.4  .07688163
end
format %tm mdate

.

[Clyde Schechter]

by mdate, sort: egen simple_mean_idiovol = mean(idiovol)

by mdate: egen numerator = total(mcap*idiovol)
by mdate: egen denominator = total(mcap)
gen mcap_weighted_mean_idiovol = numerator/denominator

[Sartaj Hussain]

Thank you! Well it is sufficiently defined. I just added one more line to it as suggested by you in another code.

by mdate: keep if _n == 1

[Sartaj Hussain]

Suppose we run code in #2 and #3 and get by mdate a new data series: mcap_weighted_mean_idiovol. Lets rename it as monthly return. From this data series, two more new series is required to be generated as:

1. Compounded Return.
2. Cumulative log Return.

The compounded return for month t is calculated as 100 times the cumulative product of one plus the monthly return up to and including the given month.
The cumulative log return is calculated as the sum of monthly log returns up to and including the given month.

How can they be generated..

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str51 stock float(mdate mcap idiovol)
1 "3I Infotech Ltd."           546   470.09   .0316652
1 "3I Infotech Ltd."           547   549.01  .14690167
1 "3I Infotech Ltd."           548   699.99   .1425231
1 "3I Infotech Ltd."           549   698.51  .10549582
1 "3I Infotech Ltd."           550   601.55  .06622392
1 "3I Infotech Ltd."           551   738.93  .08269537
1 "3I Infotech Ltd."           552  1010.53   .1527695
1 "3I Infotech Ltd."           553   958.49  .06494074
1 "3I Infotech Ltd."           554   899.97  .06511045
1 "3I Infotech Ltd."           555   968.86  .09622586
1 "3I Infotech Ltd."           556   997.06  .05170821
1 "3I Infotech Ltd."           557   908.57  .10416015
2 "3M India Ltd."              546    712.8   .0747804
2 "3M India Ltd."              547   894.67  .13923298
2 "3M India Ltd."              548   902.44  .04035586
2 "3M India Ltd."              549   986.82  .13015358
2 "3M India Ltd."              550   822.35   .1053295
2 "3M India Ltd."              551    875.3   .0648136
2 "3M India Ltd."              552   960.46  .09865496
2 "3M India Ltd."              553  1036.39  .04873206
2 "3M India Ltd."              554  1463.28  .14637086
2 "3M India Ltd."              555  1601.78  .10798793
2 "3M India Ltd."              556  1658.61  .12933055
2 "3M India Ltd."              557  1258.14  .12715587
3 "63 Moons Technologies Ltd." 546  2144.56  .14798808
3 "63 Moons Technologies Ltd." 547  3518.73   .2191684
3 "63 Moons Technologies Ltd." 548   4135.3  .13709934
3 "63 Moons Technologies Ltd." 549  5797.74   .2390417
3 "63 Moons Technologies Ltd." 550  4319.92  .09712362
3 "63 Moons Technologies Ltd." 551   5414.2  .11013233
3 "63 Moons Technologies Ltd." 552   5722.7 .066066995
3 "63 Moons Technologies Ltd." 553  5700.92  .07570697
3 "63 Moons Technologies Ltd." 554  5642.17  .07089136
3 "63 Moons Technologies Ltd." 555  7521.35  .11039127
3 "63 Moons Technologies Ltd." 556  8167.18  .13690199
3 "63 Moons Technologies Ltd." 557  7217.25   .0809899
4 "A B B India Ltd."           546  5611.55  .03572611
4 "A B B India Ltd."           547  6206.58  .09423798
4 "A B B India Ltd."           548  6752.88   .0626066
4 "A B B India Ltd."           549  7246.42 .069669716
4 "A B B India Ltd."           550  7111.43  .04674936
4 "A B B India Ltd."           551  8219.08  .09393455
4 "A B B India Ltd."           552  8175.21  .04007864
4 "A B B India Ltd."           553 10731.46  .09678438
4 "A B B India Ltd."           554 10782.75  .05612295
4 "A B B India Ltd."           555 12403.63  .06654813
4 "A B B India Ltd."           556 13202.32  .05945831
4 "A B B India Ltd."           557  9843.57   .0915469
5 "A B G Shipyard Ltd."        546        .          .
5 "A B G Shipyard Ltd."        547        .          .
5 "A B G Shipyard Ltd."        548        .          .
5 "A B G Shipyard Ltd."        549        .          .
5 "A B G Shipyard Ltd."        550        .          .
5 "A B G Shipyard Ltd."        551        .          .
5 "A B G Shipyard Ltd."        552  1465.27          .
5 "A B G Shipyard Ltd."        553  1805.94   .1645056
5 "A B G Shipyard Ltd."        554   1795.5  .12593968
5 "A B G Shipyard Ltd."        555  1924.33  .13250431
5 "A B G Shipyard Ltd."        556  2053.68   .1007655
5 "A B G Shipyard Ltd."        557     1547   .1644279
6 "A C C Ltd."                 546  6762.05  .04210848
6 "A C C Ltd."                 547  7689.84  .06167699
6 "A C C Ltd."                 548  8724.77  .05087649
6 "A C C Ltd."                 549  8921.02  .03509148
6 "A C C Ltd."                 550   8176.4  .05573912
6 "A C C Ltd."                 551  9377.73  .06117592
6 "A C C Ltd."                 552  9856.43  .06465606
6 "A C C Ltd."                 553 10611.45  .05284179
6 "A C C Ltd."                 554 11660.56  .06115739
6 "A C C Ltd."                 555 14578.24  .10851283
6 "A C C Ltd."                 556 18927.52  .10034332
6 "A C C Ltd."                 557 14245.38  .11580912
7 "A G C Networks Ltd."        546   524.71  .06772694
7 "A G C Networks Ltd."        547   505.35  .07908942
7 "A G C Networks Ltd."        548   525.78  .09037223
7 "A G C Networks Ltd."        549   501.58  .06612134
7 "A G C Networks Ltd."        550   521.51  .07958248
7 "A G C Networks Ltd."        551   623.84  .07208192
7 "A G C Networks Ltd."        552   699.07   .0816282
7 "A G C Networks Ltd."        553   584.42    .101523
7 "A G C Networks Ltd."        554    640.5  .04156522
7 "A G C Networks Ltd."        555   651.74  .07577085
7 "A G C Networks Ltd."        556   603.99  .05828489
7 "A G C Networks Ltd."        557   459.73  .11022209
8 "A I A Engineering Ltd."     546        .          .
8 "A I A Engineering Ltd."     547        .          .
8 "A I A Engineering Ltd."     548        .          .
8 "A I A Engineering Ltd."     549        .          .
8 "A I A Engineering Ltd."     550        .          .
8 "A I A Engineering Ltd."     551        .          .
8 "A I A Engineering Ltd."     552    957.7          .
8 "A I A Engineering Ltd."     553   1101.6  .16533577
8 "A I A Engineering Ltd."     554  1032.63   .0538882
8 "A I A Engineering Ltd."     555  1093.07  .12286268
8 "A I A Engineering Ltd."     556  1233.24  .15682384
8 "A I A Engineering Ltd."     557   969.43  .08363298
9 "Aarti Drugs Ltd."           546   185.99  .17199454
9 "Aarti Drugs Ltd."           547   189.56  .10319953
9 "Aarti Drugs Ltd."           548   177.15  .09320924
9 "Aarti Drugs Ltd."           549    162.4  .07688163
end
format %tm mdate

by mdate, sort: egen simple_mean_idiovol = mean(idiovol)

by mdate: egen numerator = total(mcap*idiovol)
by mdate: egen denominator = total(mcap)
gen mcap_weighted_mean_idiovol = numerator/denominator
keep mdate mcap_weighted_mean_idiovol
by mdate: keep if _n == 1
rename mcap_weighted_mean_idiovol monthly_return
gen cum_return = 1 + monthly_return
sort mdate
replace cum_return = cum_return*cum_return[_n-1] if _n > 1
replace cum_return = 100 * cum_return
gen cum_log_return = sum(log(monthly_return))

[Sartaj Hussain]

This is fine, but one thing. The last line of code generates negative outcomes only.

[Clyde Schechter]

Yes of course it generates negative outcomes only. All of the monthly returns are numbers between 0 and 1. The logarithm of any number between 0 and 1 is negative. When you add a bunch of negative numbers, you get a negative results. Did you perhaps mean to ask for the log cumulative return instead of the cumulative log return?

[Sartaj Hussain]

They are returns already, so do not need logrithmic transformation again, just their cumulation is required.