Data Profile
company <- readRDS("data/company.rds")
universe_q <- readRDS("data/universe_q.rds")
price_daily <- readRDS("data/price_daily.rds")
financials_q <- readRDS("data/financials_q.rds")
index_daily <- readRDS("data/index_daily.rds")
tribble(
~Table, ~Rows, ~Cols, ~`Primary Key`,
"company", nrow(company), ncol(company), "ticker",
"universe_q", nrow(universe_q), ncol(universe_q), "ticker, quarter_end",
"price_daily", nrow(price_daily), ncol(price_daily), "ticker, date",
"financials_q", nrow(financials_q), ncol(financials_q), "ticker, period_end",
"index_daily", nrow(index_daily), ncol(index_daily), "date"
) %>% academic_table("Table 1. Data Profile Summary", digits = 0)
Table 1. Data Profile Summary
|
Table
|
Rows
|
Cols
|
Primary Key
|
|
company
|
10
|
2
|
ticker
|
|
universe_q
|
1900
|
4
|
ticker, quarter_end
|
|
price_daily
|
13040
|
4
|
ticker, date
|
|
financials_q
|
1900
|
2
|
ticker, period_end
|
|
index_daily
|
0
|
1
|
date
|
Q1
q1_result <- price_daily %>%
mutate(year_month = floor_date(date, "month")) %>%
distinct(ticker, year_month) %>%
count(ticker, name = "months_count") %>%
filter(months_count >= 12) %>%
inner_join(company, by = "ticker") %>%
group_by(industry) %>%
summarise(num_qualified_stocks = n(), .groups = "drop")
academic_table(q1_result, "Table 2. Number of Qualified Stocks by Industry", digits = 0)
Table 2. Number of Qualified Stocks by Industry
|
industry
|
num_qualified_stocks
|
|
Consumer Goods
|
1
|
|
Electronics
|
3
|
|
Finance
|
3
|
|
Semiconductor
|
2
|
|
Telecom
|
1
|
Q2
universe_sorted <- universe_q %>% arrange(quarter_end, ticker)
q2_result <- universe_sorted %>%
group_by(quarter_end) %>%
summarise(tickers = list(ticker), .groups = "drop") %>%
mutate(
prev_tickers = lag(tickers),
entered = map2_int(tickers, prev_tickers, ~ length(setdiff(.x, .y))),
left = map2_int(prev_tickers, tickers, ~ length(setdiff(.x, .y)))
) %>%
filter(row_number() > 1) %>% # эхний улирал нь харьцуулах өмнөх улиралгүй
select(quarter_end, entered, left)
academic_table(q2_result, "Table 3. Universe Entries and Exits by Quarter", digits = 0)
Table 3. Universe Entries and Exits by Quarter
|
quarter_end
|
entered
|
left
|
|
2021-03-31
|
4
|
4
|
|
2021-06-30
|
10
|
10
|
|
2021-09-30
|
6
|
6
|
|
2021-12-30
|
4
|
4
|
|
2022-03-31
|
6
|
6
|
|
2022-06-30
|
7
|
7
|
|
2022-09-30
|
7
|
7
|
|
2022-12-30
|
3
|
3
|
|
2023-03-31
|
5
|
5
|
|
2023-06-30
|
5
|
5
|
|
2023-09-28
|
6
|
6
|
|
2023-12-29
|
3
|
3
|
|
2024-03-29
|
5
|
5
|
|
2024-06-28
|
3
|
3
|
|
2024-09-30
|
3
|
3
|
|
2024-12-31
|
5
|
5
|
|
2025-03-31
|
4
|
4
|
|
2025-06-30
|
4
|
4
|
Q3
monthly_prices <- price_daily %>%
mutate(year_month = floor_date(date, "month")) %>%
group_by(ticker, year_month) %>%
slice_max(date, n = 1) %>%
ungroup() %>%
arrange(ticker, year_month)
q3_result <- monthly_prices %>%
group_by(ticker) %>%
mutate(monthly_return = (adj_close / lag(adj_close)) - 1) %>%
ungroup() %>%
filter(!is.na(monthly_return))
academic_table(head(q3_result, 10), "Table 4. Monthly Returns (first 10 rows)", digits = 4)
Table 4. Monthly Returns (first 10 rows)
|
ticker
|
date
|
close
|
adj_close
|
year_month
|
monthly_return
|
|
1216
|
2021-02-26
|
981.1620
|
983.4154
|
2021-02-01
|
5.5723
|
|
1216
|
2021-03-31
|
272.7645
|
273.7026
|
2021-03-01
|
-0.7217
|
|
1216
|
2021-04-30
|
343.7693
|
348.2676
|
2021-04-01
|
0.2724
|
|
1216
|
2021-05-31
|
716.6918
|
706.5519
|
2021-05-01
|
1.0288
|
|
1216
|
2021-06-30
|
993.4998
|
999.3348
|
2021-06-01
|
0.4144
|
|
1216
|
2021-07-30
|
490.9937
|
494.3888
|
2021-07-01
|
-0.5053
|
|
1216
|
2021-08-31
|
610.4591
|
618.1387
|
2021-08-01
|
0.2503
|
|
1216
|
2021-09-30
|
233.7900
|
238.2072
|
2021-09-01
|
-0.6146
|
|
1216
|
2021-10-29
|
399.9548
|
395.6909
|
2021-10-01
|
0.6611
|
|
1216
|
2021-11-30
|
67.5991
|
68.0283
|
2021-11-01
|
-0.8281
|
Q4
q4_result <- q3_result %>%
group_by(ticker) %>%
summarise(
avg_monthly_return = mean(monthly_return, na.rm = TRUE),
volatility = sd(monthly_return, na.rm = TRUE),
.groups = "drop"
)
academic_table(head(q4_result, 10),
"Table 5. Average Monthly Return and Volatility by Ticker", digits = 4)
Table 5. Average Monthly Return and Volatility by Ticker
|
ticker
|
avg_monthly_return
|
volatility
|
|
1216
|
0.7861
|
2.0119
|
|
2308
|
0.6958
|
2.3414
|
|
2317
|
0.7398
|
1.9184
|
|
2330
|
1.0622
|
3.0176
|
|
2412
|
0.3669
|
1.2422
|
|
2454
|
0.7082
|
1.9818
|
|
2881
|
0.4554
|
1.7798
|
|
2882
|
1.1845
|
3.3790
|
|
2891
|
0.4521
|
1.3673
|
|
3008
|
0.7526
|
2.2707
|
Q5
q5_result <- price_daily %>%
arrange(ticker, date) %>%
group_by(ticker) %>%
mutate(day_gap = as.numeric(date - lag(date))) %>%
filter(day_gap > 10) %>%
ungroup()
if (nrow(q5_result) == 0) {
cat("**Result:** No price gaps longer than 10 days were found in `price_daily`.",
"The maximum gap between consecutive observations was",
max(price_daily %>% arrange(ticker, date) %>% group_by(ticker) %>%
mutate(g = as.numeric(date - lag(date))) %>% pull(g), na.rm = TRUE),
"days.")
} else {
print(academic_table(head(q5_result, 10),
"Table 6. Price Gaps Longer Than 10 Days", digits = 2))
}
Result: No price gaps longer than 10 days were found
in price_daily. The maximum gap between consecutive
observations was 3 days.
Q6
q6_result <- universe_q %>%
inner_join(financials_q, by = "ticker", relationship = "many-to-many") %>%
distinct(ticker, quarter_end)
academic_table(head(q6_result, 10), "Table 7. Universe Members with Financial Data (first 10 rows)", digits = 0)
Table 7. Universe Members with Financial Data (first 10 rows)
|
ticker
|
quarter_end
|
|
character(0)
|
c(“:——:”, “:———–:”)
|