Loop and cumulative returns

[Francois Durant]

Hi,

I have the following dataset that will be extended to ret_F4, ...... , ret_F`n':

permno    date    ret_F1    ret_F2    ret_F3
10000    1986m7    -.9555115    -.0588405    -.2776318
10000    1986m8    -.0588405    -.2776318    .0582689
10000    1986m9    -.2776318    .0582689    -.4737844
10000    1986m10    .0582689    -.4737844    
10000    1986m11    -.4737844        
10000    1986m12            
10001    1986m1    .0202027    .0248877    .0098523
10001    1986m2    .0248877    .0098523    -.0098523
10001    1986m3    .0098523    -.0098523    -.0131555
10001    1986m4    -.0098523    -.0131555    -.0102565
10001    1986m5    -.0131555    -.0102565    .0696799
10001    1986m6    -.0102565    .0696799    -.0030817
10001    1986m7    .0696799    -.0030817    .0384663
10001    1986m8    -.0030817    .0384663    .0550598

I would like to calculate in one loop the "n" cumulative returns starting from ret_F`n' and at each iteration remove the last period: (1/K); (1/K-1) ... (1/1). Here is the code I have so far, that need to be fixed:

local K 3

tsegen cum_ret03 = rowtotal(ret_F(1/`K'), `K')
gen cum_ret3 = exp(cum_ret03)-1

tsegen cum_ret02 = rowtotal(ret_F(1/`K'-1), `K')
gen cum_ret2 = exp(cum_ret02)-1

tsegen cum_ret01 = rowtotal(ret_F(1/`K'-2), `K')
gen cum_ret1 = exp(cum_ret01)-1

Thanks in advance!

[Francois Durant]

Did anyone see what was wrong? Thanks!

[William Lisowski]

You don't tell us what Stata responds with when you run the code you present. And for the reference of other readers, I found that tsegen is a contributed package from SSC.

With that said, it appears to me that you are attempting to run the following command, once 3 has been substituted for the macro K.

tsegen cum_ret03 = rowtotal(ret_F(1/3), 3)

You appear to be trying to abbreviate the variable list ret_F1 ret_F2 ret_F3 as ret_F(1/3). That is not correct. The (1/3) notation is limited to the time series lag and forward operators L and F, for example L(1/3).ret_F1 would give the values for ret_F1 from the previous three time periods.

[Robert Picard]

Florent, this seems to be a direct continuation of yesterday's thread Sum row variables with Macro. I can't understand what you are trying to do different here.

You would increase your chances of getting a solution if you provided a data example that does not require extra work. This is easy to do with dataex (from SSC).

My best reading of what you are trying to do is to create a cumulative return variable measured over a range of periods. You also appear to want to include the current period. The following example shows how to do this for one to six periods.

clear
input int(permno date) float ln_ret
10000 312 . 
10000 313 -.29725161 
10000 314 .31143609 
10000 315 -.1037968 
10000 316 -.2518726 
10000 317 -.0050378 
10000 318 -.0842603 
10000 319 -.95551151 
10000 320 -.0588405 
10000 321 -.27763179 
10000 322 .0582689 
10000 323 -.47378439 
10001 312 . 
10001 313 .0202027 
10001 314 .0248877 
10001 315 .0098523 
10001 316 -.0098523 
10001 317 -.0131555 
10001 318 -.0102565 
10001 319 .0696799 
10001 320 -.0030817 
10001 321 .0384663 
10001 322 .0550598 
10001 323 .0148886 
end
format %tm date
tsset permno date 

local maxperiods 6

forvalues i = 1/`maxperiods' {
    tsegen double ln_ret_p`i' = rowtotal(L(0/`i').ln_ret, `=`i'+1')
    gen double cum_ret_p`i' = exp(ln_ret_p`i') - 1
}

* spot check using 3 periods back
gen double cum_ret_test = exp(ln_ret + L1.ln_ret + L2.ln_ret + L3.ln_ret)-1
list permno date cum_ret_p3 cum_ret_test, sepby(permno) noobs

assert cum_ret_p3 == cum_ret_test

If this is not what you want, please provide a clear example of what you want to calculate, including some example of what the results should look like.

[Francois Durant]

Thanks Robert, you post helps a lot. I'm actually seeking to calculate the cumulative returns in subsequent periods (F.). Unlike, my other post, I do not want to include the current period. I have slighlty modified your code but I'm doing something wrong since Stata output missing values. Could you help me with my code?

local K 6

forvalues i = 1/`K' {
    tsegen double ln_ret_F`i' = rowtotal(F(1/`i').ln_ret, `=`i'+1')
    gen double cum_ret_F`i' = exp(ln_ret_F`i') - 1
}

Stata output:

ln_ret_F1    cum_ret_F1    ln_ret_F2    cum_ret_F2    ln_ret_F3    cum_ret_F3    ln_ret_F4    cum_ret_F4    ln_ret_F5    cum_ret_F5    ln_ret_F6    cum_ret_F6
                                            
                                            
                                            
                                            
                                            
                                            
                                            
                                            
                                            
                                            

[Francois Durant]

Got it. Here is how I want it to be:

local K 6

forvalues i = 1/`K' {
    tsegen double ln_ret_F`i' = rowtotal(F(1/`i').ln_ret, `i'), missing
    gen double cum_ret_F`i' = exp(ln_ret_F`i') - 1
}

Could you explain the syntaxt `=`i'+1' though? Thanks!

[Robert Picard]

Glad to hear that this is working for you. The `=`i'+1' was my somewhat lazy way to tune the example to work when you include the current observation. If i==5, the time-series operated variable L(0/`i').ln_ret will be expanded to 6 temporary variables by tsegen. The following would have been clearer

forvalues i = 1/`maxperiods' {
    local n = `i' + 1
    tsegen double ln_ret_p`i' = rowtotal(L(0/`i').ln_ret, `n')
    gen double cum_ret_p`i' = exp(ln_ret_p`i') - 1
}

From the "Macro expansion operators and function" section of the [P] macro manual entry:

`=exp' provides inline access to Stata’s expression evaluator. The Stata expression exp is evaluated
and the result substituted. For example,
. local alpha = 0.05
. regress mpg weight, level(`=100*(1-`alpha')')

[Francois Durant]

Many thanks Robert, you explanation really help!