# Load required packages
library(tidyverse)
library(tidyquant)
library(timetk)
library(zoo)

Question 1: Data Import

Task: Import TEJ database file and display structure

Code & Output

# Import using read_tsv (tab-separated values)
data1 <- read_tsv("tej_day_price_2024_20250630.txt", show_col_types = FALSE)

# Display structure
glimpse(data1)

## 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,…

Summary

✓ Successfully imported 337,347 records with 12 columns from TEJ database

Question 2: Column Renaming

Task: Rename columns 2, 3, 5, 9, and 12 to more meaningful names

Code & Output

# Rename key columns
data2 <- data1 %>%
  rename(
    date  = 2,    # Date column
    id    = 3,    # TSE ID column
    name  = 5,    # English Short Name column
    price = 9,    # Close(NTD) column
    cap   = 12    # Market Cap.(NTD MN) column
  )

# Display structure
glimpse(data2)

## Rows: 337,347
## Columns: 12
## $ CO_ID             <chr> "1101 TCC", "1102 ACC", "1103 CHC", "1104 UCC", "110…
## $ date              <dbl> 20240102, 20240102, 20240102, 20240102, 20240102, 20…
## $ id                <dbl> 1101, 1102, 1103, 1104, 1108, 1109, 1110, 1201, 1203…
## $ `TSE Sector`      <chr> "01", "01", "01", "01", "01", "01", "01", "02", "02"…
## $ name              <chr> "TCC", "ACC", "CHC", "UCC", "Lucky Cement", "HSINGTA…
## $ `Open(NTD)`       <dbl> 32.5373, 37.2642, 17.7825, 26.0628, 14.1679, 16.1807…
## $ `High(NTD)`       <dbl> 32.5373, 37.4442, 17.7825, 26.1505, 14.1679, 16.2696…
## $ `Low(NTD)`        <dbl> 32.3038, 36.9492, 17.5953, 25.9750, 14.0343, 16.1362…
## $ price             <dbl> 32.3972, 37.0392, 17.6421, 26.0628, 14.0788, 16.1807…
## $ `Volume(1000S)`   <dbl> 14937, 6223, 171, 260, 442, 228, 57, 126, 48, 1849, …
## $ `Amount(NTD1000)` <dbl> 518751, 256522, 3240, 7736, 6992, 4159, 1075, 2409, …
## $ cap               <dbl> 262026, 145941, 14896, 19995, 6395, 6209, 10754, 964…

Summary

✓ Renamed 5 columns for improved readability: date, id, name, price, cap

Question 3: Data Reshaping

Task: Convert from long to wide format with proper data types

Code & Output

# Select columns and reshape
data3 <- data2 %>%
  select(id, date, price) %>%
  mutate(
    id = as.character(id),    # Convert ID to text
    date = ymd(date)           # Parse dates
  ) %>%
  pivot_wider(
    names_from = id,
    values_from = price
  )

# Display preview
head(data3, 10)

## # A tibble: 10 × 947
##    date       `1101` `1102` `1103` `1104` `1108` `1109` `1110` `1201` `1203`
##    <date>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
##  1 2024-01-02   32.4   37.0   17.6   26.1   14.1   16.2   18.3   18.2   55.3
##  2 2024-01-03   31.9   36.6   17.5   25.9   14.0   16.1   18.2   18.2   54.7
##  3 2024-01-04   31.9   37.0   17.5   25.5   14.1   16.1   18.4   18.1   54.1
##  4 2024-01-05   32.1   37.0   17.5   25.8   14.1   16.1   18.3   18.1   54.9
##  5 2024-01-08   32.0   37.2   17.6   25.7   14.2   16.1   18.3   18.1   54.6
##  6 2024-01-09   31.8   36.9   17.5   25.4   13.8   16.0   18.4   18.1   54.6
##  7 2024-01-10   31.5   36.6   17.4   25.4   13.7   16.0   18.5   18.0   53.4
##  8 2024-01-11   31.5   36.7   17.5   25.6   13.8   16.1   18.5   18.0   55.1
##  9 2024-01-12   31.5   36.6   17.5   25.6   13.8   16.0   18.4   18.0   54.4
## 10 2024-01-15   31.4   36.6   17.4   25.4   13.8   16.0   18.3   18.0   53.8
## # ℹ 937 more variables: `1210` <dbl>, `1213` <dbl>, `1215` <dbl>, `1216` <dbl>,
## #   `1217` <dbl>, `1218` <dbl>, `1219` <dbl>, `1220` <dbl>, `1225` <dbl>,
## #   `1227` <dbl>, `1229` <dbl>, `1231` <dbl>, `1232` <dbl>, `1233` <dbl>,
## #   `1234` <dbl>, `1235` <dbl>, `1236` <dbl>, `1301` <dbl>, `1303` <dbl>,
## #   `1304` <dbl>, `1305` <dbl>, `1307` <dbl>, `1308` <dbl>, `1309` <dbl>,
## #   `1310` <dbl>, `1312` <dbl>, `1313` <dbl>, `1314` <dbl>, `1315` <dbl>,
## #   `1316` <dbl>, `1319` <dbl>, `1321` <dbl>, `1323` <dbl>, `1324` <dbl>, …

