手把手教你用Python实现基金持仓采集 自动算收益率生成Excel报告

在个人基金投资管理中，手动整理持仓明细、核对每日净值、计算浮动收益是件挺磨人的事——十几只基金挨个查净值，再对着Excel算盈亏，不仅耗时费力，还经常因为计算口径不一样出现偏差。

本文基于Python实现公开基金数据的自动化采集，结合Excel自动化能力，完整覆盖数据拉取、清洗、收益率计算到标准化报告生成的全流程。不用手动复制粘贴，运行一次脚本就能输出一份格式规范的持仓分析报告。

一、前期准备与整体流程

1.1 技术栈说明

整个实现只用到四个核心库，无需复杂依赖：

• requests：负责HTTP请求与公开数据采集
• pandas：负责结构化数据清洗与数值计算
• openpyxl：负责Excel文件的写入与格式美化
• json：负责接口返回数据的解析

1.2 环境配置

Python版本建议3.8及以上，通过pip一键安装依赖：

pip install requests pandas openpyxl

1.3 整体实现流程

整个流程分为五个核心环节，逻辑如下：

#mermaid-svg-v5prYULeCXQ0q7nz{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-v5prYULeCXQ0q7nz .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-v5prYULeCXQ0q7nz .error-icon{fill:#552222;}#mermaid-svg-v5prYULeCXQ0q7nz .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-v5prYULeCXQ0q7nz .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-v5prYULeCXQ0q7nz .marker{fill:#333333;stroke:#333333;}#mermaid-svg-v5prYULeCXQ0q7nz .marker.cross{stroke:#333333;}#mermaid-svg-v5prYULeCXQ0q7nz svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-v5prYULeCXQ0q7nz p{margin:0;}#mermaid-svg-v5prYULeCXQ0q7nz .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-v5prYULeCXQ0q7nz .cluster-label text{fill:#333;}#mermaid-svg-v5prYULeCXQ0q7nz .cluster-label span{color:#333;}#mermaid-svg-v5prYULeCXQ0q7nz .cluster-label span p{background-color:transparent;}#mermaid-svg-v5prYULeCXQ0q7nz .label text,#mermaid-svg-v5prYULeCXQ0q7nz span{fill:#333;color:#333;}#mermaid-svg-v5prYULeCXQ0q7nz .node rect,#mermaid-svg-v5prYULeCXQ0q7nz .node circle,#mermaid-svg-v5prYULeCXQ0q7nz .node ellipse,#mermaid-svg-v5prYULeCXQ0q7nz .node polygon,#mermaid-svg-v5prYULeCXQ0q7nz .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-v5prYULeCXQ0q7nz .rough-node .label text,#mermaid-svg-v5prYULeCXQ0q7nz .node .label text,#mermaid-svg-v5prYULeCXQ0q7nz .image-shape .label,#mermaid-svg-v5prYULeCXQ0q7nz .icon-shape .label{text-anchor:middle;}#mermaid-svg-v5prYULeCXQ0q7nz .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-v5prYULeCXQ0q7nz .rough-node .label,#mermaid-svg-v5prYULeCXQ0q7nz .node .label,#mermaid-svg-v5prYULeCXQ0q7nz .image-shape .label,#mermaid-svg-v5prYULeCXQ0q7nz .icon-shape .label{text-align:center;}#mermaid-svg-v5prYULeCXQ0q7nz .node.clickable{cursor:pointer;}#mermaid-svg-v5prYULeCXQ0q7nz .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-v5prYULeCXQ0q7nz .arrowheadPath{fill:#333333;}#mermaid-svg-v5prYULeCXQ0q7nz .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-v5prYULeCXQ0q7nz .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-v5prYULeCXQ0q7nz .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-v5prYULeCXQ0q7nz .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-v5prYULeCXQ0q7nz .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-v5prYULeCXQ0q7nz .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-v5prYULeCXQ0q7nz .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-v5prYULeCXQ0q7nz .cluster text{fill:#333;}#mermaid-svg-v5prYULeCXQ0q7nz .cluster span{color:#333;}#mermaid-svg-v5prYULeCXQ0q7nz div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-v5prYULeCXQ0q7nz .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-v5prYULeCXQ0q7nz rect.text{fill:none;stroke-width:0;}#mermaid-svg-v5prYULeCXQ0q7nz .icon-shape,#mermaid-svg-v5prYULeCXQ0q7nz .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-v5prYULeCXQ0q7nz .icon-shape p,#mermaid-svg-v5prYULeCXQ0q7nz .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-v5prYULeCXQ0q7nz .icon-shape .label rect,#mermaid-svg-v5prYULeCXQ0q7nz .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-v5prYULeCXQ0q7nz .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-v5prYULeCXQ0q7nz .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-v5prYULeCXQ0q7nz :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

