Week 2

Part 1

# load packages
library(dplyr)
library(lubridate)
library(ggplot2)

# preview of dataset
glimpse(pricemultiples)

Observations: 2,396
Variables: 9
$ gvkey    <chr> "001257", "001257", "001594", "001594", "001594", "001594", "001594"...
$ datadate <date> 1990-07-31, 1991-07-31, 1990-01-31, 1991-01-31, 1992-01-31, 1993-01...
$ conm     <chr> "ALEXANDER'S INC", "ALEXANDER'S INC", "AMES DEPT STORES INC", "AMES ...
$ ceq      <dbl> 103.902, 99.720, 212.208, -581.251, -863.633, 45.998, 60.372, 84.917...
$ csho     <dbl> 4.976, 4.976, 37.582, 37.582, 37.582, 8.962, 16.267, 20.127, 20.472,...
$ epspi    <dbl> -0.79, -0.84, -6.41, -21.47, -7.87, 34.14, 0.51, 0.79, -0.08, 0.79, ...
$ revt     <dbl> 460.224, 430.287, 4793.126, 3109.080, 2836.837, 2442.897, 2139.055, ...
$ sich     <int> 5311, 5311, 5331, 5331, 5331, 5331, 5331, 5331, 5331, 5331, 5331, 53...
$ prcc_c   <dbl> 22.750000, 24.250000, 10.375000, 0.562000, 0.812000, 1.750000, 2.250...

# compute variables
pricemultiples2<-pricemultiples %>%mutate(marketvalue=prcc_c*csho,
                                         pe_ratio=prcc_c/epspi,
                                         pb_ratio=marketvalue/ceq,
                                         ps_ratio=marketvalue/revt,
                                         year=year(datadate))
pricemultiples2

# compute median of valuation ratios by year
medians_by_year<-pricemultiples2 %>% group_by(year)%>% summarize (median_pe_ratio=median(pe_ratio, na.rm=TRUE  ),median_pb_ratio=median(pb_ratio, na.rm=TRUE  ),median_ps_ratio=median(ps_ratio, na.rm=TRUE  ))
medians_by_year

# create dataframe containing only data for your company
dsw_ratios<-pricemultiples2 %>% filter(gvkey=="024171")
dsw_ratios

# merge dataframe for your company with dataframe containing year medians. merge by year.
firm_with_year_medians <- merge(dsw_ratios,medians_by_year,by="year")
firm_with_year_medians

# 4

# For each valuation ratio create a barplot of median by year with a line graph for your company's ratio by year
ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_pe_ratio), stat="identity") +
  geom_line(aes(x = year, y = pe_ratio))

ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_pb_ratio), stat="identity") +
  geom_line(aes(x = year, y = pb_ratio))

ggplot(firm_with_year_medians)+geom_bar(aes(x =year, y=median_ps_ratio), stat="identity") +
  geom_line(aes(x = year, y = ps_ratio))

# 2, 3, 5 & 6

# use the median valuation ratio for 2017 to value your company and calculate expected return given valuation.
dsw_valuation<-firm_with_year_medians%>% filter(year==2017)%>% 
  mutate(valuation_using_pe=epspi*median_pe_ratio,
         expected_return_pe=valuation_using_pe/prcc_c-1,
        valuation_using_pb=ceq*median_pb_ratio,
          expected_return_pb=valuation_using_pb/marketvalue-1,
        valuation_using_ps=revt*median_ps_ratio,
          expected_return_ps=valuation_using_ps/marketvalue-1)
dsw_valuation

Part 2

# load packages
library(dplyr)
library(ggplot2)
library(quantmod)
library(PerformanceAnalytics)

# get daily stock price data from Yahoo! Finance for TJX
# specify the ticker symbols you want price data for
Symbols<-c("DSW")

# specify daily or monthly prices and the date range to collect.
dsw_daily_prices<-as.data.frame(getSymbols(Symbols, periodicity="daily", from="1900-01-01",
                                           to=Sys.Date(), src='yahoo', env=NULL,auto.assign = TRUE))
dsw_daily_prices

# output from Yahoo! Finance is missing date variable.  add the date variable.
dsw_daily_prices<-cbind(date=as.Date(rownames(dsw_daily_prices)), dsw_daily_prices)

# preview of output
glimpse(dsw_daily_prices)

