Mingze Gao

Compute Weekly Return from Daily CRSP Data

| 7 min read

Computing the weekly returns from the CRSP daily stock data is a common task but may be tricky sometimes. Let's discuss a few different ways to get it done incorrectly and correctly.

INCORRECT ways

Let me start with a few incorrect ways, which may seem perfectly okay at first glance. This part is important because it shows you how a small mistake can lead to hard-to-discover bugs.

Weekly index return from daily data

Date as the Friday of the week

Using intnx(), we can derive the Friday of the week given a date, as shown below.

proc sql;
/* Compute weekly marekt return from daily data */
create table mktret_weekly as
select distinct date,
	year(date) as Year,
	week(date) as Week,
	case when weekday(date)=6 then date
	else intnx("week.6",date,1) end as FridayOfWeek format=date9.,
	(exp(sum(log(1+sprtrn)))-1)*100 as mktret label="Weekly SP500 Index Return (%)"
from crsp.dsi
where
	year(date) between &startyear. and &endyear.
group by year(date), week(date) order by date;
quit;

Note that intnx("weekday.6", date, 0) will give the last Friday, which is not what we want. We want the next Friday of the week for a given date, so we use intnx("weekday.6", date, 1). The case...when... statement ensures that if the given date is already a Friday, we don't go for the next one.

Below is a sample output of the mktret_weekly table generated.

ObsDateYearWeekFridayOfWeekmktret
1198601021986003JAN1986-0.1893222
2198601031986003JAN1986-0.1893222
3198601061986110JAN1986-2.333080418
4198601071986110JAN1986-2.333080418
5198601081986110JAN1986-2.333080418
6198601091986110JAN1986-2.333080418
7198601101986110JAN1986-2.333080418
8198601131986217JAN19861.1992620931
9198601141986217JAN19861.1992620931

We can verify that the FridayOfWeek indeed gives the Friday of the week. Therefore, we modify the code above to produce the final weekly dataset using Friday as the date identifier by keeping FridayOfWeek and mktret.

proc sql;
/* Compute weekly marekt return from daily data */
create table mktret_weekly as
select distinct
	case when weekday(date)=6 then date else intnx("week.6",date,1) end
		as date format=date9. label="Friday of the Week",
	(exp(sum(log(1+sprtrn)))-1)*100
		as mktret label="Weekly SP500 Index Return (%)"
from crsp.dsi
where
	year(date) between &startyear. and &endyear.
group by year(date), week(date) order by date;
quit;

Below is a sample output of the final mktret_weekly table generated.

Obsdatemktret
103JAN1986-0.1893222
210JAN1986-2.333080418
317JAN19861.1992620931
424JAN1986-0.959555101
531JAN19862.5916781551
607FEB19861.3126828796

... sidetrack, date as the last trading day of the week

%let startyear=1986;
%let endyear=2019;

proc sql;
/* Compute weekly marekt return from daily data */
create table mktret_weekly as
select distinct date,
	(exp(sum(log(1+sprtrn)))-1)*100 as mktret label="Weekly SP500 Index Return (%)"
from crsp.dsi where year(date) between &startyear. and &endyear.
group by year(date), week(date)
having date=max(date)
order by date;
quit;

Note here that it's tempting to use having weekday(date)=6 to make sure the dates are all Friday. However, if Friday in a week is not the last trading day, then the weekly return will be missing. This is why here I use date=max(date) to ensure non-missing weekly returns. The date is the last trading day in any given week, consistent with the CRSP's daily stock file.

The caveat here is that since the dates are the weekly last trading days, when merged with other weekly datasets, you should be very careful about whether the other dataset is using Friday or the last trading day per week as its date variable.

Weekly stock return from daily data

Following the same logic, we can calculate the weekly stock returns from daily CRSP data, where dates are aligned to the Friday of the week.

