Egen command; minimum value over last 5 observations

[Sophie Wesselink]

Hi!

I would like to create a new variable where each value is the minimum value of the previous 5 values. The code below shows part of my data (5 minute close stock prices). How can this be created using egen (or gen) and min?

CLOSE Price
4377.84
4377.59
4340.01
4345.31
4354.90
4340.00
4340.00
4340.00
4345.95
4337.00
4320.07
4344.95
4320.03

Thank you!

[Clyde Schechter]

No, you can't do this with -egen-, at least not in any reasonable way. Here are two ways you can do it:

* Example generated by -dataex-. For more info, type help dataex
clear
input float close_price
4377.84
4377.59
4340.01
4345.31
 4354.9
   4340
   4340
   4340
4345.95
   4337
4320.07
4344.95
4320.03
end


gen wanted1 = min(close_price[_n-1], close_price[_n-2], close_price[_n-3], ///
    close_price[_n-4], close_price[_n-5])
    
//  OR SOMEWHAT MORE ELEGANTLY:
gen obs_no = _n
rangestat (min) wanted2 = close_price, interval(obs_no -5 -1)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Sophie Wesselink]

No, you can't do this with -egen-, at least not in any reasonable way. Here are two ways you can do it:

* Example generated by -dataex-. For more info, type help dataex
clear
input float close_price
4377.84
4377.59
4340.01
4345.31
4354.9
4340
4340
4340
4345.95
4337
4320.07
4344.95
4320.03
end


gen wanted1 = min(close_price[_n-1], close_price[_n-2], close_price[_n-3], ///
close_price[_n-4], close_price[_n-5])

// OR SOMEWHAT MORE ELEGANTLY:
gen obs_no = _n
rangestat (min) wanted2 = close_price, interval(obs_no -5 -1)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Thank you for replying; it worked, thank you! Much appreciated! Earlier I've posted another question, but no response (yet). Do you know how I can code the following; I have obtained buy signals from stock data close prices for every 5 minutes. If a buy signal is triggered, I want to retrieve a sell signal 4 periods later and ignore the signals in between (if at t=4 the signal is "BUY" it needs to be "SELL"). If after the sell signal directly the buy signal still holds, there should be a buy signal again and after 4 periods a sell signal etc. If directly after the buy signal there is (e.g. for a few periods) no buy signal I would want the next buy signal and then 4 periods after the sell signal etc. I have tried using the generate/replace commands, but that sadly doesn't work all the time (still some errors). Is there another way to do this? In addition, if I would want the buy signal to hold for 3 periods before the buy signal is triggered, how does that change the code?

* Example generated by -dataex-. To install: ssc install dataex
clear
input double CLOSE str3 buy_signal
4346.03 ""   
4344.01 ""   
   4360 "BUY"
4377.84 "BUY"
4377.59 "BUY"
4340.01 ""   
4345.31 "BUY"
 4354.9 "BUY"
   4340 ""   
   4340 ""   
   4340 ""   
4345.95 "BUY"
   4337 ""   
4320.07 ""   
4344.95 "BUY"
4320.03 ""   
4320.01 ""   
   4320 ""   
4318.51 ""   
   4325 "BUY"
end

Below shows an example how I suppose it would look like with the correct signals (after "//")

* Example generated by -dataex-. To install: ssc install dataex
clear
input double CLOSE str3 buy_signal
4346.03 ""   
4344.01 ""   
   4360 "BUY"      // "BUY"
4377.84 "BUY"
4377.59 "BUY"
4340.01 ""   
4345.31 "BUY"     // "SELL"
 4354.9 "BUY"     // "BUY"
   4340 ""   
   4340 ""   
   4340 ""   
4345.95 "BUY"    // "SELL"
   4337 ""   
4320.07 ""   
4344.95 "BUY"    // "BUY"
4320.03 ""   
4320.01 ""   
   4320 ""   
4318.51 ""        // "SELL"
   4325 "BUY"     // "BUY"
end

Thank you in advance!!