class DailyStocks(object): db, db_engine = Database.initSession() def __init__(self, date): self.date = date self.CGO = {} self.stock_list = list( pd.read_sql_query("select distinct code from basic_list", self.db_engine).code) def get_cgo(self): for code in self.stock_list: try: # 找到当前日期,获得当前日期以前的100个数据 sql = "select * from basic_list where code =" + "'" + code + "'" + "and date < " + "'" + self.date + "'" data = pd.read_sql_query(sql, self.db_engine) data = data[-100:] data.reset_index(drop=True, inplace=True) # 计算每日均价 np_data = np.array( data[['close_price', 'ave_price', 'turnover_rate']]) length = np.shape(np_data)[0] for i in range(length): for j in range(i + 1, length): # data.loc[index, 'turnover_weight'] *= (1 - data.loc[j, 'turnover_weight']) np_data[i][2] *= (1 - np_data[j][2]) num = np.sum(np_data[:, 2]) np_data[:, 2] /= num RP = np.sum(np_data[:, 2] * np_data[:, 1]) # data.eval(""" # RP = turnover_weight * ave_price # """, inplace=True) # RP = data['RP'].sum() self.CGO[code] = (np_data[-1][0] - RP) / RP except: pass def insertCGO(self): ss = scoped_session(self.db) dbs = ss() data = self.CGO.items() dbs.execute(Database.cgo_list.__table__.insert(), [{ "date": self.date, "code": element[0], "cgo_factor": float(element[1]) } for element in data]) dbs.commit() dbs.close() ss.remove()
def get_top5(date): db, db_engine = Database.initSession() cgo_sql = "select * from cgo_list where date =" + "'" + date + "'" cgo = pd.read_sql_query(cgo_sql, db_engine) data = cgo.sort_values(by='cgo_factor', ascending=False) data = data[0 * int(data.shape[0] / 5):1 * int(data.shape[0] / 5)] stock_list = list(data.code) return stock_list
class Factors(object): db, db_engine = Database.initSession() def __init__(self): self.data = None def getToday(self): self.data = pd.read_excel(path, dtype=object) self.data[['return']] = self.data[['return']].astype(float) def insertToday(self): engine = self.db_engine max_iteration = math.ceil(self.data.shape[0] / 50000) for i in range(max_iteration): if i != max_iteration - 1: self.data[i * 50000:(i + 1) * 50000].to_sql('basic_list', con=engine, if_exists='append', index=False) else: self.data[i * 50000:].to_sql('basic_list', con=engine, if_exists='append', index=False)
# total_threads[st].start() # running_threads.append(total_threads[st]) # st += 1 # continue # for t in running_threads: # if not t.is_alive(): # running_threads.remove(t) # for t in total_threads: # t.join() # f = Factors() # f.getToday() #print(f.data) #获取时间交集,保证全部时间的交集一致 db, db_engine = Database.initSession() basic = pd.read_excel(path, dtype=object) code_list = "select distinct code from basic_list" code_list = list(pd.read_sql_query(code_list, db_engine).code) error_code = [] for code in code_list: #date_sql = "select distinct date from basic_list where code =" + "'"+code+"'" #date_list = list(pd.read_sql_query(date_sql, db_engine).date) date_list = list(basic[basic['code'] == code].date) if len(date_list) > 1900: pass #print(allin(date_list,date_intersection))