How to calculate the mean over all funds

[John Lei]

Hey guys,

I would like to know how I can calcute the mean, standard deviation and median of some variables over all funds in my dataset.
My dataset looks like the following:
FundID StockID date fund_monthly_return
1 383 31july2008 0.22
3 232 28january2007 -0.37
2 239 27february2009 0.24
1 483 2january2005 0.11
1 483 2february2004 0.37

So I do not know how to calculate the return over all funds because if I simply calculate the mean of the return then it would be the return over all observations.
What would be correct? I also want to do this with the age of the funds but the age gets higher over time, sol what should I do here?

Thanks, John.

[Roman Goossens]

Hi John,

I am not sure that I understand the problem. So you want to:

1. Calculate the mean return for each fund (i.e. average return of Fund 1, Fund 2, etc)?
2. Calculate the average return for a given date (e.g. average return of all funds on 31 july 2008)
3. Calculate the average return for all funds for the whole dataset?

If it is 1) then

bysort FundID: egen mean_fund_return = mean(fund_monthly_return)

If it is 2) then

bysort date:  egen  mean_monthly_return = mean(fund_monthly_return)

If it is 3) then

egen mean_fund_return_alldates = mean(fund_monthly_return)

The same for the standard deviation (just replace "mean" with "sd") and the median (just replace "mean" with "median").

Best,
Roman

[John Lei]

Hey thanks Roman,

I would like to know the average return for all funds so option 3). But isn't your solution going to calculate the mean of all observations? So if one fund will be two times in the dataset then two times its return will be in the calculation of the average return and for example the other funds only one time. So I think this would not be correct how to calculate the mean return of all funds because it would depend a lot on how often a specific fund occurs. Please correct me if I am wrong. For example the FundID of 1 occurs 3 times in my example dataset

[John Lei]

I am not sure but if I calculate the average of a given variable for every fund and then use it only one time to calculate the average of all funds then I think that would be correct. But I do not know how to do that.
So maybe calculate with bysort FundID: egen average_per_fund = mean(fund_mothly_return) so then I have the average return for every indivual fund
and then I calculate the average of all those values, but include every value only one time

[Phil Bromiley]

If you want only one observation per fund, then you may want to collapse your data, do the means, and then merge them back into the larger dataset.

[Joro Kolev]

Easier than what Phil suggests would be to simply tag one observation per group,

egen tagvar = tag(varlist_identifying_group_from_which_we_select _one)

and then to calculate whatever is to be calculated by adding the qualifier

if tagvar

.

[Roman Goossens]

Hi John,

So using the suggestion by Joro it seems like what you need to do is:

bysort FundID: egen average_per_fund = mean(fund_mothly_return) 
egen tagvar = tag(FundID)
egen mean_fund_return_alldates = mean(average_per_fund) if tagvar

By the way, this article by Cox and Longton (2008) https://www.stata-journal.com/sjpdf....iclenum=dm0042 explains the use of "tag" and could be useful for future reference.

Best

[John Lei]

Hey thanks Roman! Thats exactly what I needed.
So if I have a dummy with 1 and zero wether a trade is a buy or sell and want to find out what the average return per fund of the the buys is (so one value over all funds), do have to drop the sells before or after I calculate then mean of every individual fund? (It is a buy if the dummy buy has a value of 1)
So is it:

drop if buy!=1
bysort FundID: egen average_per_fund = mean(fund_mothly_return)
egen tagvar = tag(FundID)
egen mean_fund_return_alldates = mean(average_per_fund) if tagvar

or

bysort FundID: egen average_per_fund = mean(fund_mothly_return)
drop if buy !=1
egen tagvar = tag(FundID)
egen mean_fund_return_alldates = mean(average_per_fund) if tagvar