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: Find the fiscal year for each obs
From
Tom <[email protected]>
To
[email protected]
Subject
Re: st: Find the fiscal year for each obs
Date
Sat, 6 Apr 2013 16:45:25 +0200
Dear Yu Chen,
It might be that the discussion at
http://www.nber.org/sys-admin/large-stata-datasets.html is helpful to
you. It specifically addresses large datasets.
Kind regards,
Tom
On Sat, Apr 6, 2013 at 4:24 PM, Yu Chen, PhD <[email protected]> wrote:
> Nick,
> Thank you so much for the code. It works great. However, I have a
> further question. Assume File A has 5000 firms, and each has 360 daily
> stock price observations for 5 years, then there are 9,000,000
> observations in File A. Assume File B has 5 years data (i.e., fiscal
> year definition) for each firm, then there are 25,000 observations in
> File B. If I use -merge-, the resulting file will be too large for the
> computer to handle. So I think some kind of loop structure might need
> to be used. Or there may be a better solution. Could you please help
> me in this regard? What code will be good to handle this problem?
> Best,
>
> Yu Chen
>
>
>
>
> On Sat, Apr 6, 2013 at 6:50 AM, Nick Cox <[email protected]> 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
>> [email protected]
>>
>>
>> On 6 April 2013 12:15, Yu Chen, PhD <[email protected]> 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/
*
* 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/