Tushare财务报表爬取到excel

财务报表爬取到excel"/>

Tushare财务报表爬取到excel

写在前面

作为一个断断续续自学代码的小白，仅以此文章记录自己的成长轨迹，如果能帮到他人也是再好不过了。代码可能效率不高或者不规范，但是经过自己的验证，运行是没有问题的。

关于写代码的一些解释

本段代码由python语言写成，主要为提高了财务工作上的效率，避免机械的重复操作，实现以下几点目的：

1. 把特定的上市公司的年报数据写入excel，并且每个sheet单独对应一个公司的报表，方便领导查看。
2. 为了联系mysql，顺便将得到的数据写入mysql数据库。

代码介绍

代码经过重构分成了以下几部分：

1. 利用tushare的api接口得到公司财报数据的主程序【account_function】
2. 放置各种设置参数的【settings】
3. 将数据写入Excel的【df_to_excel】
4. 将数据写入sql的【df_to_mysql】
5. 处理上市公司代码的【code_proceed】
6. 处理tushare参数的【indicator_code】
7. 主程序入口【main_process】
   tushare接口链接

代码

1. account_function.py

在这里插入代码片'''从tushare接口爬取数据'''
import tushare as ts
import pandas as pd
from indicator_code import indicator_1_1, indicator_1_2
from settings import my_token, account_date,\start_d, end_d, \profit_and_loss_index, asset_and_debt_index, \cash_and_flow_index, summary_index
pro = ts.pro_api(my_token)  #tushare初始化
IN_account_field = indicator_1_1()
IN_account_indicator_index = indicator_1_2()def PL_account(company_code,start_d,end_d):'''======================利润表爬取输出=================================='''df_PL = pro.income(ts_code=company_code,start_date=start_d,end_date=end_d,fields='ts_code,end_date,total_revenue,revenue,oth_b_income,\total_cogs,other_bus_cost,operate_profit,\non_oper_income,non_oper_exp,total_profit,income_tax,\n_income,compr_inc_attr_p')df_PL = df_PL[df_PL['end_date'].isin(account_date)]  #去掉月报和季报df_PL = df_PL.drop_duplicates(['end_date'])  #去重df_PL = df_PL.Tdf_PL = df_PL.sort_values(by='end_date',axis='columns',ascending=True)  #按年份从小到大排序df_PL.set_axis(df_PL.loc['end_date'],axis='columns',inplace=True)  #修改列标签df_PL.insert(df_PL.shape[1],'参数',profit_and_loss_index) #插入参数列df_insert = df_PL['参数'] #将'参数'位置提前到第一列df_PL.drop('参数', axis=1, inplace=True)df_PL.insert(0, '参数', df_insert)return df_PLdef AD_account(company_code,start_d,end_d):'''=========================资产负债表爬取输出========================='''df_AD = pro.balancesheet(ts_code=company_code,start_date=start_d,end_date=end_d,fields='ts_code,end_date,money_cap,trad_asset,notes_receiv,\accounts_receiv,oth_receiv,prepayment,div_receiv,\int_receiv,inventories,nca_within_1y,oth_cur_assets,\total_cur_assets,fa_avail_for_sale,lt_eqt_invest,\invest_real_estate,time_deposits,oth_assets,lt_rec,\fix_assets,cip,intan_assets,r_and_d,goodwill,oth_nca,\total_nca,total_assets,lt_borr,st_borr,notes_payable,\acct_payable,adv_receipts,payroll_payable,taxes_payable,\int_payable,div_payable,oth_payable,non_cur_liab_due_1y,\oth_cur_liab,total_cur_liab,bond_payable,lt_payable,\estimated_liab,defer_tax_liab,oth_ncl,total_ncl,\total_liab,total_hldr_eqy_inc_min_int')df_AD = df_AD[df_AD['end_date'].isin(account_date)]  # 去掉月报和季报df_AD = df_AD.drop_duplicates(['end_date'])  # 去重df_AD = df_AD.Tdf_AD = df_AD.sort_values(by='end_date', axis='columns', ascending=True)  # 按年份从小到大排序df_AD.set_axis(df_AD.loc['end_date'], axis='columns', inplace=True)  # 修改列标签df_AD.insert(df_AD.shape[1], '参数', asset_and_debt_index)  # 插入参数列df_insert = df_AD['参数']  # 将'参数'位置提前到第一列df_AD.drop('参数', axis=1, inplace=True)df_AD.insert(0, '参数', df_insert)return df_ADdef CF_account(company_code,start_d,end_d):'''=========================现金流量表爬取输出========================='''df_CF = pro.cashflow(ts_code=company_code,start_date=start_d,end_date=end_d,fields='ts_code,end_date,net_profit,finan_exp,c_fr_sale_sg,c_fr_oth_operate_a,\c_inf_fr_operate_a,c_paid_goods_s,c_paid_to_for_empl,\c_paid_for_taxes,oth_cash_pay_oper_act,st_cash_out_act,\n_cashflow_act,oth_recp_ral_inv_act,c_disp_withdrwl_invest,\c_recp_return_invest,n_recp_disp_fiolta,n_recp_disp_sobu,\stot_inflows_inv_act,c_pay_acq_const_fiolta,c_paid_invest,\oth_pay_ral_inv_act,stot_out_inv_act,n_cashflow_inv_act,\c_recp_borrow,proc_issue_bonds,oth_cash_recp_ral_fnc_act,\stot_cash_in_fnc_act,c_prepay_amt_borr,c_pay_dist_dpcp_int_exp,\oth_cashpay_ral_fnc_act,stot_cashout_fnc_act,n_cash_flows_fnc_act,\n_incr_cash_cash_equ')df_CF = df_CF[df_CF['end_date'].isin(account_date)]  # 去掉月报和季报df_CF = df_CF.drop_duplicates(['end_date'])  # 去重df_CF = df_CF.Tdf_CF = df_CF.sort_values(by='end_date', axis='columns', ascending=True)  # 按年份从小到大排序df_CF.set_axis(df_CF.loc['end_date'], axis='columns', inplace=True)  # 修改列标签df_CF.insert(df_CF.shape[1], '参数', cash_and_flow_index)  # 插入参数列df_insert = df_CF['参数']  # 将'参数'位置提前到第一列df_CF.drop('参数', axis=1, inplace=True)df_CF.insert(0, '参数', df_insert)return df_CFdef SU_account(company_code,start_d,end_d):'''=========================业绩快报爬取输出========================='''df_SUM = pro.express(ts_code=company_code,start_date=start_d,end_date=end_d,fields='ts_code,end_date,revenue,operate_profit,total_profit,\n_income,total_assets,total_hldr_eqy_exc_min_int,\diluted_eps,diluted_roe,yoy_net_profit,bps,\yoy_sales,yoy_op,yoy_tp,\yoy_dedu_np,yoy_eps,yoy_roe,growth_assets,\yoy_equity,growth_bps,or_last_year,op_last_year,\tp_last_year,np_last_year,eps_last_year,open_net_assets,\open_bps')df_SUM = df_SUM[df_SUM['end_date'].isin(account_date)]  # 去掉月报和季报df_SUM = df_SUM.drop_duplicates(['end_date'])  # 去重df_SUM = df_SUM.Tdf_SUM = df_SUM.sort_values(by='end_date', axis='columns', ascending=True)  # 按年份从小到大排序df_SUM.set_axis(df_SUM.loc['end_date'], axis='columns', inplace=True)  # 修改列标签df_SUM.insert(df_SUM.shape[1], '参数', summary_index)  # 插入参数列df_insert = df_SUM['参数']  # 将'参数'位置提前到第一列df_SUM.drop('参数', axis=1, inplace=True)df_SUM.insert(0, '参数', df_insert)return df_SUMdef IN_account(company_code,start_d,end_d):'''=========================财务指标爬取输出========================='''# 列标签indicator_index = IN_account_indicator_indexdf_IN = pro.fina_indicator(ts_code=company_code,start_date=start_d,end_date=end_d,fields=IN_account_field)df_IN = df_IN[df_IN['end_date'].isin(account_date)]  # 去掉月报和季报df_IN = df_IN.drop_duplicates(['end_date'])  # 去重df_IN = df_IN.Tdf_IN = df_IN.sort_values(by='end_date', axis='columns', ascending=True)  # 按年份从小到大排序df_IN.set_axis(df_IN.loc['end_date'], axis='columns', inplace=True)  # 修改列标签df_IN.insert(df_IN.shape[1], '参数', indicator_index)  # 插入参数列df_insert = df_IN['参数']  # 将'参数'位置提前到第一列df_IN.drop('参数', axis=1, inplace=True)df_IN.insert(0, '参数', df_insert)return df_INdef Renenue_class(company_code, period, type):'''======================主营业务收入按产品分类爬取输出=================================='''df_RE = pro.fina_mainbz(ts_code=company_code,period=period,type=type, #P:按产品分类 I：按行业分类fields='bz_item,bz_sales,bz_profit,bz_cost')df_RE = df_RE.sort_values(by='bz_sales', axis='index', ascending=False)# 列标签RE_index = pd.DataFrame({'bz_item': ['主营业务来源'],'bz_sales': ['主营业务收入'],'占比': '主营业务收入占比','bz_profit': ['主营业务利润'],'bz_cost': ['主营业务成本']})df_RE.loc['合计'] = df_RE[['bz_sales','bz_profit','bz_cost']].sum(axis=0) #加入汇总行df_RE['占比'] = df_RE['bz_sales']/df_RE.iloc[-1, 1] #加入占比行df_RE.replace({0: '','Nan': '','None': ''},inplace=True)df_RE = df_RE[['bz_item','bz_sales','占比','bz_profit','bz_cost']] #手动排序df_RE = pd.concat([RE_index, df_RE], ignore_index=False) #加入指标行df_RE.iloc[-1, 0] = '合计'return df_RE

