Generating return series for timeseries data

[laura wolff]

Hello,

I'm using timeseries data and want to generate a return series. The return is supposed to be calculated on the data point that is at least 3 seconds away from this datapoint. You'll see this in the data example below - so for observation 9, the return is calculated relative to the 4th observation, while the 10th return is calculated as 10th price/9th price-1, since these are the first dates, that are at least 3 seconds apart. I've tried

rolling

, but since there are gaps in my timeseries data, the result is incorrect. I've also thought of using

cond()

, but my dataset is too large to set up multiple if conditions.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(price return date)
  .4563140351911763                   0 1676192401234
 .12123021072046514                   0 1676192401456
.015799518412771385                   0 1676192401789
  .5531145142756151                   0 1676192401998
 .36208761774438625                   0 1676192402234
  .4010092064132522                   0 1676192402456
  .5990130135963275                   0 1676192402789
.010658344783998186                   0 1676192402998
 .48161974610468405  -.1292585284343224 1676192405000
 .06049858759734017  -.8743851594818326 1676192410000
  .8339152409690928  12.784044786621696 1676192460000
   .783335628399825  11.947998614669551 1676192461000
  .5320383300504032 -.32080412180761453 1676192465000
end
format %tcCCYY-NN-DD_HH:MM:SS.sss date

Any pointers towards other commands/workarounds would be appreciated!

Thank you in advance

[Clyde Schechter]

So the main problem here is to find the last price that is at least 3 seconds prior to the current price for each observation. That is exactly what Robert Picard, Roberto Ferrer & Nick Cox's new command -rangestat- does. You can get it with -ssc install rangestat-. The trick then is to specify the interval observation correctly. The unit in %tc variables is milliseconds, so 3 seconds is 3000. Then there is the fact that we have to, potentially, go back to negative infinity. There is no convenient way to represent that. But within the data set, the furthest back one would need to go is the difference between the first and last times (when the data are sorted). So this should do it:

sort date
local maxdiff = date[_N] - date[1]

rangestat (last) price, interval(date, -`maxdiff', -3000) describe
gen double my_return = (price-price_last)/price_last
list, noobs clean

It works for your example, in any case.

[laura wolff]

Thank you so much for your help, Clyde! This worked perfectly.