Excel报表操作：清洗财报与财务指标

小象实战讲义 · 量化交易Python入门：从咖啡馆到华尔街

在量化交易中，历史回测数据往往以多种异构本地文件（TXT、CSV、JSON、Excel）和关系数据库形式存在。本章你将编写数据接口，读取小象咖啡的门店营收数据并解析股票行情API响应，打通量化回测的数据通道！

💡 核心导读

本节将系统讲解如何使用Python处理Excel文件，涵盖主流库的对比选型、数据读写操作技巧以及典型问题解决方案。通过本节学习，您将掌握：1) 使用pandas进行Excel数据高效读写 2) 处理多Sheet和格式转换 3) 解决中文编码等常见问题。配套的代码示例可直接应用于实际工作中的数据采集场景。

工具选型与基础操作

主流库对比

基础读写示例

import pandas as pd

# 构造示例销售数据
data = {
    '订单日期': ['2023年01月15日', '2023年01月16日'],
    '产品': ['A', 'B'],
    '数量': [10, 5],
    '金额': ['¥1000.50', '¥750.00'],
    '地区': ['北京', '上海']
}
df = pd.DataFrame(data)

print("原始数据结构：")
print(df.head(2).to_string())
print("\n数据类型：")
print(df.dtypes.to_string())

原始数据结构：
          订单日期 产品  数量        金额  地区
0  2023年01月15日  A  10  ¥1000.50  北京
1  2023年01月16日  B   5   ¥750.00  上海

数据类型：
订单日期      str
产品        str
数量      int64
金额        str
地区        str

高级操作技巧

多Sheet处理

import pandas as pd

# 构造多季度数据，模拟多sheet文件
q1_data = {
    '股票代码': ['000001', '000002'],
    '季度': ['Q1', 'Q1'],
    '营收': [100.5, 200.3],
    '净利润': [20.1, 40.5]
}
q2_data = {
    '股票代码': ['000001', '000002'],
    '季度': ['Q2', 'Q2'],
    '营收': [110.2, 210.8],
    '净利润': [22.3, 42.7]
}
q3_data = {
    '股票代码': ['000003'],
    '季度': ['Q3'],
    '营收': [50.0],
    '净利润': [5.0]
}

all_sheets = {
    'Q1': pd.DataFrame(q1_data),
    'Q2': pd.DataFrame(q2_data),
    'Q3': pd.DataFrame(q3_data)
}

merged_data = pd.concat([
    all_sheets['Q1'],
    all_sheets['Q2']
], ignore_index=True)

print("合并前Sheet数量:", len(all_sheets))
print("合并后数据量:", len(merged_data))
print("\n合并后数据预览：")
print(merged_data.to_string())

合并前Sheet数量: 3
合并后数据量: 4

合并后数据预览：
     股票代码  季度     营收   净利润
0  000001  Q1  100.5  20.1
1  000002  Q1  200.3  40.5
2  000001  Q2  110.2  22.3
3  000002  Q2  210.8  42.7

数据类型转换

import pandas as pd

# 构造带货币符号和中文日期的数据
data = {
    '订单日期': ['2023年01月15日', '2023年02月20日', '2023年03月10日'],
    '产品': ['股票A', '股票B', '股票C'],
    '金额': ['¥1000.50', '¥750.00', '¥1200.75']
}
df = pd.DataFrame(data)

print("转换前数据类型：")
print(df.dtypes.to_string())

# 清洗转换
df['金额'] = df['金额'].str.replace('¥', '', regex=False).astype(float)
df['订单日期'] = pd.to_datetime(df['订单日期'], format='%Y年%m月%d日')

print("\n转换后数据类型：")
print(df.dtypes.to_string())
print("\n转换后数据：")
print(df.to_string())

转换前数据类型：
订单日期    str
产品      str
金额      str

转换后数据类型：
订单日期    datetime64[us]
产品                 str
金额             float64

转换后数据：
        订单日期   产品       金额
0 2023-01-15  股票A  1000.50
1 2023-02-20  股票B   750.00
2 2023-03-10  股票C  1200.75

常见问题解决方案

中文编码处理

import pandas as pd

# 模拟读取Excel文件，演示sheet_name参数
# 构造示例财务报表数据
income_statement_data = {
    '项目': ['营业收入', '营业成本', '毛利润', '营业费用', '净利润'],
    '2023Q1': [1000.0, 600.0, 400.0, 200.0, 200.0],
    '2023Q2': [1100.0, 650.0, 450.0, 220.0, 230.0]
}
df_income = pd.DataFrame(income_statement_data)

# 模拟 pd.read_excel('report.xlsx', sheet_name='IncomeStatement')
df = df_income

print("利润表数据形状:", df.shape)
print("\n利润表数据：")
print(df.to_string())

利润表数据形状: (5, 3)

利润表数据：
     项目  2023Q1  2023Q2
0  营业收入  1000.0  1100.0
1  营业成本   600.0   650.0
2   毛利润   400.0   450.0
3  营业费用   200.0   220.0
4   净利润   200.0   230.0

大文件分块处理

import pandas as pd