proc sql;
/* Stocks (ordinary shares only) in the financial sector */
create table stocks as select distinct permno from crsp.stocknames
where shrcd in (10, 11) and floor(siccd/100) between 60 and 67;

create table stockrets_weekly as
select distinct permno,
	case when weekday(date)=6 then date else intnx("week.6",date,1) end
		as date format=date9. label="Friday of the Week",
	(exp(sum(log(1+ret)))-1)*100 as ret label="Weekly Return (%)"
from crsp.dsf
where
	year(date) between &startyear. and &endyear.
	and permno in (select * from stocks)
	and prc>0 and not missing(ret)
group by year(date), week(date), permno order by permno, date;
quit;

What's wrong?

The code above seems okay. We know that CRSP daily stock file contains many observations where the daily trading volume is 0, in which case the price is recorded as the negative bid-ask midpoint. Therefore, we restrict to only those with positive stock prices. So what's the problem?

For example, check out the last week of 2019:

MonTueWedThuFriSatSun
303112345

Now we have a mistake. A single week is broken into two because of the use of week() function in SAS. Another consequence is that when there're many years of data, there will be a lot of duplicates.

CORRECT ways

Now let's explore two ways that avoid this mistake. Although both generate the same result (there can be a few differences, see the caveat), the second one is much faster.

1. Start with a list of dates (slow version)

Now we can write some correct code to compute the weekly returns. We'll generate a series of Fridays first, then we merge based on the past 5 calendar days. This will ensure all trading days with non-missing data will be included in the weekly return calculation, and correct the mistake mentioned above.

%let start_date	= 01Jan1986;
%let end_date	= 31Dec2019;

/* Generate a series of Fridays */
data fridays;
date="&start_date"d;
do while (date<="&end_date"d);
    if weekday(date)=6 then output;
    date=intnx('day', date, 1, 's');
end;
format date date9.;
run;

Weekly index return from daily data (as at Friday)

proc sql;
/* Compute weekly index return from daily data */
create table mktret_weekly as
select distinct a.date,
	(exp(sum(log(1+sprtrn)))-1)*100
		as mktret label="Weekly SP500 Index Return (%)"
from fridays as a left join crsp.dsi as dsi
on dsi.date between intnx('day', a.date, -4) and a.date
group by a.date
order by a.date;
quit;

Weekly stock return from daily data (as at Friday)

Note that this version is inefficient and takes a long time to run.

proc sql;
/* Stocks (ordinary shares) in the financial sector (2-digit SIC=60-67) */
create table stocks as select distinct permno from crsp.stocknames
where shrcd in (10, 11) and floor(siccd/100) between 60 and 67;

/* Compute weekly stock return from daily data */
create table stockrets_weekly as
select distinct a.date, dsf.permno, dsf.hsiccd,
	(exp(sum(log(1+ret)))-1)*100 as ret label="Weekly Return (%)"
from fridays as a left join crsp.dsf as dsf
on dsf.date between intnx('day', a.date, -4) and a.date
	and dsf.permno in (select * from stocks)
	and dsf.prc>0 and not missing(dsf.ret)
group by dsf.permno, a.date
order by dsf.permno, a.date;
quit;

2. Group using aligned dates (fast version with caveat)

This version uses a similar logic from the previous incorrect one, but it groups based on the aligned dates instead of year(date) and week(date).

proc sql;
/* Compute weekly stock return from daily data */
create table stockrets_weekly2 as
select distinct permno, hsiccd,
	case when weekday(date)=6 then date else intnx("week.6",date,1) end
		as date format=date9. label="Friday of the Week",
	(exp(sum(log(1+ret)))-1)*100 as ret label="Weekly Return (%)"
from crsp.dsf (keep=permno date ret prc shrout hsiccd)
where
	date between "01Jan1986"d and "31Dec2019"d
	and permno in (select * from stocks)
	and prc>0 and not missing(ret)
group by permno, calculated date order by permno, date;
quit;