Need to compound monthly returns from CRSP monthly file to get quarterly returns. Using the egenmore package.

[Dev Irani]

I am trying to get quarterly returns for securities. So I am using the CRSP monthly stock file, to run the following code:

generate Ri=RET+1
bys PERMNO date: egen quarterlyRET= Ri[_n]*Ri[_n-1]*Ri[_n-2]

The dates are month wise so essentially quarterlyRET should be multiply the current month return with the one prior and the one even before that, thus compounding 3 months of returns.

But I get the error

unknown egen function Ri[_n]*Ri[_n-1]*Ri[_n-2]()

I have already installed the -egenmore- package, and I thought I could potentially run this.
Is there a way to do this?

[Dev Irani]

I went ahead and coded a loop. But its highly inefficient for a file with millions of observations, and Tens of thousand of PERMNOs

egen uniquePERMNO=group(PERMNO)
summarize uniquePERMNO
generate Ri=RET+1
generate qri=.

forvalues i = 1/`r(max)' {
    replace qRi=Ri[_n]*Ri[_n-1]*Ri[_n-2] if uniquePERMNO == `i'    
}

Additionally, I have no way to know in case if any months data is missing for a security then it will just take in whatever is previous month's data. Ofcourse my prior method also doesnt have this check available.

[Clyde Schechter]

I'm going to have to make assumptions about your data, as you have not shown example data.

I assume that PERMNO is a numeric variable that identifies stocks, and the date is a Stata internal format monthly date variable. This code will either not run at all or will produce incorrect results if these assumptions are incorrect. I also assume that your RET variable is denominated in percent. So if these are not the way your data is you must transform your data to make them so.

isid PERMNO date, sort
gen factor = 1+RET/100
gen qdate = qofd(dofm(date))
format qofd %tq
by PERMNO qdate (date), sort: gen quarterly_return = factor[1]*factor[2]*factor[3]
replace quarterly_return = 100*(quarterly_return - 1)

This will give you the quarterly return, denominated in percent.

If you need assistance modifying your data so that this code will work correctly, when posting back, be sure to include example data, and be sure to use the -dataex- command to do that. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Nick Cox]

It's a side issue, but the mention of egenmore in the title and of egen in #1 and #2 deserves a brief comment.

The use of egen's group() in #2 was standard and successful. The statement that failed in #1 was I guess confusing egen with gen (meaning generate). egen in no sense includes generate as a special case. Any egen call must call one, and only one, egen function, where egen function means, with some risk of seeming circular, a command specifically written to work with egen. (It might have been clearer if Stata's developers had used some different term there. but they didn't.)

The abstract definition there is likely to be clear only to those who understand already. But let's try an example.When egen sees a reference to the group() function it looks for a program called _ggroup (and finds one, unless your Stata is corrupted). The _g is the not-so-secret code for what kind of thing to look for. So If you have a calculation that would fit within the egen framework, you can code up _gfoo as a command with syntax that fits and then call foo() within an egen call. That's how egen can be extended, and egenmore is a rag-bag of extensions, and there are yet others in Stataland.

But back to the thread: I am the maintainer of egenmore (SSC) and don't see that it has anything to offer here that is better than a direct attack, as in #3.