Usage of a Sum Sign equivalent in STATA

[Markus Stein]

Hello all,
I've been sitting here for hours now and I just can't seem to find a solution. Never really worked with STATA and similar statistic tools. I used to program a little bit when I was younger.
My problem is the following:

I have data of daily returns on assets. What I wanna do now is to calculate the exponential weighted average daily return for each time period t with the formula on the paper shown in the picture.
It doesn't look like there is a sum sign equivalent function in STATA (Capital Sigma). I thought about using for loops and incrementing i each time until t-2 but I don't know yet how I'd apply that with STATA syntax. I read the help content of forval, for each etc. but it didn't really help me.

Short description of the picture:
You can see 3 values with variable t and return. When I want to calculate the EWA Daily Return in period t=3 , i starts at 0, and r_t-1-i would be the return value of period 2 (0.003).

In case you're wondering why 60/61 : delta was chosen so the center of the mass is 60.

I'd appreciate it if anyone could point me in the right direction.

Thank you a lot!

Regards,
- Markus Stein

[Scott Merryman]

-sum()- will return the running sum. Perhaps something like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(t return)
1  .021
2  .003
3 -.008
end

 gen ewa = sum((1- 60/61)*((60/61)^(_n - 1) )*return[3-1 - (_n-1)])

[Markus Stein]

Thanks for your reply.
From what I see this is not the same like the formula. I played around with the sum function in STATA and it looks like it just adds up the results from "previous" row. Also the exponent of 60/61 should be dynamic for each row. So if we want to calculate the ewa for row 4, the exponent starts at 0 from there (always starts at zero). For row 5 also from 0 and so on..

Would be cool if anyone could still come up with an idea. But I think I'll prepare my data sheet with Python or any other program language for now. Right now I don't see how I could implement the formula to each return value (I have over 10.000 observations).

Running the code outputs the same value for all observations btw.

[Stephen Jenkins]

Markus: I am confident that Stata solution is readily available; and I think readers -- not necessarily me -- could offer straightforward approaches if you were to give a much clearer exposition of what you're trying to do (give a general formula, not a snapshot) plus (especially) some sample data produced using dataex (if you don't know what that is, please read the Forum FAQ). That 'toy' data set needs to have more than 3 rows of course, so that the calculation window can move (as you wish). Meanwhile, also look at rangestat on SSC. Also read up about indexing of variables in the help and manuals. (Not only are there powerful abilities to refer to leads and lags (i.e. relative to the current obs), but also absolutely: e.g. x[1] is the value of x in row 1 of the data set in memory.

[Richard Williams]

If the right answer hasn't appeared yet, the egen command may do what you want.

Like Stephen says, giving at least a few lines of data, and then saying what the answer should be for those cases, would make this easier. I'll be a little surprised if you can't get Stata to add numbers correctly!

[Markus Stein]

I see. I thought it'd be easier to understand if I took a snapshot with some notes explaining it. But thanks for your tips!

Sample Data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(t daily_return)
  1   0
  2   .019941634
  3   .003576538
  4  -.011166547
  5    .02666987
  6   .007722911
  7    .01532745
  8   .003659652
  9   -.01914312
 10   -.00580855
 11    .01706006
 12   .009420956
 13 -.0043250625
 14  -.006401463
 15  -.006902899
 16  -.015291937
 17   .016470589
 18  -.001388889
 19    .01622624
 20   .012773722
 21 .00022522523
 22   .005629363
 23   .000223914
 24  -.004701142
 25  .0017993703
 26  -.001571621
 27  .0040476727
 end

What I want to do is to create a new variable "EWA_Return" (Exponential Weighted Average Return) and calculate for each time period/row the EWA Return using the formula

where index i starts at 0 and stops at _n - 2

Example: If we wanted to calculate the EWA Return for the 10th row (t = 10), then in the formula t = 10. i equals 0 in the first run, 1 in the 2nd. etc..
So in the first run we multiply (60/61)^0 with the daily_return from last time period, in the 2nd run (60/61)^1 with daily_return looking 2 periods back.
It's kinda hard for me to explain it. I hope you know what I mean. It's basically all in the formula.

Will look into rangestat.


EDIT:

 gen ewa = sum((1- 60/61)*((60/61)^(_n - 1) )*return[3-1 - (_n-1)])

This actually works only for the 3rd observation (t = 3). Other rows unfortunately have the exact same value. If I set change

return[3-1 - (_n-1)])] to [4 -1 - (_n-1)])

