Assign value to the next observations

[Juan Gonzalex]

Hi there, I'm currently writing my master thesis and I'm stuck with the following problem. I'm trying to replicate the methodology of another paper, which states that: The excess return on a firm’s stock is defined as the difference between the firm’s actual return and the expected return from a two-parameter market model. The data are drawn from the CRSP daily stock return file. The parameters of the market model are computed for each firm using one year’s data, those of the fifth year prior to the observation year. The excess returns are computed over a period of four years prior to the observation year. (For example, consider a firm which was a target in the year 1975. Data from 1970 are used to estimate the market model, and the excess returns are computed over the period 1971-1974.) The average excess return is computed as the average excess return per day over this four-year period.

I have daily data and I have calculated the alphas and betas with the following command:

rangestat (reg) return sp_return, int(-252 0) by(cusip)

This calculates the regression coefficients in 1 trading year (252 days). However, I don't really know how to take the alpha (b_cons) and the beta (b_sp_return) of the fifth year prior to the observation year (which would be the values of the 253rd observation or dateid) and calculate the excess returns. The formula to calculate the excess returns is return - (rf + beta*5), where 5 is the market risk premium in percent and rf the daily risk-free rate, also in percent. Somehow, I would have to assign the alpha and beta for the next four years starting from the fifth year prior to the observation year.

Here is a sample of my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input float date int year str9 cusip double return float(sp_return rf) double(b_cons b_sp_return) float dateid
15698 2002 "60740F105" -2.2584677  -.54715276 3.947 -.17353570104781457 .8269450190161081 249
15700 2002 "60740F105"  1.3478875   -.3148556 3.909 -.16646314817168809  .826357899625189 250
15701 2002 "60740F105"  .37999153  -1.6028523 3.811  -.1594215250656585 .8221926723926297 251
15704 2002 "60740F105" -1.3123035    .4557848 3.792 -.16560448700447927 .8209435159003442 252
15705 2002 "60740F105"  .17900467   .04889965 3.818 -.16439477230835475 .8210089093535415 253
15707 2003 "60740F105"    4.62029   3.3200026 4.031 -.16176053376737543  .833686397212524 254
15708 2003 "60740F105"  -5.392212  -.04840493 4.019  -.1614445910376618   .83837992651035 255
15711 2003 "60740F105" -.38685203   2.2474408 4.055 -.16075809779018924 .8346701720342629 256
15712 2003 "60740F105" -2.5889933    -.654459 4.008 -.16199218656414494 .8377665254173187 257
15713 2003 "60740F105" -1.9933581   -1.408559 4.021 -.16129649747173913 .8383470362855293 258
15714 2003 "60740F105"  -.6779671    1.939714 4.174  -.1803059842683304  .833189997054967 259
15715 2003 "60740F105"  1.5017033 -.001078844  4.14 -.18279371223400062 .8347334440303571 260
15718 2003 "60740F105"  1.8897176  -.14122725 4.123 -.17415083446007254 .8345218871330222 261
15719 2003 "60740F105" -.33661127    .5829811 4.081 -.17776570146634163 .8342438571395463 262
15720 2003 "60740F105"  .26489496  -1.4425874 4.061 -.16700404677951328 .8300839843519356 263
15721 2003 "60740F105" -.92470646    -.394243 4.083 -.17413252271775526 .8288109523832407 264
15722 2003 "60740F105"  .54000616  -1.4016986 4.017  -.1555925759208614  .818527698991505 265
15726 2003 "60740F105"   .2586007  -1.5702307 3.973  -.1604736663839399 .8105876007014199 266
15727 2003 "60740F105" -.79364777  -1.0432422 3.915 -.15476626906341773 .8086371814075637 267
15728 2003 "60740F105" -.99999905   1.0223627 3.941 -.16974459899105387 .8079769873923169 268
15729 2003 "60740F105" -3.9057255  -2.9233456 3.937 -.17433771864264347 .8135958006620903 269
15732 2003 "60740F105" -2.5227785  -1.6159773 3.966  -.1804177777475772 .8154586288054988 270
15733 2003 "60740F105"  .64702034   1.3050437 3.973 -.17082362667889692 .8157238746172637 271
15734 2003 "60740F105"  2.7142882    .6778955 4.023 -.15986096659660476 .8182974469211444 272
15735 2003 "60740F105"  -2.086234  -2.2849262 3.971 -.16300915453998369 .8216786334000201 273
15736 2003 "60740F105"  1.1363626   1.3130307 3.966 -.16632101356832707 .8202796261760722 274
15739 2003 "60740F105"  -1.053369    .5399108 3.998 -.19351465037005372 .8072802582214351 275
15740 2003 "60740F105"   .5677819  -1.4087796 3.927 -.19159345223354246 .8049252572778351 276
15741 2003 "60740F105" -.84685683  -.54350495 4.002  -.1837703305708294 .7969804147202091 277
15742 2003 "60740F105"  3.1316638   -.6448627 3.946  -.1790815568014052 .7951134975202648 278
15743 2003 "60740F105"  -4.071772  -1.0093689 3.932 -.19399830778355304 .7997259900254369 279
15746 2003 "60740F105"  2.3741007    .7569075  3.97 -.20277695862783557 .8034276154859916 280
15747 2003 "60740F105" -1.6162992   -.8098304 3.959 -.20147440758373908 .8065038159134019 281
end
format %td date

Is there an easy way to do this?

Thanks

[Clyde Schechter]

I'm sorry, I don't follow what you are trying to do here, so I'm not going to attempt to offer you specific code. But from the overall description, I have the sense that the -rangerun- command, by Robert Picard, available from SSC, will help you. Take a look at it.

[Clyde Schechter]

I spent a little more time trying to figure out your problem. I still don't understand it fully, but it is also not possible to experiment with your example because it does not even contain 5 years worth of data. Of course, an example that did would be too large to post here. So I've made a toy data set that does. (I skimped a little and only have data on the first day of each month in each year, but that doesn't affect the code at all.)

And I don't understand your calculation of excess return, so for this demonstration I have used a more simplified version of excess return: the observed return minus the return predicted by regression in the 5 year lagged observations.

// CREATE TOY DATA SET
clear*

set obs 2
gen cusip = _n
expand 10
by cusip, sort: gen year = 2000 + _n
expand 12
by cusip year, sort: gen date = mdy(_n, 1, year)
format date %td

set seed 1234

gen return_sp = rnormal(0, 1)
gen param = cond(cusip == 1, 0.3, 0.9)
gen return = param*return_sp + (1-param)*rnormal(0, 1)
drop param

//    PROGRAM TO CALCULATE DESIRED RESULT FOR A SINGLE CUSIP-YEAR COMBINATION
capture program drop one_calculation
program define one_calculation
    sort cusip year
    if year[_N] - year[1] == 4 {    // IF THERE IS ENOUGH DATA
        local base_year = year[1]
        regress return return_sp if year == `base_year' // REGRESS IN YEAR 5 LAG
        predict return_hat if year > `base_year' // PREDICT IN 1-4 YEAR LAGS
        gen excess_return = return - return_hat // CALCULATE EXCESS RETURN
        summ excess_return, meanonly // GET MEAN VALUE
        gen avg_xs_return_4_lag_yrs = r(mean)
        drop return_hat excess_return
    }
    else { // DON'T HAVE DATA GOING 5 YEARS BACK--SKIP THESE
        clear
    }
    exit
end

//    DO IT!
rangerun one_calculation, by(cusip) interval(year -5 -1)

I hope this will give you a good start. You will need to adapt this to your actual data set, and you will need to replace my calculation of excess return by the formula or algorithm you actually want.

Note: This code can be made more efficient. It needlessly repeats the same calculations for every observation of the same cusip in the same year, even though the details and results are inevitably identical. In -help rangerun-, if you click on the link "Controlling the sample: Median salary of non-teammates" that is about 1/4th of the way down, it will take you to an example of using -rangerun- in a way that avoids that.

Added: I forgot to mention in #2 and here that in order to use -rangerun- you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.