Re: st: Find the fiscal year for each obs

[Devra Golbe <dgolbe@gmail.com>]

Just saw this thread; hope this is still useful.

Nick's solution is, as always,  extremely helpful, as is his admonition 
that some things the writer assumes are understood by all often are 
not.  In fact,  the convention for assigning fiscal years in the US is 
not, as Yu Chen's example implies, that the fiscal year is the calendar 
year in which the fiscal year begins.  Compustat's convention for US 
companies is that a fiscal year ending in June or later is assigned to 
the year in which the fiscal year ends:  a fiscal year ending in January 
through May is assigned to the year in which the fiscal year begins.

Here's an alternative solution which does not depend on the replacement 
of the fiscal year variable post-merge.

*********************************************************
clear
input str3 Firm str10 Date
IBM    "2009/01/15"
IBM    "2010/01/16"
3M     "2010/07/01"
3M     "2011/07/02"
end
gen Ndate = date(Date, "YMD")
gener mdate=mofd(Ndate)       /*Assign month-year to each observation*/
sort Firm mdate
save FileA, replace

clear
input str3 Firm  Fiscal  str10 Begin str10 End
IBM    2009               "2009/03/01"   "2010/02/28"
IBM    2010               "2010/03/01"   "2011/02/28"
3M    2010               "2010/06/01"    "2011/05/31"
3M     2011               "2011/06/01"     "2012/05/31"
end

gen Nend = date(End, "YMD")
gen fyend=mofd(Nend)   /*Find fiscal-year-end month*/
expand 12
bysort Firm Nend: gen mdate= fyend-12 + _n     /*Generate the 12 months 
comprising each fiscal year*/
sort Firm mdate
merge 1:m Firm mdate using FileA, keep (match using)    /*match by firm 
and month-year*/
drop _merge
sort Firm Ndate
list Firm Fiscal Begin End Date
*****************************************************


Devra Golbe
Hunter College / CUNY





On 4/6/2013 7:50 AM Nick Cox wrote:
> I'd just -merge- the files. The convention seems to be that Fiscal
> Years are labelled by the year they start in. (It's _always_ a good
> idea to explain stuff that won't be universally understood; assuming
> that people work in your field is usually wrong.) Either way, there's
> a correction for dates that are in calendar years that differ from
> fiscal years. This example uses slightly different fake data. I used
> an ancient -merge- only because I am writing from an ancient machine.
> (I have Stata 12, but this machine doesn't read DVDs.)
>
> clear
> input str3 Firm str10 Date
> IBM    "2009/01/15"
> IBM    "2010/01/16"
> 3M     "2010/07/01"
> 3M     "2011/07/02"
> end
> gen Ndate = date(Date, "YMD")
> gen Fiscal = year(Ndate)
> sort Firm Fiscal
> save FileA, replace
>
> clear
> input str3 Firm  Fiscal  str10 Begin str10 End
> IBM    2009               "2009/03/01"   "2010/02/28"
> IBM    2010               "2010/03/01"   "2011/02/28"
> 3M    2010               "2010/06/01"    "2011/05/31"
> 3M     2011               "2011/06/01"     "2012/05/31"
> end
>
> gen Nbegin = date(Begin, "YMD")
> gen Nend = date(End, "YMD")
> sort Firm Fiscal
>
> version 10: merge Firm Fiscal using FileA
>
> replace Fiscal = Fiscal - 1 if !inrange(Ndate, Nbegin, Nend)
> drop _merge
> list Firm Fiscal Begin End Date
>
>
> Nick
> njcoxstata@gmail.com
>
>
> On 6 April 2013 12:15, Yu Chen, PhD <profyuchen@gmail.com> wrote:
>
> > I have two files. File A contains the daily stock price for each firm,
> > and file B contains the beginning and ending dates for a fiscal year
> > for each firm. See below for an illustration (data are hypothetical):
> >
> > File A:
> > Firm   Date                Price
> > IBM    2009/01/15       xxxx
> > IBM    2009/01/16       xxxx
> > ...................
> > 3M     2010/07/01       yyyy
> > 3M     2010/07/02       yyyy
> > ...................
> >
> > File B:
> > Firm   Fiscal Year      Beg. Date    End. Date
> > IBM    2009               2009/03/01   2010/02/28
> > IBM    2010               2010/03/01   2011/02/28
> > ..................
> > 3M     2010               2010/06/01    2011/05/31
> > 3M     2011               2011/06/01     2012/05/31
> > ..................
> >
> > Now, I want indicate in File A for each observation the fiscal year
> > the observation belongs to, according to the definition in File B.
> > Conceptually it seems easy: Just compare the date of the observation
> > in File A with the beginning and ending dates in File B.
> *
> *   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/