# Files (TEJ exports; tab-delimited, UTF-16)
dl_dir <- file.path(Sys.getenv("USERPROFILE"), "Downloads", "rstudio")
stock_file <- file.path(dl_dir, "stock_data", "20261113015642.csv")
ff3_file <- file.path(dl_dir, "ff3", "20261113015903.csv")
# Options
ret_in_percent <- TRUE # TEJ returns and factors are in percent
ff3_variant <- "Y9999 " # "Y9999 TSEC" = all TSEC stocks (trailing space excludes "Y9999L")
require_complete <- TRUE # TRUE: replace stocks without 36 months by the next largest
# Window 1
rank_month <- ymd("2020-12-01") # month used to pick the 50 largest stocks
window_start <- ymd("2018-01-01")
window_end <- ymd("2020-12-01")
n_window <- 36stopifnot(file.exists(stock_file), file.exists(ff3_file))
# Detect encoding; fall back to tab-delimited if comma-delimited gives one column
read_tej <- function(path) {
enc <- guess_encoding(path, n_max = 20000)$encoding[1]
if (is.na(enc) || enc == "ASCII") enc <- "UTF-8"
loc <- locale(encoding = enc)
df <- read_csv(path, locale = loc, na = c("", "NA", "-"), show_col_types = FALSE)
if (ncol(df) == 1) {
df <- read_tsv(path, locale = loc, na = c("", "NA", "-"), show_col_types = FALSE)
}
rename_with(df, str_trim)
}
stock_data <- read_tej(stock_file)
ff3_data <- read_tej(ff3_file)
# Find a column by regex and stop with a clear message if it is missing
find_col <- function(df, pattern) {
hit <- str_subset(names(df), regex(pattern, ignore_case = TRUE))
if (length(hit) == 0) stop("Column not found: ", pattern, " | Available: ",
paste(names(df), collapse = " | "))
hit[1]
}
stock_cols <- c(co_id = find_col(stock_data, "^co_id"),
date = find_col(stock_data, "^date"), # "YYYY/MM" only holds "Current"
mv = find_col(stock_data, "^market cap"),
ret = find_col(stock_data, "^roi_month"))
ff3_cols <- c(id = find_col(ff3_data, "^co_id"),
date = find_col(ff3_data, "^date"),
mkt_rf = find_col(ff3_data, "risk premium"),
smb = find_col(ff3_data, "^size"),
hml = find_col(ff3_data, "^book"))
sort(unique(ff3_data[[ff3_cols["id"]]])) # the 8 FF3 variants available## [1] "Y8888 TSEC Non-Banking Subindex"
## [2] "Y8888L TSEC Non-Banking Subindex"
## [3] "Y9999 TSEC"
## [4] "Y9999L TSEC"
## [5] "Z8888 TSEC+GreTai Non-Banking index"
## [6] "Z8888L TSEC+GreTai Non-Banking index"
## [7] "Z9999 TSEC+GreTai"
## [8] "Z9999L TSEC+GreTai"# "201801", "2018/01", "2018-01-31", ... -> first day of that month
to_ym <- function(x) {
x <- as.character(x)
m <- str_match(x, "^\\s*(\\d{4})\\D+(\\d{1,2})")
d <- str_remove_all(x, "\\D")
yr <- coalesce(as.integer(m[, 2]), as.integer(str_sub(d, 1, 4)))
mo <- coalesce(as.integer(m[, 3]), as.integer(str_sub(d, 5, 6)))
make_date(yr, mo, 1)
}
pct <- if (ret_in_percent) 100 else 1
stock_clean <- stock_data %>%
select(all_of(stock_cols)) %>%
mutate(co_id = as.character(co_id),
ym = to_ym(date),
mv = parse_number(as.character(mv)),
ret = parse_number(as.character(ret)) / pct) %>%
filter(!is.na(ym), !is.na(mv))
ff3_clean <- ff3_data %>%
select(all_of(ff3_cols)) %>%
filter(str_starts(id, fixed(ff3_variant))) %>%
mutate(ym = to_ym(date),
across(c(mkt_rf, smb, hml), ~ parse_number(as.character(.x)) / pct)) %>%
filter(!is.na(ym))
stopifnot("ff3_variant matches no rows" = nrow(ff3_clean) > 0,
"more than one FF3 row in some month" = !anyDuplicated(ff3_clean$ym))range(stock_clean$ym)## [1] "2017-11-01" "2025-11-01"range(ff3_clean$ym)## [1] "2017-11-01" "2025-11-01"summary(stock_clean$ret) # monthly return, decimal## Min. 1st Qu. Median Mean 3rd Qu. Max.
## -0.636090 -0.042856 0.000001 0.013516 0.048781 4.326087summary(ff3_clean$mkt_rf) # monthly market premium, decimal## Min. 1st Qu. Median Mean 3rd Qu. Max.
## -0.13896 -0.01865 0.01129 0.01282 0.04244 0.14720if (max(stock_clean$ym) < ymd("2025-12-01") || max(ff3_clean$ym) < ymd("2025-12-01")) {
cat("WARNING: data end before 2025/12. Re-download from TEJ through 2025/12.\n")
}## WARNING: data end before 2025/12. Re-download from TEJ through 2025/12.top50_monthly <- stock_clean %>%
filter(ym >= ymd("2017-11-01"), ym <= ymd("2025-12-01")) %>%
group_by(ym) %>%
slice_max(mv, n = 50, with_ties = FALSE) %>%
mutate(rank = row_number()) %>%
ungroup()
# Monthly returns of the 50 largest stocks
top50_returns <- select(top50_monthly, ym, rank, co_id, mv, ret)
write_csv(top50_returns, file.path(dl_dir, "top50_monthly_returns.csv"))
top50_ids <- top50_monthly %>% filter(ym == rank_month) %>% pull(co_id)
length(top50_ids) # should be 50## [1] 50# List of the 50 largest stocks (row = month, column = rank)
top50_monthly %>%
select(ym, rank, co_id) %>%
pivot_wider(names_from = rank, values_from = co_id, names_prefix = "rank_") %>%
rmarkdown::paged_table()Rows are the 36 months, columns are the 50 largest stocks of 2020/12, then Mkt, SMB, HML.
# All stocks in the ranking month, largest first
ranked <- stock_clean %>%
filter(ym == rank_month) %>%
distinct(co_id, .keep_all = TRUE) %>%
arrange(desc(mv))
# Stocks with returns in all 36 months
complete_ids <- stock_clean %>%
filter(between(ym, window_start, window_end), !is.na(ret)) %>%
distinct(co_id, ym) %>%
count(co_id) %>%
filter(n == n_window) %>%
pull(co_id)
selected_ids <- ranked %>%
filter(!require_complete | co_id %in% complete_ids) %>%
slice_head(n = 50) %>%
pull(co_id)
# Differences from the plain top 50
list(dropped = setdiff(top50_ids, selected_ids),
added = setdiff(selected_ids, top50_ids))## $dropped
## [1] "3711 ASEH" "6770 PSMC"
##
## $added
## [1] "2105 CST" "3481 INX"final_window1_table <- stock_clean %>%
filter(co_id %in% selected_ids, between(ym, window_start, window_end)) %>%
distinct(ym, co_id, .keep_all = TRUE) %>%
mutate(co_id = factor(co_id, levels = selected_ids)) %>%
select(ym, co_id, ret) %>%
pivot_wider(names_from = co_id, values_from = ret, names_sort = TRUE) %>%
left_join(select(ff3_clean, ym, Mkt = mkt_rf, SMB = smb, HML = hml), by = "ym") %>%
arrange(ym) %>%
mutate(Date = paste0(year(ym), "/", month.abb[month(ym)]), .before = 1) %>%
select(-ym)
dim(final_window1_table) # 36 x 54## [1] 36 54sum(is.na(final_window1_table)) # 0 when require_complete = TRUE## [1] 0final_window1_table %>%
mutate(across(where(is.numeric), ~ round(.x, 4))) %>%
rmarkdown::paged_table(options = list(rows.print = 36, cols.print = 8))Stock order in the table (largest market cap in 2020/12 first):
stock_map <- ranked %>%
filter(co_id %in% selected_ids) %>%
transmute(stock = paste0("stock", row_number()), co_id, mv)
rmarkdown::paged_table(stock_map)write_csv(final_window1_table, file.path(dl_dir, "window1_table.csv"))
write_csv(stock_map, file.path(dl_dir, "window1_stock_map.csv"))