# 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

 year perm cusip rate of return marketcap ticker PERMNO 10001 1 1985 10001 36720410 0 2 1986 10001 36720410 0.217369077869878 6937 GFGC 3 1987 10001 36720410 -0.101275120033897 5828 GFGC 4 1988 10001 36720410 0.163160192505212 6362.25 GFGC 5 1989 10001 36720410 0.687925758196561 10347.75 GFGC 6 1990 10001 36720410 -0.00872116396250855 10013 GFGC 7 1991 10001 36720410 0.607470828774737 15587.5 GFGC 8 1992 10001 36720410 0.0126212448615792 15120 GFGC 9 1993 10001 36720410 0.367996674792745 20047.125 EWST 10 1994 10001 36720410 -0.0909283653187304 17792 EWST 11 1995 10001 36720410 0.228628312761812 21384.375 EWST 12 1996 10001 36720410 -0.0904477753414937 19150.625 EWST 13 1997 10001 36720410 0.164265312070448 21555 EWST 14 1998 10001 36720410 0.124245382179036 23302.125 EWST 15 1999 10001 36720410 -0.0662204039996153 20825 EWST 16 2000 10001 36720410 0.21472490005584 24355.5 EWST 17 2001 10001 36720410 0.229015048379317 29380.7 EWST 18 2002 10001 36720410 -0.318478502115107 19046.441 EWST 19 2003 10001 36720410 -0.176303537283407 15446.2 EWST 20 2004 10001 36720410 0.159663200958041 17933.1 EWST 21 2005 10001 36720410 0.411362899281196 28421 EWST 22 2006 10001 36720410 0.181931701624285 32844.9 EWST 23 2007 10001 36720410 0.334089491911573 40652.5 EWST 24 2008 10001 36720410 -0.0782042063757472 35493.22 EWST 25 2009 10001 36720410 0.324435340774233 44918.3 EGAS 26 2010 10001 36720410 0.0739937152689896 82413.68 EGAS 27 2011 10001 36720410 0.139163138738249 93118.68 EGAS 28 2012 10001 36720410 -0.139379698570181 76104.81 EGAS 29 2013 10001 36720410 -0.090693841501766 83929.56 EGAS 30 2014 10001 36720410 0.442748934495806 115577.76 EGAS 31 2015 10001 36720410 -0.282165270445762 78262.25 EGAS 32 2016 10001 36720410 0.740636606645356 132026 EGAS
 year perm cusip rate of return marketcap ticker PERMNO 10002 1 1985 10002 05978R10 0 2 1986 10002 05978R10 0.0645152560658284 14540.625 MBNC 3 1987 10002 05978R10 -0.191918257818693 11750 MBNC 4 1988 10002 05978R10 -0.16250041956534 9840.625 MBNC 5 1989 10002 05978R10 -0.268657418139746 7196.875 MBNC 6 1990 10002 05978R10 -0.183673167216774 5875 MBNC 7 1991 10002 05978R10 0.200000274864854 7050 MBNC 8 1992 10002 05978R10 0.499999482705257 12978 MBNC 9 1993 10002 05978R10 0.450352778329701 38612.125 SABC 10 1994 10002 05978R10 0.0394734639437373 39361.875 SABC 11 1995 10002 05978R10 0.0921287627351954 42028 SABC 12 1996 10002 05978R10 -0.0440776920193253 39026 SABC 13 1997 10002 05978R10 0.962960322570469 104027 SABC 14 1998 10002 05978R10 -0.0481709062204361 117867.25 SABC 15 1999 10002 05978R10 -0.179592566891403 94197.1875 SABC 16 2000 10002 05978R10 -0.212422789534577 78332.625 SABC 17 2001 10002 05978R10 0.119580916310851 84361.7 SABC 18 2002 10002 05978R10 0.171631590246135 96891.9 BTFG 19 2003 10002 05978R10 0.492715251900591 140094.9 BTFG 20 2004 10002 05978R10 0.578180095814652 271226.81 BTFG 21 2005 10002 05978R10 -0.161573788664541 223853.7 BTFG 22 2006 10002 05978R10 0.296736698637352 284956.32 BTFG 23 2007 10002 05978R10 -0.510884822385367 211713.7 BTFG 24 2008 10002 05978R10 0.28261156406498 259111.8 BTFG 25 2009 10002 05978R10 -0.804133121910148 50609.58 BTFG 26 2010 10002 05978R10 -0.0696870714396928 47096.13 BTFG 27 2011 10002 05978R10 -0.535581071802503 22280.32 BTFG 28 2012 10002 05978R10 1.24193909157905 49945.48 BTFG 29 2013 10002 05978R10 0.035971 51742.08 BTFG
 year perm cusip rate of return marketcap ticker PERMNO 10025 1 1985 10025 00103110 0 2 1986 10025 00103110 -0.390476318587977 24048 AEPI 3 1987 10025 00103110 0.031250370339464 24816 AEPI 4 1988 10025 00103110 0.545455335837722 38517.75 AEPI 5 1989 10025 00103110 0.749999505996598 70373.625 AEPI 6 1990 10025 00103110 -0.470587726502864 37603.125 AEPI 7 1991 10025 00103110 0.777775737240905 67424 AEPI 8 1992 10025 00103110 -0.10714137336104 60462.5 AEPI 9 1993 10025 00103110 0.466366057807737 88932.25 AEPI 10 1994 10025 00103110 0.517134328433825 135387 AEPI 11 1995 10025 00103110 0.201461408771345 105710 AEPI 12 1996 10025 00103110 1.49999887110969 392150 AEPI 13 1997 10025 00103110 -0.438637176000102 222516.125 AEPI 14 1998 10025 00103110 -0.295546089211003 158296.5 AEPI 15 1999 10025 00103110 0.183907482503174 192095 AEPI 16 2000 10025 00103110 0.73543686758457 340116.5625 AEPI 17 2001 10025 00103110 -0.463833017467369 186744.24 AEPI 18 2002 10025 00103110 -0.454506685895065 103017.74 AEPI 19 2003 10025 00103110 -0.254858939653553 79635.803 AEPI 20 2004 10025 00103110 0.51452748398919 123973.75 AEPI 21 2005 10025 00103110 0.694914398417631 213825 AEPI 22 2006 10025 00103110 1.13240001126598 420402.66 AEPI 23 2007 10025 00103110 -0.399551514112244 219268.5 AEPI 24 2008 10025 00103110 -0.450796964545808 118665 AEPI 25 2009 10025 00103110 1.17747436654493 262294.56 AEPI 26 2010 10025 00103110 -0.322099431016385 159436.8 AEPI 27 2011 10025 00103110 0.0847783787243792 154543.5 AEPI 28 2012 10025 00103110 1.10408576883163 327601.13 AEPI 29 2013 10025 00103110 -0.108052452034367 295900.83 AEPI 30 2014 10025 00103110 0.100700043579931 295460.15 AEPI 31 2015 10025 00103110 0.326743160699011 393696.45 AEPI 32 2016 10025 00103110 0.523607269936083 593735.4 AEPI
 year perm cusip rate of return marketcap ticker PERMNO 10026 1 1986 10026 46603210 -0.220000654929347 28450.5 JJSF 2 1987 10026 46603210 0.487181313861582 42862 JJSF 3 1988 10026 46603210 0.568966628191658 71321.25 JJSF 4 1989 10026 46603210 0.087912544841263 103479.75 JJSF 5 1990 10026 46603210 -0.363635978824163 64897.875 JJSF 6 1991 10026 46603210 0.809524399209781 151890.75 JJSF 7 1992 10026 46603210 -0.36842070137258 91845 JJSF 8 1993 10026 46603210 1.29166872362313 213283.125 JJSF 9 1994 10026 46603210 -0.436363958699362 109368 JJSF 10 1995 10026 46603210 -0.0537635888504683 99110 JJSF 11 1996 10026 46603210 0.227272662933723 118125 JJSF 12 1997 10026 46603210 0.212960931624776 145279 JJSF 13 1998 10026 46603210 0.366412386586049 203769.125 JJSF 14 1999 10026 46603210 -0.0837984371027144 184848.5 JJSF 15 2000 10026 46603210 -0.179877978880341 141494 JJSF 16 2001 10026 46603210 0.45427707661164 211590.3 JJSF 17 2002 10026 46603210 0.460533929907742 317997.55 JJSF 18 2003 10026 46603210 0.0574089393846946 331683.84 JJSF 19 2004 10026 46603210 0.301772082175993 442348.66 JJSF 20 2005 10026 46603210 0.223559074488823 543542.09 JJSF 21 2006 10026 46603210 0.406242753220254 766521 JJSF 22 2007 10026 46603210 -0.237010149919167 585248.8 JJSF 23 2008 10026 46603210 0.161103037721484 657393.36 JJSF 24 2009 10026 46603210 0.125422281484275 734225.04 JJSF 25 2010 10026 46603210 0.219355130741946 894466.08 JJSF 26 2011 10026 46603210 0.115274609366267 994311.36 JJSF 27 2012 10026 46603210 0.210375565724327 1199916.2673 JJSF 28 2013 10026 46603210 0.400294462778987 1654949.79 JJSF 29 2014 10026 46603210 0.244405562294745 2031606.06 JJSF 30 2015 10026 46603210 0.0867912090004315 2179045.59 JJSF 31 2016 10026 46603210 0.158931382021662 2492872.50317 JJSF

## Computer Code

  # loading libraries
library(xts)
library(zoo)
library(magrittr)
library(data.table)
library(tidyr)
library(lubridate)

colnames(dset_data) %<>% tolower

test <- dset_data
test[, date := as.Date(as.character(date), format = "%Y%m%d") ]