the calculation is correct for the 4th observation (but again all other rows have the same value). So I thought the solution could be changing the 4 to _n (as far as I know _n stands for the current row number?) but unfortunately it changes all values to 0.

[Stephen Jenkins]

thanks for the data via dataex. I'm trying to understand better what you want and the relationship between subscripts "i" and "t". Issue 1: your calculation formula has an upper limit for "i" of infinity, but your data example has maximum(t) = 27, so "t-1-i" is negative (undefined?) if i > 26. Your text refers to "i" stopping at "_n-2" (same thing) but inconsistent with the formula.. Issue 2: the lower limit for i in the formula should be zero, I presume, as stated in the text. Issue 3: should the formula refer to (60/61)^i -- which seems to be what your accompanying text cites, but the formula looks like (60)^i / 61.

[Markus Stein]

Hi Stephen,
The upper limit for 'i' of infinity is just a placeholder. When applying the formula to the data we have to adjust the upper limit which is in our case "_n-2".
Example: If we look at the 4th observation (which is the time period t = 4) then the upper limit should be 2 (because 4-2 = 2). In my data set _n is equal to t.

Regarding "stopping at _n-2". As far as I understand it _n is the current row number of the data row where STATA looks at. Example: If STATA creates a new variable and wants to insert calculated data into the 4th observation, _n should be 4. And when applying that to formula, the upper limit of the sum sign would be 4 as well (only for inserting data into the 4th observation into the new variable).

Yes the lower limit for i should start at zero.

Issue 3: should the formula refer to (60/61)^i -- which seems to be what your accompanying text cites, but the formula looks like (60)^i / 61.

It is indeed (60/61)^i

What the formula does is basically calculating the exponential weighted average return for each time period. Returns from time periods that are nearer to the current are weighted heavier than returns from time periods that are more far away.

[Stephen Jenkins]

Try the following code based on a double loop. I'm sure there are other more efficient ways, but this appears to produce the sort of thing you want.

cscript
capture log close
log using dailyreturn.log, replace

/* See  https://www.statalist.org/forums/forum/general-stata-discussion/general/1512324-usage-of-a-sum-sign-equivalent-in-stata
*/ 
input float(t daily_return)
  1   0
  2   .019941634
  3   .003576538
  4  -.011166547
  5    .02666987
  6   .007722911
  7    .01532745
  8   .003659652
  9   -.01914312
 10   -.00580855
 11    .01706006
 12   .009420956
 13 -.0043250625
 14  -.006401463
 15  -.006902899
 16  -.015291937
 17   .016470589
 18  -.001388889
 19    .01622624
 20   .012773722
 21 .00022522523
 22   .005629363
 23   .000223914
 24  -.004701142
 25  .0017993703
 26  -.001571621
 27  .0040476727
 end
 
local a = 60/61
capture drop SUM
ge SUM = 0
su t
local tmax = r(max)
forvalues tval = 1/`tmax' {

    local sum = 0
    forvalues i = 1/`tval' {
    
        local s = `tval' - 1 - `i'  // subscript
        if `s' > 0  local sum = `sum' + (1/61) * ((`a')^`i') * daily_return[`s']

        noisily di " "
        noisily di " t = `tval', i = `i', sum = `sum' "
    }

    replace SUM = `sum' in `tval'
    
}

list , noobs
log close