Summary

✓ Transformed data to wide format: 358 rows × 947 columns

Question 4: Missing Value Detection

Task: Identify stocks with NA values and count them

Code & Output

# Count NA values for each stock
na_counts <- data3 %>%
  select(-date) %>%
  summarise(across(everything(), ~sum(is.na(.)))) %>%
  pivot_longer(everything(), names_to = "key", values_to = "value") %>%
  filter(value > 0) %>%
  arrange(key)

# Display results
na_counts

## # A tibble: 10 × 2
##    key   value
##    <chr> <int>
##  1 3716    160
##  2 4585    177
##  3 7722     18
##  4 7732     43
##  5 7736     53
##  6 7750    108
##  7 7765    151
##  8 7780    196
##  9 7788    198
## 10 7799    217

Summary

⚠️ Found 10 stocks with missing values

Question 5: Missing Value Imputation

Task: Fill NA values using forward fill (LOCF) method

Code & Output

# Apply LOCF (Last Observation Carried Forward)
data5 <- data3 %>%
  mutate(across(-date, ~na.locf(., na.rm = FALSE)))

# Display preview
head(data5, 10)

## # A tibble: 10 × 947
##    date       `1101` `1102` `1103` `1104` `1108` `1109` `1110` `1201` `1203`
##    <date>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
##  1 2024-01-02   32.4   37.0   17.6   26.1   14.1   16.2   18.3   18.2   55.3
##  2 2024-01-03   31.9   36.6   17.5   25.9   14.0   16.1   18.2   18.2   54.7
##  3 2024-01-04   31.9   37.0   17.5   25.5   14.1   16.1   18.4   18.1   54.1
##  4 2024-01-05   32.1   37.0   17.5   25.8   14.1   16.1   18.3   18.1   54.9
##  5 2024-01-08   32.0   37.2   17.6   25.7   14.2   16.1   18.3   18.1   54.6
##  6 2024-01-09   31.8   36.9   17.5   25.4   13.8   16.0   18.4   18.1   54.6
##  7 2024-01-10   31.5   36.6   17.4   25.4   13.7   16.0   18.5   18.0   53.4
##  8 2024-01-11   31.5   36.7   17.5   25.6   13.8   16.1   18.5   18.0   55.1
##  9 2024-01-12   31.5   36.6   17.5   25.6   13.8   16.0   18.4   18.0   54.4
## 10 2024-01-15   31.4   36.6   17.4   25.4   13.8   16.0   18.3   18.0   53.8
## # ℹ 937 more variables: `1210` <dbl>, `1213` <dbl>, `1215` <dbl>, `1216` <dbl>,
## #   `1217` <dbl>, `1218` <dbl>, `1219` <dbl>, `1220` <dbl>, `1225` <dbl>,
## #   `1227` <dbl>, `1229` <dbl>, `1231` <dbl>, `1232` <dbl>, `1233` <dbl>,
## #   `1234` <dbl>, `1235` <dbl>, `1236` <dbl>, `1301` <dbl>, `1303` <dbl>,
## #   `1304` <dbl>, `1305` <dbl>, `1307` <dbl>, `1308` <dbl>, `1309` <dbl>,
## #   `1310` <dbl>, `1312` <dbl>, `1313` <dbl>, `1314` <dbl>, `1315` <dbl>,
## #   `1316` <dbl>, `1319` <dbl>, `1321` <dbl>, `1323` <dbl>, `1324` <dbl>, …

Summary

✓ Applied LOCF imputation to handle missing values

Question 6: Data Cleaning

Task: Remove stocks that had NA values

Code & Output

# Get list of stocks with NA
stocks_with_na <- na_counts$key

# Remove those stocks
data6 <- data3 %>%
  select(-all_of(stocks_with_na))

# Display dimensions
dim(data6)

## [1] 358 937

Summary

✓ Cleaned dataset dimensions: 358 rows × 937 columns

Question 7: Daily Returns Calculation

