HK ETF 7552 Daily

# Load data from star schema
etf_daily <- open_dataset(DAILY_PARQUET_DIR)

# Filter for stock_code 2800
etf_2800_all <- etf_daily %>%
  filter(stock_code == STOCK_CODE) %>%
  collect()

if (nrow(etf_2800_all) == 0) {
  stop("No data found for stock_code ", STOCK_CODE)
}

# Ensure trade_date is Date type
etf_2800_all$trade_date <- as.Date(etf_2800_all$trade_date)

# Sort by date (most recent first)
etf_2800_all <- etf_2800_all %>%
  arrange(desc(trade_date))

# Get last 5 days and last 30 days
etf_2800_recent <- etf_2800_all %>% slice_head(n = DAYS_TO_SHOW)
etf_2800_last30 <- etf_2800_all %>% slice_head(n = 30)

# Key variables
key_vars <- c("trade_date", "volume_cleaned", "turnover_cleaned", "aum_cleaned", 
              "closing_price", "nav", "day_high", "day_low", 
              "outstanding_units_cleaned", "premium_discount_percent")

# Calculate historical statistics (needed early for comparisons)
stats <- etf_2800_all %>%
  summarise(
    volume_median = median(volume_cleaned, na.rm = TRUE),
    volume_mean = mean(volume_cleaned, na.rm = TRUE),
    volume_min = min(volume_cleaned, na.rm = TRUE),
    volume_max = max(volume_cleaned, na.rm = TRUE),
    turnover_median = median(turnover_cleaned, na.rm = TRUE),
    turnover_mean = mean(turnover_cleaned, na.rm = TRUE),
    turnover_min = min(turnover_cleaned, na.rm = TRUE),
    turnover_max = max(turnover_cleaned, na.rm = TRUE),
    total_days = n()
  )

# Prepare comparison data
comparison <- etf_2800_recent %>%
  select(trade_date, volume_cleaned, turnover_cleaned) %>%
  mutate(
    volume_vs_median = ifelse(is.na(volume_cleaned), NA, volume_cleaned / stats$volume_median),
    volume_vs_mean = ifelse(is.na(volume_cleaned), NA, volume_cleaned / stats$volume_mean),
    turnover_vs_median = ifelse(is.na(turnover_cleaned), NA, turnover_cleaned / stats$turnover_median),
    turnover_vs_mean = ifelse(is.na(turnover_cleaned), NA, turnover_cleaned / stats$turnover_mean),
    volume_pct_rank = NA_real_,
    turnover_pct_rank = NA_real_
  )

# Calculate percentile ranks
for (i in 1:nrow(comparison)) {
  if (!is.na(comparison$volume_cleaned[i])) {
    comparison$volume_pct_rank[i] <- mean(etf_2800_all$volume_cleaned <= comparison$volume_cleaned[i], na.rm = TRUE) * 100
  }
  if (!is.na(comparison$turnover_cleaned[i])) {
    comparison$turnover_pct_rank[i] <- mean(etf_2800_all$turnover_cleaned <= comparison$turnover_cleaned[i], na.rm = TRUE) * 100
  }
}

Data Overview

cat("**Stock Code:**", STOCK_CODE, "\n\n")

## **Stock Code:** 7552

cat("**Total Records:**", nrow(etf_2800_all), "\n\n")

## **Total Records:** 65

cat("**Date Range:**", format(min(etf_2800_all$trade_date)), "to", format(max(etf_2800_all$trade_date)), "\n\n")

## **Date Range:** 2025-09-17 to 2025-12-19

cat("**Last Updated:**", format(Sys.Date()))

## **Last Updated:** 2025-12-20

---

Closing Price Trend

# Prepare data for chart - order by date ascending for line chart
chart_data <- etf_2800_all %>%
  select(trade_date, closing_price) %>%
  arrange(trade_date) %>%
  mutate(
    is_latest = trade_date == max(trade_date, na.rm = TRUE)
  )

# Get latest date for labeling
latest_date <- max(chart_data$trade_date, na.rm = TRUE)
latest_price <- chart_data$closing_price[chart_data$trade_date == latest_date][1]

