Portfolio average

Juan Gonzalex

Join Date: May 2022
Posts: 59

28 May 2022, 02:05

Hi, I'm currently writing my bachelor thesis and I'm new to Stata. I have monthly data and I want to sort the data monthly in 5 portfolios according to their leverage change. To sort the data I have used the following code:

Code:

bys year month: astile change5 = lvchange_abs, nq(5)

A sample of my data is:

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 isin int year byte month float lvchange_abs double(ri change5)
"GB0004052071" 1988  7 .003303988   9.566951 2
"GB0001826634" 1988  8 .001834629  -8.154499 1
"GB0009223206" 1988  8 .016871154  -11.46245 3
"GB0030913577" 1988  8 .003004034  -5.144036 1
"GB0007188757" 1988  8  .03863934  -2.752304 4
"GB0033195214" 1988  8   .0211871 -14.440441 4
"GB00B61TVQ02" 1988  8 .009517585 -2.8571427 2
"GB00B1WY2338" 1988  8  .04266421 -10.276688 5
"GB0004052071" 1988  8 .003303988 -3.2786906 2
"GB00B1WY2338" 1988  9  .04266421  14.096916 5
"GB0004052071" 1988  9 .003303988  7.3446393 2
"GB0001826634" 1988  9 .001834629   1.869154 1
"GB0030913577" 1988  9 .003004034  5.2060723 1
"GB0033195214" 1988  9   .0211871  6.4669967 4
"GB00B61TVQ02" 1988  9 .009517585  3.9215684 2
"GB0009223206" 1988  9 .016871154  11.160719 3
"GB0007188757" 1988  9  .03863934   7.679951 4
"GB0001826634" 1988 10 .001834629 -1.8348575 1
"GB00B1WY2338" 1988 10  .04266421   1.544416 5
"GB0030913577" 1988 10 .003004034  1.2371182 1
"GB0033195214" 1988 10   .0211871  2.0080209 4
end

I want to calculate the average return (ri) of the each portfolio per month, but I don't know how to do this. It will be something like the mean for each month for each year for each portfolio (ranging from 1 to 5). Is there an easy way to do this? And I would also like to implement an if function to only calculate the mean if a portfolio has more than 5 companies in it.

Thanks

Last edited by Juan Gonzalex; 28 May 2022, 02:08.

Tags: portfolio

Carlo Lazzaro

Join Date: Apr 2014
Posts: 17854

28 May 2022, 02:27

Juan:
do you mean something along the following lines?

Code:

. encode isin, g(new_isin)

. bysort new_isin year (month): egen wanted=mean( ri )

. list

     +------------------------------------------------------------------------------------------+
     |         isin   year   month   lvchan~s           ri   change5       new_isin      wanted |
     |------------------------------------------------------------------------------------------|
  1. | GB0001826634   1988       8   .0018346    -8.154499         1   GB0001826634   -2.706734 |
  2. | GB0001826634   1988       9   .0018346     1.869154         1   GB0001826634   -2.706734 |
  3. | GB0001826634   1988      10   .0018346   -1.8348575         1   GB0001826634   -2.706734 |
  4. | GB0004052071   1988       7    .003304     9.566951         2   GB0004052071      4.5443 |
  5. | GB0004052071   1988       8    .003304   -3.2786906         2   GB0004052071      4.5443 |
     |------------------------------------------------------------------------------------------|
  6. | GB0004052071   1988       9    .003304    7.3446393         2   GB0004052071      4.5443 |
  7. | GB0007188757   1988       8   .0386393    -2.752304         4   GB0007188757    2.463824 |
  8. | GB0007188757   1988       9   .0386393     7.679951         4   GB0007188757    2.463824 |
  9. | GB0009223206   1988       8   .0168712    -11.46245         3   GB0009223206   -.1508655 |
 10. | GB0009223206   1988       9   .0168712    11.160719         3   GB0009223206   -.1508655 |
     |------------------------------------------------------------------------------------------|
 11. | GB0030913577   1988       8    .003004    -5.144036         1   GB0030913577    .4330515 |
 12. | GB0030913577   1988       9    .003004    5.2060723         1   GB0030913577    .4330515 |
 13. | GB0030913577   1988      10    .003004    1.2371182         1   GB0030913577    .4330515 |
 14. | GB0033195214   1988       8   .0211871   -14.440441         4   GB0033195214   -1.988474 |
 15. | GB0033195214   1988       9   .0211871    6.4669967         4   GB0033195214   -1.988474 |
     |------------------------------------------------------------------------------------------|
 16. | GB0033195214   1988      10   .0211871    2.0080209         4   GB0033195214   -1.988474 |
 17. | GB00B1WY2338   1988       8   .0426642   -10.276688         5   GB00B1WY2338    1.788215 |
 18. | GB00B1WY2338   1988       9   .0426642    14.096916         5   GB00B1WY2338    1.788215 |
 19. | GB00B1WY2338   1988      10   .0426642     1.544416         5   GB00B1WY2338    1.788215 |
 20. | GB00B61TVQ02   1988       8   .0095176   -2.8571427         2   GB00B61TVQ02    .5322129 |
     |------------------------------------------------------------------------------------------|
 21. | GB00B61TVQ02   1988       9   .0095176    3.9215684         2   GB00B61TVQ02    .5322129 |
     +------------------------------------------------------------------------------------------+

