Quarterly Financial Data
Ider 115035118
2026-10-20
1. Load packages
library(dplyr) # data cleaning
library(tidyr) # drop_na()
library(knitr) # kable() tables2. Read the data file
The file is a tab-separated text file saved in
UTF-16 encoding, so a normal read.csv()
would not read it correctly. Put the CSV in the same folder as
this .Rmd file and check the file name below.
# File name (must be in the same folder as this .Rmd)
data_file <- "20261019021822.csv"
if (!file.exists(data_file)) {
stop("File not found: ", data_file,
"\nPut it in the same folder as this .Rmd. Current folder: ", getwd())
}
raw <- read.delim(
data_file,
sep = "\t", # columns are separated by tabs
fileEncoding = "UTF-16", # the file is UTF-16
colClasses = "character", # read everything as text first (IDs like "0050" stay safe)
quote = "", # company names may contain quote characters
check.names = FALSE # keep the original column names
)
# Structure of the raw file
dim(raw)## [1] 135022 6names(raw)## [1] "CO_ID"
## [2] "Date"
## [3] "Quarter"
## [4] "Market Value of Common Stocks at Quarter-End"
## [5] "Total equity"
## [6] "Net operating income (loss)"head(raw)3. Clean the data
clean <- raw |>
# Give the long column names simple names
rename(
market_value = `Market Value of Common Stocks at Quarter-End`,
total_equity = `Total equity`,
net_income = `Net operating income (loss)`
) |>
mutate(
# Remove extra spaces everywhere
across(everything(), trimws),
# CO_ID looks like "2330 TSMC": split it into the ID and the company name
stock_id = sub(" .*$", "", CO_ID), # text before the first space
name = sub("^\\S+\\s+", "", CO_ID), # text after the first space
# In this file a missing value is written as "." -> turn it into NA
market_value = na_if(market_value, "."),
total_equity = na_if(total_equity, "."),
net_income = na_if(net_income, "."),
# Convert text to numbers
market_value = as.numeric(market_value),
total_equity = as.numeric(total_equity),
net_income = as.numeric(net_income),
Quarter = as.integer(Quarter),
# Date looks like 201003 (year + month). Convert to a real date (first day of month)
date = as.Date(paste0(Date, "01"), format = "%Y%m%d"),
year = as.integer(format(date, "%Y"))
) |>
select(stock_id, name, date, year, Quarter, market_value, total_equity, net_income)
n_before <- nrow(clean)
firms_before <- n_distinct(clean$stock_id)Rows before filtering: 135022 (3002 different IDs)
4. Keep only regular (common) stocks
The file also contains items that are not ordinary stocks, such as
industry indexes (IDs starting with M), securities firms
with IDs like 0009A0, and foreign depositary receipts (IDs
starting with 91). We keep only 4-digit IDs that start with
1-9 and are not in the 91xx depositary-receipt range.
is_common <- grepl("^[1-9][0-9]{3}$", clean$stock_id) & # exactly 4 digits, not starting with 0
!grepl("^91", clean$stock_id) # remove 91xx depositary receipts
# Examples of what is removed (to check the filter)
kable(head(distinct(clean[!is_common, ], stock_id, name), 15),
caption = "Examples of removed IDs")| stock_id | name |
|---|---|
| 000930 | H.N.S |
| 000116 | JihSun SCL |
| 000960 | FBS |
| 000980 | Yuanta |
| 0009A0 | SinoPac |
| 9102 | Eastern Asia Technology |
| 911609 | Yangzijiang Shipbuilding (Hold |
| 911616 | Dukang Distillers Holdings Ltd |
| 911626 | Multi Sports Holdings Ltd. |
| 9105 | Cal-Comp Electronics (Thai) |
| 910579 | Oceanus Group Limited |
| 911602 | Hwa Fong Rubber (Thailand) Pub |
| 911606 | Super Group Ltd. |
| 911610 | United Envirotech Ltd. |
| 911612 | Hu An Cable Holdings Ltd. |
stocks <- clean[is_common, ]
# Remove duplicated stock-quarter rows (if any)
stocks <- distinct(stocks, stock_id, date, .keep_all = TRUE)cat("Rows after filtering:", nrow(stocks), "\n")## Rows after filtering: 128817cat("Firms after filtering:", n_distinct(stocks$stock_id), "\n")## Firms after filtering: 2875cat("Period:", format(min(stocks$date)), "to", format(max(stocks$date)), "\n")## Period: 2010-03-01 to 2026-06-015. Missing values
missing_table <- data.frame(
variable = c("market_value", "total_equity", "net_income"),
missing = c(sum(is.na(stocks$market_value)),
sum(is.na(stocks$total_equity)),
sum(is.na(stocks$net_income)))
)
missing_table$percent <- round(100 * missing_table$missing / nrow(stocks), 2)
kable(missing_table, caption = "Missing values in the cleaned data")| variable | missing | percent |
|---|---|---|
| market_value | 9111 | 7.07 |
| total_equity | 5 | 0.00 |
| net_income | 2957 | 2.30 |
6. Summary statistics
Values are in the original units of the file (as provided by the data source).
kable(summary(stocks[, c("market_value", "total_equity", "net_income")]),
caption = "Summary of the numeric variables")| market_value | total_equity | net_income | |
|---|---|---|---|
| Min. :2.494e+04 | Min. : -51575825 | Min. :-38720187 | |
| 1st Qu.:1.424e+06 | 1st Qu.: 862031 | 1st Qu.: -5761 | |
| Median :3.366e+06 | Median : 1986152 | Median : 31610 | |
| Mean :2.461e+07 | Mean : 13941534 | Mean : 347663 | |
| 3rd Qu.:9.446e+06 | 3rd Qu.: 5773830 | 3rd Qu.: 128717 | |
| Max. :6.250e+10 | Max. :6474470981 | Max. :766602651 | |
| NAs :9111 | NAs :5 | NAs :2957 |
7. First 20 rows
kable(head(stocks, 20), caption = "First 20 rows of the cleaned data")| stock_id | name | date | year | Quarter | market_value | total_equity | net_income |
|---|---|---|---|---|---|---|---|
| 1101 | TCC | 2010-03-01 | 2010 | 1 | 98600641 | 102157504 | 2152334 |
| 1104 | UCC | 2010-03-01 | 2010 | 1 | 9813229 | 12373734 | -102741 |
| 1108 | Lucky Cement | 2010-03-01 | 2010 | 1 | 3290520 | 4324808 | -15334 |
| 1102 | ACC | 2010-03-01 | 2010 | 1 | 92557785 | 87422478 | 762094 |
| 1109 | HSINGTA | 2010-03-01 | 2010 | 1 | 4273150 | 6911221 | 12543 |
| 1103 | CHC | 2010-03-01 | 2010 | 1 | 11234775 | 18019997 | -13640 |
| 1201 | WEI CHUAN | 2010-03-01 | 2010 | 1 | 18749597 | 5829315 | 218832 |
| 1107 | Chien Tai Cement | 2010-03-01 | 2010 | 1 | 590386 | -1153228 | -39860 |
| 1203 | Ve Wong | 2010-03-01 | 2010 | 1 | 5844446 | 5173583 | 181553 |
| 1110 | SE CEMENT | 2010-03-01 | 2010 | 1 | 6549400 | 8299983 | 19820 |
| 1216 | Uni-President | 2010-03-01 | 2010 | 1 | 140891010 | 109689846 | 5043903 |
| 1210 | GREATWALL | 2010-03-01 | 2010 | 1 | 15381807 | 14625557 | 231197 |
| 1218 | Taisun | 2010-03-01 | 2010 | 1 | 4099110 | 3719501 | 16107 |
| 1213 | Oceanic Bev. | 2010-03-01 | 2010 | 1 | 607405 | 601241 | 7572 |
| 1225 | FOPCO | 2010-03-01 | 2010 | 1 | 2538120 | 2342779 | 57715 |
| 1215 | CPE | 2010-03-01 | 2010 | 1 | 3364377 | 3570297 | 105255 |
| 1227 | SFC | 2010-03-01 | 2010 | 1 | 18706334 | 6551674 | 823567 |
| 1217 | AGV | 2010-03-01 | 2010 | 1 | 5775188 | 5025287 | 16628 |
| 1229 | LHIHC | 2010-03-01 | 2010 | 1 | 12583701 | 17145372 | 40550 |
| 1219 | FWUSOW | 2010-03-01 | 2010 | 1 | 3456268 | 3674478 | 35802 |
8. Number of firms in each quarter
firms_per_quarter <- stocks |>
group_by(date) |>
summarise(number_of_firms = n_distinct(stock_id), .groups = "drop")
kable(tail(firms_per_quarter, 12), caption = "Last 12 quarters")| date | number_of_firms |
|---|---|
| 2023-09-01 | 1953 |
| 2023-12-01 | 2343 |
| 2024-03-01 | 1997 |
| 2024-06-01 | 2420 |
| 2024-09-01 | 2024 |
| 2024-12-01 | 2412 |
| 2025-03-01 | 2058 |
| 2025-06-01 | 2455 |
| 2025-09-01 | 2016 |
| 2025-12-01 | 2444 |
| 2026-03-01 | 2046 |
| 2026-06-01 | 2435 |
plot(firms_per_quarter$date, firms_per_quarter$number_of_firms,
type = "l", xlab = "Quarter", ylab = "Number of firms",
main = "Number of firms in the data over time")
9. Save the cleaned data
write.csv(stocks, "quarterly_data_clean.csv",
row.names = FALSE, fileEncoding = "UTF-8")
cat("Saved", nrow(stocks), "rows to", file.path(getwd(), "quarterly_data_clean.csv"), "\n")## Saved 128817 rows to D:/079/quarterly_data_clean.csv