ggplot(chart_data, aes(x = trade_date, y = closing_price)) +
  geom_line(color = "#007bff", size = 0.8) +
  geom_point(data = chart_data %>% filter(is_latest), 
             color = "red", size = 3, shape = 19) +
  geom_point(data = chart_data %>% filter(!is_latest), 
             color = "#007bff", size = 0.5, alpha = 0.6) +
  labs(
    title = paste("Closing Price Trend - ETF", STOCK_CODE),
    subtitle = paste("Latest:", format(latest_date), "- Price:", ifelse(is.na(latest_price), "NA", formatC(latest_price, format = "f", digits = 4))),
    x = "Date",
    y = "Closing Price (HKD)",
    caption = "Red dot indicates latest data point"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 14, face = "bold"),
    plot.subtitle = element_text(size = 11, color = "gray50"),
    axis.title = element_text(size = 11),
    axis.text = element_text(size = 9),
    plot.caption = element_text(size = 9, color = "gray60", hjust = 0),
    panel.grid.minor = element_blank()
  ) +
  scale_x_date(date_labels = "%Y-%m-%d", date_breaks = "1 month") +
  scale_y_continuous(labels = scales::number_format(accuracy = 0.01))

---

Latest Date Verification

latest_date <- max(etf_2800_all$trade_date)
dec_16 <- etf_2800_recent %>%
  filter(trade_date == latest_date)

if (nrow(dec_16) > 0) {
  # Create a summary table
  latest_summary <- data.frame(
    Metric = c("Date", "Volume", "Turnover", "AUM", "Closing Price", "NAV", "Suspension Flag"),
    Value = c(
      format(latest_date),
      ifelse(is.na(dec_16$volume_cleaned[1]), "NA (missing/suspended)", 
             formatC(dec_16$volume_cleaned[1], format = "d", big.mark = ",")),
      ifelse(is.na(dec_16$turnover_cleaned[1]), "NA (missing/suspended)",
             formatC(dec_16$turnover_cleaned[1], format = "f", digits = 2, big.mark = ",")),
      ifelse(is.na(dec_16$aum_cleaned[1]), "NA",
             formatC(dec_16$aum_cleaned[1], format = "f", digits = 0, big.mark = ",")),
      ifelse(is.na(dec_16$closing_price[1]), "NA",
             formatC(dec_16$closing_price[1], format = "f", digits = 4)),
      ifelse(is.na(dec_16$nav[1]), "NA",
             formatC(dec_16$nav[1], format = "f", digits = 4)),
      ifelse(is.na(dec_16$suspension_flag[1]), "NA", dec_16$suspension_flag[1])
    ),
    stringsAsFactors = FALSE
  )
  
  # Add comparison metrics if available
  if (!is.na(dec_16$volume_cleaned[1]) && !is.na(dec_16$turnover_cleaned[1])) {
    vol_idx <- which(comparison$trade_date == latest_date)
    comparison_metrics <- data.frame(
      Metric = c("Volume vs Median", "Volume vs Mean", 
                 "Turnover vs Median", "Turnover vs Mean"),
      Value = c(
        sprintf("%.2fx (%s percentile)", 
                dec_16$volume_cleaned[1] / stats$volume_median,
                sprintf("%.1f%%", comparison$volume_pct_rank[vol_idx])),
        sprintf("%.2fx", dec_16$volume_cleaned[1] / stats$volume_mean),
        sprintf("%.2fx (%s percentile)",
                dec_16$turnover_cleaned[1] / stats$turnover_median,
                sprintf("%.1f%%", comparison$turnover_pct_rank[vol_idx])),
        sprintf("%.2fx", dec_16$turnover_cleaned[1] / stats$turnover_mean)
      ),
      stringsAsFactors = FALSE
    )
    latest_summary <- rbind(latest_summary, comparison_metrics)
  }
  
  latest_summary %>%
    gt() %>%
    tab_header(
      title = "Latest Date Data Summary",
      subtitle = paste("ETF", STOCK_CODE, "-", format(latest_date))
    ) %>%
    cols_label(
      Metric = "Metric",
      Value = "Value"
    ) %>%
    tab_style(
      style = cell_text(weight = "bold"),
      locations = cells_column_labels()
    ) %>%
    tab_style(
      style = cell_text(align = "left"),
      locations = cells_body(columns = Metric)
    ) %>%
    tab_style(
      style = cell_text(align = "right"),
      locations = cells_body(columns = Value)
    ) %>%
    opt_table_font(font = "Arial") %>%
    tab_options(
      table.width = pct(60),
      column_labels.background.color = "#f8f9fa",
      table_body.hlines.color = "#e9ecef",
      heading.border.bottom.color = "#dee2e6"
    )
} else {
  cat("⚠ Warning: No data found for latest date\n")
}

Latest Date Data Summary
ETF 7552 - 2025-12-19
Metric	Value
Date	2025-12-19
Volume	308,091,800
Turnover	451,222,446.00
AUM	NA
Closing Price	1.4700
NAV	NA
Suspension Flag	No
Volume vs Median	0.64x (18.5% percentile)
Volume vs Mean	0.56x
Turnover vs Median	0.74x (26.2% percentile)
Turnover vs Mean	0.65x

