Max Min values using rangestat

[Sakvarelidze Elene]

Dear Statalists,

I would like to calculate the for each day maximum Price based on the previous 3 month data. I am using the command rangestat (max) max_price = prc, interval(date -90 0) .

I am facing two issues:
1.not always there are 90 days during the month, can be more can be less. I thought, extracting months from date variable and changing the code like this: rangestat (max) max_price = prc, interval(date -2 0) would help but it did not.
2. Stata is giving the max values even when there is not sufficient past data.

This is how my data looks like:
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date str8 cusip double prc
10001 15006 "36720410"               9.875
10001 15007 "36720410"                 9.5
10001 15008 "36720410"             9.96875
10001 15011 "36720410"             9.96875
10001 15012 "36720410"           -9.734375
10001 15013 "36720410"            -9.71875
10001 15014 "36720410"            -9.71875
10001 15015 "36720410"                 9.5
10001 15018 "36720410"               9.875
10001 15019 "36720410"                 9.5
10001 15020 "36720410"                 9.5
10001 15021 "36720410"               9.375
10001 15022 "36720410"               9.375
10001 15026 "36720410"               9.375
10001 15027 "36720410"                 9.5
10001 15028 "36720410"               9.375
10001 15029 "36720410"               9.625
10001 15032 "36720410"              9.8125
10001 15033 "36720410"              9.8125
10001 15034 "36720410"                9.75
10001 15035 "36720410"                9.75
10001 15036 "36720410"              -9.625
10001 15039 "36720410"                 9.5
10001 15040 "36720410"                9.75
10001 15041 "36720410"                 9.5
10001 15042 "36720410"                 9.5
10001 15043 "36720410"                9.75
10001 15046 "36720410"                 9.5
10001 15047 "36720410"              9.8125
10001 15048 "36720410"                9.75
10001 15049 "36720410"               9.625
10001 15050 "36720410"              -9.625
10001 15053 "36720410"              9.8125
10001 15054 "36720410"              9.8125
10001 15055 "36720410"              9.8125
10001 15056 "36720410"               9.875
10001 15057 "36720410"                  10
10001 15060 "36720410"                  10
10001 15061 "36720410"             10.3125
10001 15062 "36720410"             10.1875
10001 15063 "36720410"               10.25
10001 15064 "36720410"                  10
10001 15067 "36720410"                  10
10001 15068 "36720410"               10.25
10001 15069 "36720410"                  10
10001 15070 "36720410"             10.3125
10001 15071 "36720410"             10.1875
10001 15074 "36720410"  10.149999618530273
10001 15075 "36720410"   9.899999618530273
10001 15076 "36720410"   9.899999618530273
10001 15077 "36720410"   9.899999618530273
10001 15081 "36720410"   9.899999618530273
10001 15082 "36720410"  -9.824999809265137
10001 15083 "36720410"  -9.800000190734863
10001 15084 "36720410"  10.199999809265137
10001 15085 "36720410"                9.75
10001 15088 "36720410"  10.100000381469727
10001 15089 "36720410"  10.100000381469727
10001 15090 "36720410"   9.899999618530273
10001 15091 "36720410"  10.100000381469727
10001 15092 "36720410"   9.699999809265137
10001 15095 "36720410"                9.75
10001 15096 "36720410"   9.850000381469727
10001 15097 "36720410"   9.850000381469727
10001 15098 "36720410"                  10
10001 15099 "36720410"   9.720000267028809
10001 15102 "36720410"   9.800000190734863
10001 15103 "36720410"   9.699999809265137
10001 15104 "36720410"   9.949999809265137
10001 15105 "36720410"                9.75
10001 15106 "36720410"   9.949999809265137
10001 15109 "36720410"   9.949999809265137
10001 15110 "36720410"   9.979999542236328
10001 15111 "36720410"   9.989999771118164
10001 15112 "36720410"                10.5
10001 15113 "36720410"  10.489999771118164
10001 15116 "36720410"  10.489999771118164
10001 15117 "36720410"  10.420000076293945
10001 15118 "36720410"                10.5
10001 15119 "36720410"               11.25
10001 15120 "36720410"                  11
10001 15124 "36720410"                10.5
10001 15125 "36720410"               10.75
10001 15126 "36720410"  10.699999809265137
10001 15127 "36720410"               10.75
10001 15130 "36720410"  10.649999618530273
10001 15131 "36720410"                11.5
10001 15132 "36720410" -10.795000076293945
10001 15133 "36720410"                10.5
10001 15134 "36720410"               11.25
10001 15137 "36720410"                  11
10001 15138 "36720410"               11.25
10001 15139 "36720410"                11.5
10001 15140 "36720410"                  11
10001 15141 "36720410"                  11
10001 15144 "36720410"               11.25
10001 15145 "36720410"                11.5
10001 15146 "36720410"                11.5
10001 15147 "36720410"                  13
10001 15148 "36720410"                12.5
end
format %d date

