Hello everybody; I want to calculate a division which the nominator and denominator aren't in same rows. Please consider the table below:   table 1: name price date Rank DP_nu DP_nu_sum DP_de_sum RESULT A 1 4/2/2008 1         A 2 4/2/2008 1 1       A 2 4/7/2008 1 0       A 2 4/17/2008 1 0       A 3 4/2/2008 1 1       A 3 4/7/2008 1 0 2   0.4 A 4 4/2/2008 2         A 4 4/2/2008 2 0       A 4 4/7/2008 2 0       A 5 4/2/2008 2 1       A 6 4/2/2008 2 1 2   0.4 A 7 4/2/2008 3         A 7 4/7/2008 3 0       A 8 4/7/2008 3 1       A 8 4/17/2008 3 0 1 5 0.2 B 1 7/22/2008 1         B 1 10/14/2008 1 0       B 2 7/22/2008 1 1       B 2 10/14/2008 1 0       B 3 10/14/2008 1 1 2   0.4 B 4 7/22/2008 2         B 4 7/22/2008 2 0       B 5 7/22/2008 2 1       B 5 10/14/2008 2 0       B 6 10/14/2008 2 1 2   0.4 B 7 7/22/2008 3         B 7 10/14/2008 3 0       B 7 10/14/2008 3 0       B 7 10/14/2008 3 0       B 8 7/22/2008 3 1       B 8 10/14/2008 3 0 1 5 0.2     I want to create the RESULT variable which is shown in the table 1 as the last column. RESULT variable is the (DP_nu_sum / DP_den_sum) for each name.   How can I do that?   Thanks in advance. ... View more

Hello everybody, I want to calculate the return or the growth of the price variable. Please consider the following table:   table A name price date time RETURN A 3 2-Apr-08 9:44:38   A 5 2-Apr-08 9:53:02   A 7 2-Apr-08 9:55:23   A 4 2-Apr-08 9:58:31 0.333333 A 6 2-Apr-08 11:17:33   A 2 2-Apr-08 11:18:33   A 1 2-Apr-08 11:19:33 -0.83333 A 4 2-Apr-08 11:57:33   A 8 7-Apr-08 9:08:39   A 4 7-Apr-08 9:10:22   A 2 7-Apr-08 9:13:41 -0.75 A 3 7-Apr-08 9:32:14   A 7 7-Apr-08 9:41:04 1.333333 A 8 17-Apr-08 9:15:51   A 2 17-Apr-08 10:24:15   B 4 22-Jul-08 10:16:19   B 7 22-Jul-08 10:20:51 0.75 B 8 22-Jul-08 11:12:19   B 4 22-Jul-08 11:15:27   B 2 22-Jul-08 11:17:30   B 5 22-Jul-08 11:18:36 -0.375 B 1 22-Jul-08 11:31:50   B 3 14-Oct-08 9:05:07   B 1 14-Oct-08 9:37:11   B 6 14-Oct-08 9:46:16   B 7 14-Oct-08 9:50:56   B 7 14-Oct-08 9:52:52   B 8 14-Oct-08 9:59:23 7 B 5 14-Oct-08 10:05:13   B 7 14-Oct-08 11:19:44   B 2 14-Oct-08 11:20:15 -0.71429   Now, I want to calculate the growth of price in half an hour for each name and date values. So, the RETURN variable is:   RETURNS variable = (Last price value in half an hour for each name and date variables) - (First price value in half an hour for each name and date variables) / (First price value in half an hour for each name and date variables)   Results have been shown in the last column of table A.   How can I calculate RETURNS variable in table A?   Thanks in advance. ... View more

