US Stock Closing Prices Analysis

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

1. Import data file

# Import data using read_tsv()
data1 <- read_tsv("tej_day_price_2024_20250630.txt")

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

2. Replace column names

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
  ) %>%
  select(id, name, date, price, cap)

glimpse(data2)

## 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. Convert to wide format

data3 <- data2 %>%
  select(id, date, price) %>%
  mutate(
    id = as.character(id),           # Convert id to text
    date = ymd(as.character(date))   # Convert date to date format
  ) %>%
  spread(key = id, value = price)    # Change from long to wide format

# Show results
data3

## # A tibble: 358 × 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
## # ℹ 348 more rows
## # ℹ 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>, …

4. Find stocks with NA values

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

na_counts

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

5. Replace NA values

# Replace NA with closest available stock prices
data5 <- data3 %>%
  tk_xts(date_var = date) %>%
  na.locf() %>%                      # Replace NA with last observation carried forward
  tk_tbl(preserve_index = TRUE, rename_index = "date")

# Show results
data5

## # A tibble: 358 × 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
## # ℹ 348 more rows
## # ℹ 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>, …

6. Delete stocks with NA

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

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

# Show dimensions
dim(data6)

## [1] 358 937

7. Calculate daily returns

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

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

# Remove first row (NA) and show first 5 stocks, first 5 days
daily_returns_clean <- daily_returns[-1, ]
daily_returns_clean[1:5, 1: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

8. Calculate monthly returns

# Convert to monthly and calculate returns
monthly_prices <- to.monthly(data7_xts, OHLC = FALSE)
monthly_returns <- Return.calculate(monthly_prices, method = "discrete")

# Remove first row and show first 5 stocks, first 5 months
monthly_returns_clean <- monthly_returns[-1, ]
monthly_returns_clean[1:5, 1: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

9. Find largest cap firms

# Get the original data with cap information
data9 <- data2 %>%
  mutate(date = ymd(as.character(date))) %>%
  mutate(year1 = year(date))

# Find the 20 largest cap firms at year end of 2024 and 2025
top20_firms <- data9 %>%
  filter(date == as.Date("2024-12-31") | date == as.Date("2025-06-30")) %>%
  group_by(year1) %>%
  arrange(desc(cap)) %>%
  slice(1:20) %>%
  ungroup() %>%
  mutate(cap1 = paste0("$", format(cap, big.mark = ","))) %>%
  select(date, year1, cap, cap1, id, name)

# Show results
top20_firms

## # A tibble: 40 × 6
##    date       year1      cap cap1           id name           
##    <date>     <dbl>    <dbl> <chr>       <dbl> <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

US Stock Closing Prices Analysis

陳明發

2025-10-29

1. Import data file

2. Replace column names

3. Convert to wide format

4. Find stocks with NA values

5. Replace NA values

6. Delete stocks with NA

7. Calculate daily returns

8. Calculate monthly returns

9. Find largest cap firms