def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
engine = sqlEngine()
columns = ['COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id', 'trade_date']
ttm_cash_flow = get_fin_consolidated_statements_pit(FinCashFlowTTM,
[FinCashFlowTTM.net_operate_cash_flow,
# 经营活动现金流量净额
FinCashFlowTTM.cash_and_equivalents_at_end,
# 期末现金及现金等价物余额
], dates=[trade_date])
for column in columns:
if column in list(ttm_cash_flow.keys()):
ttm_cash_flow = ttm_cash_flow.drop(column, axis=1)
ttm_income = get_fin_consolidated_statements_pit(FinIncomeTTM,
[FinIncomeTTM.operating_cost, # 营业成本
FinIncomeTTM.operating_revenue, # 营业收入
FinIncomeTTM.total_operating_revenue, # 营业总收入
], dates=[trade_date])
for column in columns:
if column in list(ttm_income.keys()):
ttm_income = ttm_income.drop(column, axis=1)
ttm_balance = get_fin_consolidated_statements_pit(FinBalanceTTM,
[FinBalanceTTM.account_receivable, # 应收账款
FinBalanceTTM.bill_receivable, # 应收票据
FinBalanceTTM.advance_payment, # 预付款项
FinBalanceTTM.inventories, # 存货
FinBalanceTTM.total_current_assets, # 流动资产合计
FinBalanceTTM.fixed_assets_net, # 固定资产
FinBalanceTTM.construction_materials, # 工程物资
FinBalanceTTM.constru_in_process, # 在建工程
FinBalanceTTM.total_assets, # 资产总计
FinBalanceTTM.advance_peceipts, # 预收款项
FinBalanceTTM.accounts_payable, # 应付账款
FinBalanceTTM.notes_payable, # 应付票据
FinBalanceTTM.total_owner_equities, # 所有者权益(或股东权益)合计
], dates=[trade_date])
for column in columns:
if column in list(ttm_balance.keys()):
ttm_balance = ttm_balance.drop(column, axis=1)
ttm_operation_capacity = pd.merge(ttm_cash_flow, ttm_income, on='security_code')
ttm_operation_capacity = pd.merge(ttm_balance, ttm_operation_capacity, on='security_code')
return ttm_operation_capacity
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
engine = sqlEngine()
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceMRQ,
[
BalanceMRQ.TOTALNONCASSETS,
BalanceMRQ.TOTASSET,
BalanceMRQ.TOTALNONCLIAB,
BalanceMRQ.LONGBORR,
BalanceMRQ.INTAASSET,
# BalanceMRQ.DEVEEXPE,
BalanceMRQ.GOODWILL,
BalanceMRQ.FIXEDASSENET,
BalanceMRQ.ENGIMATE,
BalanceMRQ.CONSPROG,
BalanceMRQ.RIGHAGGR,
BalanceMRQ.TOTCURRASSET,
],
dates=[trade_date])
for col in columns:
if col in list(balance_sets.keys()):
balance_sets = balance_sets.drop(col, axis=1)
balance_sets = balance_sets.rename(
columns={
'TOTALNONCASSETS': 'total_non_current_assets', # 非流动资产合计
'TOTASSET': 'total_assets', # 资产总计
'TOTALNONCLIAB': 'total_non_current_liability', # 非流动负债合计
'LONGBORR': 'longterm_loan', # 长期借款
'INTAASSET': 'intangible_assets', # 无形资产
# 'DEVEEXPE': 'development_expenditure', # 开发支出
'GOODWILL': 'good_will', # 商誉
'FIXEDASSENET': 'fixed_assets', # 固定资产
'ENGIMATE': 'construction_materials', # 工程物资
'CONSPROG': 'constru_in_process', # 在建工程
'RIGHAGGR': 'total_owner_equities', # 股东权益合计
'TOTCURRASSET': 'total_current_assets', # 流动资产合计
})
return balance_sets
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
trade_date_pre_year = self.get_trade_date(trade_date, 1)
trade_date_pre_year_2 = self.get_trade_date(trade_date, 2)
trade_date_pre_year_3 = self.get_trade_date(trade_date, 3)
trade_date_pre_year_4 = self.get_trade_date(trade_date, 4)
trade_date_pre_year_5 = self.get_trade_date(trade_date, 5)
engine = sqlEngine()
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
# report data
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport,
[
BalanceReport.TOTASSET, # 总资产（资产合计）
BalanceReport.RIGHAGGR, # 股东权益合计
],
dates=[trade_date])
if len(balance_sets) <= 0 or balance_sets is None:
balance_sets = pd.DataFrame({
'security_code': [],
'TOTASSET': [],
'RIGHAGGR': []
})
for column in columns:
if column in list(balance_sets.keys()):
balance_sets = balance_sets.drop(column, axis=1)
balance_sets = balance_sets.rename(
columns={
'TOTASSET': 'total_assets', # 资产总计
'RIGHAGGR': 'total_owner_equities', # 股东权益合计
})
balance_sets_pre_year = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport,
[
BalanceReport.TOTASSET, # 总资产（资产合计）
BalanceReport.RIGHAGGR, # 股东权益合计
],
dates=[trade_date_pre_year])
if len(balance_sets_pre_year) <= 0 or balance_sets_pre_year is None:
balance_sets_pre_year = pd.DataFrame({
'security_code': [],
'TOTASSET': [],
'RIGHAGGR': []
})
for column in columns:
if column in list(balance_sets_pre_year.keys()):
balance_sets_pre_year = balance_sets_pre_year.drop(column,
axis=1)
balance_sets_pre_year = balance_sets_pre_year.rename(
columns={
"TOTASSET": "total_assets_pre_year",
"RIGHAGGR": "total_owner_equities_pre_year"
})
balance_sets = pd.merge(balance_sets,
balance_sets_pre_year,
on='security_code')
# print('get_balabce_sets')
# ttm 计算
ttm_factor_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM,
[
IncomeTTM.BIZINCO, # 营业收入
IncomeTTM.PERPROFIT, # 营业利润
IncomeTTM.TOTPROFIT, # 利润总额
IncomeTTM.NETPROFIT, # 净利润
IncomeTTM.BIZCOST, # 营业成本
IncomeTTM.PARENETP
], # 归属于母公司所有者的净利润
dates=[trade_date])
if len(ttm_factor_sets) <= 0 or ttm_factor_sets is None:
ttm_factor_sets = pd.DataFrame({
'security_code': [],
'BIZINCO': [],
'PERPROFIT': [],
'TOTPROFIT': [],
'NETPROFIT': [],
'BIZCOST': [],
'PARENETP': []
})
for column in columns:
if column in list(ttm_factor_sets.keys()):
ttm_factor_sets = ttm_factor_sets.drop(column, axis=1)
ttm_cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM,
[
CashFlowTTM.FINNETCFLOW, # 筹资活动产生的现金流量净额
CashFlowTTM.MANANETR, # 经营活动产生的现金流量净额
CashFlowTTM.INVNETCASHFLOW, # 投资活动产生的现金流量净额
CashFlowTTM.CASHNETI, # 现金及现金等价物的净增加额
],
dates=[trade_date])
if len(ttm_cash_flow_sets) <= 0 or ttm_cash_flow_sets is None:
ttm_cash_flow_sets = pd.DataFrame({
'security_code': [],
'FINNETCFLOW': [],
'MANANETR': [],
'INVNETCASHFLOW': [],
'CASHNETI': []
})
for column in columns:
if column in list(ttm_cash_flow_sets.keys()):
ttm_cash_flow_sets = ttm_cash_flow_sets.drop(column, axis=1)
ttm_indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorTTM, [
IndicatorTTM.NPCUT,
], dates=[trade_date])
if len(ttm_indicator_sets) <= 0 or ttm_indicator_sets is None:
ttm_indicator_sets = pd.DataFrame({
'security_code': [],
'NPCUT': []
})
for column in columns:
if column in list(ttm_indicator_sets.keys()):
ttm_indicator_sets = ttm_indicator_sets.drop(column, axis=1)
ttm_indicator_sets = ttm_indicator_sets.rename(
columns={'NPCUT': 'ni_attr_p_cut'})
# field_key = ttm_cash_flow_sets.keys()
# for i in field_key:
# ttm_factor_sets[i] = ttm_cash_flow_sets[i]
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_cash_flow_sets,
how='outer',
on='security_code')
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_indicator_sets,
how='outer',
on='security_code')
ttm_factor_sets = ttm_factor_sets.rename(
columns={
"BIZINCO": "operating_revenue",
"PERPROFIT": "operating_profit",
"TOTPROFIT": "total_profit",
"NETPROFIT": "net_profit",
"BIZCOST": "operating_cost",
"PARENETP": "np_parent_company_owners",
"FINNETCFLOW": "net_finance_cash_flow",
"MANANETR": "net_operate_cash_flow",
"INVNETCASHFLOW": "net_invest_cash_flow",
'CASHNETI': 'n_change_in_cash'
})
ttm_income_sets_pre = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM,
[
IncomeTTM.BIZINCO, # 营业收入
IncomeTTM.PERPROFIT, # 营业利润
IncomeTTM.TOTPROFIT, # 利润总额
IncomeTTM.NETPROFIT, # 净利润
IncomeTTM.BIZCOST, # 营业成本
IncomeTTM.PARENETP # 归属于母公司所有者的净利润
],
dates=[trade_date_pre_year])
if len(ttm_income_sets_pre) <= 0 or ttm_income_sets_pre is None:
ttm_income_sets_pre = pd.DataFrame({
'security_code': [],
'BIZINCO': [],
'PERPROFIT': [],
'TOTPROFIT': [],
'NETPROFIT': [],
'BIZCOST': [],
'PARENETP': []
})
for column in columns:
if column in list(ttm_income_sets_pre.keys()):
ttm_income_sets_pre = ttm_income_sets_pre.drop(column, axis=1)
ttm_factor_sets_pre = ttm_income_sets_pre.rename(
columns={
"BIZINCO": "operating_revenue_pre_year",
"PERPROFIT": "operating_profit_pre_year",
"TOTPROFIT": "total_profit_pre_year",
"NETPROFIT": "net_profit_pre_year",
"BIZCOST": "operating_cost_pre_year",
"PARENETP": "np_parent_company_owners_pre_year",
})
ttm_indicator_sets_pre = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorTTM, [
IndicatorTTM.NPCUT,
], dates=[trade_date_pre_year])
if len(ttm_indicator_sets_pre) <= 0 or ttm_indicator_sets_pre is None:
ttm_indicator_sets_pre = pd.DataFrame({
'security_code': [],
'NPCUT': []
})
for column in columns:
if column in list(ttm_indicator_sets_pre.keys()):
ttm_indicator_sets_pre = ttm_indicator_sets_pre.drop(column,
axis=1)
ttm_indicator_sets_pre = ttm_indicator_sets_pre.rename(
columns={'NPCUT': 'ni_attr_p_cut_pre'})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_factor_sets_pre,
how='outer',
on='security_code')
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_indicator_sets_pre,
how='outer',
on='security_code')
ttm_cash_flow_sets_pre = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM,
[
CashFlowTTM.FINNETCFLOW,
# 筹资活动产生的现金流量净额
CashFlowTTM.MANANETR, # 经营活动产生的现金流量净额
CashFlowTTM.INVNETCASHFLOW,
# 投资活动产生的现金流量净额
CashFlowTTM.CASHNETI, # 现金及现金等价物的净增加额
],
dates=[trade_date_pre_year])
if len(ttm_cash_flow_sets_pre) <= 0 or ttm_cash_flow_sets_pre is None:
ttm_cash_flow_sets_pre = pd.DataFrame({
'security_code': [],
'FINNETCFLOW': [],
'MANANETR': [],
'INVNETCASHFLOW': [],
'CASHNETI': []
})
for column in columns:
if column in list(ttm_cash_flow_sets_pre.keys()):
ttm_cash_flow_sets_pre = ttm_cash_flow_sets_pre.drop(column,
axis=1)
ttm_cash_flow_sets_pre = ttm_cash_flow_sets_pre.rename(
columns={
"FINNETCFLOW": "net_finance_cash_flow_pre_year",
"MANANETR": "net_operate_cash_flow_pre_year",
"INVNETCASHFLOW": "net_invest_cash_flow_pre_year",
'CASHNETI': 'n_change_in_cash_pre_year',
})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_cash_flow_sets_pre,
how='outer',
on='security_code')
# print('get_ttm_factor_sets_pre')
# ttm 连续
ttm_factor_sets_pre_year_2 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
IncomeTTM.BIZINCO,
IncomeTTM.BIZCOST,
],
dates=[trade_date_pre_year_2])
if len(ttm_factor_sets_pre_year_2
) <= 0 or ttm_factor_sets_pre_year_2 is None:
ttm_factor_sets_pre_year_2 = pd.DataFrame({
'security_code': [],
'NETPROFIT': [],
'BIZINCO': [],
'BIZCOST': []
})
for column in columns:
if column in list(ttm_factor_sets_pre_year_2.keys()):
ttm_factor_sets_pre_year_2 = ttm_factor_sets_pre_year_2.drop(
column, axis=1)
ttm_factor_sets_pre_year_2 = ttm_factor_sets_pre_year_2.rename(
columns={
"BIZINCO": "operating_revenue_pre_year_2",
"BIZCOST": "operating_cost_pre_year_2",
"NETPROFIT": "net_profit_pre_year_2",
})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_factor_sets_pre_year_2,
how='outer',
on="security_code")
# field_key = ttm_factor_sets_pre_year_2.keys()
# for i in field_key:
# ttm_factor_sets[i] = ttm_factor_sets_pre_year_2[i]
# print('get_ttm_factor_sets_2')
ttm_factor_sets_pre_year_3 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
IncomeTTM.BIZINCO,
IncomeTTM.BIZCOST,
],
dates=[trade_date_pre_year_3])
if len(ttm_factor_sets_pre_year_3
) <= 0 or ttm_factor_sets_pre_year_3 is None:
ttm_factor_sets_pre_year_3 = pd.DataFrame({
'security_code': [],
'NETPROFIT': [],
'BIZINCO': [],
'BIZCOST': []
})
for column in columns:
if column in list(ttm_factor_sets_pre_year_3.keys()):
ttm_factor_sets_pre_year_3 = ttm_factor_sets_pre_year_3.drop(
column, axis=1)
ttm_factor_sets_pre_year_3 = ttm_factor_sets_pre_year_3.rename(
columns={
"BIZINCO": "operating_revenue_pre_year_3",
"BIZCOST": "operating_cost_pre_year_3",
"NETPROFIT": "net_profit_pre_year_3",
})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_factor_sets_pre_year_3,
how='outer',
on="security_code")
# field_key = ttm_factor_sets_pre_year_3.keys()
# for i in field_key:
# ttm_factor_sets[i] = ttm_factor_sets_pre_year_3[i]
# print('get_ttm_factor_sets_3')
ttm_factor_sets_pre_year_4 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
IncomeTTM.BIZINCO,
IncomeTTM.BIZCOST,
],
dates=[trade_date_pre_year_4])
if len(ttm_factor_sets_pre_year_4
) <= 0 or ttm_factor_sets_pre_year_4 is None:
ttm_factor_sets_pre_year_4 = pd.DataFrame({
'security_code': [],
'NETPROFIT': [],
'BIZINCO': [],
'BIZCOST': []
})
for column in columns:
if column in list(ttm_factor_sets_pre_year_4.keys()):
ttm_factor_sets_pre_year_4 = ttm_factor_sets_pre_year_4.drop(
column, axis=1)
ttm_factor_sets_pre_year_4 = ttm_factor_sets_pre_year_4.rename(
columns={
"BIZINCO": "operating_revenue_pre_year_4",
"BIZCOST": "operating_cost_pre_year_4",
"NETPROFIT": "net_profit_pre_year_4",
})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_factor_sets_pre_year_4,
how='outer',
on="security_code")
# field_key = ttm_factor_sets_pre_year_4.keys()
# for i in field_key:
# ttm_factor_sets[i] = ttm_factor_sets_pre_year_4[i]
# print('get_ttm_factor_sets_4')
ttm_factor_sets_pre_year_5 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
IncomeTTM.BIZINCO,
IncomeTTM.BIZCOST,
],
dates=[trade_date_pre_year_5])
if len(ttm_factor_sets_pre_year_5
) <= 0 or ttm_factor_sets_pre_year_5 is None:
ttm_factor_sets_pre_year_5 = pd.DataFrame({
'security_code': [],
'NETPROFIT': [],
'BIZINCO': [],
'BIZCOST': []
})
for column in columns:
if column in list(ttm_factor_sets_pre_year_5.keys()):
ttm_factor_sets_pre_year_5 = ttm_factor_sets_pre_year_5.drop(
column, axis=1)
ttm_factor_sets_pre_year_5 = ttm_factor_sets_pre_year_5.rename(
columns={
"BIZINCO": "operating_revenue_pre_year_5",
"BIZCOST": "operating_cost_pre_year_5",
"NETPROFIT": "net_profit_pre_year_5",
})
ttm_factor_sets = pd.merge(ttm_factor_sets,
ttm_factor_sets_pre_year_5,
how='outer',
on="security_code")
# field_key = ttm_factor_sets_pre_year_5.keys()
# for i in field_key:
# ttm_factor_sets[i] = ttm_factor_sets_pre_year_5[i]
# print('get_ttm_factor_sets_5')
growth_sets = pd.merge(ttm_factor_sets,
balance_sets,
how='outer',
on='security_code')
return growth_sets
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
columns = ['COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id', 'trade_date']
engine = sqlEngine()
cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(CashFlowMRQ,
[CashFlowMRQ.FINALCASHBALA,
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_sets.keys()):
cash_flow_sets = cash_flow_sets.drop(col, axis=1)
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(BalanceMRQ,
[BalanceMRQ.SHORTTERMBORR,
BalanceMRQ.DUENONCLIAB,
BalanceMRQ.LONGBORR,
BalanceMRQ.BDSPAYA,
BalanceMRQ.INTEPAYA,
BalanceMRQ.PARESHARRIGH,
BalanceMRQ.TOTASSET,
BalanceMRQ.FIXEDASSECLEATOT,
BalanceMRQ.TOTLIAB,
BalanceMRQ.RIGHAGGR, # 股东权益合计
BalanceMRQ.INTAASSET,
# BalanceMRQ.DEVEEXPE,
BalanceMRQ.GOODWILL,
BalanceMRQ.LOGPREPEXPE,
BalanceMRQ.DEFETAXASSET,
BalanceMRQ.MINYSHARRIGH,
BalanceMRQ.TOTCURRASSET, # 流动资产合计
BalanceMRQ.TOTALCURRLIAB, # 流动负债合计
BalanceMRQ.RESE, # 盈余公积
BalanceMRQ.UNDIPROF, # 未分配利润
BalanceMRQ.CURFDS, # 货币资金
BalanceMRQ.ACCOPAYA, # 应付帐款
BalanceMRQ.ADVAPAYM, # 预收款项
BalanceMRQ.NOTESPAYA, # 应付票据
BalanceMRQ.INTEPAYA, # 应付利息
BalanceMRQ.TOTALNONCLIAB, # 非流动负债合计
], dates=[trade_date])
for col in columns:
if col in list(balance_sets.keys()):
balance_sets = balance_sets.drop(col, axis=1)
balance_sets = balance_sets.rename(columns={
'SHORTTERMBORR': 'shortterm_loan', # 短期借款
'DUENONCLIAB': 'non_current_liability_in_one_year', # 一年内到期的非流动负债
'LONGBORR': 'longterm_loan', # 长期借款
'BDSPAYA': 'bonds_payable', # 应付债券
'INTEPAYA': 'interest_payable', # 应付利息
})
# indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(IndicatorMRQ,
# [IndicatorMRQ.FCFF,
# IndicatorMRQ.FCFE,
# IndicatorMRQ.NEGAL,
# IndicatorMRQ.NOPI,
# IndicatorMRQ.WORKCAP,
# IndicatorMRQ.RETAINEDEAR,
# IndicatorMRQ.NDEBT,
# IndicatorMRQ.NONINTCURLIABS,
# IndicatorMRQ.NONINTNONCURLIAB,
# # IndicatorMRQ.CURDEPANDAMOR,
# IndicatorMRQ.TOTIC,
# IndicatorMRQ.EBIT,
# ], dates=[trade_date])
# for col in columns:
# if col in list(indicator_sets.keys()):
# indicator_sets = indicator_sets.drop(col, axis=1)
income_sets = engine.fetch_fundamentals_pit_extend_company_id(IncomeMRQ,
[IncomeMRQ.INCOTAXEXPE,
IncomeMRQ.BIZTOTCOST, # 营业总成本
IncomeMRQ.BIZTOTINCO, # 营业总收入
], dates=[trade_date])
for col in columns:
if col in list(income_sets.keys()):
income_sets = income_sets.drop(col, axis=1)
tp_detivation = pd.merge(cash_flow_sets, balance_sets, how='outer', on='security_code')
# tp_detivation = pd.merge(indicator_sets, tp_detivation, how='outer', on='security_code')
tp_detivation = pd.merge(income_sets, tp_detivation, how='outer', on='security_code')
# balance_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(BalanceTTM,
# [BalanceTTM.MINYSHARRIGH,
# ], dates=[trade_date]).drop(columns, axis=1)
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(IncomeTTM,
[IncomeTTM.BIZTOTINCO,
IncomeTTM.BIZTOTCOST,
IncomeTTM.BIZINCO,
IncomeTTM.SALESEXPE,
IncomeTTM.MANAEXPE,
IncomeTTM.FINEXPE,
# IncomeTTM.INTEEXPE,
IncomeTTM.ASSEIMPALOSS,
IncomeTTM.PERPROFIT,
IncomeTTM.TOTPROFIT,
IncomeTTM.NETPROFIT,
IncomeTTM.PARENETP,
IncomeTTM.BIZTAX,
IncomeTTM.NONOREVE,
IncomeTTM.NONOEXPE,
IncomeTTM.MINYSHARRIGH,
IncomeTTM.INCOTAXEXPE,
], dates=[trade_date])
for col in columns:
if col in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(col, axis=1)
income_ttm_sets = income_ttm_sets.rename(columns={'MINYSHARRIGH': 'minority_profit'})
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(CashFlowTTM,
[CashFlowTTM.MANANETR,
CashFlowTTM.LABORGETCASH,
CashFlowTTM.INVNETCASHFLOW,
CashFlowTTM.FINNETCFLOW,
CashFlowTTM.CASHNETI,
# CashFlowTTM.INTEEXPE
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(col, axis=1)
# indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(IndicatorTTM,
# [IndicatorTTM.OPGPMARGIN,
# IndicatorTTM.NPCUT,
# IndicatorTTM.NVALCHGIT,
# IndicatorTTM.EBITDA,
# IndicatorTTM.EBIT,
# IndicatorTTM.EBITFORP,
# ], dates=[trade_date])
# for col in columns:
# if col in list(indicator_ttm_sets.keys()):
# indicator_ttm_sets = indicator_ttm_sets.drop(col, axis=1)
ttm_derivation = pd.merge(income_ttm_sets, cash_flow_ttm_sets, how='outer', on='security_code')
# ttm_derivation = pd.merge(indicator_ttm_sets, ttm_derivation, how='outer', on='security_code')
return tp_detivation, ttm_derivation
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
engine = sqlEngine()
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
ttm_cash_flow = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM, [
CashFlowTTM.MANANETR,
CashFlowTTM.FINALCASHBALA,
],
dates=[trade_date])
for column in columns:
if column in list(ttm_cash_flow.keys()):
ttm_cash_flow = ttm_cash_flow.drop(column, axis=1)
ttm_cash_flow = ttm_cash_flow.rename(
columns={
'MANANETR': 'net_operate_cash_flow', # 经营活动现金流量净额
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
ttm_income = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.BIZCOST,
IncomeTTM.BIZINCO,
IncomeTTM.BIZTOTINCO,
],
dates=[trade_date])
for column in columns:
if column in list(ttm_income.keys()):
ttm_income = ttm_income.drop(column, axis=1)
ttm_income = ttm_income.rename(
columns={
'BIZCOST': 'operating_cost', # 营业成本
'BIZINCO': 'operating_revenue', # 营业收入
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
})
ttm_balance = engine.fetch_fundamentals_pit_extend_company_id(
BalanceTTM, [
BalanceTTM.ACCORECE,
BalanceTTM.NOTESRECE,
BalanceTTM.PREP,
BalanceTTM.INVE,
BalanceTTM.TOTCURRASSET,
BalanceTTM.FIXEDASSENET,
BalanceTTM.ENGIMATE,
BalanceTTM.CONSPROG,
BalanceTTM.TOTASSET,
BalanceTTM.ADVAPAYM,
BalanceTTM.ACCOPAYA,
BalanceTTM.DERILIAB,
BalanceTTM.RIGHAGGR,
],
dates=[trade_date])
for column in columns:
if column in list(ttm_balance.keys()):
ttm_balance = ttm_balance.drop(column, axis=1)
ttm_balance = ttm_balance.rename(
columns={
'ACCORECE': 'account_receivable', # 应收账款
'NOTESRECE': 'bill_receivable', # 应收票据
'PREP': 'advance_payment', # 预付款项
'INVE': 'inventories', # 存货
'TOTCURRASSET': 'total_current_assets', # 流动资产合计
'FIXEDASSENET': 'fixed_assets', # 固定资产
'ENGIMATE': 'construction_materials', # 工程物资
'CONSPROG': 'constru_in_process', # 在建工程
'TOTASSET': 'total_assets', # 资产总计
'ADVAPAYM': 'advance_peceipts', # 预收款项
'ACCOPAYA': 'accounts_payable', # 应付账款
'DERILIAB': 'notes_payable', # 应付票据
'RIGHAGGR': 'total_owner_equities',
})
ttm_operation_capacity = pd.merge(ttm_cash_flow,
ttm_income,
on='security_code')
ttm_operation_capacity = pd.merge(ttm_balance,
ttm_operation_capacity,
on='security_code')
return ttm_operation_capacity
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
trade_date_pre_year = self.get_trade_date(trade_date, 1)
trade_date_pre_year_2 = self.get_trade_date(trade_date, 2)
trade_date_pre_year_3 = self.get_trade_date(trade_date, 3)
trade_date_pre_year_4 = self.get_trade_date(trade_date, 4)
trade_date_pre_year_5 = self.get_trade_date(trade_date, 5)
engine = sqlEngine()
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
# Report Data
cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowReport, [
CashFlowReport.LABORGETCASH,
CashFlowReport.FINALCASHBALA,
],
dates=[trade_date])
for column in columns:
if column in list(cash_flow_sets.keys()):
cash_flow_sets = cash_flow_sets.drop(column, axis=1)
cash_flow_sets = cash_flow_sets.rename(
columns={
'LABORGETCASH':
'goods_sale_and_service_render_cash', # 销售商品、提供劳务收到的现金
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
income_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.BIZTOTINCO,
IncomeReport.BIZINCO,
IncomeReport.PERPROFIT,
IncomeReport.PARENETP,
IncomeReport.NETPROFIT,
],
dates=[trade_date])
for column in columns:
if column in list(income_sets.keys()):
income_sets = income_sets.drop(column, axis=1)
income_sets = income_sets.rename(
columns={
'NETPROFIT': 'net_profit', # 净利润
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
'BIZINCO': 'operating_revenue', # 营业收入
'PERPROFIT': 'operating_profit', # 营业利润
'PARENETP': 'np_parent_company_owners', # 归属于母公司所有者的净利润
})
indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorReport,
[
IndicatorReport.NETPROFITCUT,
# 扣除非经常损益后的净利润
IndicatorReport.MGTEXPRT
],
dates=[trade_date])
for column in columns:
if column in list(indicator_sets.keys()):
indicator_sets = indicator_sets.drop(column, axis=1)
indicator_sets = indicator_sets.rename(
columns={
'NETPROFITCUT': 'adjusted_profit', # 扣除非经常损益后的净利润
})
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport, [
BalanceReport.PARESHARRIGH,
], dates=[trade_date])
for column in columns:
if column in list(balance_sets.keys()):
balance_sets = balance_sets.drop(column, axis=1)
balance_sets = balance_sets.rename(columns={
'PARESHARRIGH':
'equities_parent_company_owners', # 归属于母公司股东权益合计
})
income_sets_pre_year_1 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport,
[
IncomeReport.BIZINCO, # 营业收入
IncomeReport.NETPROFIT, # 净利润
],
dates=[trade_date_pre_year])
for column in columns:
if column in list(income_sets_pre_year_1.keys()):
income_sets_pre_year_1 = income_sets_pre_year_1.drop(column,
axis=1)
income_sets_pre_year_1 = income_sets_pre_year_1.rename(
columns={
'NETPROFIT': 'net_profit_pre_year_1', # 净利润
'BIZINCO': 'operating_revenue_pre_year_1',
# 营业收入
})
income_sets_pre_year_2 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.BIZINCO,
IncomeReport.NETPROFIT,
],
dates=[trade_date_pre_year_2])
for column in columns:
if column in list(income_sets_pre_year_2.keys()):
income_sets_pre_year_2 = income_sets_pre_year_2.drop(column,
axis=1)
income_sets_pre_year_2 = income_sets_pre_year_2.rename(
columns={
'NETPROFIT': 'net_profit_pre_year_2', # 净利润
'BIZINCO': 'operating_revenue_pre_year_2',
# 营业收入
})
income_sets_pre_year_3 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.BIZINCO,
IncomeReport.NETPROFIT,
],
dates=[trade_date_pre_year_3])
for column in columns:
if column in list(income_sets_pre_year_3.keys()):
income_sets_pre_year_3 = income_sets_pre_year_3.drop(column,
axis=1)
income_sets_pre_year_3 = income_sets_pre_year_3.rename(
columns={
'NETPROFIT': 'net_profit_pre_year_3', # 净利润
'BIZINCO': 'operating_revenue_pre_year_3',
# 营业收入
})
income_sets_pre_year_4 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.BIZINCO,
IncomeReport.NETPROFIT,
],
dates=[trade_date_pre_year_4])
for column in columns:
if column in list(income_sets_pre_year_4.keys()):
income_sets_pre_year_4 = income_sets_pre_year_4.drop(column,
axis=1)
income_sets_pre_year_4 = income_sets_pre_year_4.rename(
columns={
'NETPROFIT': 'net_profit_pre_year_4', # 净利润
'BIZINCO': 'operating_revenue_pre_year_4',
# 营业收入
})
tp_earning = pd.merge(cash_flow_sets,
income_sets,
how='outer',
on='security_code')
tp_earning = pd.merge(indicator_sets,
tp_earning,
how='outer',
on='security_code')
tp_earning = pd.merge(balance_sets,
tp_earning,
how='outer',
on='security_code')
tp_earning = pd.merge(income_sets_pre_year_1,
tp_earning,
how='outer',
on='security_code')
tp_earning = pd.merge(income_sets_pre_year_2,
tp_earning,
how='outer',
on='security_code')
tp_earning = pd.merge(income_sets_pre_year_3,
tp_earning,
how='outer',
on='security_code')
tp_earning = pd.merge(income_sets_pre_year_4,
tp_earning,
how='outer',
on='security_code')
# MRQ
balance_mrq_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceMRQ,
[
BalanceMRQ.TOTASSET, # 资产总计
BalanceMRQ.PARESHARRIGH, # 归属于母公司股东权益合计
BalanceMRQ.RIGHAGGR, # 所有者权益（或股东权益）合计
BalanceMRQ.LONGBORR, # 长期借款
],
dates=[trade_date])
for column in columns:
if column in list(balance_mrq_sets.keys()):
balance_mrq_sets = balance_mrq_sets.drop(column, axis=1)
balance_mrq_sets = balance_mrq_sets.rename(
columns={
'TOTASSET': 'total_assets_mrq',
'PARESHARRIGH': 'equities_parent_company_owners_mrq',
# 归属于母公司股东权益合计
'RIGHAGGR': 'total_owner_equities_mrq', # 所有者权益（或股东权益）合计
'LONGBORR': 'longterm_loan_mrq', # 长期借款
})
balance_mrq_sets_pre = engine.fetch_fundamentals_pit_extend_company_id(
BalanceMRQ,
[
BalanceMRQ.TOTASSET, # 资产总计
BalanceMRQ.RIGHAGGR, # 所有者权益(或股东权益)合计
BalanceMRQ.LONGBORR, # 长期借款
],
dates=[trade_date])
for column in columns:
if column in list(balance_mrq_sets_pre.keys()):
balance_mrq_sets_pre = balance_mrq_sets_pre.drop(column,
axis=1)
balance_mrq_sets_pre = balance_mrq_sets_pre.rename(
columns={
'TOTASSET': 'total_assets_mrq_pre',
'RIGHAGGR': 'total_owner_equities_mrq_pre',
# 所有者权益（或股东权益）合计
'LONGBORR': 'longterm_loan_mrq_pre', # 长期借款
})
# TTM Data
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM, [
CashFlowTTM.FINNETCFLOW,
], dates=[trade_date])
for column in columns:
if column in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(column, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(
columns={'FINNETCFLOW': 'net_finance_cash_flow'})
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM,
[
IncomeTTM.BIZINCO, # 营业收入
IncomeTTM.NETPROFIT, # 净利润
IncomeTTM.MANAEXPE, # 管理费用
IncomeTTM.BIZTOTINCO, # 营业总收入
IncomeTTM.TOTPROFIT, # 利润总额
IncomeTTM.FINEXPE, # 财务费用
IncomeTTM.INTEINCO, # 利息收入
IncomeTTM.SALESEXPE, # 销售费用
IncomeTTM.BIZTOTCOST, # 营业总成本
IncomeTTM.PERPROFIT, # 营业利润
IncomeTTM.PARENETP, # 归属于母公司所有者的净利润
IncomeTTM.BIZCOST, # 营业成本
IncomeTTM.ASSOINVEPROF, # 对联营企业和合营企业的投资收益
IncomeTTM.BIZTAX, # 营业税金及附加
IncomeTTM.ASSEIMPALOSS, # 资产减值损失
],
dates=[trade_date])
for column in columns:
if column in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(column, axis=1)
income_ttm_sets = income_ttm_sets.rename(
columns={
'BIZINCO': 'operating_revenue', # 营业收入
'NETPROFIT': 'net_profit', # 净利润
'MANAEXPE': 'administration_expense', # 管理费用
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
'TOTPROFIT': 'total_profit', # 利润总额
'FINEXPE': 'financial_expense', # 财务费用
'INTEINCO': 'interest_income', # 利息收入
'SALESEXPE': 'sale_expense', # 销售费用
'BIZTOTCOST': 'total_operating_cost', # 营业总成本
'PERPROFIT': 'operating_profit', # 营业利润
'PARENETP': 'np_parent_company_owners', # 归属于母公司所有者的净利润
'BIZCOST': 'operating_cost', # 营业成本
'ASSOINVEPROF': 'invest_income_associates', # 对联营企业和合营企业的投资收益
'BIZTAX': 'operating_tax_surcharges', # 营业税金及附加
'ASSEIMPALOSS': 'asset_impairment_loss', # 资产减值损失
})
balance_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceTTM,
[
BalanceTTM.TOTASSET, # 资产总计
BalanceTTM.RIGHAGGR, # 所有者权益（或股东权益）合计
BalanceTTM.PARESHARRIGH, # 归属于母公司股东权益合计
],
dates=[trade_date])
for column in columns:
if column in list(balance_ttm_sets.keys()):
balance_ttm_sets = balance_ttm_sets.drop(column, axis=1)
balance_ttm_sets = balance_ttm_sets.rename(
columns={
'PARESHARRIGH':
'equities_parent_company_owners', # 归属于母公司股东权益合计
'RIGHAGGR': 'total_owner_equities', # 所有者权益（或股东权益）合计
'TOTASSET': 'total_assets', # 资产总计
})
income_ttm_sets_pre_year_1 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.BIZINCO,
IncomeTTM.NETPROFIT,
],
dates=[trade_date_pre_year])
for column in columns:
if column in list(income_ttm_sets_pre_year_1.keys()):
income_ttm_sets_pre_year_1 = income_ttm_sets_pre_year_1.drop(
column, axis=1)
income_ttm_sets_pre_year_1 = income_ttm_sets_pre_year_1.rename(
columns={
'BIZINCO': 'operating_revenue_pre_year_1', # 营业收入
'NETPROFIT': 'net_profit_pre_year_1', # 净利润
})
income_ttm_sets_pre_year_2 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.BIZINCO,
IncomeTTM.NETPROFIT,
],
dates=[trade_date_pre_year_2])
for column in columns:
if column in list(income_ttm_sets_pre_year_2.keys()):
income_ttm_sets_pre_year_2 = income_ttm_sets_pre_year_2.drop(
column, axis=1)
income_ttm_sets_pre_year_2 = income_ttm_sets_pre_year_2.rename(
columns={
'BIZINCO': 'operating_revenue_pre_year_2', # 营业收入
'NETPROFIT': 'net_profit_pre_year_2', # 净利润
})
income_ttm_sets_pre_year_3 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.BIZINCO,
IncomeTTM.NETPROFIT,
],
dates=[trade_date_pre_year_3])
for column in columns:
if column in list(income_ttm_sets_pre_year_3.keys()):
income_ttm_sets_pre_year_3 = income_ttm_sets_pre_year_3.drop(
column, axis=1)
income_ttm_sets_pre_year_3 = income_ttm_sets_pre_year_3.rename(
columns={
'BIZINCO': 'operating_revenue_pre_year_3', # 营业收入
'NETPROFIT': 'net_profit_pre_year_3', # 净利润
})
income_ttm_sets_pre_year_4 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.BIZINCO,
IncomeTTM.NETPROFIT,
],
dates=[trade_date_pre_year_4])
for column in columns:
if column in list(income_ttm_sets_pre_year_4.keys()):
income_ttm_sets_pre_year_4 = income_ttm_sets_pre_year_4.drop(
column, axis=1)
income_ttm_sets_pre_year_4 = income_ttm_sets_pre_year_4.rename(
columns={
'BIZINCO': 'operating_revenue_pre_year_4', # 营业收入
'NETPROFIT': 'net_profit_pre_year_4', # 净利润
})
# indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(IndicatorTTM,
# [IndicatorTTM.ROIC, # 投入资本回报率
# ], dates=[trade_date]).drop(columns, axis=1)
#
# indicator_ttm_sets = indicator_ttm_sets.rename(columns={'ROIC': '',
# })
ttm_earning = pd.merge(income_ttm_sets,
balance_ttm_sets,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
cash_flow_ttm_sets,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
income_ttm_sets_pre_year_1,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
income_ttm_sets_pre_year_2,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
income_ttm_sets_pre_year_3,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
income_ttm_sets_pre_year_4,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
balance_mrq_sets,
how='outer',
on='security_code')
ttm_earning = pd.merge(ttm_earning,
balance_mrq_sets_pre,
how='outer',
on='security_code')
balance_con_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceTTM,
[
BalanceTTM.TOTASSET, # 资产总计
BalanceTTM.RIGHAGGR, # 所有者权益（或股东权益）合计
],
dates=[
trade_date,
trade_date_pre_year,
trade_date_pre_year_2,
trade_date_pre_year_3,
trade_date_pre_year_4,
])
for column in columns:
if column in list(balance_con_sets.keys()):
balance_con_sets = balance_con_sets.drop(column, axis=1)
balance_con_sets = balance_con_sets.groupby(['security_code'])
balance_con_sets = balance_con_sets.sum()
balance_con_sets = balance_con_sets.rename(
columns={
'TOTASSET': 'total_assets',
'RIGHAGGR': 'total_owner_equities'
})
# cash_flow_con_sets = engine.fetch_fundamentals_pit_extend_company_id(CashFlowReport,
# [CashFlowReport.FINALCASHBALA,
# ],
# dates=[trade_date,
# trade_date_pre_year,
# trade_date_pre_year_2,
# trade_date_pre_year_3,
# trade_date_pre_year_4,
# trade_date_pre_year_5,
# ]).drop(columns, axis=1)
# cash_flow_con_sets = cash_flow_con_sets.groupby(['security_code'])
# cash_flow_con_sets = cash_flow_con_sets.sum()
# cash_flow_con_sets = cash_flow_con_sets.rename(columns={'FINALCASHBALA':'cash_and_equivalents_at_end'})
income_con_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.NETPROFIT,
],
dates=[
trade_date,
trade_date_pre_year,
trade_date_pre_year_2,
trade_date_pre_year_3,
trade_date_pre_year_4,
trade_date_pre_year_5,
])
for column in columns:
if column in list(income_con_sets.keys()):
income_con_sets = income_con_sets.drop(column, axis=1)
income_con_sets = income_con_sets.groupby(['security_code'])
income_con_sets = income_con_sets.sum()
income_con_sets = income_con_sets.rename(columns={
'NETPROFIT': 'net_profit'
}).reset_index()
ttm_earning_5y = pd.merge(balance_con_sets,
income_con_sets,
how='outer',
on='security_code')
return tp_earning, ttm_earning, ttm_earning_5y
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
engine = sqlEngine()
columns = ['COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id', 'trade_date']
# MRQ data
balance_mrq_sets = engine.fetch_fundamentals_pit_extend_company_id(BalanceMRQ,
[BalanceMRQ.BDSPAYA,
BalanceMRQ.TOTASSET,
BalanceMRQ.TOTALNONCLIAB,
BalanceMRQ.TOTCURRASSET,
BalanceMRQ.TOTALCURRLIAB,
BalanceMRQ.TOTLIAB,
BalanceMRQ.FIXEDASSENET,
BalanceMRQ.PARESHARRIGH,
BalanceMRQ.SHORTTERMBORR,
BalanceMRQ.DUENONCLIAB,
BalanceMRQ.LONGBORR,
BalanceMRQ.BDSPAYA,
BalanceMRQ.INTEPAYA,
BalanceMRQ.RIGHAGGR,
BalanceMRQ.TOTALNONCASSETS,
BalanceMRQ.INVE,
BalanceMRQ.INTAASSET,
BalanceMRQ.DEVEEXPE,
BalanceMRQ.GOODWILL,
BalanceMRQ.LOGPREPEXPE,
BalanceMRQ.DEFETAXASSET,
BalanceMRQ.CURFDS,
BalanceMRQ.TRADFINASSET,
BalanceMRQ.NOTESRECE,
BalanceMRQ.ACCORECE,
BalanceMRQ.OTHERRECE,
], dates=[trade_date])
for col in columns:
if col in list(balance_mrq_sets.keys()):
balance_mrq_sets = balance_mrq_sets.drop(col, axis=1)
balance_mrq_sets = balance_mrq_sets.rename(columns={
'TOTLIAB': 'total_liability', # 负债合计
'TOTASSET': 'total_assets', # 资产总计
'TOTALCURRLIAB': 'total_current_liability', # 流动负债合计
'TOTCURRASSET': 'total_current_assets', # 流动资产合计
'INVE': 'inventories', # 存货
'CURFDS': 'cash_equivalents', # 货币资金
'TRADFINASSET': 'trading_assets', # 交易性金融资产
'NOTESRECE': 'bill_receivable', # 应收票据
'ACCORECE': 'account_receivable', # 应收账款
'OTHERRECE': 'other_receivable', # 其他应收款
'PARESHARRIGH': 'equities_parent_company_owners', # 归属于母公司股东权益合计
'INTAASSET': 'intangible_assets', # 无形资产
'DEVEEXPE': 'development_expenditure', # 开发支出
'GOODWILL': 'good_will', # 商誉
'LOGPREPEXPE': 'long_deferred_expense', # 长期待摊费用
'DEFETAXASSET': 'deferred_tax_assets', # 递延所得税资产
'DUENONCLIAB': 'non_current_liability_in_one_year', # 一年内到期的非流动负债
'SHORTTERMBORR': 'shortterm_loan', # 短期借款
'LONGBORR': 'longterm_loan', # 长期借款
'BDSPAYA': 'bonds_payable', # 应付债券
'INTEPAYA': 'interest_payable', # 应付利息
'TOTALNONCLIAB': 'total_non_current_liability', # 非流动负债合计
'TOTALNONCASSETS': 'total_non_current_assets', # 非流动资产合计
'FIXEDASSENET': 'fixed_assets', # 固定资产
'RIGHAGGR': 'total_owner_equities', # 所有者权益（或股东权益）合计
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
cash_flow_mrq_sets = engine.fetch_fundamentals_pit_extend_company_id(CashFlowMRQ,
[CashFlowMRQ.MANANETR,
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_mrq_sets.keys()):
cash_flow_mrq_sets = cash_flow_mrq_sets.drop(col, axis=1)
cash_flow_mrq_sets = cash_flow_mrq_sets.rename(columns={'MANANETR': 'net_operate_cash_flow_mrq', # 经营活动现金流量净额
})
mrq_solvency = pd.merge(cash_flow_mrq_sets, balance_mrq_sets, on='security_code')
# ttm data
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(IncomeTTM,
[IncomeTTM.TOTPROFIT,
IncomeTTM.FINEXPE,
IncomeTTM.INTEINCO,
], dates=[trade_date])
for col in columns:
if col in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(col, axis=1)
income_ttm_sets = income_ttm_sets.rename(columns={'TOTPROFIT': 'total_profit', # 利润总额
'FINEXPE': 'financial_expense', # 财务费用
'INTEINCO': 'interest_income', # 利息收入
})
balance_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(BalanceTTM,
[
BalanceTTM.TOTALCURRLIAB,
BalanceTTM.DUENONCLIAB,
], dates=[trade_date])
for col in columns:
if col in list(balance_ttm_sets.keys()):
balance_ttm_sets = balance_ttm_sets.drop(col, axis=1)
balance_ttm_sets = balance_ttm_sets.rename(columns={
'TOTALCURRLIAB': 'total_current_liability_ttm', # 流动负债合计
'DUENONCLIAB': 'non_current_liability_in_one_year_ttm', # 一年内到期的非流动负债
})
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(CashFlowTTM,
[CashFlowTTM.MANANETR, # 经营活动现金流量净额
CashFlowTTM.FINALCASHBALA, # 期末现金及现金等价物余额
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(col, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(columns={
'MANANETR': 'net_operate_cash_flow', # 经营活动现金流量净额
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(IndicatorTTM,
[IndicatorTTM.NDEBT,
], dates=[trade_date])
for col in columns:
if col in list(indicator_ttm_sets.keys()):
indicator_ttm_sets = indicator_ttm_sets.drop(col, axis=1)
indicator_ttm_sets = indicator_ttm_sets.rename(columns={'NDEBT': 'net_liability', # 净负债
})
ttm_solvency = pd.merge(balance_ttm_sets, cash_flow_ttm_sets, how='outer', on="security_code")
ttm_solvency = pd.merge(ttm_solvency, income_ttm_sets, how='outer', on="security_code")
ttm_solvency = pd.merge(ttm_solvency, indicator_ttm_sets, how='outer', on="security_code")
column = ['trade_date']
valuation_sets = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.market_cap, )
.filter(Valuation.trade_date.in_([trade_date])))
for col in column:
if col in list(valuation_sets.keys()):
valuation_sets = valuation_sets.drop(col, axis=1)
tp_solvency = pd.merge(ttm_solvency, valuation_sets, how='outer', on='security_code')
tp_solvency = pd.merge(tp_solvency, mrq_solvency, how='outer', on='security_code')
return tp_solvency
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
engine = sqlEngine()
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
# Report Data
cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowReport, [
CashFlowReport.BIZNETCFLOW,
], dates=[trade_date]).drop(columns, axis=1)
cash_flow_sets = cash_flow_sets.rename(
columns={
'BIZNETCFLOW': 'net_operate_cash_flow', # 经营活动产生的现金流量净额
})
income_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport, [
IncomeReport.TOTPROFIT,
IncomeReport.NONOREVE,
IncomeReport.NONOEXPE,
IncomeReport.BIZTOTCOST,
IncomeReport.BIZTOTINCO,
],
dates=[trade_date])
for col in columns:
if col in list(income_sets.keys()):
income_sets = income_sets.drop(col, axis=1)
income_sets = income_sets.rename(
columns={
'TOTPROFIT': 'total_profit', # 利润总额
'NONOREVE': 'non_operating_revenue', # 营业外收入
'NONOEXPE': 'non_operating_expense', # 营业外支出
'BIZTOTCOST': 'total_operating_cost', # 营业总成本
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
})
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport, [BalanceReport.TOTALCURRLIAB], dates=[trade_date])
for col in columns:
if col in list(balance_sets.keys()):
balance_sets = balance_sets.drop(col, axis=1)
balance_sets = balance_sets.rename(
columns={
'TOTALCURRLIAB': 'total_current_liability', # 流动负债合计
})
tp_revenue_quanlity = pd.merge(cash_flow_sets,
income_sets,
on='security_code')
tp_revenue_quanlity = pd.merge(balance_sets,
tp_revenue_quanlity,
on='security_code')
trade_date_pre_year = self.get_trade_date(trade_date, 1)
trade_date_pre_year_2 = self.get_trade_date(trade_date, 2)
trade_date_pre_year_3 = self.get_trade_date(trade_date, 3)
trade_date_pre_year_4 = self.get_trade_date(trade_date, 4)
income_con_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
],
dates=[
trade_date,
trade_date_pre_year,
trade_date_pre_year_2,
trade_date_pre_year_3,
trade_date_pre_year_4,
])
for col in columns:
if col in list(income_con_sets.keys()):
income_con_sets = income_con_sets.drop(col, axis=1)
income_con_sets = income_con_sets.groupby(['security_code'])
income_con_sets = income_con_sets.sum()
income_con_sets = income_con_sets.rename(
columns={'NETPROFIT': 'net_profit_5'})
# TTM Data
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM, [
CashFlowTTM.BIZNETCFLOW,
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(col, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(
columns={
'BIZNETCFLOW': 'net_operate_cash_flow', # 经营活动产生的现金流量净额
})
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.TOTPROFIT,
IncomeTTM.NONOREVE,
IncomeTTM.NONOEXPE,
IncomeTTM.BIZTOTCOST,
IncomeTTM.BIZTOTINCO,
IncomeTTM.PERPROFIT,
IncomeTTM.NETPROFIT,
IncomeTTM.BIZINCO,
],
dates=[trade_date])
for col in columns:
if col in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(col, axis=1)
income_ttm_sets = income_ttm_sets.rename(
columns={
'TOTPROFIT': 'total_profit', # 利润总额
'NONOREVE': 'non_operating_revenue', # 营业外收入
'NONOEXPE': 'non_operating_expense', # 营业外支出
'BIZTOTCOST': 'total_operating_cost', # 营业总成本
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
'PERPROFIT': 'operating_profit', # 营业利润
'NETPROFIT': 'net_profit', # 净利润
'BIZINCO': 'operating_revenue', # 营业收入
})
balance_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceTTM, [BalanceTTM.TOTALCURRLIAB], dates=[trade_date])
for col in columns:
if col in list(balance_ttm_sets.keys()):
balance_ttm_sets = balance_ttm_sets.drop(col, axis=1)
balance_ttm_sets = balance_ttm_sets.rename(
columns={
'TOTALCURRLIAB': 'total_current_liability', # 流动负债合计
})
column = ['trade_date']
valuation_sets = get_fundamentals(
query(
Valuation.security_code,
Valuation.trade_date,
Valuation.market_cap,
).filter(Valuation.trade_date.in_([trade_date])))
for col in column:
if col in list(valuation_sets.keys()):
valuation_sets = valuation_sets.drop(col, axis=1)
indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorTTM, [
IndicatorTTM.NVALCHGITOTP,
], dates=[trade_date])
for col in columns:
if col in list(indicator_ttm_sets.keys()):
indicator_ttm_sets = indicator_ttm_sets.drop(col, axis=1)
ttm_revenue_quanlity = pd.merge(cash_flow_ttm_sets,
income_ttm_sets,
on='security_code')
ttm_revenue_quanlity = pd.merge(balance_ttm_sets,
ttm_revenue_quanlity,
on='security_code')
ttm_revenue_quanlity = pd.merge(valuation_sets,
ttm_revenue_quanlity,
on='security_code')
ttm_revenue_quanlity = pd.merge(indicator_ttm_sets,
ttm_revenue_quanlity,
on='security_code')
ttm_revenue_quanlity = pd.merge(income_con_sets,
ttm_revenue_quanlity,
on='security_code')
valuation_con_sets = get_fundamentals(
query(
Valuation.security_code,
Valuation.trade_date,
Valuation.market_cap,
Valuation.circulating_market_cap,
).filter(
Valuation.trade_date.in_([
trade_date, trade_date_pre_year, trade_date_pre_year_2,
trade_date_pre_year_3, trade_date_pre_year_4
])))
for col in column:
if col in list(valuation_con_sets.keys()):
valuation_con_sets = valuation_con_sets.drop(col, axis=1)
valuation_con_sets = valuation_con_sets.groupby(['security_code'])
valuation_con_sets = valuation_con_sets.sum()
valuation_con_sets = valuation_con_sets.rename(
columns={
'market_cap': 'market_cap_5',
'circulating_market_cap': 'circulating_market_cap_5'
})
# tp_revenue_quanlity = pd.merge(valuation_con_sets, tp_revenue_quanlity, on='security_code')
ttm_revenue_quanlity = pd.merge(valuation_con_sets,
ttm_revenue_quanlity,
on='security_code')
return tp_revenue_quanlity, ttm_revenue_quanlity
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
# 转换时间格式
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
# 读取目前涉及到的因子
engine = sqlEngine()
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
# Report data
cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowReport,
[
CashFlowReport.FINALCASHBALA, # 期末现金及现金等价物余额
],
dates=[trade_date])
for col in columns:
if col in list(cash_flow_sets.keys()):
cash_flow_sets = cash_flow_sets.drop(col, axis=1)
cash_flow_sets = cash_flow_sets.rename(
columns={
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
income_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeReport,
[
IncomeReport.BIZINCO, # 营业收入
IncomeReport.BIZTOTINCO, # 营业总收入
IncomeReport.PERPROFIT, # 营业利润
IncomeReport.DILUTEDEPS, # 稀释每股收益
],
dates=[trade_date])
for col in columns:
if col in list(income_sets.keys()):
income_sets = income_sets.drop(col, axis=1)
income_sets = income_sets.rename(
columns={
'BIZINCO': 'operating_revenue', # 营业收入
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
'PERPROFIT': 'operating_profit', # 营业利润
'DILUTEDEPS': 'diluted_eps', # 稀释每股收益
})
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport,
[
BalanceReport.PARESHARRIGH, # 归属于母公司的所有者权益
BalanceReport.CAPISURP,
BalanceReport.RESE,
BalanceReport.UNDIPROF,
],
dates=[trade_date])
for col in columns:
if col in list(balance_sets.keys()):
balance_sets = balance_sets.drop(col, axis=1)
balance_sets = balance_sets.rename(
columns={
'PARESHARRIGH': 'total_owner_equities', # 归属于母公司的所有者权益
'CAPISURP': 'capital_reserve_fund', # 资本公积
'RESE': 'surplus_reserve_fund', # 盈余公积
'UNDIPROF': 'retained_profit', # 未分配利润
})
indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorReport,
[
IndicatorReport.FCFE, # 股东自由现金流量
IndicatorReport.FCFF, # 企业自由现金流量
IndicatorReport.EPSBASIC, # 基本每股收益
IndicatorReport.DPS, # 每股股利（税前）
],
dates=[trade_date])
for col in columns:
if col in list(indicator_sets.keys()):
indicator_sets = indicator_sets.drop(col, axis=1)
indicator_sets = indicator_sets.rename(
columns={
'FCFE': 'shareholder_fcfps', # 股东自由现金流量
'FCFF': 'enterprise_fcfps', # 企业自由现金流量
'EPSBASIC': 'basic_eps', # 基本每股收益
'DPS': 'dividend_receivable', # 每股股利（税前）
})
# TTM data
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM,
[
CashFlowTTM.CASHNETI, # 现金及现金等价物净增加额
CashFlowTTM.MANANETR, # 经营活动现金流量净额
],
dates=[trade_date])
for col in columns:
if col in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(col, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(
columns={
'CASHNETI': 'cash_equivalent_increase_ttm', # 现金及现金等价物净增加额
'MANANETR': 'net_operate_cash_flow_ttm', # 经营活动现金流量净额
})
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM,
[
IncomeTTM.PARENETP, # 归属于母公司所有者的净利润
IncomeTTM.PERPROFIT, # 营业利润
IncomeTTM.BIZINCO, # 营业收入
IncomeTTM.BIZTOTINCO, # 营业总收入
],
dates=[trade_date])
for col in columns:
if col in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(col, axis=1)
income_ttm_sets = income_ttm_sets.rename(
columns={
'PARENETP': 'np_parent_company_owners_ttm', # 归属于母公司所有者的净利润
'PERPROFIT': 'operating_profit_ttm', # 营业利润
'BIZINCO': 'operating_revenue_ttm', # 营业收入
'BIZTOTINCO': 'total_operating_revenue_ttm', # 营业总收入
})
column = ['trade_date']
valuation_data = get_fundamentals(
query(
Valuation.security_code,
Valuation.trade_date,
Valuation.capitalization,
).filter(Valuation.trade_date.in_([trade_date])))
for col in column:
if col in list(valuation_data.keys()):
valuation_data = valuation_data.drop(col, axis=1)
valuation_sets = pd.merge(cash_flow_sets,
income_sets,
on='security_code').reindex()
valuation_sets = pd.merge(balance_sets,
valuation_sets,
on='security_code').reindex()
valuation_sets = pd.merge(indicator_sets,
valuation_sets,
on='security_code').reindex()
valuation_sets = pd.merge(cash_flow_ttm_sets,
valuation_sets,
on='security_code').reindex()
valuation_sets = pd.merge(income_ttm_sets,
valuation_sets,
on='security_code').reindex()
valuation_sets = pd.merge(valuation_data,
valuation_sets,
on='security_code').reindex()
return valuation_sets
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
engine = sqlEngine()
trade_date_pre = self.get_trade_date(trade_date, 1, days=30)
trade_date_1y = self.get_trade_date(trade_date, 1)
trade_date_3y = self.get_trade_date(trade_date, 3)
trade_date_4y = self.get_trade_date(trade_date, 4)
trade_date_5y = self.get_trade_date(trade_date, 5)
# report data
columns = ['COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id', 'trade_date']
balance_report = engine.fetch_fundamentals_pit_extend_company_id(FinBalance,
[FinBalance.total_assets,
], dates=[trade_date])
if len(balance_report) <= 0 or balance_report is None:
balance_report = pd.DataFrame({'security_code': [], 'total_assets': []})
for column in columns:
if column in list(balance_report.keys()):
balance_report = balance_report.drop(column, axis=1)
balance_report = balance_report.rename(columns={
'total_assets': 'total_assets_report', # 资产总计
})
# valuation_report_sets = pd.merge(indicator_sets, balance_report, how='outer', on='security_code')
# MRQ data
cash_flow_mrq = engine.fetch_fundamentals_pit_extend_company_id(FinCashFlow,
[FinCashFlow.cash_and_equivalents_at_end,
], dates=[trade_date])
if len(cash_flow_mrq) <= 0 or cash_flow_mrq is None:
cash_flow_mrq = pd.DataFrame({'security_code': [], 'cash_and_equivalents_at_end': []})
for column in columns:
if column in list(cash_flow_mrq.keys()):
cash_flow_mrq = cash_flow_mrq.drop(column, axis=1)
cash_flow_mrq = cash_flow_mrq.rename(columns={
'cash_and_equivalents_at_end': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
balance_mrq = engine.fetch_fundamentals_pit_extend_company_id(FinBalance,
[FinBalance.longterm_loan, # 短期借款
FinBalance.total_assets, # 资产总计
FinBalance.shortterm_loan, # 短期借款
FinBalance.equities_parent_company_owners,
# 归属于母公司股东权益合计
], dates=[trade_date])
if len(balance_mrq) <= 0 or balance_mrq is None:
balance_mrq = pd.DataFrame(
{'security_code': [], 'longterm_loan': [], 'total_assets': [], 'shortterm_loan': [],
'equities_parent_company_owners': []})
for column in columns:
if column in list(balance_mrq.keys()):
balance_mrq = balance_mrq.drop(column, axis=1)
balance_mrq = balance_mrq.rename(columns={
'shortterm_loan': 'shortterm_loan', # 短期借款
'longterm_loan': 'longterm_loan', # 长期借款
'total_assets': 'total_assets', # 资产总计
'equities_parent_company_owners': 'equities_parent_company_owners', # 归属于母公司股东权益合计
})
valuation_mrq = pd.merge(cash_flow_mrq, balance_mrq, on='security_code')
indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(FinIndicator,
[FinIndicator.np_cut,
], dates=[trade_date])
for col in columns:
if col in list(indicator_sets.keys()):
indicator_sets = indicator_sets.drop(col, axis=1)
# indicator_sets = indicator_sets.rename(columns={'EBIT': 'ebit_mrq'})
valuation_mrq = pd.merge(indicator_sets, valuation_mrq, how='outer', on='security_code')
income_sets = engine.fetch_fundamentals_pit_extend_company_id(FinIncome,
[FinIncome.income_tax, # 所得税
], dates=[trade_date])
for col in columns:
if col in list(income_sets.keys()):
income_sets = income_sets.drop(col, axis=1)
valuation_mrq = pd.merge(income_sets, valuation_mrq, how='outer', on='security_code')
cash_flow_sets = engine.fetch_fundamentals_pit_extend_company_id(FinCashFlow,
[FinCashFlow.fixed_assets_depreciation,
# 固定资产折旧
FinCashFlow.intangible_assets_amortization,
# 无形资产摊销
FinCashFlow.fix_intan_other_asset_acqui_cash,
# 购建固定资产、无形资产和其他...
FinCashFlow.defferred_expense_amortization,
# 长期待摊费用摊销
FinCashFlow.borrowing_repayment, # 偿还债务支付的现金
FinCashFlow.cash_from_borrowing, # 取得借款收到的现金
FinCashFlow.cash_from_bonds_issue,
# 发行债券所收到的现金
], dates=[trade_date])
for col in columns:
if col in list(cash_flow_sets.keys()):
cash_flow_sets = cash_flow_sets.drop(col, axis=1)
valuation_mrq = pd.merge(cash_flow_sets, valuation_mrq, how='outer', on='security_code')
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(FinBalance,
[FinBalance.shortterm_loan,
FinBalance.total_current_assets, # 流动资产合计
FinBalance.total_current_liability, # 流动负债合计
], dates=[trade_date])
for col in columns:
if col in list(balance_sets.keys()):
balance_sets = balance_sets.drop(col, axis=1)
valuation_mrq = pd.merge(balance_sets, valuation_mrq, how='outer', on='security_code')
balance_sets_pre = engine.fetch_fundamentals_pit_extend_company_id(FinBalance,
[FinBalance.total_current_assets, # 流动资产合计
FinBalance.total_current_liability,
# 流动负债合计
], dates=[trade_date_pre])
for col in columns:
if col in list(balance_sets_pre.keys()):
balance_sets_pre = balance_sets_pre.drop(col, axis=1)
balance_sets_pre = balance_sets_pre.rename(columns={
'total_current_assets': 'total_current_assets_pre',
'total_current_liability': 'total_current_liability_pre',
})
valuation_mrq = pd.merge(balance_sets_pre, valuation_mrq, how='outer', on='security_code')
# TTM data
# 总市值合并到TTM数据中，
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(FinCashFlowTTM,
[FinCashFlowTTM.net_operate_cash_flow,
], dates=[trade_date])
if len(cash_flow_ttm_sets) <= 0 or cash_flow_ttm_sets is None:
cash_flow_ttm_sets = pd.DataFrame({'security_code': [], 'net_operate_cash_flow': []})
for column in columns:
if column in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(column, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(columns={
'net_operate_cash_flow': 'net_operate_cash_flow', # 经营活动现金流量净额
})
indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(FinIndicatorTTM,
[FinIndicatorTTM.np_cut,
], dates=[trade_date_1y])
if len(indicator_ttm_sets) <= 0 or indicator_ttm_sets is None:
indicator_ttm_sets = pd.DataFrame({'security_code': [], 'np_cut': []})
for column in columns:
if column in list(indicator_ttm_sets.keys()):
indicator_ttm_sets = indicator_ttm_sets.drop(column, axis=1)
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(FinIncomeTTM,
[FinIncomeTTM.net_profit,
FinIncomeTTM.np_parent_company_owners,
FinIncomeTTM.total_operating_revenue,
FinIncomeTTM.operating_revenue,
FinIncomeTTM.total_profit,
], dates=[trade_date])
if len(income_ttm_sets) <= 0 or income_ttm_sets is None:
income_ttm_sets = pd.DataFrame(
{'security_code': [], 'net_profit': [], 'np_parent_company_owners': [], 'total_operating_revenue': [],
'operating_revenue': [], 'total_profit': []})
for column in columns:
if column in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(column, axis=1)
income_ttm_sets = income_ttm_sets.rename(columns={
'total_profit': 'total_profit', # 利润总额 ttm
'net_profit': 'net_profit', # 净利润
'np_parent_company_owners': 'np_parent_company_owners', # 归属于母公司所有者的净利润
'total_operating_revenue': 'total_operating_revenue', # 营业总收入
'operating_revenue': 'operating_revenue', # 营业收入
})
income_ttm_sets_3 = engine.fetch_fundamentals_pit_extend_company_id(FinIncomeTTM,
[FinIncomeTTM.np_parent_company_owners,
], dates=[trade_date_3y])
if len(income_ttm_sets_3) <= 0 or income_ttm_sets_3 is None:
income_ttm_sets_3 = pd.DataFrame({'security_code': [], 'np_parent_company_owners': []})
for column in columns:
if column in list(income_ttm_sets_3.keys()):
income_ttm_sets_3 = income_ttm_sets_3.drop(column, axis=1)
income_ttm_sets_3 = income_ttm_sets_3.rename(columns={
'np_parent_company_owners': 'np_parent_company_owners_3', # 归属于母公司所有者的净利润
})
income_ttm_sets_5 = engine.fetch_fundamentals_pit_extend_company_id(FinIncomeTTM,
[FinIncomeTTM.np_parent_company_owners,
], dates=[trade_date_5y])
if len(income_ttm_sets_5) <= 0 or income_ttm_sets_5 is None:
income_ttm_sets_5 = pd.DataFrame({'security_code': [], 'np_parent_company_owners': []})
for column in columns:
if column in list(income_ttm_sets_5.keys()):
income_ttm_sets_5 = income_ttm_sets_5.drop(column, axis=1)
income_ttm_sets_5 = income_ttm_sets_5.rename(columns={
'np_parent_company_owners': 'np_parent_company_owners_5', # 归属于母公司所有者的净利润
})
valuation_ttm_sets = pd.merge(cash_flow_ttm_sets, income_ttm_sets, how='outer', on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets, indicator_ttm_sets, how='outer', on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets, income_ttm_sets_3, how='outer', on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets, income_ttm_sets_5, how='outer', on='security_code')
# 流通市值，总市值
column = ['trade_date']
sk_daily_price_sets = get_fundamentals(query(SkDailyPrice.security_code,
SkDailyPrice.trade_date,
SkDailyPrice.tot_market_cap,
SkDailyPrice.circulating_market_cap
).filter(SkDailyPrice.trade_date.in_([trade_date])))
if len(sk_daily_price_sets) <= 0 or sk_daily_price_sets is None:
sk_daily_price_sets = pd.DataFrame({'security_code': [],
'tot_market_cap': [],
'circulating_market_cap': []})
for col in column:
if col in list(sk_daily_price_sets.keys()):
sk_daily_price_sets = sk_daily_price_sets.drop(col, axis=1)
# PS, PE, PB, PCF
column = ['trade_date']
valuation_sets = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe,
Valuation.ps,
Valuation.pb,
Valuation.pcf,
).filter(Valuation.trade_date.in_([trade_date])))
if len(valuation_sets) <= 0 or valuation_sets is None:
valuation_sets = pd.DataFrame({'security_code': [],
'pe': [],
'ps': [],
'pb': [],
'pcf': []})
for col in column:
if col in list(valuation_sets.keys()):
valuation_sets = valuation_sets.drop(col, axis=1)
trade_date_6m = self.get_trade_date(trade_date, 1, 180)
trade_date_3m = self.get_trade_date(trade_date, 1, 90)
# trade_date_2m = self.get_trade_date(trade_date, 1, 60)
trade_date_1m = self.get_trade_date(trade_date, 1, 30)
pe_set = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe,
).filter(Valuation.trade_date.in_([trade_date])))
if len(pe_set) <= 0 or pe_set is None:
pe_set = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_set.keys()):
pe_set = pe_set.drop(col, axis=1)
pe_sets_6m = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe)
.filter(Valuation.trade_date.between(trade_date_6m, trade_date)))
if len(pe_sets_6m) <= 0 or pe_sets_6m is None:
pe_sets_6m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_6m.keys()):
pe_sets_6m = pe_sets_6m.drop(col, axis=1)
pe_sets_6m = pe_sets_6m.groupby('security_code').mean().rename(columns={'pe': 'pe_mean_6m'})
pe_sets_3m = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe)
.filter(Valuation.trade_date.between(trade_date_3m, trade_date)))
if len(pe_sets_3m) <= 0 or pe_sets_3m is None:
pe_sets_3m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_3m.keys()):
pe_sets_3m = pe_sets_3m.drop(col, axis=1)
pe_sets_3m = pe_sets_3m.groupby('security_code').mean().rename(columns={'pe': 'pe_mean_3m'})
pe_sets_2m = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe)
.filter(Valuation.trade_date.between(trade_date_1m, trade_date)))
if len(pe_sets_2m) <= 0 or pe_sets_2m is None:
pe_sets_2m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_2m.keys()):
pe_sets_2m = pe_sets_2m.drop(col, axis=1)
pe_sets_2m = pe_sets_2m.groupby('security_code').mean().rename(columns={'pe': 'pe_mean_1m'})
pe_sets_1y = get_fundamentals(query(Valuation.security_code,
Valuation.trade_date,
Valuation.pe)
.filter(Valuation.trade_date.between(trade_date_1y, trade_date)))
if len(pe_sets_1y) <= 0 or pe_sets_1y is None:
pe_sets_1y = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_1y.keys()):
pe_sets_1y = pe_sets_1y.drop(col, axis=1)
pe_sets_1y = pe_sets_1y.groupby('security_code').mean().rename(columns={'pe': 'pe_mean_1y'})
pe_sets = pd.merge(pe_sets_6m, pe_sets_3m, how='outer', on='security_code')
pe_sets = pd.merge(pe_sets, pe_sets_2m, how='outer', on='security_code')
pe_sets = pd.merge(pe_sets, pe_sets_1y, how='outer', on='security_code')
pe_sets = pd.merge(pe_sets, pe_set, how='outer', on='security_code')
industry_set = ['801010', '801020', '801030', '801040', '801050', '801080', '801110', '801120', '801130',
'801140', '801150', '801160', '801170', '801180', '801200', '801210', '801230', '801710',
'801720', '801730', '801740', '801750', '801760', '801770', '801780', '801790', '801880',
'801890']
column_sw = ['trade_date', 'symbol', 'company_id']
sw_indu = get_fundamentals_extend_internal(query(Industry.trade_date,
Industry.symbol,
Industry.isymbol)
.filter(Industry.trade_date.in_([trade_date])),
internal_type='symbol')
for col in column_sw:
if col in list(sw_indu.keys()):
sw_indu = sw_indu.drop(col, axis=1)
sw_indu = sw_indu[sw_indu['isymbol'].isin(industry_set)]
# valuation_sets = pd.merge(valuation_sets, indicator_sets, how='outer', on='security_code')
valuation_sets = pd.merge(valuation_sets, balance_report, how='outer', on='security_code')
valuation_sets = pd.merge(valuation_sets, valuation_mrq, how='outer', on='security_code')
valuation_sets = pd.merge(valuation_sets, valuation_ttm_sets, how='outer', on='security_code')
valuation_sets = pd.merge(valuation_sets, sk_daily_price_sets, how='outer', on='security_code')
# valuation_sets['tot_market_cap'] = valuation_sets['tot_market_cap'] * 10000
# valuation_sets['circulating_market_cap'] = valuation_sets['circulating_market_cap'] * 10000
return valuation_sets, sw_indu, pe_sets
def loading_data(self, trade_date):
"""
获取基础数据
按天获取当天交易日所有股票的基础数据
:param trade_date: 交易日
:return:
"""
time_array = datetime.strptime(trade_date, "%Y-%m-%d")
trade_date = datetime.strftime(time_array, '%Y%m%d')
engine = sqlEngine()
trade_date_1y = self.get_trade_date(trade_date, 1)
trade_date_3y = self.get_trade_date(trade_date, 3)
trade_date_4y = self.get_trade_date(trade_date, 4)
trade_date_5y = self.get_trade_date(trade_date, 5)
# report data
columns = [
'COMPCODE', 'PUBLISHDATE', 'ENDDATE', 'symbol', 'company_id',
'trade_date'
]
indicator_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorReport, [
IndicatorReport.FCFF,
], dates=[trade_date])
if len(indicator_sets) <= 0 or indicator_sets is None:
indicator_sets = pd.DataFrame({'security_code': [], 'FCFF': []})
for column in columns:
if column in list(indicator_sets.keys()):
indicator_sets = indicator_sets.drop(column, axis=1)
indicator_sets = indicator_sets.rename(columns={
'FCFF': 'enterprise_fcfps', # 企业自由现金流
})
balance_sets = engine.fetch_fundamentals_pit_extend_company_id(
BalanceReport, [
BalanceReport.TOTASSET,
], dates=[trade_date])
if len(balance_sets) <= 0 or balance_sets is None:
balance_sets = pd.DataFrame({'security_code': [], 'TOTASSET': []})
for column in columns:
if column in list(balance_sets.keys()):
balance_sets = balance_sets.drop(column, axis=1)
balance_sets = balance_sets.rename(columns={
'TOTASSET': 'total_assets_report', # 资产总计
})
valuation_report_sets = pd.merge(indicator_sets,
balance_sets,
how='outer',
on='security_code')
# MRQ data
cash_flow_mrq = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowMRQ, [
CashFlowMRQ.FINALCASHBALA,
], dates=[trade_date])
if len(cash_flow_mrq) <= 0 or cash_flow_mrq is None:
cash_flow_mrq = pd.DataFrame({
'security_code': [],
'FINALCASHBALA': []
})
for column in columns:
if column in list(cash_flow_mrq.keys()):
cash_flow_mrq = cash_flow_mrq.drop(column, axis=1)
cash_flow_mrq = cash_flow_mrq.rename(
columns={
'FINALCASHBALA': 'cash_and_equivalents_at_end', # 期末现金及现金等价物余额
})
balance_mrq = engine.fetch_fundamentals_pit_extend_company_id(
BalanceMRQ, [
BalanceMRQ.LONGBORR,
BalanceMRQ.TOTASSET,
BalanceMRQ.SHORTTERMBORR,
BalanceMRQ.PARESHARRIGH,
],
dates=[trade_date])
if len(balance_mrq) <= 0 or balance_mrq is None:
balance_mrq = pd.DataFrame({
'security_code': [],
'LONGBORR': [],
'TOTASSET': [],
'SHORTTERMBORR': [],
'PARESHARRIGH': []
})
for column in columns:
if column in list(balance_mrq.keys()):
balance_mrq = balance_mrq.drop(column, axis=1)
balance_mrq = balance_mrq.rename(
columns={
'SHORTTERMBORR': 'shortterm_loan', # 短期借款
'LONGBORR': 'longterm_loan', # 长期借款
'TOTASSET': 'total_assets', # 资产总计
'PARESHARRIGH':
'equities_parent_company_owners', # 归属于母公司股东权益合计
})
valuation_mrq = pd.merge(cash_flow_mrq,
balance_mrq,
on='security_code')
# TTM data
# 总市值合并到TTM数据中，
cash_flow_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
CashFlowTTM, [
CashFlowTTM.MANANETR,
], dates=[trade_date])
if len(cash_flow_ttm_sets) <= 0 or cash_flow_ttm_sets is None:
cash_flow_ttm_sets = pd.DataFrame({
'security_code': [],
'MANANETR': []
})
for column in columns:
if column in list(cash_flow_ttm_sets.keys()):
cash_flow_ttm_sets = cash_flow_ttm_sets.drop(column, axis=1)
cash_flow_ttm_sets = cash_flow_ttm_sets.rename(
columns={
'MANANETR': 'net_operate_cash_flow', # 经营活动现金流量净额
})
indicator_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IndicatorTTM, [
IndicatorTTM.NETPROFITCUT,
], dates=[trade_date_1y])
if len(indicator_ttm_sets) <= 0 or indicator_ttm_sets is None:
indicator_ttm_sets = pd.DataFrame({
'security_code': [],
'NETPROFITCUT': []
})
for column in columns:
if column in list(indicator_ttm_sets.keys()):
indicator_ttm_sets = indicator_ttm_sets.drop(column, axis=1)
indicator_ttm_sets = indicator_ttm_sets.rename(
columns={
'NETPROFITCUT': 'net_profit_cut_pre', # 扣除非经常性损益的净利润
})
income_ttm_sets = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.NETPROFIT,
IncomeTTM.PARENETP,
IncomeTTM.BIZTOTINCO,
IncomeTTM.BIZINCO,
IncomeTTM.TOTPROFIT,
],
dates=[trade_date])
if len(income_ttm_sets) <= 0 or income_ttm_sets is None:
income_ttm_sets = pd.DataFrame({
'security_code': [],
'NETPROFIT': [],
'PARENETP': [],
'BIZTOTINCO': [],
'BIZINCO': [],
'TOTPROFIT': []
})
for column in columns:
if column in list(income_ttm_sets.keys()):
income_ttm_sets = income_ttm_sets.drop(column, axis=1)
income_ttm_sets = income_ttm_sets.rename(
columns={
'TOTPROFIT': 'total_profit', # 利润总额 ttm
'NETPROFIT': 'net_profit', # 净利润
'PARENETP': 'np_parent_company_owners', # 归属于母公司所有者的净利润
'BIZTOTINCO': 'total_operating_revenue', # 营业总收入
'BIZINCO': 'operating_revenue', # 营业收入
})
income_ttm_sets_3 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.PARENETP,
], dates=[trade_date_3y])
if len(income_ttm_sets_3) <= 0 or income_ttm_sets_3 is None:
income_ttm_sets_3 = pd.DataFrame({
'security_code': [],
'PARENETP': []
})
for column in columns:
if column in list(income_ttm_sets_3.keys()):
income_ttm_sets_3 = income_ttm_sets_3.drop(column, axis=1)
income_ttm_sets_3 = income_ttm_sets_3.rename(
columns={
'PARENETP': 'np_parent_company_owners_3', # 归属于母公司所有者的净利润
})
income_ttm_sets_5 = engine.fetch_fundamentals_pit_extend_company_id(
IncomeTTM, [
IncomeTTM.PARENETP,
], dates=[trade_date_5y])
if len(income_ttm_sets_5) <= 0 or income_ttm_sets_5 is None:
income_ttm_sets_5 = pd.DataFrame({
'security_code': [],
'PARENETP': []
})
for column in columns:
if column in list(income_ttm_sets_5.keys()):
income_ttm_sets_5 = income_ttm_sets_5.drop(column, axis=1)
income_ttm_sets_5 = income_ttm_sets_5.rename(
columns={
'PARENETP': 'np_parent_company_owners_5', # 归属于母公司所有者的净利润
})
valuation_ttm_sets = pd.merge(cash_flow_ttm_sets,
income_ttm_sets,
how='outer',
on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets,
indicator_ttm_sets,
how='outer',
on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets,
income_ttm_sets_3,
how='outer',
on='security_code')
valuation_ttm_sets = pd.merge(valuation_ttm_sets,
income_ttm_sets_5,
how='outer',
on='security_code')
# PS, PE, PB, PCF
column = ['trade_date']
valuation_sets = get_fundamentals(
query(Valuation.security_code, Valuation.trade_date, Valuation.pe,
Valuation.ps, Valuation.pb, Valuation.pcf,
Valuation.market_cap,
Valuation.circulating_market_cap).filter(
Valuation.trade_date.in_([trade_date])))
if len(valuation_sets) <= 0 or valuation_sets is None:
valuation_sets = pd.DataFrame({
'security_code': [],
'pe': [],
'ps': [],
'pb': [],
'pcf': [],
'market_cap': [],
'circulating_market_cap': []
})
for col in column:
if col in list(valuation_sets.keys()):
valuation_sets = valuation_sets.drop(col, axis=1)
trade_date_6m = self.get_trade_date(trade_date, 1, 180)
trade_date_3m = self.get_trade_date(trade_date, 1, 90)
# trade_date_2m = self.get_trade_date(trade_date, 1, 60)
trade_date_1m = self.get_trade_date(trade_date, 1, 20)
pe_set = get_fundamentals(
query(
Valuation.security_code,
Valuation.trade_date,
Valuation.pe,
).filter(Valuation.trade_date.in_([trade_date])))
if len(pe_set) <= 0 or pe_set is None:
pe_set = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_set.keys()):
pe_set = pe_set.drop(col, axis=1)
pe_sets_6m = get_fundamentals(
query(Valuation.security_code, Valuation.trade_date,
Valuation.pe).filter(
Valuation.trade_date.between(trade_date_6m, trade_date)))
if len(pe_sets_6m) <= 0 or pe_sets_6m is None:
pe_sets_6m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_6m.keys()):
pe_sets_6m = pe_sets_6m.drop(col, axis=1)
pe_sets_6m = pe_sets_6m.groupby('security_code').mean().rename(
columns={'pe': 'pe_mean_6m'})
pe_sets_3m = get_fundamentals(
query(Valuation.security_code, Valuation.trade_date,
Valuation.pe).filter(
Valuation.trade_date.between(trade_date_3m, trade_date)))
if len(pe_sets_3m) <= 0 or pe_sets_3m is None:
pe_sets_3m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_3m.keys()):
pe_sets_3m = pe_sets_3m.drop(col, axis=1)
pe_sets_3m = pe_sets_3m.groupby('security_code').mean().rename(
columns={'pe': 'pe_mean_3m'})
pe_sets_2m = get_fundamentals(
query(Valuation.security_code, Valuation.trade_date,
Valuation.pe).filter(
Valuation.trade_date.between(trade_date_1m, trade_date)))
if len(pe_sets_2m) <= 0 or pe_sets_2m is None:
pe_sets_2m = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_2m.keys()):
pe_sets_2m = pe_sets_2m.drop(col, axis=1)
pe_sets_2m = pe_sets_2m.groupby('security_code').mean().rename(
columns={'pe': 'pe_mean_1m'})
pe_sets_1y = get_fundamentals(
query(Valuation.security_code, Valuation.trade_date,
Valuation.pe).filter(
Valuation.trade_date.between(trade_date_1y, trade_date)))
if len(pe_sets_1y) <= 0 or pe_sets_1y is None:
pe_sets_1y = pd.DataFrame({'security_code': [], 'pe': []})
for col in column:
if col in list(pe_sets_1y.keys()):
pe_sets_1y = pe_sets_1y.drop(col, axis=1)
pe_sets_1y = pe_sets_1y.groupby('security_code').mean().rename(
columns={'pe': 'pe_mean_1y'})
pe_sets = pd.merge(pe_sets_6m,
pe_sets_3m,
how='outer',
on='security_code')
pe_sets = pd.merge(pe_sets,
pe_sets_2m,
how='outer',
on='security_code')
pe_sets = pd.merge(pe_sets,
pe_sets_1y,
how='outer',
on='security_code')
pe_sets = pd.merge(pe_sets, pe_set, how='outer', on='security_code')
industry_set = [
'801010', '801020', '801030', '801040', '801050', '801080',
'801110', '801120', '801130', '801140', '801150', '801160',
'801170', '801180', '801200', '801210', '801230', '801710',
'801720', '801730', '801740', '801750', '801760', '801770',
'801780', '801790', '801880', '801890'
]
column_sw = ['trade_date', 'symbol', 'company_id']
sw_indu = get_fundamentals_extend_internal(query(
Industry.trade_date, Industry.symbol,
Industry.isymbol).filter(Industry.trade_date.in_([trade_date])),
internal_type='symbol')
for col in column_sw:
if col in list(sw_indu.keys()):
sw_indu = sw_indu.drop(col, axis=1)
sw_indu = sw_indu[sw_indu['isymbol'].isin(industry_set)]
valuation_sets = pd.merge(valuation_sets,
valuation_report_sets,
how='outer',
on='security_code')
valuation_sets = pd.merge(valuation_sets,
valuation_mrq,
how='outer',
on='security_code')
valuation_sets = pd.merge(valuation_sets,
valuation_ttm_sets,
how='outer',
on='security_code')
return valuation_sets, sw_indu, pe_sets