def get_balance_data(provider: Provider, sleep, desc, pc, lock, region,
batch):
# 资产负债表
BalanceSheet.record_data(provider=provider,
share_para=(desc, pc, lock, True, region),
sleeping_time=sleep,
batch_size=batch)
def risky_company(region: Region,
the_date=None,
income_yoy=-0.1,
profit_yoy=-0.1,
entity_ids=None):
if not the_date:
the_date = to_pd_timestamp(now_time_str(region))
codes = []
start_timestamp = to_pd_timestamp(the_date) - datetime.timedelta(130)
# 营收降，利润降,流动比率低，速动比率低
finance_filter = or_(FinanceFactor.op_income_growth_yoy < income_yoy,
FinanceFactor.net_profit_growth_yoy <= profit_yoy,
FinanceFactor.current_ratio < 0.7,
FinanceFactor.quick_ratio < 0.5)
df = FinanceFactor.query_data(region=region,
entity_ids=entity_ids,
start_timestamp=start_timestamp,
filters=[finance_filter],
columns=['code'])
if pd_is_not_null(df):
codes = codes + df.code.tolist()
# 高应收，高存货，高商誉
balance_filter = (BalanceSheet.accounts_receivable + BalanceSheet.inventories + BalanceSheet.goodwill) \
> BalanceSheet.total_equity
df = BalanceSheet.query_data(region=region,
entity_ids=entity_ids,
start_timestamp=start_timestamp,
filters=[balance_filter],
columns=['code'])
if pd_is_not_null(df):
codes = codes + df.code.tolist()
# 应收>利润*1/2
df1 = BalanceSheet.query_data(
region=region,
entity_ids=entity_ids,
start_timestamp=start_timestamp,
columns=[BalanceSheet.code, BalanceSheet.accounts_receivable])
if pd_is_not_null(df1):
df1.drop_duplicates(subset='code', keep='last', inplace=True)
df1 = df1.set_index('code', drop=True).sort_index()
df2 = IncomeStatement.query_data(
region=region,
entity_ids=entity_ids,
start_timestamp=start_timestamp,
columns=[IncomeStatement.code, IncomeStatement.net_profit])
if pd_is_not_null(df2):
df2.drop_duplicates(subset='code', keep='last', inplace=True)
df2 = df2.set_index('code', drop=True).sort_index()
if pd_is_not_null(df1) and pd_is_not_null(df2):
codes = codes + df1[df1.accounts_receivable > df2.net_profit /
2].index.tolist()
return list(set(codes))
def report_core_company():
while True:
error_count = 0
email_action = EmailInformer()
try:
StockTradeDay.record_data(provider='joinquant')
Stock.record_data(provider='joinquant')
FinanceFactor.record_data(provider='eastmoney')
BalanceSheet.record_data(provider='eastmoney')
latest_day: StockTradeDay = StockTradeDay.query_data(
order=StockTradeDay.timestamp.desc(),
limit=1,
return_type='domain')
if latest_day:
target_date = latest_day[0].timestamp
else:
target_date = now_pd_timestamp()
my_selector: TargetSelector = FundamentalSelector(
start_timestamp='2015-01-01', end_timestamp=target_date)
my_selector.run()
long_targets = my_selector.get_open_long_targets(
timestamp=target_date)
if long_targets:
stocks = get_entities(provider='joinquant',
entity_schema=Stock,
entity_ids=long_targets,
return_type='domain')
info = [f'{stock.name}({stock.code})' for stock in stocks]
msg = ' '.join(info)
else:
msg = 'no targets'
logger.info(msg)
email_action.send_message([
'*****@*****.**', '*****@*****.**',
'*****@*****.**', '*****@*****.**',
'*****@*****.**'
], f'{to_time_str(target_date)} 核心资产选股结果', msg)
break
except Exception as e:
logger.exception('report_core_company error:{}'.format(e))
time.sleep(60 * 3)
error_count = error_count + 1
if error_count == 10:
email_action.send_message(
"*****@*****.**", f'report_core_company error',
'report_core_company error:{}'.format(e))
def record(self, entity, start, end, size, timestamps):
param = self.generate_request_param(entity, start, end, size,
timestamps)
# to_time_str(
# self.data_schema.query_data(filters=[self.data_schema.report_date>='20200101'],
# entity_id=entity.id, columns=['report_date']).report_date.max()) >= to_time_str(
# '20200101')
columns_map = {
key: value[0]
for key, value in self.get_data_map().items()
}
columns_list = list(columns_map.values())
em_code = to_em_entity_id(entity)
df = pd.DataFrame()
for reportdate in param:
em_data = c.css(
em_code, columns_list,
"ispandas=1,TtmType=1,TradeDate=" + to_time_str(reportdate) +
",ReportDate=" + to_time_str(reportdate))
if type(em_data) == pd.DataFrame:
em_data['report_date'] = to_time_str(reportdate)
df = df.append(em_data)
if df.empty:
return None
df.rename(columns={value: key
for key, value in columns_map.items()},
inplace=True)
df = df.sort_values("report_date", ascending=True)
if pd_is_not_null(df):
df.rename(
columns={value: key
for key, value in columns_map.items()},
inplace=True)
df['entity_id'] = entity.id
df['provider'] = 'emquantapi'
df['code'] = entity.code
df['report_period'] = df['report_date'].apply(
lambda x: to_report_period_type(x))
def generate_id(se):
return "{}_{}".format(
se['entity_id'],
to_time_str(se['report_date'], fmt=TIME_FORMAT_DAY))
df['id'] = df[['entity_id', 'report_date']].apply(generate_id,
axis=1)
data_pub_date = BalanceSheet.query_data(entity_id=entity.id,
columns=['pub_date', 'id'])
del data_pub_date['timestamp']
df = pd.merge(df, data_pub_date, on=['id'])
df['timestamp'] = df['pub_date']
df_to_db(df=df,
data_schema=self.data_schema,
provider=self.provider,
force_update=True)
return None
def test_000001_balance_sheet():
correct_timestamps = [
'2018-09-30', '2018-06-30', '2018-03-31', '2017-12-31', '2017-09-30',
'2017-06-30', '2017-03-31', '2016-12-31', '2016-09-30', '2016-06-30',
'2016-03-31', '2015-12-31', '2015-09-30', '2015-06-30', '2015-03-31',
'2014-12-31', '2014-09-30', '2014-06-30', '2014-03-31', '2013-12-31',
'2013-09-30', '2013-06-30', '2013-03-31', '2012-12-31', '2012-09-30',
'2012-06-30', '2012-03-31', '2011-12-31', '2011-09-30', '2011-06-30',
'2011-03-31', '2010-12-31', '2010-09-30', '2010-06-30', '2010-03-31',
'2009-12-31', '2009-09-30', '2009-06-30', '2009-03-31', '2008-12-31',
'2008-09-30', '2008-06-30', '2008-03-31', '2007-12-31', '2007-09-30',
'2007-06-30', '2007-03-31', '2006-12-31', '2006-09-30', '2006-06-30',
'2006-03-31', '2005-12-31', '2005-09-30', '2005-06-30', '2005-03-31',
'2004-12-31', '2004-09-30', '2004-06-30', '2004-03-31', '2003-12-31',
'2003-09-30', '2003-06-30', '2003-03-31', '2002-12-31', '2002-09-30',
'2002-06-30', '2002-03-31', '2001-12-31', '2001-06-30', '2000-12-31',
'2000-06-30', '1999-12-31', '1999-06-30', '1998-12-31', '1998-06-30',
'1997-12-31', '1997-06-30', '1996-12-31', '1996-06-30', '1995-12-31',
'1995-06-30', '1994-12-31', '1994-06-30', '1993-12-31', '1992-12-31',
'1991-12-31', '1990-12-31', '1989-12-31'
]
result = BalanceSheet.query_data(session=session,
provider='eastmoney',
return_type='domain',
codes=['000001'],
end_timestamp='2018-12-30',
order=BalanceSheet.report_date.desc(),
time_field='report_date')
assert len(correct_timestamps) == len(result)
timestamps = [to_time_str(item.report_date) for item in result]
assert set(correct_timestamps) == set(timestamps)
latest: BalanceSheet = result[0]
assert latest.fi_cash_and_deposit_in_central_bank == 287600000000
assert latest.fi_deposit_in_other_fi == 95310000000
assert latest.fi_expensive_metals == 72020000000
assert latest.fi_lending_to_other_fi == 88100000000
assert latest.fi_financial_assets_effect_current_income == 103500000000
assert latest.fi_financial_derivative_asset == 25650000000
assert latest.fi_buying_sell_back_fi__asset == 32760000000
assert latest.accounts_receivable == 22830000000
assert latest.fi_interest_receivable == 18310000000
assert latest.fi_disbursing_loans_and_advances == 1870000000000
assert latest.real_estate_investment == 194000000
assert latest.fixed_assets == 9374000000
assert latest.intangible_assets == 4722000000
assert latest.goodwill == 7568000000
assert latest.deferred_tax_assets == 28880000000
assert latest.fi_other_asset == 15630000000
assert latest.total_assets == 3350000000000
assert latest.fi_borrowings_from_central_bank == 149900000000
assert latest.fi_deposit_from_other_fi == 402300000000
assert latest.fi_borrowings_from_fi == 17830000000
assert latest.fi_financial_liability_effect_current_income == 9599000000
assert latest.fi_financial_derivative_liability == 20730000000
assert latest.fi_sell_buy_back_fi_asset == 2000000000
assert latest.fi_savings_absorption == 2130000000000
assert latest.fi_notes_payable == 0
assert latest.employee_benefits_payable == 10550000000
assert latest.taxes_payable == 7595000000
assert latest.interest_payable == 27670000000
assert latest.fi_estimated_liabilities == 24000000
assert latest.fi_bond_payable == 321500000000
assert latest.fi_other_liability == 12570000000
assert latest.total_liabilities == 3120000000000
assert latest.fi_capital == 17170000000
assert latest.fi_other_equity_instruments == 19950000000
assert latest.fi_preferred_stock == 19950000000
assert latest.capital_reserve == 56470000000
assert latest.surplus_reserve == 10780000000
assert latest.fi_generic_risk_reserve == 38550000000
assert latest.undistributed_profits == 91970000000
assert latest.equity == 235200000000
assert latest.total_liabilities_and_equity == 3350000000000
def test_000778_balance_sheet():
correct_timestamps = [
'2018-09-30', '2018-06-30', '2018-03-31', '2017-12-31', '2017-09-30',
'2017-06-30', '2017-03-31', '2016-12-31', '2016-09-30', '2016-06-30',
'2016-03-31', '2015-12-31', '2015-09-30', '2015-06-30', '2015-03-31',
'2014-12-31', '2014-09-30', '2014-06-30', '2014-03-31', '2013-12-31',
'2013-09-30', '2013-06-30', '2013-03-31', '2012-12-31', '2012-09-30',
'2012-06-30', '2012-03-31', '2011-12-31', '2011-09-30', '2011-06-30',
'2011-03-31', '2010-12-31', '2010-09-30', '2010-06-30', '2010-03-31',
'2009-12-31', '2009-09-30', '2009-06-30', '2009-03-31', '2008-12-31',
'2008-09-30', '2008-06-30', '2008-03-31', '2007-12-31', '2007-09-30',
'2007-06-30', '2007-03-31', '2006-12-31', '2006-09-30', '2006-06-30',
'2006-03-31', '2005-12-31', '2005-09-30', '2005-06-30', '2005-03-31',
'2004-12-31', '2004-09-30', '2004-06-30', '2004-03-31', '2003-12-31',
'2003-09-30', '2003-06-30', '2003-03-31', '2002-12-31', '2002-09-30',
'2002-06-30', '2002-03-31', '2001-12-31', '2001-06-30', '2000-12-31',
'2000-06-30', '1999-12-31', '1999-06-30', '1998-12-31', '1998-06-30',
'1997-12-31', '1997-06-30', '1996-12-31', '1995-12-31', '1994-12-31'
]
result = BalanceSheet.query_data(session=session,
provider='eastmoney',
return_type='domain',
codes=['000778'],
end_timestamp='2018-12-30',
order=BalanceSheet.report_date.desc(),
time_field='report_date')
assert len(correct_timestamps) == len(result)
timestamps = [to_time_str(item.report_date) for item in result]
assert set(correct_timestamps) == set(timestamps)
latest: BalanceSheet = result[0]
assert latest.cash_and_cash_equivalents == 6381000000
assert latest.note_receivable == 4655000000
assert latest.accounts_receivable == 1920000000
assert latest.advances_to_suppliers == 2112000000
assert latest.other_receivables == 4671000000
assert latest.inventories == 3891000000
assert latest.current_portion_of_non_current_assets == 270000000
assert latest.other_current_assets == 220100000
assert latest.total_current_assets == 24120000000
assert latest.fi_assets_saleable == 914900000
assert latest.long_term_receivables == 1078000000
assert latest.long_term_equity_investment == 6071000000
assert latest.real_estate_investment == 24130000
assert latest.fixed_assets == 15440000000
assert latest.construction_in_process == 1196000000
assert latest.intangible_assets == 853800000
assert latest.goodwill == 32990000
assert latest.long_term_prepaid_expenses == 2527000
assert latest.deferred_tax_assets == 292300000
assert latest.other_non_current_assets == 332800000
assert latest.total_assets == 50350000000
assert latest.short_term_borrowing == 9104000000
assert latest.accept_money_deposits == 0
assert latest.advances_from_customers == 3199000000
assert latest.employee_benefits_payable == 207800000
assert latest.taxes_payable == 735200000
assert latest.other_payable == 2022000000
assert latest.current_portion_of_non_current_liabilities == 1099000000
assert latest.other_current_liabilities == 1000000000
assert latest.total_current_liabilities == 24050000000
assert latest.long_term_borrowing == 914400000
assert latest.fi_bond_payable == 2992000000
assert latest.long_term_payable == 636800000
assert latest.deferred_tax_liabilities == 314800000
assert latest.other_non_current_liabilities == 127800000
assert latest.total_non_current_liabilities == 4986000000
assert latest.total_liabilities == 29040000000
assert latest.capital == 3991000000
assert latest.capital_reserve == 8688000000
assert latest.special_reserve == 40730000
assert latest.surplus_reserve == 1286000000
assert latest.undistributed_profits == 7236000000
assert latest.equity == 20320000000
assert latest.equity_as_minority_interest == 997400000
assert latest.total_equity == 21320000000
assert latest.total_liabilities_and_equity == 50350000000
def record(self, entity, start, end, size, timestamps):
param = self.generate_request_param(entity, start, end, size,
timestamps)
if self.finance_report_type == 'FinanceGrowthAbility':
from zvt.domain import FinanceGrowthAbility
data_old = FinanceGrowthAbility.query_data(entity_id=entity.id)
if self.finance_report_type == 'FinancePerShare':
from zvt.domain import FinancePerShare
data_old = FinancePerShare.query_data(entity_id=entity.id)
if self.finance_report_type == 'FinanceProfitAbility':
from zvt.domain import FinanceProfitAbility
data_old = FinanceProfitAbility.query_data(entity_id=entity.id)
if self.finance_report_type == 'FinanceReceivingAbility':
from zvt.domain import FinanceReceivingAbility
data_old = FinanceReceivingAbility.query_data(entity_id=entity.id)
if 'pub_date' in data_old.columns:
del data_old['pub_date']
dict_ps = self.get_data_map2()
data_pub_date = BalanceSheet.query_data(entity_id=entity.id,
columns=['pub_date', 'id'])
del data_pub_date['timestamp']
df_old_all = pd.merge(data_old, data_pub_date, on=['id'])
df_old_all.rename(columns=dict_ps, inplace=True)
columns_map = {
key: value[0]
for key, value in self.get_data_map().items()
}
init_list = [
'pub_date', 'id', 'entity_id', 'timestamp', 'provider', 'code',
'report_period', 'report_date'
]
get_data_list = [
i for i in columns_map
if i not in df_old_all.columns and i not in init_list
]
choice_list = [j for k, j in columns_map.items() if k in get_data_list]
em_code = to_em_entity_id(entity)
df = pd.DataFrame()
for reportdate in df_old_all.report_date.to_list():
em_data = c.css(
em_code, choice_list,
"ispandas=1,TtmType=1,TradeDate=" + to_time_str(reportdate) +
",ReportDate=" + to_time_str(reportdate))
if type(em_data) == pd.DataFrame:
em_data['report_date'] = to_time_str(reportdate)
df = df.append(em_data)
if df.empty:
return None
df.rename(columns={value: key
for key, value in columns_map.items()},
inplace=True)
df = df.sort_values("report_date", ascending=True)
if pd_is_not_null(df):
df.rename(
columns={value: key
for key, value in columns_map.items()},
inplace=True)
df['entity_id'] = entity.id
def generate_id(se):
return "{}_{}".format(
se['entity_id'],
to_time_str(se['report_date'], fmt=TIME_FORMAT_DAY))
df['id'] = df[['entity_id', 'report_date']].apply(generate_id,
axis=1)
del df['entity_id'], df['report_date']
data_res = pd.merge(df, df_old_all, on=['id'])
data_res['provider'] = 'emquantapi'
data_res['timestamp'] = data_res['pub_date']
df_to_db(df=data_res,
data_schema=self.data_schema,
provider=self.provider,
force_update=True)
return None
risk_stock_list = list(
set(stock_df.symbol.tolist() + buy_signaldata.股票代码.tolist()))
# 收入中应收账款占比不能超过50
# 质押比例
EquityPledgeData = EquityPledge.query_data(
filters=[
EquityPledge.end_date >= risk_start,
],
columns=["pledge_total_ratio", "start_date", "end_date", "code"],
codes=risk_stock_list,
)
# 应收账款 000568
BalanceSheetData = BalanceSheet.query_data(
filters=[BalanceSheet.report_period == "year"],
columns=["accounts_receivable", "report_date", "code"],
codes=risk_stock_list,
end_timestamp=risk_end,
)
# 营业收入
IncomeStatementData = IncomeStatement.query_data(
filters=[IncomeStatement.report_period == "year"],
columns=["operating_income", "report_date", "code"],
codes=risk_stock_list,
end_timestamp=risk_end,
)
HolderTradingData = HolderTrading.query_data(
filters=[HolderTrading.holder_direction == "减持"],
codes=risk_stock_list,
start_timestamp=risk_start,
end_timestamp=risk_end,
provider="emquantapi",