Hong Kong ETF Volume Analysis

Data Overview

cat("**Date Range:**", format(START_DATE), "to", format(END_DATE), "\n\n")

## **Date Range:** 2025-09-15 to 2025-12-16

cat("**Total Days in Range:**", as.numeric(END_DATE - START_DATE) + 1, "\n\n")

## **Total Days in Range:** 93

cat("**Report Generated:**", format(Sys.Date()))

## **Report Generated:** 2025-12-17

Load Data

# Load dimension table
etf_master <- readRDS(file.path(STAR_SCHEMA_DIR, "etf_master.rds"))

# Open fact table (Parquet dataset)
etf_daily <- open_dataset(file.path(STAR_SCHEMA_DIR, "etf_daily"))

cat("Loaded etf_master:", nrow(etf_master), "ETFs\n")

## Loaded etf_master: 375 ETFs

cat("Opened etf_daily dataset\n")

## Opened etf_daily dataset

Calculate Volume Statistics

# Calculate comprehensive volume statistics for each ETF
volume_stats <- etf_daily %>%
  filter(trade_date >= START_DATE & trade_date <= END_DATE) %>%
  filter(!is.na(volume_cleaned)) %>%
  collect() %>%
  group_by(stock_code) %>%
  summarise(
    # Basic counts
    total_days = n(),
    days_with_volume = sum(volume_cleaned > 0, na.rm = TRUE),
    days_with_zero_volume = sum(volume_cleaned == 0, na.rm = TRUE),
    days_with_na = sum(is.na(volume_cleaned)),
    
    # Volume aggregates
    total_volume = sum(volume_cleaned, na.rm = TRUE),
    avg_volume = mean(volume_cleaned, na.rm = TRUE),
    median_volume = median(volume_cleaned, na.rm = TRUE),
    max_volume = max(volume_cleaned, na.rm = TRUE),
    min_volume = min(volume_cleaned, na.rm = TRUE),
    
    # Trading frequency metrics
    trading_frequency = days_with_volume / total_days,  # Proportion of days with trading
    avg_volume_when_traded = mean(volume_cleaned[volume_cleaned > 0], na.rm = TRUE),
    
    # Volume consistency (coefficient of variation)
    volume_cv = ifelse(avg_volume > 0, 
                       sd(volume_cleaned, na.rm = TRUE) / avg_volume, 
                       NA),
    
    # Percentiles for distribution understanding
    p25_volume = quantile(volume_cleaned, 0.25, na.rm = TRUE),
    p75_volume = quantile(volume_cleaned, 0.75, na.rm = TRUE),
    p90_volume = quantile(volume_cleaned, 0.90, na.rm = TRUE),
    p95_volume = quantile(volume_cleaned, 0.95, na.rm = TRUE),
    
    .groups = "drop"
  ) %>%
  # Add date range info
  mutate(
    date_range_start = START_DATE,
    date_range_end = END_DATE,
    date_range_days = as.numeric(END_DATE - START_DATE) + 1
  )

cat("Calculated statistics for", nrow(volume_stats), "ETFs\n")

## Calculated statistics for 354 ETFs

Join with Master Data

volume_categorized <- volume_stats %>%
  left_join(etf_master, by = "stock_code") %>%
  select(
    stock_code,
    stock_short_name,
    issuer,
    asset_class,
    geographic_focus,
    investment_focus,
    listing_date,
    # Volume statistics
    total_volume,
    avg_volume,
    median_volume,
    max_volume,
    trading_frequency,
    days_with_volume,
    total_days,
    # Categories
    volume_category_adv,
    volume_category_frequency,
    volume_category_combined,
    volume_category_total,
    # Additional metrics
    volume_cv,
    avg_volume_when_traded
  ) %>%
  arrange(desc(avg_volume))

cat("Joined data for", nrow(volume_categorized), "ETFs\n")

## Joined data for 354 ETFs

Overall Statistics

overall_stats <- volume_categorized %>%
  summarise(
    total_etfs = n(),
    etfs_with_zero_volume = sum(avg_volume == 0 | is.na(avg_volume)),
    etfs_with_some_volume = sum(avg_volume > 0, na.rm = TRUE),
    median_avg_volume = median(avg_volume, na.rm = TRUE),
    mean_avg_volume = mean(avg_volume, na.rm = TRUE),
    median_trading_frequency = median(trading_frequency, na.rm = TRUE),
    mean_trading_frequency = mean(trading_frequency, na.rm = TRUE),
    total_volume_all_etfs = sum(total_volume, na.rm = TRUE)
  )