持仓参数配置

公开基金数据采集

数据清洗与格式统一

收益率与持仓占比计算

Excel多Sheet报告生成

输出最终分析报告

二、分步实现细节

2.1 数据源与接口分析

我们选取国内公开的基金行情接口作为数据源，返回标准JSON格式数据，包含基金基础信息（名称、代码、最新净值）与十大持仓明细（股票名称、持仓占比、持仓市值）等字段。

开发前先通过浏览器调试工具确认接口地址、请求方式与必要的请求头，重点关注返回数据的层级结构，避免后续解析出错。

2.2 数据采集模块封装

这里我们封装一个通用的基金数据采集函数，加入异常处理与请求头伪装，保证采集稳定性。核心实现如下：

import requests
import pandas as pd
import time

def fetch_fund_info(fund_code):
"""
采集单只基金的基础信息与持仓数据
:param fund_code: 基金代码，字符串格式
:return: 基础信息DataFrame、持仓明细DataFrame
"""
base_url = "对应公开接口地址前缀"
headers = {
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
"Referer": "对应公开页面地址"
}

try:
# 请求基础信息
resp = requests.get(f"{base_url}/base/{fund_code}", headers=headers, timeout=10)
resp.raise_for_status()
base_data = resp.json().get("data", {})

# 请求持仓明细
hold_resp = requests.get(f"{base_url}/holdings/{fund_code}", headers=headers, timeout=10)
hold_resp.raise_for_status()
hold_data = hold_resp.json().get("data", {}).get("holdings", [])

# 转为结构化DataFrame
base_df = pd.DataFrame([{
"基金代码": fund_code,
"基金名称": base_data.get("name"),
"最新净值": float(base_data.get("net_value", 0)),
"净值日期": base_data.get("date")
}])

hold_df = pd.DataFrame(hold_data)
# 控制请求频率
time.sleep(0.5)
return base_df, hold_df

except Exception as e:
print(f"基金{fund_code}数据采集失败：{str(e)}")
return None, None

这里踩过一个很典型的坑：最开始没加Referer请求头，接口一直返回403禁止访问，加上对应页面的Referer后就能正常返回数据了。另外一定要加请求间隔，避免短时间大量请求触发平台的访问限制。

2.3 收益率计算逻辑实现

这部分是整个脚本的核心，我们基于个人持仓的本金与份额，结合采集到的最新净值，自动计算每只基金的浮动盈亏、收益率，以及整体持仓占比。

先定义个人持仓的输入格式，用Excel导入或者直接写在脚本里都可以，这里用DataFrame模拟持仓数据：

# 个人持仓配置示例
my_holdings = pd.DataFrame([
{"基金代码": "000001", "持有份额": 10000, "买入成本": 15000},
{"基金代码": "110011", "持有份额": 5000, "买入成本": 8000},
{"基金代码": "161725", "持有份额": 8000, "买入成本": 12000}
])

然后封装计算函数，统一计算口径：

def calculate_holding_income(my_holdings, fund_base_list):
"""
计算持仓收益率与明细
:param my_holdings: 个人持仓DataFrame
:param fund_base_list: 采集到的基金基础信息列表
:return: 持仓收益总览DataFrame
"""
# 合并持仓与最新净值数据
base_all = pd.concat(fund_base_list, ignore_index=True)
merged = pd.merge(my_holdings, base_all, on="基金代码", how="left")

# 核心指标计算
merged["持仓市值"] = round(merged["持有份额"] * merged["最新净值"], 2)
merged["浮动盈亏"] = round(merged["持仓市值"] – merged["买入成本"], 2)
merged["收益率(%)"] = round(merged["浮动盈亏"] / merged["买入成本"] * 100, 2)

# 计算整体持仓占比
total_market = merged["持仓市值"].sum()
merged["持仓占比(%)"] = round(merged["持仓市值"] / total_market * 100, 2)

# 补充合计行
total_row = pd.DataFrame([{
"基金代码": "合计",
"基金名称": "-",
"最新净值": "-",
"持有份额": merged["持有份额"].sum(),
"买入成本": round(merged["买入成本"].sum(), 2),
"持仓市值": round(total_market, 2),
"浮动盈亏": round(merged["浮动盈亏"].sum(), 2),
"收益率(%)": round(merged["浮动盈亏"].sum() / merged["买入成本"].sum() * 100, 2),
"持仓占比(%)": 100.0
}])