.

As an aside, the fist line of your previous code should have probably been:

Code:

bys year month: xtile change5 = lvchange_abs, nq(5)

Kind regards,
Carlo
(Stata 19.0)

Comment

Nick Cox

Join Date: Mar 2014

Posts: 36058
#3

28 May 2022, 05:39

I think the first line of #1 was intended. Although Juan Gonzalex should tell us about community-contributed commands he is using (see FAQ Advice #12), astile is from SSC (the as means Attaullah Shah in this case).
Comment
Juan Gonzalex

Join Date: May 2022

Posts: 59
#4

28 May 2022, 08:25

Hi Carlo and Nick and thanks for your answers,

I am indeed using astile intended. Regarding the example you gave me, I don't think that is what I'm looking for, because for example all observations in portfolio 1 for month 8 should have the same mean. I'm replicating the research method of another paper and this is what they say about it:

"For each month during 1975-2002, we rank all firms in our sample by their leverage change during the previous fiscal quarter. We then sort the firms into ten portfolios according to their leverage change rankings, with portfolio one having the lowest leverage change and portfolio ten having the highest. Since different firms end their fiscal quarters in different months, we perform the sorting monthly, and the resulting portfolios are different in every month. We calculate the monthly returns for each portfolio, both equal-weighted and market-value-weighted. "

I have 5 portfolios instead of 10 due to having less data. For example, for the year 1988, month 8, all observations in portfolio 1 should have the same mean, which is the mean of the returns for the stocks of portfolio 1. 1988, month 9, all observations in portfolio 1 should have the same mean, but probably a different mean than in month 8.

If it's not clear please let me know.
Comment

Carlo Lazzaro

Join Date: Apr 2014
Posts: 17854

29 May 2022, 05:35

Juan:
I'm possibly missing out on something but the portfolio-specific monthly average is actually the same across months:

Code:

  1. | GB0001826634   1988       8   .0018346    -8.154499         1   GB0001826634   -2.706734 |
  2. | GB0001826634   1988       9   .0018346     1.869154         1   GB0001826634   -2.706734 |
  3. | GB0001826634   1988      10   .0018346   -1.8348575         1   GB0001826634   -2.706734 |

Kind regards,
Carlo
(Stata 19.0)

Comment

Juan Gonzalex

Join Date: May 2022

Posts: 59
#6

29 May 2022, 11:37

Hi Carlo, in post #2, line 1 and line 11 should have the same average, because they both are in portfolio 1 for month 8 of year 1988.
Comment

Carlo Lazzaro

Join Date: Apr 2014
Posts: 17854

29 May 2022, 12:09

Juan:
two option here (hopfully one of them is on spot).
1st option:

Code:

. bysort change5 year (month): egen wanted=mean( ri )

