st: Calculating year end annual returns based on monthly mid-year observations

["Peter Miller" <peter_miller@gmx.ch>]

Dear Stata Community,

I’ve a problem with calculating annual returns based on monthly data and I was wondering if anyone has experienced the same issue.

Here is my setting: I’ve monthly data for a sample of 5000 firms. For each firm I’ve monthly returns and I would like to calculate year end annual returns, that is returns until December for all following years. However, my problem is that for some firms monthly returns are available say from July onwards.

I would then need a command that calculates returns based on the first month (here July) until December of the ith year. That command needs to flexible enough so I can run it for all my firms.

Here is how it should look like at the end.

ID 	Date		Ret 		1st year end return	2nd year end return
1	July 00      	0.05		0.1016			0.22748
1 	Aug 00		0.04
1 	Sept 00 	0.07
1 	Oct 00	       -0.08
1 	Nov 00	       -0.02
1 	Dec 00		0.05
1	Jan 01		0.03
1	Feb 01		0.02
1	March 01	0.01
1	April 01       -0.04
1 	May 01	       -0.03
1	June 01	       -0.02
1	Juli 01	       -0.01
1	Aug 01		0.05
1	Sept 01 	0.06
1 	Oct 01		0.07
1	Nov 01	        0.08
1	Dec 01	       -0.10

Any help would be much appreciated, as I stuck on this problem for quite some while!
Thank you

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/