Re: st: Assign fiscal-year end values to previous 365 days/12 months

[Nick Cox <njcoxstata@gmail.com>]

Thanks for this.

1. I can't really comment on the economic virtues of interpolation for
the variables you mention. On general grounds, it might be better to
assign values notionally to the middle of the year, but then again if
you think there are lagged responses that might be questionable too. I
occasionally describe myself flippantly as an ex-economist on the
dubious grounds that I studied it for two years in secondary school,
so I wouldn't take these thoughts too seriously.

2. It does seem sensible to use quarterly data if you have them. I was
inferring that only annual data were available.
Nick
njcoxstata@gmail.com


On 16 January 2014 16:34, miriam geringer <miriam.geringer@gmail.com> wrote:
> Nick, thanks for the amazing answer!
> Your second described command works like a charm!
>
> You mentioned interpolation. Do you think this would be reasonable for:
> - Earnings per share
> - Common/Ordinary Equity Value
>
> Would it even be more accurate if I would take quarterly instead of
> annual data (from Compustat) and then interpolate the quarterly EPS &
> Equity values? This should provide me with the closest possible
> estimation for daily EPS & Equity. Am I correct?
>
> I am doing this, because later on, I need to find matching firms with
> similiar Earnings-to-Price and Book-to-Equity values.
>
>
> 2014/1/16 Nick Cox <njcoxstata@gmail.com>:
>> I want to add a plug for thinking about this as interpolation.
>>
>> First, it _is_ interpolation, namely block-wise replacement of missing
>> values with existing values. There is a specific rationale for that
>> here because the non-missing values are defined and calculated for
>> each year, so arguably what you are doing is just a specific kind of
>> data merging.
>>
>> But -- second -- this raises the simple question of whether other
>> interpolation methods might work as well or better -- if not in this
>> problem, then in loosely similar problems.
>>
>> I posted some general remarks recently about interpolation, so I will
>> just add a cross-reference:
>> http://www.stata.com/statalist/archive/2014-01/msg00315.html
>>
>>
>> As I've
>> Nick
>> njcoxstata@gmail.com
>>
>>
>> On 16 January 2014 10:35, Nick Cox <njcoxstata@gmail.com> wrote:
>>> I've corrected a typo in this version.
>>>
>>> Nick
>>> njcoxstata@gmail.com
>>>
>>>
>>> On 16 January 2014 10:27, Nick Cox <njcoxstata@gmail.com> wrote:
>>>> I assume that you -merge-d the datasets. Assuming that fiscal year
>>>> values are identified with the last day of the fiscal year, the next
>>>> fiscal year can be seen to start on the next day. So, we should be
>>>> able to identify blocks of observations for each fiscal year.
>>>>
>>>> I assume also some -dailydate- variable.
>>>>
>>>> The start of the fiscal year is defined by there being non-missing
>>>> values for the previous day:
>>>>
>>>> bysort PERMNO (dailydate) : ///
>>>> gen fyear = _n == 1 | !missing(earnings_per_share[_n-1])
>>>>
>>>> Note that we include code _n == 1 for the first observation for each
>>>> firm. Even if the first day is the start of a new fiscal year
>>>> (unlikely), there won't be non-missing values for the previous day.
>>>>
>>>> Now we define blocks by
>>>>
>>>> bysort PERMNO : replace fyear = sum(fyear)
>>>>
>>>> so that series which are 1 (block of 0) 1 (block of 0) .... become
>>>> (block of 1) (block of 2) ... separately by fiscal year.
>>>
>>> We could combine those two statements into one.
>>>
>>>> Now we assign variables such as -earnings_per_share- to every
>>>> observation in the fiscal year
>>>>
>>>> egen eps = total(earnings_per_share), by(PERMNO fyear)
>>>>
>>>> Each total should be just the total of one value, Stata helpfully
>>>> ignoring the missings. We should check that assumption
>>>>
>>>> egen eps_count = count(earnings_per_share), by(PERMO fyear)
>>>>
>>>> That variable should be 0 or 1 (and nothing else).
>>>>
>>>> assert inlist(eps_count, 0, 1)
>>>>
>>>> Another check is that there are no more than 366 observations for each
>>>> block of -fyear-.
>>>>
>>>> That said, something even easier might work. You want to copy
>>>> non-missing values backwards in blocks. It is easier to reverse time
>>>> and copy them forwards:
>>>>
>>>> gen neg_dailydate = -dailydate
>>>> gen eps = earnings_per_share
>>>> bysort PERMO (neg_dailydate) : replace eps = eps[_n-1] if missing(eps)
>>>>
>>>> See also
>>>>
>>>> FAQ     . . . . . . . . . . . . . . . . . . . . . . . Replacing missing values
>>>>         . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
>>>>         2/03    How can I replace missing values with previous or
>>>>                 following nonmissing values?
>>>> http://www.stata.com/support/faqs/data-management/replacing-missing-values/
>>>>
>>>> Generic tips in this territory:
>>>>
>>>> 1. -by:- and -egen- are your friends.
>>>>
>>>> 2. Stata works forward in time, so indicators for starts can be easier
>>>> to work with.
>>>>
>>>> 3. The obvious facts that the first day of a period is one after the
>>>> last day of the previous period and the last day of a period is one
>>>> before the first day of the next period can be useful. For example,
>>>> code for the last day of a month is tricky given different month
>>>> lengths of 28, 29, 30, 31; it is easier to get the first day of the
>>>> next month and subtract 1.
>>>>
>>>> 4. The inclusion of anything depending on the magic number 365 in code
>>>> is usually a bad idea. Not only do you then need to fix the code for
>>>> leap years, you can get small or even large problems with gaps in
>>>> data, incomplete years, etc.
>>>>
>>>> 5. Reversing time can be useful.
>>>> Nick
>>>> njcoxstata@gmail.com
>>>>
>>>>
>>>> On 16 January 2014 09:11, miriam geringer <miriam.geringer@gmail.com> wrote:
>>>>> Hi guys,
>>>>>
>>>>> I have CRSP stock return data on a daily basis over a period of 30 years.
>>>>> On the other hand, I have annual firm data from Compustat.
>>>>>
>>>>> Each firm has a different fiscal-year end. The fiscal-year end can be the
>>>>> last day of each month, depending on firm.
>>>>>
>>>>> What I want to do now is the following: Assign the annual data (the
>>>>> fiscal-year end data) to all previous twelve months. Basically, assume that
>>>>> the data from the last day of the fiscal-year is valid for the entire
>>>>> previous twelve months.
>>>>>
>>>>> For example, on fiscal-year end, I have the following values (assume fiscal
>>>>> year-end is 31 August 2010):
>>>>> - Earnings-per-share
>>>>> - Common/Ordinary Equity
>>>>>
>>>>> Now I want to assign these values of 31 August 2010 to the previous twelve
>>>>> months on a daily basis. Basically that I have Earnings-per-share and
>>>>> Common equity values for the 365 days prior to 31 August 2010. In our
>>>>> example, this means that I have between 1 September 2009 and 30 August 2010
>>>>> the same values as on 31 August 2010. The fiscal-year end value is a proxy
>>>>> for the previous twelve months.
>>>>>
>>>>> So far, all days during the fiscal-year, except the last one, have missing
>>>>> values, which need to be replaced.
>>>>>
>>>>> Does anyone know how I could do that?
>>>>> Variables I have:
>>>>> - Firm identifier (PERMNO) (1000 different firms with each 30-years daily
>>>>> data)
>>>>> - Earnings per share on fiscal-year-end
>>>>> - Common/Ordinary equity on fiscal-year-end
>>>>> - Also, I calculated dofd (daily numerical variable), mofd (monthly
>>>>> numerical variable) and yofd (annual numerical variable) if that's helpful
>>>>>
>>>>> It has to be something like:
>>>>> If there is a value on fiscal-year-end for firm X, copy that value to the
>>>>> previous 365 days.
>> *
>> *   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/