Number of Days since 52 Weeks High Price

[Steven Sanders]

Hello everyone,

I have daily unbalanced panel data with ID = permno and Time = date.
The variable prc is the price of a stock and the variable maxprc_52 is the highest price in the last 52 weeks. I calculated the highest price via "rangestat" command.
The other variables are the year, the month and the day.

Problem: Now I want to calculate a new variable that equals the number of days since the 52 weeks high price.

Here is an example of my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date float(year month week day prc) double maxprc_52
10001 18639 2011 1  2 12   10.61 12.350000381469727
10001 18640 2011 1  2 13   10.66 12.350000381469727
10001 18641 2011 1  2 14   10.77 12.350000381469727
10001 18645 2011 1  3 18   10.66 12.350000381469727
10001 18646 2011 1  3 19   10.73 12.350000381469727
10001 18647 2011 1  3 20   10.76 12.350000381469727
10001 18648 2011 1  3 21 10.6699 12.350000381469727
10001 18651 2011 1  4 24   10.77 12.350000381469727
10001 18652 2011 1  4 25 10.8699 12.350000381469727
10001 18653 2011 1  4 26   10.92 12.350000381469727
10001 18654 2011 1  4 27    10.8 12.350000381469727
10001 18655 2011 1  4 28   10.74 12.350000381469727
10001 18658 2011 1  5 31   10.78 12.350000381469727
10001 18659 2011 2  5  1   10.77 12.350000381469727
10001 18660 2011 2  5  2   10.76 12.350000381469727
10001 18661 2011 2  5  3   10.79 12.350000381469727
10001 18662 2011 2  5  4  10.779 12.350000381469727
10001 18665 2011 2  6  7   10.93 12.350000381469727
10001 18666 2011 2  6  8   10.83 12.350000381469727
10001 18667 2011 2  6  9 10.8775 12.350000381469727
10001 18668 2011 2  6 10   10.85 12.350000381469727
10001 18669 2011 2  6 11   10.85 12.350000381469727
10001 18672 2011 2  7 14   10.85 12.350000381469727
10001 18673 2011 2  7 15   10.84 12.350000381469727
10001 18674 2011 2  7 16   10.87 12.350000381469727
10001 18675 2011 2  7 17   10.92 12.350000381469727
10001 18676 2011 2  7 18   10.93 12.350000381469727
10001 18680 2011 2  8 22   10.87 12.350000381469727
10001 18681 2011 2  8 23 10.9699 12.350000381469727
10001 18682 2011 2  8 24   10.94 12.350000381469727
10001 18683 2011 2  8 25   10.99 12.350000381469727
10001 18686 2011 2  9 28   10.98 12.350000381469727
10001 18687 2011 3  9  1      11 12.350000381469727
10001 18688 2011 3  9  2 10.9968 12.350000381469727
10001 18689 2011 3  9  3   11.13 12.350000381469727
10001 18690 2011 3  9  4   11.25 12.350000381469727
10001 18693 2011 3 10  7  11.354 12.350000381469727
10001 18694 2011 3 10  8   11.04 12.350000381469727
10001 18695 2011 3 10  9   11.15 12.350000381469727
10001 18696 2011 3 10 10   11.04 12.350000381469727
10001 18697 2011 3 10 11   11.04 12.350000381469727
10001 18700 2011 3 11 14 11.0801 12.350000381469727
10001 18701 2011 3 11 15  10.825 12.350000381469727
10001 18702 2011 3 11 16   10.95 12.350000381469727
10001 18703 2011 3 11 17    11.1 12.350000381469727
10001 18704 2011 3 11 18   11.05 12.350000381469727
10001 18707 2011 3 12 21    11.2 12.350000381469727
10001 18708 2011 3 12 22   11.11 12.350000381469727
10001 18709 2011 3 12 23   11.09 12.350000381469727
10001 18710 2011 3 12 24   11.17 12.350000381469727
10001 18711 2011 3 12 25   11.24 12.350000381469727
10001 18714 2011 3 13 28   11.31 12.350000381469727
10001 18715 2011 3 13 29   11.25 12.350000381469727
10001 18716 2011 3 13 30   11.35 12.350000381469727
10001 18717 2011 3 13 31   11.73 12.350000381469727
10001 18718 2011 4 13  1   11.75 12.350000381469727
10001 18721 2011 4 14  4 11.7799 12.350000381469727
10001 18722 2011 4 14  5  11.689 12.350000381469727
10001 18723 2011 4 14  6   11.65 12.350000381469727
10001 18724 2011 4 14  7   11.58 12.350000381469727
10001 18725 2011 4 14  8 11.5929 12.350000381469727
10001 18728 2011 4 15 11   11.74 12.350000381469727
10001 18729 2011 4 15 12 11.7399 12.350000381469727
10001 18730 2011 4 15 13   11.74 12.350000381469727
10001 18731 2011 4 15 14   11.83 12.350000381469727
10001 18732 2011 4 15 15   11.75 12.350000381469727
10001 18735 2011 4 16 18 11.6801 12.350000381469727
10001 18736 2011 4 16 19    11.8 12.350000381469727
10001 18737 2011 4 16 20   11.78 12.350000381469727
10001 18738 2011 4 16 21 11.6657 12.350000381469727
10001 18742 2011 4 17 25   11.69 12.350000381469727
10001 18743 2011 4 17 26   11.42 12.350000381469727
10001 18744 2011 4 17 27   11.35 12.350000381469727
10001 18745 2011 4 17 28   11.28 12.350000381469727
10001 18746 2011 4 17 29   11.23 12.350000381469727
10001 18749 2011 5 18  2   11.09 12.350000381469727
10001 18750 2011 5 18  3   11.25 12.350000381469727
10001 18751 2011 5 18  4   11.14 12.350000381469727
10001 18752 2011 5 18  5 11.3199 12.350000381469727
10001 18753 2011 5 18  6   11.28 12.350000381469727
10001 18756 2011 5 19  9    11.5 12.350000381469727
10001 18757 2011 5 19 10    11.2 12.350000381469727
10001 18758 2011 5 19 11 11.2399 12.350000381469727
10001 18759 2011 5 19 12   11.32 12.350000381469727
10001 18760 2011 5 19 13    11.3 12.350000381469727
10001 18763 2011 5 20 16   11.45 12.350000381469727
10001 18764 2011 5 20 17   11.35 12.350000381469727
10001 18765 2011 5 20 18   11.29 12.350000381469727
10001 18766 2011 5 20 19 11.3299 12.350000381469727
10001 18767 2011 5 20 20    11.3 12.350000381469727
10001 18770 2011 5 21 23   11.23 12.350000381469727
10001 18771 2011 5 21 24   11.29 12.350000381469727
10001 18772 2011 5 21 25   11.29 12.350000381469727
10001 18773 2011 5 21 26   11.36 12.350000381469727
10001 18774 2011 5 21 27   11.36 12.350000381469727
10001 18778 2011 5 22 31    11.5 12.350000381469727
10001 18779 2011 6 22  1   11.42 12.350000381469727
10001 18780 2011 6 22  2    11.4 12.350000381469727
10001 18781 2011 6 22  3   11.52 12.350000381469727
10001 18784 2011 6 23  6 11.5299 12.350000381469727
end
format %d date