The listing produces the following

. list , noobs

  +---------------------------+
  |  t   daily_r~n        SUM |
  |---------------------------|
  |  1           0          0 |
  |  2    .0199416          0 |
  |  3    .0035765          0 |
  |  4   -.0111665   .0003216 |
  |  5    .0266699    .000374 |
  |---------------------------|
  |  6    .0077229   .0001878 |
  |  7    .0153274   .0006147 |
  |  8    .0036597   .0007292 |
  |  9   -.0191431   .0009644 |
  | 10   -.0058085   .0010076 |
  |---------------------------|
  | 11    .0170601   .0006824 |
  | 12     .009421   .0005775 |
  | 13   -.0043251   .0008432 |
  | 14   -.0064015   .0009812 |
  | 15   -.0069029   .0008954 |
  |---------------------------|
  | 16   -.0152919   .0007775 |
  | 17    .0164706   .0006535 |
  | 18   -.0013889   .0003962 |
  | 19    .0162262   .0006553 |
  | 20    .0127737   .0006221 |
  |---------------------------|
  | 21    .0002252   .0008736 |
  | 22    .0056294   .0010652 |
  | 23    .0002239   .0010514 |
  | 24   -.0047011   .0011249 |
  | 25    .0017994   .0011101 |
  |---------------------------|
  | 26   -.0015716   .0010161 |
  | 27    .0040477   .0010285 |
  +---------------------------+

I've exhausted my inspiration (and available time) now ...

[Stephen Jenkins]

Correction: I note you want index i to start at zero, not one. So, if I change one line in my code as follows:

forvalues i = 1/`tval' {

to

forvalues i = 0/`tval' {

and then re-run, I get the following listing at the end:

  +---------------------------+
  |  t   daily_r~n        SUM |
  |---------------------------|
  |  1           0          0 |
  |  2    .0199416          0 |
  |  3    .0035765   .0003269 |
  |  4   -.0111665   .0003802 |
  |  5    .0266699   .0001909 |
  |---------------------------|
  |  6    .0077229    .000625 |
  |  7    .0153274   .0007413 |
  |  8    .0036597   .0009805 |
  |  9   -.0191431   .0010244 |
  | 10   -.0058085   .0006938 |
  |---------------------------|
  | 11    .0170601   .0005872 |
  | 12     .009421   .0008572 |
  | 13   -.0043251   .0009976 |
  | 14   -.0064015   .0009103 |
  | 15   -.0069029   .0007905 |
  |---------------------------|
  | 16   -.0152919   .0006644 |
  | 17    .0164706   .0004028 |
  | 18   -.0013889   .0006662 |
  | 19    .0162262   .0006325 |
  | 20    .0127737   .0008881 |
  |---------------------------|
  | 21    .0002252    .001083 |
  | 22    .0056294   .0010689 |
  | 23    .0002239   .0011437 |
  | 24   -.0047011   .0011286 |
  | 25    .0017994    .001033 |
  |---------------------------|
  | 26   -.0015716   .0010456 |
  | 27    .0040477   .0010027 |
  +---------------------------+

If this is not exactly what you want (or there are further errors in my code!), I hope that the approach employed is something that you can adapt to your case as you wish.

[Markus Stein]

Hi Stephen Jenkins,
Thank you! Even though your code works like I wanted, I will probably still do it on my own in C++ or Python. The thing is that this "value transformation" is a part of my Bachelor thesis. I'm replicating a paper and the formula I provided was a small part of the work of it. Since I need to include the code and document my procedures I'm not sure whether can use that code.

I was hoping that I could come on the solution on my own with some tips but looking at your code I would have never come up with that.
Mainly because I'm not familiar with all the syntax and possible loops in STATA yet.

I will study the code though to better understand how STATA works. Definitely helpful. Eventually I can then solve other problems in STATA for my bachelor thesis.

[Dimple khattar khattar]