# 构造一个稍大的示例数据集，用于演示分块处理
large_data = {
    '日期': pd.date_range('2023-01-01', periods=25000, freq='D'),
    '股票代码': ['000001'] * 25000,
    '收盘价': [10.0 + i * 0.001 for i in range(25000)],
    '成交量': [1000000 + i * 100 for i in range(25000)]
}
df_large = pd.DataFrame(large_data)

# 使用pandas的chunksize参数进行分块迭代（这里用DataFrame的groupby模拟chunks）
chunk_size = 10000
total_rows = len(df_large)
print(f"总数据量: {total_rows} 行")
print(f"分块大小: {chunk_size}")

# 模拟分块处理逻辑
for start in range(0, total_rows, chunk_size):
    end = start + chunk_size
    chunk = df_large.iloc[start:end]
    chunk_num = start // chunk_size + 1
    print(f"正在处理第 {chunk_num} 个数据块，行范围: {start}-{end-1}")
    # 在此处添加处理逻辑，例如计算每块的均值
    mean_price = chunk['收盘价'].mean()
    print(f"  本块收盘价均值: {mean_price:.4f}")

总数据量: 25000 行
分块大小: 10000
正在处理第 1 个数据块，行范围: 0-9999
  本块收盘价均值: 14.9995
正在处理第 2 个数据块，行范围: 10000-19999
  本块收盘价均值: 24.9995
正在处理第 3 个数据块，行范围: 20000-29999
  本块收盘价均值: 32.4995

📝 动手练一练

第 1 题 · 概念应用

你正在处理一份从某券商导出的股票持仓 Excel 文件 portfolio.xlsx。该文件包含两个工作表：CurrentHoldings（当前持仓）和 TransactionHistory（交易历史）。你只需要分析当前持仓数据，并希望避免因读取整个文件而消耗过多内存。以下哪种做法最符合你的需求？

• A. 使用 pd.read_excel(‘portfolio.xlsx’)，因为 pandas 默认会智能地只读取第一个工作表。
• B. 使用 pd.read_excel(‘portfolio.xlsx’, sheet_name=0)，通过索引指定第一个工作表。
• C. 使用 pd.read_excel(‘portfolio.xlsx’, sheet_name=‘CurrentHoldings’)，通过名称精确读取目标工作表。
• D. 使用 pd.read_excel(‘portfolio.xlsx’, sheet_name=None)，读取所有工作表后再提取 CurrentHoldings。

第 2 题 · 动手题（代码填空）

你收到一份来自分析师的简易财报数据，其中“净利润”列带有货币单位“万元”。你需要将其转换为纯数值（浮点型），以便后续计算净资产收益率（ROE）。请补全下方代码中的关键步骤。

import pandas as pd

# 模拟从Excel读取的简易资产负债表数据
data = {
    '公司': ['公司A', '公司B', '公司C'],
    '净资产（万元）': ['5000', '3200', '1800'],
    '净利润（万元）': ['¥800.5', '¥450.2', '¥210.8']  # 注意包含货币符号
}
df = pd.DataFrame(data)

print("原始数据：")
print(df)
print("\n原始数据类型：")
print(df.dtypes)

# 任务：清洗"净利润（万元）"列，移除货币符号"¥"并转换为浮点数
# 请补全下方代码
df['净利润（万元）'] = df['净利润（万元）'].str.replace(______, '').astype(float)

print("\n清洗后数据：")
print(df)
print("\n清洗后数据类型：")
print(df.dtypes)

import pandas as pd

# 模拟从Excel读取的简易资产负债表数据
data = {
    '公司': ['公司A', '公司B', '公司C'],
    '净资产（万元）': ['5000', '3200', '1800'],
    '净利润（万元）': ['¥800.5', '¥450.2', '¥210.8']  # 注意包含货币符号
}
df = pd.DataFrame(data)

print("原始数据：")
print(df)
print("\n原始数据类型：")
print(df.dtypes)

# 任务：清洗"净利润（万元）"列，移除货币符号"¥"并转换为浮点数
df['净利润（万元）'] = df['净利润（万元）'].str.replace('¥', '').astype(float)

print("\n清洗后数据：")
print(df)
print("\n清洗后数据类型：")
print(df.dtypes)

原始数据：
    公司 净资产（万元） 净利润（万元）
0  公司A    5000  ¥800.5
1  公司B    3200  ¥450.2
2  公司C    1800  ¥210.8

原始数据类型：
公司         str
净资产（万元）    str
净利润（万元）    str
dtype: object

清洗后数据：
    公司 净资产（万元）  净利润（万元）
0  公司A    5000    800.5
1  公司B    3200    450.2
2  公司C    1800    210.8

清洗后数据类型：
公司             str
净资产（万元）        str
净利润（万元）    float64
dtype: object

本章小结

📋 行动清单

• 使用pandas读取本地Excel文件，尝试用to_markdown()方法输出前5行数据
• 创建一个包含多个Sheet的Excel文件，编写代码合并所有Sheet数据
• 处理包含中文日期格式（如”2023年12月31日”）的列，转换为标准datetime类型

「小象量化教研组」建议在实际操作时注意：1) 始终指定engine参数避免版本问题 2) 处理大型文件时使用chunksize参数 3) 重要操作前先创建数据备份。

—— 小象教研组