Days since 52 week high

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Days since 52 week high

Hi,

We managed to work out how to calculate 52 week highs from daily stock market data.


However we also need to work out how many days since the last 52 week high.

Our data looks like this. It is 9m records so a little computationally intensive.

116821NOV20059.129.35
116822NOV20059.099.35
116823NOV20059.079.35
116824NOV20059.329.35
116825NOV20059.329.35
116828NOV20059.569.56
116829NOV20059.719.71
116830NOV20059.729.72
116801DEC20059.619.72
116802DEC20059.619.72
116805DEC20059.619.72
116806DEC20059.719.72
116807DEC20059.929.92
116808DEC20059.749.92
116809DEC20059.689.92
116812DEC20059.629.92

For example, on 6th December, the days since the last 52 week high would be 4.

We need this for every date.

Note that there will also been days with missing data i.e. no trade occurred.

Many thanks for any assistance or direction!


Accepted Solutions
Solution
‎06-06-2015 09:42 AM
Respected Advisor
Posts: 3,775

Re: Days since 52 week high

Perhaps adding NOTSORTED would work.  Sample data should be included.

View solution in original post


All Replies
Contributor
Posts: 44

Re: Days since 52 week high

I'm not sure if this is what you are looking for but at least there is some direction. I assumed 52 weeks as 365 days.

And I'm not sure what do you want for those days which are current year's highs.

data have;

attrib date format=date9. informat=date9.;

infile cards dlm=' ';

input stock date dailylow dailyhigh;

cards;

1168 21NOV2005 9.12 9.35

1168 22NOV2005 9.09 9.35

1168 23NOV2005 9.07 9.35

1168 24NOV2005 9.32 9.35

1168 25NOV2005 9.32 9.35

1168 28NOV2005 9.56 9.56

1168 29NOV2005 9.71 9.71

1168 30NOV2005 9.72 9.72

1168 01DEC2005 9.61 9.72

1168 02DEC2005 9.61 9.72

1168 05DEC2005 9.61 9.72

1168 06DEC2005 9.71 9.72

1168 07DEC2005 9.92 9.92

1168 08DEC2005 9.74 9.92

1168 09DEC2005 9.68 9.92

1168 12DEC2005 9.62 9.92

;

run;

proc sql;

create table have2 as

select *, monotonic() as obs

from have;

quit;

proc sql;

  create table want as

  select h1.stock, h1.date, h1.dailylow, h1.dailyhigh, coalesce(h1.obs-h2.obs,0) as days_since, h2.date as highdate, h2.dailylow as highdailylow, h2.dailyhigh as highdailyhigh

  from   have2 h1

         left join have2 h2

               on  h1.stock = h2.stock

               and h1.date gt h2.date

               and h1.date lt h2.date+365

               and h2.dailylow ge h1.dailylow

  group by h1.stock, h1.date

  having h2.dailylow = max(h2.dailylow) ;

quit;

Valued Guide
Posts: 854

Re: Days since 52 week high

For your purpose you probably want to replace '06DEC2005'd with today() or another date field but I think this will work for your purposes:

data want;

set have;

format high_date date9.;

retain high_date;

if dailyhigh = dailylow then high_date = date;

diff_date = '06DEC2005'd - high_date;

run;

Respected Advisor
Posts: 3,775

Re: Days since 52 week high

Based on your rather sparse description of what you have and what you want.

data stock;
   infile cards expandtabs;
  
input stock date:Date. close high52;
   format date date9.;
  
cards;
1168  21NOV2005   9.12  9.35
1168  22NOV2005   9.09  9.35
1168  23NOV2005   9.07  9.35
1168  24NOV2005   9.32  9.35
1168  25NOV2005   9.32  9.35
1168  28NOV2005   9.56  9.56
1168  29NOV2005   9.71  9.71
1168  30NOV2005   9.72  9.72
1168  01DEC2005   9.61  9.72
1168  02DEC2005   9.61  9.72
1168  05DEC2005   9.61  9.72
1168  06DEC2005   9.71  9.72
1168  07DEC2005   9.92  9.92
1168  08DEC2005   9.74  9.92
1168  09DEC2005   9.68  9.92
1168  12DEC2005   9.62  9.92
;;;;
   run;
proc print;
  
run;
data days;
   set stock;
   by stock high52;
   if first.high52 then daysince=0;
  
else daysince+1;
  
run;
proc print;
  
run;
6-5-2015 9-59-31 AM.png
Occasional Contributor
Posts: 5

Re: Days since 52 week high

Thanks so much data_null_ and sorry for the sparse description

The only problem with this code is that it doesn't like situations where there are duplicate 52 week highs. That is, it has a sorting problem. Any suggestions for this?

Solution
‎06-06-2015 09:42 AM
Respected Advisor
Posts: 3,775

Re: Days since 52 week high

Perhaps adding NOTSORTED would work.  Sample data should be included.

Occasional Contributor
Posts: 5

Re: Days since 52 week high

Thanks very much, this solved the problem. Again, thanks for your help!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 428 views
  • 6 likes
  • 4 in conversation