2. settings.py

'''=======================主要参数设置========================='''
my_token = 'xxxxx' #你的token,可以在tushare个人主页查询到
start_d = '20180101' #财报的起始日期
end_d = '20301231' #财报的终止日期
period = '20230630' #主营业务收入分类的时间点
input_excel_path = 'xxxx.xlsx' #导入excel的路径，根据实际情况修改
out_put_excel_path = 'xxxx.xlsx' #要写入的excel路径
#保存从2018-2022年报数据和2023年-2026年的半年报和年报数据
account_date = ['20181231','20191231','20201231','20211231','20221231',\'20230630','20231231','20240630','20241231',\'20250630','20251231','20260630','20261231']
'''8开头的北交所股票，tushare接口不支持，这里输入你要查询的公司股票代码就可以了'''
public_companys_code = set(['600426','600328','603823','603867','603938','688628','300109','300801','301065','301277','300487','300267','300174','300586','300765','301069','002748','002001','002748','002001','000739','002408','002688','000830','000756'
])#利润表列标签，方便查询，以下同理，财务指标的代码太多了就没手动输入，采用从excel中输出的方式
profit_and_loss_index = ['代码','报告期','营业总收入','营业收入','其他业务收入',\'营业总成本','其他业务成本',\'营业利润','加：营业外收入','减：营业外支出',\'利润总额','所得税费用','净利润（含少数股东损益）',\'归属于母公司的综合收益总额']#资产负债表列标签
asset_and_debt_index = ['代码', '报告期', '货币资金', '交易性金融资产', '应收票据','应收账款', '其他应收款', '预付款项', '应收股利', '应收利息','存货', '一年内到期的非流动资产', '其他流动资产', '流动资产合计','可供出售金融资产', '长期股权投资', '投资性房地产', '定期存款','其他资产', '长期应收款', '固定资产', '在建工程', '无形资产','研发支出', '商誉', '其他非流动资产', '非流动资产合计','资产总计', '长期借款', '短期借款', '应付票据', '应付账款','预收款项', '应付职工薪酬', '应交税费', '应付利息','应付股利', '其他应付款', '一年内到期的非流动负债','其他流动负债', '流动负债合计', '应付债券', '长期应付款','预计负债', '递延所得税负债', '其他非流动负债', '非流动负债合计','负债合计', '股东权益合计(含少数股东权益)']#现金流量表列标签
cash_and_flow_index = ['代码', '报告期', '净利润', '财务费用', '销售商品、提供劳务收到的现金','收到其他与经营活动有关的现金', '经营活动现金流入小计', '购买商品、接受劳务支付的现金','支付给职工以及为职工支付的现金', '支付的各项税费', '支付其他与经营活动有关的现金','经营活动现金流出小计', '经营活动产生的现金流量净额', '收到其他与投资活动有关的现金','收回投资收到的现金', '取得投资收益收到的现金', '处置固定资产、无形资产和其他长期资产收回的现金净额','处置子公司及其他营业单位收到的现金净额', '投资活动现金流入小计', '购建固定资产、无形资产和其他长期资产支付的现金','投资支付的现金', '支付其他与投资活动有关的现金', '投资活动现金流出小计', '投资活动产生的现金流量净额','取得借款收到的现金', '发行债券收到的现金', '收到其他与筹资活动有关的现金', '筹资活动现金流入小计','偿还债务支付的现金', '分配股利、利润或偿付利息支付的现金', '支付其他与筹资活动有关的现金','筹资活动现金流出小计', '筹资活动产生的现金流量净额', '现金及现金等价物净增加额']#业绩快报列标签
summary_index = ['代码', '报告期', '营业收入', '营业利润', '利润总额','净利润', '总资产', '股东权益合计(不含少数股东权益)', '每股收益(摊薄)','净资产收益率(摊薄)(%)', '去年同期修正后净利润', '每股净资产', '同比增长率:营业收入','同比增长率:营业利润', '同比增长率:利润总额', '同比增长率:归属母公司股东的净利润','同比增长率:基本每股收益', '同比增减:加权平均净资产收益率', '比年初增长率:总资产','比年初增长率:归属母公司的股东权益', '比年初增长率:归属于母公司股东的每股净资产','去年同期营业收入', '去年同期营业利润', '去年同期利润总额', '去年同期净利润','去年同期每股收益', '期初净资产', '期初每股净资产']