It would be great if someone could help me!

Best regards
Steven

[Nick Cox]

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

There are various small ambiguities here such as the exact interpretation of 52 weeks (364 days?) and whether that period includes the present or not. These will be resolved by your code.

This should help. Your data example is helpful but the time period is too long to allow a compact illustration.

rangerun is also from SSC. The time since the maximum is evidently the difference between the time of the maximum and the present.

webuse grunfeld, clear

capture program drop mymax

program mymax
    su invest, meanonly
    gen max = r(max)
    
    su year if invest == r(max) , meanonly
    gen when = r(max)
end
    
rangerun mymax , int(year -5 -1) by(company) use(invest year)

l company year invest max when if company == 1

     +-----------------------------------------+
     | company   year   invest      max   when |
     |-----------------------------------------|
  1. |       1   1935    317.6        .      . |
  2. |       1   1936    391.8    317.6   1935 |
  3. |       1   1937    410.6    391.8   1936 |
  4. |       1   1938    257.7    410.6   1937 |
  5. |       1   1939    330.8    410.6   1937 |
     |-----------------------------------------|
  6. |       1   1940    461.2    410.6   1937 |
  7. |       1   1941      512    461.2   1940 |
  8. |       1   1942      448      512   1941 |
  9. |       1   1943    499.6      512   1941 |
 10. |       1   1944    547.5      512   1941 |
     |-----------------------------------------|
 11. |       1   1945    561.2    547.5   1944 |
 12. |       1   1946    688.1    561.2   1945 |
 13. |       1   1947    568.9    688.1   1946 |
 14. |       1   1948    529.2    688.1   1946 |
 15. |       1   1949    555.1    688.1   1946 |
     |-----------------------------------------|
 16. |       1   1950    642.9    688.1   1946 |
 17. |       1   1951    755.9    688.1   1946 |
 18. |       1   1952    891.2    755.9   1951 |
 19. |       1   1953   1304.4    891.2   1952 |
 20. |       1   1954   1486.7   1304.4   1953 |
     +-----------------------------------------+

.

[Steven Sanders]

Thank you very much!