2025年期货月度数据分析报告.qmd

---
title: "2025年全国期货市场交易情况简报"
author: "超级分析师Lixin Wu"
date: "`r Sys.Date()`"
format:
  html:
    latex-engine: xelatex
    theme: cosmo
    toc: true
    toc-depth: 2
    number-sections: true
    highlight-style: github
    css: styles.css
editor: visual
execute:
  enabled: true
---

# 中国期货市场综合性月度报告分析

```{r setup, include=FALSE}
# 加载必需的R包
library(tidyverse)          # 数据处理和可视化
library(knitr)              # 表格美化
library(kableExtra)         # 高级表格样式
library(scales)             # 数字格式化（如逗号分隔）
library(readxl)             # 读取Excel文件
library(ggplot2)  
library(openxlsx)# 绘图
library(ggcorrplot)
library(corrplot)
library(showtext)
library(plotly)
# 关键：告诉 kableExtra 不要使用 tabu
options(kableExtra.latex.use_xfrac = FALSE)
showtext_auto(enable=TRUE)
font_add('Songti','Songti.ttc')
```

## 数据准备

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 2. 获取所有工作表名称
library(readxl)

folder_path <- "/Users/wulixin/Downloads/"
file_list <- list.files(path = folder_path, pattern = "\\.xls$", full.names = TRUE)

all_data <- list()

for (file in file_list) {
  # 从文件名提取月份，如 "1月"
  month_match <- regmatches(basename(file), regexpr("\\d+月", basename(file)))
  if (length(month_match) == 0) {
    cat("警告：无法从文件名 '", basename(file), "' 提取月份。\n")
    next
  }
  month <- month_match[[1]]

  # 明确读取名为“全国期货期权市场交易情况统计月报”的工作表
  raw_data <- read_xls(
    path = file,
    sheet = "全国期货期权市场交易情况统计月报",
    skip = 2,n_max=92
  )

  # 处理重复列名
  colnames(raw_data) <- make.unique(colnames(raw_data), sep = "_")

  # 添加月份标识
  raw_data$Month <- month

  all_data[[month]] <- raw_data
  cat("已成功读取：", basename(file), "（", month, "）\n")
}


# 查看结果
#print(head(combined_data))
#5. 将所有月份的数据合并成一个大表格
# 使用 rbind() 将所有列表中的数据框垂直合并
#combined_data <- do.call(rbind, all_data)

# 这会为所有重复的列名添加后缀以使其唯一
#new_names <- make.unique(colnames(combined_data), sep = "_")
#colnames(combined_data) <- new_names
# 合并所有月份数据（推荐方式）
library(dplyr)

combined_data <- bind_rows(all_data)

# 验证列名
cat("合并后列名数量:", length(colnames(combined_data)), "\n")
print(head(colnames(combined_data)))

# 如果仍有重复列名（理论上不会），再做一次 make.unique
new_names <- make.unique(colnames(combined_data), sep = "_")
colnames(combined_data) <- new_names


# 如果仍有重复列名（理论上不会），再做一次 make.unique
new_names <- make.unique(colnames(combined_data), sep = "_")
colnames(combined_data) <- new_names

# 使用 rename() 函数进行重命名
cleaned_data <- cleaned_data %>%
  # 重新命名列，使用简洁、无特殊字符的名称
  rename(
    Exchange = `交易所名称`,
    Commodity = `品种名称`,
    Volume_Jul = `本月成交量（手）`,
    Volume_YoY = `去年同期成交量（手）`,
    Volume_YoY_Change = `同比增减（％）`,
    Volume_Last_Month = `上月成交量（手）`,
    Volume_Ring_Change = `环比增减（％）`,
    Volume_Share_National = `本月成交量占全国份额（％）`,
    Value_Jul = `本月成交额.（亿元）`, 
    Value_YoY = `去年同期成交额（亿元）`,
    Value_YoY_Change = `同比增减（％）_1`,
    Value_Last_Month = `上月成交额.（亿元）`, 
    Value_Ring_Change = `环比增减（％）_1`,
    Value_Share_National = `本月交易额占全国份额（％）`,
    Volume_Cumulative = `今年累计成交总量（手）`,
    Volume_Cumulative_YoY = `去年同期成交总量（手）`,
    Volume_Cumulative_YoY_Change = `同比增减（％）_2`,
    Volume_Cumulative_Share = `今年累计成交总量占全国份额(%)`,
    Value_Cumulative = `今年累计成交总额（亿元）`,
    Value_Cumulative_YoY = `去年同期成交总额（亿元）`,
    Value_Cumulative_YoY_Change = `同比增减（％）_3`,
    Value_Cumulative_Share = `今年累计成交总额占全国份额(%)`,
    Position_End = `本月月末持仓量(手）`,
    Position_Share_National = `本月月末持仓量占全国份额（%）`,
    Position_Last_Month = `上月月末持仓量（手）`,
    Position_Ring_Change = `环比增减（％）_2`
  ) %>%
  mutate_at(vars(starts_with("Volume"), starts_with("Value"), starts_with("Position")), as.numeric) %>%
  mutate_at(vars(ends_with("_Change"), ends_with("_Share")), ~ . / 100)

