Convert Quarterly data From Compustat in Monthly data

[Massimo Coletta]

Hi everyone,

I have a problem and I need your help to solve it. I have Quarterly firm accounting data from Compustat that I would convert in monthly data, but everything I tried until now didn't work.
The main goal should be have within each quarter two more data for the relative months that belong to it (fro instance, for the quarter 31mar1994, I would have the same data for the previous month of January and February as a proxy). Mine its a Panel data, so I need to do it for every firm I have in my sample. Each firm is identified by the variable GVKEY.
I need to this because I have to merge this dataset with another one from CRSP that have monthly data.
I attached a photo of both dataset hoping that could make everything more clear (first Quarterly and second monthly).
I would be very very grateful to everyone of you that will help me.

Massimo

[Nick Cox]

I'd merge the other way: use qofd(dofm()) to get quarterly dates for the monthly dataset and then merge that with the quarterly dataset.

Please do read the advice in the FAQ #12 that screenshot photos are to be avoided. On my machine yours are unreadable even if I click on them. I certainly can't copy and paste them to use as a sandbox.

[Massimo Coletta]

Hi Nick, thank you for your answer, and forgive me if I have posted the screenshots.
Anyway, my goal it's the opposite, I need to get monthly data from quarterly.
As I said above, I have a variable GVKEY that identify each firm, and the date is in this format, 31Jan1994. I would have for each observation two more identical as a proxy for the other two months of each quarter.
I run this code:
bysort gvkey cusip datadate: gen yrm = mofd(dofq(datadate)) + _n - 1 but it turns me out just a variable "yrm" with number, and no new observations.

So my firt dataset (Quarterly from Compustat) is have three main variables:
gvkey: Compustat code that identify each firm;
cusip: a specific number assigned to each firm, that I will use to merge the dataset
and time variable "datadate" in this format: 31Jan1994, 31Apr1994, 31Jul1994... also the quarter is not the same for all the firms, I mean some firms end their quarters in different months.

The second dataset from CRSP has monthly data.

How I can do that?

Thanks so much in advance.

[Nick Cox]

I think you miss my point. Either way, what you tried is not what I suggested at all.

You need to merge two data files. In practice you should do that the easiest way.

Here is proof of concept. It is a self-contained example you can run and study. The principle is to use a quarterly date to match in a merge.

clear 
input month year stuff1 
10 2015 12
11 2015 34
12 2015 56
end 
gen mdate = ym(year, month) 
gen qdate = qofd(dofm(mdate)) 
format mdate %tm 
format qdate %tq 
list mdate qdate stuff1 
save nov5data, replace 

clear 
input quarter year stuff2 
4 2015 78 
end 

gen qdate = yq(year, quarter) 
format qdate %tq 

list 

merge 1:m qdate using nov5data 

list

I can't use your data examples, as you don't give any.

[Massimo Coletta]

This is the dataset with quarterly data:


+-----------------------------------------------------------+
| ID datadate atq prccq |
|-----------------------------------------------------------|
1. | 1 28feb1994 406.572 15.87499809 |
2. | 1 31may1994 417.626 14.37499809 |
3. | 1 31aug1994 410.644 13.49999857 |
4. | 1 30nov1994 413.529 13.12499857 |
5. | 1 28feb1995 425.363 13.74999905 |
|-----------------------------------------------------------|
6. | 1 31may1995 425.814 15.24999905 |
7. | 1 31aug1995 411.362 16.62499809 |
8. | 1 30nov1995 421.45 18.37499857 |
9. | 1 29feb1996 429.271 19.62499809 |
10. | 1 31may1996 437.846 22.12499857 |
|-----------------------------------------------------------|
11. | 1 31aug1996 449.645 21.62499762 |
12. | 1 30nov1996 468.55 29.87498474 |
13. | 1 28feb1997 523.852 25.49997711 |
14. | 1 31may1997 529.584 30.99998474 |
15. | 1 31aug1997 542.819 33.56197357 |
|-----------------------------------------------------------|
16. | 1 30nov1997 587.136 38.56198883 |
17. | 1 28feb1998 662.345 30.375 |
18. | 1 31may1998 670.559 26.4375 |
19. | 1 31aug1998 707.6950000000001 22.125 |
20. | 1 30nov1998 737.4160000000001 25.25 |
|-----------------------------------------------------------|
21. | 1 28feb1999 708.218 15.125 |
22. | 1 31may1999 726.63 19.75 |
23. | 1 31aug1999 718.913 21.375 |
24. | 1 30nov1999 747.043 16.5 |
25. | 1 29feb2000 753.755 23.75 |
|-----------------------------------------------------------|



and this one with monthly data


+--------------------------------+
| ID1 date ret |
|--------------------------------|
1. | 1 31jan1994 -.047619049 |
2. | 1 28feb1994 0 |
3. | 1 31mar1994 -.004285715 |
4. | 1 29apr1994 -.144927531 |
5. | 1 31may1994 .06779661 |
|--------------------------------|
6. | 1 30jun1994 .107301585 |
7. | 1 29jul1994 .072463766 |
8. | 1 31aug1994 -.027027028 |
9. | 1 30sep1994 .038333334 |
10. | 1 31oct1994 -.054054055 |
|--------------------------------|
11. | 1 30nov1994 -.042857144 |
12. | 1 30dec1994 -.033432838 |
13. | 1 31jan1995 -.03125 |
14. | 1 28feb1995 -.026209677 |
15. | 1 31mar1995 .006376811 |
|--------------------------------|
16. | 1 28apr1995 0 |
17. | 1 31may1995 .050000001 |
18. | 1 30jun1995 .06031746 |
19. | 1 31jul1995 0 |
20. | 1 31aug1995 -.030303031 |
|--------------------------------|
21. | 1 29sep1995 .043749999 |
22. | 1 31oct1995 -.030303031 |
23. | 1 30nov1995 .09375 |
24. | 1 29dec1995 .082857139 |
25. | 1 31jan1996 -.026666667 |
|--------------------------------|


where ID and ID1 identify each firm respectively in the fist and the second dataset

[Phil Bromiley]

I strongly agree with Nick that making monthly data from quarterly is problematic - it creates serious statistical problems.

However, you asked a technical question, so let me suggest how you can do it.

Simply create a quarter variable in your monthly data set and then merge by firm and quarter. You will need a k:1 merge that allows the quarterly to be written across multiple observations in the monthly. You'll need to correct for the fact that some accounting values are stocks reported with end of period values (like assets) and some are flows reported as the sum of actions over a period (like sales). The total assets in the monthly data should look at lot like the assets in the quarterly data, but the sales should only be 1/3 as large.

However, if you are really committed to this approach, you might want to consider interpolating between the values. Some accounting variables are end of period. In such a case, interpolating between quarters might be a more legitimate indicator of monthly values. It is a bit more complex with flow variables (e.g., sales), but might still be worth considering.

[Nick Cox]

For once I did not comment on the difficulties of principle but Phil's warnings are clearly pertinent.

His advice to create a quarterly date echoes precisely what was done in #4. It's for Massimo to implement it, extended to identifier as well as date.