1. Load the Data

twse_raw <- read_csv(
  "TWSE_listed_stocks.csv",
  locale = locale(encoding = "UTF-8"),
  show_col_types = FALSE,
  name_repair = "minimal"
)

finmind_raw <- read_csv(
  "FinMind_listed_stocks.csv",
  locale = locale(encoding = "UTF-8"),
  show_col_types = FALSE,
  name_repair = "minimal"
)

# Remove spaces from column names
names(twse_raw) <- trimws(names(twse_raw))
names(finmind_raw) <- trimws(names(finmind_raw))

print(names(twse_raw))
## [1] "Stock ID"         "Name"             "Date"             "Industrial Group"
print(names(finmind_raw))
## [1] "Industrial Group" "Stock ID"         "Name"             "Date"

2. Clean the Official TWSE List

twse <- twse_raw %>%
  rename(
    stock_id = `Stock ID`,
    name = Name,
    listing_date = Date,
    industry = `Industrial Group`
  ) %>%
  mutate(
    stock_id = str_trim(as.character(stock_id)),
    name = str_trim(as.character(name)),
    listing_date = as.character(listing_date),
    industry = as.character(industry)
  ) %>%
  filter(str_detect(stock_id, "^\\d{4}$")) %>%
  filter(
    !str_detect(
      coalesce(name, ""),
      regex("ETF|基金|優先股|特別股|preferred", ignore_case = TRUE)
    ),
    !str_detect(
      coalesce(industry, ""),
      regex("ETF|基金|優先股|特別股", ignore_case = TRUE)
    )
  ) %>%
  distinct(stock_id, .keep_all = TRUE) %>%
  select(stock_id, name, listing_date, industry) %>%
  arrange(stock_id)

write_csv(twse, "TWSE_listed_common_stocks.csv")

kable(head(twse, 10), caption = "TWSE Listed Common Stocks")
TWSE Listed Common Stocks
stock_id name listing_date industry
1101 TCC 2/9/1962 Cement
1102 ACC 6/8/1962 Cement
1103 CHC 11/14/1969 Cement
1104 UCC 2/1/1971 Cement
1108 LUCKY CEMENT 6/6/1990 Cement
1109 HSINGTA 12/5/1991 Cement
1110 SE CEMENT 10/22/1994 Cement
1201 WEI CHUAN 2/9/1962 Food
1203 VE WONG 8/24/1964 Food
1210 GREATWALL 5/20/1978 Food

3. Clean the FinMind List

finmind <- finmind_raw %>%
  rename(
    industry = `Industrial Group`,
    stock_id = `Stock ID`,
    name = Name,
    data_date = Date
  ) %>%
  mutate(
    stock_id = str_trim(as.character(stock_id)),
    name = str_trim(as.character(name)),
    industry = as.character(industry),
    data_date = as.character(data_date)
  ) %>%
  filter(str_detect(stock_id, "^\\d{4}$")) %>%
  filter(
    !str_detect(
      coalesce(name, ""),
      regex("ETF|基金|優先股|特別股|preferred", ignore_case = TRUE)
    ),
    !str_detect(
      coalesce(industry, ""),
      regex("ETF|基金|優先股|特別股", ignore_case = TRUE)
    )
  ) %>%
  distinct(stock_id, .keep_all = TRUE) %>%
  transmute(
    stock_id,
    name,
    listing_date = NA_character_,
    industry,
    data_date
  ) %>%
  arrange(stock_id)

write_csv(finmind, "FinMind_listed_common_stocks.csv")

kable(head(finmind, 10), caption = "FinMind Stock List")
FinMind Stock List
stock_id name listing_date industry data_date
1101 台泥 NA 水泥工業 10/11/2026
1102 亞泥 NA 水泥工業 10/11/2026
1103 嘉泥 NA 水泥工業 10/11/2026
1104 環泥 NA 水泥工業 10/11/2026
1107 建台 NA 其他 12/4/2024
1108 幸福 NA 水泥工業 10/11/2026
1109 信大 NA 水泥工業 10/11/2026
1110 東泥 NA 水泥工業 10/11/2026
1201 味全 NA 食品工業 10/11/2026
1203 味王 NA 食品工業 10/11/2026

4. Compare Both Sources

comparison <- full_join(
  twse %>%
    mutate(in_TWSE = TRUE),
  finmind %>%
    select(stock_id, name, industry, data_date) %>%
    mutate(in_FinMind = TRUE),
  by = "stock_id",
  suffix = c("_TWSE", "_FinMind")
) %>%
  mutate(
    in_TWSE = coalesce(in_TWSE, FALSE),
    in_FinMind = coalesce(in_FinMind, FALSE)
  )

write_csv(comparison, "TWSE_FinMind_comparison.csv")

summary_table <- data.frame(
  Source = c("TWSE Official List", "FinMind List"),
  Total_Stocks = c(nrow(twse), nrow(finmind))
)

kable(summary_table, caption = "Number of Stocks by Source")
Number of Stocks by Source
Source Total_Stocks
TWSE Official List 1054
FinMind List 2541

5. Find Differences

only_twse <- anti_join(twse, finmind, by = "stock_id")
only_finmind <- anti_join(finmind, twse, by = "stock_id")

cat("Stocks only in TWSE:", nrow(only_twse), "\n")
## Stocks only in TWSE: 1
cat("Stocks only in FinMind:", nrow(only_finmind), "\n")
## Stocks only in FinMind: 1488
kable(head(only_twse, 10), caption = "Stocks Only in TWSE")
Stocks Only in TWSE
stock_id name listing_date industry
2883 KGI FINANCIAL 12/28/2001 Financial & Insurance 
kable(head(only_finmind, 10), caption = "Stocks Only in FinMind")
Stocks Only in FinMind
stock_id name listing_date industry data_date
1107 建台 NA 其他 12/4/2024
1230 聯成食 NA 電器電纜 12/3/2024
1240 茂生農經 NA 農業科技業 10/11/2026
1258 其祥-KY NA 食品工業 6/3/2023
1259 安心 NA 觀光事業 6/29/2023
1260 富味鄉 NA 食品工業 10/11/2026
1262 綠悅-KY NA 其他 7/1/2026
1264 德麥 NA 食品工業 10/11/2026
1268 漢來美食 NA 觀光事業 6/29/2023
1269 乾杯 NA 觀光餐旅 10/11/2026

6. Export Results

write_csv(only_twse, "Only_TWSE.csv")
write_csv(only_finmind, "Only_FinMind.csv")

cat("All output files have been created successfully.")
## All output files have been created successfully.