Transform Quarterly data to Monthly Data for an event study

[Markus Kitsing]

Hello Everyone!

First, I would like to thank whoever made the forum page and I am excited to be part of the community. I hope that my question is not too elementary and the format of my question is understandable.

I am a masters student studying Financial Management and I am currently writing my thesis using an event study methodology. I need to merge 2 datasets, 1 is monthly stock data and another that is quarterly reported financial data. My supervisor told me to convert the financial data into monthly but I am having major issues in stata with this.

I must convert it such that each quarter's data turns into following 3 months data. (ie. Quarter reported date = following 3 months after reported date, deleting the initial date it was reported). Since not all firms have the same end dates for quarters, it has become rather confusing on how to convert the data (example: I cannot use a quarterly variable and duplicate such that Q1 = April May June, since some firms report Q1 in April....)

My quarterly data has a variable 'date_td' in MMDDYYYY format.

I have been running in circles for 10+ hours, and chatgpt/google/internet/statahelp is no help. The closest I have gotten is to duplicate the dates but they do not come out properly (see below)

Happy to provide more information if needed.

Thanks for any help in advance!

Markus

The date format before i try to convert is the following:

date_td
1/31/2010
4/30/2010
7/31/2010
10/31/2010

When I attempt to convert it to Quarterly it duplicates but does not change the dates. It becomes this(see code after the dates):

date_td
31jan2010
31jan2010
31jan2010
30apr2010
30apr2010
30apr2010
31jul2010
31jul2010
31jul2010
31oct2010
31oct2010
31oct2010

The code i used is the following:

///turn QDATE from Quarterly into Monthly

// Convert MMDDYYYY dates to Stata's date format
format date_td %td
gen Quarter_End = qofd(date_td)

//Create a unique identifier for each quarter
sort Quarter_End
gen Quarter_ID = _n

//Expand quarterly data to monthly data by repeating each quarterly row for the next three months
expand 3
sort Quarter_ID
by Quarter_ID: gen Month = _n

// Generate the date variable for each month
gen Date_Monthly = mofd(Quarter_End - 1) + (Month - 1)

sort GVKEY date_td

[Nick Cox]

Cross-posted at https://www.reddit.com/r/stata/comme...y_data_for_an/

It's a rule (their word) on Reddit that you tell people about cross-posting and a request (our word) here that you do so.

There are answers on Reddit so at this point the best advice is to tell people in both places if you've not yet solved your problem.

[Markus Kitsing]

Hello!!

I got some help but uncertain on how to apply it. Was told on on Reddit to try this code:
gen mdate = mofd(date_td) tsset mdate, monthly tsfill Where would this be added in?

[Nick Cox]

I imagine the implication was

* Example generated by -dataex-. For more info, type help dataex
clear
input float date_td
18293
18382
18474
18566
end
format %td date_td

gen mdate = mofd(date_td)
format %tm mdate 
tsset mdate 
tsfill 

list 

     +---------------------+
     |   date_td     mdate |
     |---------------------|
  1. | 31jan2010    2010m1 |
  2. |         .    2010m2 |
  3. |         .    2010m3 |
  4. | 30apr2010    2010m4 |
  5. |         .    2010m5 |
     |---------------------|
  6. |         .    2010m6 |
  7. | 31jul2010    2010m7 |
  8. |         .    2010m8 |
  9. |         .    2010m9 |
 10. | 31oct2010   2010m10 |
     +---------------------+

Whether that's the right answer and what else you need to do I can't really tell you, but https://journals.sagepub.com/doi/pdf...6867X231196519 may help.