# Runs queries.R (loads the tables from data/, builds q1..q6 and q3 etc.)
# Everything it prints is hidden here; results are shown below.
source("queries.R", local = knitr::knit_global())

1. Data profile (Part 1, Task 7)

All five tables were built by 01_build_universe.R and 02_build_tables.R from the unmodified downloads in raw/.

1.1 Integrity checks

dup <- function(df, keys) nrow(df %>% count(across(all_of(keys))) %>% filter(n > 1))
na_key <- function(df, keys) sum(!complete.cases(df[, keys]))

per_q <- universe_q %>% count(quarter_end)

checks <- tribble(
  ~check,                                          ~result,
  "universe_q: exactly 100 rows per quarter-end",  all(per_q$n == 100),
  "universe_q: 20 quarter-end dates",              nrow(per_q) == 20,
  "company: duplicate keys (ticker)",              dup(company, "ticker") == 0,
  "universe_q: duplicate keys",                    dup(universe_q, c("ticker", "quarter_end")) == 0,
  "price_daily: duplicate keys",                   dup(price_daily, c("ticker", "date")) == 0,
  "financials_q: duplicate keys",                  dup(financials_q, c("ticker", "period_end")) == 0,
  "index_daily: duplicate keys (date)",            dup(index_daily, "date") == 0,
  "Missing key values (all tables)",               (na_key(company, "ticker") + na_key(universe_q, c("ticker", "quarter_end")) +
                                                    na_key(price_daily, c("ticker", "date")) +
                                                    na_key(financials_q, c("ticker", "period_end")) +
                                                    na_key(index_daily, "date")) == 0,
  "Orphans: universe_q not in company",            nrow(anti_join(universe_q, company, by = "ticker")) == 0,
  "Orphans: price_daily not in company",           nrow(anti_join(price_daily, company, by = "ticker")) == 0,
  "Orphans: financials_q not in company",          nrow(anti_join(financials_q, company, by = "ticker")) == 0
) %>% mutate(result = if_else(result, "PASS", "FAIL"))

knitr::kable(checks)
check result
universe_q: exactly 100 rows per quarter-end PASS
universe_q: 20 quarter-end dates PASS
company: duplicate keys (ticker) PASS
universe_q: duplicate keys PASS
price_daily: duplicate keys PASS
financials_q: duplicate keys PASS
index_daily: duplicate keys (date) PASS
Missing key values (all tables) PASS
Orphans: universe_q not in company PASS
Orphans: price_daily not in company PASS
Orphans: financials_q not in company PASS

1.2 Row counts, date ranges, tickers

profile_tbl <- function(df, name, date_col = NULL) {
  d <- if (!is.null(date_col)) df[[date_col]] else NULL
  tibble(table     = name,
         rows      = nrow(df),
         columns   = ncol(df),
         date_from = if (!is.null(d)) as.character(min(d, na.rm = TRUE)) else NA_character_,
         date_to   = if (!is.null(d)) as.character(max(d, na.rm = TRUE)) else NA_character_,
         n_tickers = if ("ticker" %in% names(df)) n_distinct(df$ticker) else NA_integer_)
}

profile <- bind_rows(
  profile_tbl(company,      "company",      "listing_date"),
  profile_tbl(universe_q,   "universe_q",   "quarter_end"),
  profile_tbl(price_daily,  "price_daily",  "date"),
  profile_tbl(financials_q, "financials_q", "period_end"),
  profile_tbl(index_daily,  "index_daily",  "date")
)
knitr::kable(profile, format.args = list(big.mark = ","))
table rows columns date_from date_to n_tickers
company 139 5 1962-02-09 2025-11-27 139
universe_q 2,000 6 2020-12-31 2025-09-30 139
price_daily 140,529 7 2020-01-02 2024-04-18 137
financials_q 3,559 8 2019-09-30 2025-12-31 139
index_daily 1,041 2 2020-01-02 2024-04-18 NA

1.3 Percent missing by column

miss_tbl <- function(df, name) {
  tibble(table = name, column = names(df),
         type = map_chr(df, ~ class(.x)[1]),
         pct_missing = round(100 * map_dbl(df, ~ mean(is.na(.x))), 2))
}
missing <- bind_rows(miss_tbl(company, "company"), miss_tbl(universe_q, "universe_q"),
                     miss_tbl(price_daily, "price_daily"), miss_tbl(financials_q, "financials_q"),
                     miss_tbl(index_daily, "index_daily"))
