From list of daily closing stock prices, how do I extract monthly and annual closing stock prices

[Carl Berthold]

Total STATA newbie here, so this is likely a dumb question. If so, apologies in advance.

I have a dataset with daily closing stock prices, and want to convert this to two datasets with monthly and annual closing stock prices respectively. How do I do that?

Any help & pointers would be much appreciated.

[Attaullah Shah]

I have written a program for this purpose. The program help file and code are attached.

here is the help file

Title

ascol - Collapse stock returns and stock prices from
daily to weekly, monthly, quarterly, and yearly frequency


Syntax

ascol varlist , rturn 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.


Frequency Options


This program convert stock returns and stock prices data from
daily to weekly, monthly, quarterly, or yearly frequency using
the following options;

toweekly() converts from daily to weekly frequency
tomonth() converts from daily to monthly frequency
toquarter() converts from daily to quarterly frequency
toyear() converts from daily to yearly frequency



Example Data Set

clear
set obs 1000
gen date=date("1/1/2012" , "DMY")+_n
format %td date
tsset date
gen pr=10
replace pr=pr[_n-1]+uniform() if _n>1
gen ri=(pr/l.pr)-1
save stocks,replace

Example 1: From Daily to weekly - returns

. use stocks, clear

. ascol ri, toweek returns

OR

. ascol ri, tow r

ascol is the program name, ri is the stock return variable in our data set,
toweek is the program option that tells Stata that we want to convert daily
data to weakly frequency, and the returns option tells Stata that our ri variable
is stock returns.

Example 2: From Daily to monthly - prices

. use stocks, clear

. ascol pr, tomonth price

OR

. ascol pr, tow p

pr is the stock prices variable in our data set, tomonth option tells
Stata that we want to convert daily share prices
to monhtly frequency, and the price option tells Stata that our pr variable
is stock price.


Converting Data to Other Frequencies


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;

From daily to monthly, option tomonth or tom is to be used
From daily to quarterly, option toquarter or {opt} toq is to be used
From daily to yearly, option toyear or toy is to be used


Author


:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::....................... ....
* *
* Dr. Attaullah Shah *
* Institute of Management Sciences, Peshawar, Pakistan *
* Email: [email protected] *
* See my webpage for more programs and help at: *
* http://www.OpenDoors.Pk/STATA
*::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::::::::::::::::::...................... ......

[Nick Cox]

Closing prices are presumably the last price in each month or year. If so, then given variables say price and dailydate, the first reduction is

gen monthdate = mofd(dailydate) 
bysort monthdate  (dailydate) : keep if _n == _N 
save monthlydata

and the second reduction is then

gen yeardate = year(dailydate) 
bysort yeardate (monthdate) : keep if _n == _N 
save yearlydata

No doubt Attaullah's program adds further features to this minimal outline. The key point here is that this reduction is an application of by: