Measure variation in variable values based on a date variable

[Francesco Tucci]

Hi all,
I am trying to compute the quarterly variation in a list of stock prices ( variables "prccd" for the price and "cusip" for the specific stock ) using the information contained in the "date" variable, which is just a list of dates. Hence, the panel looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 cusip float(date prccd)
"000360206" 16072  18.93
"000360206" 16075  19.02
"000360206" 16076  19.29
"000360206" 16077  19.19
"000360206" 16078  19.21
"000360206" 16079  19.63
"000360206" 16082   19.5
"000360206" 16083   19.5
"000360206" 16084  20.41
"000360206" 16085  21.55
"000360206" 16086  22.03
"000360206" 16090   22.4
"000360206" 16091     22
"000360206" 16092  20.92
"000360206" 16093  21.37
"000360206" 16096  21.14
"000360206" 16097  21.28
"000360206" 16098  20.92
"000360206" 16099  19.04
"000360206" 16100  19.04
"000360206" 16103   19.3
"000360206" 16104  18.92
"000360206" 16105   18.7
"000360206" 16106   18.7
"000360206" 16107  19.12
"000360206" 16110  19.44
"000360206" 16111  19.45
"000360206" 16112     18
"000360206" 16113  19.62
"000360206" 16114  19.48
"000360206" 16118  18.92
"000360206" 16119  19.28
"000360206" 16120 19.801
"000360206" 16121  19.77
"000360206" 16124  19.83
"000360206" 16125  19.83
"000360206" 16126  19.78
"000360206" 16127     20
"000360206" 16128  20.02
"000360206" 16131  20.12
"000360206" 16132  20.42
"000360206" 16133 20.061
"000360206" 16134  20.18
"000360206" 16135  20.12
"000360206" 16138  20.07
"000360206" 16139  20.26
"000360206" 16140  20.14
"000360206" 16141  20.16
"000360206" 16142  20.43
"000360206" 16145  20.14
end
format %td date

I am trying to obtain the result using the following code:

by cusip: gen trimester_return_daily = (prccd[_n+120]-prccd[_n])/prccd[_n]

but was wondering if there is an easier way through making use of the information contained in the "date" variable.
Many thanks in advance for your help and support!

[Nick Cox]

You're counting over intervals of 120 observations which don't include weekends (obviously) and holidays (possibly)

Whether using a date variable is better depends on your exact definition of a quarter, but either way 120 days, or 120 observations, don't seem to qualify.

[Francesco Tucci]

Hi Nick, thanks for the reply!
Yes, that is actually the issue, because 120 observations/lines never correspond to 3 actual months later on, considering the occurencies that you mentioned (weekends, holidays etc.). So the definition of a quarter would be the specific date +3 months.
Do you have any suggestions on how to make use of the date variable to account for this?

[Nick Cox]

3 months ago could be a weekend, etc. I don't think we're further forward without a definition.

[William Lisowski]

Let me add that "the specific date +3 months" is not well defined - what is January 31 plus 3 months? What is November 29 plus three months in non-leap-years?

Let me also add that "quarterly" most often is used to refer to "calendar quarters" - January 1 through March 31, etc. But it seems perhaps you want a "120 day rolling window" or something similar. In that case, do you mean "the day and the preceding 119 days" or "the day and the following 119 days" or perhaps "the day and the 60 days before and the 60 days after"?

[Francesco Tucci]

Thanks both! I clearly see your point and need to think more thoroughly into that, but leaving for the moment the quarter point aside, let's imagine that I want to compute the annual return for that specific date on a year-to-year basis (e.g. 10/02/2004-10/02/2005). Let's also theoretically leave aside the point that the 10/02/2005 might not be in the data because it's during the weekend. Do you think there is a way to use the information in the "date" variable to do that, rather than writing something like (what I have done so far):

by cusip: gen trimester_return_daily = (prccd[_n+365]-prccd[_n])/prccd[_n]

Thanks again!

[William Lisowski]

Start by familiarizing yourself with Stata's notation for time series variables and factor variables in chapter 11 of the Stata User's Guide PDF included in your Stata installation and accessible from Stata's Help menu.

Having read that, the following example answers the question posed in post #6, but for demonstration purposes using your example data reduces the period from a year to 7 days.

tsset date
generate ret_7day = (F7.prccd - prccd) / prccd
format %9.3f ret_7day
format %9.2f prccd
list, clean

. list, clean

           cusip        date   prccd   ret_7day  
  1.   000360206   02jan2004   18.93      0.037  
  2.   000360206   05jan2004   19.02      0.025  
  3.   000360206   06jan2004   19.29      0.011  
  4.   000360206   07jan2004   19.19      0.064  
  5.   000360206   08jan2004   19.21      0.122  
  6.   000360206   09jan2004   19.63      0.122  
  7.   000360206   12jan2004   19.50          .  
  8.   000360206   13jan2004   19.50      0.149  
  9.   000360206   14jan2004   20.41      0.078  
 10.   000360206   15jan2004   21.55     -0.029  
 11.   000360206   16jan2004   22.03     -0.030  
 12.   000360206   20jan2004   22.40     -0.050  
 13.   000360206   21jan2004   22.00     -0.049  
 14.   000360206   22jan2004   20.92     -0.090  
 15.   000360206   23jan2004   21.37     -0.109  
 16.   000360206   26jan2004   21.14     -0.087  
 17.   000360206   27jan2004   21.28     -0.111  
 18.   000360206   28jan2004   20.92     -0.106  
 19.   000360206   29jan2004   19.04     -0.018  
 20.   000360206   30jan2004   19.04      0.004  
 21.   000360206   02feb2004   19.30      0.007  
 22.   000360206   03feb2004   18.92      0.028  
 23.   000360206   04feb2004   18.70     -0.037  
 24.   000360206   05feb2004   18.70      0.049  
 25.   000360206   06feb2004   19.12      0.019  
 26.   000360206   09feb2004   19.44          .  
 27.   000360206   10feb2004   19.45     -0.027  
 28.   000360206   11feb2004   18.00      0.071  
 29.   000360206   12feb2004   19.62      0.009  
 30.   000360206   13feb2004   19.48      0.015  
 31.   000360206   17feb2004   18.92      0.048  
 32.   000360206   18feb2004   19.28      0.026  
 33.   000360206   19feb2004   19.80      0.010  
 34.   000360206   20feb2004   19.77      0.013  
 35.   000360206   23feb2004   19.83      0.015  
 36.   000360206   24feb2004   19.83      0.030  
 37.   000360206   25feb2004   19.78      0.014  
 38.   000360206   26feb2004   20.00      0.009  
 39.   000360206   27feb2004   20.02      0.005  
 40.   000360206   01mar2004   20.12     -0.002  
 41.   000360206   02mar2004   20.42     -0.008  
 42.   000360206   03mar2004   20.06      0.004  
 43.   000360206   04mar2004   20.18     -0.001  
 44.   000360206   05mar2004   20.12      0.015  
 45.   000360206   08mar2004   20.07      0.003  
 46.   000360206   09mar2004   20.26          .  
 47.   000360206   10mar2004   20.14          .  
 48.   000360206   11mar2004   20.16          .  
 49.   000360206   12mar2004   20.43          .  
 50.   000360206   15mar2004   20.14          .

[Francesco Tucci]

Thanks William Lisowski and sorry for the late reply, I solved the issue resorting to your suggested approach (i.e. time series)! Thanks again!