rm(list=ls())
library(tidyverse)
library(tidyquant)
library(timetk)

Late submission is not allowed.

Questions:

1. Please load up packages `tidyquant`, `tidyverse` and `timetk`. Import the data file from Tronclass: `tej_day_price_2024_20250630.txt`. Try using functions `read_csv()`, `read_tsv()` and `read_delim()` to import data. Show me the imported results of by using one of the three functions: `glimpse()`, `head()` or `str()` to show the results. (Note: If you are asked to show the results, you can use `head()` or `glimpse()` when your data in your answers is very big and long.)

## Rows: 337347 Columns: 12
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: "\t"
## chr (3): CO_ID, TSE Sector, English Short Name
## dbl (9): Date, TSE ID, Open(NTD), High(NTD), Low(NTD), Close(NTD), Volume(10...
## 
## ℹ 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.

## Rows: 337,347
## Columns: 12
## $ CO_ID                 <chr> "1101 TCC", "1102 ACC", "1103 CHC", "1104 UCC", …
## $ Date                  <dbl> 20240102, 20240102, 20240102, 20240102, 20240102…
## $ `TSE ID`              <dbl> 1101, 1102, 1103, 1104, 1108, 1109, 1110, 1201, …
## $ `TSE Sector`          <chr> "01", "01", "01", "01", "01", "01", "01", "02", …
## $ `English Short Name`  <chr> "TCC", "ACC", "CHC", "UCC", "Lucky Cement", "HSI…
## $ `Open(NTD)`           <dbl> 32.5373, 37.2642, 17.7825, 26.0628, 14.1679, 16.…
## $ `High(NTD)`           <dbl> 32.5373, 37.4442, 17.7825, 26.1505, 14.1679, 16.…
## $ `Low(NTD)`            <dbl> 32.3038, 36.9492, 17.5953, 25.9750, 14.0343, 16.…
## $ `Close(NTD)`          <dbl> 32.3972, 37.0392, 17.6421, 26.0628, 14.0788, 16.…
## $ `Volume(1000S)`       <dbl> 14937, 6223, 171, 260, 442, 228, 57, 126, 48, 18…
## $ `Amount(NTD1000)`     <dbl> 518751, 256522, 3240, 7736, 6992, 4159, 1075, 24…
## $ `Market Cap.(NTD MN)` <dbl> 262026, 145941, 14896, 19995, 6395, 6209, 10754,…

2. Replace column 2, 3, 5, 9 and 12 with new column names: “date”, “id”, “name”, “price”, “cap”. Show your results.

## Rows: 337,347
## Columns: 5
## $ id    <dbl> 1101, 1102, 1103, 1104, 1108, 1109, 1110, 1201, 1203, 1210, 1213…
## $ name  <chr> "TCC", "ACC", "CHC", "UCC", "Lucky Cement", "HSINGTA", "Tuna Cem…
## $ date  <dbl> 20240102, 20240102, 20240102, 20240102, 20240102, 20240102, 2024…
## $ price <dbl> 32.3972, 37.0392, 17.6421, 26.0628, 14.0788, 16.1807, 18.3336, 1…
## $ cap   <dbl> 262026, 145941, 14896, 19995, 6395, 6209, 10754, 9640, 13992, 52…

3. Select column `id`, `date`, `price`, and change `id`format to text, `date` format to date. Also change the data format from long to wide and show your results (Hint：you can use `dcast()` or `spread()` function).

4. Show the stock ids with `NA` values and compute the number of `NA` for each stock.

5. Replace `NA` values with the closest available stock prices (Hint: you can use `na.locf()`).

6. Delete the stock which contains prices of `NA` in your data in question 4. Show the updated number of rows and columns in your filtered data.

## [1] 358 937

7. Convert data in Question 6 into time series data (`xts`) (Hint: you can use `tk_xts()`). And calculuate daily rate of returns (Hint：use `Return.calculate()` and compute `discrete` returns). Delete the first row and show the first five stocks with first five days of returns.

##                    1101         1102         1103         1104         1108
## 2024-01-03 -0.014408653 -0.012149290 -0.007958236 -0.006733735 -0.003160781
## 2024-01-04  0.000000000  0.012298711  0.000000000 -0.013558772  0.003170803
## 2024-01-05  0.004384536 -0.001214929  0.002674026  0.010306896  0.000000000
## 2024-01-08 -0.002909225  0.004865628  0.002666895 -0.003399291  0.006328664
## 2024-01-09 -0.005841680 -0.007263102 -0.002659801 -0.013655209 -0.025155457

