Homework 3, by Ao Luo, Jan 31, 2018

Executive Summary

This homework is focused on the collection of annual data for all stocks. The data sets come from WRDS. Given the monthly data for PERMNO, tickers, cusip, price, shares outstanding and holding period return, we calculate the annualized marketcap and rate of return. Measurement error due to bid/ask gap, sample selection, and treatment of missing data for stocks with short history may affect the conclusion.

Introduction & Discussion

Data Set

The WRDS monthly data for all stocks contains the holding period return including dividend, PERMNO code and cusip for each ticker, the price and shares outstanding from 1973 through 2016.

Data Processing

First we transform the price data. The negative price that comes from the average of bid/ask needs to be transformed to absolute values. Then the marketcap is calculated by multiplying the price with number of outstanding shares. Finally the year number is extracted from the date.

Since the ticker may change with time, the one we choose for annual data will be the latest ticker the company uses in the respective year. As permno and cusip are fixed for all the stocks, we group them by year, perm and cusip into new dataset.

Methodology

R Package “data.table” is used to process the data. The data visualization is accomplished through ggplot2 and htmlTable.

Performance Analysis

For illustration purpose, we draw the first 4 stocks out from the stock universe that has more than 20-year history. We need to notice that the annual rate of return only represent the data we have in the respective year. For beginning year and ending year, the annual rate of return may not be accurate.

And the showcase of the annual data can be seen in the figures below. The table for the above 4 stocks are also attached to illustrate the data structure. The Rdata for all stocks is stored online and you can access it from HERE.

Tables and Figures

Tables for Selected 4 Stocks

Plot of Marketcap and Rate of Return for Selected 4 Stocks

Computer Code

  # loading libraries
  library(xts)
  library(zoo)
  library(magrittr)
  library(data.table)
  library(tidyr)
  library(lubridate)
  
  # load raw data
  dset_data <- fread("./data/monthlydata.csv")
  colnames(dset_data) %<>% tolower
  
  # read from raw data
  test <- dset_data
  test[, date := as.Date(as.character(date), format = "%Y%m%d") ]
  test[, adjprc := abs(prc)]
  test[, mkc := adjprc * shrout]
  test[, ret := as.numeric(ret)]
  test[, test[,is.na(ret)]] = 0
  test[, year := year(date)]
  # calculate the year-end marketcap, ticker, and annualized rate of return
  mdata <- test[, 
       .(prod(na.omit(ret) + 1)-1, tail(na.omit(mkc), n=1), tail(unique(ticker), n=1)),
       by = .(year, permno, cusip)]
  colnames(mdata) <- c('year', 'perm', 'cusip', 'rate of return', 'marketcap', 'ticker')
  
  save(mdata, file="./annualData.RData")