def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "daily_basic"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("trade_date", String(8)), # 交易日期 Column("close", Float), # 当日收盘价 Column("turnover_rate", Float), # 换手率(%) Column("turnover_rate_f", Float), # 换手率(自由流通股) Column("volume_ratio", Float), # 量比 Column("pe", Float), # 市盈率(总市值/净利润, 亏损的PE为空 Column("pe_ttm", Float), # 市盈率(TTM,亏损的PE为空) Column("pb", Float), # 市净率(总市值/净资产) Column("ps", Float), # 市销率 Column("ps_ttm", Float), # 市销率(TTM) Column("dv_ratio", Float), # 股息率 (%) Column("dv_ttm", Float), # 股息率(TTM)(%) Column("total_share", Float), # 总股本 (万股) Column("float_share", Float), # 流通股本 (万股) Column("free_share", Float), # 由流通股本 (万) Column("total_mv", Float), # 总市值 (万元) Column("circ_mv", Float) # 流通市值(万元) )
def update_daily_date(engine, pro, date, retry_count, pause): """日期方式更新 复权因子 行情""" df = get_adj_factor_date(pro, date, retry_count, pause) df.to_sql(dbUtil.getTableName(int(date[0:4]), "adj_factor"), engine, if_exists='append', index=False)
def update_daily_date(engine, pro, date, retry_count, pause): """日期方式更新 每日指标""" df = get_daily_basic_date(pro, date, retry_count, pause) df.to_sql(dbUtil.getTableName(int(date[0:4]), "daily_basic"), engine, if_exists='append', index=False)
def update_bulk_daily_using_code_by_year(engine, pro, codes, start_date, end_date, retry_count, pause): """ 股票代码方式更新 每日指标 """ start_year = int(start_date[0:4]) end_year = int(end_date[0:4]) for i in range(start_year, end_year): logger.info("starting processing year " + str(i)) if (i == start_year): temp_start_date = start_date temp_end_date = str(i) + "1231" elif (i == end_year): temp_start_date = str(i) + "0101" temp_end_date = end_date else: temp_start_date = str(i) + "0101" temp_end_date = str(i) + "1231" for value in codes['ts_code']: logger.debug("processing " + value + " for date " + temp_start_date + "-" + temp_end_date) df = get_limit_list_code(pro, value, temp_start_date, temp_end_date, retry_count, pause) try: logger.debug("start inserting data into DB") df.to_sql(dbUtil.getTableName(i, "limit_list"), engine, if_exists='append', index=False) logger.debug("end inserting data into DB") except IntegrityError as err: logger.error("error processing data for year" + str(i) + " stock code " + value) logger.error(err)
def update_bulk_income_by_ts_code_and_insert_by_year(base_name, engine, pro, codes, sharding_column, failed_count=0, failed_tolerent=3): failed = [] for code in codes['ts_code']: logger.debug("started processing data for " + code) try: to_insert = pro.income_vip(ts_code=code) logger.debug("start inserting data into DB") distinct_years = set(to_insert[sharding_column].str[0:4]) for year in distinct_years: year_section = to_insert[to_insert[sharding_column].str[0:4] == year] if (year == None): year = 9999 year_section = to_insert[pd.isna(to_insert[sharding_column]) == True] year_section.to_sql(dbUtil.getTableName(int(year), base_name=base_name), engine, if_exists='append', index=False) logger.debug("end inserting data into DB") except Exception as e: failed.append(code) logger.error(e) logger.error("error processing data for code " + code) if (failed_count < failed_tolerent and len(failed)>0): logger.warning("retrying now.") failed_count = failed_count + 1 update_bulk_income_by_ts_code_and_insert_by_year(base_name=base_name, engine=engine, pro=pro, codes=pd.DataFrame(failed, columns=['ts_code']), sharding_column=sharding_column, failed_count=failed_count) else: logger.error("the below code has failed after maximum attempts. " + ','.join(failed))
def update_bulk_express_by_ts_code_and_insert_by_year(base_name, engine, pro, codes, sharding_column, failed_count=0, failed_tolerent=3): failed = [] for code in codes['ts_code']: logger.debug("started processing data for " + code) try: to_insert = pro.express_vip(ts_code=code, fields='ts_code,ann_date,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') logger.debug("start inserting data into DB") distinct_years = set(to_insert[sharding_column].str[0:4]) for year in distinct_years: year_section = to_insert[to_insert[sharding_column].str[0:4] == year] if (year == None): year = 9999 year_section = to_insert[pd.isna(to_insert[sharding_column]) == True] year_section.to_sql(dbUtil.getTableName(int(year), base_name=base_name), engine, if_exists='append', index=False) logger.debug("end inserting data into DB") except Exception as e: failed.append(code) logger.error(e) logger.error("error processing data for code " + code) if (failed_count < failed_tolerent): logger.warning("retrying now.") failed_count = failed_count + 1 update_bulk_express_by_ts_code_and_insert_by_year(base_name=base_name, engine=engine, pro=pro, codes=pd.DataFrame(failed, columns=['ts_code']), sharding_column=sharding_column, failed_count=failed_count) else: logger.error("the below code has failed after maximum attempts. " + ','.join(failed))
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "moneyflow"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("trade_date", String(8)), # 交易日期 Column("buy_sm_vol", Float), # 小单买入量(手) Column("buy_sm_amount", Float), # 小单买入金额(万元) Column("sell_sm_vol", Float), # 小单卖出量(手) Column("sell_sm_amount", Float), # 小单卖出金额(万元) Column("buy_md_vol", Float), # 中单买入量(手) Column("buy_md_amount", Float), # 中单买入金额(万元) Column("sell_md_vol", Float), # 中单卖出量(手) Column("sell_md_amount", Float), # 中单卖出金额(万元) Column("buy_lg_vol", Float), # 大单买入量(手) Column("buy_lg_amount", Float), # 大单买入金额(万元) Column("sell_lg_vol", Float), # 大单卖出量(手) Column("sell_lg_amount", Float), # 大单卖出金额(万元) Column("buy_elg_vol", Float), # 特大单买入量(手) Column("buy_elg_amount", Float), # 特大单买入金额(万元) Column("sell_elg_vol", Float), # 特大单卖出量(手) Column("sell_elg_amount", Float), # 特大单卖出金额(万元) Column("net_mf_vol", Float), # 净流入量(手) Column("net_mf_amount", Float) # 净流入额(万元) )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "limit_list"), metadata, Column("id", INT, primary_key=True), Column("trade_date", String(8)), # 交易日期 Column("ts_code", String(10)), # 股票代码 Column("name", String(10)), # 股票名称 Column("close", Float), # 收盘价 Column("pct_chg", Float), # 涨跌幅 Column("amp", Float), # 振幅 Column("fc_ratio", Float), # 封单金额/日成交金额 Column("fl_ratio", Float), # 封单手数/流通股本 Column("fd_amount", Float), # 封单金额 Column("first_time", String(8)), # 首次涨停时间 Column("last_time", String(8)), # 最后封板时间 Column("open_times", Float), # 打开次数 Column("strth", Float), # 涨跌停强度 Column("limit", String(1)) # D跌停U涨停 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "adj_factor"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("trade_date", String(8)), # 交易日期 Column("adj_factor", Float), # 复权因子 )
def update_bulk_fina_indicator_by_ts_code_and_insert_by_year( base_name, engine, pro, codes, sharding_column, failed_count=0, failed_tolerent=3): failed = [] for code in codes['ts_code']: logger.debug("started processing data for " + code) try: to_insert = pro.fina_indicator_vip( ts_code=code, fields= 'ts_code,ann_date,end_date,eps,dt_eps,total_revenue_ps,revenue_ps,capital_rese_ps,surplus_rese_ps,undist_profit_ps,extra_item,profit_dedt,gross_margin,current_ratio,quick_ratio,cash_ratio,invturn_days,arturn_days,inv_turn,ar_turn,ca_turn,fa_turn,assets_turn,op_income,valuechange_income,interst_income,daa,ebit,ebitda,fcff,fcfe,current_exint,noncurrent_exint,interestdebt,netdebt,tangible_asset,working_capital,networking_capital,invest_capital,retained_earnings,diluted2_eps,bps,ocfps,retainedps,cfps,ebit_ps,fcff_ps,fcfe_ps,netprofit_margin,grossprofit_margin,cogs_of_sales,expense_of_sales,profit_to_gr,saleexp_to_gr,adminexp_of_gr,finaexp_of_gr,impai_ttm,gc_of_gr,op_of_gr,ebit_of_gr,roe,roe_waa,roe_dt,roa,npta,roic,roe_yearly,roa2_yearly,roe_avg,opincome_of_ebt,investincome_of_ebt,n_op_profit_of_ebt,tax_to_ebt,dtprofit_to_profit,salescash_to_or,ocf_to_or,ocf_to_opincome,capitalized_to_da,debt_to_assets,assets_to_eqt,dp_assets_to_eqt,ca_to_assets,nca_to_assets,tbassets_to_totalassets,int_to_talcap,eqt_to_talcapital,currentdebt_to_debt,longdeb_to_debt,ocf_to_shortdebt,debt_to_eqt,eqt_to_debt,eqt_to_interestdebt,tangibleasset_to_debt,tangasset_to_intdebt,tangibleasset_to_netdebt,ocf_to_debt,ocf_to_interestdebt,ocf_to_netdebt,ebit_to_interest,longdebt_to_workingcapital,ebitda_to_debt,turn_days,roa_yearly,roa_dp,fixed_assets,profit_prefin_exp,non_op_profit,op_to_ebt,nop_to_ebt,ocf_to_profit,cash_to_liqdebt,cash_to_liqdebt_withinterest,op_to_liqdebt,op_to_debt,roic_yearly,total_fa_trun,profit_to_op,q_opincome,q_investincome,q_dtprofit,q_eps,q_netprofit_margin,q_gsprofit_margin,q_exp_to_sales,q_profit_to_gr,q_saleexp_to_gr,q_adminexp_to_gr,q_finaexp_to_gr,q_impair_to_gr_ttm,q_gc_to_gr,q_op_to_gr,q_roe,q_dt_roe,q_npta,q_opincome_to_ebt,q_investincome_to_ebt,q_dtprofit_to_profit,q_salescash_to_or,q_ocf_to_sales,q_ocf_to_or,basic_eps_yoy,dt_eps_yoy,cfps_yoy,op_yoy,ebt_yoy,netprofit_yoy,dt_netprofit_yoy,ocf_yoy,roe_yoy,bps_yoy,assets_yoy,eqt_yoy,tr_yoy,or_yoy,q_gr_yoy,q_gr_qoq,q_sales_yoy,q_sales_qoq,q_op_yoy,q_op_qoq,q_profit_yoy,q_profit_qoq,q_netprofit_yoy,q_netprofit_qoq,equity_yoy,rd_exp' ) logger.debug("start inserting data into DB") distinct_years = set(to_insert[sharding_column].str[0:4]) for year in distinct_years: year_section = to_insert[to_insert[sharding_column].str[0:4] == year] if (year == None): year = 9999 year_section = to_insert[pd.isna( to_insert[sharding_column]) == True] year_section.to_sql(dbUtil.getTableName(int(year), base_name=base_name), engine, if_exists='append', index=False) logger.debug("end inserting data into DB") except Exception as e: failed.append(code) logger.error(e) logger.error("error processing data for code " + code) if (failed_count < failed_tolerent): logger.warning("retrying now.") failed_count = failed_count + 1 update_bulk_fina_indicator_by_ts_code_and_insert_by_year( base_name=base_name, engine=engine, pro=pro, codes=pd.DataFrame(failed, columns=['ts_code']), sharding_column=sharding_column, failed_count=failed_count) else: logger.error("the below code has failed after maximum attempts. " + ','.join(failed))
def update_bulk_limit_list_by_day(engine, pro, start_date, end_date): trade_cal = get_trade_cal(pro, start_date, end_date) for a_day in trade_cal: logger.debug("started processing data for date " + a_day) df = get_limit_list_date(pro=pro, date=a_day) try: logger.debug("start inserting data into DB") df.to_sql(dbUtil.getTableName(int(a_day[0:4]), "limit_list"), engine, if_exists='append', index=False) logger.debug("end inserting data into DB") except IntegrityError as err: logger.error("error processing data for date" + str(a_day) + " as data for that day already exists") logger.error(err) except Exception as e: logger.error("error processing data for date" + str(a_day)) logger.error(e)
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "daily"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("trade_date", String(8)), # 交易日期 Column("open", Float), # 开盘价 Column("high", Float), # 最高价 Column("low", Float), # 最低价 Column("close", Float), # 收盘价 Column("pre_close", Float), # 昨收价 Column("change", Float), # 涨跌额 Column("pct_chg", Float), # 涨跌幅 (未复权,如果是复权请用 通用行情接口 ) Column("vol", Float), # 成交量 (手) Column("amount", Float) # 成交额 (千元) )
def get_missing_dates(pro, engine, ts_code, dates, interface_type): retry_count = 0 missing_from_tushare = 0 number_of_inserted_rows = 0 for date in dates: isSuccessful = False if (interface_type == 'daily'): while (retry_count < 3 and not isSuccessful): try: df = pro.daily(ts_code=ts_code, start_date=date, end_date=date) isSuccessful = True except Exception as e: logger.error( "error pulling data from tushare for interface daily, code " + ts_code) logger.error(e) retry_count = retry_count + 1 if (isSuccessful): if df.empty: missing_from_tushare = missing_from_tushare + 1 else: df['trade_date_int'] = int(df['trade_date']) df.to_sql(dbUtil.getTableName(int(str(date)[0:4]), "daily"), engine, if_exists='append', index=False) number_of_inserted_rows = number_of_inserted_rows + 1 else: logger.error( 'error pulling data from tushare for interface daily, code' + ts_code + ' date ' + date) return { "missing_from_tushare": missing_from_tushare, "number_of_inserted_rows": number_of_inserted_rows }
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "limit_list"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("holder_name", String(10)), # 股东名称 Column("holder_type", String(1)), # 股东类型G高管P个人C公司 Column("in_de", String(2)), # 类型IN增持DE减持 Column("change_vol", Float), # 变动数量 Column("change_ratio", Float), # 占流通比例(%) Column("after_share", Float), # 变动后持股 Column("after_ratio", Float), # 变动后占流通比例(%) Column("avg_price", Float), # 平均价格 Column("total_share", Float) # 持股总数 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "express"), metadata, Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("end_date", String(8)), Column("revenue", Float), # 营业收入(元) Column("operate_profit", Float), # 营业利润(元) Column("total_profit", Float), # 利润总额(元) Column("n_income", Float), # 净利润(元) Column("total_assets", Float), # 总资产(元) Column("total_hldr_eqy_exc_min_int", Float), # 股东权益合计(不含少数股东权益)(元) Column("diluted_eps", Float), # 每股收益(摊薄)(元) Column("diluted_roe", Float), # 净资产收益率(摊薄)(%) Column("yoy_net_profit", Float), # 去年同期修正后净利润 Column("bps", Float), # 每股净资产 Column("yoy_sales", Float), # 同比增长率:营业收入 Column("yoy_op", Float), # 同比增长率:营业利润 Column("yoy_tp", Float), # 同比增长率:利润总额 Column("yoy_dedu_np", Float), # 同比增长率:归属母公司股东的净利润 Column("yoy_eps", Float), # 同比增长率:基本每股收益 Column("yoy_roe", Float), # 同比增减:加权平均净资产收益率 Column("growth_assets", Float), # 比年初增长率:总资产 Column("yoy_equity", Float), # 比年初增长率:归属母公司的股东权益 Column("growth_bps", Float), # 比年初增长率:归属于母公司股东的每股净资产 Column("or_last_year", Float), # 去年同期营业收入 Column("op_last_year", Float), # 去年同期营业利润 Column("tp_last_year", Float), # 去年同期利润总额 Column("np_last_year", Float), # 去年同期净利润 Column("eps_last_year", Float), # 去年同期每股收益 Column("open_net_assets", Float), # 期初净资产 Column("open_bps", Float) # 期初每股净资产 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "fina_indicator"), metadata, Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("end_date", String(8)), Column("eps", Float), # 基本每股收益 Column("dt_eps", Float), # 稀释每股收益 Column("total_revenue_ps", Float), # 每股营业总收入 Column("revenue_ps", Float), # 每股营业收入 Column("capital_rese_ps", Float), # 每股资本公积 Column("surplus_rese_ps", Float), # 每股盈余公积 Column("undist_profit_ps", Float), # 每股未分配利润 Column("extra_item", Float), # 非经常性损益 Column("profit_dedt", Float), # 扣除非经常性损益后的净利润 Column("gross_margin", Float), # 毛利 Column("current_ratio", Float), # 流动比率 Column("quick_ratio", Float), # 速动比率 Column("cash_ratio", Float), # 保守速动比率 Column("invturn_days", Float), # 存货周转天数 Column("arturn_days", Float), # 应收账款周转天数 Column("inv_turn", Float), # 存货周转率 Column("ar_turn", Float), # 应收账款周转率 Column("ca_turn", Float), # 流动资产周转率 Column("fa_turn", Float), # 固定资产周转率 Column("assets_turn", Float), # 总资产周转率 Column("op_income", Float), # 经营活动净收益 Column("valuechange_income", Float), # 价值变动净收益 Column("interst_income", Float), # 利息费用 Column("daa", Float), # 折旧与摊销 Column("ebit", Float), # 息税前利润 Column("ebitda", Float), # 息税折旧摊销前利润 Column("fcff", Float), # 企业自由现金流量 Column("fcfe", Float), # 股权自由现金流量 Column("current_exint", Float), # 无息流动负债 Column("noncurrent_exint", Float), # 无息非流动负债 Column("interestdebt", Float), # 带息债务 Column("netdebt", Float), # 净债务 Column("tangible_asset", Float), # 有形资产 Column("working_capital", Float), # 营运资金 Column("networking_capital", Float), # 营运流动资本 Column("invest_capital", Float), # 全部投入资本 Column("retained_earnings", Float), # 留存收益 Column("diluted2_eps", Float), # 期末摊薄每股收益 Column("bps", Float), # 每股净资产 Column("ocfps", Float), # 每股经营活动产生的现金流量净额 Column("retainedps", Float), # 每股留存收益 Column("cfps", Float), # 每股现金流量净额 Column("ebit_ps", Float), # 每股息税前利润 Column("fcff_ps", Float), # 每股企业自由现金流量 Column("fcfe_ps", Float), # 每股股东自由现金流量 Column("netprofit_margin", Float), # 销售净利率 Column("grossprofit_margin", Float), # 销售毛利率 Column("cogs_of_sales", Float), # 销售成本率 Column("expense_of_sales", Float), # 销售期间费用率 Column("profit_to_gr", Float), # 净利润/营业总收入 Column("saleexp_to_gr", Float), # 销售费用/营业总收入 Column("adminexp_of_gr", Float), # 管理费用/营业总收入 Column("finaexp_of_gr", Float), # 财务费用/营业总收入 Column("impai_ttm", Float), # 资产减值损失/营业总收入 Column("gc_of_gr", Float), # 营业总成本/营业总收入 Column("op_of_gr", Float), # 营业利润/营业总收入 Column("ebit_of_gr", Float), # 息税前利润/营业总收入 Column("roe", Float), # 净资产收益率 Column("roe_waa", Float), # 加权平均净资产收益率 Column("roe_dt", Float), # 净资产收益率(扣除非经常损益) Column("roa", Float), # 总资产报酬率 Column("npta", Float), # 总资产净利润 Column("roic", Float), # 投入资本回报率 Column("roe_yearly", Float), # 年化净资产收益率 Column("roa2_yearly", Float), # 年化总资产报酬率 Column("roe_avg", Float), # 平均净资产收益率(增发条件) Column("opincome_of_ebt", Float), # 经营活动净收益/利润总额 Column("investincome_of_ebt", Float), # 价值变动净收益/利润总额 Column("n_op_profit_of_ebt", Float), # 营业外收支净额/利润总额 Column("tax_to_ebt", Float), # 所得税/利润总额 Column("dtprofit_to_profit", Float), # 扣除非经常损益后的净利润/净利润 Column("salescash_to_or", Float), # 销售商品提供劳务收到的现金/营业收入 Column("ocf_to_or", Float), # 经营活动产生的现金流量净额/营业收入 Column("ocf_to_opincome", Float), # 经营活动产生的现金流量净额/经营活动净收益 Column("capitalized_to_da", Float), # 资本支出/折旧和摊销 Column("debt_to_assets", Float), # 资产负债率 Column("assets_to_eqt", Float), # 权益乘数 Column("dp_assets_to_eqt", Float), # 权益乘数(杜邦分析) Column("ca_to_assets", Float), # 流动资产/总资产 Column("nca_to_assets", Float), # 非流动资产/总资产 Column("tbassets_to_totalassets", Float), # 有形资产/总资产 Column("int_to_talcap", Float), # 带息债务/全部投入资本 Column("eqt_to_talcapital", Float), # 归属于母公司的股东权益/全部投入资本 Column("currentdebt_to_debt", Float), # 流动负债/负债合计 Column("longdeb_to_debt", Float), # 非流动负债/负债合计 Column("ocf_to_shortdebt", Float), # 经营活动产生的现金流量净额/流动负债 Column("debt_to_eqt", Float), # 产权比率 Column("eqt_to_debt", Float), # 归属于母公司的股东权益/负债合计 Column("eqt_to_interestdebt", Float), # 归属于母公司的股东权益/带息债务 Column("tangibleasset_to_debt", Float), # 有形资产/负债合计 Column("tangasset_to_intdebt", Float), # 有形资产/带息债务 Column("tangibleasset_to_netdebt", Float), # 有形资产/净债务 Column("ocf_to_debt", Float), # 经营活动产生的现金流量净额/负债合计 Column("ocf_to_interestdebt", Float), # 经营活动产生的现金流量净额/带息债务 Column("ocf_to_netdebt", Float), # 经营活动产生的现金流量净额/净债务 Column("ebit_to_interest", Float), # 已获利息倍数(EBIT/利息费用) Column("longdebt_to_workingcapital", Float), # 长期债务与营运资金比率 Column("ebitda_to_debt", Float), # 息税折旧摊销前利润/负债合计 Column("turn_days", Float), # 营业周期 Column("roa_yearly", Float), # 年化总资产净利率 Column("roa_dp", Float), # 总资产净利率(杜邦分析) Column("fixed_assets", Float), # 固定资产合计 Column("profit_prefin_exp", Float), # 扣除财务费用前营业利润 Column("non_op_profit", Float), # 非营业利润 Column("op_to_ebt", Float), # 营业利润/利润总额 Column("nop_to_ebt", Float), # 非营业利润/利润总额 Column("ocf_to_profit", Float), # 经营活动产生的现金流量净额/营业利润 Column("cash_to_liqdebt", Float), # 货币资金/流动负债 Column("cash_to_liqdebt_withinterest", Float), # 货币资金/带息流动负债 Column("op_to_liqdebt", Float), # 营业利润/流动负债 Column("op_to_debt", Float), # 营业利润/负债合计 Column("roic_yearly", Float), # 年化投入资本回报率 Column("total_fa_trun", Float), # 固定资产合计周转率 Column("profit_to_op", Float), # 利润总额/营业收入 Column("q_opincome", Float), # 经营活动单季度净收益 Column("q_investincome", Float), # 价值变动单季度净收益 Column("q_dtprofit", Float), # 扣除非经常损益后的单季度净利润 Column("q_eps", Float), # 每股收益(单季度) Column("q_netprofit_margin", Float), # 销售净利率(单季度) Column("q_gsprofit_margin", Float), # 销售毛利率(单季度) Column("q_exp_to_sales", Float), # 销售期间费用率(单季度) Column("q_profit_to_gr", Float), # 净利润/营业总收入(单季度) Column("q_saleexp_to_gr", Float), # 销售费用/营业总收入 (单季度) Column("q_adminexp_to_gr", Float), # 管理费用/营业总收入 (单季度) Column("q_finaexp_to_gr", Float), # 财务费用/营业总收入 (单季度) Column("q_impair_to_gr_ttm", Float), # 资产减值损失/营业总收入(单季度) Column("q_gc_to_gr", Float), # 营业总成本/营业总收入 (单季度) Column("q_op_to_gr", Float), # 营业利润/营业总收入(单季度) Column("q_roe", Float), # 净资产收益率(单季度) Column("q_dt_roe", Float), # 净资产单季度收益率(扣除非经常损益) Column("q_npta", Float), # 总资产净利润(单季度) Column("q_opincome_to_ebt", Float), # 经营活动净收益/利润总额(单季度) Column("q_investincome_to_ebt", Float), # 价值变动净收益/利润总额(单季度) Column("q_dtprofit_to_profit", Float), # 扣除非经常损益后的净利润/净利润(单季度) Column("q_salescash_to_or", Float), # 销售商品提供劳务收到的现金/营业收入(单季度) Column("q_ocf_to_sales", Float), # 经营活动产生的现金流量净额/营业收入(单季度) Column("q_ocf_to_or", Float), # 经营活动产生的现金流量净额/经营活动净收益(单季度) Column("basic_eps_yoy", Float), # 基本每股收益同比增长率(%) Column("dt_eps_yoy", Float), # 稀释每股收益同比增长率(%) Column("cfps_yoy", Float), # 每股经营活动产生的现金流量净额同比增长率(%) Column("op_yoy", Float), # 营业利润同比增长率(%) Column("ebt_yoy", Float), # 利润总额同比增长率(%) Column("netprofit_yoy", Float), # 归属母公司股东的净利润同比增长率(%) Column("dt_netprofit_yoy", Float), # 归属母公司股东的净利润-扣除非经常损益同比增长率(%) Column("ocf_yoy", Float), # 经营活动产生的现金流量净额同比增长率(%) Column("roe_yoy", Float), # 净资产收益率(摊薄)同比增长率(%) Column("bps_yoy", Float), # 每股净资产相对年初增长率(%) Column("assets_yoy", Float), # 资产总计相对年初增长率(%) Column("eqt_yoy", Float), # 归属母公司的股东权益相对年初增长率(%) Column("tr_yoy", Float), # 营业总收入同比增长率(%) Column("or_yoy", Float), # 营业收入同比增长率(%) Column("q_gr_yoy", Float), # 营业总收入同比增长率(%)(单季度) Column("q_gr_qoq", Float), # 营业总收入环比增长率(%)(单季度) Column("q_sales_yoy", Float), # 营业收入同比增长率(%)(单季度) Column("q_sales_qoq", Float), # 营业收入环比增长率(%)(单季度) Column("q_op_yoy", Float), # 营业利润同比增长率(%)(单季度) Column("q_op_qoq", Float), # 营业利润环比增长率(%)(单季度) Column("q_profit_yoy", Float), # 净利润同比增长率(%)(单季度) Column("q_profit_qoq", Float), # 净利润环比增长率(%)(单季度) Column("q_netprofit_yoy", Float), # 归属母公司股东的净利润同比增长率(%)(单季度) Column("q_netprofit_qoq", Float), # 归属母公司股东的净利润环比增长率(%)(单季度) Column("equity_yoy", Float), # 净资产同比增长率 Column("rd_exp", Float) # 研发费用 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "income"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("f_ann_date", String(8)), Column("end_date", String(8)), Column("report_type", String(3)), Column("comp_type", String(3)), Column("basic_eps", Float), # 基本每股收益 Column("diluted_eps", Float), # 稀释每股收益 Column("total_revenue", Float), # 营业总收入 Column("revenue", Float), # 营业收入 Column("int_income", Float), # 利息收入 Column("prem_earned", Float), # 已赚保费 Column("comm_income", Float), # 手续费及佣金收入 Column("n_commis_income", Float), # 手续费及佣金净收入 Column("n_oth_income", Float), # 其他经营净收益 Column("n_oth_b_income", Float), # 加:其他业务净收益 Column("prem_income", Float), # 保险业务收入 Column("out_prem", Float), # 减:分出保费 Column("une_prem_reser", Float), # 提取未到期责任准备金 Column("reins_income", Float), # 其中:分保费收入 Column("n_sec_tb_income", Float), # 代理买卖证券业务净收入 Column("n_sec_uw_income", Float), # 证券承销业务净收入 Column("n_asset_mg_income", Float), # 受托客户资产管理业务净收入 Column("oth_b_income", Float), # 其他业务收入 Column("fv_value_chg_gain", Float), # 加:公允价值变动净收益 Column("invest_income", Float), # 加:投资净收益 Column("ass_invest_income", Float), # 其中:对联营企业和合营企业的投资收益 Column("forex_gain", Float), # 加:汇兑净收益 Column("total_cogs", Float), # 营业总成本 Column("oper_cost", Float), # 减:营业成本 Column("int_exp", Float), # 减:利息支出 Column("comm_exp", Float), # 减:手续费及佣金支出 Column("biz_tax_surchg", Float), # 减:营业税金及附加 Column("sell_exp", Float), # 减:销售费用 Column("admin_exp", Float), # 减:管理费用 Column("fin_exp", Float), # 减:财务费用 Column("assets_impair_loss", Float), # 减:资产减值损失 Column("prem_refund", Float), # 退保金 Column("compens_payout", Float), # 赔付总支出 Column("reser_insur_liab", Float), # 提取保险责任准备金 Column("div_payt", Float), # 保户红利支出 Column("reins_exp", Float), # 分保费用 Column("oper_exp", Float), # 营业支出 Column("compens_payout_refu", Float), # 减:摊回赔付支出 Column("insur_reser_refu", Float), # 减:摊回保险责任准备金 Column("reins_cost_refund", Float), # 减:摊回分保费用 Column("other_bus_cost", Float), # 其他业务成本 Column("operate_profit", Float), # 营业利润 Column("non_oper_income", Float), # 加:营业外收入 Column("non_oper_exp", Float), # 减:营业外支出 Column("nca_disploss", Float), # 其中:减:非流动资产处置净损失 Column("total_profit", Float), # 利润总额 Column("income_tax", Float), # 所得税费用 Column("n_income", Float), # 净利润(含少数股东损益) Column("n_income_attr_p", Float), # 净利润(不含少数股东损益) Column("minority_gain", Float), # 少数股东损益 Column("oth_compr_income", Float), # 其他综合收益 Column("t_compr_income", Float), # 综合收益总额 Column("compr_inc_attr_p", Float), # 归属于母公司(或股东)的综合收益总额 Column("compr_inc_attr_m_s", Float), # 归属于少数股东的综合收益总额 Column("ebit", Float), # 息税前利润 Column("ebitda", Float), # 息税折旧摊销前利润 Column("insurance_exp", Float), # 保险业务支出 Column("undist_profit", Float), # 年初未分配利润 Column("distable_profit", Float), # 可分配利润 Column("update_flag", Float) # 更新标识,0未修改1更正过 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "balancesheet"), metadata, Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("f_ann_date", String(8)), Column("end_date", String(8)), Column("report_type", String(3)), Column("comp_type", String(3)), Column("total_share", Float), # 期末总股本 Column("cap_rese", Float), # 资本公积金 Column("undistr_porfit", Float), # 未分配利润 Column("surplus_rese", Float), # 盈余公积金 Column("special_rese", Float), # 专项储备 Column("money_cap", Float), # 货币资金 Column("trad_asset", Float), # 交易性金融资产 Column("notes_receiv", Float), # 应收票据 Column("accounts_receiv", Float), # 应收账款 Column("oth_receiv", Float), # 其他应收款 Column("prepayment", Float), # 预付款项 Column("div_receiv", Float), # 应收股利 Column("int_receiv", Float), # 应收利息 Column("inventories", Float), # 存货 Column("amor_exp", Float), # 待摊费用 Column("nca_within_1y", Float), # 一年内到期的非流动资产 Column("sett_rsrv", Float), # 结算备付金 Column("loanto_oth_bank_fi", Float), # 拆出资金 Column("premium_receiv", Float), # 应收保费 Column("reinsur_receiv", Float), # 应收分保账款 Column("reinsur_res_receiv", Float), # 应收分保合同准备金 Column("pur_resale_fa", Float), # 买入返售金融资产 Column("oth_cur_assets", Float), # 其他流动资产 Column("total_cur_assets", Float), # 流动资产合计 Column("fa_avail_for_sale", Float), # 可供出售金融资产 Column("htm_invest", Float), # 持有至到期投资 Column("lt_eqt_invest", Float), # 长期股权投资 Column("invest_real_estate", Float), # 投资性房地产 Column("time_deposits", Float), # 定期存款 Column("oth_assets", Float), # 其他资产 Column("lt_rec", Float), # 长期应收款 Column("fix_assets", Float), # 固定资产 Column("cip", Float), # 在建工程 Column("const_materials", Float), # 工程物资 Column("fixed_assets_disp", Float), # 固定资产清理 Column("produc_bio_assets", Float), # 生产性生物资产 Column("oil_and_gas_assets", Float), # 油气资产 Column("intan_assets", Float), # 无形资产 Column("r_and_d", Float), # 研发支出 Column("goodwill", Float), # 商誉 Column("lt_amor_exp", Float), # 长期待摊费用 Column("defer_tax_assets", Float), # 递延所得税资产 Column("decr_in_disbur", Float), # 发放贷款及垫款 Column("oth_nca", Float), # 其他非流动资产 Column("total_nca", Float), # 非流动资产合计 Column("cash_reser_cb", Float), # 现金及存放中央银行款项 Column("depos_in_oth_bfi", Float), # 存放同业和其它金融机构款项 Column("prec_metals", Float), # 贵金属 Column("deriv_assets", Float), # 衍生金融资产 Column("rr_reins_une_prem", Float), # 应收分保未到期责任准备金 Column("rr_reins_outstd_cla", Float), # 应收分保未决赔款准备金 Column("rr_reins_lins_liab", Float), # 应收分保寿险责任准备金 Column("rr_reins_lthins_liab", Float), # 应收分保长期健康险责任准备金 Column("refund_depos", Float), # 存出保证金 Column("ph_pledge_loans", Float), # 保户质押贷款 Column("refund_cap_depos", Float), # 存出资本保证金 Column("indep_acct_assets", Float), # 独立账户资产 Column("client_depos", Float), # 其中:客户资金存款 Column("client_prov", Float), # 其中:客户备付金 Column("transac_seat_fee", Float), # 其中:交易席位费 Column("invest_as_receiv", Float), # 应收款项类投资 Column("total_assets", Float), # 资产总计 Column("lt_borr", Float), # 长期借款 Column("st_borr", Float), # 短期借款 Column("cb_borr", Float), # 向中央银行借款 Column("depos_ib_deposits", Float), # 吸收存款及同业存放 Column("loan_oth_bank", Float), # 拆入资金 Column("trading_fl", Float), # 交易性金融负债 Column("notes_payable", Float), # 应付票据 Column("acct_payable", Float), # 应付账款 Column("adv_receipts", Float), # 预收款项 Column("sold_for_repur_fa", Float), # 卖出回购金融资产款 Column("comm_payable", Float), # 应付手续费及佣金 Column("payroll_payable", Float), # 应付职工薪酬 Column("taxes_payable", Float), # 应交税费 Column("int_payable", Float), # 应付利息 Column("div_payable", Float), # 应付股利 Column("oth_payable", Float), # 其他应付款 Column("acc_exp", Float), # 预提费用 Column("deferred_inc", Float), # 递延收益 Column("st_bonds_payable", Float), # 应付短期债券 Column("payable_to_reinsurer", Float), # 应付分保账款 Column("rsrv_insur_cont", Float), # 保险合同准备金 Column("acting_trading_sec", Float), # 代理买卖证券款 Column("acting_uw_sec", Float), # 代理承销证券款 Column("non_cur_liab_due_1y", Float), # 一年内到期的非流动负债 Column("oth_cur_liab", Float), # 其他流动负债 Column("total_cur_liab", Float), # 流动负债合计 Column("bond_payable", Float), # 应付债券 Column("lt_payable", Float), # 长期应付款 Column("specific_payables", Float), # 专项应付款 Column("estimated_liab", Float), # 预计负债 Column("defer_tax_liab", Float), # 递延所得税负债 Column("defer_inc_non_cur_liab", Float), # 递延收益-非流动负债 Column("oth_ncl", Float), # 其他非流动负债 Column("total_ncl", Float), # 非流动负债合计 Column("depos_oth_bfi", Float), # 同业和其它金融机构存放款项 Column("deriv_liab", Float), # 衍生金融负债 Column("depos", Float), # 吸收存款 Column("agency_bus_liab", Float), # 代理业务负债 Column("oth_liab", Float), # 其他负债 Column("prem_receiv_adva", Float), # 预收保费 Column("depos_received", Float), # 存入保证金 Column("ph_invest", Float), # 保户储金及投资款 Column("reser_une_prem", Float), # 未到期责任准备金 Column("reser_outstd_claims", Float), # 未决赔款准备金 Column("reser_lins_liab", Float), # 寿险责任准备金 Column("reser_lthins_liab", Float), # 长期健康险责任准备金 Column("indept_acc_liab", Float), # 独立账户负债 Column("pledge_borr", Float), # 其中:质押借款 Column("indem_payable", Float), # 应付赔付款 Column("policy_div_payable", Float), # 应付保单红利 Column("total_liab", Float), # 负债合计 Column("treasury_share", Float), # 减:库存股 Column("ordin_risk_reser", Float), # 一般风险准备 Column("forex_differ", Float), # 外币报表折算差额 Column("invest_loss_unconf", Float), # 未确认的投资损失 Column("minority_int", Float), # 少数股东权益 Column("total_hldr_eqy_exc_min_int", Float), # 股东权益合计(不含少数股东权益) Column("total_hldr_eqy_inc_min_int", Float), # 股东权益合计(含少数股东权益) Column("total_liab_hldr_eqy", Float), # 负债及股东权益总计 Column("lt_payroll_payable", Float), # 长期应付职工薪酬 Column("oth_comp_income", Float), # 其他综合收益 Column("oth_eqt_tools", Float), # 其他权益工具 Column("oth_eqt_tools_p_shr", Float), # 其他权益工具(优先股) Column("lending_funds", Float), # 融出资金 Column("acc_receivable", Float), # 应收款项 Column("st_fin_payable", Float), # 应付短期融资款 Column("payables", Float), # 应付款项 Column("hfs_assets", Float), # 持有待售的资产 Column("hfs_sales", Float), # 持有待售的负债 Column("update_flag", Float) # 更新标识 )
def getTableMeta(year: int, metadata: MetaData) -> Table: """ get corresponding table meta data. :param year: year of the data :return: a Table object representing the table structure """ return Table( dbUtil.getTableName(year, "cashflow"), metadata, Column("id", INT, primary_key=True), Column("ts_code", String(10)), # 股票代码 Column("ann_date", String(8)), Column("f_ann_date", String(8)), Column("end_date", String(8)), Column("comp_type", String(3)), Column("report_type", String(3)), Column("net_profit", Float), # 净利润 Column("finan_exp", Float), # 财务费用 Column("c_fr_sale_sg", Float), # 销售商品、提供劳务收到的现金 Column("recp_tax_rends", Float), # 收到的税费返还 Column("n_depos_incr_fi", Float), # 客户存款和同业存放款项净增加额 Column("n_incr_loans_cb", Float), # 向中央银行借款净增加额 Column("n_inc_borr_oth_fi", Float), # 向其他金融机构拆入资金净增加额 Column("prem_fr_orig_contr", Float), # 收到原保险合同保费取得的现金 Column("n_incr_insured_dep", Float), # 保户储金净增加额 Column("n_reinsur_prem", Float), # 收到再保业务现金净额 Column("n_incr_disp_tfa", Float), # 处置交易性金融资产净增加额 Column("ifc_cash_incr", Float), # 收取利息和手续费净增加额 Column("n_incr_disp_faas", Float), # 处置可供出售金融资产净增加额 Column("n_incr_loans_oth_bank", Float), # 拆入资金净增加额 Column("n_cap_incr_repur", Float), # 回购业务资金净增加额 Column("c_fr_oth_operate_a", Float), # 收到其他与经营活动有关的现金 Column("c_inf_fr_operate_a", Float), # 经营活动现金流入小计 Column("c_paid_goods_s", Float), # 购买商品、接受劳务支付的现金 Column("c_paid_to_for_empl", Float), # 支付给职工以及为职工支付的现金 Column("c_paid_for_taxes", Float), # 支付的各项税费 Column("n_incr_clt_loan_adv", Float), # 客户贷款及垫款净增加额 Column("n_incr_dep_cbob", Float), # 存放央行和同业款项净增加额 Column("c_pay_claims_orig_inco", Float), # 支付原保险合同赔付款项的现金 Column("pay_handling_chrg", Float), # 支付手续费的现金 Column("pay_comm_insur_plcy", Float), # 支付保单红利的现金 Column("oth_cash_pay_oper_act", Float), # 支付其他与经营活动有关的现金 Column("st_cash_out_act", Float), # 经营活动现金流出小计 Column("n_cashflow_act", Float), # 经营活动产生的现金流量净额 Column("oth_recp_ral_inv_act", Float), # 收到其他与投资活动有关的现金 Column("c_disp_withdrwl_invest", Float), # 收回投资收到的现金 Column("c_recp_return_invest", Float), # 取得投资收益收到的现金 Column("n_recp_disp_fiolta", Float), # 处置固定资产、无形资产和其他长期资产收回的现金净额 Column("n_recp_disp_sobu", Float), # 处置子公司及其他营业单位收到的现金净额 Column("stot_inflows_inv_act", Float), # 投资活动现金流入小计 Column("c_pay_acq_const_fiolta", Float), # 购建固定资产、无形资产和其他长期资产支付的现金 Column("c_paid_invest", Float), # 投资支付的现金 Column("n_disp_subs_oth_biz", Float), # 取得子公司及其他营业单位支付的现金净额 Column("oth_pay_ral_inv_act", Float), # 支付其他与投资活动有关的现金 Column("n_incr_pledge_loan", Float), # 质押贷款净增加额 Column("stot_out_inv_act", Float), # 投资活动现金流出小计 Column("n_cashflow_inv_act", Float), # 投资活动产生的现金流量净额 Column("c_recp_borrow", Float), # 取得借款收到的现金 Column("proc_issue_bonds", Float), # 发行债券收到的现金 Column("oth_cash_recp_ral_fnc_act", Float), # 收到其他与筹资活动有关的现金 Column("stot_cash_in_fnc_act", Float), # 筹资活动现金流入小计 Column("free_cashflow", Float), # 企业自由现金流量 Column("c_prepay_amt_borr", Float), # 偿还债务支付的现金 Column("c_pay_dist_dpcp_int_exp", Float), # 分配股利、利润或偿付利息支付的现金 Column("incl_dvd_profit_paid_sc_ms", Float), # 其中:子公司支付给少数股东的股利、利润 Column("oth_cashpay_ral_fnc_act", Float), # 支付其他与筹资活动有关的现金 Column("stot_cashout_fnc_act", Float), # 筹资活动现金流出小计 Column("n_cash_flows_fnc_act", Float), # 筹资活动产生的现金流量净额 Column("eff_fx_flu_cash", Float), # 汇率变动对现金的影响 Column("n_incr_cash_cash_equ", Float), # 现金及现金等价物净增加额 Column("c_cash_equ_beg_period", Float), # 期初现金及现金等价物余额 Column("c_cash_equ_end_period", Float), # 期末现金及现金等价物余额 Column("c_recp_cap_contrib", Float), # 吸收投资收到的现金 Column("incl_cash_rec_saims", Float), # 其中:子公司吸收少数股东投资收到的现金 Column("uncon_invest_loss", Float), # 未确认投资损失 Column("prov_depr_assets", Float), # 加:资产减值准备 Column("depr_fa_coga_dpba", Float), # 固定资产折旧、油气资产折耗、生产性生物资产折旧 Column("amort_intang_assets", Float), # 无形资产摊销 Column("lt_amort_deferred_exp", Float), # 长期待摊费用摊销 Column("decr_deferred_exp", Float), # 待摊费用减少 Column("incr_acc_exp", Float), # 预提费用增加 Column("loss_disp_fiolta", Float), # 处置固定、无形资产和其他长期资产的损失 Column("loss_scr_fa", Float), # 固定资产报废损失 Column("loss_fv_chg", Float), # 公允价值变动损失 Column("invest_loss", Float), # 投资损失 Column("decr_def_inc_tax_assets", Float), # 递延所得税资产减少 Column("incr_def_inc_tax_liab", Float), # 递延所得税负债增加 Column("decr_inventories", Float), # 存货的减少 Column("decr_oper_payable", Float), # 经营性应收项目的减少 Column("incr_oper_payable", Float), # 经营性应付项目的增加 Column("others", Float), # 其他 Column("im_net_cashflow_oper_act", Float), # 经营活动产生的现金流量净额(间接法) Column("conv_debt_into_cap", Float), # 债务转为资本 Column("conv_copbonds_due_within_1y", Float), # 一年内到期的可转换公司债券 Column("fa_fnc_leases", Float), # 融资租入固定资产 Column("end_bal_cash", Float), # 现金的期末余额 Column("beg_bal_cash", Float), # 减:现金的期初余额 Column("end_bal_cash_equ", Float), # 加:现金等价物的期末余额 Column("beg_bal_cash_equ", Float), # 减:现金等价物的期初余额 Column("im_n_incr_cash_equ", Float), # 现金及现金等价物净增加额(间接法) Column("update_flag", Float) # 更新标识 )
def update_moneyflow_date(engine, pro, date, retry_count, pause): """日期方式更新 日线行情""" df = get_moneyflow_date(pro, date, retry_count, pause) df.to_sql(dbUtil.getTableName(int(date[0:4]), "moneyflow"), engine, if_exists='append', index=False)