Re: st: Collapsing daily data to a monthly data on the 15th each month

[Michael Barker <mdb96statalist@gmail.com>]

I'm not sure if you are still working on this, but here is another suggestion.

If you want the average from the 16th of one month to the 15th of the
next month, you could first offset the date variable, collapse your
data, then increment your date variable.

So if the observation marked Jan 15 contains an average over the days
Dec 16 to Jan 15:

* move first 15 days of January to December, Dec 1-15 to Nov, etc.
gen date2 = date - 15
* move new month of December, Dec 16 - Jan 15, to January
g dm=mofd(date2) +1

format dm %tm
collapse  prc ret shrout vwretd ewretd, by (dm)

* Change Jan 1 to Jan 15
gen date = dofm(dm) + 15

But it seems like it would also be valid to construct monthly averages
centered around the 15th. So the observation marked Jan 15 would be an
average of Jan 1 to Jan 31. If you think that will work, just use your
collapse commands, then increment your date back up:

g dm=mofd(date)
format dm %tm
collapse  prc ret shrout vwretd ewretd, by (dm)

gen date = dofm(dm) + 15
format date %td
tsset date, monthly

On Wed, Jan 22, 2014 at 7:49 AM, Nick Cox <njcoxstata@gmail.com> wrote:
> You can work this out.
>
> The start of each "month" is the 16th of that "month". I called this
> the "target" for some reason that escapes me, perhaps that it is our
> aim to calculate this.
>
> Here is an attempt at more general code, setting aside the previous
> idea of copying of dates that are already the 16th and surgery at the
> beginning of each panel. (You didn't say before that you had panels.
> Conversely, my code before assumed that the dates of 16th are always
> present in the data. I don't assume that any more.)
>
> The previous target is in the previous month for days of the month up
> to 15 and in the same month otherwise. But the previous month for
> January is naturally December.
>
> gen month_target = cond(day(date) <= 15, month(date) - 1, month(date))
> replace month_target = 12 if month_target == 0
>
> Similarly, the year of the target is in the same year, unless it was
> in the previous December.
>
> gen year_target = cond(month_target == 12, year(date) - 1, year(date))
>
> So,
>
> gen target = mdy(month_target, 16, year_target)
>
> and your calculations are typically
>
> bysort <panelid> target :
>
> or
>
> ... by(<panelid> target)
>
> I've not tested this code, but it should get you started.
>
> Nick
> njcoxstata@gmail.com
>
> On 22 January 2014 12:21, Anonymous User wrote:
>
>> Thank you Nick for the very useful reply!
>>
>> Unfortunately I'm still struggling with the second command:
>> replace target = mdy(<month>, 16, <year>) in 1
>>
>> As you described, there is some surgery needed, because not every
>> month has a 16th day(business days). Problem is that I can't adapt
>> this manually as I have data for 10 years and for 400 firms (about 1.5
>> million observations).
>>
>> Hopefully you can help me out how to fix this.
>
> On 21 January 2014 18:56, Nick Cox <njcoxstata@gmail.com> wrote:
>
>>> Let's identify each month by a start date that is 16th of each month
>>> and each year.
>>>
>>> The target starts are
>>>
>>> gen target = date if day(date) == 16
>>>
>>> Some surgery will needed at the beginning to enter an appropriate
>>> target for observation 1 if the first day is not the 16th.
>>>
>>> replace target = mdy(<month>, 16, <year>) in 1
>>>
>>> Then we copy each target by
>>>
>>> replace target = target[_n-1] if missing(target)
>>>
>>> then -collapse ... , by(target)-
>
> On 21 January 2014 17:42, Anonymous User wrote:
>
>>>> I would like to transform my daily data to monthly data, calculated on
>>>> the 15th of each month, more precisely, from the 16th day of a
>>>> specific month up to and including the 15th day of the following
>>>> month. The problem is that I can only find the way to this it based on
>>>> 'regular' months. This can be done by using the following commands:
>>>>
>>>> g dm=mofd(date)
>>>> format dm %tm
>>>> collapse  prc ret shrout vwretd ewretd, by (dm)
>>>> tsset dm, monthly
>>>>
>>>> Since I'm having inflation dates on the 15th each month I would like
>>>> to have the monthly data on the 15th as well, otherwise there will be
>>>> a mismatch in my data. The data on my dates looks like below:
>>>>
>>>> 16dec2002
>>>> 17dec2002
>>>> 18dec2002
>>>> 19dec2002
>>>> 20dec2002
>>>> 23dec2002
>>>> 24dec2002
>>>> 26dec2002
>>>> 27dec2002
>>>> 30dec2002
>>>> 31dec2002
>>>> 02jan2003
>>>> 03jan2003
>>>> 06jan2003
>>>> 07jan2003
>>>> 08jan2003
>>>> 09jan2003
>>>> 10jan2003
>>>> 13jan2003
>>>> 14jan2003
>>>> 15jan2003
>>>> 16jan2003
>>>> 17jan2003
>>>> 21jan2003
>>>> 22jan2003
>>>> 23jan2003
>>>> 24jan2003
>>>> 27jan2003
>>>> 28jan2003
>>>>
> *
> *   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/