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

[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/