How to multiply within observations

[Mia Pham]

Dear all
I'm a new Stata user and I would need your help with the following. My dataset is as follow. I am trying to calculate BH_returns of an ID i at time t as:
BH_returns(i,t)= returns(i,1)* returns(i,2)*...*(returns(i,t)
Can you please suggest a code that can handle that? thank you


ID time returns BH_returns
aa 1 0.1 0.1
aa 2 0.14 0.14*0.1
aa 3 0.15 0.14*0.1*0.15
aa 4 0.02 0.14*0.1*0.15*0.02
bb 1 0.06 0.06
bb 2 0.2 0.06*0.2
bb 3 0.5 0.06*0.2*0.5
bb 4 0.4 0.06*0.2*0.5*0.4

[daniel klein]

Given strictly positive returns, you can use the (running) sum() function as in

bys ID (time) : generate BH_returns = exp(sum(ln(returns)))

Best
Daniel

[Mia Pham]

Thanks Daniel
The table is just an example. In fact I have to deal with both negative and positive returns. Is there any other ways to solve it?

[daniel klein]

You could probably

 bys ID (time) : generate BH_returns = exp(sum(ln(abs(returns))))

then correct the sign somehow.

Best
Daniel

[William Lisowski]

"Returns" suggests something different to me than what you are computing, but taking your statement of the problem as given, I suggest the following.

clear
input str2 ID time returns
aa 1 0.1  
aa 2 0.14 
aa 3 0.15 
aa 4 0.02 
bb 1 0.06 
bb 2 0.2  
bb 3 0.5  
bb 4 0.4  
end
generate BH_returns = returns
bysort ID (time) : replace BH_returns = BH_returns[_n-1]*returns if _n>1
list, sepby(ID)

which yields

     +--------------------------------+
     | ID   time   returns   BH_ret~s |
     |--------------------------------|
  1. | aa      1        .1         .1 |
  2. | aa      2       .14       .014 |
  3. | aa      3       .15      .0021 |
  4. | aa      4       .02    .000042 |
     |--------------------------------|
  5. | bb      1       .06        .06 |
  6. | bb      2        .2       .012 |
  7. | bb      3        .5       .006 |
  8. | bb      4        .4      .0024 |
     +--------------------------------+

[Nick Cox]

William's careful comment can be echoed. Bear in mind that any zeros will necessarily annihilate your products thereafter. Indeed, it is puzzling that the running product of values of either sign is thought to make sense.

[Mia Pham]

Thank you all for your kind help!
Actually the formula of BH_returns is (1+returns1)*(1+returns2)*...
I just want to keep the example simple because the main problem here is to get the multiplication of something from 1 to _n within a group.
Sorry for the confusion.
Regards
Mia