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