result = pd.concat([merged, total_row], ignore_index=True)
return result

计算时要注意两个细节：一是所有数值统一保留两位小数，符合金融数据的展示习惯；二是单独添加合计行，不用打开Excel再手动求和。

2.4 Excel自动化报告生成

最后一步是把计算好的数据生成规范的Excel报告，我们分两个Sheet存放：「持仓总览」放收益与占比数据，「十大持仓汇总」放所有基金的持仓股票明细。

同时加入表头美化、条件格式、自动列宽，让报告不用再手动调整格式：

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows

def generate_excel_report(income_result, hold_detail_list, output_file="基金持仓报告.xlsx"):
wb = Workbook()
thin_border = Border(
left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin')
)
center_align = Alignment(horizontal="center", vertical="center")

# ========== Sheet1: 持仓总览 ==========
ws1 = wb.active
ws1.title = "持仓总览"

# 写入数据
for row_idx, row in enumerate(dataframe_to_rows(income_result, index=False, header=True), 1):
for col_idx, value in enumerate(row, 1):
cell = ws1.cell(row=row_idx, column=col_idx, value=value)
cell.alignment = center_align
cell.border = thin_border
# 表头样式
if row_idx == 1:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill("solid", fgColor="4472C4")
# 合计行样式
if value == "合计":
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="FFF2CC")

# 收益率条件格式：正收益标绿，负收益标红
green_fill = PatternFill("solid", fgColor="C6EFCE")
red_fill = PatternFill("solid", fgColor="FFC7CE")
rate_col = 7 # 收益率所在列号，根据实际调整
for row in range(2, ws1.max_row + 1):
rate_val = ws1.cell(row=row, column=rate_col).value
if isinstance(rate_val, (int, float)):
if rate_val > 0:
ws1.cell(row=row, column=rate_col).fill = green_fill
elif rate_val < 0:
ws1.cell(row=row, column=rate_col).fill = red_fill

# 自动调整列宽
for col in ws1.columns:
max_len = 0
col_letter = col[0].column_letter
for cell in col:
try:
# 中文按2个字符宽度计算
length = len(str(cell.value).encode("gbk"))
if length > max_len:
max_len = length
except:
pass
ws1.column_dimensions[col_letter].width = max_len + 2

# ========== Sheet2: 十大持仓汇总 ==========
ws2 = wb.create_sheet("十大持仓汇总")
all_holdings = pd.concat(hold_detail_list, ignore_index=True)

for row_idx, row in enumerate(dataframe_to_rows(all_holdings, index=False, header=True), 1):
for col_idx, value in enumerate(row, 1):
cell = ws2.cell(row=row_idx, column=col_idx, value=value)
cell.alignment = center_align
cell.border = thin_border
if row_idx == 1:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill("solid", fgColor="70AD47")

# 同样调整列宽
for col in ws2.columns:
max_len = 0
col_letter = col[0].column_letter
for cell in col:
try:
length = len(str(cell.value).encode("gbk"))
if length > max_len:
max_len = length
except:
pass
ws2.column_dimensions[col_letter].width = max_len + 2

wb.save(output_file)
print(f"持仓报告已生成：{output_file}")

这里重点说下列宽的坑：最开始直接用len()计算字符长度，中文列总是显示不全，后来换成gbk编码的字节数来计算宽度，就基本准确了，再加上2个单位的冗余，完美适配中文场景。

三、常见问题排查

3.1 数据采集返回403/404

• 检查基金代码是否正确，部分基金代码需要补全6位数字
• 确认请求头是否完整，特别是Referer和User-Agent字段
• 接口地址可能会更新，建议定期抓包验证接口可用性
• 如果频繁请求被限制，可以拉长请求间隔，不建议硬刚反爬策略

3.2 收益率计算结果不对

• 确认净值日期是否为最新交易日，节假日与非交易时间净值不更新
• 如果基金有过分红或拆分，需要统一计算口径，本文默认现金分红
• 买入成本要包含手续费，否则计算出的收益率会有偏差

3.3 Excel生成报错或格式乱

• 确保pandas版本在1.1.0以上，低版本dataframe_to_rows有兼容问题
• 中文路径可能导致保存失败，建议使用英文路径或转码处理
• 条件格式列号要和实际数据列对应，否则会标错颜色

四、总结与扩展思路

本文实现了从基金数据采集、收益率自动计算到Excel报告生成的全流程自动化，原本需要十几分钟手动整理的工作，现在运行脚本十几秒就能完成，而且计算口径统一，不会出现人工误差。

如果想要进一步扩展能力，可以尝试这几个方向：