show(missing, 15)
table column type pct_missing
company ticker character 0
company company_name character 0
company industry character 0
company listing_date Date 0
company delisting_date Date 100
universe_q ticker character 0
universe_q quarter_end Date 0
universe_q mktcap numeric 0
universe_q rank numeric 0
universe_q market character 0
universe_q industry_code character 0
price_daily ticker character 0
price_daily date Date 0
price_daily adj_close numeric 0
price_daily volume numeric 0

Comment. All key checks pass (11 of 11). The most incomplete column is company.delisting_date (100.00% missing); the cleaning log explains how missing values were handled.

2. Query results (Part 2)

Q1. Stocks with at least 12 months of price data, by industry

show(q1, 10)
industry n_stocks
M23G 21
M23A 13
M23D 13
M28L 12
M23B 7
M26A 5
M15B 4
M17C 4
M23C 4
M23K 4 
q1 %>% slice_head(n = 15) %>%
  ggplot(aes(x = reorder(industry, n_stocks), y = n_stocks)) +
  geom_col(fill = "#2b6cb0") + coord_flip() +
  labs(x = "TEJ industry code", y = "Number of stocks",
       title = "Top 15 industries by number of stocks with 12+ months of prices") +
  theme_minimal(base_size = 12)

Comment. 137 stocks have at least 12 months of price data across 40 TEJ industry codes. The largest group is M23G with 21 stocks.

Q2. Top-100 turnover versus the previous quarter-end

show(q2 %>% mutate(turnover_pct = round(turnover_pct, 1)), 10)
quarter_end prev_quarter_end n_entered n_left n_stayed turnover_pct
2021-03-31 2020-12-31 4 4 96 4
2021-06-30 2021-03-31 10 10 90 10
2021-09-30 2021-06-30 6 6 94 6
2021-12-30 2021-09-30 4 4 96 4
2022-03-31 2021-12-30 6 6 94 6
2022-06-30 2022-03-31 7 7 93 7
2022-09-30 2022-06-30 7 7 93 7
2022-12-30 2022-09-30 3 3 97 3
2023-03-31 2022-12-30 5 5 95 5
2023-06-30 2023-03-31 5 5 95 5 
q2 %>%
  pivot_longer(c(n_entered, n_left), names_to = "type", values_to = "n") %>%
  ggplot(aes(quarter_end, n, fill = type)) +
  geom_col(position = "dodge") +
  scale_fill_manual(values = c(n_entered = "#2f855a", n_left = "#c53030"),
                    labels = c("Entered", "Left"), name = NULL) +
  labs(x = NULL, y = "Stocks", title = "Entries and exits of the top 100, by quarter-end") +
  theme_minimal(base_size = 12)

Comment. On average 4.9 stocks enter and 4.9 leave the top 100 each quarter (about 4.9% turnover). Turnover peaked at 10 stocks in the quarter ending 2021-06-30. Entries equal exits because the universe is always exactly 100 stocks.

Q3. Monthly returns from month-end adjusted prices

show(q3 %>% mutate(ret = round(ret, 4)), 10)
ticker month px_date adj_close ret outlier_flag
1101 2020-01-01 2020-01-31 27.9577 NA FALSE
1101 2020-02-01 2020-02-27 28.0907 0.0048 FALSE
1101 2020-03-01 2020-03-31 26.2623 -0.0651 FALSE
1101 2020-04-01 2020-04-30 28.7223 0.0937 FALSE
1101 2020-05-01 2020-05-29 28.2901 -0.0150 FALSE
1101 2020-06-01 2020-06-30 28.4231 0.0047 FALSE
1101 2020-07-01 2020-07-31 29.9191 0.0526 FALSE
1101 2020-08-01 2020-08-31 31.6542 0.0580 FALSE
1101 2020-09-01 2020-09-30 30.5474 -0.0350 FALSE
1101 2020-10-01 2020-10-30 29.9203 -0.0205 FALSE 
q3 %>% filter(!is.na(ret), abs(ret) <= 1) %>%
  ggplot(aes(ret)) +
  geom_histogram(bins = 80, fill = "#2b6cb0") +
  scale_x_continuous(labels = scales::percent) +
  labs(x = "Monthly return", y = "Stock-months",
       title = "Distribution of monthly returns (|return| <= 100%)") +
  theme_minimal(base_size = 12)

