Computting four-year stock returns without overlap

[Ali Nik]

Hello, I am new to this forum.

I have a panel data of CRSP monthly stock returns since 1959. (the cross section variable is firm id and the time variable is month-year). the panel is then merged with the yearly tape of Cmpustat financial statement data. In order to merge the two panels I take each firm's fiscal year end month in the Compustat into account and generate a month-year variable based on it (in addition to the firm-id) I merge the Compustat and CRSP datasets. I need to calculate four-year-interval stock returns for non-overlapping firm-observations. I try to explain the overlapping issue in the merged panel:

I am currently using the command:
bysort id: generate return= ((1+ret)*(1+F.ret)*.......*(1+F47.ret))-1
after assigning my panel variables by xtset command(xtset id monthyear) in CRSP.
Then I merge this data with Compustat with 1:1 command using id and monthyear as identifying variables.

Assume that firm-id 100 appears in my panel every year from 1960 to 1967. Now If I start counting the monthly returns from the beginning of 1960, 48 months, the years 1960, 1961, 1962, and 1963 will be considered. Then STATA goes to the next firm-year which is firm-id 100 in year 1961. This time years 1961, 1962, 1963, and 1964 will be counted. So you see that there is an overlap for years 1961 to 1963 that are used to compute stock returns for two firm-year observations.
I want the computation to to begin for firm-id 100 in the beginning of 1960 and once the four-year stock return is computed I want to skip the firm-id 100 in years 1961 to 1963 and the next round of counting for this individual firm should begin in the year 1964.

The command is obviously not addequate to do the analysis that I want.

I would appreciate your help.

[Clyde Schechter]

I assume that for each firm you want the four-year blocks to begin with the first year the firm appears in the data, which might or might not be 1960. If I have that right:

gen ret_factor = 1 + ret_factor
by id (year), sort: gen four_year_block_num = floor((year-year[1])/4)
by id four_year_block_num (year), sort: gen return = ret_factor if _n == 1
by id four_year_block_num (year): replace return = return[_n-1]*ret_factor if _n > 1
by id four_year_block_num (year): replace return = return[_N] - 1

By the way, if you do not have a separate year variable, you can extract it from your month-year variable as follows:

gen year = year(dofm(month_year))

where month_year is your Stata internal format monthly variable.

[Ali Nik]

Thank you so much Clyde. This should solve my problem.
I indeed had the year variable, I generated the month-year via ym command.

[Ali Nik]

Unfortunately, I did not get the correct result this way. Let me put some example excel screen shots of the merged sample and how I want it to look like.

The current sample looks like this:

And how I want the end result to be is like this:

As you see I want at least 4 year gep between id-years.

[Clyde Schechter]

It is easy to get from the results of the code in #2 to what you want. I misunderstood your request: I thought you wanted to retain the yearly data, but you wanted the results for 2001 through 2003 to be the same as those for 2000. All you have to do is get rid of the surplus observations:

by id (four_year_block_num year), sort: keep if _n == 1

[Ali Nik]

Great, thank you Clyde. Ill try this out.

[Ali Nik]

This way only the first year that the firm appears is kept in my panel. It doesnt perform what I want. I want the time-difference between firm-years to be at least 4 years.

[Clyde Schechter]

Oh, sorry, my mistake. I put the parentheses in the wrong place. It should be

by id four_year_block_num (year), sort: keep if _n == 1

[Ali Nik]

thank you