R Markdown
# Install and load necessary packages
if (!require("pacman")) install.packages("pacman")
## Loading required package: pacman
pacman::p_load(readr, tidyverse)
# --- TASK 1: Import data from downloaded CSV file ---
# A window will pop up. Please select 'tlon111.csv' from your Downloads folder
etf_data <- read_csv(file.choose())
## Rows: 12297 Columns: 7
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): COID_Name, Date
## dbl (5): COID, Open, High, Low, Close
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# --- TASK 2: Convert to time series data in the required format ---
# Automatically select and rename the first 3 columns
etf_formatted <- etf_data %>%
select(1:3) %>%
rename(Date = 1, Stock = 2, Price = 3) %>%
pivot_wider(names_from = Stock, values_from = Price)
## Warning: Values from `Price` are not uniquely identified; output will contain list-cols.
## • Use `values_fn = list` to suppress this warning.
## • Use `values_fn = {summary_fun}` to summarise duplicates.
## • Use the following dplyr code to identify duplicates.
## {data} |>
## dplyr::summarise(n = dplyr::n(), .by = c(Date, Stock)) |>
## dplyr::filter(n > 1L)
# Rename columns to match the exact requirement
colnames(etf_formatted) <- c("Date", "0050 元大台灣50", "0052 富邦科技", "0056 元大高股息")
# Show the first few returns for all stocks
head(etf_formatted)
## # A tibble: 3 × 4
## Date `0050 元大台灣50` `0052 富邦科技` `0056 元大高股息`
## <dbl> <list> <list> <list>
## 1 52 <chr [4,099]> <NULL> <NULL>
## 2 56 <NULL> <chr [4,099]> <NULL>
## 3 50 <NULL> <NULL> <chr [4,099]>