Comment. The table has 7,020 stock-months, of which 137 have no return (first month of a stock or a gap in prices). 7 observations exceed 100% in absolute value and are flagged in outlier_flag for review.

Q4. Average monthly return and volatility by industry

show(q4 %>% mutate(across(c(avg_monthly_ret, volatility), ~ round(.x, 4))), 10)
industry n_stocks n_obs avg_monthly_ret volatility
M17C 4 136 0.0639 0.2480
M15B 4 160 0.0514 0.1397
M20B 1 40 0.0469 0.1374
M26A 5 200 0.0438 0.2391
M16 1 40 0.0318 0.1571
M23T 3 120 0.0308 0.0896
M23A 13 520 0.0273 0.1057
M26D 2 80 0.0260 0.1347
M23B 7 280 0.0234 0.1276
M23H 2 80 0.0210 0.0861 
q4 %>% filter(n_stocks >= 3) %>%
  ggplot(aes(volatility, avg_monthly_ret, size = n_stocks, label = industry)) +
  geom_point(alpha = 0.6, colour = "#2b6cb0") +
  geom_text(size = 3, vjust = -1, check_overlap = TRUE) +
  scale_x_continuous(labels = scales::percent) +
  scale_y_continuous(labels = scales::percent) +
  labs(x = "Volatility (sd of monthly return)", y = "Average monthly return",
       size = "Stocks", title = "Risk and return by industry (industries with 3+ stocks)") +
  theme_minimal(base_size = 12)

Comment. Among industries with at least 3 stocks, M17C had the highest average monthly return (6.39%) and M13A the lowest (-0.41%). Industries with only one or two stocks give noisy averages and should be read with care.

Q5. Stocks with a gap of more than 10 trading days without a price

if (nrow(q5) > 0) show(q5, 10) else knitr::kable(head(q5_max_gap, 10))
ticker max_gap_trading_days
2201 7
2327 7
2368 7
2409 7
2603 7
3481 7
9945 7
6531 6
6919 6
6415 5

Comment. No stock has a gap longer than 10 trading days; the longest gap in the data is 7 trading days (table above shows the largest gap per ticker). A day counts as having a price only if the adjusted close exists and volume is positive.

Q6. The analysis table

Rule used to avoid look-ahead bias: at the end of month t only the latest quarter whose statutory deadline has passed is used (Q1 by May 15, Q2 by Aug 14, Q3 by Nov 14, annual by Mar 31 of the next year). A check in the code stops the script if any statement is used before its deadline.

show(q6 %>% mutate(across(c(ret_next, log_mktcap, bm, roe), ~ round(.x, 4))), 10)
ticker month month_end ret_next log_mktcap bm roe fin_period_end industry
1101 2021-01-01 2021-01-29 0.0461 19.2777 0.8810 0.1227 2020-09-30 M11A
1101 2021-02-01 2021-02-26 0.1143 19.3274 0.8383 0.1227 2020-09-30 M11A
1101 2021-03-01 2021-03-31 0.1132 19.4460 0.7849 0.1175 2020-12-31 M11A
1101 2021-04-01 2021-04-29 -0.0096 19.5701 0.6933 0.1175 2020-12-31 M11A
1101 2021-05-01 2021-05-31 -0.0116 19.5604 0.7218 0.1174 2021-03-31 M11A
1101 2021-06-01 2021-06-30 0.0314 19.5595 0.7225 0.1174 2021-03-31 M11A
1101 2021-07-01 2021-07-30 -0.0139 19.5904 0.7005 0.1174 2021-03-31 M11A
1101 2021-08-01 2021-08-31 0.0526 19.5082 0.7770 0.1172 2021-06-30 M11A
1101 2021-09-01 2021-09-30 -0.0539 19.5596 0.7381 0.1172 2021-06-30 M11A
1101 2021-10-01 2021-10-29 -0.0466 19.5043 0.7800 0.1172 2021-06-30 M11A 
q6_sum <- q6 %>%
  summarise(across(c(ret_next, log_mktcap, bm, roe),
                   list(n = ~ sum(!is.na(.x)), mean = ~ mean(.x, na.rm = TRUE),
                        sd = ~ sd(.x, na.rm = TRUE), min = ~ min(.x, na.rm = TRUE),
                        p50 = ~ median(.x, na.rm = TRUE), max = ~ max(.x, na.rm = TRUE)),
                   .names = "{.col}__{.fn}")) %>%
  pivot_longer(everything(), names_to = c("variable", "stat"), names_sep = "__") %>%
  pivot_wider(names_from = stat, values_from = value) %>%
  mutate(across(-variable, ~ round(.x, 4)))