. list

     +------------------------------------------------------------------------------------------+
     |         isin   year   month   lvchan~s           ri   change5       new_isin      wanted |
     |------------------------------------------------------------------------------------------|
  1. | GB0030913577   1988       8    .003004    -5.144036         1   GB0030913577   -1.136841 |
  2. | GB0001826634   1988       8   .0018346    -8.154499         1   GB0001826634   -1.136841 |
  3. | GB0030913577   1988       9    .003004    5.2060723         1   GB0030913577   -1.136841 |
  4. | GB0001826634   1988       9   .0018346     1.869154         1   GB0001826634   -1.136841 |
  5. | GB0030913577   1988      10    .003004    1.2371182         1   GB0030913577   -1.136841 |
     |------------------------------------------------------------------------------------------|
  6. | GB0001826634   1988      10   .0018346   -1.8348575         1   GB0001826634   -1.136841 |
  7. | GB0004052071   1988       7    .003304     9.566951         2   GB0004052071    2.939465 |
  8. | GB0004052071   1988       8    .003304   -3.2786906         2   GB0004052071    2.939465 |
  9. | GB00B61TVQ02   1988       8   .0095176   -2.8571427         2   GB00B61TVQ02    2.939465 |
 10. | GB00B61TVQ02   1988       9   .0095176    3.9215684         2   GB00B61TVQ02    2.939465 |
     |------------------------------------------------------------------------------------------|
 11. | GB0004052071   1988       9    .003304    7.3446393         2   GB0004052071    2.939465 |
 12. | GB0009223206   1988       8   .0168712    -11.46245         3   GB0009223206   -.1508655 |
 13. | GB0009223206   1988       9   .0168712    11.160719         3   GB0009223206   -.1508655 |
 14. | GB0033195214   1988       8   .0211871   -14.440441         4   GB0033195214   -.2075553 |
 15. | GB0007188757   1988       8   .0386393    -2.752304         4   GB0007188757   -.2075553 |
     |------------------------------------------------------------------------------------------|
 16. | GB0007188757   1988       9   .0386393     7.679951         4   GB0007188757   -.2075553 |
 17. | GB0033195214   1988       9   .0211871    6.4669967         4   GB0033195214   -.2075553 |
 18. | GB0033195214   1988      10   .0211871    2.0080209         4   GB0033195214   -.2075553 |
 19. | GB00B1WY2338   1988       8   .0426642   -10.276688         5   GB00B1WY2338    1.788215 |
 20. | GB00B1WY2338   1988       9   .0426642    14.096916         5   GB00B1WY2338    1.788215 |
     |------------------------------------------------------------------------------------------|
 21. | GB00B1WY2338   1988      10   .0426642     1.544416         5   GB00B1WY2338    1.788215 |
     +------------------------------------------------------------------------------------------+

.

2nd option:

Code:

. bysort change5 year month: egen wanted2=mean( ri )

. list isin year month lvchange_abs ri change5 new_isin wanted2

     +------------------------------------------------------------------------------------------+
     |         isin   year   month   lvchan~s           ri   change5       new_isin     wanted2 |
     |------------------------------------------------------------------------------------------|
  1. | GB0030913577   1988       8    .003004    -5.144036         1   GB0030913577   -6.649268 |
  2. | GB0001826634   1988       8   .0018346    -8.154499         1   GB0001826634   -6.649268 |
  3. | GB0030913577   1988       9    .003004    5.2060723         1   GB0030913577    3.537613 |
  4. | GB0001826634   1988       9   .0018346     1.869154         1   GB0001826634    3.537613 |
  5. | GB0030913577   1988      10    .003004    1.2371182         1   GB0030913577   -.2988696 |
     |------------------------------------------------------------------------------------------|
  6. | GB0001826634   1988      10   .0018346   -1.8348575         1   GB0001826634   -.2988696 |
  7. | GB0004052071   1988       7    .003304     9.566951         2   GB0004052071    9.566951 |
  8. | GB0004052071   1988       8    .003304   -3.2786906         2   GB0004052071   -3.067917 |
  9. | GB00B61TVQ02   1988       8   .0095176   -2.8571427         2   GB00B61TVQ02   -3.067917 |
 10. | GB00B61TVQ02   1988       9   .0095176    3.9215684         2   GB00B61TVQ02    5.633104 |
     |------------------------------------------------------------------------------------------|
 11. | GB0004052071   1988       9    .003304    7.3446393         2   GB0004052071    5.633104 |
 12. | GB0009223206   1988       8   .0168712    -11.46245         3   GB0009223206   -11.46245 |
 13. | GB0009223206   1988       9   .0168712    11.160719         3   GB0009223206    11.16072 |
 14. | GB0033195214   1988       8   .0211871   -14.440441         4   GB0033195214   -8.596373 |
 15. | GB0007188757   1988       8   .0386393    -2.752304         4   GB0007188757   -8.596373 |
     |------------------------------------------------------------------------------------------|
 16. | GB0007188757   1988       9   .0386393     7.679951         4   GB0007188757    7.073474 |
 17. | GB0033195214   1988       9   .0211871    6.4669967         4   GB0033195214    7.073474 |
 18. | GB0033195214   1988      10   .0211871    2.0080209         4   GB0033195214    2.008021 |
 19. | GB00B1WY2338   1988       8   .0426642   -10.276688         5   GB00B1WY2338   -10.27669 |
 20. | GB00B1WY2338   1988       9   .0426642    14.096916         5   GB00B1WY2338    14.09692 |
     |------------------------------------------------------------------------------------------|
 21. | GB00B1WY2338   1988      10   .0426642     1.544416         5   GB00B1WY2338    1.544416 |
     +------------------------------------------------------------------------------------------+

