Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Assign fiscal-year end values to previous 365 days/12 months
From
miriam geringer <[email protected]>
To
[email protected]
Subject
Re: st: Assign fiscal-year end values to previous 365 days/12 months
Date
Thu, 16 Jan 2014 17:34:59 +0100
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 <[email protected]>:
> 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
> [email protected]
>
>
> On 16 January 2014 10:35, Nick Cox <[email protected]> wrote:
>> I've corrected a typo in this version.
>>
>> Nick
>> [email protected]
>>
>>
>> On 16 January 2014 10:27, Nick Cox <[email protected]> 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
>>> [email protected]
>>>
>>>
>>> On 16 January 2014 09:11, miriam geringer <[email protected]> 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/