Task: Convert to time series and compute daily returns

Code & Output

# Convert to xts format
data7_xts <- data6 %>%
  tk_xts(date_var = date)

# Calculate daily returns
returns_daily <- Return.calculate(data7_xts, method = "discrete")

# Show first 5 stocks, first 5 days (excluding first NA row)
returns_daily[-1, 1:5] %>% head(5)

##                    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

Summary

✓ Calculated discrete daily returns for 936 stocks

Question 8: Monthly Returns Calculation

Task: Aggregate to monthly frequency and compute returns

Code & Output

# Convert to monthly prices
monthly_prices <- to.monthly(data7_xts, OHLC = FALSE)

# Calculate monthly returns
returns_monthly <- Return.calculate(monthly_prices, method = "discrete")

# Show first 5 stocks, first 5 months (excluding first NA row)
returns_monthly[-1, 1:5] %>% head(5)

##                  1101        1102         1103        1104         1108
## Feb 2024  0.006272034  0.01760804 -0.008404066  0.01887014  0.036185231
## Mar 2024  0.001554899  0.02101398 -0.016950585  0.06397241  0.003170803
## Apr 2024 -0.003108301  0.05811288  0.057470064  0.11234002  0.072790295
## May 2024  0.029639309 -0.04920108  0.032611536 -0.03271487  0.000000000
## Jun 2024  0.036364817  0.05535680 -0.036845213  0.04852830 -0.011805133

Summary

✓ Computed monthly returns across 17 months

Question 9: Top Market Cap Firms

Task: Identify 20 largest firms by market cap at year-ends

Code & Output

# Find top 20 firms by market cap
data9 <- data2 %>%
  select(id, date, name, cap) %>%
  mutate(
    id = as.character(id),
    date = ymd(date)
  ) %>%
  filter(date == as.Date("2024-12-31") | date == as.Date("2025-06-30")) %>%
  mutate(year1 = year(date)) %>%
  group_by(year1) %>%
  arrange(desc(cap)) %>%
  slice(1:20) %>%
  ungroup() %>%
  mutate(cap1 = scales::dollar(cap, prefix = "$", suffix = "", 
                                big.mark = ",", accuracy = 1)) %>%
  select(date, year1, cap, cap1, id, name)

# Display results
data9

## # A tibble: 40 × 6
##    date       year1      cap cap1        id    name           
##    <date>     <dbl>    <dbl> <chr>       <chr> <chr>          
##  1 2024-12-31  2024 27877688 $27,877,688 2330  TSMC           
##  2 2024-12-31  2024  2556073 $2,556,073  2317  Hon Hai        
##  3 2024-12-31  2024  2266389 $2,266,389  2454  MediaTek       
##  4 2024-12-31  2024  1234015 $1,234,015  2881  Fubon Financial
##  5 2024-12-31  2024  1118242 $1,118,242  2308  DELTA          
##  6 2024-12-31  2024  1108574 $1,108,574  2382  QCI            
##  7 2024-12-31  2024  1001907 $1,001,907  2882  CATHAY FHC     
##  8 2024-12-31  2024   958045 $958,045    2412  CHT            
##  9 2024-12-31  2024   767154 $767,154    2891  CTBC Holding   
## 10 2024-12-31  2024   715219 $715,219    3711  ASEH           
## # ℹ 30 more rows

Summary

🏆 Identified top 20 companies by market capitalization for 2024 and 2025

Executive Summary

## 📊 ANALYSIS SUMMARY

## ═══════════════════════════════════════════════

## ✓ Total Records Processed:     337,347

## ✓ Clean Securities:            936

## ✓ Trading Days Analyzed:       358

## ✓ Securities with NA values:   10

## ✓ Daily Returns Calculated:    936 stocks

## ✓ Monthly Returns Calculated:  17 months

## 
## ═══════════════════════════════════════════════

## 🎯 All 9 questions completed successfully!

END OF REPORT

Financial Database Analysis - Midterm Examination

Temuulen, 114035127

2025-10-28

Question 1: Data Import

Code & Output

Summary

Question 2: Column Renaming

Code & Output

Summary

Question 3: Data Reshaping

Code & Output

Summary

Question 4: Missing Value Detection

Code & Output

Summary

Question 5: Missing Value Imputation

Code & Output

Summary

Question 6: Data Cleaning

Code & Output

Summary

Question 7: Daily Returns Calculation

Code & Output

Summary

Question 8: Monthly Returns Calculation

Code & Output

Summary

Question 9: Top Market Cap Firms

Code & Output

Summary

Executive Summary