Observations: 3,353
Variables: 7
$ date         <date> 2005-06-29, 2005-06-30, 2005-07-01, 2005-07-05, 2005-07-06, 200...
$ DSW.Open     <dbl> 12.000, 12.250, 12.225, 13.025, 13.300, 12.700, 13.050, 13.250, ...
$ DSW.High     <dbl> 12.255, 12.500, 13.000, 13.300, 13.345, 13.075, 13.250, 13.470, ...
$ DSW.Low      <dbl> 11.555, 12.125, 12.225, 13.025, 12.450, 12.600, 12.675, 13.215, ...
$ DSW.Close    <dbl> 12.050, 12.475, 12.925, 13.175, 12.705, 12.950, 13.205, 13.325, ...
$ DSW.Volume   <dbl> 21130200, 3585200, 1156200, 1952400, 1221200, 916000, 491200, 66...
$ DSW.Adjusted <dbl> 9.532927, 9.869151, 10.225153, 10.422933, 10.051108, 10.244930, ...

# 7

# daily stock chart for life of the stock. DSW.Adjusted price adjusts price for dividends and stock splits.
dsw_daily_prices %>% ggplot(aes(x=date , y=DSW.Adjusted)) +geom_point() + geom_line()

# 8

# daily stock chart for the last year
dsw_daily_prices %>% filter(date >= "2017-10-23") %>% ggplot(aes(x=date , y=DSW.Adjusted)) +geom_point() + geom_line()

#estimate beta for group project companies

# Companies from group project list +DSW +JWN (Nordstrom) +SPY (Market Return)
Symbols2<-c("ANF", "AEO", "BIG", "CHS", "CTRN", "COST", "DG", "DLTR",
           "DSW", "EXPR", "FIVE", "FL", "FRAN", "GCO", "JCP", "KSS", 
           "M", "NWY", "PSMT", "ROST", "TGT", "BKE", "PLCE", "GPS",
           "TLYS", "URBN", "WMT", "ZUMZ", "TJX", "JWN", "SPY")

# Specify last 5 years of monthly data (note: 2018-10-01 will be the last price in September 2018)
dates<-c(as.Date("2013-10-01"), as.Date("2018-10-02"))
names(dates) <- c("start", "end")
dates

       start          end 
"2013-10-01" "2018-10-02" 

#create data enviroment where prices for each company will go
data_env<-new.env()

# Specify monthly prices
getSymbols(Symbols2, periodicity="monthly", from=dates[1], to=dates[2], 
           src='yahoo', env=data_env,
           auto.assign = TRUE)

pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols
pausing 1 second between requests for more than 5 symbols

 [1] "ANF"  "AEO"  "BIG"  "CHS"  "CTRN" "COST" "DG"   "DLTR" "DSW"  "EXPR" "FIVE" "FL"  
[13] "FRAN" "GCO"  "JCP"  "KSS"  "M"    "NWY"  "PSMT" "ROST" "TGT"  "BKE"  "PLCE" "GPS" 
[25] "TLYS" "URBN" "WMT"  "ZUMZ" "TJX"  "JWN"  "SPY" 

# Merge all of the companies together, only keep the Adjusted Price column
adjclose_data<-do.call(merge,eapply(data_env, Ad))
head(adjclose_data)

           JCP.Adjusted DG.Adjusted FIVE.Adjusted TLYS.Adjusted EXPR.Adjusted
2013-10-01         7.50    54.96966         48.26      12.85591         23.21
2013-11-01        10.19    54.17051         53.16      10.63085         24.61
2013-12-01         9.15    57.38612         43.20      10.10990         18.67
2014-01-01         5.92    53.58067         36.65      10.25118         17.32
2014-02-01         7.28    56.98655         38.54      10.56022         18.29
2014-03-01         8.62    52.78153         42.48      10.33065         15.88
           DLTR.Adjusted ANF.Adjusted FL.Adjusted SPY.Adjusted KSS.Adjusted AEO.Adjusted
2013-10-01         58.40     31.02083    31.10720     159.2703     46.44024     13.04375
2013-11-01         55.65     28.37230    35.07705     163.9907     45.19747     13.70057
2013-12-01         56.42     27.40044    37.37704     167.3340     46.39936     12.12589
2014-01-01         50.52     29.45692    34.81548     162.3124     41.66216     11.49439
2014-02-01         54.77     32.99543    37.80560     169.7002     46.23735     12.34394
2014-03-01         52.18     32.05460    42.58229     170.3560     46.73931     10.39848
           ZUMZ.Adjusted FRAN.Adjusted M.Adjusted BKE.Adjusted PSMT.Adjusted TGT.Adjusted