3.df_to_excel.py

'''将财务数据写入excel表格'''
import pandas as pd
import time
from settings import start_d, end_d, period, out_put_excel_path
from code_proceed import code_and_trade_sum
company_codes = code_and_trade_sum
from account_function import PL_account, AD_account, CF_account, SU_account, IN_account, Renenue_classdef companys_data_write_to_excel():time_start = time.time()succeed_num = 0failed_num = 0print('==============开始将数据写入excel==============')for i, company_code in enumerate(code_and_trade_sum):try:#workbook.create_sheet(index=i, title=code_and_trade_sum[i]) #创建新sheet，名字为股票代码df1 = PL_account(company_code, start_d, end_d) #利润表（自选）df2 = AD_account(company_code, start_d, end_d) #资产负债表（自选）df3 = CF_account(company_code, start_d, end_d) #现金流量表（自选）df4 = SU_account(company_code, start_d, end_d) #业绩快报（自选）df5 = IN_account(company_code, start_d, end_d) #财务指标（全部）df6 = Renenue_class(company_code, period, type='P') #按产品划分营业收入df7 = Renenue_class(company_code, period, type='I') #按行业划分营业收入df8 = pd.concat([df6, df7])df = pd.concat([df1,df2,df3,df5])with pd.ExcelWriter(out_put_excel_path,mode='a',engine='openpyxl',if_sheet_exists='replace') as writer:'''默认值是对于xls使用xlwt，对于xlsx使用openpyxl，对于ods使用odf。因为用的是xlsx，所以得多加engine="openpyxl"'''df.to_excel(writer, sheet_name=code_and_trade_sum[i], index=False, na_rep=0)with pd.ExcelWriter(out_put_excel_path,mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:df8.to_excel(writer, sheet_name=code_and_trade_sum[i],index=False, header=False, startrow=1, startcol=8)print(str(code_and_trade_sum[i])+' 写入excel完成')succeed_num = succeed_num + 1except Exception as h:print(h)failed_num = failed_num + 1print('全部写入excel完成')time_end = time.time()print('*********'+str(len(code_and_trade_sum)-failed_num)+'个数据写入成功 总用时：'+str('%.2f'%(time_end-time_start))+'秒')

