Referring to previous observation that differs from current observation of same variable

[Eugene Hart]

Hello,

I am very new to Stata, so please excuse my inexperience.

I am using a data set comprised by the variables future, enddate, date, price, tk, S1_1, S1_1_TK, SKX.
"Future" refers to the settlement period of the future, enddate specifies the date of settlement, date is connected to price and specifies the date in which the price can be observed, tk is a constant 100bp for transaction costs, S1_1 refers to the spread in effect at the specified date between the future and the previous future, S1_1_TK is the spread minus the transaction costs at the time of opening the position and SKX refers to the settlement price of the future.

Now I would like to generate a new variable "S1_1_TK2". S1_1_TK2 should be comprised of S1_1_TK and the transaction costs at the time of closing the position. Thus I need to refer to the settlement price of the prior period future.

In this case (and as you can see in the screenshot attached) I want to refer to SKX=6,98 when calculating the value of S1_1_TK2 for the Dec-12 future.

I tried:

gen S1_1_TK2 = S1_1_TK - SKX[_n-1] * tk

Obviously this does not work expect for the first Dec-12 future. For all the following Dec-12 future this command refers to the closing price of the Dec-12 future aswell.

So I would need to find an command which refers to the first observation of the variable SKX which differs from the current observation.


Thanks!

[David Benson]

Hi Eugene,

Unfortunately, I can't read the screenshot (the FAQ asks you not to post screenshots anyway ). What you are asking for is pretty easy--just please share a sample of your data using Stata's dataex command; sharing data that way makes it *much* easier for someone to help you.

There is a Youtube tutorial on using dataex here (I would watch it at double speed; you also probably only need to watch the first 6-7 minutes ).

dataex future enddate date price tk S1_1 S1_1_TK SKX

Hope that helps!
--David

[Eugene Hart]

Hi David,

thank you for making me aware. Is there a way to remove the screenshot from the post?

this is the data that I use:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(future enddate date) double price float(tk S1_1 S1_1_TK SKX)
18962 18980 18512 16.04 .01   .     . 6.98
18962 18980 18660 14.96 .01   .     . 6.98
18962 18980 18857 12.35 .01   .     . 6.98
18962 18980 18886 12.17 .01   .     . 6.98
18962 18980 18226 13.48 .01   .     . 6.98
18962 18980 18834 12.88 .01   .     . 6.98
19328 19344 19269  7.87 .01   .     . 6.46
19328 19344 18883 12.53 .01  .5 .2544 6.46
19328 19344 18569 15.52 .01 .51 .2047 6.46
19328 19344 18947 10.31 .01 .44 .2382 6.46
19328 19344 18318 14.02 .01 .59 .3155 6.46
19328 19344 18862 13.51 .01 .58 .3156 6.46
19328 19344 18591 15.85 .01 .48 .1678 6.46
19328 19344 18911 11.18 .01 .52 .3016 6.46
end
format %tdMon-YY future
format %tdnn/dd/CCYY enddate
format %tdnn/dd/CCYY date

[David Benson]

So, there are probably several ways you could do this. I would try this:

sort future enddate date
egen future_id = group(future)  // creating an id for each different future_date
gen change = 1 if future!=future[_n-1]  // this might be redundant once create the n variable is created
bysort future_id ( enddate date): gen n=_n

* Where it actually grabs the prior_SKX
gen prior_SKX = SKX[_n-1] if change==1
bysort future_id: replace prior_SKX = prior_SKX[1] if prior_SKX==.

. list, noobs sepby(future) abbrev(12)

  +-------------------------------------------------------------------------------------------------------------+
  | future      enddate         date   price    tk   S1_1   S1_1_TK    SKX   future_id   change   n   prior_SKX |
  |-------------------------------------------------------------------------------------------------------------|
  | Dec-11   12/19/2011   11/25/2009   13.48   .01      .         .   6.98           1        1   1           . |
  | Dec-11   12/19/2011     9/7/2010   16.04   .01      .         .   6.98           1        .   2           . |
  | Dec-11   12/19/2011     2/2/2011   14.96   .01      .         .   6.98           1        .   3           . |
  | Dec-11   12/19/2011    7/26/2011   12.88   .01      .         .   6.98           1        .   4           . |
  | Dec-11   12/19/2011    8/18/2011   12.35   .01      .         .   6.98           1        .   5           . |
  | Dec-11   12/19/2011    9/16/2011   12.17   .01      .         .   6.98           1        .   6           . |
  |-------------------------------------------------------------------------------------------------------------|
  | Dec-12   12/17/2012    2/25/2010   14.02   .01    .59     .3155   6.46           2        1   1        6.98 |
  | Dec-12   12/17/2012    11/3/2010   15.52   .01    .51     .2047   6.46           2        .   2        6.98 |
  | Dec-12   12/17/2012   11/25/2010   15.85   .01    .48     .1678   6.46           2        .   3        6.98 |
  | Dec-12   12/17/2012    8/23/2011   13.51   .01    .58     .3156   6.46           2        .   4        6.98 |
  | Dec-12   12/17/2012    9/13/2011   12.53   .01     .5     .2544   6.46           2        .   5        6.98 |
  | Dec-12   12/17/2012   10/11/2011   11.18   .01    .52     .3016   6.46           2        .   6        6.98 |
  | Dec-12   12/17/2012   11/16/2011   10.31   .01    .44     .2382   6.46           2        .   7        6.98 |
  | Dec-12   12/17/2012    10/3/2012    7.87   .01      .         .   6.46           2        .   8        6.98 |
  +-------------------------------------------------------------------------------------------------------------+

If you've got different stocks in this data, you will need to add the stock_id when creating the prior_SKX above

bysort stock_id future_id ( enddate date): gen n=_n
gen prior_SKX = SKX[_n-1] if n==1