Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.

# Re: st: Find the fiscal year for each obs

 From Devra Golbe To statalist@hsphsun2.harvard.edu Subject Re: st: Find the fiscal year for each obs Date Tue, 23 Apr 2013 14:19:22 -0400

```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/
```