def strategy_gap():
stocks_sql = "select code,short_name from security"
stock_codes = my.select_all(stocks_sql, ())
gap_list = []
for stock_code in stock_codes:
code = stock_code['code']
price_sql = "select date,close,low,high from stock_price where code = %s order by date desc limit 2"
price_data = my.select_all(price_sql, code)
now_low = float(price_data[0]['low'])
now_high = float(price_data[0]['high'])
yesterday_low = float(price_data[1]['low'])
yesterday_high = float(price_data[1]['high'])
if now_low > yesterday_high or yesterday_low > now_high:
short_name = stock_code['short_name']
now_date = price_data[0]['date']
now_close = float(price_data[0]['close'])
now_range = _get_range(now_close, now_high, now_low)
gap_value = (code, short_name, now_date, '缺口', now_close, now_high,
now_low, now_range)
print(gap_value)
gap_list.append(gap_value)
insert_sql = "insert into strategy(code, company_name, date, type , price, highest, lowest, `range`)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, gap_list)
def init_sma(limit, ma_num):
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
for stock_code in stock_codes:
code = stock_code['code']
price_query_sql = "select date, close from stock_price where code = %s order by date desc limit %s"
price_data = my.select_all(price_query_sql, (code, limit + ma_num))
price_list = []
for price in price_data:
price_value = price['close']
price_list.append(price_value)
price_list = np.array(price_list)
sma_list = []
for i in range(len(price_data)):
sma_value = math.get_sma(price_list[i:20 + i])
price = price_data[i]
sma = (sma_value, code, price['date'])
sma_list.append(sma)
if i >= limit - 1:
break
print(sma_list)
update_sql = "update stock_price set sma_20 = %s where code = %s and date = %s"
my.update_many(update_sql, sma_list)
def strategy_ma():
stocks_sql = "select code,short_name,highest,lowest from security"
stocks = my.select_all(stocks_sql, ())
ma_list = []
for index_stock in stocks:
code = index_stock['code']
price_sql = "select date, sma_20, close from stock_price where code = %s order by date desc limit 2"
ma_data = my.select_all(price_sql, code)
now_close = float(ma_data[0]['close'])
now_ma_20 = float(ma_data[0]['sma_20'])
yesterday_close = float(ma_data[1]['close'])
yesterday_ma_20 = float(ma_data[1]['sma_20'])
if yesterday_close > yesterday_ma_20:
continue
strategy_type = None
if now_close > now_ma_20:
strategy_type = '破线'
if now_ma_20 > yesterday_ma_20:
if strategy_type is None:
strategy_type = '拐头'
else:
strategy_type += '拐头'
if strategy_type is None:
continue
short_name = index_stock['short_name']
now_date = ma_data[0]['date']
now_close = float(ma_data[0]['close'])
highest = float(index_stock['highest'])
lowest = float(index_stock['lowest'])
now_range = _get_range(now_close, highest, lowest)
ma_value = (code, short_name, now_date, strategy_type, now_close,
highest, lowest, now_range)
print(ma_value)
ma_list.append(ma_value)
insert_sql = "insert into strategy(code, company_name, date, type , price, highest, lowest, `range`)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, ma_list)
def strategy_market_width(industry_type='sw_l1', start_date=None):
width_sql = "select industry.industry_name, industry.industry_code, price.date, count(1) count, " \
"count(if(price.close > price.sma_20, 1, null)) success " \
"from stock_price price left join stock_industry industry on price.code = industry.code " \
"where industry.type = %s and price.date >= (select price_1.date from stock_price price_1 order by price_1.date desc limit 1) " \
"group by industry.industry_name, industry.industry_code, price.date " \
"order by price.date desc"
width_data = my.select_all(width_sql, (industry_type))
width_list = []
for index_width in width_data:
industry_name = index_width['industry_name']
industry_code = index_width['industry_code']
date = index_width['date']
count = index_width['count']
success = index_width['success']
success_range = round(success / count, 4) * 100
width = (industry_type, industry_name, industry_code, date, count,
success, success_range)
print(width)
width_list.append(width)
insert_sql = "insert into width(industry_type, industry_name, industry_code, date, count, success, `range`) " \
"values (%s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, width_list)
def strategy_low_value():
price_sql = "select stock.code, stock.short_name,price.date,price.close,stock.highest,stock.lowest " \
"from stock_price price left join security stock on price.code = stock.code " \
"where price.date = (select price_1.date from stock_price price_1 order by price_1.date desc limit 1)"
price_data = my.select_all(price_sql, ())
low_value_list = []
for index_price in price_data:
close = float(index_price['close'])
highest = float(index_price['highest'])
lowest = float(index_price['lowest'])
print("close: %s, highest: %s, lowest: %s", close, highest, lowest)
low_range = _get_range(close, highest, lowest)
if low_range > 85:
code = index_price['code']
short_name = index_price['short_name']
date = index_price['date']
low_value = (code, short_name, date, '低值', close, highest, lowest,
low_range)
low_value_list.append(low_value)
print(low_value_list)
insert_sql = "insert into strategy(code, company_name, date, type , price, highest, lowest, `range`)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, low_value_list)
def strategy_slump():
stocks_sql = "select code,short_name,highest,lowest from security"
stocks = my.select_all(stocks_sql, ())
slump_list = []
for index_stock in stocks:
code = index_stock['code']
price_sql = "select date, close from stock_price where code = %s order by date desc limit 6"
slump_data = my.select_all(price_sql, code)
now_close = float(slump_data[0]['close'])
# 两天前的收盘价
pre_2_close = float(slump_data[2]['close'])
pre_5_close = float(slump_data[5]['close'])
# 跌幅
range_2 = round((pre_2_close - now_close) / pre_2_close, 4)
range_5 = round((pre_5_close - now_close) / pre_5_close, 4)
if range_2 > 0.15:
short_name = index_stock['short_name']
now_date = slump_data[0]['date']
highest = float(index_stock['highest'])
lowest = float(index_stock['lowest'])
now_range_2 = _get_range(now_close, highest, lowest)
slump_value = (code, short_name, now_date, '急跌: 2日', now_close,
highest, lowest, now_range_2)
print(slump_value)
slump_list.append(slump_value)
if range_5 > 0.3:
short_name = index_stock['short_name']
now_date = slump_data[0]['date']
now_range_5 = _get_range(now_close, highest, lowest)
slump_value = (code, short_name, now_date, '急跌: 5日', now_close,
highest, lowest, now_range_5)
print(slump_value)
slump_list.append(slump_value)
insert_sql = "insert into strategy(code, company_name, date, type , price, highest, lowest, `range`)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, slump_list)
def init_stock_price(start_date, end_date):
stocks_sql = "select code,highest,lowest from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
for stock_code in stock_codes:
code = stock_code['code']
# exist_sql = "select count(1) count from stock_price where code = %s"
# exist = my.select_one(exist_sql, code)
#
# if exist['count'] > 0:
# print('%s had init', code)
# continue
price_data = sdk.get_price(code,
start_date=start_date,
end_date=end_date,
frequency='daily',
fq='pre')
stock_price_list = []
for index in price_data.index:
index_price = price_data.loc[index]
open = float(index_price['open'])
if math.isnan(open):
continue
close = float(index_price['close'])
low = float(index_price['low'])
high = float(index_price['high'])
volume = float(index_price['volume'])
money = float(index_price['money'])
date = index.strftime('%Y-%m-%d')
stock_price = (code, date, open, close, low, high, volume, money)
print(stock_price)
stock_price_list.append(stock_price)
insert_sql = "insert into stock_price(code, date, open, close, low, high, volume, money) values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, stock_price_list)
now_highest = stock_code['highest']
highest_sql = "select close from stock_price where code = %s order by close desc limit 1"
highest = my.select_one(highest_sql, code)
if now_highest is None or highest['close'] > now_highest:
update_highest = "update security set highest = %s where code = %s"
my.update_one(update_highest, (highest['close'], code))
print('%s 修改最高值：现在：%s， 修改为：%s', code, now_highest,
highest['close'])
now_lowest = stock_code['lowest']
lowest_sql = "select close from stock_price where code = %s order by close asc limit 1"
lowest = my.select_one(lowest_sql, code)
if now_lowest is None or lowest['close'] < now_lowest:
update_lowest = "update security set lowest = %s where code = %s"
my.update_one(update_lowest, (lowest['close'], code))
print('%s 修改最低值：现在：%s， 修改为：%s', code, now_lowest, lowest['close'])
def interest_stock(date, strategy_type):
query_sql = "select s.* from strategy s left join stock_price price on s.code = price.code and s.date = price.date " \
"where s.date = %s and price.money > 100000000 and s.type = %s"
data = my.select_all(query_sql, (date, strategy_type))
frame = pd.DataFrame(data)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('max_colwidth', 200)
print(frame)
def init_stock_industries():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
stock_industry_list = []
for stock_code in stock_codes:
code = stock_code['code']
data = sdk.get_industry(code, date='2020-10-30')
print(data)
stock_industry_data = data[code]
if not bool(stock_industry_data):
continue
industry_zjw = stock_industry_data['zjw']
stock_industry_zjw = (code, 'zjw', industry_zjw['industry_code'],
industry_zjw['industry_name'])
stock_industry_list.append(stock_industry_zjw)
has_sw_l1 = 'sw_l1' in stock_industry_data.keys()
if has_sw_l1:
industry_sw_l1 = stock_industry_data['sw_l1']
industry_sw_l2 = stock_industry_data['sw_l2']
stock_industry_sw_l1 = (code, 'sw_l1',
industry_sw_l1['industry_code'],
industry_sw_l1['industry_name'])
stock_industry_sw_l2 = (code, 'sw_l2',
industry_sw_l2['industry_code'],
industry_sw_l2['industry_name'])
stock_industry_list.append(stock_industry_sw_l1)
stock_industry_list.append(stock_industry_sw_l2)
has_jq_l1 = 'jq_l1' in stock_industry_data.keys()
if has_jq_l1:
industry_jq_l1 = stock_industry_data['jq_l1']
stock_industry_jq_l1 = (code, 'jq_l1',
industry_jq_l1['industry_code'],
industry_jq_l1['industry_name'])
stock_industry_list.append(stock_industry_jq_l1)
has_jq_l2 = 'jq_l2' in stock_industry_data.keys()
if has_jq_l2:
industry_jq_l2 = stock_industry_data['jq_l2']
stock_industry_jq_l2 = (code, 'jq_l2',
industry_jq_l2['industry_code'],
industry_jq_l2['industry_name'])
stock_industry_list.append(stock_industry_jq_l2)
insert_sql = "insert into stock_industry(code, type, industry_code, industry_name) values (%s, %s, %s, %s)"
my.insert_many(insert_sql, stock_industry_list)
def stock_update():
pool_sql = "select code from pool where level > 0"
pool_data = my.select_all(pool_sql, ())
for index_pool in pool_data:
code = index_pool['code']
update_price = "update pool set now_price = (select close from stock_price where code = %s order by date desc limit 1) where code = %s"
my.update_one(update_price, (code, code))
update_ratio_sql = "update pool set ratio = round(now_price / add_price, 4) * 100, `range` = (100 - round((now_price - lowest) / (highest - lowest), 4) * 100) where code = %s"
my.update_one(update_ratio_sql, code)
update_strategy = "update pool set strategy = (select group_concat(type) from strategy where code = %s and date = (select date FROM stock_price where code = %s order by date desc limit 1)) where code = %s"
my.update_one(update_strategy, (code, code, code))
def strategy_volume_large(limit=5):
stocks_sql = "select code,short_name,highest,lowest from security"
stocks = my.select_all(stocks_sql, ())
large_list = []
for index_stock in stocks:
code = index_stock['code']
price_sql = "select date, volume,close from stock_price where code = %s order by date desc limit %s"
large_data = my.select_all(price_sql, (code, limit + 1))
total = 0
for index in range(len(large_data)):
if index == 0:
continue
total += float(large_data[index]['volume'])
avg = round(total / limit, 4)
now_volume = float(large_data[0]['volume'])
if now_volume > avg * 3:
short_name = index_stock['short_name']
now_date = large_data[0]['date']
now_close = float(large_data[0]['close'])
highest = float(index_stock['highest'])
lowest = float(index_stock['lowest'])
now_range = _get_range(now_close, highest, lowest)
large_value = (code, short_name, now_date, '大量', now_close,
highest, lowest, now_range)
print(large_value)
large_list.append(large_value)
insert_sql = "insert into strategy(code, company_name, date, type , price, highest, lowest, `range`)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, large_list)
def ini_stock_concept():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
for stock_code in stock_codes:
code = stock_code['code']
stock_concept_data = sdk.get_concept(code, '2020-10-30')
stock_concept_list = []
stock_concept = stock_concept_data[code]['jq_concept']
for concept in stock_concept:
concept_data = (code, concept['concept_code'],
concept['concept_name'], 1)
print(concept_data)
stock_concept_list.append(concept_data)
insert_sql = "insert into stock_concept(code, concept_code, concept_name, status) values (%s, %s, %s, %s)"
my.insert_many(insert_sql, stock_concept_list)
def init_stock_info():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
stock_infos = []
for stock_code in stock_codes:
code = stock_code['code']
company_info = finance.run_query(
query(finance.STK_COMPANY_INFO).filter(
finance.STK_COMPANY_INFO.code == code).limit(1))
company_id = int(company_info.iloc[0]['company_id'])
full_name = company_info.iloc[0]['full_name']
short_name = company_info.iloc[0]['short_name']
register_location = company_info.iloc[0]['register_location']
office_address = company_info.iloc[0]['office_address']
register_capital = is_nan(
float(company_info.iloc[0]['register_capital']))
main_business = company_info.iloc[0]['main_business']
business_scope = company_info.iloc[0]['business_scope']
description = company_info.iloc[0]['description']
province = company_info.iloc[0]['province']
city = company_info.iloc[0]['city']
comments = company_info.iloc[0]['comments']
stock_info = (company_id, full_name, short_name, register_location,
office_address, register_capital, main_business,
business_scope, description, province, city, comments,
code)
print(stock_info)
stock_infos.append(stock_info)
update_stock_info_sql = "update security set company_id = %s, full_name = %s, short_name = %s, register_location = %s, office_address = %s," \
" register_capital = %s, main_business = %s, business_scope = %s, description = %s, province = %s, city = %s, comments = %s" \
" where code = %s"
my.update_many(update_stock_info_sql, stock_infos)
def del_st(date):
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
st_list = []
for stock_code in stock_codes:
code = stock_code['code']
st_data = sdk.get_extras('is_st', [code],
start_date=date,
end_date=date)
result = st_data.iloc[0][code]
if result:
print('%s 结果为：%s', code, result)
st = code
st_list.append(st)
print(st_list)
del_sql = "delete from security where code in (%s)"
my.delete_many(del_sql, st_list)
def init_index_price(limit=100):
industry_sql = "select code from industry where type = 'sw_l1'"
industry_codes = my.select_all(industry_sql, ())
jq.login()
index_price_list = []
for industry_code in industry_codes:
code = industry_code['code']
jq1_price_data = finance.run_query(
sdk.query(finance.SW1_DAILY_PRICE).filter(
finance.SW1_DAILY_PRICE.code == code).order_by(
finance.SW1_DAILY_PRICE.date.desc()).limit(limit))
for index in jq1_price_data.index:
index_jq1_price = jq1_price_data.iloc[index]
name = index_jq1_price['name']
code = index_jq1_price['code']
date = index_jq1_price['date'].strftime('%Y-%m-%d')
open = float(index_jq1_price['open'])
high = float(index_jq1_price['high'])
low = float(index_jq1_price['low'])
close = float(index_jq1_price['close'])
volume = float(index_jq1_price['volume'])
money = float(index_jq1_price['money'])
change_pct = float(index_jq1_price['change_pct'])
jq1_price = (name, code, date, open, high, low, close, volume,
money, change_pct)
print(jq1_price)
index_price_list.append(jq1_price)
insert_sql = "insert into index_price(name, code, date, open, high, low, close, volume, money, change_pct) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, index_price_list)
def get_cash_flow():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
cash_flow_list = []
for stock_code in stock_codes:
code = stock_code['code']
cash_flow_data = finance.run_query(
sdk.query(finance.STK_CASHFLOW_STATEMENT).
filter(finance.STK_CASHFLOW_STATEMENT.code == code).filter(
finance.STK_CASHFLOW_STATEMENT.report_type == 0).order_by(
finance.STK_CASHFLOW_STATEMENT.pub_date.desc()).limit(1))
if cash_flow_data.empty:
continue
index_cash_flow = cash_flow_data.iloc[0]
pub_date = index_cash_flow['pub_date'].strftime('%Y-%m-%d')
exist_sql = "select count(1) count from cash_flow where code = %s and pub_date = %s"
exist = my.select_one(exist_sql, (code, pub_date))
if exist['count'] > 0:
print('%s had init', code)
continue
company_name = index_cash_flow['company_name']
start_date = index_cash_flow['start_date'].strftime('%Y-%m-%d')
end_date = index_cash_flow['end_date'].strftime('%Y-%m-%d')
goods_sale_and_service_render_cash = float(
is_none(index_cash_flow['goods_sale_and_service_render_cash']))
tax_levy_refund = float(is_none(index_cash_flow['tax_levy_refund']))
subtotal_operate_cash_inflow = float(
is_none(index_cash_flow['subtotal_operate_cash_inflow']))
goods_and_services_cash_paid = float(
is_none(index_cash_flow['goods_and_services_cash_paid']))
staff_behalf_paid = float(is_none(
index_cash_flow['staff_behalf_paid']))
tax_payments = float(is_none(index_cash_flow['tax_payments']))
subtotal_operate_cash_outflow = float(
is_none(index_cash_flow['subtotal_operate_cash_outflow']))
net_operate_cash_flow = float(
is_none(index_cash_flow['net_operate_cash_flow']))
invest_withdrawal_cash = float(
is_none(index_cash_flow['invest_withdrawal_cash']))
invest_proceeds = float(is_none(index_cash_flow['invest_proceeds']))
fix_intan_other_asset_dispo_cash = float(
is_none(index_cash_flow['fix_intan_other_asset_dispo_cash']))
net_cash_deal_subcompany = float(
is_none(index_cash_flow['net_cash_deal_subcompany']))
subtotal_invest_cash_inflow = float(
is_none(index_cash_flow['subtotal_invest_cash_inflow']))
fix_intan_other_asset_acqui_cash = float(
is_none(index_cash_flow['fix_intan_other_asset_acqui_cash']))
invest_cash_paid = float(is_none(index_cash_flow['invest_cash_paid']))
impawned_loan_net_increase = float(
is_none(index_cash_flow['impawned_loan_net_increase']))
net_cash_from_sub_company = float(
is_none(index_cash_flow['net_cash_from_sub_company']))
subtotal_invest_cash_outflow = float(
is_none(index_cash_flow['subtotal_invest_cash_outflow']))
net_invest_cash_flow = float(
is_none(index_cash_flow['net_invest_cash_flow']))
cash_from_invest = float(is_none(index_cash_flow['cash_from_invest']))
cash_from_borrowing = float(
is_none(index_cash_flow['cash_from_borrowing']))
cash_from_bonds_issue = float(
is_none(index_cash_flow['cash_from_bonds_issue']))
subtotal_finance_cash_inflow = float(
is_none(index_cash_flow['subtotal_finance_cash_inflow']))
borrowing_repayment = float(
is_none(index_cash_flow['borrowing_repayment']))
dividend_interest_payment = float(
is_none(index_cash_flow['dividend_interest_payment']))
subtotal_finance_cash_outflow = float(
is_none(index_cash_flow['subtotal_finance_cash_outflow']))
net_finance_cash_flow = float(
is_none(index_cash_flow['net_finance_cash_flow']))
exchange_rate_change_effect = float(
is_none(index_cash_flow['exchange_rate_change_effect']))
other_reason_effect_cash = float(
is_none(index_cash_flow['other_reason_effect_cash']))
cash_equivalent_increase = float(
is_none(index_cash_flow['cash_equivalent_increase']))
cash_equivalents_at_beginning = float(
is_none(index_cash_flow['cash_equivalents_at_beginning']))
cash_and_equivalents_at_end = float(
is_none(index_cash_flow['cash_and_equivalents_at_end']))
net_profit = float(is_none(index_cash_flow['net_profit']))
assets_depreciation_reserves = float(
is_none(index_cash_flow['assets_depreciation_reserves']))
fixed_assets_depreciation = float(
is_none(index_cash_flow['fixed_assets_depreciation']))
intangible_assets_amortization = float(
is_none(index_cash_flow['intangible_assets_amortization']))
defferred_expense_amortization = float(
is_none(index_cash_flow['defferred_expense_amortization']))
fix_intan_other_asset_dispo_loss = float(
is_none(index_cash_flow['fix_intan_other_asset_dispo_loss']))
fixed_asset_scrap_loss = float(
is_none(index_cash_flow['fixed_asset_scrap_loss']))
fair_value_change_loss = float(
is_none(index_cash_flow['fair_value_change_loss']))
financial_cost = float(is_none(index_cash_flow['financial_cost']))
invest_loss = float(is_none(index_cash_flow['invest_loss']))
deffered_tax_asset_decrease = float(
is_none(index_cash_flow['deffered_tax_asset_decrease']))
deffered_tax_liability_increase = float(
is_none(index_cash_flow['deffered_tax_liability_increase']))
inventory_decrease = float(
is_none(index_cash_flow['inventory_decrease']))
operate_receivables_decrease = float(
is_none(index_cash_flow['operate_receivables_decrease']))
operate_payable_increase = float(
is_none(index_cash_flow['operate_payable_increase']))
others = float(is_none(index_cash_flow['others']))
net_operate_cash_flow_indirect = float(
is_none(index_cash_flow['net_operate_cash_flow_indirect']))
debt_to_capital = float(is_none(index_cash_flow['debt_to_capital']))
cbs_expiring_in_one_year = float(
is_none(index_cash_flow['cbs_expiring_in_one_year']))
financial_lease_fixed_assets = float(
is_none(index_cash_flow['financial_lease_fixed_assets']))
cash_at_end = float(is_none(index_cash_flow['cash_at_end']))
cash_at_beginning = float(is_none(
index_cash_flow['cash_at_beginning']))
equivalents_at_end = float(
is_none(index_cash_flow['equivalents_at_end']))
equivalents_at_beginning = float(
is_none(index_cash_flow['equivalents_at_beginning']))
other_reason_effect_cash_indirect = float(
is_none(index_cash_flow['other_reason_effect_cash_indirect']))
cash_equivalent_increase_indirect = float(
is_none(index_cash_flow['cash_equivalent_increase_indirect']))
net_deposit_increase = float(
is_none(index_cash_flow['net_deposit_increase']))
net_borrowing_from_central_bank = float(
is_none(index_cash_flow['net_borrowing_from_central_bank']))
net_borrowing_from_finance_co = float(
is_none(index_cash_flow['net_borrowing_from_finance_co']))
net_original_insurance_cash = float(
is_none(index_cash_flow['net_original_insurance_cash']))
net_cash_received_from_reinsurance_business = float(
is_none(
index_cash_flow['net_cash_received_from_reinsurance_business'])
)
net_insurer_deposit_investment = float(
is_none(index_cash_flow['net_insurer_deposit_investment']))
net_deal_trading_assets = float(
is_none(index_cash_flow['net_deal_trading_assets']))
interest_and_commission_cashin = float(
is_none(index_cash_flow['interest_and_commission_cashin']))
net_increase_in_placements = float(
is_none(index_cash_flow['net_increase_in_placements']))
net_buyback = float(is_none(index_cash_flow['net_buyback']))
net_loan_and_advance_increase = float(
is_none(index_cash_flow['net_loan_and_advance_increase']))
net_deposit_in_cb_and_ib = float(
is_none(index_cash_flow['net_deposit_in_cb_and_ib']))
original_compensation_paid = float(
is_none(index_cash_flow['original_compensation_paid']))
handling_charges_and_commission = float(
is_none(index_cash_flow['handling_charges_and_commission']))
policy_dividend_cash_paid = float(
is_none(index_cash_flow['policy_dividend_cash_paid']))
cash_from_mino_s_invest_sub = float(
is_none(index_cash_flow['cash_from_mino_s_invest_sub']))
proceeds_from_sub_to_mino_s = float(
is_none(index_cash_flow['proceeds_from_sub_to_mino_s']))
investment_property_depreciation = float(
is_none(index_cash_flow['investment_property_depreciation']))
# pd.set_option('display.max_columns', None)
# pd.set_option('display.max_rows', None)
# pd.set_option('max_colwidth', 100)
cash_flow = (
code, company_name, pub_date, start_date, end_date,
goods_sale_and_service_render_cash, tax_levy_refund,
subtotal_operate_cash_inflow, goods_and_services_cash_paid,
staff_behalf_paid, tax_payments, subtotal_operate_cash_outflow,
net_operate_cash_flow, invest_withdrawal_cash, invest_proceeds,
fix_intan_other_asset_dispo_cash, net_cash_deal_subcompany,
subtotal_invest_cash_inflow, fix_intan_other_asset_acqui_cash,
invest_cash_paid, impawned_loan_net_increase,
net_cash_from_sub_company, subtotal_invest_cash_outflow,
net_invest_cash_flow, cash_from_invest, cash_from_borrowing,
cash_from_bonds_issue, subtotal_finance_cash_inflow,
borrowing_repayment, dividend_interest_payment,
subtotal_finance_cash_outflow, net_finance_cash_flow,
exchange_rate_change_effect, other_reason_effect_cash,
cash_equivalent_increase, cash_equivalents_at_beginning,
cash_and_equivalents_at_end, net_profit,
assets_depreciation_reserves, fixed_assets_depreciation,
intangible_assets_amortization, defferred_expense_amortization,
fix_intan_other_asset_dispo_loss, fixed_asset_scrap_loss,
fair_value_change_loss, financial_cost, invest_loss,
deffered_tax_asset_decrease, deffered_tax_liability_increase,
inventory_decrease, operate_receivables_decrease,
operate_payable_increase, others, net_operate_cash_flow_indirect,
debt_to_capital, cbs_expiring_in_one_year,
financial_lease_fixed_assets, cash_at_end, cash_at_beginning,
equivalents_at_end, equivalents_at_beginning,
other_reason_effect_cash_indirect,
cash_equivalent_increase_indirect, net_deposit_increase,
net_borrowing_from_central_bank, net_borrowing_from_finance_co,
net_original_insurance_cash,
net_cash_received_from_reinsurance_business,
net_insurer_deposit_investment, net_deal_trading_assets,
interest_and_commission_cashin, net_increase_in_placements,
net_buyback, net_loan_and_advance_increase,
net_deposit_in_cb_and_ib, original_compensation_paid,
handling_charges_and_commission, policy_dividend_cash_paid,
cash_from_mino_s_invest_sub, proceeds_from_sub_to_mino_s,
investment_property_depreciation)
print(cash_flow)
cash_flow_list.append(cash_flow)
insert_sql = "insert into cash_flow(code, company_name, pub_date, start_date, end_date," \
" goods_sale_and_service_render_cash, tax_levy_refund," \
" subtotal_operate_cash_inflow, goods_and_services_cash_paid," \
" staff_behalf_paid, tax_payments, subtotal_operate_cash_outflow," \
" net_operate_cash_flow, invest_withdrawal_cash, invest_proceeds," \
" fix_intan_other_asset_dispo_cash, net_cash_deal_subcompany," \
" subtotal_invest_cash_inflow, fix_intan_other_asset_acqui_cash," \
" invest_cash_paid, impawned_loan_net_increase," \
" net_cash_from_sub_company, subtotal_invest_cash_outflow," \
" net_invest_cash_flow, cash_from_invest, cash_from_borrowing," \
" cash_from_bonds_issue, subtotal_finance_cash_inflow," \
" borrowing_repayment, dividend_interest_payment," \
" subtotal_finance_cash_outflow, net_finance_cash_flow," \
" exchange_rate_change_effect, other_reason_effect_cash," \
" cash_equivalent_increase, cash_equivalents_at_beginning," \
" cash_and_equivalents_at_end, net_profit," \
" assets_depreciation_reserves, fixed_assets_depreciation," \
" intangible_assets_amortization, defferred_expense_amortization," \
" fix_intan_other_asset_dispo_loss, fixed_asset_scrap_loss," \
" fair_value_change_loss, financial_cost, invest_loss," \
" deffered_tax_asset_decrease, deffered_tax_liability_increase," \
" inventory_decrease, operate_receivables_decrease," \
" operate_payable_increase, others, net_operate_cash_flow_indirect," \
" debt_to_capital, cbs_expiring_in_one_year," \
" financial_lease_fixed_assets, cash_at_end, cash_at_beginning," \
" equivalents_at_end, equivalents_at_beginning," \
" other_reason_effect_cash_indirect," \
" cash_equivalent_increase_indirect, net_deposit_increase," \
" net_borrowing_from_central_bank, net_borrowing_from_finance_co," \
" net_original_insurance_cash," \
" net_cash_received_from_reinsurance_business," \
" net_insurer_deposit_investment, net_deal_trading_assets," \
" interest_and_commission_cashin, net_increase_in_placements," \
" net_buyback, net_loan_and_advance_increase," \
" net_deposit_in_cb_and_ib, original_compensation_paid," \
" handling_charges_and_commission, policy_dividend_cash_paid," \
" cash_from_mino_s_invest_sub, proceeds_from_sub_to_mino_s," \
" investment_property_depreciation) " \
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, cash_flow_list)
def get_fundamentals(date):
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
fundamental_list = []
for stock_code in stock_codes:
code = stock_code['code']
fundamental_data = sdk.get_fundamentals(
sdk.query(sdk.indicator).filter(sdk.valuation.code == code), date)
if fundamental_data.empty:
continue
index_fundamental = fundamental_data.iloc[0]
pubDate = index_fundamental['pubDate']
eps = is_nan(float(index_fundamental['eps']))
adjusted_profit = is_nan(float(index_fundamental['adjusted_profit']))
operating_profit = is_nan(float(index_fundamental['operating_profit']))
value_change_profit = is_nan(
float(index_fundamental['value_change_profit']))
roe = is_nan(float(index_fundamental['roe']))
inc_return = is_nan(float(index_fundamental['inc_return']))
roa = is_nan(float(index_fundamental['roa']))
net_profit_margin = is_nan(
float(index_fundamental['net_profit_margin']))
gross_profit_margin = is_nan(
float(index_fundamental['gross_profit_margin']))
expense_to_total_revenue = is_nan(
float(index_fundamental['expense_to_total_revenue']))
operation_profit_to_total_revenue = is_nan(
float(index_fundamental['operation_profit_to_total_revenue']))
net_profit_to_total_revenue = is_nan(
float(index_fundamental['net_profit_to_total_revenue']))
operating_expense_to_total_revenue = is_nan(
float(index_fundamental['operating_expense_to_total_revenue']))
ga_expense_to_total_revenue = is_nan(
float(index_fundamental['ga_expense_to_total_revenue']))
financing_expense_to_total_revenue = is_nan(
float(index_fundamental['financing_expense_to_total_revenue']))
operating_profit_to_profit = is_nan(
float(index_fundamental['operating_profit_to_profit']))
invesment_profit_to_profit = is_nan(
float(index_fundamental['invesment_profit_to_profit']))
adjusted_profit_to_profit = is_nan(
float(index_fundamental['adjusted_profit_to_profit']))
goods_sale_and_service_to_revenue = is_nan(
float(index_fundamental['goods_sale_and_service_to_revenue']))
ocf_to_revenue = is_nan(float(index_fundamental['ocf_to_revenue']))
ocf_to_operating_profit = is_nan(
float(index_fundamental['ocf_to_operating_profit']))
inc_total_revenue_year_on_year = is_nan(
float(index_fundamental['inc_total_revenue_year_on_year']))
inc_total_revenue_annual = is_nan(
float(index_fundamental['inc_total_revenue_annual']))
inc_revenue_year_on_year = is_nan(
float(index_fundamental['inc_revenue_year_on_year']))
inc_revenue_annual = is_nan(
float(index_fundamental['inc_revenue_annual']))
inc_operation_profit_year_on_year = is_nan(
float(index_fundamental['inc_operation_profit_year_on_year']))
inc_operation_profit_annual = is_nan(
float(index_fundamental['inc_operation_profit_annual']))
inc_net_profit_year_on_year = is_nan(
float(index_fundamental['inc_net_profit_year_on_year']))
inc_net_profit_annual = is_nan(
float(index_fundamental['inc_net_profit_annual']))
inc_net_profit_to_shareholders_year_on_year = is_nan(
float(index_fundamental[
'inc_net_profit_to_shareholders_year_on_year']))
inc_net_profit_to_shareholders_annual = is_nan(
float(index_fundamental['inc_net_profit_to_shareholders_annual']))
fundamental = (
code, pubDate, eps, adjusted_profit, operating_profit,
value_change_profit, roe, inc_return, roa, net_profit_margin,
gross_profit_margin, expense_to_total_revenue,
operation_profit_to_total_revenue, net_profit_to_total_revenue,
operating_expense_to_total_revenue, ga_expense_to_total_revenue,
financing_expense_to_total_revenue, operating_profit_to_profit,
invesment_profit_to_profit, adjusted_profit_to_profit,
goods_sale_and_service_to_revenue, ocf_to_revenue,
ocf_to_operating_profit, inc_total_revenue_year_on_year,
inc_total_revenue_annual, inc_revenue_year_on_year,
inc_revenue_annual, inc_operation_profit_year_on_year,
inc_operation_profit_annual, inc_net_profit_year_on_year,
inc_net_profit_annual, inc_net_profit_to_shareholders_year_on_year,
inc_net_profit_to_shareholders_annual)
fundamental_list.append(fundamental)
print(fundamental)
insert_sql = "insert into fundamental(code, pubDate, eps, adjusted_profit, operating_profit, value_change_profit, roe," \
" inc_return, roa, net_profit_margin, gross_profit_margin, expense_to_total_revenue, " \
" operation_profit_to_total_revenue, net_profit_to_total_revenue, operating_expense_to_total_revenue, " \
" ga_expense_to_total_revenue,financing_expense_to_total_revenue,operating_profit_to_profit, invesment_profit_to_profit, " \
" adjusted_profit_to_profit,goods_sale_and_service_to_revenue, ocf_to_revenue, ocf_to_operating_profit," \
" inc_total_revenue_year_on_year,inc_total_revenue_annual, inc_revenue_year_on_year, inc_revenue_annual," \
" inc_operation_profit_year_on_year,inc_operation_profit_annual, inc_net_profit_year_on_year, inc_net_profit_annual," \
" inc_net_profit_to_shareholders_year_on_year,inc_net_profit_to_shareholders_annual) " \
" values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, fundamental_list)
def get_balance_sheet():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
balance_sheet_list = []
for stock_code in stock_codes:
code = stock_code['code']
balance_sheet_data = finance.run_query(
sdk.query(finance.STK_BALANCE_SHEET).filter(
finance.STK_BALANCE_SHEET.code == code).filter(
finance.STK_BALANCE_SHEET.report_type == 0).order_by(
finance.STK_BALANCE_SHEET.pub_date.desc()).limit(1))
if balance_sheet_data.empty:
continue
index_balance_sheet = balance_sheet_data.iloc[0]
pub_date = index_balance_sheet['pub_date'].strftime('%Y-%m-%d')
# exist_sql = "select count(1) count from balance_sheet where code = %s and pub_date = %s"
# exist = my.select_one(exist_sql, (code, pub_date))
# if exist['count'] > 0:
# print('%s had init', code)
# continue
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('max_colwidth', 120)
company_name = index_balance_sheet['company_name']
start_date = index_balance_sheet['start_date'].strftime('%Y-%m-%d')
end_date = index_balance_sheet['end_date'].strftime('%Y-%m-%d')
cash_equivalents = float(
is_none(index_balance_sheet['cash_equivalents']))
trading_assets = float(is_none(index_balance_sheet['trading_assets']))
bill_receivable = float(is_none(
index_balance_sheet['bill_receivable']))
account_receivable = float(
is_none(index_balance_sheet['account_receivable']))
advance_payment = float(is_none(
index_balance_sheet['advance_payment']))
other_receivable = float(
is_none(index_balance_sheet['other_receivable']))
affiliated_company_receivable = float(
is_none(index_balance_sheet['affiliated_company_receivable']))
interest_receivable = float(
is_none(index_balance_sheet['interest_receivable']))
dividend_receivable = float(
is_none(index_balance_sheet['dividend_receivable']))
inventories = float(is_none(index_balance_sheet['inventories']))
expendable_biological_asset = float(
is_none(index_balance_sheet['expendable_biological_asset']))
non_current_asset_in_one_year = float(
is_none(index_balance_sheet['non_current_asset_in_one_year']))
total_current_assets = float(
is_none(index_balance_sheet['total_current_assets']))
hold_for_sale_assets = float(
is_none(index_balance_sheet['hold_for_sale_assets']))
hold_to_maturity_investments = float(
is_none(index_balance_sheet['hold_to_maturity_investments']))
longterm_receivable_account = float(
is_none(index_balance_sheet['longterm_receivable_account']))
longterm_equity_invest = float(
is_none(index_balance_sheet['longterm_equity_invest']))
investment_property = float(
is_none(index_balance_sheet['investment_property']))
fixed_assets = float(is_none(index_balance_sheet['fixed_assets']))
constru_in_process = float(
is_none(index_balance_sheet['constru_in_process']))
construction_materials = float(
is_none(index_balance_sheet['construction_materials']))
fixed_assets_liquidation = float(
is_none(index_balance_sheet['fixed_assets_liquidation']))
biological_assets = float(
is_none(index_balance_sheet['biological_assets']))
oil_gas_assets = float(is_none(index_balance_sheet['oil_gas_assets']))
intangible_assets = float(
is_none(index_balance_sheet['intangible_assets']))
development_expenditure = float(
is_none(index_balance_sheet['development_expenditure']))
good_will = float(is_none(index_balance_sheet['good_will']))
long_deferred_expense = float(
is_none(index_balance_sheet['long_deferred_expense']))
deferred_tax_assets = float(
is_none(index_balance_sheet['deferred_tax_assets']))
total_non_current_assets = float(
is_none(index_balance_sheet['total_non_current_assets']))
total_assets = float(is_none(index_balance_sheet['total_assets']))
shortterm_loan = float(is_none(index_balance_sheet['shortterm_loan']))
trading_liability = float(
is_none(index_balance_sheet['trading_liability']))
notes_payable = float(is_none(index_balance_sheet['notes_payable']))
accounts_payable = float(
is_none(index_balance_sheet['accounts_payable']))
advance_peceipts = float(
is_none(index_balance_sheet['advance_peceipts']))
salaries_payable = float(
is_none(index_balance_sheet['salaries_payable']))
taxs_payable = float(is_none(index_balance_sheet['taxs_payable']))
interest_payable = float(
is_none(index_balance_sheet['interest_payable']))
dividend_payable = float(
is_none(index_balance_sheet['dividend_payable']))
other_payable = float(is_none(index_balance_sheet['other_payable']))
affiliated_company_payable = float(
is_none(index_balance_sheet['affiliated_company_payable']))
non_current_liability_in_one_year = float(
is_none(index_balance_sheet['non_current_liability_in_one_year']))
total_current_liability = float(
is_none(index_balance_sheet['total_current_liability']))
longterm_loan = float(is_none(index_balance_sheet['longterm_loan']))
bonds_payable = float(is_none(index_balance_sheet['bonds_payable']))
longterm_account_payable = float(
is_none(index_balance_sheet['longterm_account_payable']))
specific_account_payable = float(
is_none(index_balance_sheet['specific_account_payable']))
estimate_liability = float(
is_none(index_balance_sheet['estimate_liability']))
deferred_tax_liability = float(
is_none(index_balance_sheet['deferred_tax_liability']))
total_non_current_liability = float(
is_none(index_balance_sheet['total_non_current_liability']))
total_liability = float(is_none(
index_balance_sheet['total_liability']))
paidin_capital = float(is_none(index_balance_sheet['paidin_capital']))
capital_reserve_fund = float(
is_none(index_balance_sheet['capital_reserve_fund']))
specific_reserves = float(
is_none(index_balance_sheet['specific_reserves']))
surplus_reserve_fund = float(
is_none(index_balance_sheet['surplus_reserve_fund']))
treasury_stock = float(is_none(index_balance_sheet['treasury_stock']))
retained_profit = float(is_none(
index_balance_sheet['retained_profit']))
equities_parent_company_owners = float(
is_none(index_balance_sheet['equities_parent_company_owners']))
minority_interests = float(
is_none(index_balance_sheet['minority_interests']))
foreign_currency_report_conv_diff = float(
is_none(index_balance_sheet['foreign_currency_report_conv_diff']))
irregular_item_adjustment = float(
is_none(index_balance_sheet['irregular_item_adjustment']))
total_owner_equities = float(
is_none(index_balance_sheet['total_owner_equities']))
total_sheet_owner_equities = float(
is_none(index_balance_sheet['total_sheet_owner_equities']))
other_comprehensive_income = float(
is_none(index_balance_sheet['other_comprehensive_income']))
deferred_earning = float(
is_none(index_balance_sheet['deferred_earning']))
settlement_provi = float(
is_none(index_balance_sheet['settlement_provi']))
lend_capital = float(is_none(index_balance_sheet['lend_capital']))
loan_and_advance_current_assets = float(
is_none(index_balance_sheet['loan_and_advance_current_assets']))
derivative_financial_asset = float(
is_none(index_balance_sheet['derivative_financial_asset']))
insurance_receivables = float(
is_none(index_balance_sheet['insurance_receivables']))
reinsurance_receivables = float(
is_none(index_balance_sheet['reinsurance_receivables']))
reinsurance_contract_reserves_receivable = float(
is_none(
index_balance_sheet['reinsurance_contract_reserves_receivable']
))
bought_sellback_assets = float(
is_none(index_balance_sheet['bought_sellback_assets']))
hold_sale_asset = float(is_none(
index_balance_sheet['hold_sale_asset']))
loan_and_advance_noncurrent_assets = float(
is_none(index_balance_sheet['loan_and_advance_noncurrent_assets']))
borrowing_from_centralbank = float(
is_none(index_balance_sheet['borrowing_from_centralbank']))
deposit_in_interbank = float(
is_none(index_balance_sheet['deposit_in_interbank']))
borrowing_capital = float(
is_none(index_balance_sheet['borrowing_capital']))
derivative_financial_liability = float(
is_none(index_balance_sheet['derivative_financial_liability']))
sold_buyback_secu_proceeds = float(
is_none(index_balance_sheet['sold_buyback_secu_proceeds']))
commission_payable = float(
is_none(index_balance_sheet['commission_payable']))
reinsurance_payables = float(
is_none(index_balance_sheet['reinsurance_payables']))
insurance_contract_reserves = float(
is_none(index_balance_sheet['insurance_contract_reserves']))
proxy_secu_proceeds = float(
is_none(index_balance_sheet['proxy_secu_proceeds']))
receivings_from_vicariously_sold_securities = float(
is_none(index_balance_sheet[
'receivings_from_vicariously_sold_securities']))
hold_sale_liability = float(
is_none(index_balance_sheet['hold_sale_liability']))
estimate_liability_current = float(
is_none(index_balance_sheet['estimate_liability_current']))
deferred_earning_current = float(
is_none(index_balance_sheet['deferred_earning_current']))
preferred_shares_noncurrent = float(
is_none(index_balance_sheet['preferred_shares_noncurrent']))
pepertual_liability_noncurrent = float(
is_none(index_balance_sheet['pepertual_liability_noncurrent']))
longterm_salaries_payable = float(
is_none(index_balance_sheet['longterm_salaries_payable']))
other_equity_tools = float(
is_none(index_balance_sheet['other_equity_tools']))
preferred_shares_equity = float(
is_none(index_balance_sheet['preferred_shares_equity']))
pepertual_liability_equity = float(
is_none(index_balance_sheet['pepertual_liability_equity']))
balance_sheet = (
code, company_name, pub_date, start_date, end_date,
cash_equivalents, trading_assets, bill_receivable,
account_receivable, advance_payment, other_receivable,
affiliated_company_receivable, interest_receivable,
dividend_receivable, inventories, expendable_biological_asset,
non_current_asset_in_one_year, total_current_assets,
hold_for_sale_assets, hold_to_maturity_investments,
longterm_receivable_account, longterm_equity_invest,
investment_property, fixed_assets, constru_in_process,
construction_materials, fixed_assets_liquidation,
biological_assets, oil_gas_assets, intangible_assets,
development_expenditure, good_will, long_deferred_expense,
deferred_tax_assets, total_non_current_assets, total_assets,
shortterm_loan, trading_liability, notes_payable, accounts_payable,
advance_peceipts, salaries_payable, taxs_payable, interest_payable,
dividend_payable, other_payable, affiliated_company_payable,
non_current_liability_in_one_year, total_current_liability,
longterm_loan, bonds_payable, longterm_account_payable,
specific_account_payable, estimate_liability,
deferred_tax_liability, total_non_current_liability,
total_liability, paidin_capital, capital_reserve_fund,
specific_reserves, surplus_reserve_fund, treasury_stock,
retained_profit, equities_parent_company_owners,
minority_interests, foreign_currency_report_conv_diff,
irregular_item_adjustment, total_owner_equities,
total_sheet_owner_equities, other_comprehensive_income,
deferred_earning, settlement_provi, lend_capital,
loan_and_advance_current_assets, derivative_financial_asset,
insurance_receivables, reinsurance_receivables,
reinsurance_contract_reserves_receivable, bought_sellback_assets,
hold_sale_asset, loan_and_advance_noncurrent_assets,
borrowing_from_centralbank, deposit_in_interbank,
borrowing_capital, derivative_financial_liability,
sold_buyback_secu_proceeds, commission_payable,
reinsurance_payables, insurance_contract_reserves,
proxy_secu_proceeds, receivings_from_vicariously_sold_securities,
hold_sale_liability, estimate_liability_current,
deferred_earning_current, preferred_shares_noncurrent,
pepertual_liability_noncurrent, longterm_salaries_payable,
other_equity_tools, preferred_shares_equity,
pepertual_liability_equity)
print(balance_sheet)
balance_sheet_list.append(balance_sheet)
insert_sql = "insert into balance_sheet(code, company_name, pub_date, start_date, end_date, cash_equivalents, trading_assets, bill_receivable," \
" account_receivable, advance_payment, other_receivable," \
" affiliated_company_receivable, interest_receivable, dividend_receivable, inventories," \
" expendable_biological_asset, non_current_asset_in_one_year, total_current_assets, hold_for_sale_assets," \
" hold_to_maturity_investments, longterm_receivable_account, longterm_equity_invest, investment_property," \
" fixed_assets, constru_in_process, construction_materials," \
" fixed_assets_liquidation, biological_assets, oil_gas_assets, intangible_assets, development_expenditure," \
" good_will, long_deferred_expense, deferred_tax_assets, total_non_current_assets," \
" total_assets, shortterm_loan, trading_liability, notes_payable, accounts_payable, advance_peceipts," \
" salaries_payable, taxs_payable, interest_payable, dividend_payable," \
" other_payable, affiliated_company_payable, non_current_liability_in_one_year, total_current_liability," \
" longterm_loan, bonds_payable, longterm_account_payable, specific_account_payable," \
" estimate_liability, deferred_tax_liability, total_non_current_liability, total_liability, paidin_capital," \
" capital_reserve_fund, specific_reserves, surplus_reserve_fund, treasury_stock," \
" retained_profit, equities_parent_company_owners, minority_interests, foreign_currency_report_conv_diff," \
" irregular_item_adjustment, total_owner_equities, total_sheet_owner_equities," \
" other_comprehensive_income, deferred_earning, settlement_provi, lend_capital," \
" loan_and_advance_current_assets," \
" derivative_financial_asset, insurance_receivables, reinsurance_receivables," \
" reinsurance_contract_reserves_receivable, bought_sellback_assets, hold_sale_asset," \
" loan_and_advance_noncurrent_assets, borrowing_from_centralbank, deposit_in_interbank, borrowing_capital," \
" derivative_financial_liability, sold_buyback_secu_proceeds, commission_payable, reinsurance_payables," \
" insurance_contract_reserves, proxy_secu_proceeds, receivings_from_vicariously_sold_securities," \
" hold_sale_liability, estimate_liability_current, deferred_earning_current, preferred_shares_noncurrent," \
" pepertual_liability_noncurrent, longterm_salaries_payable, other_equity_tools," \
" preferred_shares_equity, pepertual_liability_equity)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, balance_sheet_list)