Calculate 52-Week High

[Gilles Dijkman]

Dear users

I would like to calculate the 52-Week High stock price on a certain date. What I have right now is a list of dates with the Stock Price/day. So I would like to generate a new variable that gives me the max value over a period of 52 weeks before that date. See the screen below for an example of my data set. I hope my question is clear enough this way.

[Nick Cox]

Please do

1. read the Statalist FAQ Advice #12 for explicit advice not to use screenshots.

2. search the forum for mentions of rangestat (SSC), which will solve your problem.

[Gilles Dijkman]

Thanks!

The following command seems to work:
rangestat (max) prc, interval(date -252 0) by(cusip)

but can I assume there are always 252 working days in a year? This probably differs per country, is there a way to make the interval look for the exact date a year earlier?

[Nick Cox]

The same daily date except a year earlier is something like

gen pdate = mdy(month(date), day(date), year(date) - 1)  
replace pdate = mdy(3, 1, year(date) - 1) if missing(pdate)

and you can use that directly in rangestat

[Gilles Dijkman]

This is what I did now:
gen pdate = mdy(month(date), day(date), year(date) - 1)
replace pdate = mdy(3, 1, year(date) - 1) if missing(pdate)
rangestat (max) price, interval(date pdate 0) by(cusip)

But now the results are 1 month off (i.e. the stock price at 30th april 2013 looks back until 1st march 2012). What am I doing wrong?

And another question: I would like it to only show a result if there is information in the past year (i.e. my data-set starts on the 1st January 1990, so it wouldn't be possible to calculate the 52-week High in the first year. Not every company starts in 1990 however (otherwise I could drop observations if date ==1990.

Thanks for all the help!

[Nick Cox]

I can't reproduce that:

. local d = mdy(4, 30, 2013)

. di %td mdy(month(`d'), day(`d'), year(`d') - 1)
30apr2012

Just ignore what you don't want is my answer to the supplementary.

[Gilles Dijkman]

Okay. I have the formula working correctly now, so it displays the 52-Week High Price, but your suggestion to just ignore what I don't want is not an answer to the supplementary, because I'm going to merge this data with another data-set and we're talking about millions of observations.

So my question remains, is there a way to make the outcome be NA/0/missing value if the implied range is not possible. Maybe we could make a new variable calculates if the observation goes back at least one year (based on the cusip). So if we have a date like 02feb1992, I would like to check if the date goes back until at least 02feb1991. Any suggestions?

[Nick Cox]

rangestat ensures that missing results automatically ensue when there are no pertinent observations. Also, rangestat has as one of its documented results a count of non-missing observations and in its help includes this explicit advice

You should always use the (count) statistic to get the number of non-missing values
and use the count to reject results based on an insufficient sample size.

It's also explicit that you can calculate the window minimum and maximum on any variable, including the date.

#7 appears to be the first mention of merging. That's fine, but I have to confess to not thinking about a problem you did not mention. At worst you just have to drop observations beforehand that can't be merged successfully or let merge indicate what they are.

[Gilles Dijkman]

Okay. So I now have the Offer Price (OfferP = y) of a certain set of M&A deals and the 52-week High Stock Price (Price52 = x), which I want to regress using a piece-wise regression. I used the steps of the mkspline command explained here, but it's not giving me the results I'm expecting.

I want to look at ranges of Price52 between 0.00 - 0.20, 0.20 - 0.80, and 0.80 - 1.00.


These are the commands I tried:
mkspline Price1 0.20 Price2 0.80 Price3 = Price52
regress OfferP Price1 Price2 Price3 if Price52 >= 0.00 & Price52 < 1.00

or

mkspline Price0 0 Price1 0.20 Price2 0.80 Price3 1.00 Price4 = Price52
regress OfferP Price1 Price2 Price3

How do I deal with observations that are not in the range between 0.00 and 1.00? And any idea what could be wrong with the commands above? (if it helps, one of the three ranges returns a negative value, while I expect them all to be positive).

[Sakvarelidze Elene]

Dear Nick,

I am also facing the same problem meaning: i dont want stata to display the max value when there is not sufficient past data... So if i got right you suggested to drop the observations that we dont need?

BR,

Elene

[Nick Cox]

#10 is superseded by https://www.statalist.org/forums/for...sing-rangestat

Please don't pursue the same question in multiple threads.