---

Comparison: Recent vs Historical

Percentile Explanation: Percentile rank shows what percentage of historical values are less than or equal to the current value. For example, a 75th percentile means the current value is higher than 75% of all historical values (higher is better for volume/turnover). A 25th percentile means it’s higher than only 25% of historical values (lower activity).

comparison_display <- comparison %>%
  select(trade_date, volume_cleaned, volume_vs_median, volume_vs_mean, volume_pct_rank,
         turnover_cleaned, turnover_vs_median, turnover_vs_mean, turnover_pct_rank)

comparison_display %>%
  gt() %>%
  tab_header(
    title = "Recent Performance vs Historical Statistics",
    subtitle = paste("ETF", STOCK_CODE, "- Comparison of last 5 days")
  ) %>%
  fmt_date(columns = trade_date, date_style = "yMd") %>%
  fmt_number(columns = volume_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = volume_vs_median, decimals = 2, pattern = "{x}x") %>%
  fmt_number(columns = volume_vs_mean, decimals = 2, pattern = "{x}x") %>%
  fmt_number(columns = volume_pct_rank, decimals = 1, pattern = "{x}%") %>%
  fmt_number(columns = turnover_cleaned, decimals = 2, use_seps = TRUE) %>%
  fmt_number(columns = turnover_vs_median, decimals = 2, pattern = "{x}x") %>%
  fmt_number(columns = turnover_vs_mean, decimals = 2, pattern = "{x}x") %>%
  fmt_number(columns = turnover_pct_rank, decimals = 1, pattern = "{x}%") %>%
  sub_missing(columns = everything(), missing_text = "NA") %>%
  cols_label(
    trade_date = "Date",
    volume_cleaned = "Volume",
    volume_vs_median = "vs Median",
    volume_vs_mean = "vs Mean",
    volume_pct_rank = "Percentile",
    turnover_cleaned = "Turnover",
    turnover_vs_median = "vs Median",
    turnover_vs_mean = "vs Mean",
    turnover_pct_rank = "Percentile"
  ) %>%
  tab_spanner(
    label = "Volume",
    columns = c(volume_cleaned, volume_vs_median, volume_vs_mean, volume_pct_rank)
  ) %>%
  tab_spanner(
    label = "Turnover",
    columns = c(turnover_cleaned, turnover_vs_median, turnover_vs_mean, turnover_pct_rank)
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body()
  ) %>%
  tab_style(
    style = cell_text(weight = "bold", align = "center"),
    locations = cells_column_spanners()
  ) %>%
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.width = pct(100),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef",
    heading.border.bottom.color = "#dee2e6"
  )

Recent Performance vs Historical Statistics
ETF 7552 - Comparison of last 5 days
Date	Volume				Turnover
	Volume	vs Median	vs Mean	Percentile	Turnover	vs Median	vs Mean	Percentile
12/19/2025	308,091,800	0.64x	0.56x	18.5%	451,222,446.00	0.74x	0.65x	26.2%
12/18/2025	260,428,102	0.54x	0.47x	6.2%	392,957,621.00	0.64x	0.57x	13.8%
12/17/2025	292,510,003	0.61x	0.53x	15.4%	436,157,084.00	0.72x	0.63x	21.5%
12/16/2025	434,770,201	0.90x	0.79x	43.1%	656,449,663.00	1.08x	0.94x	55.4%
12/15/2025	315,378,360	0.66x	0.57x	23.1%	452,139,147.00	0.74x	0.65x	27.7%

---

Recent 5 Days

display_data <- etf_2800_recent %>%
  select(any_of(key_vars))

display_data %>%
  gt() %>%
  tab_header(
    title = "Recent 5 Days - Key Metrics",
    subtitle = paste("ETF", STOCK_CODE)
  ) %>%
  fmt_date(columns = trade_date, date_style = "yMd") %>%
  fmt_number(columns = volume_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = turnover_cleaned, decimals = 2, use_seps = TRUE) %>%
  fmt_number(columns = aum_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = closing_price, decimals = 4) %>%
  fmt_number(columns = nav, decimals = 4) %>%
  fmt_number(columns = day_high, decimals = 4) %>%
  fmt_number(columns = day_low, decimals = 4) %>%
  fmt_number(columns = outstanding_units_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = premium_discount_percent, decimals = 2) %>%
  cols_label(
    trade_date = "Date",
    volume_cleaned = "Volume",
    turnover_cleaned = "Turnover",
    aum_cleaned = "AUM",
    closing_price = "Close Price",
    nav = "NAV",
    day_high = "Day High",
    day_low = "Day Low",
    outstanding_units_cleaned = "Outstanding Units",
    premium_discount_percent = "Premium/Discount %"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body()
  ) %>%
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.width = pct(100),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef",
    heading.border.bottom.color = "#dee2e6"
  )