4. df_to_mysql.py

'''将财务数据写入mysql'''
from account_function import *
from sqlalchemy import create_engine
import pymysql
import time
import re
from settings import start_d, end_d, account_date
from code_proceed import code_and_trade_sum
company_codes = code_and_trade_sum
def df_to_mysql(data, account, engine):try:data.to_sql(account,engine,schema='company_data',if_exists='replace',  #确定新数据的模式：可选'replace'，'append'，'fail'index=False,index_label='参数',chunksize=None)except Exception as e:print(e)def companys_data_write_to_mysql():# 准备连接mysql数据库connection = pymysql.connect(host='localhost',user='xxxx', #用户名password='xxxx', #密码port=3306,charset='utf8mb4')# 创建一个游标对象cursor = connection.cursor()#导入df到sqlengine = create_engine('mysql+pymysql://用户名:密码@localhost:3306/company_data') #用户名和密码需要修改cursor.fast_executemany = Truetime_start = time.time()succeed_times = 0failed_times = 0failed_companys = []#写入数据循环开始print('==============开始将数据写入mysql==============')for company_code in company_codes:try:data_PL = PL_account(company_code, start_d, end_d)print('利润表数据爬取完成')#print(data_PL)account = ''.join(re.findall('\d+',company_code)) + 'PL_account_'df_to_mysql(data=data_PL, account=account, engine=engine)print('利润表数据写入完成')data_AD = AD_account(company_code, start_d, end_d)print('资产负债表数据爬取完成')#print(data_AD)account = ''.join(re.findall('\d+', company_code)) + 'AD_account_'df_to_mysql(data=data_AD, account=account, engine=engine)print('资产负债表数据写入完成')data_CF = CF_account(company_code, start_d, end_d)print('现金流量表数据爬取完成')#print(data_CF)account = ''.join(re.findall('\d+', company_code)) + 'CF_account_'df_to_mysql(data=data_CF, account=account, engine=engine)print('现金流量表数据写入完成')data_SU = SU_account(company_code, start_d, end_d)print('业绩快报数据爬取完成')#print(data_SU)account = ''.join(re.findall('\d+', company_code)) + 'SU_account_'df_to_mysql(data=data_SU, account=account, engine=engine)print('业绩快报数据写入完成')data_IN = IN_account(company_code, start_d, end_d)print('财务指标数据爬取完成')#print(data_IN)account = ''.join(re.findall('\d+', company_code)) + 'IN_account_'df_to_mysql(data=data_IN, account=account, engine=engine)print('财务指标数据写入完成')print('==============='+str(company_code)+'数据写入mysql完成=================')succeed_times = succeed_times + 1except Exception as d:failed_times = failed_times + 1failed_companys.append(company_code)print(d)time_end = time.time()cursor.close()print('*********'+str(len(company_codes)-len(failed_companys))+'个数据写入成功\总用时：'+str('%.2f'%(time_end-time_start))+'秒')print(str(len(failed_companys))+'个数据写入失败，分别为：'+str(failed_companys))

5. code_proceed.py

'''根据代码前两位确定交易所代码'''
from settings import public_companys_code
code_and_trade_sum = []
for code in public_companys_code:try:if code[0:2] == '60' or code[0:2] == '68':code_and_trade = str(code) + '.'+'SH'elif code[0:2] == '30' or code[0:2] == '00':code_and_trade = str(code) + '.' + 'SZ'elif code[0:2] == '83':code_and_trade = str(code) + '.' + 'BJ'else:print(str(code) + ' 未正确匹配交易所代码，请检查代码或修改代码匹配规则')code_and_trade_sum.append(code_and_trade)except Exception as p:continue
#print(code_and_trade_sum)

6. indicator_code.py

from openpyxl import *
from settings import input_excel_path
'''根据excel表格的数据提取财务指标代码和解释'''
def indicator_1():wb = load_workbook(input_excel_path)ws = wb['财务指标'] #需要读取的sheet名称indicator_list = []for i in range(0,ws.max_row-1):indicator_and_code = {}indicator_and_code['indicator'] = ws.cell(i + 2, 1).valueindicator_and_code['word'] = ws.cell(i + 2, 4).valueindicator_list.append(indicator_and_code)return indicator_listdef indicator_1_1():  #返回代码indicator_1_indicator_list = []for i in range(0, len(indicator_1())):indicator_1_indicator_list.append(indicator_1()[i]['indicator'])indicator_1_indicator_list_str = ','.join(indicator_1_indicator_list)return indicator_1_indicator_list_strdef indicator_1_2():  #返回中文indicator_1_word_list = []for i in range(0, len(indicator_1())):indicator_1_word_list.append(indicator_1()[i]['word'])return indicator_1_word_list

7. main_process.py

from account_function import *
from df_to_mysql import companys_data_write_to_mysql
from df_to_excel import companys_data_write_to_excel
from code_proceed import code_and_trade_sum'''主程序入口'''
if __name__ == '__main__':companys_data_write_to_mysql()  #财务数据写入mysqlcompanys_data_write_to_excel()  #财务数据写入excel

PS：

第一次发帖子有不规范的地方还请各位大佬指正。