knitr::kable(q6_sum)
variable n mean sd min p50 max
ret_next 3900 0.0132 0.1056 -0.3589 0.0058 1.2138
log_mktcap 2400 19.0959 0.8784 17.3642 18.9580 23.5261
bm 2400 0.6119 0.4354 0.0221 0.5344 2.8214
roe 3996 0.1705 0.1690 -0.7957 0.1345 1.2354 
q6 %>% filter(!is.na(bm), !is.na(ret_next), bm < quantile(bm, 0.99, na.rm = TRUE)) %>%
  ggplot(aes(bm, ret_next)) +
  geom_point(alpha = 0.15, colour = "#2b6cb0") +
  geom_smooth(method = "lm", colour = "#c53030", se = FALSE) +
  scale_y_continuous(labels = scales::percent, limits = c(-0.4, 0.5)) +
  labs(x = "Book-to-market", y = "Next-month return",
       title = "Next-month return vs book-to-market (descriptive only)") +
  theme_minimal(base_size = 12)

Comment. The final table has 4,000 stock-months for 133 stocks. 0 of 4,000 observations (0.00%) were dropped because book equity was zero or negative. ret_next is missing for 100 rows (mostly the last month of the sample) and roe for 4 rows (not enough quarters of history). No statistical test is run here.

3. Notes

  • All code is in 01_build_universe.R, 02_build_tables.R and queries.R; see README.md for the run order and sessionInfo().
  • Market capitalization for ranking and for Q6 uses unadjusted close × shares outstanding; returns use adjusted close.
  • Details of every cleaning decision are in cleaning_log.md.
sessionInfo()
## R version 4.6.1 (2026-06-24 ucrt)
## Platform: x86_64-w64-mingw32/x64
## Running under: Windows 11 x64 (build 26200)
## 
## Matrix products: default
##   LAPACK version 3.12.1
## 
## locale:
## [1] LC_COLLATE=English_United States.utf8 
## [2] LC_CTYPE=English_United States.utf8   
## [3] LC_MONETARY=English_United States.utf8
## [4] LC_NUMERIC=C                          
## [5] LC_TIME=English_United States.utf8    
## 
## time zone: Asia/Ulaanbaatar
## tzcode source: internal
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] ggplot2_4.0.3   purrr_1.2.2     lubridate_1.9.5 readr_2.2.0    
## [5] tidyr_1.3.2     dplyr_1.2.1    
## 
## loaded via a namespace (and not attached):
##  [1] sass_0.4.10        utf8_1.2.6         generics_0.1.4     lattice_0.22-9    
##  [5] hms_1.1.4          digest_0.6.39      magrittr_2.0.5     evaluate_1.0.5    
##  [9] grid_4.6.1         timechange_0.4.0   RColorBrewer_1.1-3 fastmap_1.2.0     
## [13] Matrix_1.7-5       jsonlite_2.0.0     mgcv_1.9-4         scales_1.4.0      
## [17] jquerylib_0.1.4    cli_3.6.6          rlang_1.3.0        crayon_1.5.3      
## [21] bit64_4.8.6        splines_4.6.1      withr_3.0.3        cachem_1.1.0      
## [25] yaml_2.3.12        otel_0.2.0         tools_4.6.1        parallel_4.6.1    
## [29] tzdb_0.5.0         vctrs_0.7.3        R6_2.6.1           lifecycle_1.0.5   
## [33] bit_4.6.0          vroom_1.7.1        pkgconfig_2.0.3    pillar_1.11.1     
## [37] bslib_0.12.0       gtable_0.3.6       glue_1.8.1         xfun_0.60         
## [41] tibble_3.3.1       tidyselect_1.2.1   rstudioapi_0.19.0  knitr_1.52        
## [45] farver_2.1.2       htmltools_0.5.9    nlme_3.1-169       rmarkdown_2.32    
## [49] labeling_0.4.3     compiler_4.6.1     S7_0.2.2