8. Compute monthly returns. Delete the first row and show the first five stocks with first five months of returns (Hint: you can use `to.period()` or `to.monthly()`).

##                    1101        1102         1103        1104         1108
## 2024-02-29  0.006272034  0.01760804 -0.008404066  0.01887014  0.036185231
## 2024-03-31  0.001554899  0.02101398 -0.016950585  0.06397241  0.003170803
## 2024-04-30 -0.003108301  0.05811288  0.057470064  0.11234002  0.072790295
## 2024-05-31  0.029639309 -0.04920108  0.032611536 -0.03271487  0.000000000
## 2024-06-30  0.036364817  0.05535680 -0.036845213  0.04852830 -0.011805133

9. Find the 20 largest cap firms in the year end of 2024 and 2025. Show the results (Hint: you can use `select()`, `filter()`, `group_by()`, `arrange()`, `slice()`, `ungroup()`).

Finanical Database Analysis and Application - Midterm

Shyh-Weir Tzang

2025/10/28

Late submission is not allowed.

Questions:

2. Replace column 2, 3, 5, 9 and 12 with new column names: “date”, “id”, “name”, “price”, “cap”. Show your results.

3. Select column `id`, `date`, `price`, and change `id`format to text, `date` format to date. Also change the data format from long to wide and show your results (Hint：you can use `dcast()` or `spread()` function).

4. Show the stock ids with `NA` values and compute the number of `NA` for each stock.

5. Replace `NA` values with the closest available stock prices (Hint: you can use `na.locf()`).

6. Delete the stock which contains prices of `NA` in your data in question 4. Show the updated number of rows and columns in your filtered data.

7. Convert data in Question 6 into time series data (`xts`) (Hint: you can use `tk_xts()`). And calculuate daily rate of returns (Hint：use `Return.calculate()` and compute `discrete` returns). Delete the first row and show the first five stocks with first five days of returns.

8. Compute monthly returns. Delete the first row and show the first five stocks with first five months of returns (Hint: you can use `to.period()` or `to.monthly()`).

9. Find the 20 largest cap firms in the year end of 2024 and 2025. Show the results (Hint: you can use `select()`, `filter()`, `group_by()`, `arrange()`, `slice()`, `ungroup()`).

Finanical Database Analysis and Application - Midterm

Shyh-Weir Tzang

2025/10/28

Late submission is not allowed.

Questions:

2. Replace column 2, 3, 5, 9 and 12 with new column names: “date”, “id”, “name”, “price”, “cap”. Show your results.

3. Select column id, date, price, and change idformat to text, date format to date. Also change the data format from long to wide and show your results (Hint：you can use dcast() or spread() function).

4. Show the stock ids with NA values and compute the number of NA for each stock.

5. Replace NA values with the closest available stock prices (Hint: you can use na.locf()).

6. Delete the stock which contains prices of NA in your data in question 4. Show the updated number of rows and columns in your filtered data.

7. Convert data in Question 6 into time series data (xts) (Hint: you can use tk_xts()). And calculuate daily rate of returns (Hint：use Return.calculate() and compute discrete returns). Delete the first row and show the first five stocks with first five days of returns.

8. Compute monthly returns. Delete the first row and show the first five stocks with first five months of returns (Hint: you can use to.period() or to.monthly()).

9. Find the 20 largest cap firms in the year end of 2024 and 2025. Show the results (Hint: you can use select(), filter(), group_by(), arrange(), slice(), ungroup()).

3. Select column `id`, `date`, `price`, and change `id`format to text, `date` format to date. Also change the data format from long to wide and show your results (Hint：you can use `dcast()` or `spread()` function).

4. Show the stock ids with `NA` values and compute the number of `NA` for each stock.

5. Replace `NA` values with the closest available stock prices (Hint: you can use `na.locf()`).

6. Delete the stock which contains prices of `NA` in your data in question 4. Show the updated number of rows and columns in your filtered data.

7. Convert data in Question 6 into time series data (`xts`) (Hint: you can use `tk_xts()`). And calculuate daily rate of returns (Hint：use `Return.calculate()` and compute `discrete` returns). Delete the first row and show the first five stocks with first five days of returns.

8. Compute monthly returns. Delete the first row and show the first five stocks with first five months of returns (Hint: you can use `to.period()` or `to.monthly()`).

9. Find the 20 largest cap firms in the year end of 2024 and 2025. Show the results (Hint: you can use `select()`, `filter()`, `group_by()`, `arrange()`, `slice()`, `ungroup()`).