I. Loading package

At first, we load the necessary packages:

library(pacman)
library(tidyverse)
library(readxl)
library(kableExtra)
library(dplyr)
library(reshape2)
library(PerformanceAnalytics)

II. Running data

1. Importing data

We import the data of the closing price of 3 stock: 0050, 0052, 0056 and name 3 columns respectively by “id”, “date”, “close”.

Daily closing price of ETF 0050, 0052 and 0056
ID Date Close
0050 20100104 37.5723
0052 20100104 23.1720
0056 20100104 11.9283
0050 20100105 37.5723
0052 20100105 23.2037
0056 20100105 11.8775
0050 20100106 38.2705
0052 20100106 23.7433
0056 20100106 12.0552
0050 20100107 38.1708

2. Reshape the data

We reshape the data with date and closing price of each stocks ID:

Daily closing price of ETF 0050, 0052 and 0056
Date 0050 0052 0056
20100104 37.5723 23.1720 11.9283
20100105 37.5723 23.2037 11.8775
20100106 38.2705 23.7433 12.0552
20100107 38.1708 23.4894 11.9537
20100108 38.4035 23.4195 12.0805
20100111 38.5033 23.5529 12.2074
20100112 38.3038 23.4894 12.1415
20100113 37.7385 23.4894 12.0095
20100114 38.2040 23.4894 12.1821
20100115 38.4368 23.8703 12.2430

3. Convert from number to date format

Lastly, we use the “xts” function to convert the data of date column to date format. The data frame now has the row’s name as the date of closing price:

install.packages("xts")
library(xts)
str(etf3.1)
## 'data.frame':    2954 obs. of  4 variables:
##  $ Date: num  20100104 20100105 20100106 20100107 20100108 ...
##  $ 0050: num  37.6 37.6 38.3 38.2 38.4 ...
##  $ 0052: num  23.2 23.2 23.7 23.5 23.4 ...
##  $ 0056: num  11.9 11.9 12.1 12 12.1 ...
etf3.xts <- xts(etf3.1[,-1],order.by= as.Date(as.character(etf3.1$Date),format='%Y%m%d'))
table <- data.frame(head(etf3.xts,n=6L))
kbl(table,caption="First 6 rows of dataset")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = "#F9EBEA") %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
First 6 rows of dataset
X0050 X0052 X0056
2010-01-04 37.5723 23.1720 11.9283
2010-01-05 37.5723 23.2037 11.8775
2010-01-06 38.2705 23.7433 12.0552
2010-01-07 38.1708 23.4894 11.9537
2010-01-08 38.4035 23.4195 12.0805
2010-01-11 38.5033 23.5529 12.2074

These are the first 6 lines of the data after converting.

4. Calculate the return

a. Daily return

etf3.daily <- Return.calculate(etf3.xts, method= 'log') %>% na.omit() 
etf3.daily %>% head(6) %>% data.frame() %>%
           kbl (caption="First 6 rows of dataset")%>%
           row_spec(row =0, bold= TRUE, color = "black", background = "#F9EBEA") %>%
           kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
First 6 rows of dataset
X0050 X0052 X0056
2010-01-05 0.0000000 0.0013671 -0.0042679
2010-01-06 0.0184123 0.0229886 0.0148502
2010-01-07 -0.0026085 -0.0107511 -0.0084552
2010-01-08 0.0060778 -0.0029802 0.0105517
2010-01-11 0.0025954 0.0056799 0.0104497
2010-01-12 -0.0051948 -0.0026997 -0.0054130

b. Weekly return

etf3.weekly <- etf3.xts %>% to.weekly(indexAt = "lastof", OHLC= FALSE)
etf3.weekly %>% Return.calculate(method = 'log') %>% na.omit() %>% head(6)%>%
  data.frame()%>%
  kbl(caption="First 6 rows of dataset")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = "#F9EBEA") %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
First 6 rows of dataset
X0050 X0052 X0056
2010-01-15 0.0008667 0.0190660 0.0133618
2010-01-22 -0.0606249 -0.0688020 -0.0453741
2010-01-29 -0.0260724 -0.0318437 -0.0308472
2010-02-06 -0.0345526 -0.0497466 -0.0462376
2010-02-10 0.0174255 0.0349326 0.0281814
2010-02-26 -0.0106143 -0.0105029 -0.0022824

c. Monthly return

etf3.monthly <- etf3.xts %>% to.monthly(indexAt = "lastof", OHLC= FALSE)
etf3.monthly %>% Return.calculate(method = 'log') %>% na.omit() %>% head(6)%>%
  data.frame()%>%
  kbl(caption="First 6 rows of dataset")%>%
  row_spec(row =0, bold= TRUE, color = "black", background = "#F9EBEA") %>%
  kable_styling(bootstrap_options = "striped", full_width = F, position = "center")
First 6 rows of dataset
X0050 X0052 X0056
2010-02-28 -0.0277413 -0.0253169 -0.0203385
2010-03-31 0.0510511 0.0508715 0.0402803
2010-04-30 0.0100886 0.0074283 0.0234083
2010-05-31 -0.0896697 -0.0879287 -0.0650569
2010-06-30 -0.0110373 -0.0295602 0.0013683
2010-07-31 0.0841472 0.0781947 0.0873944