Re: st: Assign fiscal year-end values to a specific event in time

[Torsten Häberle <haeberle.torsten@gmail.com>]

Hi Nick,

thanks for your answer. It's not an interpolation problem. I just want
to assign either the last available fiscal year-end value (before the
event) or the first available fiscal year-end value (after the event)
to the event date.

But as you said, Stata keeps on going until it reaches the next
non-missing. But this is wrong in my case. Values are only valid in
this time window:
- Either 12 months before the event (as in dataset 1) or
- 12 months after the event (as in dataset 2)

But Stata should not copy a value further in the future if let's say
the next fiscal year-end value is missing.

I just need an approximate value on event date. For dataset 1, this is
a value from the previous 12 months. For dataset 2, it is one from the
following 12 months.

Do you know what I mean?

Thanks for helping


2014-01-28 Nick Cox <njcoxstata@gmail.com>:
> Yes; if you copy previous values to replace missing values, Stata
> keeps on going until it reaches the next non-missing.
>
> But what are you trying to do here? Like many others, your problem
> seems more of an interpolation problem, and  copying the last known
> value is not necessarily the best way forward. See
>
> http://www.stata.com/statalist/archive/2014-01/msg00315.html
>
> for some general comments and mentions of Stata commands for interpolation.
>
> Nick
> njcoxstata@gmail.com
>
> On 27 January 2014 21:36, miriam geringer <miriam.geringer@gmail.com> wrote:
>
>> In terms of my example:
>>
>> Company   Date      Year     Price      Event    Month*
>> A                 31Okt    1988                               334
>> A                 29Nov    1988    10                      335
>> A                 31Dez    1988                              336
>> A                 31Jan     1989                              337
>> A                 14Feb     1989               EventX   338
>> A                 28Feb     1989                              339
>> A                 31Mar     1989                              340
>> B
>> B
>> ....
>>
>> *Month = Numerical date variable, monthly frequency. Stata command = mofd(Date)
>>
>> This is my command: bysort Company (Month): replace Price =
>> Price[_n-1] if missing(Price)
>>
>>>>> then 10 is spread forward until the next fiscal year-end value is available
>>>>> Basically, the price indicates that the fiscal year ends. In our case, on 29Nov 1988, there is a price available. This means the fiscal year ends here. And this price shall now be spread forward until the next fiscal year-end arrives.
>>>>> What I wonder now: What happens if the next fiscal year-end value (in our example 1989) is now missing?
>>
>> Example:
>>
>> Company   Date      Year     Price      Event    Month*
>> A                 31Okt    1988                               334
>> A                 29Nov    1988    10                      335
>> A                 31Dez    1988    10                      336
>> A                 31Jan     1989    10                      337
>> A                 14Feb     1989    10        EventX  338
>> A                 28Feb     1989    10                      339
>> A                 31Mar     1989    10                      340
>> A                 30Apr      1989    10
>> A                 31May     1989    10
>> A                 30Jun      1989     10
>> A                 31Jul        1989    10
>> A                 31Aug      1989     10
>> A                 30Sep      1989     10
>> A                 31Okt       1989     .
>>
>> If the price on 31 Okt 1989 (the fiscal year-end) is missing, Stata
>> should NOT replace the missing value with the price (10) from the
>> previous fiscal year 1988. Instead, it should remain missing. But I
>> think my command doesn't consider this?
>
>  2014-01-26 Nick Cox <njcoxstata@gmail.com>:
>
>>> That does not help me in terms of your example or explanation.
>>>
>>> A strategy is to "spread" values to other observations for the same
>>> firm and then look for intersections. My Stata Journal article just
>>> cited gives examples.
>
> On 26 January 2014 11:51, miriam geringer <miriam.geringer@gmail.com> wrote:
>
>>>> I have two variables, which tell me when the fiscal year ends.
>>>> An exact variable, e.g. 31Jul 1994 and a year variable, e.g. 1994.
>
> 2014-01-26 Nick Cox <njcoxstata@gmail.com>:
>
>>>>> How can you tell Stata in terms of these data when fiscal years end?
>>>>>
>>>>> What kind of variable is -Date- (numeric or string)? Are you using
>>>>> Stata dates (see -help dates and times-)?
>>>>>
>>>>> There is much discussion of technique in
>>>>> http://www.stata-journal.com/article.html?article=dm0055
>
> On 25 January 2014 19:19, miriam geringer <miriam.geringer@gmail.com> wrote:
>
>>>>>> I have the following two datasets:
>>>>>>
>>>>>> 1)
>>>>>>
>>>>>> Company   Date      Year     Price      Event
>>>>>> A                 31Okt    1988
>>>>>> A                 29Nov    1988    10
>>>>>> A                 31Dez    1988
>>>>>> A                 31Jan     1989
>>>>>> A                 14Feb     1989                EventX
>>>>>> A                 28Feb     1989
>>>>>> A                 31Mar     1989
>>>>>> B
>>>>>> B
>>>>>> ....
>>>>>>
>>>>>> Basically, there is some event going on in 1989. This event can happen
>>>>>> in every month, on every day. I want to assign a specific value from
>>>>>> the previous fiscal-year to the event in 1989. To be more precise, I
>>>>>> want to assign the fiscal year-end price from fiscal year 1988 to the
>>>>>> event which happens - in our example - on 14Feb 1989. In the end, I
>>>>>> only need to know in what year the event happened and the
>>>>>> corresponding price from the previous fiscal year-end. Sometimes,
>>>>>> there is no price available in the previous fiscal year, then it
>>>>>> should be just marked as missing value.
>>>>>>
>>>>>> ---- Fiscal Year-End 1988 ------- Event -------- Fiscal Year-End 1889
>>>>>>>>>>> Assign Fiscal Year-End 1988 to Event
>>>>>>
>>>>>> The fiscal year can end in every month and it is possible that, for
>>>>>> example, the fiscal year ends on 31Jan 1989 and the event occurs on
>>>>>> 14Feb 1989. Then the 31Jan 1989 value shall be assigned.
>>>>>>
>>>>>> It should be something like:
>>>>>> If Stata finds an EventX in fiscal year 1989 of Company A, go to
>>>>>> fiscal year 1988 and take the 1988 fiscal year-end price of Company A
>>>>>> and assign it to the EventX in 1989.
>>>>>>
>>>>>> 2) The second dataset is identical. However, this time I want to
>>>>>> assign the first available fiscal year-end value.
>>>>>>
>>>>>> Company   Date      Year     Price      Event
>>>>>> A                 31Okt    1988
>>>>>> A                 29Nov    1988
>>>>>> A                 31Dez    1988
>>>>>> A                 31Jan     1989
>>>>>> A                 14Feb     1989               EventY
>>>>>> A                 28Feb     1989
>>>>>> A                 31Mar     1989    10
>>>>>> B
>>>>>> B
>>>>>> ....
>>>>>>
>>>>>> In this example, let's assume the year ends on 31Mar 1989 and on that
>>>>>> date, the price is 10 (the fiscal year-end price). I want to assign
>>>>>> this price to the row with the EventY and 14Feb. The same as in
>>>>>> dataset 1, just the other way around.
>>>>>>
>>>>>> It should be something like:
>>>>>> If Stata finds an EventY in year 1989 of Company A, take the first
>>>>>> available fiscal year-end value (first one available after the event
>>>>>> occured). The event can occur in February and the fiscal year can
>>>>>> could end shortly after. Just take the next available value, no matter
>>>>>> whether there is a calendar year shift in between or not.
> *
> *   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/