# 假设 df 已经是经过 fill() 和 rename() 处理后的 cleaned_data
df <- cleaned_data
# 6. （可选）将合并后的数据保存为新的Excel文件
#write.xlsx(df, "/Users/wulixin/Desktop/综合月报数据.xlsx", rowNames = FALSE)

```

## 计算各交易所每月的交易量（用于趋势图）

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 步骤1: 按月份和交易所计算总成交量 (只保留1-7月)
monthly_exchange_volume <- df %>%
  # 过滤掉 "总计" 行
  filter(Commodity != "总计") %>%
  # 确保 Month 列是因子或字符型
  mutate(Month = as.character(Month)) %>%
  # 按月和交易所分组求和
  group_by(Month, Exchange) %>%
  summarise(Total_Volume = sum(Volume_Jul, na.rm = TRUE), .groups = 'drop') %>%
  # 将月份转换为日期格式以便排序
  mutate(Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01"))) %>%
  # 按日期排序
  arrange(Date)

# 步骤2: 计算全国市场每月总成交量
national_monthly_volume <- monthly_exchange_volume %>%
  group_by(Date) %>%
  summarise(National_Volume = sum(Total_Volume), .groups = 'drop')
```

### 分析市场份额演变

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 计算每个月各交易所的市场份额
market_share_evolution <- monthly_exchange_volume %>%
  group_by(Date) %>%
  mutate(Total_Monthly_Volume = sum(Total_Volume),
         Share_Percentage = Total_Volume / Total_Monthly_Volume * 100) %>%
  ungroup() %>%
  select(Date, Exchange, Share_Percentage)

# 查看广期所的增长情况
guangqi_growth <- market_share_evolution %>%
  filter(Exchange == "广州期货交易所") %>%
  arrange(Date)

cat("广期所的市场份额从1月的", round(first(guangqi_growth$Share_Percentage), 2), 
    "%增长至7月的", round(last(guangqi_growth$Share_Percentage), 2), "%，显示出强劲的增长势头。\n")

# 使用之前计算好的 monthly_exchange_volume 数据
# 它包含了1-7月每个交易所的总成交量

# 创建堆叠柱状图
ggplot(monthly_exchange_volume, aes(x = Date, y = Total_Volume / 1e6, fill = Exchange)) +
  geom_col(position = "stack") +
  scale_x_date(date_labels = "%m月", date_breaks = "1 month") +
  scale_y_continuous(labels = scales::comma, name = "成交量 (百万手)") +
  labs(title = "2025年1-7月各交易所月度成交量堆叠图",
       x = "") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "bottom") +
  guides(fill = guide_legend(ncol = 2)) # 让图例多行显示
```

### 构建各交易所表现表格

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 创建一个宽表，展示每个交易所1-7月的成交量
exchange_performance_wide <- monthly_exchange_volume %>%
  select(Date, Exchange, Total_Volume) %>%
  # 转换为宽格式
  tidyr::pivot_wider(names_from = Date, values_from = Total_Volume, names_prefix = "Vol_") %>%
  # 添加平均值列
  rowwise() %>%
  mutate(Average = mean(c_across(starts_with("Vol_")), na.rm = TRUE)) %>%
  ungroup() %>%
  # 格式化数值
  mutate(across(starts_with("Vol_"), ~ format(. / 1e6, big.mark = ",")),
         Average = format(Average / 1e6, big.mark = ","))

# 在Quarto报告中渲染此表格
kable(exchange_performance_wide, caption = "2025年1-7月各交易所月度成交量 (百万手)", booktabs = TRUE) %>%
  kable_styling(full_width = TRUE, font_size = 9)
```

### 绘制增长率趋势图

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 步骤1: 需要1-7月的去年同期数据
# 假设您也有一个包含2024年1-7月数据的工作簿

