def db_fetch_composition_list(conn, y, m ):
s = '''
select code, name, t_day
from CompositionList
where year = %d and month = %d
order by code
''' % ( y , m )
r = conn.execute( alch_text(s) ).fetchall()
# 代码, 名称，交易日
# 0 1 2
tr_list = []
for row in r:
tr = data_struct.TradeRecord()
tr.code = row[0]
tr.name = row[1]
tr.t_day = row[2]
tr_list.append( tr )
return tr_list
def query_dailyline(conn, t_day, code):
s = '''
select close, open , pre_close, paused
from DailyLine
where t_day = '%s' and code='%s'
''' % ( t_day,code )
r = conn.execute( alch_text(s) ).fetchall()
if 0 == len(r):
return None
return r[0]
def db_fetch_funda(conn, start_d, end_d ):
s = '''
select code, stat_date, total_assets, total_liability,
net_operate_cash_flow, net_invest_cash_flow, adjusted_profit, gross_profit_margin
from Fundamentals x
where
x.stat_date >= '%s' and x.stat_date <= '%s'
order by x.code, x.stat_date
''' % ( start_d , end_d )
r = conn.execute( alch_text(s) ).fetchall()
tr_list = []
for row in r:
#print row
tr = data_struct.FundaInfo ()
#代码
tr.code = row[0]
#报告期
tr.stat_date = row[1]
#总资产(元)
tr.total_assets = row[2]
#总负债(元)
tr.total_liability = row[3]
if tr.total_assets is None or tr.total_liability is None :
tr.net_asset = None
else:
tr.net_asset = (tr.total_assets - tr.total_liability) /10000
tr.net_operate_cash_flow = row[4] # 经营活动产生的现金流量净额(元)
tr.net_invest_cash_flow = row[5] # 投资活动产生的现金流量净额(元)
if tr.net_operate_cash_flow is None or tr.net_invest_cash_flow is None:
tr.free_cash_flow = None
else:
tr.free_cash_flow = (tr.net_operate_cash_flow - tr.net_invest_cash_flow) /10000
tr.adjusted_profit = util.null_or_0(row[6]) # 扣除非经常损益后的净利润(元)
tr.gross_profit_margin = util.null_or_0(row[7]) #销售毛利率(%)
tr_list.append(tr)
return tr_list
def query_first_n_dailyline(conn, from_day, code, num):
s = '''
select t_day, close, open, pre_close, factor
from DailyLine
where
t_day >= '%s' and code='%s'
and paused = 0
order by t_day
limit %d
''' % ( from_day,code, num )
r = conn.execute( alch_text(s) ).fetchall()
if 0 == len(r):
return None
return r
def db_save_composition_list(conn, year, compo_m, t_day, composition_list):
trans = conn.begin()
try:
s = alch_text(
'''
delete from CompositionList
where year = :y and month = :m
'''
)
conn.execute( s, y = year, m = compo_m )
for entry in composition_list:
db_save_composition_entry( conn, year, compo_m , t_day, entry)
trans.commit()
except Exception as e:
trans.rollback()
raise e
def query_valuation(engine, code, t_day ):
conn = engine.connect()
s = alch_text(
'''
select * from Valuation
where code = :a and day = :b
'''
)
r = conn.execute( s, a = code, b = t_day ).fetchall()
#print r
if len(r) ==0 :
return None
return r
def query_income(engine, code, statDate ):
conn = engine.connect()
s = alch_text(
'''
select * from Income
where code = :a and statDate = :b
'''
)
r = conn.execute( s, a = code, b = statDate ).fetchall()
#print r
if len(r) ==0 :
return None
return r
def query_paused(engine, code, t_day ):
yyyymmdd= "%d-%02d-%02d" % (t_day.year , t_day.month, t_day.day)
conn = engine.connect()
s = alch_text(
'''
select is_paused from IsPaused
where code = :a and t_day = :b
'''
)
r = conn.execute( s, a = code, b = yyyymmdd).fetchall()
if len(r) ==0 :
return None
return r[0][0]
def db_save_simu_trade_list(conn, year, month, buy_list, sell_list ):
trans = conn.begin()
try:
s = alch_text(
'''
delete from SimuTrade
where year = :y and month = :m
'''
)
conn.execute( s, y = year, m = month )
lb = len(buy_list)
ls = len(sell_list)
if lb != ls:
raise Exception("Error %年%月，买%d条，卖%d条！" \
, year, month
, lb, ls
)
for i in range(lb):
b = buy_list[i]
s = sell_list[i]
if b.code != s.code :
raise Exception ("Error %年%月，第%d行, 买%s，卖%s！" \
, year, month
, i
, b.code, s.code
)
db_save_simu_trade_entry(conn, year, month, b ,s )
trans.commit()
except Exception as e:
trans.rollback()
raise e
return
def db_save_dailyline(engine, code, t_day, open_,close_,high, low,volume,money, high_limit, low_limit, pre_close, paused, factor ):
conn = engine.connect()
global s_metadata
#print s_metadata.tables
T_Dailyline = s_metadata.tables['DailyLine']
trans = conn.begin()
try:
s = alch_text(
'''
delete from DailyLine
where t_day = :t and code = :c
'''
)
conn.execute( s, t = t_day, c = code)
ins = T_Dailyline.insert().values(
code = code
, t_day = t_day
, open = open_
, close = close_
, high = high
, low = low
, volume = volume
, money = money
, high_limit = high_limit
, low_limit = low_limit
, pre_close = pre_close
, paused = paused
, factor = factor
)
r = conn.execute( ins )
trans.commit()
except Exception as e:
trans.rollback()
raise e
def db_save_sub_line(engine, code, t_day, interval, seqno, open_,close_,high, low,volume,money ):
conn = engine.connect()
global s_metadata
#print s_metadata.tables
T_Subline = s_metadata.tables['SubLine']
trans = conn.begin()
try:
s = alch_text(
'''
delete from SubLine
where t_day = :t and code = :c and interval = :iv and seqno = :se
'''
)
conn.execute( s, t = t_day, c = code, iv = interval, se = seqno )
ins = T_Subline.insert().values(
code = code
, t_day = t_day
, interval = interval
, seqno = seqno
, open = open_
, close = close_
, high = high
, low = low
, volume = volume
, money = money
)
r = conn.execute( ins )
trans.commit()
except Exception as e:
trans.rollback()
raise e
def db_save_annual_funda(engine, stat_date, df ):
conn = engine.connect()
trans = conn.begin()
try:
s = alch_text(
'''
delete from Fundamentals
where stat_date = :t
'''
)
conn.execute( s, t = stat_date)
row_num = len(df.index)
for i in range(row_num):
db_save_fundamentals( conn, df.iloc[i] )
trans.commit()
except Exception as e:
trans.rollback()
raise e
def db_fetch_stock_statements(conn,stat_end , t_day, y, m ):
ymd = '%d-%02d-%02d' % (t_day.year, t_day.month, t_day.day )
s = '''
select m.*,e.EBIT,v.market_cap
from tmpEBIT e
join vMagicBalance m on ( e.code = m.code and m.statDate= '%s' )
join Valuation v on ( e.code = v.code and v.day = '%s' )
''' % ( stat_end , ymd )
#print s
r = conn.execute( alch_text(s) ).fetchall()
#util.bp(r)
# 代码, 报表期末日,净运营资本，固定资产，有息负债，少数股东权益，EBIT，市值
# 0 1 2 3 4 5 6 7
sci_list = []
for row in r:
sci = data_struct.StockCandidatorInfo()
sci.year = y
sci.month = m
sci.stat_end = stat_end
sci.code = row[0]
sci.net_op_cap = row[2]
sci.fixed_assets = row[3]
sci.nonfree_liability = row[4]
sci.minority_interests = row[5]
sci.EBIT = row[6]
sci.market_cap = row[7]
if sci.net_op_cap is None:
print "WARN: %s 净运营资本为空，忽略" % sci.code
continue
if sci.fixed_assets is None:
print "WARN: %s 固定资产为空，忽略" % sci.code
continue
# 指标一：ROC = EBIT /（净营运资本 + 固定资产）
a = sci.net_op_cap + sci.fixed_assets
if 0 == a:
print "WARN!!! %s 的'运营资本+固定资产'为0， 指标1 做 0 处理" % sci.code
sci.ROC = 0
else:
sci.ROC = sci.EBIT / a
# 指标二：EY = EBIT /（总市值 + 带息负债 + 其他权益工具 + 少数股东权益）
if sci.market_cap is None:
print "WARN: %s 总市值为空，忽略" % sci.code
continue
if sci.nonfree_liability is None:
print "WARN: %s 有息负债为空，忽略" % sci.code
continue
if sci.minority_interests is None:
print "WARN: %s 少数股东权益为空，忽略" % sci.code
continue
b = sci.market_cap * 100000000 + sci.nonfree_liability + sci.minority_interests
sci.EY = sci.EBIT / b
sci_list.append(sci)
return sci_list
def db_fetch_forcast(conn, start_d, end_d ):
s = '''
select code, end_date, report_type_id, report_type, pub_date,
type_id, type, profit_min, profit_max, profit_last,
profit_ratio_min, profit_ratio_max, content
from Forcast x
where
x.end_date >= '%s' and x.end_date <= '%s'
order by x.code, x.end_date
''' % ( start_d , end_d )
r = conn.execute( alch_text(s) ).fetchall()
tr_list = []
for row in r:
tr = data_struct.ForcastInfo()
#代码
tr.code = row[0]
#报告期
tr.end_date = row[1]
#预告期类型编码: 304001 一季度预告, 304002 中报预告, 304003 三季度预告, 304004 四季度预告
tr.report_type_id = row[2]
#预告期类型
tr.report_type = row[3]
# 公布日期
tr.pub_date = row[4]
#预告类型编码: 305001 业绩大幅上升, 305002 业绩预增, 305003 业绩预盈, 305004 预计扭亏
# 305005 业绩持平, 305006 无大幅变动
# 305007 业绩预亏, 305008 业绩大幅下降, 305009 大幅减亏, 305010 业绩预降, 305011 预计减亏
# 305012 不确定, 305013 取消预测
tr.type_id = row[5]
#预告类型
tr.forcast_type = row[6]
#预告净利润（下限）
tr.profit_min = row[7]
#预告净利润（上限）
tr.profit_max = row[8]
#去年同期净利润
tr.profit_last = row[9]
#预告净利润变动幅度(下限) 单位：%
tr.profit_ratio_min = row[10]
#预告净利润变动幅度(上限) 单位：%
tr.profit_ratio_max = row[11]
#预告内容
tr.content = row[12]
tr_list.append(tr)
return tr_list
def db_fetch_xrxd(conn, start_d, end_d, must_register ):
if must_register:
join_type = ''
else:
join_type = 'left'
s = '''
select x.code, x.report_date
, board_plan_pub_date, board_plan_bonusnote
, shareholders_plan_pub_date, shareholders_plan_bonusnote
, implementation_pub_date, implementation_bonusnote
, a_registration_date, dividend_ratio, transfer_ratio, bonus_ratio_rmb
, distributed_share_base_implement, dividend_number, transfer_number, bonus_amount_rmb
, bonus_amount_rmb/10000/v.market_cap as distr_r, v.market_cap
from XrXd x
%s join Valuation v on (x.code = v.code and x.a_registration_date = v.day )
where
x.report_date >= '%s' and x.report_date <= '%s'
order by x.code, x.report_date
''' % ( join_type, start_d , end_d )
r = conn.execute( alch_text(s) ).fetchall()
tr_list = []
for row in r:
tr = data_struct.XrXdInfo()
#代码
tr.code = row[0]
#报告期
tr.report_date = row[1]
#董事会公告日
tr.board_plan_pub_date = row[2]
#董事会公告方案
tr.board_plan_bonusnote = row[3]
#股东大会公告日
tr.shareholders_plan_pub_date = row[4]
#股东大会方案
tr.shareholders_plan_bonusnote = row[5]
#实施公告日
tr.implementation_pub_date = row[6]
#实施方案
tr.implementation_bonusnote = row[7]
#A股登记日
tr.a_registration_date = row[8]
#每10股送几股
tr.dividend_ratio = row[9]
#每10股转几股
tr.transfer_ratio = row[10]
#每10股派多少
tr.bonus_ratio_rmb = row[11]
#分配基盘(万股)
tr.distributed_share_base_implement = row[12]
#print "分配基盘(万股)" , tr.distributed_share_base_implement
#送股数(万股)
tr.dividend_number = row[13]
#转股数 (万股)
tr.transfer_number = row[14]
#分红额(万元)
tr.bonus_amount_rmb = row[15]
# 股息率
tr.distr_r = row[16]
# 登记日总市值(亿元)
tr.market_cap = row[17]
tr_list.append(tr)
return tr_list
def db_fetch_dailyline_w_valuation_by_code(conn, threshold , code ):
s = '''
select d.t_day, d.code, d.close, d.high_limit, d.pre_close, d.paused, v.pb_ratio, v.turnover_ratio
from DailyLine as d
join Valuation as v on (d.code = v.code and d.t_day = v.day)
where
d.code = '%s' and
d.code in (select code from Valuation group by code having count(*) > %d)
order by d.t_day asc, d.code asc
''' % ( code, threshold )
r = conn.execute( alch_text(s) ).fetchall()
# 交易日, 代码，收盘， 涨停价， 前日收盘， 停牌标志 , PB, 换手率
# 0 1 2 3 4 5 , 6 , 7
row_num = 0
his_md = []
last_t_day = ''
md_of_1_day = collections.OrderedDict ()
for row in r:
t_day = str(row[0])
code = str(row[1])
close = row[2]
if close is None:
print "WARN! skip bad MD:" , row
continue
if t_day != last_t_day:
# 新的一日行情开始
is_new_t_day = True
if len(md_of_1_day) > 0:
his_md.append( [ last_t_day , md_of_1_day ] )
md_of_1_day = collections.OrderedDict ()
last_t_day = t_day
else:
# 对当前日行情，填入新的代码
is_new_t_day = False
if (row[3] - close ) < 0.01:
close_on_ceil = 1
else:
close_on_ceil = 0
pre_close = row[4]
if pre_close is not None and pre_close != 0:
delta_r = (close - pre_close) / pre_close
else:
delta_r = 0
paused = row[5]
pb = row[6]
turnover = row[7]
md_of_1_sec = [close, pre_close, delta_r, close_on_ceil, paused, pb, turnover] # 收盘价，前日收盘价, 涨幅， 涨停，停牌，PB, 换手
md_of_1_day[code] = md_of_1_sec
his_md.append( [ last_t_day , md_of_1_day ] )
return his_md
def db_fetch_dailyline(conn, start_day ):
s = '''
select t_day, code, close, high_limit, pre_close, paused, high, low
from DailyLine
where t_day >= '%s'
order by t_day asc, code asc
''' % ( start_day )
r = conn.execute( alch_text(s) ).fetchall()
# 交易日, 代码，收盘， 涨停价， 前日收盘， 停牌标志, 最高, 最低
# 0 1 2 3 4 5 6 7
row_num = 0
his_md = []
last_t_day = ''
md_of_1_day = collections.OrderedDict ()
for row in r:
t_day = str(row[0])
code = str(row[1])
close = row[2]
if t_day != last_t_day:
# 新的一日行情开始
is_new_t_day = True
if len(md_of_1_day) > 0:
his_md.append( [ last_t_day , md_of_1_day ] )
md_of_1_day = collections.OrderedDict ()
last_t_day = t_day
else:
# 对当前日行情，填入新的代码
is_new_t_day = False
if (row[3] - close ) < 0.01:
close_on_ceil = 1
else:
close_on_ceil = 0
pre_close = row[4]
if pre_close is not None and pre_close != 0:
delta_r = (close - pre_close) / pre_close
else:
delta_r = 0
paused = row[5]
high = row[6]
low = row[7]
md_of_1_sec = [close, pre_close, delta_r, close_on_ceil, paused, high, low] # 收盘价，前日收盘价, 涨幅， 涨停，停牌, 最高, 最低
md_of_1_day[code] = md_of_1_sec
his_md.append( [ last_t_day , md_of_1_day ] )
return his_md
