Re: st: Calculating returns if a stock is delisted

[Rebecca Pope <rebecca.a.pope@gmail.com>]

Chris,
In your original post, you asked if you could avoid doing this in
pieces. The answer is yes. You've solved your problem this time, but
perhaps this will help in the future.

Since you've said you're new to Stata, I'm going to show you each step
and then the final command.
(1) To identify stocks that were delisted, by your rule of having the
last day before Dec 31, 1997, we can have Stata conduct a simple logic
test. This creates a 0/1 indicator variable that takes the value 1 if
the last date for the stock is before 12/31/97 AND the current
observation is the last observation for the given stock, otherwise 0.
You can do this with just the date and an "if _n==_N" but we'll need
this version later.

bys Id: gen delisted = (Date[_N] < mdy(12,31,1997) & _n==_N)

(2) Create the "factor" that you'll use to adjust your delisted
returns, multiply the delisted indicator by the appropriate factor.
The factor is 1 (no adjustment) for active stocks & pre-delisting (not
sure what the correct jargon is) dates. Otherwise, it is either -0.3
or -1, as specified in your rules.

gen factor = 1
replace factor = -1 if (DelistCode < 500 | missing(DelistCode)) & delisted==1
replace factor = -0.3 if inrange(DelistCode,500,600)

You can add "& delisted==1" to the last line if you want to ensure
against having a DelistCode appear in your data when you shouldn't be
changing something (i.e. data entry error). Also, just "& delisted"
without the explicit check for the "==1" will work as well. I like
explicit construction though if I know other people will be reading my
code.

(3) Calculate adjusted return by multiplying the factor by the return.
gen adjret = DelistRet
replace adjret = factor*Return if missing(DelistRet)

We can combine all of this into a single generate statement using
Stata's -cond()- function. See -help f_cond- for more information. If
there are DelistCode values > 600, you'll need a 4th cond() statement,
but you didn't mention that possibility, so I'm ignoring it.

bys Id: gen adjret = cond(Date[_N] < mdy(12,1,1997) & _n==_N, ///
cond(!missing(DelistRet),DelistRet,cond(inrange(DelistCode,500,600),-0.3*Return,-Return)),Return)

Here is the result using your sample data. I think this covers all your rules.
    Id        Date   DelistCode   DelistRet   Return   Day   Month
Year   adjret
     1   02jan1964          .          .      1.1     2       1   1964
     1.1
     1   03jan1964        520        -.7      1.2     3       1   1964
     -.7
     2   02jan1964          .          .      1.2     2       1   1964
     1.2
     2   03jan1964          .          .      1.4     3       1   1964
     1.4
     2   04jan1964          .          .      1.3     4       1   1964
    -1.3


I've sent this directly to you as well since you asked for replies to
be forwarded to you since you're on the digest.

Regards,
Rebecca

On Thu, Jul 4, 2013 at 11:53 AM, C. Evans <ce306@cam.ac.uk> wrote:
> Dear Satalist
>
> Problem Solved!
>
> The solution was to create new variables for each situation and then use
> -egen- and rowtotal( ) to finally add them up. I created 3 variables. One
> for delisted returns with codes. 2nd for normal returns if we were not at
> the delist date, ensuring I didn't count the final date of the sample as a
> delist date. 3rd variable was for the stocks which were delisted but they
> didn't have a delist return or code.
>
> I hope this may help someone else out there. Sorry for the spam.
>
> Best Regards,
> Chris Evans
> On Jul 4 2013, C. Evans wrote:
>
>> Dear Satalist
>>
>> Sorry if you have already replied (I was on the stata digest) so please
>> forward the replies if you have. I have been working on this and will give
>> you an update - see my original email below for previous work:
>>
>> For the delisting stocks that have delist codes I have now worked out
>> their discounts and have them stored as delistreturn
>>
>> gen discountreturn500 = (-0.3)*delistret if delistcode>499 &
>> delistcode<601
>> gen discountreturnrest = (-1)*delistret if delistcode<500
>> egen delistreturn = rowtotal( discountreturn500 discountreturnrest )
>> egen maxdate = max(date) if return<5000, by(id)
>> gen date2 = (date)
>> gen nodelist =1 if maxdate==13879
>>
>> The maximum date is 13879 which corresponds to 31Dec1997.
>> I now need to create a returns variable that uses the normal return if the
>> nodelist =1 (so the stock doesn't delist before the end of my sample)or if
>> we are not at the delist date. At the delist date the variable must use the
>> delist return value if it is available. If the delist return is not
>> availible it must calcualte a delist return by using normal return*(-1).
>> Thinking about it now I should probably create a delist date variable where
>> -gen- delistdate = 1 if date2<13879 & date2==maxdate
>>
>> I know this is quite confusing,
>>
>> Best Regards,
>> Chris Evans
>>
>>
>> -----------------------------------------------
>> Dear Statalist
>>
>> My dataset goes from 1964 to 31 December 1997. I am looking at stock
>> returns and trying to create a new variable that will be related to
>> returns. This new variable will use DelistRet (delisted return) if
>> avilable
>> and then use the delist code to determine what to multiply it by. As such,
>> a delist code of between 500 and 600 would be multiplied by -0.3. However,
>> sometimes the stock finishes at a date before 31 December 1997 and has no
>> delistcode or delistret, for this I need to multiply the Return by -1. For
>> all the other days when the stock is trading and not delisted I would just
>> use Return.
>>
>> Below is an example of my data (not going all the way to 31Dec1997)
>>
>> Id  Date   DelistCode DelistRet Return Day Month   Year
>> 1  2Jan1964    .        .        1.1    2    1     1964
>> 1  3Jan1964   520       -0.7     1.2    3    1     1964
>> 2  2Jan1964    .        .        1.2    2    1     1964
>> 2  3Jan1964    .        .        1.4    3    1     1964
>> 2  4Jan1964    .        .        1.3    4    1     1964
>>
>> I was trying to do this in stages as my stata skills are not great. I had
>> an idea to use:
>>
>> egen delisted = 1 if max(date)  != day==31 & month==12 & year ==1997
>>
>> Which I thought would give a value of 1 for the stocks that didn't reach
>> the max date. I'd be much happier to use a different line of code and I am
>> open to any suggestions.
>>
>> Best Regards,
>> Chris Evans
>>
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
>>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/