This homework uses the TEJ database to download quarterly data for all listed firms in Taiwan, from January 2010 to June 2026. The accounts are total equity, market value of common stock, net operating income and quarter. All amounts are total values (not per share), in thousand NT$.
The TEJ file is tab-separated and saved in UTF-16LE. Missing values are marked with “.”, so they are read as NA.
raw <- read.delim("20261019011028.txt",
fileEncoding = "UTF-16LE",
na.strings = ".",
quote = "",
comment.char = "",
check.names = FALSE,
stringsAsFactors = FALSE)
dim(raw)## [1] 62521 6I renamed the columns, split the firm name into stock ID and name, converted the date (201003) into a quarter-end date (2010-03-31) and a label (2010Q1), and sorted by firm and time.
tej <- raw
names(tej) <- c("co_id", "ym", "equity", "quarter", "mkt_value", "op_income")
# "1101 TCC" -> stock_id "1101" + stock_name "TCC"
tej$stock_id <- sub(" .*$", "", tej$co_id)
tej$stock_name <- sub("^\\S+ ", "", tej$co_id)
# 201003 -> 2010-03-31 (quarter-end date) and a label like "2010Q1"
first_day <- as.Date(paste0(tej$ym, "01"), format = "%Y%m%d")
tej$qtr_end <- as.Date(format(first_day + 31, "%Y-%m-01")) - 1
tej$yq <- paste0(substr(tej$ym, 1, 4), "Q", tej$quarter)
# keep the useful columns, sorted by firm then time
tej <- tej[order(tej$stock_id, tej$qtr_end),
c("stock_id", "stock_name", "qtr_end", "yq", "quarter",
"equity", "mkt_value", "op_income")]
rownames(tej) <- NULL
str(tej)## 'data.frame': 62521 obs. of 8 variables:
## $ stock_id : chr "1101" "1101" "1101" "1101" ...
## $ stock_name: chr "TCC" "TCC" "TCC" "TCC" ...
## $ qtr_end : Date, format: "2010-03-31" "2010-06-30" ...
## $ yq : chr "2010Q1" "2010Q2" "2010Q3" "2010Q4" ...
## $ quarter : int 1 2 3 4 1 2 3 4 1 2 ...
## $ equity : num 1.02e+08 1.02e+08 1.10e+08 1.25e+08 1.29e+08 ...
## $ mkt_value : num 9.86e+07 8.99e+07 1.10e+08 1.21e+08 1.31e+08 ...
## $ op_income : int 2152334 3791319 4176068 5534591 3425462 5605798 4357630 2783801 2286803 3169387 ...The data has 62,521 rows, 1,096 firms and 66 quarters (2010-03-31 to 2026-06-30). Missing market values are mostly quarters before a firm was listed. Missing operating income is mostly banks and insurers, which don’t report it. Not every firm has all 66 quarters because firms list and delist over time.
options(scipen = 999)
cat("Rows :", nrow(tej), "\n")## Rows : 62521cat("Firms :", length(unique(tej$stock_id)), "\n")## Firms : 1096cat("Quarters:", length(unique(tej$qtr_end)), "\n")## Quarters: 66cat("Period :", format(min(tej$qtr_end)), "to", format(max(tej$qtr_end)), "\n")## Period : 2010-03-31 to 2026-06-30# missing values per account
colSums(is.na(tej[, c("equity", "mkt_value", "op_income")]))## equity mkt_value op_income
## 4 1837 1492First 10 rows (amounts in thousand NT$, total values, not per share):
knitr::kable(head(tej, 10), format.args = list(big.mark = ","))| stock_id | stock_name | qtr_end | yq | quarter | equity | mkt_value | op_income |
|---|---|---|---|---|---|---|---|
| 1101 | TCC | 2010-03-31 | 2010Q1 | 1 | 102,157,504 | 98,600,641 | 2,152,334 |
| 1101 | TCC | 2010-06-30 | 2010Q2 | 2 | 102,349,225 | 89,876,378 | 3,791,319 |
| 1101 | TCC | 2010-09-30 | 2010Q3 | 3 | 109,586,305 | 109,958,645 | 4,176,068 |
| 1101 | TCC | 2010-12-31 | 2010Q4 | 4 | 124,513,778 | 121,103,340 | 5,534,591 |
| 1101 | TCC | 2011-03-31 | 2011Q1 | 1 | 128,728,151 | 131,072,213 | 3,425,462 |
| 1101 | TCC | 2011-06-30 | 2011Q2 | 2 | 126,201,240 | 158,025,090 | 5,605,798 |
| 1101 | TCC | 2011-09-30 | 2011Q3 | 3 | 125,190,869 | 121,472,558 | 4,357,630 |
| 1101 | TCC | 2011-12-31 | 2011Q4 | 4 | 129,320,349 | 129,226,125 | 2,783,801 |
| 1101 | TCC | 2012-03-31 | 2012Q1 | 1 | 141,483,035 | 127,564,646 | 2,286,803 |
| 1101 | TCC | 2012-06-30 | 2012Q2 | 2 | 133,456,442 | 130,518,386 | 3,169,387 |
Summary of the three accounts (in thousand NT$):
summary(tej[, c("equity", "mkt_value", "op_income")])## equity mkt_value op_income
## Min. : -2123788 Min. : 14399 Min. :-34704518
## 1st Qu.: 1898900 1st Qu.: 2785774 1st Qu.: 3386
## Median : 4298959 Median : 6451392 Median : 73361
## Mean : 22185583 Mean : 43255861 Mean : 633905
## 3rd Qu.: 10929877 3rd Qu.: 17096477 3rd Qu.: 257281
## Max. :6474470981 Max. :62497011700 Max. :766602651
## NAs :4 NAs :1837 NAs :1492