Midterm database

##Loading packages and import data

{r, echo = FALSE, message = FALSE} install.packages("pacman") install.packages("readxl") install.packages("tidyquant") install.packages("tidyverse") install.packages("kableExtra") install.packages("dplyr") install.packages("dtplyr") install.packages("reshape2") install.packages("PerformanceAnalytics")

{r, message = FALSE} library(pacman) library(readxl) library(tidyquant) library(tidyverse) library(timetk) library(kableExtra) library(dplyr) library(dtplyr) library(reshape2) library(PerformanceAnalytics)

data<- read_tsv("tej_day_price_2017_2018.txt")
etf <- data
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

colnames(etf)[1] = "id"
colnames(etf)[2] = "name"
colnames(etf)[5] = "date"
colnames(etf)[8] = "price"
colnames(etf)[11] = "cap"
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

etf2 <- select(etf,id,date,price) 
etf2$id <- as.character(etf2$id)
etf2$date <- as.Date(as.character(etf$date),format='%Y%m%d')
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

p_load(reshape2)
etf3  <- dcast(etf2, date~id)
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

id_na <- as.data.frame(apply(is.na(etf3),2,sum)) 
colnames(id_na)="sum_na"
id_na <- filter(id_na,sum_na>0)
glimpse(id_na)
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

{r, message = FALSE} etf5 <- na.locf(etf3) apply(is.na(etf5),2,sum) etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>% row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

{r, message = FALSE, echo = FALSE} etf6 <- etf3 %>% select_if(~ !any(is.na(.))) apply(is.na(etf6),2,sum) etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>% row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

{r, message = FALSE} library(xts) etf7 <- xts(etf6[,-1],order.by= etf6$date) etf7.daily <- Return.calculate(etf7, method= 'log') daily <- etf7.daily[,1:5] %>% na.omit() daily %>% head(5) %>% data.frame() %>% kbl (caption="First 6 rows of dataset")%>% row_spec(row =0, bold= TRUE, color = "black") %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "center") etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>% row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "center")

etf8 <- etf7 %>% to.monthly(indexAt = "lastof", OHLC= FALSE)
etf8 <- Return.calculate(etf8,method = 'log') 
etf.monthly <- etf8[,1:5] %>% na.omit() 
etf.monthly %>% head(5) %>% data.frame()%>%
  kbl(caption="First 6 rows of dataset")%>%
  row_spec(row =0, bold= TRUE, color = "black") %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
etf %>% head(6) %>% kbl(caption="New data after rename 5 columns")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = rainbow(6)) %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")