Recent 5 Days - Key Metrics
ETF 7552
Date	Volume	Turnover	AUM	Close Price	NAV	Day High	Day Low	Outstanding Units	Premium/Discount %
12/19/2025	308,091,800	451,222,446.00	NA	1.4700	NA	1.4900	1.4500	2,957,400,000	NA
12/18/2025	260,428,102	392,957,621.00	NA	1.5000	NA	1.5300	1.4900	2,957,400,000	−0.03
12/17/2025	292,510,003	436,157,084.00	4,360,000,000	1.4800	1.4800	1.5200	1.4600	2,957,400,000	0.07
12/16/2025	434,770,201	656,449,663.00	4,670,000,000	1.5100	1.5100	1.5300	1.4600	3,101,800,000	−0.01
12/15/2025	315,378,360	452,139,147.00	4,550,000,000	1.4500	1.4600	1.4600	1.4100	3,123,000,000	−0.22

---

Recent 30 Days

table_30days <- etf_2800_last30 %>%
  select(any_of(key_vars)) %>%
  arrange(desc(trade_date))

table_30days %>%
  gt() %>%
  tab_header(
    title = "Recent 30 Days - Complete Data",
    subtitle = paste("ETF", STOCK_CODE, "- Most recent dates first")
  ) %>%
  fmt_date(columns = trade_date, date_style = "yMd") %>%
  fmt_number(columns = volume_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = turnover_cleaned, decimals = 2, use_seps = TRUE) %>%
  fmt_number(columns = aum_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = closing_price, decimals = 4) %>%
  fmt_number(columns = nav, decimals = 4) %>%
  fmt_number(columns = day_high, decimals = 4) %>%
  fmt_number(columns = day_low, decimals = 4) %>%
  fmt_number(columns = outstanding_units_cleaned, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = premium_discount_percent, decimals = 2) %>%
  sub_missing(columns = everything(), missing_text = "NA") %>%
  cols_label(
    trade_date = "Date",
    volume_cleaned = "Volume",
    turnover_cleaned = "Turnover",
    aum_cleaned = "AUM",
    closing_price = "Close Price",
    nav = "NAV",
    day_high = "Day High",
    day_low = "Day Low",
    outstanding_units_cleaned = "Outstanding Units",
    premium_discount_percent = "Premium/Discount %"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body()
  ) %>%
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.width = pct(100),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef",
    heading.border.bottom.color = "#dee2e6",
    table.font.size = px(12)
  )