# 查看有哪些 sheet
wb_2024 <- loadWorkbook("/Users/wulixin/Desktop/2024年期货月报数据.xlsx")
sheet_names <- getSheetNames("/Users/wulixin/Desktop/期货月报数据.xlsx")

# 读取2024年1-7月的数据并清洗
all_data_2024 <- list()
# 4. 循环遍历从 "1月" 到 "7月" 的工作表
for (month in c("1月", "2月", "3月", "4月", "5月", "6月", "7月")) {
  # 检查该月份的工作表是否存在
  if (month %in% sheet_names) {
    # 读取当前月份的数据
    raw_data <- read.xlsx(wb_2024, sheet = month, startRow = 3, colNames = TRUE)
    # 处理重复的列名
    new_names <- make.unique(colnames(raw_data), sep = "_")
    colnames(raw_data) <- new_names
    
    # 添加一个表示月份的列
    raw_data$Month <- month
    
    # 将当前月份的数据添加到列表中
    all_data_2024[[month]] <- raw_data
  } else {
    cat("警告：工作表 '", month, "' 不存在。\n")
  }
}
combined_data_2024 <- do.call(rbind, all_data_2024)

# 步骤2: 计算每个月的全国总成交量
national_2025 <- combined_data %>%
  filter(品种名称 != "总计") %>%
  group_by(Month) %>%
  summarise(Vol_2025 = sum(`本月成交量（手）`, na.rm = TRUE), .groups = 'drop')

national_2024 <- combined_data_2024 %>%
  filter(品种名称 != "总计") %>%
  group_by(Month) %>%
  summarise(Vol_2024 = sum(`本月成交量（手）`, na.rm = TRUE), .groups = 'drop')

# 步骤3: 合并两年数据并计算同比增长率
growth_rates_real <- national_2025 %>%
  left_join(national_2024, by = "Month") %>%
  mutate(
    YoY_Rate = (Vol_2025 - Vol_2024) / Vol_2024 * 100,
    Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01"))
  ) %>%
  bind_rows(tibble(
    Month = "8月",
    Vol_2025 = 838.81e6, # 来自知识库
    Vol_2024 = 838.81e6 / (1 + 0.1398), # 根据13.98%增长率反推
    YoY_Rate = 13.98,
    Date = lubridate::ymd("2025-08-01")
  )) %>%
  arrange(Date)

# 现在可以绘制真实的增长率趋势图了
ggplot(growth_rates_real, aes(x = Date, y = YoY_Rate)) +
  geom_line(color = "steelblue", size = 1.2) +
  geom_point(size = 2) +
  scale_x_date(date_labels = "%m月", date_breaks = "1 month") +
  labs(title = "2025年1-8月全国期货市场真实成交量同比增长率",
       x = "", y = "同比增长率 (%)") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
```

## 基于成交量的综合评分

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 计算每个品种的“热门度”
hot_commodities <- df %>%
  # 过滤掉 "总计" 和交易所行
  filter(Commodity != "总计" & !is.na(Commodity)) %>%
  # 计算两个指标
  mutate(
    # 指标1: 当前成交量排名 (越高越好)
    Volume_Rank = rank(desc(Volume_Jul), ties.method = "min"),
    # 指标2: 同比增长率 (越高越好)
    YoY_Score = pmax(Volume_YoY_Change, 0), # 只取正增长
    # 综合得分 (可调整权重)
    Hotness_Score = 0.6 * (1 / Volume_Rank) + 0.4 * (YoY_Score / max(YoY_Score, na.rm = TRUE))
  ) %>%
  # 按综合得分降序排列
  arrange(desc(Hotness_Score)) %>%
  # 取前N名
  head(10)

# 输出结果
cat("8月最热门的10个期货品种：\n")
print(hot_commodities %>% select(Exchange, Commodity, Volume_Jul, Volume_YoY_Change, Hotness_Score))
```

### 识别“黑马”品种

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 找出同比增长率最高的品种（忽略基数）
high_growth_stars <- df %>%
  filter(!is.na(Volume_YoY_Change), Volume_YoY_Change > 50) %>% # 增长率>50%
  arrange(desc(Volume_YoY_Change))