overall_stats_df <- data.frame(
  Metric = c(
    "Total ETFs",
    "ETFs with Zero Volume",
    "ETFs with Some Volume",
    "Median Average Volume",
    "Mean Average Volume",
    "Median Trading Frequency",
    "Mean Trading Frequency",
    "Total Volume (All ETFs)"
  ),
  Value = c(
    overall_stats$total_etfs,
    overall_stats$etfs_with_zero_volume,
    overall_stats$etfs_with_some_volume,
    overall_stats$median_avg_volume,
    overall_stats$mean_avg_volume,
    overall_stats$median_trading_frequency,
    overall_stats$mean_trading_frequency,
    overall_stats$total_volume_all_etfs
  ),
  stringsAsFactors = FALSE
)

overall_stats_df %>%
  gt() %>%
  tab_header(
    title = "Overall Volume Statistics"
  ) %>%
  fmt_number(columns = Value, rows = c(4, 5, 8), decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = Value, rows = c(6, 7), decimals = 3) %>%
  fmt_number(columns = Value, rows = c(1, 2, 3), 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(60),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef",
    heading.border.bottom.color = "#dee2e6"
  )

Metric	Value
Overall Volume Statistics
Total ETFs	354
ETFs with Zero Volume	0
ETFs with Some Volume	354
Median Average Volume	12,897
Mean Average Volume	9,242,867
Median Trading Frequency	1.000
Mean Trading Frequency	1.000
Total Volume (All ETFs)	202,067,958,846

Summary Statistics by Category

Distribution by Average Daily Volume Category

summary_adv <- volume_categorized %>%
  group_by(volume_category_adv) %>%
  summarise(
    count = n(),
    pct_of_total = round(100 * n() / nrow(volume_categorized), 1),
    avg_avg_volume = round(mean(avg_volume, na.rm = TRUE), 2),
    median_avg_volume = round(median(avg_volume, na.rm = TRUE), 2),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_avg_volume))

summary_adv %>%
  gt() %>%
  tab_header(
    title = "Distribution by Average Daily Volume Category"
  ) %>%
  fmt_number(columns = avg_avg_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = median_avg_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = pct_of_total, decimals = 1, pattern = "{x}%") %>%
  cols_label(
    volume_category_adv = "Category",
    count = "Count",
    pct_of_total = "% of Total",
    avg_avg_volume = "Avg Volume",
    median_avg_volume = "Median Volume"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body(columns = c(count, pct_of_total, avg_avg_volume, median_avg_volume))
  ) %>%
  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"
  )

Category	Count	% of Total	Avg Volume	Median Volume
Distribution by Average Daily Volume Category
Very High Volume (Top 10%)	36	10.2%	90,349,907	2,674,180
High Volume (75-90%)	53	15.0%	284,690	223,810
Above Average Volume (50-75%)	88	24.9%	41,169	32,420
Below Average Volume (25-50%)	88	24.9%	6,629	5,816
Low Volume (10-25%)	53	15.0%	1,472	1,386
Very Low Volume (Bottom 10%)	36	10.2%	149	123

Top and Bottom ETFs

Top 20 ETFs by Average Daily Volume

top_20 <- volume_categorized %>%
  head(20) %>%
  select(stock_code, stock_short_name, issuer, asset_class,
         avg_volume, total_volume, trading_frequency, volume_category_combined)

top_20 %>%
  gt() %>%
  tab_header(
    title = "Top 20 ETFs by Average Daily Volume"
  ) %>%
  fmt_number(columns = avg_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = total_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = trading_frequency, decimals = 3) %>%
  cols_label(
    stock_code = "Stock Code",
    stock_short_name = "Name",
    issuer = "Issuer",
    asset_class = "Asset Class",
    avg_volume = "Avg Volume",
    total_volume = "Total Volume",
    trading_frequency = "Trading Frequency",
    volume_category_combined = "Category"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body(columns = c(avg_volume, total_volume, trading_frequency))
  ) %>%
  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"
  )

