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]

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/

**Follow-Ups**:

**References**:**st: Assign fiscal-year end values to previous 365 days/12 months***From:*miriam geringer <[email protected]>

**Re: st: Assign fiscal-year end values to previous 365 days/12 months***From:*Nick Cox <[email protected]>

**Re: st: Assign fiscal-year end values to previous 365 days/12 months***From:*Nick Cox <[email protected]>

**Re: st: Assign fiscal-year end values to previous 365 days/12 months***From:*Nick Cox <[email protected]>

- Prev by Date:
**Re: st: Why is in memory size different?** - Next by Date:
**st: emptycells(reweight)** - Previous by thread:
**Re: st: Assign fiscal-year end values to previous 365 days/12 months** - Next by thread:
**Re: st: Assign fiscal-year end values to previous 365 days/12 months** - Index(es):