Transforming monthly to annual returns

[Martin Heiroth]

Hi there!

I would like to transform the monthly returns to annual returns with the reference date of April. Therefore I used: (ret=return; dlret=delisting return)

• gen retannual=exp(log(ret+dlret[month])+log(ret+dlret[month-1])+log(ret+dlret[month-2])+log(ret+dlret[month-3])+log(ret+dlret[month-4])+log(ret+dlret[month-5])+log(ret+dlret[month-6])+log(ret+dlret[month-7])+log(ret+dlret[month-8])+log(ret+dlret[month-9])+log(ret+dlret[month-10])+log(ret+dlret[month-11]))
• drop if month != 4

Now my problem is that I just create missing values with this command.
On the other hand i mixed up different companys because some appear and some other disappear with the time so if I just go back and summarize the last 12 months it will result in an error. I need to lock the company to the return but I have no good idea to do so. The image shows such an example of a delisting:

I hope someone can give me an idea how to solve this problem. Many thanks for every help!

All the best,

Martin

[Nick Cox]

Please read the FAQ Advice especially #12 and do not show data or code by photo attachments. Why not and what to do instead are explained there.
All members are requested to read the FAQ Advice before posting. Please see http://www.statalist.org/forums/help

There are lots of tools to make this much easier.

First and foremost, you need to tsset or xtset your data to declare panel (in your case company) and time variables. so that Stata automatically keeps calculations within panel. So look at the help on those commands.

In this particular case, I don't know what you are trying to calculate, but this code looks extremely confused.

exp(log(ret+dlret[month])+log(ret+dlret[month-1])+log(ret+dlret[month-2])+log(ret+dlret[month-3])+log(ret+dlret[month-4])+log(ret+dlret[month-5])+log(ret+dlret[month-6])+log(ret+dlret[month-7])+log(ret+dlret[month-8])+log(ret+dlret[month-9])+log(ret+dlret[month-10])+log(ret+dlret[month-11]))

I get no further than

 
dlret[month]

looking at observation 4038 in your screenshot the variable month is 1. So for that observation Stata will evaluate the above as

 
dlret[1]

the value for the very first observation in the dataset. Similarly month - 1, month -2, etc., for that observation will be observations 0, -1, etc., in the dataset, which is not what you want, I'm sure, and nonsense any way.

You're thinking that Stata will interpret month as a reference to the current month, month - 1 as a reference to the previous month, etc. but that is not how it works.

I guess also that you hope that a reference to

 
ret + dlret[whatever]

means

 
ret[whatever] + dlret[whatever]

but that's not so either. You need the latter form, except that time series operators (see help varlist) are what to use instead.

I will stop there because I don't know the correct formula you want to use, but I know also that tsegen (SSC) will make your life easier too, so search this forum for mentions.

[Jorrit Gosens]

I think what you want is:

bys company year: egen yearlyreturn = total(return)

Not sure why you would want to drop all except for April, but maybe I am misunderstanding what you are trying to do.

Also, for future posts, please use dataex, and code blocks, to post your data. Makes it much easier for people to provide code that will directly work on your end.
See the FAQ nr 12: http://www.statalist.org/forums/help#stata
To install, do:

ssc install dataex

edit:
Or perhaps you meant April should be the first year of the 12 month period, in that case it can be as simple as:

gen yearfiscal = year
replace yearfiscal = year-1 if month<4

followed by some totals as calculated with above code

[Martin Heiroth]

First of all I want to apologize for my misbehavior. Jorrit your Code helped me alot, thank you!

bys company year: egen yearlyreturn = total(return)

I wanted to get the returns from April because i build there the portfolios and need to analyze the five years after portfolio formation. Now i use

gen logret=ln(ret+1)
bys permno year: egen logreturn = total(logret)
gen annualreturn=exp(logreturn)-1

I will go further with the idea in your last edit.

All the best,

Martin

[Julien Maas]

Hey there! I was in the exact same situation, and I used a code similar to the one in Martin's last post. Altough it worked, I still have an issue with missing monthly data. So my question is, how do I adjust for missing data? For example if for a given return year I have 10 months instead of 12, should I give the yearly return computed by the total of monthly log returns a weight of 10/12? Or what would you suggest? Thank you in advance, Kind regards, Julien.

[Julien Maas]

Ps. Sent the same post twice by accident.