how to calculate stock return for the previous 12 months at each month

[Fiona Zhang]

Hi all,

I have a dataset looks like this and I would like to calculate the cumulative stock return for the last 12 months. I found some answers but they did not solve my problems completely. Any suggestions are truly appreciated!!

permno date ret
10000 31dec1985
10000 31jan1986
10000 28feb1986 -.257142872
10000 31mar1986 .365384609
10000 30apr1986 -.098591551
10000 30may1986 -.22265625
10000 30jun1986 -.005025126
10000 31jul1986 -.080808081
10000 29aug1986 -.615384638
10000 30sep1986 -.057142857
10000 31oct1986 -.24242425
10000 28nov1986 .059999999
10000 31dec1986 -.377358496
10000 30jan1987 -.212121218
10000 27feb1987 0
10000 31mar1987 -.384615391
10000 30apr1987 -.0625
10000 29may1987 -.06666667
10000 30jun1987
10001 31dec1985
10001 31jan1986
10001 28feb1986 .020408163
10001 31mar1986 .025200004
10001 30apr1986 .00990099
10001 30may1986 -.009803922

My tasks have two-fold:

1. Compute cumulative return for the last 12 months defined as = (1+ return of month 1) * ((1+ return of month 2)*….*(1+return of month12) -1

The first variable is the permno (the security identifier), the ret is the monthly stock return. I would like to calculate the cumulative stock return for the past 12 months for each security. For example, for permno "10000", at date "30apr 1987", I would like to have the cumulative return starts from "30may 1986" (month 1) to "30apr 1987" (month 12). Likewise, for permno "10000", at date "29may1987", the cumulative return is compounded from "30jun 1986" (month 1) to "29may 1987" (month 12).

2. As you can see, there are some missing values for monthly stock return. I don't know if the monthly stock return is missing, how should I handle it. Can you give me some advice?

I know there are some posts about calculating the cumulative return but none of them compute the cumulative return for the past 12 months.


Thanks a lot for your time and help!!

Best,
Fiona

[Fiona Zhang]

For the second task, if a security (permno date) has less than 6 monthly consecutive returns, then set the cumulative return from 1 to missing. Can anyone give some advise how to achieve it in Stata?

[Max Hoerl]

I don't have a complete solution but here are some suggestions that might help you:

1. Separate your date variable into a day, a month and a year variable (preferably all numeric variables). You can for example extract the month (and similarly day and year) by using the -substr()- function

gen day = substr(date, 1, 2)
gen year = substr(date, 6, 4) 
gen month = substr(date, 3, 3)
replace month = "1" if month == "jan"
//...
destring day month year, replace

Although for that date stuff there probably are Stata functions that do this work for you.

2. For calculating the product you can maybe calculate the logarithm, then calculate the sum and then exponentiate it (or maybe there is a function in Stata that calculates that product for you, I don't know). This step could look something like this:

gen ret_ln = ln(1 + return)
by year: egen ret_cum = total(ret_ln)
replace ret_cum = exp(ret_cum)

This would calculate the cumulative returns for each year. Although I don't know what happens with the missings.

Good luck!

[Clyde Schechter]

I think the solutions proposed in #3 are not optimal.

First, the proposed approach will only calculate the cumulative return for calendar years (Jan-Dec). It will not, for example, give the cumulative return between, say September 2014 and August 2015.

Second, for most purposes, the best way to deal with dates is to use Stata internal format date variables: the parts can then be extracted by functions as needed, but the whole date variable lends itself to sorting and calculations.

There is no command to generate a running product directly in Stata. Max's approach of doing a running some of logarithms will work in this context, but won't work more generally where one might have to be multiplying negative numbers or zeroes. Here, we don't encounter that problem because, by definition, the return cannot be less than -1, so log(1+ret) is always defined (unless ret = -1, which means all is lost, an unusual event.) The code below verifies that ret > -1 throughout before proceeding.

So, here's how I would do this:

clear*
input permno str9 date ret
10000 31dec1985 .
10000 31jan1986 .
10000 28feb1986 -.257142872
10000 31mar1986 .365384609
10000 30apr1986 -.098591551
10000 30may1986 -.22265625
10000 30jun1986 -.005025126
10000 31jul1986 -.080808081
10000 29aug1986 -.615384638
10000 30sep1986 -.057142857
10000 31oct1986 -.24242425
10000 28nov1986 .059999999
10000 31dec1986 -.377358496
10000 30jan1987 -.212121218
10000 27feb1987 0
10000 31mar1987 -.384615391
10000 30apr1987 -.0625
10000 29may1987 -.06666667
10000 30jun1987 .
10001 31dec1985 .
10001 31jan1986 .
10001 28feb1986 .020408163
10001 31mar1986 .025200004
10001 30apr1986 .00990099
10001 30may1986 -.009803922
end

//    CONVERT STRING DATE TO STATA INTERNAL FORMAT
gen sif_date = daily(date, "DMY")
assert missing(sif_date) == missing(date)
format sif_date %td
//    EXTRACT MONTHLY DATE
gen monthly_date = mofd(sif_date)
format monthly_date %tm

//    CALCULATE CUMULATIVE RETURNS
assert ret > -1 if !missing(ret)
gen log_factor = log(1+ret)
rangestat (count) log_factor (sum) log_factor, by(permno) interval(monthly_date -11 0)
gen cumulative_return = exp(log_factor_sum) - 1 if log_factor_count == 12

Notes:
1. This requires the -rangestat- command, written by Robert Picard, Nick Cox & Roberto Ferrer, available from SSC.
2. The missing values for return are a problem. I imagine that within the finance community there is some convention about how this is handled, but that is not my discipline and I don't know it. The code I wrote will only generate the final result, cumulative_return, if there is a full 12 months of data (which is only a small number of observations in this example).