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

在个人基金投资管理中手动整理持仓明细、核对每日净值、计算浮动收益是件挺磨人的事——十几只基金挨个查净值再对着Excel算盈亏不仅耗时费力还经常因为计算口径不一样出现偏差。本文基于Python实现公开基金数据的自动化采集结合Excel自动化能力完整覆盖数据拉取、清洗、收益率计算到标准化报告生成的全流程。不用手动复制粘贴运行一次脚本就能输出一份格式规范的持仓分析报告。一、前期准备与整体流程1.1 技术栈说明整个实现只用到四个核心库无需复杂依赖requests负责HTTP请求与公开数据采集pandas负责结构化数据清洗与数值计算openpyxl负责Excel文件的写入与格式美化json负责接口返回数据的解析1.2 环境配置Python版本建议3.8及以上通过pip一键安装依赖pipinstallrequests pandas openpyxl1.3 整体实现流程整个流程分为五个核心环节逻辑如下持仓参数配置公开基金数据采集数据清洗与格式统一收益率与持仓占比计算Excel多Sheet报告生成输出最终分析报告二、分步实现细节2.1 数据源与接口分析我们选取国内公开的基金行情接口作为数据源返回标准JSON格式数据包含基金基础信息名称、代码、最新净值与十大持仓明细股票名称、持仓占比、持仓市值等字段。开发前先通过浏览器调试工具确认接口地址、请求方式与必要的请求头重点关注返回数据的层级结构避免后续解析出错。2.2 数据采集模块封装这里我们封装一个通用的基金数据采集函数加入异常处理与请求头伪装保证采集稳定性。核心实现如下importrequestsimportpandasaspdimporttimedeffetch_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:# 请求基础信息resprequests.get(f{base_url}/base/{fund_code},headersheaders,timeout10)resp.raise_for_status()base_dataresp.json().get(data,{})# 请求持仓明细hold_resprequests.get(f{base_url}/holdings/{fund_code},headersheaders,timeout10)hold_resp.raise_for_status()hold_datahold_resp.json().get(data,{}).get(holdings,[])# 转为结构化DataFramebase_dfpd.DataFrame([{基金代码:fund_code,基金名称:base_data.get(name),最新净值:float(base_data.get(net_value,0)),净值日期:base_data.get(date)}])hold_dfpd.DataFrame(hold_data)# 控制请求频率time.sleep(0.5)returnbase_df,hold_dfexceptExceptionase:print(f基金{fund_code}数据采集失败{str(e)})returnNone,None这里踩过一个很典型的坑最开始没加Referer请求头接口一直返回403禁止访问加上对应页面的Referer后就能正常返回数据了。另外一定要加请求间隔避免短时间大量请求触发平台的访问限制。2.3 收益率计算逻辑实现这部分是整个脚本的核心我们基于个人持仓的本金与份额结合采集到的最新净值自动计算每只基金的浮动盈亏、收益率以及整体持仓占比。先定义个人持仓的输入格式用Excel导入或者直接写在脚本里都可以这里用DataFrame模拟持仓数据# 个人持仓配置示例my_holdingspd.DataFrame([{基金代码:000001,持有份额:10000,买入成本:15000},{基金代码:110011,持有份额:5000,买入成本:8000},{基金代码:161725,持有份额:8000,买入成本:12000}])然后封装计算函数统一计算口径defcalculate_holding_income(my_holdings,fund_base_list): 计算持仓收益率与明细 :param my_holdings: 个人持仓DataFrame :param fund_base_list: 采集到的基金基础信息列表 :return: 持仓收益总览DataFrame # 合并持仓与最新净值数据base_allpd.concat(fund_base_list,ignore_indexTrue)mergedpd.merge(my_holdings,base_all,on基金代码,howleft)# 核心指标计算merged[持仓市值]round(merged[持有份额]*merged[最新净值],2)merged[浮动盈亏]round(merged[持仓市值]-merged[买入成本],2)merged[收益率(%)]round(merged[浮动盈亏]/merged[买入成本]*100,2)# 计算整体持仓占比total_marketmerged[持仓市值].sum()merged[持仓占比(%)]round(merged[持仓市值]/total_market*100,2)# 补充合计行total_rowpd.DataFrame([{基金代码:合计,基金名称:-,最新净值:-,持有份额:merged[持有份额].sum(),买入成本:round(merged[买入成本].sum(),2),持仓市值:round(total_market,2),浮动盈亏:round(merged[浮动盈亏].sum(),2),收益率(%):round(merged[浮动盈亏].sum()/merged[买入成本].sum()*100,2),持仓占比(%):100.0}])resultpd.concat([merged,total_row],ignore_indexTrue)returnresult计算时要注意两个细节一是所有数值统一保留两位小数符合金融数据的展示习惯二是单独添加合计行不用打开Excel再手动求和。2.4 Excel自动化报告生成最后一步是把计算好的数据生成规范的Excel报告我们分两个Sheet存放「持仓总览」放收益与占比数据「十大持仓汇总」放所有基金的持仓股票明细。同时加入表头美化、条件格式、自动列宽让报告不用再手动调整格式fromopenpyxlimportWorkbookfromopenpyxl.stylesimportFont,Alignment,PatternFill,Border,Sidefromopenpyxl.utils.dataframeimportdataframe_to_rowsdefgenerate_excel_report(income_result,hold_detail_list,output_file基金持仓报告.xlsx):wbWorkbook()thin_borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))center_alignAlignment(horizontalcenter,verticalcenter)# Sheet1: 持仓总览 ws1wb.active ws1.title持仓总览# 写入数据forrow_idx,rowinenumerate(dataframe_to_rows(income_result,indexFalse,headerTrue),1):forcol_idx,valueinenumerate(row,1):cellws1.cell(rowrow_idx,columncol_idx,valuevalue)cell.alignmentcenter_align cell.borderthin_border# 表头样式ifrow_idx1:cell.fontFont(boldTrue,colorFFFFFF)cell.fillPatternFill(solid,fgColor4472C4)# 合计行样式ifvalue合计:cell.fontFont(boldTrue)cell.fillPatternFill(solid,fgColorFFF2CC)# 收益率条件格式正收益标绿负收益标红green_fillPatternFill(solid,fgColorC6EFCE)red_fillPatternFill(solid,fgColorFFC7CE)rate_col7# 收益率所在列号根据实际调整forrowinrange(2,ws1.max_row1):rate_valws1.cell(rowrow,columnrate_col).valueifisinstance(rate_val,(int,float)):ifrate_val0:ws1.cell(rowrow,columnrate_col).fillgreen_fillelifrate_val0:ws1.cell(rowrow,columnrate_col).fillred_fill# 自动调整列宽forcolinws1.columns:max_len0col_lettercol[0].column_letterforcellincol:try:# 中文按2个字符宽度计算lengthlen(str(cell.value).encode(gbk))iflengthmax_len:max_lenlengthexcept:passws1.column_dimensions[col_letter].widthmax_len2# Sheet2: 十大持仓汇总 ws2wb.create_sheet(十大持仓汇总)all_holdingspd.concat(hold_detail_list,ignore_indexTrue)forrow_idx,rowinenumerate(dataframe_to_rows(all_holdings,indexFalse,headerTrue),1):forcol_idx,valueinenumerate(row,1):cellws2.cell(rowrow_idx,columncol_idx,valuevalue)cell.alignmentcenter_align cell.borderthin_borderifrow_idx1:cell.fontFont(boldTrue,colorFFFFFF)cell.fillPatternFill(solid,fgColor70AD47)# 同样调整列宽forcolinws2.columns:max_len0col_lettercol[0].column_letterforcellincol:try:lengthlen(str(cell.value).encode(gbk))iflengthmax_len:max_lenlengthexcept:passws2.column_dimensions[col_letter].widthmax_len2wb.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报告生成的全流程自动化原本需要十几分钟手动整理的工作现在运行脚本十几秒就能完成而且计算口径统一不会出现人工误差。如果想要进一步扩展能力可以尝试这几个方向加入定时任务每天收盘后自动运行脚本生成当日报告增加行业分布统计基于持仓股票计算整体行业暴露度接入邮件或企业微信推送自动把报告发送到手机扩展支持股票、理财等其他品类做成统一的资产看板