2013-10-01         29.64         17.98   38.37164     39.89265     109.17086     54.94980
2013-11-01         27.77         19.62   44.32169     43.42250     119.72433     54.22042
2013-12-01         26.00         18.40   44.43820     43.01331     110.84982     54.00784
2014-01-01         21.52         19.00   44.48655     37.14408      87.21004     48.34841
2014-02-01         23.76         19.57   48.38330     38.19327      97.60042     53.38471
2014-03-01         24.24         18.14   49.57908     38.55525      97.18800     52.04548
           BIG.Adjusted JWN.Adjusted PLCE.Adjusted GCO.Adjusted DSW.Adjusted GPS.Adjusted
2013-10-01     33.34871     49.56719      51.46137        68.11     37.49954     31.23619
2013-11-01     35.15556     50.99346      51.84786        74.91     38.35073     34.77235
2013-12-01     29.61578     50.90015      53.70497        73.06     36.55423     33.16826
2014-01-01     24.57128     47.31737      49.65140        70.22     32.30375     32.31953
2014-02-01     27.10271     50.63659      51.06543        74.25     33.01590     37.32068
2014-03-01     34.73366     51.43550      46.95531        74.57     30.76793     34.17296
           WMT.Adjusted URBN.Adjusted CHS.Adjusted NWY.Adjusted TJX.Adjusted
2013-10-01     67.38482         37.88     14.87507         5.12     56.81984
2013-11-01     71.12501         39.02     16.21079         5.13     58.77335
2013-12-01     69.08810         37.10     16.34089         4.37     59.70818
2014-01-01     65.94908         35.82     14.45594         4.53     53.74017
2014-02-01     65.96674         37.44     14.39498         4.43     57.58143
2014-03-01     67.49447         36.47     13.95956         4.39     56.96191
           CTRN.Adjusted COST.Adjusted ROST.Adjusted
2013-10-01      14.00305     103.84253      36.18815
2013-11-01      15.74867     110.38110      35.77177
2013-12-01      16.21607     105.00599      35.05596
2014-01-01      15.26218      99.13019      31.92211
2014-02-01      15.64374     103.04739      34.22073
2014-03-01      15.53881      98.79658      33.64256

# Use function from PerformanceAnalytics package to calculate returns using the price data
adjclose_data2<-Return.calculate(adjclose_data)
head(adjclose_data2)

           JCP.Adjusted DG.Adjusted FIVE.Adjusted TLYS.Adjusted EXPR.Adjusted
2013-10-01           NA          NA            NA            NA            NA
2013-11-01    0.3586667 -0.01453800    0.10153341   -0.17307691    0.06031892
2013-12-01   -0.1020608  0.05936091   -0.18735890   -0.04900341   -0.24136533
2014-01-01   -0.3530055 -0.06631309   -0.15162034    0.01397382   -0.07230852
2014-02-01    0.2297297  0.06356538    0.05156886    0.03014648    0.05600468
2014-03-01    0.1840659 -0.07378971    0.10223142   -0.02173904   -0.13176604
           DLTR.Adjusted ANF.Adjusted FL.Adjusted SPY.Adjusted KSS.Adjusted AEO.Adjusted
2013-10-01            NA           NA          NA           NA           NA           NA
2013-11-01   -0.04708904  -0.08537906  0.12761864  0.029637847  -0.02676057   0.05035500
2013-12-01    0.01383641  -0.03425387  0.06556948  0.020386706   0.02659211  -0.11493553
2014-01-01   -0.10457281   0.07505311 -0.06853293 -0.030009304  -0.10209633  -0.05207801
2014-02-01    0.08412510   0.12012476  0.08588490  0.045515853   0.10981656   0.07391003
2014-03-01   -0.04728866  -0.02851383  0.12634876  0.003864869   0.01085609  -0.15760513
           ZUMZ.Adjusted FRAN.Adjusted  M.Adjusted BKE.Adjusted PSMT.Adjusted
2013-10-01            NA            NA          NA           NA            NA
2013-11-01   -0.06309039    0.09121251 0.155063770  0.088483616   0.096669267
2013-12-01   -0.06373785   -0.06218150 0.002628781 -0.009423318  -0.074124484
2014-01-01   -0.17230769    0.03260870 0.001087893 -0.136451521  -0.213259574
2014-02-01    0.10408922    0.03000000 0.087593921  0.028246412   0.119142032
2014-03-01    0.02020202   -0.07307108 0.024714831  0.009477586  -0.004225617
           TGT.Adjusted BIG.Adjusted JWN.Adjusted PLCE.Adjusted GCO.Adjusted DSW.Adjusted
