1. Task and data source

The task is to download, from the TEJ (Taiwan Economic Journal) database, the quarterly operating profits, quarterly book values and quarterly market values of all listed firms in the Taiwan stock market from January 2010 to June 2026. All values must be in dollars (NT$), not per share.

Variable required TEJ account Variable in this dataset
Operating profit Net operating income operating_profit
Book value Total equity total_equity
Market value Market value of common stock market_value
Period Quarter year, quarter, year_quarter

2. Load the TEJ data

library(readr)
library(dplyr)
library(ggplot2)
library(knitr)

raw <- read_csv(
  "TEJ_quarterly_2010Q1_2026Q2_4digit_stocks.csv",
  locale = locale(encoding = "UTF-8"),
  col_types = cols(
    stock_id                      = col_character(),
    company_name                  = col_character(),
    tej_co_id                     = col_character(),
    quarter_end                   = col_date(),
    year                          = col_integer(),
    quarter                       = col_integer(),
    year_quarter                  = col_character(),
    book_value_total_equity       = col_double(),
    market_value_common_stock     = col_double(),
    operating_profit_quarter      = col_double(),
    operating_profit_ytd_original = col_double(),
    profit_calculation_status     = col_character()
  )
)
names(raw) <- sub("^", "", names(raw))   # remove byte-order mark if present

3. Convert units to NT dollars

TEJ reports financial statement and market value figures in thousands of NT$. Each variable is multiplied by 1,000 so that every value is a total amount in NT dollars.

unit <- 1000

panel <- raw %>%
  transmute(
    stock_id,
    company_name,
    quarter_end,
    year,
    quarter,
    year_quarter,
    total_equity     = book_value_total_equity   * unit,  # Book value
    market_value     = market_value_common_stock * unit,  # Market value
    operating_profit = operating_profit_quarter  * unit,  # Quarterly operating profit
    op_status        = profit_calculation_status
  ) %>%
  filter(quarter_end >= as.Date("2010-01-01"),
         quarter_end <= as.Date("2026-06-30")) %>%
  distinct(stock_id, year, quarter, .keep_all = TRUE) %>%
  arrange(stock_id, year, quarter)

TEJ reports operating income on a year-to-date (cumulative) basis. Single-quarter operating profit is therefore derived as:

  • Q1 = Q1 year-to-date value
  • Q2, Q3, Q4 = current year-to-date value − previous quarter’s year-to-date value

4. Data checks

checks <- data.frame(
  Item  = c("Observations (firm-quarters)", "Firms", "Quarters", "First quarter", "Last quarter"),
  Value = c(format(nrow(panel), big.mark = ","),
            format(n_distinct(panel$stock_id), big.mark = ","),
            n_distinct(panel$year_quarter),
            min(panel$year_quarter),
            max(panel$year_quarter))
)
kable(checks, caption = "Sample coverage")
Sample coverage
Item Value
Observations (firm-quarters) 131,775
Firms 2,996
Quarters 66
First quarter 2010Q1
Last quarter 2026Q2 
missing <- data.frame(
  Variable = c("total_equity", "market_value", "operating_profit"),
  Missing  = c(sum(is.na(panel$total_equity)),
               sum(is.na(panel$market_value)),
               sum(is.na(panel$operating_profit)))
) %>%
  mutate(Share = sprintf("%.1f%%", 100 * Missing / nrow(panel)))
kable(missing, format.args = list(big.mark = ","), caption = "Missing values")
Missing values
Variable Missing Share
total_equity 6 0.0%
market_value 11,791 8.9%
operating_profit 18,366 13.9% 
kable(as.data.frame(table(panel$op_status)),
      col.names = c("How quarterly operating profit was derived", "Count"),
      format.args = list(big.mark = ","))
How quarterly operating profit was derived Count
difference_from_previous_YTD 84,132
missing_previous_quarter_YTD 15,168
missing_ytd_operating_profit 3,198
Q1_equals_YTD 29,277

Notes on missing values

  • Operating profit: when the previous quarter’s year-to-date figure is not available in TEJ (e.g. firms that reported only semi-annually, or newly listed firms), a single-quarter value cannot be derived, so it is left missing rather than estimated.
  • Market value: missing when the stock was not yet listed or not traded at the end of the quarter.

