Calculating weighted returns panel data

[Bram Smith]

Hi all,

I am trying to do something in Stata but I am not sure where to start. I have a set of panel data containing the following information:
- 2320 firms,
- pricing data for every firm for 36 months
- market values (MV) for every firm for 36 months

I calculated returns for every firm for every month.


Now I am facing the following challenges in calculating weighted returns.

1. I want to calculate equally weighted returns across the entire sample. So every firm has the same weight in the portfolio. (so 1/2320 is the return for every firm, I divide this by 36 so also every month has the same weight). In Excel this is relatively straight forward but I would like to do this in stata in case my sample changes and I could just use the same formula again.
2. I want to calculate value-weighted returns across the entire sample. Based on the market value (MV) at month 1, I want to assign a certain weight to every firm. I want to do this by summing the market values for every firm at month 1 and then divide the MV of each individual firm by the sum of market values. Again this needs to be divided by 36 so every month receive the same weight.

Above are the first problems I am facing. But there are some more and complicated ones which I will try to explain below.

For some firms, no more price data is available after a certain time, and hence no more returns (which, for example, could be after 5 month for firm X, and could be after 27 month for firm Y, etc). If this happens, I want to allocate the weights of these firms to the remaining firms in the portfolio. So if one firm has no more returns after month 5, the other firms should receive an equal weight of 1/2319 for the subsequent months. In case of the value weight, a new total market value has to be calculated and new weights have to be calculated for each firm for the subsequent months.

I would be grateful if there is someone who could help me out.

Kind regards,
Bram

[Attaullah Shah]

Bram, you have not shared a sample of your data set, that would make it easy for us to understand what you want. I bet that you want to find average of the portfolio in each month, then the strategy would be to use egen command.

bys month: egen mean_ret = mean(ret)

For value weighted returns, you need to install the wrapper program for egen, so

net install _gwtmean.pkg

After installation, you can type

bys month: egen  wmean_ret = wtmean(ret), weight(market_cap)

[Bram Smith]

Hi Attaullah Shah,

Thanks for your comment. Actually I want to calculate buy-and-hold returns for every month. I was thinking about doing it the following way.

bys month: egen BHAR_MSCI_EW_total = total(BHAR_MSCI_EW)
bys month: egen BHAR_MSCI_VW_total = total(BHAR_MSCI_VW)

However, first I need the value-weighted and equally-weighted returns. I have attached a small example file with 8 firms.
In this file id=9 has no more prices and hence no more Buy-and-hold returns (BHAR) after month 15. So I would like to allocate the weight of id=9 to the other 7 firms (id 7 is already dropped earlier). (till month 15 every firm has a weight of (1/8) 12,5%, from month 16 till 36 firm 9 should receive a 0% weight and the other firms (1/7) 14,29% for these months).

Next firm 5 has no more prices and BHAR from month 29 onwards. I want to allocate the weight of this firm to the other 6 remaining firms.

Do you understand what I mean?