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

[Nick Cox <njcoxstata@gmail.com>]

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(year)

so that series which are 1 (block of 0) 1 (block of 0) .... become
(block of 1) (block of 2) ... separately by fiscal year.

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.
>
> Thanks
> *
> *   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/