hw5
kevin_115035132
19 October 2026
1 Title and Research Objective
This report prepares and examines quarterly financial data
for firms in the Taiwan stock market for the period
January 2010 to June 2026. The data come from the
Taiwan Economic Journal (TEJ) database and were supplied as the Excel
workbook TEJ_Quarterly_2010_2026_Combined.xlsx.
The objectives are to:
- collect quarterly operating profit, book value and market value (total monetary amounts, not per-share values);
- inspect, validate and clean the dataset in a transparent and reproducible way, without silently deleting observations;
- document data quality, coverage and limitations;
- describe the quarterly evolution of the three variables; and
- export the processed data for later use.
How to read this report. Statements labelled Observed are computed directly from the data by the code in this document. Statements labelled Assumption or Interpretation are judgements that the data alone cannot prove.
2 Data Source and Variable Definitions
2.1 Parameters (edit here if your file is stored elsewhere)
By default the workbook is expected in the same folder as
this .Rmd file. When you knit in RStudio, the
working directory is automatically that folder. To use a different
location, either (a) change data_file below to a full path
such as
"C:/Users/YourName/Documents/TEJ_Quarterly_2010_2026_Combined.xlsx"
(use forward slashes / on Windows), or (b) in the console
run setwd("path/to/folder"), or use Session > Set
Working Directory > To Source File Location. When running chunks
interactively (not knitting), a file-selection dialog opens if the file
is not found.
data_file <- "TEJ_Quarterly_2010_2026_Combined.xlsx"
sheet_name <- "TEJ Quarterly Data"
start_date <- ymd("2010-01-01")
end_date <- ymd("2026-06-30")
out_xlsx <- "Taiwan_Stock_Market_Processed_Data.xlsx"
out_csv <- "Taiwan_Stock_Market_Cleaned_Data.csv"
if (!file.exists(data_file)) {
if (interactive()) {
message("File not found in: ", getwd(), " -- please select the TEJ workbook.")
data_file <- file.choose()
} else {
stop("Cannot find '", data_file, "' in the working directory (", getwd(), "). ",
"Place the workbook next to this .Rmd file or edit `data_file` above.")
}
}
if (!(sheet_name %in% readxl::excel_sheets(data_file))) {
stop("Worksheet '", sheet_name, "' not found. Available sheets: ",
paste(readxl::excel_sheets(data_file), collapse = ", "))
}
cat("Working directory:", getwd(), "\nInput file:", data_file, "\n")## Working directory: /Users/kvn.andretty/Downloads
## Input file: TEJ_Quarterly_2010_2026_Combined.xlsx2.2 Variable mapping
var_map <- tibble::tibble(
`Original TEJ column` = c("COID", "COID_Name", "YYYY/MM",
"Net operating income (loss)", "Total equity",
"Market Value of Common Stocks at Quarter-End"),
`Standardised variable` = c("company_id", "company_name", "report_date / quarter",
"operating_profit", "book_value", "market_value"),
Meaning = c("TEJ company / series code", "Company (or series) name",
"Reporting month; converted to quarter-end date and calendar quarter",
"Operating profit (loss) for the quarter, total amount",
"Book value of equity, total amount",
"Market capitalisation of common stock at quarter end, total amount")
)
kbl_tbl(var_map, caption = "Mapping of original TEJ columns to standardised variables")| Original TEJ column | Standardised variable | Meaning |
|---|---|---|
| COID | company_id | TEJ company / series code |
| COID_Name | company_name | Company (or series) name |
| YYYY/MM | report_date / quarter | Reporting month; converted to quarter-end date and calendar quarter |
| Net operating income (loss) | operating_profit | Operating profit (loss) for the quarter, total amount |
| Total equity | book_value | Book value of equity, total amount |
| Market Value of Common Stocks at Quarter-End | market_value | Market capitalisation of common stock at quarter end, total amount |
All three financial variables are kept as total monetary amounts in their original units. No per-share conversion is performed anywhere in this document.
Monetary unit. The workbook itself contains no metadata about currency or scale, so the unit cannot be confirmed from the file. Section 3.3 reports a plausibility check (TSMC, code 2330) that suggests New Taiwan dollars in thousands, but this remains an unconfirmed inference. Please verify it in the TEJ data dictionary before quoting units in your submission.
3 Data Import and Initial Inspection
3.1 Import
All columns are first read as text so that nothing is silently
coerced (for example, company codes with leading zeros such as
000116). Numeric conversion is done later, with explicit
checks.
raw <- readxl::read_excel(data_file, sheet = sheet_name, col_types = "text")
expected_cols <- c("COID", "COID_Name", "YYYY/MM", "Total equity",
"Net operating income (loss)",
"Market Value of Common Stocks at Quarter-End")
missing_cols <- setdiff(expected_cols, names(raw))
if (length(missing_cols) > 0) {
stop("Expected column(s) missing from the worksheet: ", paste(missing_cols, collapse = ", "))
}3.2 First 10 rows, column names and data types
kbl_tbl(head(raw, 10), caption = "First 10 rows of the raw workbook (all read as text)")| COID | COID_Name | YYYY/MM | Total equity | Net operating income (loss) | Market Value of Common Stocks at Quarter-End |
|---|---|---|---|---|---|
| 000116 | JihSun SCL | 2014/12 | 21909608 | 125285 | NA |
| 000930 | H.N.S | 2014/12 | 11846315 | 34087 | NA |
| 000960 | FBS | 2014/12 | 31332984 | -67346 | NA |
| 000980 | Yuanta | 2014/12 | 104160275 | 282429 | NA |
| 0009A0 | SinoPac | 2014/12 | 25121285 | 578710 | NA |
| 1101 | TCC | 2014/12 | 162017681 | 4690964 | 160240395 |
| 1102 | ACC | 2014/12 | 161727226 | 2357783 | 131096433 |
| 1103 | CHC | 2014/12 | 19282058 | -51768 | 10992116 |
| 1104 | UCC | 2014/12 | 15797970 | 47488 | 15892000 |
| 1107 | Chien Tai Cement | 2014/12 | 778447 | -110 | NA |
# Types that readxl would guess by default (first 1,000 rows) vs. how we imported them
guess <- readxl::read_excel(data_file, sheet = sheet_name, n_max = 1000)
types_tbl <- tibble::tibble(
Column = names(raw),
`readxl guessed type (first 1,000 rows)` = vapply(guess[names(raw)], function(x) class(x)[1], character(1)),
`Imported as` = "character (converted explicitly in cleaning step)"
)
kbl_tbl(types_tbl, caption = "Column names and data types")| Column | readxl guessed type (first 1,000 rows) | Imported as |
|---|---|---|
| COID | character | character (converted explicitly in cleaning step) |
| COID_Name | character | character (converted explicitly in cleaning step) |
| YYYY/MM | character | character (converted explicitly in cleaning step) |
| Total equity | numeric | character (converted explicitly in cleaning step) |
| Net operating income (loss) | numeric | character (converted explicitly in cleaning step) |
| Market Value of Common Stocks at Quarter-End | numeric | character (converted explicitly in cleaning step) |
3.3 Size, firms and dates
ym_tmp <- ymd(paste0(raw$`YYYY/MM`, "/01"), quiet = TRUE)
n_raw <- nrow(raw)
n_ids_raw <- n_distinct(raw$COID)
first_raw <- min(ym_tmp, na.rm = TRUE)
last_raw <- max(ym_tmp, na.rm = TRUE)
n_bad_dates <- sum(is.na(ym_tmp))
kbl_tbl(tibble::tibble(
Item = c("Observations (rows)", "Unique company / series codes (COID)",
"Earliest reporting month", "Latest reporting month",
"Unparseable YYYY/MM values", "Distinct reporting months"),
Value = c(n_fmt(n_raw), n_fmt(n_ids_raw), format(first_raw, "%Y-%m"),
format(last_raw, "%Y-%m"), n_fmt(n_bad_dates), n_fmt(n_distinct(raw$`YYYY/MM`)))
), caption = "Basic dimensions of the raw workbook")| Item | Value |
|---|---|
| Observations (rows) | 135,022 |
| Unique company / series codes (COID) | 3,002 |
| Earliest reporting month | 2010-03 |
| Latest reporting month | 2026-06 |
| Unparseable YYYY/MM values | 0 |
| Distinct reporting months | 66 |
3.4 Is the data already quarterly?
months_tbl <- raw %>%
mutate(month = month(ymd(paste0(`YYYY/MM`, "/01"), quiet = TRUE))) %>%
count(month, name = "observations")
kbl_tbl(months_tbl, caption = "Observations by reporting month")| month | observations |
|---|---|
| 3 | 31,514 |
| 6 | 38,360 |
| 9 | 29,566 |
| 12 | 35,582 |
max_per_id_month <- raw %>% count(COID, `YYYY/MM`) %>% summarise(m = max(n)) %>% pull(m)
already_quarterly <- all(months_tbl$month %in% c(3, 6, 9, 12)) && max_per_id_month == 1Observed: reporting months are limited to March, June, September and December, and the maximum number of rows per company-month is 1. Therefore the data are already at quarterly frequency and do not need to be aggregated.
3.5 Unit plausibility check (TSMC, code 2330)
tsmc <- raw %>% filter(COID == "2330", `YYYY/MM` %in% c("2014/12", "2025/12")) %>%
select(COID, COID_Name, `YYYY/MM`, `Total equity`, `Net operating income (loss)`,
`Market Value of Common Stocks at Quarter-End`)
kbl_tbl(tsmc, caption = "TSMC rows used for a unit plausibility check (raw values)")| COID | COID_Name | YYYY/MM | Total equity | Net operating income (loss) | Market Value of Common Stocks at Quarter-End |
|---|---|---|---|---|---|
| 2330 | TSMC | 2014/12 | 1046328332 | 88216285 | 3656082342 |
| 2330 | TSMC | 2025/12 | 5460795283 | 564902413 | 40195413750 |
Interpretation (unconfirmed): TSMC’s equity in 2014 is of the order of 1,000,000,000 in the file. A figure of that size is only sensible for a company of TSMC’s scale if the unit is NT$ thousands (about NT\(1 trillion). Under that inference, dividing the raw values by 10^9 expresses them in **NT\) trillion**, which is the scale used in the charts below. This is an assumption and is labelled “inferred” in every chart.
unit_div <- 1e9
unit_lbl <- "NT$ trillion (inferred from NT$ thousands; unit unconfirmed)"4 Data Cleaning and Preparation
4.1 Standardising names and converting types
raw_clean_names <- janitor::clean_names(raw)
cat("janitor::clean_names() produced:\n", paste(names(raw_clean_names), collapse = "\n "), "\n")## janitor::clean_names() produced:
## coid
## coid_name
## yyyy_mm
## total_equity
## net_operating_income_loss
## market_value_of_common_stocks_at_quarter_enddf0 <- raw_clean_names %>%
rename(company_id = coid,
company_name = coid_name,
ym_raw = yyyy_mm,
book_value_raw = total_equity,
operating_profit_raw = net_operating_income_loss,
market_value_raw = market_value_of_common_stocks_at_quarter_end) %>%
mutate(across(c(company_id, company_name, ym_raw), ~ trimws(.x)),
across(c(book_value_raw, operating_profit_raw, market_value_raw),
~ dplyr::na_if(trimws(.x), "")))
to_num <- function(x) suppressWarnings(as.numeric(x))
df1 <- df0 %>%
mutate(
report_month = ymd(paste0(ym_raw, "/01"), quiet = TRUE),
report_date = ceiling_date(report_month, "month") - days(1), # quarter-end date
quarter = paste0(year(report_date), " Q", quarter(report_date)),
book_value = to_num(book_value_raw),
operating_profit_reported = to_num(operating_profit_raw),
market_value = to_num(market_value_raw)
)4.2 Validation checks before any exclusion
chk <- tibble::tibble(
Check = c(
"YYYY/MM not in the form YYYY/MM",
"YYYY/MM could not be converted to a date",
"Reporting month is not 3, 6, 9 or 12",
"Blank / missing company_id",
"Non-numeric text in book value (original not blank, conversion failed)",
"Non-numeric text in operating profit",
"Non-numeric text in market value",
"Infinite values in any financial variable",
"company_id mapped to more than one company_name (rows)",
"company_name mapped to more than one company_id (rows)"
),
Rows = c(
sum(!grepl("^[0-9]{4}/[0-9]{2}$", df1$ym_raw)),
sum(is.na(df1$report_date)),
sum(!month(df1$report_date) %in% c(3, 6, 9, 12), na.rm = TRUE),
sum(is.na(df1$company_id) | df1$company_id == ""),
sum(!is.na(df1$book_value_raw) & is.na(df1$book_value)),
sum(!is.na(df1$operating_profit_raw) & is.na(df1$operating_profit_reported)),
sum(!is.na(df1$market_value_raw) & is.na(df1$market_value)),
sum(is.infinite(df1$book_value) | is.infinite(df1$operating_profit_reported) | is.infinite(df1$market_value)),
df1 %>% group_by(company_id) %>% filter(n_distinct(company_name) > 1) %>% nrow(),
df1 %>% group_by(company_name) %>% filter(n_distinct(company_id) > 1) %>% nrow()
)
)
kbl_tbl(chk, caption = "Structural validation checks (0 = no problem found)")| Check | Rows |
|---|---|
| YYYY/MM not in the form YYYY/MM | 0 |
| YYYY/MM could not be converted to a date | 0 |
| Reporting month is not 3, 6, 9 or 12 | 0 |
| Blank / missing company_id | 0 |
| Non-numeric text in book value (original not blank, conversion failed) | 0 |
| Non-numeric text in operating profit | 0 |
| Non-numeric text in market value | 0 |
| Infinite values in any financial variable | 0 |
| company_id mapped to more than one company_name (rows) | 0 |
| company_name mapped to more than one company_id (rows) | 8,728 |
if (chk$Rows[2] > 0) stop("Some dates could not be parsed; inspect `df1` before continuing.")4.3 Restricting to January 2010 – June 2026
exclusion_log <- tibble::tibble(Step = character(), Rows_affected = integer(), Reason = character(), Action = character())
n_outside <- sum(df1$report_date < start_date | df1$report_date > end_date)
exclusion_log <- bind_rows(exclusion_log, tibble::tibble(
Step = "Date window 2010-01-01 to 2026-06-30", Rows_affected = n_outside,
Reason = "Report date outside the assignment window",
Action = ifelse(n_outside == 0, "No rows removed (all rows already inside window)", "Rows removed")))
df2 <- df1 %>% filter(report_date >= start_date, report_date <= end_date)
expected_q <- tibble::tibble(q_start = seq(ymd("2010-01-01"), ymd("2026-04-01"), by = "3 months")) %>%
mutate(quarter = paste0(year(q_start), " Q", quarter(q_start)),
report_date = ceiling_date(q_start, "quarter") - days(1))
if (nrow(expected_q) != 66) stop("Expected 66 calendar quarters, got ", nrow(expected_q))
observed_q <- n_distinct(df2$quarter)
cat("Expected quarters:", nrow(expected_q), "| Observed quarters:", observed_q,
"| Missing quarters:", paste(setdiff(expected_q$quarter, df2$quarter), collapse = ", "), "\n")## Expected quarters: 66 | Observed quarters: 66 | Missing quarters:4.4 Duplicate company-quarter observations
dups <- df2 %>% count(company_id, quarter) %>% filter(n > 1)
n_dup_pairs <- nrow(dups)
n_dup_rows <- if (n_dup_pairs > 0) sum(dups$n) else 0L
cat("Company-quarter pairs with more than one row:", n_dup_pairs, "\n")## Company-quarter pairs with more than one row: 0if (n_dup_pairs > 0) {
# Investigate: show the conflicting rows and their reporting dates
dup_detail <- df2 %>% semi_join(dups, by = c("company_id", "quarter")) %>%
arrange(company_id, report_date) %>%
select(company_id, company_name, ym_raw, quarter, operating_profit_reported, book_value, market_value)
print(kbl_tbl(head(dup_detail, 50), caption = "Duplicate company-quarter rows (first 50)"))
# Rule: do NOT sum. Keep the single row with the most non-missing financial values.
df2 <- df2 %>%
mutate(n_nonmissing = (!is.na(operating_profit_reported)) + (!is.na(book_value)) + (!is.na(market_value))) %>%
group_by(company_id, quarter) %>% arrange(desc(n_nonmissing), .by_group = TRUE) %>% slice(1) %>% ungroup() %>%
select(-n_nonmissing)
exclusion_log <- bind_rows(exclusion_log, tibble::tibble(
Step = "Duplicate company-quarter", Rows_affected = n_dup_rows - n_dup_pairs,
Reason = "Several rows for the same company and quarter; financial statements are NOT summed",
Action = "Kept the row with the most non-missing values (investigate manually)"))
} else {
exclusion_log <- bind_rows(exclusion_log, tibble::tibble(
Step = "Duplicate company-quarter", Rows_affected = 0L,
Reason = "No company has more than one row in the same calendar quarter",
Action = "No deduplication or aggregation needed"))
}4.5 Classifying codes: companies versus index / aggregate series
TEJ exports can contain series that are not individual firms. The
code patterns and names in this file were inspected (see the examples
table). If market-index or sector-index series were included in
aggregate totals they would double-count the whole
market. They are therefore flagged, not deleted: every
row stays in the cleaned dataset, but only rows with
analysis_sample = TRUE are used for aggregates, charts and
ratios.
df3 <- df2 %>%
mutate(
entity_type = case_when(
grepl("^[0-9]{4}$", company_id) ~ "Company (4-digit code)",
grepl("^91[0-9]{4}$", company_id) ~ "Foreign / secondary listing (91xxxx)",
grepl("^000", company_id) ~ "Securities-firm code (000xxx, no market value)",
TRUE ~ "Index / sector aggregate (non-company)"
),
analysis_sample = entity_type %in% c("Company (4-digit code)",
"Foreign / secondary listing (91xxxx)")
)
entity_summary <- df3 %>% group_by(entity_type, analysis_sample) %>%
summarise(codes = n_distinct(company_id), rows = n(),
example_codes_and_names = paste(head(unique(paste(company_id, company_name, sep = ": ")), 3), collapse = " | "),
.groups = "drop")
kbl_tbl(entity_summary, caption = "Types of codes in the workbook and their use in the analysis")| entity_type | analysis_sample | codes | rows | example_codes_and_names |
|---|---|---|---|---|
| Company (4-digit code) | TRUE | 2,885 | 129,069 | 1101: TCC | 1102: ACC | 1103: CHC |
| Foreign / secondary listing (91xxxx) | TRUE | 26 | 595 | 910069: Serial System Ltd. | 910322: Tingyi (Cayman Islands) | 910708: Evergrande Health Ind. Group |
| Index / sector aggregate (non-company) | FALSE | 86 | 5,042 | M1100: TSE Cement Subindex | M1200: TSE Foods Subindex | M1300: TSE Plastics Subindex |
| Securities-firm code (000xxx, no market value) | FALSE | 5 | 316 | 000116: JihSun SCL | 000930: H.N.S | 000960: FBS |
exclusion_log <- bind_rows(exclusion_log, tibble::tibble(
Step = "Non-company / aggregate series flagged",
Rows_affected = sum(!df3$analysis_sample),
Reason = "Index, sector-aggregate or no-market-value series would double-count or are not individual listed firms (judgement based on code pattern and name; please verify)",
Action = "Kept in cleaned data with analysis_sample = FALSE; excluded from aggregates, charts and ratios"))Why this matters (observed): summing a single quarter across all code types gives a much larger total than summing only the individual-firm codes, which would be misleading.
last_q <- max(df3$report_date)
demo <- df3 %>% filter(report_date == last_q) %>% group_by(entity_type) %>%
summarise(`Sum of market value (NT$ trillion, inferred)` = sum(market_value, na.rm = TRUE) / unit_div,
`Sum of book value (NT$ trillion, inferred)` = sum(book_value, na.rm = TRUE) / unit_div, .groups = "drop")
kbl_tbl(demo, caption = paste("Totals by code type in", format(last_q), "(illustration of double counting)"))| entity_type | Sum of market value (NT$ trillion, inferred) | Sum of book value (NT$ trillion, inferred) |
|---|---|---|
| Company (4-digit code) | 163.74 | 50.38 |
| Foreign / secondary listing (91xxxx) | 0.00 | 0.13 |
| Index / sector aggregate (non-company) | 664.25 | 188.00 |
| Securities-firm code (000xxx, no market value) | 0.00 | 0.35 |
4.6 Is operating income standalone or cumulative?
TEJ income-statement items are sometimes reported year-to-date. If so, Q2 would contain H1, Q3 nine months and Q4 the full year. The following test uses firm-years with four positive quarterly values. Under cumulative reporting we would expect Q4/Q1 close to 4 and values rising every quarter; under standalone reporting neither pattern should dominate.
oi_test <- df3 %>% filter(analysis_sample) %>%
mutate(yr = year(report_date), qn = quarter(report_date)) %>%
select(company_id, yr, qn, operating_profit_reported) %>%
pivot_wider(names_from = qn, values_from = operating_profit_reported, names_prefix = "Q") %>%
filter(!is.na(Q1), !is.na(Q2), !is.na(Q3), !is.na(Q4)) %>%
filter(Q1 > 0, Q2 > 0, Q3 > 0, Q4 > 0)
test_res <- tibble::tibble(
Statistic = c("Firm-years tested", "Median Q2/Q1", "Median Q3/Q2", "Median Q4/Q3", "Median Q4/Q1",
"Share of firm-years with Q1<Q2<Q3<Q4"),
Value = c(n_fmt(nrow(oi_test)), round(median(oi_test$Q2 / oi_test$Q1), 3),
round(median(oi_test$Q3 / oi_test$Q2), 3), round(median(oi_test$Q4 / oi_test$Q3), 3),
round(median(oi_test$Q4 / oi_test$Q1), 3),
sprintf("%.1f%%", 100 * mean(oi_test$Q1 < oi_test$Q2 & oi_test$Q2 < oi_test$Q3 & oi_test$Q3 < oi_test$Q4)))
)
kbl_tbl(test_res, caption = "Test for cumulative (year-to-date) operating income")| Statistic | Value |
|---|---|
| Firm-years tested | 15,351 |
| Median Q2/Q1 | 1.144 |
| Median Q3/Q2 | 1.038 |
| Median Q4/Q3 | 0.948 |
| Median Q4/Q1 | 1.123 |
| Share of firm-years with Q1<Q2<Q3<Q4 | 10.9% |
oi_cumulative <- median(oi_test$Q4 / oi_test$Q1) > 2.5 &&
mean(oi_test$Q1 < oi_test$Q2 & oi_test$Q2 < oi_test$Q3 & oi_test$Q3 < oi_test$Q4) > 0.5
tsmc_oi <- df3 %>% filter(company_id == "2330", year(report_date) == 2014) %>%
select(company_id, quarter, operating_profit_reported)
kbl_tbl(tsmc_oi, caption = "Worked example: TSMC 2014 operating income as reported")| company_id | quarter | operating_profit_reported |
|---|---|---|
| 2330 | 2014 Q4 | 88,216,285 |
| 2330 | 2014 Q3 | 84,427,238 |
| 2330 | 2014 Q2 | 70,709,172 |
| 2330 | 2014 Q1 | 52,517,614 |
Result: the test indicates that operating income is STANDALONE quarterly (not cumulative); no conversion is applied.
if (oi_cumulative) {
df3 <- df3 %>% mutate(yr = year(report_date), qn = quarter(report_date), qidx = yr * 4 + qn) %>%
group_by(company_id) %>% arrange(qidx, .by_group = TRUE) %>%
mutate(prev_val = lag(operating_profit_reported), prev_q = lag(qidx),
operating_profit = case_when(
qn == 1 ~ operating_profit_reported,
!is.na(prev_q) & prev_q == qidx - 1 ~ operating_profit_reported - prev_val,
TRUE ~ NA_real_)) %>% # previous quarter missing -> cannot de-cumulate
ungroup() %>% select(-yr, -qn, -qidx, -prev_val, -prev_q)
message("Cumulative operating income converted to standalone quarterly values.")
} else {
df3 <- df3 %>% mutate(operating_profit = operating_profit_reported)
}4.7 Final cleaned dataset
Missing values are not replaced by zero, and legitimate negative operating profits are preserved.
clean <- df3 %>%
mutate(
neg_operating_profit = !is.na(operating_profit) & operating_profit < 0,
nonpos_book_value = !is.na(book_value) & book_value <= 0,
nonpos_market_value = !is.na(market_value) & market_value <= 0
) %>%
select(company_id, company_name, report_date, quarter,
operating_profit, book_value, market_value,
entity_type, analysis_sample,
neg_operating_profit, nonpos_book_value, nonpos_market_value) %>%
arrange(company_id, report_date)
analysis <- clean %>% filter(analysis_sample)
kbl_tbl(exclusion_log, caption = "Log of exclusions, flags and corrections (nothing is removed silently)")| Step | Rows_affected | Reason | Action |
|---|---|---|---|
| Date window 2010-01-01 to 2026-06-30 | 0 | Report date outside the assignment window | No rows removed (all rows already inside window) |
| Duplicate company-quarter | 0 | No company has more than one row in the same calendar quarter | No deduplication or aggregation needed |
| Non-company / aggregate series flagged | 5,358 | Index, sector-aggregate or no-market-value series would double-count or are not individual listed firms (judgement based on code pattern and name; please verify) | Kept in cleaned data with analysis_sample = FALSE; excluded from aggregates, charts and ratios |
cat("Rows in cleaned dataset:", n_fmt(nrow(clean)), "| Rows in analysis sample:", n_fmt(nrow(analysis)), "\n")## Rows in cleaned dataset: 135,022 | Rows in analysis sample: 129,6645 Data Quality Assessment
5.1 Missing values (analysis sample)
miss_tbl <- tibble::tibble(
Variable = c("operating_profit", "book_value", "market_value"),
`Missing (n)` = c(sum(is.na(analysis$operating_profit)), sum(is.na(analysis$book_value)), sum(is.na(analysis$market_value))),
`Missing (%)` = 100 * `Missing (n)` / nrow(analysis)
)
kbl_tbl(miss_tbl, caption = "Missing values in the analysis sample")| Variable | Missing (n) | Missing (%) |
|---|---|---|
| operating_profit | 2,965 | 2.29 |
| book_value | 5 | 0.00 |
| market_value | 9,145 | 7.05 |
5.2 Coverage by company
Not every firm should have 66 quarters (firms list and delist at different times), so the following definitions are used: a company is complete if it has all 66 quarters; it has internal gaps if quarters are missing between its first and last observed quarter; otherwise it has a partial but contiguous span (consistent with later listing or earlier delisting, which cannot be verified from this file).
coverage <- analysis %>%
mutate(qidx = year(report_date) * 4 + quarter(report_date)) %>%
group_by(company_id) %>% arrange(report_date, .by_group = TRUE) %>%
summarise(
company_name = last(company_name),
n_quarters = n(),
first_quarter = first(quarter), last_quarter = last(quarter),
span_quarters = max(qidx) - min(qidx) + 1,
internal_gap_quarters = span_quarters - n_quarters,
n_missing_operating_profit = sum(is.na(operating_profit)),
n_missing_book_value = sum(is.na(book_value)),
n_missing_market_value = sum(is.na(market_value)),
.groups = "drop") %>%
mutate(coverage_status = case_when(
n_quarters == 66 ~ "Complete (66 quarters)",
internal_gap_quarters > 0 ~ "Incomplete: internal gaps",
TRUE ~ "Partial span, contiguous"))
kbl_tbl(count(coverage, coverage_status, name = "companies"), caption = "Company coverage categories")| coverage_status | companies |
|---|---|
| Complete (66 quarters) | 1,284 |
| Incomplete: internal gaps | 1,307 |
| Partial span, contiguous | 320 |
kbl_tbl(coverage %>% summarise(Min = min(n_quarters), Median = median(n_quarters), Mean = mean(n_quarters), Max = max(n_quarters)),
caption = "Distribution of quarters per company")| Min | Median | Mean | Max |
|---|---|---|---|
| 1 | 57 | 44.54 | 66 |
q_pattern <- analysis %>% mutate(qn = paste0("Calendar Q", quarter(report_date))) %>%
count(qn, quarter) %>% group_by(qn) %>% summarise(`Average companies per quarter` = mean(n), .groups = "drop")
kbl_tbl(q_pattern, caption = "Average number of companies per calendar quarter")| qn | Average companies per quarter |
|---|---|
| Calendar Q1 | 1,772.53 |
| Calendar Q2 | 2,175.29 |
| Calendar Q3 | 1,766.81 |
| Calendar Q4 | 2,142.62 |
Observed: the number of companies differs systematically between calendar quarters (see table above and the chart in Section 8). Interpretation (unverified): this may reflect differences in reporting or listing categories within TEJ (for example, some firms reporting only semi-annually), but the workbook alone cannot confirm the reason. Aggregates for different quarters are therefore not strictly like-for-like.
5.3 Data quality report
dq <- function(d) {
n <- nrow(d)
c(
n_fmt(n),
n_fmt(n_distinct(d$company_id)),
format(min(d$report_date)), format(max(d$report_date)),
n_fmt(n_distinct(d$quarter)),
paste0(n_fmt(sum(is.na(d$operating_profit))), " (", pct(sum(is.na(d$operating_profit)), n), ")"),
paste0(n_fmt(sum(is.na(d$book_value))), " (", pct(sum(is.na(d$book_value)), n), ")"),
paste0(n_fmt(sum(is.na(d$market_value))), " (", pct(sum(is.na(d$market_value)), n), ")"),
n_fmt(sum(d$neg_operating_profit)),
n_fmt(sum(d$nonpos_book_value)),
n_fmt(sum(d$nonpos_market_value))
)
}
dq_metrics <- c("Observations after cleaning", "Unique company IDs", "Earliest date", "Latest date",
"Unique quarters (expected: 66)",
"Missing operating profit (n, %)", "Missing book value (n, %)", "Missing market value (n, %)",
"Observations with negative operating income",
"Observations with zero or negative book value",
"Observations with zero or negative market value")
dq_table <- tibble::tibble(Metric = dq_metrics,
`All cleaned records` = dq(clean),
`Analysis sample (firms only)` = dq(analysis)) %>%
bind_rows(
tibble::tibble(Metric = c("Observations before cleaning (raw workbook)",
"Duplicate company-quarter observations (pairs)",
"Companies with incomplete quarterly records (internal gaps)",
"Companies with partial but contiguous span",
"Companies with all 66 quarters"),
`All cleaned records` = c(n_fmt(n_raw), n_fmt(n_dup_pairs), "n/a", "n/a", "n/a"),
`Analysis sample (firms only)` = c(n_fmt(n_raw), n_fmt(n_dup_pairs),
n_fmt(sum(coverage$coverage_status == "Incomplete: internal gaps")),
n_fmt(sum(coverage$coverage_status == "Partial span, contiguous")),
n_fmt(sum(coverage$coverage_status == "Complete (66 quarters)"))))
)
kbl_tbl(dq_table, caption = "Data quality report")| Metric | All cleaned records | Analysis sample (firms only) |
|---|---|---|
| Observations after cleaning | 135,022 | 129,664 |
| Unique company IDs | 3,002 | 2,911 |
| Earliest date | 2010-03-31 | 2010-03-31 |
| Latest date | 2026-06-30 | 2026-06-30 |
| Unique quarters (expected: 66) | 66 | 66 |
| Missing operating profit (n, %) | 3,489 (2.58%) | 2,965 (2.29%) |
| Missing book value (n, %) | 5 (0.00%) | 5 (0.00%) |
| Missing market value (n, %) | 9,690 (7.18%) | 9,145 (7.05%) |
| Observations with negative operating income | 37,546 | 37,203 |
| Observations with zero or negative book value | 396 | 396 |
| Observations with zero or negative market value | 0 | 0 |
| Observations before cleaning (raw workbook) | 135,022 | 135,022 |
| Duplicate company-quarter observations (pairs) | 0 | 0 |
| Companies with incomplete quarterly records (internal gaps) | n/a | 1,307 |
| Companies with partial but contiguous span | n/a | 320 |
| Companies with all 66 quarters | n/a | 1,284 |
Completeness caveat. The file contains 2,911 firm codes in the analysis sample. It is not possible to verify from the workbook alone that this covers every listed Taiwanese firm, so no such claim is made. Verification would require comparing against an official list of TWSE / TPEx listed companies.
6 Descriptive Statistics
All statistics below use the analysis sample (individual firm codes only) and raw units (inferred NT$ thousands).
desc <- function(x) tibble::tibble(
N = sum(!is.na(x)), Mean = mean(x, na.rm = TRUE), SD = sd(x, na.rm = TRUE), Min = min(x, na.rm = TRUE),
P25 = quantile(x, .25, na.rm = TRUE), Median = median(x, na.rm = TRUE),
P75 = quantile(x, .75, na.rm = TRUE), Max = max(x, na.rm = TRUE))
desc_tbl <- bind_rows(`Operating profit` = desc(analysis$operating_profit),
`Book value` = desc(analysis$book_value),
`Market value` = desc(analysis$market_value), .id = "Variable")
kbl_tbl(desc_tbl, digits = 0, caption = "Descriptive statistics (original units)")| Variable | N | Mean | SD | Min | P25 | Median | P75 | Max |
|---|---|---|---|---|---|---|---|---|
| Operating profit | 126,699 | 348,777 | 5,494,788 | -38,720,187 | -5,838 | 31,638 | 129,262 | 766,602,651 |
| Book value | 129,659 | 13,956,406 | 76,343,482 | -51,575,825 | 867,140 | 1,999,237 | 5,806,692 | 6,474,470,981 |
| Market value | 120,519 | 24,452,915 | 374,336,731 | 14,399 | 1,403,030 | 3,328,729 | 9,371,901 | 62,497,011,700 |
Distributions are strongly right-skewed (mean far above median), which is typical of firm-size data. Extreme observations are retained because they correspond to genuinely very large firms; log-type scales are used in the charts instead of removing them.
7 Quarterly Trend Analysis
Two versions of every quarterly total are produced:
- All non-missing observations: each variable is summed over all firms with a non-missing value that quarter. The set of firms can differ across variables and quarters.
- Consistent sample: only firm-quarters where operating profit, book value and market value are all non-missing.
quarterly_summary <- analysis %>%
mutate(complete_case = !is.na(operating_profit) & !is.na(book_value) & !is.na(market_value)) %>%
group_by(quarter, report_date) %>%
summarise(
n_companies = n(),
n_complete_case = sum(complete_case),
sum_operating_profit_all = sum(operating_profit, na.rm = TRUE),
sum_book_value_all = sum(book_value, na.rm = TRUE),
sum_market_value_all = sum(market_value, na.rm = TRUE),
sum_operating_profit_cc = sum(operating_profit[complete_case]),
sum_book_value_cc = sum(book_value[complete_case]),
sum_market_value_cc = sum(market_value[complete_case]),
median_operating_profit = median(operating_profit, na.rm = TRUE),
median_book_value = median(book_value, na.rm = TRUE),
median_market_value = median(market_value, na.rm = TRUE),
pct_missing_operating_profit = 100 * mean(is.na(operating_profit)),
pct_missing_book_value = 100 * mean(is.na(book_value)),
pct_missing_market_value = 100 * mean(is.na(market_value)),
.groups = "drop") %>%
arrange(report_date)
# Check that all 66 expected quarters are present
if (nrow(quarterly_summary) != 66) warning("Quarterly summary has ", nrow(quarterly_summary), " quarters instead of 66.")
kbl_tbl(quarterly_summary %>% mutate(across(starts_with("sum_"), ~ .x / unit_div)) %>%
select(quarter, n_companies, n_complete_case, starts_with("sum_")),
digits = 3, scroll_h = "420px",
caption = paste("Quarterly totals (", unit_lbl, ")"))| quarter | n_companies | n_complete_case | sum_operating_profit_all | sum_book_value_all | sum_market_value_all | sum_operating_profit_cc | sum_book_value_cc | sum_market_value_cc |
|---|---|---|---|---|---|---|---|---|
| 2010 Q1 | 1,455 | 1,347 | 0.394 | 15.801 | 22.785 | 0.370 | 11.691 | 20.418 |
| 2010 Q2 | 1,842 | 1,528 | 0.506 | 15.952 | 21.561 | 0.474 | 11.730 | 19.297 |
| 2010 Q3 | 1,477 | 1,369 | 0.446 | 16.202 | 23.829 | 0.431 | 12.066 | 21.248 |
| 2010 Q4 | 1,830 | 1,587 | 0.349 | 17.042 | 26.796 | 0.342 | 12.662 | 23.790 |
| 2011 Q1 | 1,508 | 1,409 | 0.344 | 16.965 | 25.547 | 0.335 | 12.997 | 22.662 |
| 2011 Q2 | 1,923 | 1,637 | 0.312 | 17.160 | 25.896 | 0.295 | 12.788 | 22.992 |
| 2011 Q3 | 1,529 | 1,439 | 0.228 | 16.687 | 21.230 | 0.217 | 12.720 | 18.852 |
| 2011 Q4 | 1,911 | 1,674 | 0.141 | 17.855 | 21.367 | 0.139 | 13.324 | 18.916 |
| 2012 Q1 | 1,567 | 1,473 | 0.206 | 17.488 | 23.755 | 0.199 | 13.033 | 21.323 |
| 2012 Q2 | 1,955 | 1,699 | 0.247 | 17.482 | 22.310 | 0.242 | 12.886 | 20.008 |
| 2012 Q3 | 1,579 | 1,487 | 0.332 | 17.478 | 23.297 | 0.308 | 12.786 | 20.794 |
| 2012 Q4 | 1,942 | 1,729 | 0.301 | 18.738 | 23.769 | 0.296 | 13.728 | 21.200 |
| 2013 Q1 | 1,598 | 1,511 | 0.308 | 18.833 | 24.417 | 0.284 | 13.785 | 21.520 |
| 2013 Q2 | 2,015 | 1,732 | 0.373 | 19.121 | 25.424 | 0.347 | 13.955 | 22.555 |
| 2013 Q3 | 1,615 | 1,523 | 0.430 | 18.939 | 25.609 | 0.402 | 13.821 | 22.583 |
| 2013 Q4 | 1,997 | 1,752 | 0.442 | 20.451 | 27.460 | 0.413 | 14.851 | 24.146 |
| 2014 Q1 | 1,653 | 1,555 | 0.353 | 20.546 | 28.318 | 0.332 | 14.922 | 25.181 |
| 2014 Q2 | 2,078 | 1,761 | 0.492 | 20.919 | 30.766 | 0.451 | 14.857 | 27.459 |
| 2014 Q3 | 1,671 | 1,573 | 0.503 | 20.896 | 28.962 | 0.464 | 15.101 | 25.531 |
| 2014 Q4 | 2,069 | 1,821 | 0.414 | 22.465 | 30.620 | 0.394 | 16.245 | 27.030 |
| 2015 Q1 | 1,705 | 1,608 | 0.485 | 22.581 | 31.132 | 0.448 | 16.363 | 27.542 |
| 2015 Q2 | 2,109 | 1,845 | 0.498 | 22.149 | 30.836 | 0.445 | 15.852 | 26.863 |
| 2015 Q3 | 1,713 | 1,621 | 0.392 | 22.181 | 26.746 | 0.375 | 16.056 | 23.465 |
| 2015 Q4 | 2,083 | 1,859 | 0.416 | 23.321 | 28.268 | 0.404 | 16.717 | 24.904 |
| 2016 Q1 | 1,729 | 1,644 | 0.376 | 23.443 | 29.129 | 0.372 | 16.670 | 25.864 |
| 2016 Q2 | 2,111 | 1,872 | 0.479 | 23.016 | 29.277 | 0.445 | 16.155 | 26.120 |
| 2016 Q3 | 1,742 | 1,659 | 0.549 | 23.297 | 30.522 | 0.512 | 16.286 | 27.167 |
| 2016 Q4 | 2,086 | 1,885 | 0.553 | 24.112 | 30.787 | 0.535 | 17.022 | 27.198 |
| 2017 Q1 | 1,768 | 1,682 | 0.516 | 24.124 | 32.403 | 0.509 | 16.949 | 28.665 |
| 2017 Q2 | 2,138 | 1,901 | 0.489 | 24.064 | 34.630 | 0.453 | 16.710 | 30.779 |
| 2017 Q3 | 1,769 | 1,690 | 0.611 | 24.649 | 34.471 | 0.554 | 17.110 | 30.678 |
| 2017 Q4 | 2,116 | 1,912 | 0.581 | 25.846 | 35.939 | 0.560 | 18.049 | 31.925 |
| 2018 Q1 | 1,785 | 1,710 | 0.508 | 26.307 | 36.617 | 0.470 | 18.445 | 32.522 |
| 2018 Q2 | 2,163 | 1,918 | 0.545 | 26.308 | 36.650 | 0.491 | 18.189 | 32.409 |
| 2018 Q3 | 1,788 | 1,716 | 0.626 | 26.183 | 36.380 | 0.589 | 18.235 | 31.968 |
| 2018 Q4 | 2,137 | 1,937 | 0.483 | 26.678 | 32.515 | 0.475 | 18.652 | 28.461 |
| 2019 Q1 | 1,795 | 1,719 | 0.380 | 27.417 | 35.380 | 0.359 | 19.022 | 31.092 |
| 2019 Q2 | 2,180 | 1,937 | 0.464 | 27.530 | 35.995 | 0.422 | 18.524 | 31.484 |
| 2019 Q3 | 1,802 | 1,727 | 0.586 | 27.402 | 36.087 | 0.533 | 18.432 | 31.633 |
| 2019 Q4 | 2,156 | 1,950 | 0.526 | 28.715 | 40.259 | 0.509 | 19.105 | 35.545 |
| 2020 Q1 | 1,813 | 1,738 | 0.366 | 26.898 | 32.249 | 0.318 | 18.346 | 28.338 |
| 2020 Q2 | 2,210 | 1,953 | 0.595 | 28.046 | 39.544 | 0.557 | 18.535 | 35.111 |
| 2020 Q3 | 1,832 | 1,753 | 0.764 | 28.429 | 42.044 | 0.692 | 18.887 | 37.857 |
| 2020 Q4 | 2,197 | 1,971 | 0.841 | 30.849 | 49.912 | 0.796 | 20.437 | 45.437 |
| 2021 Q1 | 1,842 | 1,765 | 0.981 | 30.883 | 55.609 | 0.854 | 20.730 | 50.767 |
| 2021 Q2 | 2,250 | 1,992 | 1.125 | 32.221 | 60.767 | 1.021 | 21.457 | 55.510 |
| 2021 Q3 | 1,855 | 1,781 | 1.190 | 32.186 | 57.761 | 1.116 | 21.850 | 52.186 |
| 2021 Q4 | 2,239 | 2,023 | 1.233 | 34.527 | 63.271 | 1.167 | 23.398 | 57.237 |
| 2022 Q1 | 1,873 | 1,794 | 1.229 | 33.913 | 60.494 | 1.120 | 23.576 | 53.865 |
| 2022 Q2 | 2,302 | 2,039 | 1.223 | 32.138 | 51.394 | 1.149 | 23.207 | 45.768 |
| 2022 Q3 | 1,895 | 1,813 | 1.033 | 32.107 | 46.561 | 1.009 | 24.125 | 41.406 |
| 2022 Q4 | 2,290 | 2,060 | 0.773 | 34.539 | 49.467 | 0.832 | 25.120 | 44.139 |
| 2023 Q1 | 1,932 | 1,839 | 0.619 | 34.559 | 55.512 | 0.635 | 24.729 | 50.041 |
| 2023 Q2 | 2,364 | 2,085 | 0.711 | 35.371 | 59.770 | 0.632 | 24.824 | 53.943 |
| 2023 Q3 | 1,956 | 1,855 | 0.836 | 35.774 | 57.237 | 0.767 | 25.627 | 51.435 |
| 2023 Q4 | 2,353 | 2,122 | 0.770 | 37.747 | 63.602 | 0.786 | 26.461 | 57.417 |
| 2024 Q1 | 2,000 | 1,905 | 0.805 | 38.320 | 71.362 | 0.733 | 26.762 | 64.786 |
| 2024 Q2 | 2,430 | 2,150 | 1.032 | 39.416 | 81.437 | 0.931 | 27.576 | 74.255 |
| 2024 Q3 | 2,027 | 1,910 | 1.203 | 40.168 | 78.338 | 1.125 | 28.064 | 70.865 |
| 2024 Q4 | 2,422 | 2,180 | 1.072 | 41.658 | 81.635 | 1.043 | 29.436 | 74.103 |
| 2025 Q1 | 2,061 | 1,954 | 1.097 | 41.616 | 73.158 | 1.040 | 29.588 | 65.706 |
| 2025 Q2 | 2,465 | 2,218 | 1.070 | 39.195 | 78.791 | 1.102 | 27.923 | 71.292 |
| 2025 Q3 | 2,019 | 1,940 | 1.283 | 41.800 | 90.890 | 1.223 | 29.465 | 82.877 |
| 2025 Q4 | 2,454 | 2,249 | 1.374 | 44.861 | 102.636 | 1.315 | 31.884 | 94.051 |
| 2026 Q1 | 2,049 | 1,971 | 1.656 | 45.499 | 112.117 | 1.595 | 32.614 | 103.604 |
| 2026 Q2 | 2,445 | 2,282 | 2.127 | 50.509 | 163.742 | 2.023 | 35.588 | 152.583 |
8 Visualizations
8.1 Number of companies per quarter
quarterly_summary %>%
select(report_date, `All firm records` = n_companies, `With all three variables` = n_complete_case) %>%
pivot_longer(-report_date, names_to = "Sample", values_to = "n") %>%
ggplot(aes(report_date, n, colour = Sample)) + geom_line(linewidth = 0.9) + geom_point(size = 1) +
date_scale + scale_y_continuous(labels = comma) +
labs(title = "Number of companies represented in each quarter",
subtitle = "Analysis sample (index / aggregate series excluded)",
x = "Quarter end", y = "Number of companies", colour = NULL)
8.2 Aggregate operating profit, book value and market value
agg_plot <- function(v, label) {
quarterly_summary %>%
transmute(report_date,
`All non-missing observations` = .data[[paste0("sum_", v, "_all")]] / unit_div,
`Consistent sample (all three variables non-missing)` = .data[[paste0("sum_", v, "_cc")]] / unit_div) %>%
pivot_longer(-report_date, names_to = "Sample", values_to = "value") %>%
ggplot(aes(report_date, value, colour = Sample)) + geom_line(linewidth = 0.9) + geom_point(size = 1) +
geom_hline(yintercept = 0, linewidth = 0.3, colour = "grey40") +
date_scale + scale_y_continuous(labels = comma) +
labs(title = paste("Aggregate", tolower(label), "by quarter"),
x = "Quarter end", y = paste0(label, "\n", unit_lbl), colour = NULL) +
guides(colour = guide_legend(nrow = 2))
}agg_plot("operating_profit", "Operating profit")
agg_plot("book_value", "Book value")
agg_plot("market_value", "Market value")
The two lines per chart differ because the “all non-missing” series changes with the number of reporting firms in each quarter, whereas the consistent sample requires all three variables. Alternating coverage between calendar quarters (Section 5.2) can create apparent seasonality in the “all non-missing” totals that is a coverage effect rather than an economic one.
8.3 Distributions (signed-log scale, no observations removed)
Raw-scale histograms are dominated by a few very large firms and are
unreadable. A signed log scale,
sign(x) * log10(1 + |x|), is used instead: it handles
positive, zero and negative values and requires no deletion of
observations. Axis labels show original values (inferred NT$
thousands).
slog <- function(x) sign(x) * log10(1 + abs(x))
hist_slog <- function(d, var, title, xlab, brk) {
d <- d %>% filter(!is.na(.data[[var]]))
ggplot(d, aes(x = slog(.data[[var]]))) +
geom_histogram(bins = 70, fill = "#2C7FB8", colour = "white", linewidth = 0.1) +
scale_x_continuous(breaks = slog(brk),
labels = label_number(scale_cut = cut_short_scale())(brk)) +
scale_y_continuous(labels = comma) +
labs(title = title, subtitle = paste0("N = ", n_fmt(nrow(d)), " firm-quarters; signed log scale"),
x = xlab, y = "Number of firm-quarters")
}hist_slog(analysis, "operating_profit", "Distribution of quarterly operating profit",
"Operating profit (NT$ thousands, inferred)", c(-1e8, -1e6, -1e4, 0, 1e4, 1e6, 1e8, 1e10))
hist_slog(analysis, "book_value", "Distribution of book value",
"Book value (NT$ thousands, inferred)", c(-1e8, -1e6, -1e4, 0, 1e4, 1e6, 1e8, 1e10))
hist_slog(analysis, "market_value", "Distribution of market value",
"Market value (NT$ thousands, inferred)", c(1e4, 1e5, 1e6, 1e7, 1e8, 1e9, 1e10, 1e11))
8.4 Missing-value trends
quarterly_summary %>%
select(report_date, starts_with("pct_missing_")) %>%
pivot_longer(-report_date, names_to = "Variable", values_to = "pct") %>%
mutate(Variable = sub("pct_missing_", "", Variable)) %>%
ggplot(aes(report_date, pct, colour = Variable)) + geom_line(linewidth = 0.9) +
date_scale + scale_y_continuous(labels = label_number(suffix = "%")) +
labs(title = "Share of missing values by quarter",
subtitle = "Among firm records present in the file for that quarter",
x = "Quarter end", y = "Missing (%)", colour = NULL)
This chart measures missing values in rows that exist. Quarters in which a firm has no row at all are a separate coverage issue (Section 5.2).
9 Optional Financial Ratios
Two derived ratios are computed separately from the three original TEJ variables:
- Profitability ratio = operating profit / book value (this is a quarterly, not annualised, ratio);
- Market-to-book ratio = market value / book value.
A ratio is set to NA (not zero) when either input is
missing or when book value is zero or negative. A negative denominator
reverses the sign of the ratio and produces numbers that cannot be
interpreted as returns or valuation multiples, so these observations are
treated as undefined.
ratios <- analysis %>%
transmute(company_id, report_date, quarter,
profitability_ratio = if_else(!is.na(operating_profit) & !is.na(book_value) & book_value > 0,
operating_profit / book_value, NA_real_),
market_to_book = if_else(!is.na(market_value) & !is.na(book_value) & book_value > 0,
market_value / book_value, NA_real_))
ratio_status <- tibble::tibble(
Item = c("Analysis-sample observations", "Book value missing", "Book value zero or negative",
"Profitability ratio defined", "Market-to-book defined"),
N = c(nrow(analysis), sum(is.na(analysis$book_value)),
sum(!is.na(analysis$book_value) & analysis$book_value <= 0),
sum(!is.na(ratios$profitability_ratio)), sum(!is.na(ratios$market_to_book))))
kbl_tbl(ratio_status, digits = 0, caption = "Availability of derived ratios")| Item | N |
|---|---|
| Analysis-sample observations | 129,664 |
| Book value missing | 5 |
| Book value zero or negative | 396 |
| Profitability ratio defined | 126,298 |
| Market-to-book defined | 120,424 |
kbl_tbl(bind_rows(`Profitability ratio` = desc(ratios$profitability_ratio),
`Market-to-book` = desc(ratios$market_to_book), .id = "Ratio"),
digits = 3, caption = "Descriptive statistics of derived ratios (extreme values retained)")| Ratio | N | Mean | SD | Min | P25 | Median | P75 | Max |
|---|---|---|---|---|---|---|---|---|
| Profitability ratio | 126,298 | 0.002 | 0.972 | -297.520 | -0.004 | 0.018 | 0.043 | 4.905 |
| Market-to-book | 120,424 | 2.206 | 4.412 | 0.003 | 0.981 | 1.494 | 2.397 | 355.703 |
Because both ratios have extreme values, the quarterly median (robust to outliers) is plotted.
ratio_q <- ratios %>% group_by(quarter, report_date) %>%
summarise(median_profitability_ratio = median(profitability_ratio, na.rm = TRUE),
median_market_to_book = median(market_to_book, na.rm = TRUE), .groups = "drop") %>%
arrange(report_date)
ratio_q %>% pivot_longer(starts_with("median_"), names_to = "Ratio", values_to = "value") %>%
mutate(Ratio = recode(Ratio, median_profitability_ratio = "Median operating profit / book value (quarterly)",
median_market_to_book = "Median market-to-book ratio")) %>%
ggplot(aes(report_date, value)) + geom_line(linewidth = 0.9, colour = "#2C7FB8") +
facet_wrap(~ Ratio, ncol = 1, scales = "free_y") + date_scale +
labs(title = "Cross-sectional median of derived ratios", x = "Quarter end", y = "Ratio")
# Attach derived medians to the quarterly summary (kept in clearly named separate columns)
quarterly_summary <- quarterly_summary %>%
left_join(ratio_q %>% rename(derived_median_profitability_ratio = median_profitability_ratio,
derived_median_market_to_book = median_market_to_book),
by = c("quarter", "report_date"))No regression models are estimated, as the assignment does not require them.
10 Limitations
- Unit unconfirmed. The workbook has no unit metadata; NT$ thousands is an inference from a magnitude check on TSMC.
- Completeness unverified. The file cannot be checked against an official list of all listed firms, so full market coverage is not claimed.
- Non-company series. The file includes index / sector-aggregate and other non-firm codes. They were identified by code pattern and name (a judgement) and excluded from aggregates; they should be verified.
- Uneven coverage across calendar quarters means that quarterly totals are not always like-for-like. The consistent-sample totals reduce, but do not remove, this problem.
- Missing market values (and a few missing book values) are not imputed. Totals based on non-missing values understate the true aggregate to the extent that values are missing.
- Accounting basis unknown. Whether the figures are consolidated or parent-only, and any differences in fiscal-year ends, cannot be determined from the file.
- Survivorship / entry effects. Firms enter and exit the sample; the file does not document listing or delisting dates.
- Ratios are undefined for non-positive book value and are sensitive to very small denominators.
11 Conclusion
The workbook contains 135,022 firm-quarter rows for 3,002 codes, covering 66 of the 66 expected calendar quarters (2010-03-31 to 2026-06-30). The data are already at quarterly frequency, and there were 0 duplicate company-quarter pairs, so no aggregation of repeated statements was required. The cumulative-income test indicates that operating income is standalone quarterly, so no conversion was needed.
Roughly 5,358 rows belong to index, aggregate or no-market-value series. They were flagged rather than deleted and kept out of the aggregates to avoid double counting. The analysis sample comprises 129,664 rows for 2,911 firm codes, of which 1,284 have all 66 quarters and 1,307 have internal gaps. Missing shares are 2.3% for operating profit, 0.00% for book value and 7.1% for market value. Negative operating profits are common (37,203 firm-quarters) and were preserved.
The three variables are heavily right-skewed; aggregate trends should be read together with the changing number of reporting firms and with the caveat that the monetary unit is unconfirmed. Section 10 lists the remaining limitations.
12 References and Source Notes
- Taiwan Economic Journal (TEJ). Quarterly financial data, supplied as
TEJ_Quarterly_2010_2026_Combined.xlsx. Accounts used: Net operating income (loss), Total equity, Market Value of Common Stocks at Quarter-End. Consult the TEJ data dictionary to confirm currency, scale and accounting basis. - R packages: readxl, dplyr, tidyr, lubridate, janitor, ggplot2, scales, knitr, kableExtra, writexl, readr (see CRAN documentation).
13 Export of Processed Data
var_dict <- tibble::tibble(
variable = c("company_id", "company_name", "report_date", "quarter", "operating_profit", "book_value",
"market_value", "entity_type", "analysis_sample", "neg_operating_profit",
"nonpos_book_value", "nonpos_market_value"),
source_or_derivation = c("COID", "COID_Name", "YYYY/MM (converted to quarter-end date)",
"Derived from report_date (calendar quarter label)",
"Net operating income (loss)", "Total equity",
"Market Value of Common Stocks at Quarter-End",
"Derived from COID pattern (judgement; verify)",
"TRUE for individual firm codes used in aggregates",
"Flag: operating_profit < 0", "Flag: book_value <= 0", "Flag: market_value <= 0"),
type = c("text", "text", "date", "text", "number", "number", "number", "text", "logical", "logical", "logical", "logical"),
unit = c(NA, NA, NA, NA, rep("Total amount; currency/scale unconfirmed (NT$ thousands inferred)", 3),
NA, NA, NA, NA, NA),
notes = c(NA, NA, "Month-end date of the reporting month", "e.g. 2010 Q1",
"Standalone quarterly amount (see cumulative test); negatives preserved; missing kept as NA",
"Total amount, not per share", "Total amount, not per share",
NA, "Rows with FALSE are retained but excluded from aggregates", NA, NA, NA))
sheets <- list(
Cleaned_Data = clean,
Data_Quality_Report = dq_table,
Quarterly_Summary = quarterly_summary,
Company_Coverage = coverage,
Variable_Dictionary = var_dict,
Exclusion_Log = exclusion_log # extra sheet documenting every flag / correction
)
writexl::write_xlsx(sheets, path = out_xlsx)
readr::write_csv(clean, out_csv, na = "")
stopifnot(file.exists(out_xlsx), file.exists(out_csv))
cat("Files written to:", normalizePath(getwd()), "\n -", out_xlsx, "\n -", out_csv, "\n")## Files written to: /Users/kvn.andretty/Downloads
## - Taiwan_Stock_Market_Processed_Data.xlsx
## - Taiwan_Stock_Market_Cleaned_Data.csvThe original workbook is only read, never modified. The two output
files are saved in the working directory shown above. In RStudio, open
the Files pane (bottom right) and click More >
Go To Working Directory, or run getwd() in the
console.