Exercise 1: The day of the week effect

One of the famous calendar anomalies is the weekend effect, which refers to the surprisingly low (and in fact negative) average returns earned on Mondays relative to the other days of the week. The original study on this used data on NYSE stocks for the period 1928-1982 (Donald B. Keim and Robert F. Stambaugh, 1984, A Further Investigation of the Weekend Effect in Stock Returns, Journal of Finance 39, pp. 819-835).

In this exercise, you will update this analysis using returns on the S&P 500 index for the period January 3, 1950 to the present. You will learn how to obtain this type of data yourself from Yahoo! Finance.

a. Go to the Yahoo! Finance website and download historical closing levels on the S&P 500 index from January 3, 1950 to the present. In the document you submit, please include a partial screen shot of the spreadsheet.

Hint: Go to http://finance.yahoo.com/ and search ^GSPC. This will open a webpage that shows you the recent performance of the index, its current level, the 52-week range, etc. Towards the middle of the page, click on Historical Data, enter 1/3/1950 as the start date and yesterday as the end date, click Apply and then Download Data. This will give you a csv file, which you can open in Excel. Delete all the columns but A and F, since you only need the date and the adjusted close. Finally, sort the observations from oldest to newest, so that the first observation is 1/3/1950, then 1/4/1950, etc.

b. Compute daily returns on the index. In the document you submit, please include a partial screen shot of the spreadsheet showing the result and the equations used.

Hint: the first return you will compute is the 1/4/1950 return which can be calculated as (1/4/1950 price 1/3/1950 price) / (1/3/1950 price).

c. For each date, determine the day of the week. In the document you submit, please include a partial screen shot of the spreadsheet showing the result and the equations used.

Hint: use the Excel function WEEKDAY. Google it if you need help with it. Note that =WEEKDAY(cell_reference)-1 will produce a 1 for Monday, 2 for Tuesday, etc.

d. Compute the average return on the index separately for each day of the week. In the document you submit, please explain the approach you followed, and include a partial screen shot of the spreadsheet showing the result and the equations used.

Hint: Think about a logical way to set this up in Excel so that you do not need to do any calculations by hand, since you have thousands of observations. There are several ways of doing this. For example, using IF statements (Google this too if needed) you can create dummy variables that equal 1 on Monday and 0 on all other days. You can do this for each weekday. Doing this will allow you to create 5 columns, one per day, where in each column the entry is the return on the corresponding day and zero otherwise.

e. Present the result in a graph showing the average return by day of the week.

f. Can you describe the pattern? Why is it interesting/surprising?