2013-10-01           NA           NA           NA            NA           NA           NA
2013-11-01 -0.013273587    0.0541805  0.028774479   0.007510293  0.099838539   0.02269884
2013-12-01 -0.003920626   -0.1575790 -0.001829882   0.035818353 -0.024696381  -0.04684388
2014-01-01 -0.104789058   -0.1703314 -0.070388364  -0.075478475 -0.038872120  -0.11627876
2014-02-01  0.104166813    0.1030236  0.070148048   0.028479198  0.057391042   0.02204521
2014-03-01 -0.025086452    0.2815568  0.015777445  -0.080487230  0.004309764  -0.06808735
           GPS.Adjusted  WMT.Adjusted URBN.Adjusted CHS.Adjusted NWY.Adjusted
2013-10-01           NA            NA            NA           NA           NA
2013-11-01   0.11320726  0.0555049202    0.03009501  0.089795678  0.001953125
2013-12-01  -0.04613122 -0.0286383658   -0.04920559  0.008026014 -0.148148148
2014-01-01  -0.02558868 -0.0454350723   -0.03450130 -0.115351944  0.036613272
2014-02-01   0.15474088  0.0002678127    0.04522610 -0.004216744 -0.022075055
2014-03-01  -0.08434262  0.0231590333   -0.02590807 -0.030247967 -0.009029345
           TJX.Adjusted CTRN.Adjusted COST.Adjusted ROST.Adjusted
2013-10-01           NA            NA            NA            NA
2013-11-01   0.03438078   0.124659440    0.06296618   -0.01150603
2013-12-01   0.01590568   0.029678954   -0.04869590   -0.02001042
2014-01-01  -0.09995298  -0.058823678   -0.05595682   -0.08939575
2014-02-01   0.07147836   0.025000355    0.03951569    0.07200721
2014-03-01  -0.01075894  -0.006707474   -0.04125101   -0.01689546

# 9

# Use function from PerformanceAnalytics package to compute beta for each company
betas <- function(returns) {
  
  CAPM.beta(returns, adjclose_data2$SPY.Adjusted)
}
betas2<-lapply(adjclose_data2,betas)
betas3<-as.data.frame(betas2)
sort(betas3)

# Write our own function to compute the 95% confidence interval for our beta estimates
market_return<-adjclose_data2$SPY.Adjusted
confinterval <- function(returns) {
  
  fit<-lm(returns~market_return)
  confint(fit, "market_return", level=0.95)
}
confinterval_results<-sapply(adjclose_data2,confinterval)

essentially perfect fit: summary may be unreliable

confinterval_results

     JCP.Adjusted DG.Adjusted FIVE.Adjusted TLYS.Adjusted EXPR.Adjusted DLTR.Adjusted
[1,]   -0.4483868   0.1641207    -0.5969217     -1.407859    -0.3331591    0.01632939
[2,]    2.3061785   1.3527016     1.3489374      1.613132     2.0398615    1.30028539
     ANF.Adjusted FL.Adjusted SPY.Adjusted KSS.Adjusted AEO.Adjusted ZUMZ.Adjusted
[1,]   -0.5897331  -0.1827717            1    0.3695992  -0.07839372    0.03080541
[2,]    1.4832728   1.6543787            1    1.8719174   1.56604924    2.46847753
     FRAN.Adjusted M.Adjusted BKE.Adjusted PSMT.Adjusted TGT.Adjusted BIG.Adjusted
[1,]     -0.985509  -0.106453   -0.1495301     0.1680937   0.08085303    0.2115688
[2,]      1.542242   1.597899    1.6209455     1.4409719   1.19763517    1.7510115
     JWN.Adjusted PLCE.Adjusted GCO.Adjusted DSW.Adjusted GPS.Adjusted WMT.Adjusted
[1,]   -0.2065527    -0.5699314    0.1180309    0.2180102   -0.1888101   -0.1151998
[2,]    1.2550163     1.1536263    2.0271529    1.8847466    1.6795409    0.8305468
     URBN.Adjusted CHS.Adjusted NWY.Adjusted TJX.Adjusted CTRN.Adjusted COST.Adjusted
[1,]    -0.3723914  -0.09897539    -0.508397    0.2154469    -0.4070595     0.4851496
[2,]     1.3975146   1.64928009     2.910679    0.9733039     1.5463970     1.2013781
     ROST.Adjusted
[1,]     0.4793741
[2,]     1.4697625

# Create scatter plot of company returns as y and market returns as x.  Included regression line in plot.  
adjclose_data2%>% ggplot(aes(x=SPY.Adjusted, y=DSW.Adjusted)) +geom_point() +geom_smooth(method = "lm", se = FALSE)

# Create scatter plot of company returns as y and market returns as x.  Included regression line in plot with 95% Confidence Interval.  
adjclose_data2%>% ggplot(aes(x=SPY.Adjusted, y=DSW.Adjusted)) +geom_point() +geom_smooth(method = "lm", se = TRUE, level=0.95)