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

[Roberto Ferrer <refp16@gmail.com>]

Not exactly as you want it but you could create a "year" variable and
use -bysort-.

An example assuming "year-end returns" are computed as the sum of
monthly returns:

*------------------- begin code ---------------------------

clear all
set more off

*-------------------- example data ----------

input ///
ID     str12  Date            Ret
1       "July00"         0.05
1       "Aug00"         0.04
1       "Sept00"         0.07
1       "Oct00"        -0.08
1       "Nov00"         -0.02
1       "Dec00"          0.05
1       "Jan01"          0.03
1       "Feb01"          0.02
1       "March01"        0.01
1       "April01"       -0.04
1       "May01"         -0.03
1       "June01"        -0.02
1       "July01"        -0.01
1       "Aug01"          0.05
1       "Sept01"         0.06
1       "Oct01"          0.07
1       "Nov01"          0.08
1       "Dec01"        -0.10
end

gen dt = date(Date, "M20Y")
format %td dt

gen yr = year(dt)
drop dt

list, separator(0)

*---------------------- compute returns --------

// one way
bysort ID yr: egen yearendret = total(Ret)

// another way
collapse (sum) yearendret=Ret, by(ID yr)

*-----------------end code -----------------------------

See -help date-, -help year-, -help by-, -help collapse-

On Fri, Feb 7, 2014 at 8:30 AM, Peter Miller <peter_miller@gmx.ch> wrote:
> 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/

*
*   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/