Calculating monthly returns and lagged monthly returns

[Ofir Gefen]

Dear Statalist,

I'm struggling with computing a compounded monthly return. Later on, I also need to lag it for one week as to before running my regressions.

My data structure in panel data:

stock number day daily return

1 25jan2010 0.02
1 26jan2010 0
1 27jan2010 -0.01
2 25jan2010 -0.03
2 26jan2010 0.8
2 27jan2010 -0.001
. .
. .
. .


At the end, I want to have a new variable; comulative_return, that computes for each stock its monthly return.

I've looked into https://www.stata.com/statalist/arch...msg00563.html#
But unfortunately, this example is for the entire data sample and does not start counting from zero at the beginning of next month. After all that, I want to create a 'week_lag_comulative_return' that does the same, only waits for a week before starting. I think that is simple, though so as long as anyone can help with the first should be fine.

Thank you!

[Phil Bromiley]

You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex. Also, assume we are not in your area. So, in your case, it would help to know exactly the formula you want to use for compounded monthly return based on daily return. I'm also not sure how you do monthly returns then lag them a week. Are you talking about monthly returns from t-37 to t-7, or actual months or what?

If you want a calculation by month, you can use bysort month: egen x=*() or bysort month: gen x=*() where * is any of a number of different functions specified in the documentation - total, count, mean, etc. If you need to do the t-37 to t-7, given that you appear to be a beginner, it might be easiest for your to create your lags and then use egen with rowtotal etc. to do the calculations. It is not the most efficient way to do it, but it is easily understandable.

forvalues t=7/37 {
g L`t'return'=L`t'.return
}
egen sumreturn=rowtotal(L7return L8return L9return ...)

Where ... means you put in L10return to L37return.

Note - I have not checked this programming so there may be errors.

Finally, if you need to do this incrementally (i.e., take return in t-37, then apply return in t-36, then apply return in t-35, etc.) you can do it with the same sort of loop with lags that I did to lag variables.

[Ofir Gefen]

Thank you so much! worked out woderfully.