5. Number of firms over time

panel %>%
  group_by(quarter_end) %>%
  summarise(firms = n_distinct(stock_id)) %>%
  ggplot(aes(quarter_end, firms)) +
  geom_line(color = "#2C3E50", linewidth = 0.8) +
  labs(x = NULL, y = "Number of firms",
       title = "Listed firms in the sample, by quarter") +
  theme_minimal()

6. Aggregate values over time

panel %>%
  group_by(quarter_end) %>%
  summarise(`Market value`     = sum(market_value, na.rm = TRUE) / 1e12,
            `Book value`       = sum(total_equity, na.rm = TRUE) / 1e12) %>%
  tidyr::pivot_longer(-quarter_end, names_to = "Variable", values_to = "value") %>%
  ggplot(aes(quarter_end, value, color = Variable)) +
  geom_line(linewidth = 0.8) +
  labs(x = NULL, y = "NT$ trillion",
       title = "Aggregate market value and book value of all firms") +
  theme_minimal() +
  theme(legend.position = "bottom", legend.title = element_blank())

7. Data preview

First 10 observations

panel %>%
  select(stock_id, company_name, year_quarter, operating_profit, total_equity, market_value) %>%
  head(10) %>%
  kable(format.args = list(big.mark = ",", scientific = FALSE))
stock_id company_name year_quarter operating_profit total_equity market_value
1101 TCC 2010Q1 2,152,334,000 102,157,504,000 98,600,641,000
1101 TCC 2010Q2 3,791,319,000 102,349,225,000 89,876,378,000
1101 TCC 2010Q3 4,176,068,000 109,586,305,000 109,958,645,000
1101 TCC 2010Q4 5,534,591,000 124,513,778,000 121,103,340,000
1101 TCC 2011Q1 3,425,462,000 128,728,151,000 131,072,213,000
1101 TCC 2011Q2 5,605,798,000 126,201,240,000 158,025,090,000
1101 TCC 2011Q3 4,357,630,000 125,190,869,000 121,472,558,000
1101 TCC 2011Q4 2,783,801,000 129,320,349,000 129,226,125,000
1101 TCC 2012Q1 2,286,803,000 141,483,035,000 127,564,646,000
1101 TCC 2012Q2 3,169,387,000 133,456,442,000 130,518,386,000

Example: TSMC (2330), last 4 quarters (NT$)

panel %>%
  filter(stock_id == "2330") %>%
  tail(4) %>%
  select(year_quarter, operating_profit, total_equity, market_value) %>%
  kable(format.args = list(big.mark = ",", scientific = FALSE))
year_quarter operating_profit total_equity market_value
2025Q3 500,684,818,000 5,035,577,802,000 33,842,063,880,000
2025Q4 564,902,413,000 5,460,795,283,000 40,195,413,750,000
2026Q1 658,966,142,000 5,932,388,921,000 45,641,244,000,000
2026Q2 766,602,651,000 6,474,470,981,000 62,497,011,700,000

Summary statistics (NT$)

summ <- function(x) c(N = sum(!is.na(x)), Mean = mean(x, na.rm = TRUE),
                      Median = median(x, na.rm = TRUE),
                      Min = min(x, na.rm = TRUE), Max = max(x, na.rm = TRUE))
stats <- rbind(`Operating profit` = summ(panel$operating_profit),
               `Book value`       = summ(panel$total_equity),
               `Market value`     = summ(panel$market_value))
kable(stats, digits = 0, format.args = list(big.mark = ",", scientific = FALSE))
N Mean Median Min Max
Operating profit 113,409 381,249,790 35,944,000 -38,720,187,000 766,602,651,000
Book value 131,769 13,676,620,289 1,924,428,000 -51,575,825,000 6,474,470,981,000
Market value 119,984 24,565,110,992 3,356,848,500 24,940,000 62,497,011,700,000

8. Save the final dataset

write_csv(panel, "taiwan_panel_2010Q1_2026Q2_NTD.csv", na = "")

The final dataset taiwan_panel_2010Q1_2026Q2_NTD.csv contains one row per firm-quarter with the following columns:

stock_id | company_name | quarter_end | year | quarter | year_quarter | total_equity | market_value | operating_profit | op_status