.

Kind regards,
Carlo
(Stata 19.0)

Comment

Juan Gonzalex

Join Date: May 2022

Posts: 59
#8

29 May 2022, 12:13

Hi Carlo, the second option seems to be the answer. I don't know how you did it, but in the mean time I got the result I wanted by the following code:

Code:

bysort change5 year month: egen mean_port = mean(ri)

Did you have the same?

Is there a way to add the condition of group size? For example, only calculate the mean if a portfolio has more than 20 stocks.

Thanks for trying to help me!

Last edited by Juan Gonzalex; 29 May 2022, 12:16.
Comment

Carlo Lazzaro

Join Date: Apr 2014
Posts: 17854

29 May 2022, 23:22

Juan:
you may want to try:

Code:

. bysort change5: egen wanted=count( new_isin)

. . list isin year month lvchange_abs ri change5 new_isin wanted2 if wanted ==5

     +------------------------------------------------------------------------------------------+
     |         isin   year   month   lvchan~s           ri   change5       new_isin     wanted2 |
     |------------------------------------------------------------------------------------------|
  7. | GB0004052071   1988       7    .003304     9.566951         2   GB0004052071    9.566951 |
  8. | GB0004052071   1988       8    .003304   -3.2786906         2   GB0004052071   -3.067917 |
  9. | GB00B61TVQ02   1988       8   .0095176   -2.8571427         2   GB00B61TVQ02   -3.067917 |
 10. | GB0004052071   1988       9    .003304    7.3446393         2   GB0004052071    5.633104 |
 11. | GB00B61TVQ02   1988       9   .0095176    3.9215684         2   GB00B61TVQ02    5.633104 |
     |------------------------------------------------------------------------------------------|
 14. | GB0033195214   1988       8   .0211871   -14.440441         4   GB0033195214   -8.596373 |
 15. | GB0007188757   1988       8   .0386393    -2.752304         4   GB0007188757   -8.596373 |
 16. | GB0033195214   1988       9   .0211871    6.4669967         4   GB0033195214    7.073474 |
 17. | GB0007188757   1988       9   .0386393     7.679951         4   GB0007188757    7.073474 |
 18. | GB0033195214   1988      10   .0211871    2.0080209         4   GB0033195214    2.008021 |
     +------------------------------------------------------------------------------------------+

.

If what above does not do the trick, change the variable between brackets in -egen. accordingly.

Kind regards,
Carlo
(Stata 19.0)

Comment

Juan Gonzalex

Join Date: May 2022

Posts: 59
#10

30 May 2022, 01:12

Hi, thanks to you I figured it out! I used the code:

Code:

bysort change5 year month: egen count = count(isin)

And this gave me the desired result, thank you so much for your help!!
Comment

Announcement