Stock Code	Name	Issuer	Asset Class	Avg Volume	Total Volume	Trading Frequency	Category
Top 20 ETFs by Average Daily Volume
3033	CSOP HS TECH	CSOP Asset Management Limited	Equity	1,434,752,089	88,954,629,524	1.000	High Activity
7552	XI2CSOPHSTECH	CSOP Asset Management Limited	Equity	562,372,849	34,867,116,647	1.000	High Activity
2800	TRACKER FUND	Hang Seng Investment Management Limited	Equity	542,322,622	33,624,002,548	1.000	High Activity
7226	XL2CSOPHSTECH	CSOP Asset Management Limited	Equity	223,523,023	13,858,427,422	1.000	High Activity
7500	FI2 CSOP HSI	CSOP Asset Management Limited	Equity	173,662,682	10,767,086,302	1.000	High Activity
2828	HSCEI ETF	Hang Seng Investment Management Limited	Equity	85,373,378	5,293,149,424	1.000	High Activity
7200	FL2 CSOP HSI	CSOP Asset Management Limited	Equity	40,908,678	2,536,338,028	1.000	High Activity
3032	HSTECH ETF	Hang Seng Investment Management Limited	Equity	39,231,854	2,432,374,964	1.000	High Activity
3067	ISHARESHSTECH	BlackRock Asset Management North Asia Limited	Equity	35,560,440	2,204,747,299	1.000	High Activity
3416	A GX HSCEICC	Mirae Asset Global Investments (Hong Kong) Limited	Equity	28,158,498	1,745,826,876	1.000	High Activity
7709	XL2CSOPHYNIX	CSOP Asset Management Limited	Equity	22,699,288	976,069,365	1.000	High Activity
7568	FI2CSOPNASDAQ	CSOP Asset Management Limited	Equity	7,350,925	455,757,324	1.000	High Activity
3417	A GX HSTCC	Mirae Asset Global Investments (Hong Kong) Limited	Equity	6,605,045	409,512,776	1.000	High Activity
2823	ISHARES A50	BlackRock Asset Management North Asia Limited	Equity	6,191,583	383,878,158	1.000	High Activity
3088	CAM HS TECH	China Asset Management (Hong Kong) Limited	Equity	5,657,584	350,770,205	1.000	High Activity
3466	HS HIGH DIV	Hang Seng Investment Management Limited	Equity	4,639,109	287,624,764	1.000	High Activity
2802	A CSOP HSCEICC	CSOP Asset Management Limited	Equity	3,527,000	14,108,000	1.000	High Activity
2822	CSOP A50 ETF	CSOP Asset Management Limited	Equity	2,790,672	173,021,649	1.000	High Activity
7288	FL2 CSOP HSCEI	CSOP Asset Management Limited	Equity	2,557,689	158,576,700	1.000	High Activity
7588	FI2 CSOP HSCEI	CSOP Asset Management Limited	Equity	2,435,639	151,009,600	1.000	High Activity

Bottom 20 ETFs by Average Daily Volume (Excluding Zero)

bottom_20 <- volume_categorized %>%
  filter(avg_volume > 0) %>%
  tail(20) %>%
  select(stock_code, stock_short_name, issuer, asset_class,
         avg_volume, total_volume, trading_frequency, volume_category_combined) %>%
  arrange(avg_volume)

bottom_20 %>%
  gt() %>%
  tab_header(
    title = "Bottom 20 ETFs by Average Daily Volume",
    subtitle = "Excluding ETFs with zero volume"
  ) %>%
  fmt_number(columns = avg_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = total_volume, decimals = 0, use_seps = TRUE) %>%
  fmt_number(columns = trading_frequency, decimals = 3) %>%
  cols_label(
    stock_code = "Stock Code",
    stock_short_name = "Name",
    issuer = "Issuer",
    asset_class = "Asset Class",
    avg_volume = "Avg Volume",
    total_volume = "Total Volume",
    trading_frequency = "Trading Frequency",
    volume_category_combined = "Category"
  ) %>%
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels()
  ) %>%
  tab_style(
    style = cell_text(align = "right"),
    locations = cells_body(columns = c(avg_volume, total_volume, trading_frequency))
  ) %>%
  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"
  )

