06-05-2015 02:13 AM
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.
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!
06-05-2015 05:20 AM
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.
attrib date format=date9. informat=date9.;
infile cards dlm=' ';
input stock date dailylow dailyhigh;
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
create table have2 as
select *, monotonic() as obs
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) ;
06-05-2015 09:34 AM
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:
format high_date date9.;
if dailyhigh = dailylow then high_date = date;
diff_date = '06DEC2005'd - high_date;
06-05-2015 11:01 AM
Based on your rather sparse description of what you have and what you want.
06-06-2015 04:00 AM
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?
Need further help from the community? Please ask a new question.