[Nick Cox]

Thanks for the data example.

rangestat is from SSC, as you are asked to explain (FAQ Advice #12).

I think you are misunderstanding what rangestat does -- and even the implications of, your own data. It seems that you have prices for business days only, excluding weekends and presumably public holidays too.

The negative prices here strike me as being nonsensical, unless you have a good story. Setting that aside

rangestat (max) max_price = prc, interval(date -90 0) .

says: look in a window of length 91 (not 90) which is -90 -89 ... -2 -1 (the previous 90 days) PLUS 0 (the present day) and report the maximum price in that interval.

Nothing is implied about how many values that might mean. There might be 91 if you were using a business calendar, but you aren't. There will be a problem with the beginning of series (no values earlier) and there will be a problem in your case with non-business days.

Things should be clearer with this -- and incidentally doing this separately for each stock, or whatever it is, has to be more sensible --

gen fixed = abs(prc) 
rangestat (count) fixed (max) max_price = fixed, interval(date -90 0) by(permno)


     +-----------------------------------------------------------------------------+
     | permno        date      cusip          prc      fixed   fixed_~t   max_pr~e |
     |-----------------------------------------------------------------------------|
  1. |  10001   31jan2001   36720410        9.875      9.875          1      9.875 |
  2. |  10001   01feb2001   36720410          9.5        9.5          2      9.875 |
  3. |  10001   02feb2001   36720410      9.96875    9.96875          3    9.96875 |
  4. |  10001   05feb2001   36720410      9.96875    9.96875          4    9.96875 |
  5. |  10001   06feb2001   36720410    -9.734375   9.734375          5    9.96875 |
     |-----------------------------------------------------------------------------|
  6. |  10001   07feb2001   36720410     -9.71875    9.71875          6    9.96875 |
  7. |  10001   08feb2001   36720410     -9.71875    9.71875          7    9.96875 |
  8. |  10001   09feb2001   36720410          9.5        9.5          8    9.96875 |
  9. |  10001   12feb2001   36720410        9.875      9.875          9    9.96875 |
 10. |  10001   13feb2001   36720410          9.5        9.5         10    9.96875 |
     |-----------------------------------------------------------------------------|
 11. |  10001   14feb2001   36720410          9.5        9.5         11    9.96875 |
 12. |  10001   15feb2001   36720410        9.375      9.375         12    9.96875 |
 13. |  10001   16feb2001   36720410        9.375      9.375         13    9.96875 |
 14. |  10001   20feb2001   36720410        9.375      9.375         14    9.96875 |
 15. |  10001   21feb2001   36720410          9.5        9.5         15    9.96875 |
     |-----------------------------------------------------------------------------|
 16. |  10001   22feb2001   36720410        9.375      9.375         16    9.96875 |
 17. |  10001   23feb2001   36720410        9.625      9.625         17    9.96875 |
 18. |  10001   26feb2001   36720410       9.8125     9.8125         18    9.96875 |
 19. |  10001   27feb2001   36720410       9.8125     9.8125         19    9.96875 |
 20. |  10001   28feb2001   36720410         9.75       9.75         20    9.96875 |
     |-----------------------------------------------------------------------------|
 21. |  10001   01mar2001   36720410         9.75       9.75         21    9.96875 |
 22. |  10001   02mar2001   36720410       -9.625      9.625         22    9.96875 |
 23. |  10001   05mar2001   36720410          9.5        9.5         23    9.96875 |
 24. |  10001   06mar2001   36720410         9.75       9.75         24    9.96875 |
 25. |  10001   07mar2001   36720410          9.5        9.5         25    9.96875 |
     |-----------------------------------------------------------------------------|
 26. |  10001   08mar2001   36720410          9.5        9.5         26    9.96875 |
 27. |  10001   09mar2001   36720410         9.75       9.75         27    9.96875 |
 28. |  10001   12mar2001   36720410          9.5        9.5         28    9.96875 |
 29. |  10001   13mar2001   36720410       9.8125     9.8125         29    9.96875 |
 30. |  10001   14mar2001   36720410         9.75       9.75         30    9.96875 |
     |-----------------------------------------------------------------------------|
 31. |  10001   15mar2001   36720410        9.625      9.625         31    9.96875 |
 32. |  10001   16mar2001   36720410       -9.625      9.625         32    9.96875 |
 33. |  10001   19mar2001   36720410       9.8125     9.8125         33    9.96875 |
 34. |  10001   20mar2001   36720410       9.8125     9.8125         34    9.96875 |
 35. |  10001   21mar2001   36720410       9.8125     9.8125         35    9.96875 |
     |-----------------------------------------------------------------------------|
 36. |  10001   22mar2001   36720410        9.875      9.875         36    9.96875 |
 37. |  10001   23mar2001   36720410           10         10         37         10 |
 38. |  10001   26mar2001   36720410           10         10         38         10 |
 39. |  10001   27mar2001   36720410      10.3125    10.3125         39    10.3125 |
 40. |  10001   28mar2001   36720410      10.1875    10.1875         40    10.3125 |
     |-----------------------------------------------------------------------------|
 41. |  10001   29mar2001   36720410        10.25      10.25         41    10.3125 |
 42. |  10001   30mar2001   36720410           10         10         42    10.3125 |
 43. |  10001   02apr2001   36720410           10         10         43    10.3125 |
 44. |  10001   03apr2001   36720410        10.25      10.25         44    10.3125 |
 45. |  10001   04apr2001   36720410           10         10         45    10.3125 |
     |-----------------------------------------------------------------------------|
 46. |  10001   05apr2001   36720410      10.3125    10.3125         46    10.3125 |
 47. |  10001   06apr2001   36720410      10.1875    10.1875         47    10.3125 |
 48. |  10001   09apr2001   36720410        10.15      10.15         48    10.3125 |
 49. |  10001   10apr2001   36720410    9.8999996        9.9         49    10.3125 |
 50. |  10001   11apr2001   36720410    9.8999996        9.9         50    10.3125 |
     |-----------------------------------------------------------------------------|
 51. |  10001   12apr2001   36720410    9.8999996        9.9         51    10.3125 |
 52. |  10001   16apr2001   36720410    9.8999996        9.9         52    10.3125 |
 53. |  10001   17apr2001   36720410   -9.8249998      9.825         53    10.3125 |
 54. |  10001   18apr2001   36720410   -9.8000002        9.8         54    10.3125 |
 55. |  10001   19apr2001   36720410         10.2       10.2         55    10.3125 |
     |-----------------------------------------------------------------------------|
 56. |  10001   20apr2001   36720410         9.75       9.75         56    10.3125 |
 57. |  10001   23apr2001   36720410         10.1       10.1         57    10.3125 |
 58. |  10001   24apr2001   36720410         10.1       10.1         58    10.3125 |
 59. |  10001   25apr2001   36720410    9.8999996        9.9         59    10.3125 |
 60. |  10001   26apr2001   36720410         10.1       10.1         60    10.3125 |
     |-----------------------------------------------------------------------------|
 61. |  10001   27apr2001   36720410    9.6999998        9.7         61    10.3125 |
 62. |  10001   30apr2001   36720410         9.75       9.75         62    10.3125 |
 63. |  10001   01may2001   36720410    9.8500004       9.85         63    10.3125 |
 64. |  10001   02may2001   36720410    9.8500004       9.85         63    10.3125 |
 65. |  10001   03may2001   36720410           10         10         63    10.3125 |
     |-----------------------------------------------------------------------------|
 66. |  10001   04may2001   36720410    9.7200003       9.72         63    10.3125 |
 67. |  10001   07may2001   36720410    9.8000002        9.8         63    10.3125 |
 68. |  10001   08may2001   36720410    9.6999998        9.7         63    10.3125 |
 69. |  10001   09may2001   36720410    9.9499998       9.95         63    10.3125 |
 70. |  10001   10may2001   36720410         9.75       9.75         63    10.3125 |
     |-----------------------------------------------------------------------------|
 71. |  10001   11may2001   36720410    9.9499998       9.95         63    10.3125 |
 72. |  10001   14may2001   36720410    9.9499998       9.95         63    10.3125 |
 73. |  10001   15may2001   36720410    9.9799995       9.98         63    10.3125 |
 74. |  10001   16may2001   36720410    9.9899998       9.99         63    10.3125 |
 75. |  10001   17may2001   36720410         10.5       10.5         63       10.5 |
     |-----------------------------------------------------------------------------|
 76. |  10001   18may2001   36720410        10.49      10.49         63       10.5 |
 77. |  10001   21may2001   36720410        10.49      10.49         64       10.5 |
 78. |  10001   22may2001   36720410        10.42      10.42         64       10.5 |
 79. |  10001   23may2001   36720410         10.5       10.5         64       10.5 |
 80. |  10001   24may2001   36720410        11.25      11.25         64      11.25 |
     |-----------------------------------------------------------------------------|
 81. |  10001   25may2001   36720410           11         11         64      11.25 |
 82. |  10001   29may2001   36720410         10.5       10.5         63      11.25 |
 83. |  10001   30may2001   36720410        10.75      10.75         63      11.25 |
 84. |  10001   31may2001   36720410         10.7       10.7         63      11.25 |
 85. |  10001   01jun2001   36720410        10.75      10.75         63      11.25 |
     |-----------------------------------------------------------------------------|
 86. |  10001   04jun2001   36720410        10.65      10.65         63      11.25 |
 87. |  10001   05jun2001   36720410         11.5       11.5         63       11.5 |
 88. |  10001   06jun2001   36720410      -10.795     10.795         63       11.5 |
 89. |  10001   07jun2001   36720410         10.5       10.5         63       11.5 |
 90. |  10001   08jun2001   36720410        11.25      11.25         63       11.5 |
     |-----------------------------------------------------------------------------|
 91. |  10001   11jun2001   36720410           11         11         63       11.5 |
 92. |  10001   12jun2001   36720410        11.25      11.25         63       11.5 |
 93. |  10001   13jun2001   36720410         11.5       11.5         63       11.5 |
 94. |  10001   14jun2001   36720410           11         11         63       11.5 |
 95. |  10001   15jun2001   36720410           11         11         63       11.5 |
     |-----------------------------------------------------------------------------|
 96. |  10001   18jun2001   36720410        11.25      11.25         63       11.5 |
 97. |  10001   19jun2001   36720410         11.5       11.5         63       11.5 |
 98. |  10001   20jun2001   36720410         11.5       11.5         63       11.5 |
 99. |  10001   21jun2001   36720410           13         13         63         13 |
100. |  10001   22jun2001   36720410         12.5       12.5         63         13 |
     +-----------------------------------------------------------------------------+

.

It seems that you are usually going to get 63 or 64 days at most with data.


In short, rangestat uses what it can find given your instructions.

I can't see why you think that date(-2 0) would select 3 months.

[Sakvarelidze Elene]

Nick thanks a lot for a such thorough answer!

But I dont get now what fixed t stands for... Sorry for that but can you please briefly elaborate on that?

And also is there any other way around this? I am trying by this to do technical analysis on stocks so basically have to find maximum price daily using let's say 90 day previous price information. Could you maybe advise another command? Because going stock by stock its impossible since i am taking whole US stock exchange...

BR,

Elene

[Nick Cox]

Sorry, but which part of

 gen fixed = abs(prc)

is unclear? The function abs() throws away the minus signs. See also

 help abs()

Look in your data at e.g.

  
10001 15008 "36720410"             9.96875
10001 15011 "36720410"             9.96875
10001 15012 "36720410"           -9.734375
10001 15013 "36720410"            -9.71875
10001 15014 "36720410"            -9.71875
10001 15015 "36720410"                 9.5

and explain the minus signs otherwise. In general: I am not an economist. I do not work on this kind of data.

Many people do this kind of calculation for many different stocks. What is axiomatic is that if you just use the interval() option you are mixing all the separate stocks together which even this non-economist would argue is nonsensical.

What do think is wrong with your results here? You don't have non-business days in the data. Nor does anybody else. It's not a problem. It's not your fault.

I think you need to speak to your advisor/supervisor/mentor if you are unclear about your goals.

[Sakvarelidze Elene]

Taking the absolute value of a price is absolutely understandable what is not --> fixed_~t which can only get max 64...

to separate the stocks i found the way by modifying the code: rangestat (max) max_price = prc, interval(date -89 0) by(cusip)

[Nick Cox]

Using by() was already explained in #2.

That is fixed_count abbreviated. As I've explained, in a 91 day interval you will have no data for non-business days. So, at most

. di 91 * 5/7
65

65 days can be expected without Saturdays or Sundays; the other gaps I presume are public holidays. Do read the help for rangestat if you want more information on what (count) does, but it is just the count of non-missing values.

[Sakvarelidze Elene]

Thank you again!

[Xiaoke Ye]

Using by() was already explained in #2.

That is fixed_count abbreviated. As I've explained, in a 91 day interval you will have no data for non-business days. So, at most

. di 91 * 5/7
65

65 days can be expected without Saturdays or Sundays; the other gaps I presume are public holidays. Do read the help for rangestat if you want more information on what (count) does, but it is just the count of non-missing values.

Hi Nick
May I just ask a simple question?
When calculate max by using rangestat, the "dot" is ignored by rangestat, is this correct? I am aware "dot" in stata is treated as infinitely large

[Nick Cox]

Missing values will be ignored in the calculation of a maximum, unless naturally all values are missing.