Stock Code	Name	Issuer	Asset Class	Avg Volume	Total Volume	Trading Frequency	Category
Bottom 20 ETFs by Average Daily Volume
Excluding ETFs with zero volume
3480	A VP USD MM	Value Partners Hong Kong Limited	Money Market	2	5	1.000	Mixed Activity
3471	A CAM HKDTMMF	China Asset Management (Hong Kong) Limited	Money Market	7	33	1.000	Mixed Activity
3472	A CAM USDTMMF	China Asset Management (Hong Kong) Limited	Money Market	20	40	1.000	Mixed Activity
83455	INVESCO QQQ-R	Invesco Capital Management LLC	Equity	26	763	1.000	Mixed Activity
3015	X TRNIFTY50	DWS Investment S.A.	Equity	56	670	1.000	Mixed Activity
3072	AMOVA INET	Amova Asset Management Hong Kong Limited	Equity	59	762	1.000	Mixed Activity
83420	A VP RMB MM-R	Value Partners Hong Kong Limited	Money Market	60	179	1.000	Mixed Activity
9455	INVESCO QQQ-U	Invesco Capital Management LLC	Equity	64	1,605	1.000	Mixed Activity
9011	A ICBCCICCUSD-U	China International Capital Corporation Hong Kong Asset Management Limited	Money Market	70	2,029	1.000	Mixed Activity
9156	BOS 20 UST-U	Bosera Asset Management (International) Co., Limited	Fixed income	78	157	1.000	Mixed Activity
9078	PREMIA UST A -U	Premia Partners Company Limited	Fixed income	80	319	1.000	Mixed Activity
83196	A BOS USD MM-R	Bosera Asset Management (International) Co., Limited	Money Market	83	166	1.000	Mixed Activity
82840	SPDR GOLD TRT-R	SPDR GOLD TRUST	Commodity	91	5,181	1.000	Mixed Activity
9077	PREMIA UST-U	Premia Partners Company Limited	Fixed income	93	465	1.000	Mixed Activity
3192	A BOS RMB MM	Bosera Asset Management (International) Co., Limited	Money Market	96	1,350	1.000	Mixed Activity
83192	A BOS RMB MM-R	Bosera Asset Management (International) Co., Limited	Money Market	106	529	1.000	Mixed Activity
83053	A CSOP HKD MM-R	CSOP Asset Management Limited	Money Market	110	220	1.000	Mixed Activity
3077	PREMIA UST	Premia Partners Company Limited	Fixed income	122	3,425	1.000	Mixed Activity
3011	A ICBCCICCUSD	China International Capital Corporation Hong Kong Asset Management Limited	Money Market	123	7,116	1.000	Mixed Activity
3102	ICBCU KS KWEB	ICBC UBS Asset Management (International) Company Limited	Equity	125	500	1.000	Mixed Activity

Volume Concentration Analysis

top_10_pct_volume <- volume_categorized %>%
  arrange(desc(total_volume)) %>%
  head(round(nrow(volume_categorized) * 0.1)) %>%
  summarise(total_vol = sum(total_volume, na.rm = TRUE)) %>%
  pull(total_vol)

top_25_pct_volume <- volume_categorized %>%
  arrange(desc(total_volume)) %>%
  head(round(nrow(volume_categorized) * 0.25)) %>%
  summarise(total_vol = sum(total_volume, na.rm = TRUE)) %>%
  pull(total_vol)

concentration_df <- data.frame(
  Metric = c(
    "Top 10% of ETFs - Volume Share",
    "Top 25% of ETFs - Volume Share"
  ),
  Value = c(
    round(100 * top_10_pct_volume / overall_stats$total_volume_all_etfs, 1),
    round(100 * top_25_pct_volume / overall_stats$total_volume_all_etfs, 1)
  ),
  stringsAsFactors = FALSE
)

concentration_df %>%
  gt() %>%
  tab_header(
    title = "Volume Concentration Analysis",
    subtitle = "Percentage of total volume accounted for by top ETFs"
  ) %>%
  fmt_number(columns = Value, decimals = 1, pattern = "{x}%") %>%
  cols_label(
    Metric = "Metric",
    Value = "Percentage"
  ) %>%
  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(60),
    column_labels.background.color = "#f8f9fa",
    table_body.hlines.color = "#e9ecef",
    heading.border.bottom.color = "#dee2e6"
  )

Metric	Percentage
Volume Concentration Analysis
Percentage of total volume accounted for by top ETFs
Top 10% of ETFs - Volume Share	99.5%
Top 25% of ETFs - Volume Share	99.9%

Report generated: 2025-12-17 09:11:44.485499

Hong Kong ETF Volume Analysis

ML using Cursor AI

2025-12-17

Data Overview

Load Data

Calculate Volume Statistics

Join with Master Data

Overall Statistics

Summary Statistics by Category

Distribution by Average Daily Volume Category

Top and Bottom ETFs

Top 20 ETFs by Average Daily Volume

Bottom 20 ETFs by Average Daily Volume (Excluding Zero)

Volume Concentration Analysis