Can someone help me with the code for this formula,
For the first formula, I have the code: (let me know if this is not correct)

gen techsum=0
forvalues i = 1/31 {
replace techsum= techsum + tech_`i'
}

bysort year: gen num= _N
bysort year: gen avalue= 1-(techsum/num)

[Clyde Schechter]

No, the code you show cannot be correct. You are trying to calculate a_k,t which is indexed by technology and time. But your code only indices over technology and takes no account of time at all, nor does it distinguish hospitals. Moreover, although the text you quoted says N_t is the total number of hospitals in the United States, the fact that it carries a subscript t suggests that they really mean that it is the total number of hospitals in the United States in year t. (And, realistically, the number does change from year to year.)

As you don't show example data, I have made a toy data set that may resemble what you have and illustrate an approach to your problem here:

//    CREATE DEMONSTRATION DATA SET
clear*
set obs 30
gen byte hosp_num = _n    //    INDEX i IN FORMULA
expand 5
by hosp_num, sort: gen tech_num = _n    // INDEX k IN FORMULA
expand 10
by hosp_num tech_num, sort: gen year = 1999 + _n    // INDEX t IN FORMULA

set seed 1234
gen tau = runiformint(0, 1)    // RANDOM VALUES OF tau

//    CALCULATE THE FORMULA
by year (hosp_num), sort: gen N_t = sum(hosp_num != hosp_num[_n-1])
by year (hosp_num): replace N_t = N_t[_N]   // SAME FOR EACH HOSPITAL IN THIS DEMO, BUT NOT IN REAL DATA

by tech_num year (hosp_num), sort: egen a_kt = total(tau)
replace a_kt = 1 - a_kt/N_t

[Dimple khattar khattar]

Can you please revise the code for this dataset?
prov_is id the hospital id
tech- are technologies present or absent in the hospital

I also need help in calculating Si,t (saidindex)
TIA

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str7 prov_id byte(tech_6 tech_7 tech_8 tech_9 tech_12 tech_11 tech_14 teach nonprof) int beds byte govt
2007 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2010 "11Z111"  1 0 0 1 1 0 0 0 0  35 1
2001 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2005 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2002 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2004 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2006 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2003 "11Z111 " 1 0 0 0 0 0 0 0 0  35 1
2009 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2008 "11Z111"  1 0 0 0 0 0 0 0 0  35 1
2005 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2007 "11Z113 " 1 0 0 0 0 0 0 0 0  40 1
2009 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2010 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2002 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2003 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2001 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2006 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2004 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2008 "11Z113"  1 0 0 0 0 0 0 0 0  40 1
2007 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2003 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2002 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2001 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2009 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2006 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2005 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2004 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2010 "11Z132 " 1 1 0 0 0 0 0 0 0 211 1
2008 "11Z132"  1 1 0 0 0 0 0 0 0 211 1
2007 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2010 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2001 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2002 "11Z135 " 1 1 0 0 0 0 0 0 1  55 0
2004 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2008 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2006 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2009 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2005 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2003 "11Z135"  1 1 0 0 0 0 0 0 1  55 0
2010 "11Z13Z"  1 0 0 1 1 0 0 0 0  34 1
2004 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2002 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2008 "11Z13Z"  1 0 0 0 1 0 0 0 0  34 1
2007 "11Z13Z"  1 0 0 0 1 0 0 0 0  34 1
2009 "11Z13Z"  1 0 0 1 1 0 0 0 0  34 1
2001 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2006 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2003 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2005 "11Z13Z"  1 0 0 0 0 0 0 0 0  34 1
2009 "11Z142 " 1 1 0 0 0 0 0 0 1 195 0
2007 "11Z142"  1 1 0 0 0 0 0 0 1 195 0
2008 "11Z142"  1 1 0 0 0 0 0 0 1 195 0
2010 "11Z142"  1 1 0 0 0 0 0 0 1 195 0
2005 "11Z142"  1 1 0 0 0 0 0 0 1 195 0
2003 "11Z142"  1 0 0 0 0 0 0 0 1 195 0
2004 "11Z142 " 1 1 0 0 0 0 0 0 1 195 0
2001 "11Z142"  1 0 0 0 0 0 0 0 1 195 0
2006 "11Z142"  1 1 0 0 1 0 0 0 1 195 0
2002 "11Z142"  1 0 0 0 0 0 0 0 1 195 0
2008 "11Z161"  1 1 0 0 1 0 0 0 1 487 0
2003 "11Z161"  1 1 0 0 1 0 1 0 1 487 0
2004 "11Z161"  1 1 0 0 1 0 1 0 1 487 0
2006 "11Z161"  1 1 0 0 1 0 0 0 1 487 0
2007 "11Z161"  1 1 0 0 1 0 0 0 1 487 0
2002 "11Z161"  1 1 0 0 0 0 1 0 1 487 0
2001 "11Z161"  1 1 0 0 0 0 1 0 1 487 0
2009 "11Z161"  1 1 0 0 1 0 0 0 1 487 0
2005 "11Z161"  1 1 0 0 1 0 0 0 1 487 0
2010 "11Z161"  1 1 0 1 1 0 0 0 1 487 0
2007 "11Z163 " 1 0 0 0 1 0 0 0 0 174 0
2008 "11Z163"  1 0 0 0 1 0 0 0 0 174 0
2009 "11Z163 " 1 0 0 0 1 0 0 0 0 174 0
2004 "11Z163"  1 0 0 0 0 0 0 0 0 174 0
2002 "11Z163"  1 1 0 0 0 0 0 0 0 174 0
2006 "11Z163"  1 0 0 0 0 0 0 0 0 174 0
2003 "11Z163"  1 1 0 0 0 0 0 0 0 174 0
2001 "11Z163"  1 0 0 0 0 0 0 0 0 174 0
2005 "11Z163"  1 0 0 0 0 0 0 0 0 174 0
2010 "11Z163"  1 0 0 0 1 0 0 0 0 174 0
2001 "11Z165"  1 1 0 0 0 0 0 0 1 336 0
2010 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2002 "11Z165"  1 1 0 0 0 0 0 0 1 336 0
2006 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2009 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2005 "11Z165 " 1 1 0 0 1 0 0 0 1 336 0
2003 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2007 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2008 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2004 "11Z165"  1 1 0 0 1 0 0 0 1 336 0
2007 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2010 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2006 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2001 "11Z177"  1 0 0 0 0 0 0 0 0 255 0
2003 "11Z177"  1 1 0 0 0 0 0 0 0 255 0
2008 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2009 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2004 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
2002 "11Z177"  1 1 0 0 0 0 0 0 0 255 0
2005 "11Z177"  1 1 0 0 1 0 0 0 0 255 0
end

[Clyde Schechter]

You have the tech_* indicators in wide layout. The code requires -reshape-ing to wide first. After that, it's the same code with a change of some variable names. I've added the calculation of s_i,t in the final line of code.

//    GO TO LONG LAYOUT
reshape long tech_, i(prov_id year) j(tech_num)
rename tech_ tau

//    CALCULATE THE FORMULA
by year (prov_id), sort: gen N_t = sum(prov_id != prov_id[_n-1])
by year (prov_id): replace N_t = N_t[_N]   // SAME FOR EACH HOSPITAL IN THIS DEMO, BUT NOT IN REAL DATA

by tech_num year (prov_id), sort: egen a_kt = total(tau)
replace a_kt = 1 - a_kt/N_t

by prov_id year, sort: egen s_it = total(a_kt * tau)

Because you did not show example data originally, we both wasted some time on the first code--me, writing it, and you trying to figure it out or use it when your data weren't appropriate for it. So moral of the story: always show example data when asking for help with code. It is a rare coding problem that can be solved without knowing the details of data organization and metadata.