Log returns

[Steven John]

Dear Statalister,

I have a few questions with respect to the table depicted below. Permno represents only one of the many funds in my dataset (CRSP Ziman).

Objective: calculate the past 11-month log return (one-month lagged) for all months of every fund. So, for dec. 1987 I want to calculate the past 11-month log return of the prior 11 months, and for jan. 1988 also... and so forth.

Does someone know how to do this? I already tried many different things, but unfortunately, I have not succeeded. Help would be highly appreciated!

permno 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230 11230

date 30apr1987 29may1987 30jun1987 31jul1987 31aug1987 30sep1987 30oct1987 30nov1987 31dec1987 29jan1988 29feb1988 31mar1988 29apr1988 31may1988 30jun1988 29jul1988 31aug1988 30sep1988 31oct1988 30nov1988 30dec1988 31jan1989 28feb1989 31mar1989 28apr1989 31may1989 30jun1989 31jul1989 31aug1989 29sep1989 31oct1989 30nov1989 29dec1989

year 1987 1987 1987 1987 1987 1987 1987 1987 1987 1988 1988 1988 1988 1988 1988 1988 1988 1988 1988 1988 1988 1989 1989 1989 1989 1989 1989 1989 1989 1989 1989 1989 1989

price 10.625 10.25 10.125 10.5 9.75 9.75 9.5 9.5 9 9.125 9.5 9 8.875 8.75 8.875 9.125 8.5 8.625 8.375 7.875 7.375 8 7.75 8.125 8.25 7.75 7.75 8 7.5 7.25 6.625 4.875 5.5

return . -.0353 -.0122 .0667 -.0714 0 .0051 0 -.0211 .0139 .0411 -.0526 .0194 -.0141 .0143 .062 -.0685 .0147 .0058 -.0597 -.0254 .0847 -.0312 .0484 .0523 -.0606 0 .071 -.0625 -.0333 -.0448 -.2642 .2041

Best,

Steven John

[Clyde Schechter]

What does "past 11 month log return" mean? Each month has a return, and some of these are negative or zero, so no logarithm can be taken.

Even if all the returns were positive, in what way do you want to combine the 11 months worth of log-returns? The total? The average? Something else?

[Steven John]

Thanks for your comment! I thought I needed log, but clearly, that is not the case...

I'll try to explain what I intend to do: for every month in my data sample, I want to rank all available funds by their past-11-month returns (one month lagged) and group them into equally weighted tercile portfolios (low/ middle/ top).

So, basically, the fund's return over the past 11 months determines the position of the fund on a ranking which consists of all funds. I want to do this for every month. Thus, for every month I have three different portfolios, which consist of the low/middle/top performing funds (measured by their return over the past 11 months).

Do you have an idea how I can do this?

Best,
Steven John

[Clyde Schechter]

So, to the extent I understand, you can get the accumulated returns of the preceding 11 months by:

gen mdate = mofd(date)
format mdate %td
xtset permno mdate
gen return11 = L1.price/L11.price - 1 // MULTIPLY BY 100 IF YOU WANT THIS IN PERCENT

Note: I do not work in finance and my understanding of "returns" is gleaned from what I have seen posted here on Statalist. Different people seem to have different definitions of them. Here I'm looking at the price 1 month previous divided by the price 11 months earlier, then subtract 1. Modify the code if you have something different in mind.

The key here is to create a monthly date variable so that when you -xtset- the data, the approximate one-month intervals in your dates will be treated by Stata as consecutive months rather than as a daily time series with large gaps.

From there, to rank stocks into terciles according to their return in any month it's:

levelsof mdate, local(months)
gen tercile = .
foreach m of local months {
    xtile temp return11 if mdate == `m' nq(3)
    replce tercile = temp if mdate == `m'
    drop temp
}

By the way, in the future please use the -dataex- command to post example data. In this case, I did not need to import your example to Stata to experiment with code. But had that been necessary, HTML tables can prove difficult. Run -ssc install dataex- to install the -dataex- command, and then run -help dataex- to read the simple instructions for using it. By using -dataex- you make it possible for those who want to help you to create a completely faithful replica of your Stata example with a simple copy/paste operation.

[Nick Cox]

I think there are a couple of small typos in the otherwise excellent advice of Clyde Schechter

levelsof mdate, local(months)
gen tercile = .
foreach m of local months {
    xtile temp = return11 if mdate == `m', nq(3)
    replce tercile = temp if mdate == `m'
    drop temp
}

Other ways to do it that I know:

The egen function xtile() in egenmore from SSC. Will be slow (very slow) for large (very large) datasets.

astile (SSC). Will be (much) faster.

fastxtile (GitHub, forthcoming as I write on SSC). Will be (much) faster. The most versatile command I know in this territory (more options than astile).