How to convert annual data to quarterly data in stata

All material on this site has been provided by the respective publishers and authors. You can help correct errors and omissions. When requesting a correction, please mention this item's handle: RePEc:boc:bocode:s412101. See general information about how to correct material in RePEc.

For technical questions regarding this item, or to correct its authors, title, abstract, bibliographic or download information, contact: . General contact details of provider: https://edirc.repec.org/data/debocus.html .

If you have authored this item and are not yet registered with RePEc, we encourage you to do it here. This allows to link your profile to this item. It also allows you to accept potential citations to this item that we are uncertain about.

We have no bibliographic references for this item. You can help adding them by using this form .

If you know of missing items citing this one, you can help us creating those links by adding the relevant references in the same way as above, for each refering item. If you are a registered author of this item, you may also want to check the "citations" tab in your RePEc Author Service profile, as there may be some citations waiting for confirmation.

For technical questions regarding this item, or to correct its authors, title, abstract, bibliographic or download information, contact: Christopher F Baum (email available below). General contact details of provider: https://edirc.repec.org/data/debocus.html .

Please note that corrections may take a couple of weeks to filter through the various RePEc services.

It is pretty easy to convert your data from daily frequency to weekly, monthly, quarterly, or yearly frequency. We can use the Stata built-in collapse function after creating period identifiers. Alternatively, we can use the ascol program that I have written. ascol makes it pretty simple to convert stock returns or prices data from daily to weekly, monthly, quarterly, or yearly frequency. Since returns and prices need different treatments for conversion (returns need to be summed for their conversion from daily to say weekly frequency, while in case of prices, the conversion will just results in producing end of the week prices.), the program gives us both the option of converting returns and prices data.

This program requires data to be a panel data or time series series data. This program can be downloaded from SSC by typing:

ssc install ascol

For Stata 13, the program can downloaded using net command:

net from "https://sites.google.com/site/imspeshawar"

The program requires the data to be declared as panel data

Syntax

ascol varlist , return price [frequency options]

options

return

This option tells the program that the data is stock returns data. Since stock returns are already expressed in percentage change form, the collapse treatement would be to sum these returns within the specified time interval.

price

Alternatively, users can specify that the data in memory is share prices data using option price. The return and price cannot be combined together. To collapse prices to desired frequency, the program finds the last traded prices of the period which the users specify.

Let us use some examples to understand how the program works

In all of the following examples, I assume that we have data or returns (named as ri) and prices (named as pr)

Let us first generate some examples data for practice.

-----------------------------------------------------+

set obs 1000 |

gen date=date("1/1/2012" , "DMY")+_n |

format %td date |

gen pr=10 |

replace pr=pr[_n-1]+uniform() if _n>1 |

gen ri=(pr/l.pr)-1 |

save stocks,replace |

---------------------------------------+

From Daily to weekly –returns (adsbygoogle = window.adsbygoogle || []).push({});

----------------------------------+

use stocks, clear |

ascol returns, toweek returns |

|

OR |

ascol ri, tow r |

--------------------------------------------- +

ascol is the program name, ri is the variable name in our data set, toweek is the program option that tells Stata that we want to convert the daily data to weakly frequency, and the return option tells Stata that our ri variable is return (i.e. already converted from prices into periodic returns)

From Daily to weekly – Prices

----------------------------------+

use stocks, clear |

ascol returns, toweek prices |

|

OR |

ascol ri, tow p |

--------------------------------------------- +

ascol can also be used similarly as in the above examples to convert from daily to monthly, quarterly, and yearly frequency. The options to be used in each case are given below;

STATA: Time series data

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

Suppose we have annual data on variable GDP and we want to compute lagged GDP, the annual change in GDP and the annual percentage change in GDP.
One way to compute these is to note that _n denotes the current observation number, so _n-1 denotes the previous observation number.
Then
   generate GDPlag = GDP[_n-1]                           constructs the lagged value of GDP, i.e. the value last year
   generate GDPchange = GDP[_n] - GDP[_n-1]   constructs the change in GDP
   generate GDPgrowth = 100*(GDP[_n] - GDP[_n-1]) / GDP[_n-1]  constructs the annual percentage change in GDP
Note the formatting - we use square brackets [ ] and _n  is underscore n
Also note that the first observation for GDPlag, GDPchange and GDPgrowth will be missing since there is no observation zero.
For quarterly data if we wanted the year-on-year percentage change, for example, we give command
   generate GDPgrowth = 100*(GDP[_n] - GDP[_n-4]) / GDP[_n-4]

LAGS AND CHANGES IN STATA FOLLOWING TSSET

Suppose the dataset has a variable year that takes numeric values, say, 1985, 1986, 1987, ....
Then we can use command tsset to set a time variable to year and then use Stata time series operators and commands.
Then
  tsset year                                                           sets year as the time variable
  generate GDPlag = l.GDP                               constructs the lagged value of GDP, i.e. the value last year
  generate GDPchange = GDP - l.GDP               constructs the change in GDP
  generate GDPchange = (GDP - l.GDP) / l.GDP    constructs the annual percentage change in GDP
Note that here l. is the letter "el" and stands for lag.
Instead of GDP - l.GDP we could use d.GDP where the letter d stands for difference.
For quarterly data if we wanted the year-on-year percentage change, for example, we give command
   generate GDPgrowth = 100*(GDP - l4.GDP) / l4.GDP
A time series graph of GDP can be produced using the command
  tsline GDP

CONVERTING STRING DATES TO A NUMERIC DATE - DIFFICULT

Dates are often given in data sets as string variables  e.g. "February 1, 1960 "  or  "2/1/1960"
In order to use Stata time series commands and tsset this needs to be converted to a number that Stat understands.
And then to have nice output for graphs this number in turn needs to be given a date format.

As an example,  suppose we have string variable named date formatted as e.g. "2/1/1960"
(1) Convert to a number using the date( ) function
   generate date2 = date(date, "MDY")      here MDY as the date string variable was ordered month, day, year
This yields a number that is the number of days since 1/1/1960  e.g. 2/1/1960 yields 31.
Note that date appears twice - the first is the date function and the second because our variable happened to be called date.
(2) Since we have monthly data convert this to the number of months since 1960.
   generate date3 = mofd(date2)
(3) date3 can be used immediately in a tsset command, but for proper dates to appear on graphs we should give a date format.
Here date3 is months since 1960 so we use the %tm format for monthly data
    format %tm date3
(4) Now give commands tsset date3   etcetera

Note that the particulars for steps (1) - (3) will change according to whether your data is daily, weekly, monthly, quarterly, yearly, ..... and the exact way that they appear in the original data e.g. "February 1, 1960 "  or  "2/1/1960".
The Stata video https://www.youtube.com/watch?v=SOQvXICIRNY is very useful.
For details see the Stata PDF documentation on Date and Time Functions which you can link to following command help date. 

For further information on how to use Stata go to
   http://www.econ.ucdavis.edu/faculty/cameron

How to convert monthly data to quarterly data in Stata?

How do I convert yearly data to quarterly data in Excel?