Hi everyone,

I am kinda lost at the moment and thus would like to ask for your advice.

I am evaluation every stock based on specific ratios on June each year. The stocks will be allocated into different portfolios based on their ratios on June of year x. I want to calculate the total return of each stock from Juli to June the following year, however! the issue is as followed:

As you can see on the data below; stock "11691" was initially in portfolio 1 and then moved to portfolio 2 in the following june. Which is okay and should stay that way, but this poses difficulties in calculating the TOTAL annual return of all stocks, , since I initially wrote a code that calculated the returned the total of all stocks within the portfolio 1. However, since the end_v of stock 11691 lies in June of the following year and it changed from portfolio 2 to 1, my code does not pick up the end_v of this particulare stock and other stocks that display the same issue.

The fact that the stock moved into portfolio 2 is not a problem, I just need a code that incorporates stock return for the entire returnperiod (see column) that were in portfolio 1 on the June of each year

This is the code that I tried and didn't work:

by returnperiod: egen test = total(PE_MCH_LowPE) if BIG_PE_portfolio == 1 & public_month == 6


permno public_date public_month public_year BIG_PE_portfolio initial_v end_v dividend_total returnperiod annual_return
11691 31jul2009 7 2009 1 15.30571 2009
11691 31aug2009 8 2009 1 2009
11691 30sep2009 9 2009 1 2009
11691 30oct2009 10 2009 1 2009
11691 30nov2009 11 2009 1 2009
11691 31dec2009 12 2009 1 2009
11691 29jan2010 1 2010 1 2009
11691 26feb2010 2 2010 1 2009
11691 31mar2010 3 2010 1 2009
11691 30apr2010 4 2010 1 2009
11691 28may2010 5 2010 1 2009
11691 30jun2010 6 2010 2 15.59905 1.26 2009 10.14872

I hope I explained the situation clear enough guys.

Best wishes and thanks in advance,
John L.


edit: Since the posted data might look confusing, I would like to add a screenshot: