CODEWITHSUNDEEP
returnstata
June_Stephanie
June_Stephanie

Reputation: 17

Stata daily return compounded to monthly

I have a panel datast of daily stock returns. For each stock, I need to calculate its compound monthly return (say 30 days):

(1+r_1)*(1+r_2)*...*(1+r_30) - 1

Stock identifier is permno, dm is year and month indicator. I use the following Stata code:

gen gross_ret = 1+ret
bys permno dm: gen prod = sum(ln(gross_ret))
by permno dm: replace prod = exp(prod[_N])
gen mret = prod - 1

I randomly pick permno dm combinations to verify the results, and they seem to be right. However, I do see extreme values such as mret = 26. I guess the reason is that some gross_ret is near 0, so ln(gross_ret) is very high. Then I double check using CRSP monthly return data, I found 99% of differences between compounding return (calculated by the code above) and CRSP monthly return smaller than 0.0007, which is acceptable. But the largest absolute difference is 3.24, which is too big and might affect my final result (I have been trouble shooting for two whole days, and this might be my last resort).

Is my way of calculating monthly return wrong? If so, please suggest a better way.

Upvotes: 0

Views: 1996

Answers (1)

user4690969
user4690969

Reputation:

Perhaps you might achieve better accuracy by avoiding the logarithims, along with using double for intermediate results, as Nick recommends.

bys permno dm: gen double prod = (1+ret) if _n==1
by permno dm: replace prod = (1+ret)*prod[_n-1] if _n>1
by permno dm: gen mret = prod[_N] - 1

With that said, if you have a daily date variable (either year-month-day or just day), you could include it in the sort so your data is in the order you expect it after this process.

bys permno dm (day): gen double prod = (1+ret) if _n==1
by permno dm: replace prod = (1+ret)*prod[_n-1] if _n>1
by permno dm: gen mret = prod[_N] - 1

Upvotes: 1

Related Questions