Hello everybody; I want to calculate returns of equities for specific name and date variables. Here is a sample of my data:     data have; infile datalines expandtabs truncover ; input name $ date :mmddyy10. time : time10. Intraday; format date mmddyy10. time time10.; datalines; A 12/1/2013 9:30:00 1 A 12/1/2013 10:00:00 2 A 12/2/2013 10:30:00 3 A 12/2/2013 11:00:00 2 A 12/3/2013 10:00:00 2 A 12/3/2013 10:30:00 1 A 12/3/2013 11:00:00 1 A 12/4/2013 11:30:00 1 A 12/4/2013 12:00:00 3 A 12/4/2013 12:30:00 3 A 12/7/2013 9:30:00 1 A 12/7/2013 10:00:00 1 A 12/8/2013 10:30:00 2 A 12/8/2013 11:00:00 3 A 12/17/2013 11:30:00 1 A 12/17/2013 12:00:00 1 A 12/18/2013 12:30:00 2 A 12/28/2013 9:30:00 1 A 12/28/2013 10:00:00 1 B 12/8/2013 10:30:00 2 B 12/8/2013 11:30:00 2 B 12/8/2013 12:30:00 1 B 12/14/2013 9:30:00 1 B 12/14/2013 10:30:00 1 B 12/15/2013 12:00:00 2 B 12/15/2013 12:30:00 1 B 12/24/2013 9:30:00 1 B 12/25/2013 10:00:00 2 B 12/25/2013 10:30:00 2 B 12/26/2013 12:00:00 2 B 12/30/2013 9:30:00 3 B 12/30/2013 10:00:00 2 B 12/30/2013 10:30:00 1 B 1/3/2014 11:00:00 1 B 1/3/2014 11:30:00 1 B 1/13/2014 12:00:00 1 ;     There are two major numbers needed to calculate the rate of return: Current value: the current value of the intraday variable. Original value: the price at which you purchased the item (The intraday value of previous row). Then, by applying these values to the rate of return formula: ((Current value of an intraday - original value of an intraday) / original value of an intraday) = rate of return   So the results is: name date time Intraday return A 12/1/2013 9:30:00 1   A 12/1/2013 10:00:00 2 1 A 12/2/2013 10:30:00 3   A 12/2/2013 11:00:00 2 -0.33333 A 12/3/2013 10:00:00 2   A 12/3/2013 10:30:00 1 -0.5 A 12/3/2013 11:00:00 1 0 A 12/4/2013 11:30:00 1   A 12/4/2013 12:00:00 3 2 A 12/4/2013 12:30:00 3 0 A 12/7/2013 9:30:00 1   A 12/7/2013 10:00:00 1 0 A 12/8/2013 10:30:00 2   A 12/8/2013 11:00:00 3 0.5 A 12/17/2013 11:30:00 1   A 12/17/2013 12:00:00 1 0 A 12/18/2013 12:30:00 2   A 12/28/2013 9:30:00 1   A 12/28/2013 10:00:00 1 0 B 12/8/2013 10:30:00 2   B 12/8/2013 11:30:00 2 0 B 12/8/2013 12:30:00 1 -0.5 B 12/14/2013 9:30:00 1   B 12/14/2013 10:30:00 1 0 B 12/15/2013 12:00:00 2   B 12/15/2013 12:30:00 1 -0.5 B 12/24/2013 9:30:00 1   B 12/25/2013 10:00:00 2   B 12/25/2013 10:30:00 2 0 B 12/26/2013 12:00:00 2   B 12/30/2013 9:30:00 3   B 12/30/2013 10:00:00 2 -0.33333 B 12/30/2013 10:30:00 1 -0.5 B 1/3/2014 11:00:00 1   B 1/3/2014 11:30:00 1 0 B 1/13/2014 12:00:00 1     How can I do that? Thanks in advance. ... View more

Hello everybody; I want to calculate a division which the nominator is intraday variable and the denominator is the aggregate of daily values of two days before and after that specific date and name. This new variable is called adjusted as shown in table below;   table1: name date time Intraday daily adjusted A 12/1/2013 9:30:00 1     A 12/1/2013 10:00:00 2 3   A 12/2/2013 10:30:00 3     A 12/2/2013 11:00:00 2 5   A 12/3/2013 10:00:00 2   0.0952381 A 12/3/2013 10:30:00 1   0.04761905 A 12/3/2013 11:00:00 1 4 0.04761905 A 12/4/2013 11:30:00 1   0.04347826 A 12/4/2013 12:00:00 3   0.13043478 A 12/4/2013 12:30:00 3 7 0.13043478 A 12/7/2013 9:30:00 1   0.05 A 12/7/2013 10:00:00 1 2 0.05 A 12/8/2013 10:30:00 2   0.11111111 A 12/8/2013 11:00:00 3 5 0.16666667 A 12/17/2013 11:30:00 1   0.07692308 A 12/17/2013 12:00:00 1 2 0.07692308 A 12/18/2013 12:30:00 2 2   A 12/28/2013 9:30:00 1     A 12/28/2013 10:00:00 1 2   B 12/8/2013 10:30:00 2     B 12/8/2013 11:30:00 2     B 12/8/2013 12:30:00 1 5   B 12/14/2013 9:30:00 1     B 12/14/2013 10:30:00 1 2   B 12/15/2013 12:00:00 2   0.13333333 B 12/15/2013 12:30:00 1 3 0.06666667 B 12/24/2013 9:30:00 1 1 0.08333333 B 12/25/2013 10:00:00 2   0.125 B 12/25/2013 10:30:00 2 4 0.125 B 12/26/2013 12:00:00 2 2 0.125 B 12/30/2013 9:30:00 3     B 12/30/2013 10:00:00 2     B 12/30/2013 10:30:00 1 6   B 1/3/2014 11:00:00 1     B 1/3/2014 11:30:00 1     B 1/13/2014 12:00:00 1 3     Now, let me give you an example. the value '0.0952381' at date '12/3/2013' in adjusted column is (2/(3+5+4+7+2)). the value '2' in nominator is intraday value at '12/3/2013' and the denominator is the sum of daily values for two days before and after plus daily volume of that specific day. For another instance, the adjusted value '0.04347826' at date '12/4/2013' is (1/(3+5+4+7+2)).   How can I create the variable adjusted in table 1? Thanks in advance. ... View more

