Calculate daily stock return relative to peak in the last 6 month period

[Justin Niakamal]

I have daily stock market data and would like to calculate the return for each observation relative to the peak in the last 6 months. I've tried fishing around for a solution, but I am stuck trying to code this in.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date float value
"2009-02-02" 825.44
"2009-02-03" 838.51
"2009-02-04" 832.23
"2009-02-05" 845.85
"2009-02-06"  868.6
"2009-02-09" 869.89
"2009-02-10" 827.16
"2009-02-11" 833.74
"2009-02-12" 835.19
"2009-02-13" 826.84
"2009-02-16"      .
"2009-02-17" 789.17
"2009-02-18" 788.42
"2009-02-19" 778.94
"2009-02-20" 770.05
"2009-02-23" 743.33
"2009-02-24" 773.14
"2009-02-25"  764.9
"2009-02-26" 752.83
"2009-02-27" 735.09
"2009-03-02" 700.82
"2009-03-03" 696.33
"2009-03-04" 712.87
"2009-03-05" 682.55
"2009-03-06" 683.38
"2009-03-09" 676.53
"2009-03-10"  719.6
"2009-03-11" 721.36
"2009-03-12" 750.74
"2009-03-13" 756.55
"2009-03-16" 753.89
"2009-03-17" 778.12
"2009-03-18" 794.35
"2009-03-19" 784.04
"2009-03-20" 768.54
"2009-03-23" 822.92
"2009-03-24" 806.12
"2009-03-25" 813.88
"2009-03-26" 832.86
"2009-03-27" 815.94
"2009-03-30" 787.53
"2009-03-31" 797.87
"2009-04-01" 811.08
"2009-04-02" 834.38
"2009-04-03"  842.5
"2009-04-06" 835.48
"2009-04-07" 815.55
"2009-04-08" 825.16
"2009-04-09" 856.56
"2009-04-10"      .
"2009-04-13" 858.73
"2009-04-14"  841.5
"2009-04-15" 852.06
"2009-04-16"  865.3
"2009-04-17"  869.6
"2009-04-20" 832.39
"2009-04-21" 850.08
"2009-04-22" 843.55
"2009-04-23" 851.92
"2009-04-24" 866.23
"2009-04-27" 857.51
"2009-04-28" 855.16
"2009-04-29" 873.64
"2009-04-30" 872.81
"2009-05-01" 877.52
"2009-05-04" 907.24
"2009-05-05"  903.8
"2009-05-06" 919.53
"2009-05-07" 907.39
"2009-05-08" 929.23
"2009-05-11" 909.24
"2009-05-12" 908.35
"2009-05-13" 883.92
"2009-05-14" 893.07
"2009-05-15" 882.88
"2009-05-18" 909.71
"2009-05-19" 908.13
"2009-05-20" 903.47
"2009-05-21" 888.33
"2009-05-22"    887
"2009-05-25"      .
"2009-05-26" 910.33
"2009-05-27" 893.06
"2009-05-28" 906.83
"2009-05-29" 919.14
"2009-06-01" 942.87
"2009-06-02" 944.74
"2009-06-03" 931.76
"2009-06-04" 942.46
"2009-06-05" 940.09
"2009-06-08" 939.14
"2009-06-09" 942.43
"2009-06-10" 939.15
"2009-06-11" 944.89
"2009-06-12" 946.21
"2009-06-15" 923.72
"2009-06-16" 911.97
"2009-06-17" 910.71
"2009-06-18" 918.37
"2009-06-19" 921.23
end

[Clyde Schechter]

So, first you need to convert your date variable to a numeric Stata internal format date variable.

Then you need to find the peak value in the preceding 6 months for each observation. In the code below, I use 183 days to represent 6 months, and I include all observations from the 183 days before the current date through the current date. If you want to exclude the current date, change 0 to -1 in the -interval()- option Similarly, if you want to use some other convention for the number of days in 6 months, change the 183 value accordingly.

Finally, I leave it to you to calculate the return you are looking for from that. It seems an odd point to base the return on, as the return will necessarily be negative (or 0) as the peak value in the past 6 months has to be at least as large as the current value.

gen date_sif = daily(date, "YMD")
assert missing(date) == missing(date_sif)
format date_sif %td

rangestat (max) value, interval(date_sif -183 0)

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

[Justin Niakamal]

That does the trick, thank you, Clyde! I agree the base is a bit odd – the intent is to look at major market sell-offs, which here are defined as a double-digit decline in stock prices (relative to the peak value in the last 6 months).