Recent 30 Days - Complete Data
ETF 7552 - Most recent dates first
Date	Volume	Turnover	AUM	Close Price	NAV	Day High	Day Low	Outstanding Units	Premium/Discount %
12/19/2025	308,091,800	451,222,446.00	NA	1.4700	NA	1.4900	1.4500	2,957,400,000	NA
12/18/2025	260,428,102	392,957,621.00	NA	1.5000	NA	1.5300	1.4900	2,957,400,000	−0.03
12/17/2025	292,510,003	436,157,084.00	4,360,000,000	1.4800	1.4800	1.5200	1.4600	2,957,400,000	0.07
12/16/2025	434,770,201	656,449,663.00	4,670,000,000	1.5100	1.5100	1.5300	1.4600	3,101,800,000	−0.01
12/15/2025	315,378,360	452,139,147.00	4,550,000,000	1.4500	1.4600	1.4600	1.4100	3,123,000,000	−0.22
12/12/2025	361,676,300	506,222,656.00	4,330,000,000	1.3900	1.3900	1.4300	1.3800	3,123,000,000	0.11
12/11/2025	240,240,000	342,942,977.00	4,500,000,000	1.4400	1.4400	1.4500	1.3900	3,123,000,000	−0.03
12/10/2025	263,715,900	378,698,265.00	4,490,000,000	1.4200	1.4200	1.4600	1.4100	3,169,000,000	−0.05
12/9/2025	311,088,300	440,394,976.00	4,530,000,000	1.4300	1.4300	1.4400	1.3700	3,169,000,000	−0.03
12/8/2025	210,795,300	289,083,144.00	4,300,000,000	1.3800	1.3800	1.3900	1.3600	3,124,200,000	0.04
12/5/2025	382,221,627	532,654,873.00	4,310,000,000	1.3800	1.3800	1.4300	1.3600	3,124,200,000	0.02
12/4/2025	328,506,900	468,543,714.00	4,380,000,000	1.4000	1.4000	1.4600	1.3900	3,124,200,000	0.04
12/3/2025	263,304,412	377,415,150.00	4,510,000,000	1.4500	1.4500	1.4500	1.4100	3,124,200,000	0.16
12/2/2025	279,906,000	389,554,498.00	4,360,000,000	1.4000	1.4000	1.4100	1.3700	3,113,400,000	−0.11
12/1/2025	334,907,100	466,948,803.00	4,330,000,000	1.4000	1.3900	1.4200	1.3800	3,113,400,000	0.37
11/28/2025	204,418,300	287,873,703.00	4,400,000,000	1.4100	1.4100	1.4300	1.3900	3,113,400,000	−0.25
11/27/2025	361,330,200	504,757,562.00	4,350,000,000	1.4100	1.4100	1.4200	1.3700	3,078,200,000	NA
11/26/2025	272,959,200	380,174,723.00	4,280,000,000	1.4000	1.4000	1.4100	1.3800	3,050,200,000	NA
11/25/2025	440,221,700	614,826,084.00	4,290,000,000	1.4000	1.4100	1.4300	1.3700	3,050,200,000	−0.22
11/24/2025	520,999,800	761,233,956.00	4,390,000,000	1.4400	1.4400	1.5200	1.4300	3,050,200,000	NA
11/21/2025	635,522,825	962,019,014.00	5,030,000,000	1.5300	1.5300	1.5400	1.4800	3,297,800,000	0.17
11/20/2025	421,930,500	606,446,130.00	5,110,000,000	1.4300	1.4400	1.4700	1.3900	3,564,600,000	−0.22
11/19/2025	319,876,200	451,068,643.00	5,170,000,000	1.4200	1.4200	1.4300	1.3800	3,647,800,000	−0.21
11/18/2025	502,903,900	699,062,025.00	5,330,000,000	1.4000	1.4000	1.4100	1.3500	3,810,200,000	0.01
11/17/2025	301,171,700	403,768,565.00	5,280,000,000	1.3500	1.3500	1.3600	1.3100	3,923,800,000	−0.07
11/14/2025	504,693,000	658,223,332.00	5,340,000,000	1.3200	1.3200	1.3300	1.2800	4,048,200,000	−0.26
11/13/2025	481,053,302	608,755,909.00	5,060,000,000	1.2500	1.2500	1.3000	1.2300	4,048,200,000	0.09
11/12/2025	338,582,400	431,653,846.00	5,140,000,000	1.2700	1.2700	1.2900	1.2500	4,048,200,000	0.06
11/11/2025	288,090,200	369,233,058.00	5,160,000,000	1.2800	1.2800	1.3000	1.2500	4,048,200,000	0.04
11/10/2025	314,418,800	407,827,730.00	5,180,000,000	1.2800	1.2800	1.3300	1.2700	4,049,800,000	0.07

---

Historical Statistics

Volume Statistics

volume_stats_df <- data.frame(
  Metric = c("Median", "Mean", "Minimum", "Maximum", "Total Days"),
  Value = c(
    stats$volume_median,
    stats$volume_mean,
    stats$volume_min,
    stats$volume_max,
    stats$total_days
  )
)

volume_stats_df %>%
  gt() %>%
  fmt_number(columns = Value, rows = 1:4, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = Value, rows = 5, decimals = 0, use_seps = FALSE) %>%
  cols_label(
    Metric = "Metric",
    Value = "Value"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body(columns = Value)
  ) %>%
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.width = pct(50),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef"
  )

Metric	Value
Median	481,053,302
Mean	549,663,793
Minimum	204,418,300
Maximum	1,642,927,600
Total Days	65

Turnover Statistics

turnover_stats_df <- data.frame(
  Metric = c("Median", "Mean", "Minimum", "Maximum"),
  Value = c(
    stats$turnover_median,
    stats$turnover_mean,
    stats$turnover_min,
    stats$turnover_max
  )
)

turnover_stats_df %>%
  gt() %>%
  fmt_number(columns = Value, decimals = 2, use_seps = TRUE) %>%
  cols_label(
    Metric = "Metric",
    Value = "Value"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body(columns = Value)
  ) %>%
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.width = pct(50),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef"
  )

Metric	Value
Median	609,627,000.00
Mean	694,869,143.57
Minimum	287,873,703.00
Maximum	1,950,129,738.00

---

Report generated: 2025-12-20 08:23:29.172474