python循环批量读取tushare财务数据

前段时间接到有个需求，需要获取A股中的1570个公司的财务数据，在tushare官方的代码当中，一次只能获取一个公司的数据，那就需要利用循环批量地去读取。（个人的tushareID：498867）

公司的代码在“信用数据.xlsx”文件的第一列中，如图，只需要用到第一列的证券代码。

首先需要读取xlsx文件，可以用openpyxl库，代码如下：

"""这个代码主要是xlsx文件读取"""
import openpyxl
code=[]  #公司代码
def package(path):  # path是文件路径，必须是xlsx文件
    """
    :param path: 文件读取路径
    :return: 返回列名
    """
    # 加载工作簿--->Workbook
    wbd = openpyxl.load_workbook(path)
    # 获取工作表worksheet
    sheetd = wbd.worksheets[0]
    for row_ch in range(2, sheetd.max_row + 1):  #openpyxl库读取excel行列索引从1开始
        for col_ch in 'A':
            t=sheetd[f'{col_ch}{row_ch}'].value
            code.append(t)   #读取第一列
    colName1=sheetd[f"{'A'}{1}"].value #列1的名称
    return colName1, code

 读取xlsx文件后公司代码存放在code列表当中。下面用循环和time.sleep()方法批量读取财务数据，time.sleep()主要是应对tushare每分钟接口调用次数的，不同接口不同，一般设置每分钟读取20次即可。以资产负债表为例，代码如下：

import tushare as ts
from readXlsx import package
import time
# 初始化pro接口
pro = ts.pro_api('你的token')
colName1,code =package("信用数据.xlsx")
# 拉取数据
start_time = time.time() #计算时间
count_time = 0 #计算读取次数，初始为0
for i in code[0:1570]:
    count_time +=1 #每读取一次，次数加1
    df = pro.balancesheet(**{
        "ts_code": i,
        "ann_date": "",
        "f_ann_date": "",
        "start_date": 20170101,  #开始时间，这个自己设置即可，我设置的20170101开始
        "end_date": "", #结束时间，我没有设置，默认最新
        "period": "",
        "report_type": "",
        "comp_type": "",
        "end_type": "",
        "limit": "",
        "offset": ""
    }, fields=[
        "ts_code",
        "ann_date",
        "f_ann_date",
        "end_date",
        "report_type",
        "comp_type",
        "end_type",
        "total_share",
        "cap_rese",
        "undistr_porfit",
        "surplus_rese",
        "special_rese",
        "money_cap",
        "trad_asset",
        "notes_receiv",
        "accounts_receiv",
        "oth_receiv",
        "prepayment",
        "div_receiv",
        "int_receiv",
        "inventories",
        "amor_exp",
        "nca_within_1y",
        "sett_rsrv",
        "loanto_oth_bank_fi",
        "premium_receiv",
        "reinsur_receiv",
        "reinsur_res_receiv",
        "pur_resale_fa",
        "oth_cur_assets",
        "total_cur_assets",
        "fa_avail_for_sale",
        "htm_invest",
        "lt_eqt_invest",
        "invest_real_estate",
        "time_deposits",
        "oth_assets",
        "lt_rec",
        "fix_assets",
        "cip",
        "const_materials",
        "fixed_assets_disp",
        "produc_bio_assets",
        "oil_and_gas_assets",
        "intan_assets",
        "r_and_d",
        "goodwill",
        "lt_amor_exp",
        "defer_tax_assets",
        "decr_in_disbur",
        "oth_nca",
        "total_nca",
        "cash_reser_cb",
        "depos_in_oth_bfi",
        "prec_metals",
        "deriv_assets",
        "rr_reins_une_prem",
        "rr_reins_outstd_cla",
        "rr_reins_lins_liab",
        "rr_reins_lthins_liab",
        "refund_depos",
        "ph_pledge_loans",
        "refund_cap_depos",
        "indep_acct_assets",
        "client_depos",
        "client_prov",
        "transac_seat_fee",
        "invest_as_receiv",
        "total_assets",
        "lt_borr",
        "st_borr",
        "cb_borr",
        "depos_ib_deposits",
        "loan_oth_bank",
        "trading_fl",
        "notes_payable",
        "acct_payable",
        "adv_receipts",
        "sold_for_repur_fa",
        "comm_payable",
        "payroll_payable",
        "taxes_payable",
        "int_payable",
        "div_payable",
        "oth_payable",
        "acc_exp",
        "deferred_inc",
        "st_bonds_payable",
        "payable_to_reinsurer",
        "rsrv_insur_cont",
        "acting_trading_sec",
        "acting_uw_sec",
        "non_cur_liab_due_1y",
        "oth_cur_liab",
        "total_cur_liab",
        "bond_payable",
        "lt_payable",
        "specific_payables",
        "estimated_liab",
        "defer_tax_liab",
        "defer_inc_non_cur_liab",
        "oth_ncl",
        "total_ncl",
        "depos_oth_bfi",
        "deriv_liab",
        "depos",
        "agency_bus_liab",
        "oth_liab",
        "prem_receiv_adva",
        "depos_received",
        "ph_invest",
        "reser_une_prem",
        "reser_outstd_claims",
        "reser_lins_liab",
        "reser_lthins_liab",
        "indept_acc_liab",
        "pledge_borr",
        "indem_payable",
        "policy_div_payable",
        "total_liab",
        "treasury_share",
        "ordin_risk_reser",
        "forex_differ",
        "invest_loss_unconf",
        "minority_int",
        "total_hldr_eqy_exc_min_int",
        "total_hldr_eqy_inc_min_int",
        "total_liab_hldr_eqy",
        "lt_payroll_payable",
        "oth_comp_income",
        "oth_eqt_tools",
        "oth_eqt_tools_p_shr",
        "lending_funds",
        "acc_receivable",
        "st_fin_payable",
        "payables",
        "hfs_assets",
        "hfs_sales",
        "cost_fin_assets",
        "fair_value_fin_assets",
        "contract_assets",
        "contract_liab",
        "accounts_receiv_bill",
        "accounts_pay",
        "oth_rcv_total",
        "fix_assets_total",
        "cip_total",
        "oth_pay_total",
        "long_pay_total",
        "debt_invest",
        "oth_debt_invest",
        "update_flag"
    ])
    df.to_excel(r'D:\负债数据\负债' + '{:}'.format(i) + '.xlsx') #从tushare读取到的数据存到excel当中
    print(code.index(i))
    if count_time % 20 == 0: #每20次停止休息60s
        time.sleep(60)
end_time = time.time()
seconds = end_time-start_time
print(seconds)

最后输出文件夹如图所示：