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

[Nick Cox <njcoxstata@gmail.com>]

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/