Hello everybody; I want to calculate a division which the nominator and denominator are in different rows. Please consider the table below: table 1: name date time Intraday daily sum adjusted A 12/1/2013 9:30:00 1       A 12/1/2013 10:00:00 2 3     A 12/2/2013 10:30:00 3       A 12/2/2013 11:00:00 2 5     A 12/3/2013 10:00:00 2       A 12/3/2013 10:30:00 1       A 12/3/2013 11:00:00 1 4     A 12/4/2013 11:30:00 1       A 12/4/2013 12:00:00 3       A 12/4/2013 12:30:00 3 7     A 12/7/2013 9:30:00 1       A 12/7/2013 10:00:00 1 2 21   A 12/8/2013 10:30:00 2     0.095238 A 12/8/2013 11:00:00 3 5 23 0.142857 A 12/17/2013 11:30:00 1     0.043478 A 12/17/2013 12:00:00 1 2 23 0.043478 A 12/18/2013 12:30:00 2 2 20 0.086957 A 12/28/2013 9:30:00 1     0.05 A 12/28/2013 10:00:00 1 2 18 0.05 B 12/8/2013 10:30:00 2       B 12/8/2013 11:30:00 2       B 12/8/2013 12:30:00 1 5     B 12/14/2013 9:30:00 1       B 12/14/2013 10:30:00 1 2     B 12/15/2013 12:00:00 2       B 12/15/2013 12:30:00 1 3     B 12/24/2013 9:30:00 1 1     B 12/25/2013 10:00:00 2       B 12/25/2013 10:30:00 2 4 15   B 12/26/2013 12:00:00 2 2 12 0.133333 B 12/30/2013 9:30:00 3     0.25 B 12/30/2013 10:00:00 2     0.166667 B 12/30/2013 10:30:00 1 6 16 0.083333 B 1/3/2014 11:00:00 1     0.0625 B 1/3/2014 11:30:00 1     0.0625 B 1/13/2014 12:00:00 1 3 16 0.0625   I want to create the adjusted variable which is shown in the table 1. This variable is calculated using the formula which is described below: The intraday variable is divided by the sum variable of previous date. For instance, values '0.095238' and '0.142857' at '12/8/2013' are 2/21 and 3/21 respectively.   How can I do that? Thanks in advance. ... View more

Hello everybody; I want to sum obs at specific day and four days before. For instance, consider the following table:   table 1: name date time Intraday daily sum A 12/1/2013 9:30:00 1     A 12/1/2013 10:00:00 2 3   A 12/2/2013 10:30:00 3     A 12/2/2013 11:00:00 2 5   A 12/3/2013 10:00:00 2     A 12/3/2013 10:30:00 1     A 12/3/2013 11:00:00 1 4   A 12/4/2013 11:30:00 1     A 12/4/2013 12:00:00 3     A 12/4/2013 12:30:00 3 7   A 12/7/2013 9:30:00 1     A 12/7/2013 10:00:00 1 2 21 A 12/8/2013 10:30:00 2     A 12/8/2013 11:00:00 3 5 23 A 12/17/2013 11:30:00 1     A 12/17/2013 12:00:00 1 2 23 A 12/18/2013 12:30:00 2 2 20 A 12/28/2013 9:30:00 1     A 12/28/2013 10:00:00 1 2 18 B 12/8/2013 10:30:00 2     B 12/8/2013 11:30:00 2     B 12/8/2013 12:30:00 1 5   B 12/14/2013 9:30:00 1     B 12/14/2013 10:30:00 1 2   B 12/15/2013 12:00:00 2     B 12/15/2013 12:30:00 1 3   B 12/24/2013 9:30:00 1 1   B 12/25/2013 10:00:00 2     B 12/25/2013 10:30:00 2 4 15 B 12/26/2013 12:00:00 2 2 12 B 12/30/2013 9:30:00 3     B 12/30/2013 10:00:00 2     B 12/30/2013 10:30:00 1 6 16 B 1/3/2014 11:00:00 1     B 1/3/2014 11:30:00 1     B 1/13/2014 12:00:00 1 3 16    Please consider the sum variable which is the aggregate of daily variable for that day and four days before. For example the value 21 at 12/7/2013 is 3+5+4+7+2 values of daily column. In other words, I want to generate variable sum in table 1.   How can I do that? Thanks in advance. ... View more