cat("8月增长最快的‘黑马’品种：\n")
print(high_growth_stars %>% select(Exchange, Commodity, Volume_Jul, Volume_YoY_Change))
```

### 热门品种趋势分析

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 选择几个热门品种
hot_commodities <- c("黄金", "螺纹钢", "原油")

commodity_trend <- df %>%
  filter(Commodity %in% hot_commodities & Commodity != "总计") %>%
  group_by(Month, Commodity) %>%
  summarise(Total_Volume = sum(Volume_Jul, na.rm = TRUE), .groups = 'drop') %>%
  mutate(Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01")),
         Month_Name = factor(Month, levels = paste0(1:7, "月"))) %>%
  arrange(Date)

ggplot(commodity_trend, aes(x = Month_Name, y = Total_Volume, color = Commodity)) +
  geom_line(aes(group = Commodity), size = 1) +
  geom_point(size = 2) +
  labs(title = "2025年1-7月热门品种成交量趋势",
       x = "月份", y = "成交量 (手)") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
```

### 持仓量分析

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 计算全国持仓量趋势 (需要1-7月的持仓数据)
# 假设您的原始数据中也包含每月的持仓量
national_position_trend <- df %>%
  filter(Commodity != "总计") %>%
  group_by(Month) %>%
  summarise(Position_Sum = sum(Position_End, na.rm = TRUE), .groups = 'drop') %>%
  mutate(Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01"))) %>%
  arrange(Date) %>%
  # 添加8月数据 (来自知识库)
  bind_rows(tibble(
    Month = "8月",
    Position_Sum = 48.2 * 1e6, # 48.2百万手
    Date = lubridate::ymd("2025-08-01")
  ))

ggplot(national_position_trend, aes(x = Date, y = Position_Sum / 1e6)) +
  geom_line(color = "darkgreen", size = 1.2) +
  geom_point(size = 2) +
  scale_x_date(date_labels = "%m月", date_breaks = "1 month") +
  labs(title = "2025年1-8月全国期货市场持仓量趋势",
       x = "", y = "持仓量 (百万手)") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
```

## 交易所细分品种交易趋势可视化

### （一）热门交易品种趋势图

```{r echo=FALSE,warning=FALSE}
#|echo: false
library(tidyverse)
library(patchwork) # 用于组合多个图表

# 假设 cleaned_data 已经包含1-7月的数据
df <- cleaned_data

# 步骤1: 按月份、交易所和品种计算总成交量 (只保留1-7月)
monthly_commodity_volume <- df %>%
  filter(Commodity != "总计" & !is.na(Commodity)) %>%
  mutate(Month = as.character(Month)) %>%
  group_by(Month, Exchange, Commodity) %>%
  summarise(Total_Volume = sum(Volume_Jul, na.rm = TRUE), .groups = 'drop') %>%
  mutate(Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01"))) %>%
  arrange(Date)

# 步骤2: 定义各交易所的热门品种 (可以从知识库获取)
hot_commodities <- list(
  "上海期货交易所" = c("黄金", "白银", "原油", "螺纹钢", "天然橡胶"),
  "郑州商品交易所" = c("纯碱", "玻璃", "菜籽油", "烧碱", "PTA"),
  "大连商品交易所" = c("焦煤", "棕榈油", "豆粕", "豆油", "铁矿石"),
  "中国金融期货交易所" = c("中证1000股指期货", "30年期国债期货", "沪深300股指期货", "中证500股指期货", "10年期国债期货"),
  "广州期货交易所" = c("多晶硅期货", "碳酸锂期货", "工业硅期货")
)

# 步骤3: 过滤出这些热门品种
filtered_data <- monthly_commodity_volume %>%
  filter(Commodity %in% unlist(hot_commodities))
```

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 修改后的 create_exchange_plot 函数
create_exchange_plot <- function(data, exchange_name) {
  data_subset <- data %>% filter(Exchange == exchange_name, Commodity %in% hot_commodities[[exchange_name]])
  
  # 如果数据为空，创建一个简单的占位符图表
  if (nrow(data_subset) == 0) {
    p <- ggplot() +
      annotate("text", x = 0.5, y = 0.5, label = paste("暂无数据\n", exchange_name), 
               size = 4, color = "gray") +
      xlim(0, 1) + ylim(0, 1) +
      theme_void() +
      labs(title = paste(exchange_name, "热门品种成交量趋势 (1-7月)"),
           x = "", y = "")
    return(p)
  }
  
  # 数据不为空时，正常绘制图表
  p <- ggplot(data_subset, aes(x = Date, y = Total_Volume / 1e6, color = Commodity)) +
    geom_line(size = 1.2) +
    geom_point(size = 2) +
    scale_x_date(date_labels = "%m月", date_breaks = "1 month") +
    scale_y_continuous(labels = scales::comma, name = "成交量 (百万手)") +
    labs(title = paste(exchange_name, "热门品种成交量趋势 (1-7月)"),
         x = "") +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1),
          legend.position = "bottom",
          plot.title = element_text(size = 10, hjust = 0.5))
  
  return(p)
}
# 重新生成所有图表
p_shfe <- create_exchange_plot(filtered_data, "上海期货交易所")
p_czce <- create_exchange_plot(filtered_data, "郑州商品交易所")
p_dce <- create_exchange_plot(filtered_data, "大连商品交易所")
p_cffex <- create_exchange_plot(filtered_data, "中国金融期货交易所")
p_gfex <- create_exchange_plot(filtered_data, "广州期货交易所")

# 现在可以安全地打印它们了
print(p_shfe)
print(p_czce)
print(p_dce)
print(p_cffex)
print(p_gfex)
```

### （二）各交易品种趋势图

```{r echo=FALSE,warning=FALSE}
#|echo: false
# 步骤1: 按月份、交易所和品种计算总成交量 (只保留1-7月)
monthly_commodity_volume <- df %>%
  filter(Commodity != "总计" & !is.na(Commodity)) %>%
  mutate(Month = as.character(Month)) %>%
  group_by(Month, Exchange, Commodity) %>%
  summarise(Total_Volume = sum(Volume_Jul, na.rm = TRUE), .groups = 'drop') %>%
  mutate(Date = lubridate::ymd(paste0("2025-", gsub("月", "", Month), "-01"))) %>%
  arrange(Date)

# ------------------- 关键修改点 -------------------
# 不再使用 hot_commodities 进行过滤
# filtered_data <- monthly_commodity_volume %>% 
#   filter(Commodity %in% unlist(hot_commodities))
# --------------------------------------------------

# 直接使用所有品种的数据
all_varieties_data <- monthly_commodity_volume

# 创建一个函数来生成单个交易所的趋势图 (适用于所有品种)
create_exchange_plot_all <- function(data, exchange_name) {
  data_subset <- data %>% filter(Exchange == exchange_name)
  
  # 如果该交易所没有数据，返回占位图
  if (nrow(data_subset) == 0) {
    p <- ggplot() +
      annotate("text", x = 0.5, y = 0.5, label = paste("暂无数据\n", exchange_name), 
               size = 4, color = "gray") +
      xlim(0, 1) + ylim(0, 1) +
      theme_void() +
      labs(title = paste(exchange_name, "所有品种成交量趋势 (1-7月)"),
           x = "", y = "")
    return(p)
  }
  
  # 计算每个品种的总成交量，用于排序
  top_commodities <- data_subset %>%
    group_by(Commodity) %>%
    summarise(Sum_Volume = sum(Total_Volume), .groups = 'drop') %>%
    arrange(desc(Sum_Volume)) %>%
    pull(Commodity)
  
  # 按总成交量降序排列，并限制显示前N个最活跃的品种以避免图表过于杂乱
  max_display <- 15 # 可调整
  top_n_commodities <- head(top_commodities, max_display)
  
  data_filtered <- data_subset %>% filter(Commodity %in% top_n_commodities)
  
  p <- ggplot(data_filtered, aes(x = Date, y = Total_Volume / 1e6, color = Commodity)) +
    geom_line(size = 1.2) +
    geom_point(size = 2) +
    scale_x_date(date_labels = "%m月", date_breaks = "1 month") +
    scale_y_continuous(labels = scales::comma, name = "成交量 (百万手)") +
    labs(title = paste(exchange_name, "最活跃品种成交量趋势 (1-7月)"),
         subtitle = paste("展示成交量最高的前", max_display, "个品种"),
         x = "") +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1),
          legend.position = "bottom",
          legend.title = element_blank(),
          plot.title = element_text(size = 10, hjust = 0.5),
          plot.subtitle = element_text(size = 8, hjust = 0.5)) +
    guides(color = guide_legend(ncol = 3)) # 让图例多行显示
  
  return(p)
}

# 为每个交易所生成图表
p_shfe_all <- create_exchange_plot_all(all_varieties_data, "上海期货交易所")
p_czce_all <- create_exchange_plot_all(all_varieties_data, "郑州商品交易所")
p_dce_all <- create_exchange_plot_all(all_varieties_data, "大连商品交易所")
p_cffex_all <- create_exchange_plot_all(all_varieties_data, "中国金融期货交易所")
p_gfex_all <- create_exchange_plot_all(all_varieties_data, "广州期货交易所")

# 在Quarto报告中单独显示每个图表
p_shfe_all
p_czce_all
p_dce_all
p_cffex_all
p_gfex_all
```