def start_account(): # 建仓 the_date = trade_date[0].strftime('%Y-%m-%d') items = 'symbol, close' table = 'stk_price_forward' if len(holding_list[the_date]) != 0: # stock_price = w.wsd(holding_list[the_date], "close", trade_date[0], trade_date[0], "Fill=Previous;PriceAdj=F") db_data = get_data(items, table, holding_list[the_date], the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position = {} stock_price_holding = {} for c in range(0, len(code_list)): stock_position[code_list[c]] = int(MONEY / len(code_list) / 100 / round(float(stock_price[c]), 3)) * 100 stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) Account.stock_position[the_date] = stock_position Account.stock_price_holding[the_date] = stock_price_holding Account.total_fee[the_date] = round(sum([stock_price_holding[k] * stock_position[k] for k in stock_position]) * FEE_RATE, 2) Account.cash[the_date] = round(MONEY - sum([stock_price_holding[k] * stock_position[k] for k in stock_position]) * (1 + FEE_RATE), 2) Account.change_rate[the_date] = - Account.total_fee[the_date] * 100 / MONEY Account.cumulative_change_rate[the_date] = 1 + Account.change_rate[the_date] / 100 Account.total_amount[the_date] = Account.cash[the_date] + round(sum([stock_price_holding[k] * stock_position[k] for k in stock_position]), 2) else: # 买入列表为空 Account.stock_position[the_date] = {} Account.stock_price_holding[the_date] = {} Account.total_fee[the_date] = 0 Account.cash[the_date] = MONEY Account.change_rate[the_date] = 0 Account.cumulative_change_rate[the_date] = 1 Account.total_amount[the_date] = MONEY
def fill_ratio(table): item = 'symbol, min(date), max(date)' table = table condition = ' where symbol > \'600757.SH\' and date >= \'2010-01-01\' and date < \'2018-03-15\' group by symbol order by symbol' db_data = get_all_data(item, table, condition) for a in range(0, len(db_data)): index_start = date_list.index(db_data[a][1]) index_end = date_list.index(db_data[a][2]) + 1 date_list_idx = date_list[index_start:index_end] item2 = 'date, pe_ttm, pb' table2 = table symbol_list2 = '\'' + db_data[a][0] + '\'' startDate2 = db_data[a][1].strftime('%Y-%m-%d') endDate2 = db_data[a][2].strftime('%Y-%m-%d') stk_data = get_data(item2, table2, symbol_list2, startDate2, endDate2) date_stk = [] pe_ttm_stk = {} pb_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) pe_ttm_stk[stk_data[b][0]] = stk_data[b][1] pb_stk[stk_data[b][0]] = stk_data[b][2] fill_stk = {} fill_stk[date_list_idx[0]] = [ float(pe_ttm_stk[date_list_idx[0]]), float(pb_stk[date_list_idx[0]]) ] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_stk[date_list_idx[c]] = [ float(pe_ttm_stk[date_list_idx[c]]), float(pb_stk[date_list_idx[c]]) ] else: fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]] for d in date_stk: if d in date_list_idx: fill_stk.pop(d) for e in fill_stk: insert_sql = 'insert into data.' + table2 + ' values (' + symbol_list2 + ',\'' + str( e) + '\',' + str(fill_stk[e]).replace('[', '').replace( ']', '') + ');' try: cur.execute(insert_sql) except Exception as e: print(e) log.info(symbol_list2 + 'is filled.')
def format_files(): get_data() path = 'C:/temp/idx_price/' for filename in os.listdir(path): stockprice = open(path + filename, mode='r', encoding=None, errors=None, newline=None, closefd=True, opener=None) content = stockprice.readlines() del content[0] for r in range(0, len(content)): if filename.startswith('0') or filename.startswith('9'): content[r] = re.sub(r'\'(?P<name>\w+)', '\g<name>.SH', content[r]).strip() else: content[r] = re.sub(r'\'(?P<name>\w+)', '\g<name>.SZ', content[r]).strip() list = content[r].split(',') del list[2] del list[6:8] del list[7:9] del list[8:] line = str(list).replace('[', '(').replace(']', ')').replace( 'None', '0') content[r] = line stockprice.close() stockvalue = open(path + filename, mode='w+', encoding=None, errors=None, newline=None, closefd=True, opener=None) for r2 in range(0, len(content)): stockvalue.writelines(content[r2] + '\n') stockvalue.close()
def start_account(): # 建仓 the_date = date_list[0].strftime('%Y-%m-%d') items = 'symbol, close' table = 'etf_price_backward' if len(etf_list[the_date]) != 0: db_data = get_data(items, table, list(etf_list[the_date].keys()), the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position = {} stock_price_holding = {} for c in range(0, len(code_list)): stock_position[code_list[c]] = int( MONEY / len(code_list) / 100 / round(float(stock_price[c]), 3)) * 100 stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) Account.stock_position[the_date] = stock_position Account.stock_price_holding[the_date] = stock_price_holding Account.total_fee[the_date] = round( sum([ stock_price_holding[k] * stock_position[k] for k in stock_position ]) * FEE_RATE, 2) Account.cash[the_date] = round( MONEY - sum([ stock_price_holding[k] * stock_position[k] for k in stock_position ]) * (1 + FEE_RATE), 2) Account.change_rate[ the_date] = -Account.total_fee[the_date] * 100 / MONEY Account.cumulative_change_rate[ the_date] = 1 + Account.change_rate[the_date] / 100 Account.total_amount[the_date] = Account.cash[the_date] + round( sum([ stock_price_holding[k] * stock_position[k] for k in stock_position ]), 2) else: # 买入列表为空 Account.stock_position[the_date] = {} Account.stock_price_holding[the_date] = {} Account.total_fee[the_date] = 0 Account.cash[the_date] = MONEY Account.change_rate[the_date] = 0 Account.cumulative_change_rate[the_date] = 1 Account.total_amount[the_date] = MONEY Account.Show_Account(the_date)
def fill_adj_stk(table): items2 = 'symbol, min(date), max(date)' table2 = table condition = ' group by symbol order by symbol' db_data2 = get_all_data(items2, table2, condition) for a in range(0, len(db_data2)): index_start = date_list.index(db_data2[a][1]) index_end = date_list.index(db_data2[a][2]) + 1 date_list_idx = date_list[index_start:index_end] item3 = 'date, close' table3 = table2 symbol_list3 = '\'' + db_data2[a][0] + '\'' startDate3 = db_data2[a][1].strftime('%Y-%m-%d') endDate3 = db_data2[a][2].strftime('%Y-%m-%d') stk_data = get_data(item3, table3, symbol_list3, startDate3, endDate3) date_stk = [] close_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) close_stk[stk_data[b][0]] = stk_data[b][1] fill_stk = {} fill_stk[date_list_idx[0]] = close_stk[date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_stk[date_list_idx[c]] = close_stk[date_list_idx[c]] else: fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]] for d in date_stk: if d in date_list_idx: fill_stk.pop(d) for e in fill_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_stk[e])) + '\',\'' + str( float(fill_stk[e])) + '\',\'' + str(float( fill_stk[e])) + '\',\'' + str(float( fill_stk[e])) + '\');' try: cur.execute(insert_sql) except Exception as e: print(e) log.info(symbol_list3 + 'is filled.')
def main(): db_list = { '159901.OF': 'etf_price', '000001.SH': 'idx_price', '000001.SZ': 'stk_price', '000002.SZ': 'stk_price_forward', '600036.SH': 'stk_price_backward', '002456.SZ': 'stk_ratio', '000002.SH': 'idx_price_tec', '000001.SZ': 'stk_price_tec' } db_info = [] for i in db_list: temp = get_data(i, db_list[i]) db_info.append(temp) tb = pt.PrettyTable() tb.field_names = ['Code', 'Table', 'Date'] for stock in db_info: tb.add_row(stock) tb.align = 'l' print(tb) return (tb)
def fill_ratio_data(): items = 'symbol, date' endDate = (datetime.now().date() + timedelta(days=-1)).strftime('%Y%m%d') startDate = (datetime.now().date() + timedelta(days=-15)).strftime('%Y%m%d') table = 'idx_price' symbol_list = ['000001.SH'] db_data = get_data(items, table, symbol_list, startDate, endDate) date_list = [] for i in range(0, len(db_data)): date_list.append(db_data[i][1]) items2 = 'symbol, min(date), max(date)' table2 = 'stk_ratio' condition = ' where date >= \'' + startDate + '\' and date <=\'' + endDate + '\' group by symbol order by symbol' db_data2 = get_all_data(items2, table2, condition) for a in range(0, len(db_data2)): index_start = date_list.index(db_data2[a][1]) index_end = date_list.index(db_data2[a][2]) + 1 date_list_idx = date_list[index_start:index_end] item3 = 'date, pe_ttm, pb ' table3 = table2 symbol_list3 = '\'' + db_data2[a][0] + '\'' startDate3 = db_data2[a][1].strftime('%Y-%m-%d') endDate3 = db_data2[a][2].strftime('%Y-%m-%d') if startDate3 > endDate3: continue stk_data = get_data(item3, table3, symbol_list3, startDate3, endDate3) date_stk = [] pe_stk = {} pb_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) pe_stk[stk_data[b][0]] = stk_data[b][1] pb_stk[stk_data[b][0]] = stk_data[b][2] fill_pe_stk = {} fill_pb_stk = {} fill_pe_stk[date_list_idx[0]] = pe_stk[date_list_idx[0]] fill_pb_stk[date_list_idx[0]] = pb_stk[date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_pe_stk[date_list_idx[c]] = pe_stk[date_list_idx[c]] fill_pb_stk[date_list_idx[c]] = pb_stk[date_list_idx[c]] else: fill_pe_stk[date_list_idx[c]] = fill_pe_stk[date_list_idx[c - 1]] fill_pb_stk[date_list_idx[c]] = fill_pb_stk[date_list_idx[c - 1]] for d in date_list_idx: if d in date_stk: date_stk.remove(d) fill_pe_stk.pop(d) fill_pb_stk.pop(d) if len(date_stk) == 0: continue for e in date_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_pe_stk[e])) + '\',\'' + str( float(fill_pb_stk[e])) + '\');' print(insert_sql) try: fill_data(insert_sql) except Exception as e: print(e) print(symbol_list3 + 'is filled.')
def trade_account(): # 按历史列表开始交易 for d in range(1, len(trade_date)): the_date = trade_date[d].strftime('%Y-%m-%d') pre_date = trade_date[d-1].strftime('%Y-%m-%d') pre_holding_list = holding_list[pre_date] pre_holding_list.sort() holding_list[the_date].sort() items = 'symbol, close' table = 'stk_price_forward' if pre_holding_list == holding_list[the_date]: # stock_price = w.wsd(holding_list[the_date], "close", the_date, the_date, "Fill=Previous;PriceAdj=F") db_data = get_data(items, table, holding_list[the_date], the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_price_holding = {} for c in range(0, len(code_list)): stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) Account.stock_price_holding[the_date] = stock_price_holding Account.stock_position[the_date] = Account.stock_position[pre_date] Account.total_fee[the_date] = Account.total_fee[pre_date] Account.cash[the_date] = Account.cash[pre_date] logger.info(the_date) logger.info(stock_price_holding) logger.info(Account.stock_position[pre_date]) Account.total_amount[the_date] = Account.cash[the_date] + round( sum([stock_price_holding[k] * Account.stock_position[pre_date][k] for k in Account.stock_position[pre_date]]), 2) Account.change_rate[the_date] = round(100 * (Account.total_amount[the_date]-Account.total_amount[pre_date])/Account.total_amount[pre_date], 3) Account.cumulative_change_rate[the_date] = round(Account.total_amount[the_date] / MONEY, 4) if len(holding_list[the_date]) == 0: Account.stock_price_holding[the_date] = {} Account.stock_position[the_date] = {} Account.total_fee[the_date] = Account.total_fee[pre_date] Account.cash[the_date] = Account.cash[pre_date] Account.total_amount[the_date] = Account.cash[the_date] Account.change_rate[the_date] = 0 Account.cumulative_change_rate[the_date] = Account.cumulative_change_rate[pre_date] elif set(holding_list[the_date]) > set(pre_holding_list): buy_list = list(set(holding_list[the_date]).difference(set(pre_holding_list))) # stock_price = w.wsd(holding_list[the_date], "close", the_date, the_date, "Fill=Previous;PriceAdj=F") db_data = get_data(items, table, holding_list[the_date], the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position = {} stock_price_holding = {} for c in range(0, len(code_list)): stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) for e in range(0, len(buy_list)): stock_position[buy_list[e]] = int(Account.cash[pre_date] / len(buy_list) / 1000 / round(stock_price_holding[buy_list[e]], 3)) * 100 Account.stock_price_holding[the_date] = stock_price_holding Account.stock_position[the_date] = dict(stock_position, **Account.stock_position[pre_date]) Account.total_fee[the_date] = Account.total_fee[pre_date] + round( sum([stock_price_holding[k] * stock_position[k] for k in stock_position]) * FEE_RATE, 2) Account.cash[the_date] = round(Account.cash[pre_date] - sum( [stock_price_holding[k] * stock_position[k] for k in stock_position])*(1 + FEE_RATE), 2) Account.total_amount[the_date] = Account.cash[the_date] + round( sum([stock_price_holding[k] * Account.stock_position[the_date][k] for k in Account.stock_position[the_date]]), 2) Account.change_rate[the_date] = round(100 * (Account.total_amount[the_date]-Account.total_amount[pre_date])/Account.total_amount[pre_date], 3) Account.cumulative_change_rate[the_date] = round(Account.total_amount[the_date] / MONEY, 4) elif set(pre_holding_list) > set(holding_list[the_date]): sell_list = list(set(pre_holding_list).difference(set(holding_list[the_date]))) # stock_price = w.wsd(pre_holding_list, "close", the_date, the_date, "Fill=Previous;PriceAdj=F") db_data = get_data(items, table, holding_list[the_date], the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position = {} stock_price_holding = {} for c in range(0, len(code_list)): stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) Account.stock_price_holding[the_date] = stock_price_holding.copy() Account.stock_position[the_date] = Account.stock_position[pre_date] for e in range(0, len(sell_list)): stock_position[sell_list[e]] = Account.stock_position[pre_date][sell_list[e]] Account.stock_price_holding[the_date].pop(sell_list[e]) Account.stock_position[the_date].pop(sell_list[e]) Account.total_fee[the_date] = Account.total_fee[pre_date] + round( sum([stock_price_holding[k] * stock_position[k] for k in stock_position]) * (FEE_RATE + TAX_RATE), 2) Account.cash[the_date] = round(Account.cash[pre_date] + sum( [stock_price_holding[k] * stock_position[k] for k in stock_position]) * (1 - FEE_RATE - TAX_RATE), 2) Account.total_amount[the_date] = Account.cash[the_date] + round( sum([stock_price_holding[k] * Account.stock_position[the_date][k] for k in Account.stock_position[the_date]]), 2) Account.change_rate[the_date] = round( 100 * (Account.total_amount[the_date] - Account.total_amount[pre_date]) / Account.total_amount[ pre_date], 3) Account.cumulative_change_rate[the_date] = round(Account.total_amount[the_date] / MONEY, 4) else: sell_list = list(set(pre_holding_list).difference(set(holding_list[the_date]))) buy_list = list(set(holding_list[the_date]).difference(set(pre_holding_list))) total_trade_list = list(set(pre_holding_list + holding_list[the_date])) # stock_price = w.wsd(total_trade_list, "close", the_date, the_date, "Fill=Previous;PriceAdj=F") db_data = get_data(items, table, total_trade_list, the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position_sell = {} stock_position_buy = {} stock_price_holding = {} for c in range(0, len(code_list)): stock_price_holding[code_list[c]] = round(float(stock_price[c]), 3) Account.stock_price_holding[the_date] = stock_price_holding.copy() Account.stock_position[the_date] = Account.stock_position[pre_date] for e in range(0, len(sell_list)): stock_position_sell[sell_list[e]] = Account.stock_position[pre_date][sell_list[e]] Account.stock_price_holding[the_date].pop(sell_list[e]) Account.stock_position[the_date].pop(sell_list[e]) currenct_cash = Account.cash[pre_date] + round( sum([stock_price_holding[k] * stock_position_sell[k] for k in stock_position_sell]) * (1 - FEE_RATE - TAX_RATE), 2) for f in range(0, len(buy_list)): stock_position_buy[buy_list[f]] = int(currenct_cash / len(buy_list) / 100 / round(stock_price_holding[buy_list[f]], 3)) * 100 Account.stock_position[the_date] = dict(stock_position_buy, **Account.stock_position[the_date]) Account.total_fee[the_date] = round(Account.total_fee[pre_date] + sum([stock_price_holding[k] * stock_position_sell[k] for k in stock_position_sell]) * (FEE_RATE + TAX_RATE) + sum([stock_price_holding[k] * stock_position_buy[k] for k in stock_position_buy]) * FEE_RATE, 2) Account.cash[the_date] = round(currenct_cash - sum( [stock_price_holding[k] * stock_position_buy[k] for k in stock_position_buy]) * (1 + FEE_RATE), 2) Account.total_amount[the_date] = Account.cash[the_date] + round( sum([stock_price_holding[k] * Account.stock_position[the_date][k] for k in Account.stock_position[the_date]]), 2) Account.change_rate[the_date] = round( 100 * (Account.total_amount[the_date] - Account.total_amount[pre_date]) / Account.total_amount[ pre_date], 3) Account.cumulative_change_rate[the_date] = round(Account.total_amount[the_date] / MONEY, 4)
def fill_adj_stk(table): items = 'symbol, date' endDate = (datetime.now().date() + timedelta(days=-1)).strftime('%Y%m%d') startDate = (datetime.now().date() + timedelta(days=-5)).strftime('%Y%m%d') tables = 'idx_price' symbol_list = ['000001.SH'] db_data = get_data(items, tables, symbol_list, startDate, endDate) date_list = [] for i in range(0, len(db_data)): date_list.append(db_data[i][1]) conn, cur = connDB() items2 = 'symbol, min(date), max(date)' table2 = table condition = ' where date >= \'' + startDate + '\' and date <=\'' + endDate + '\' group by symbol order by symbol' db_data2 = get_all_data(items2, table2, condition) for a in range(0, len(db_data2)): index_start = date_list.index(db_data2[a][1]) index_end = date_list.index(db_data2[a][2]) if index_start >= index_end: continue date_list_idx = date_list[index_start:index_end] item3 = 'date, close' table3 = table2 symbol_list3 = '\'' + db_data2[a][0] + '\'' startDate3 = db_data2[a][1].strftime('%Y-%m-%d') endDate3 = db_data2[a][2].strftime('%Y-%m-%d') if startDate3 > endDate3: continue stk_data = get_data(item3, table3, symbol_list3, startDate3, endDate3) date_stk = [] close_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) close_stk[stk_data[b][0]] = stk_data[b][1] fill_stk = {} fill_stk[date_list_idx[0]] = close_stk[date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_stk[date_list_idx[c]] = close_stk[date_list_idx[c]] else: fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]] for d in date_stk: if d in date_list_idx: fill_stk.pop(d) for e in fill_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_stk[e])) + '\',\'' + str( float(fill_stk[e])) + '\',\'' + str( float(fill_stk[e])) + '\',\'' + str( float(fill_stk[e])) + '\');' try: cur.execute(insert_sql) except Exception as e: print(e) conn.commit() print(symbol_list3 + 'is filled.')
def fill_stk_data(): load_data_to_db() items = 'symbol, date' endDate = (datetime.now().date() + timedelta(days=-1)).strftime('%Y%m%d') startDate = (datetime.now().date() + timedelta(days=-5)).strftime('%Y%m%d') table = 'idx_price' symbol_list = ['000001.SH'] db_data = get_data(items, table, symbol_list, startDate, endDate) date_list = [] for i in range(0, len(db_data)): date_list.append(db_data[i][1]) items2 = 'symbol, min(date), max(date)' table2 = 'stk_price' condition = ' where date >= \'' + startDate + '\' and date <=\'' + endDate + '\' group by symbol order by symbol' db_data2 = get_all_data(items2, table2, condition) for a in range(0, len(db_data2)): index_start = date_list.index(db_data2[a][1]) index_end = date_list.index(db_data2[a][2]) + 1 date_list_idx = date_list[index_start:index_end] item3 = 'date, close, mktcap, trade_mktcap ' table3 = table2 symbol_list3 = '\'' + db_data2[a][0] + '\'' startDate3 = db_data2[a][1].strftime('%Y-%m-%d') endDate3 = db_data2[a][2].strftime('%Y-%m-%d') if startDate3 > endDate3: continue stk_data = get_data(item3, table3, symbol_list3, startDate3, endDate3) date_stk = [] close_stk = {} mkt_stk = {} trade_mkt_stk = {} for b in range(0, len(stk_data)): date_stk.append(stk_data[b][0]) close_stk[stk_data[b][0]] = stk_data[b][1] mkt_stk[stk_data[b][0]] = stk_data[b][2] trade_mkt_stk[stk_data[b][0]] = stk_data[b][3] fill_stk = {} fill_mkt_stk = {} fill_trade_mkt_stk = {} fill_stk[date_list_idx[0]] = close_stk[date_list_idx[0]] fill_mkt_stk[date_list_idx[0]] = mkt_stk[date_list_idx[0]] fill_trade_mkt_stk[date_list_idx[0]] = trade_mkt_stk[ date_list_idx[0]] for c in range(1, len(date_list_idx)): if date_list_idx[c] in date_stk: fill_stk[date_list_idx[c]] = close_stk[date_list_idx[c]] fill_mkt_stk[date_list_idx[c]] = mkt_stk[date_list_idx[c]] fill_trade_mkt_stk[date_list_idx[c]] = trade_mkt_stk[ date_list_idx[c]] else: log.info(date_list_idx[c]) fill_stk[date_list_idx[c]] = fill_stk[date_list_idx[c - 1]] fill_mkt_stk[date_list_idx[c]] = fill_mkt_stk[ date_list_idx[c - 1]] fill_trade_mkt_stk[date_list_idx[c]] = fill_trade_mkt_stk[ date_list_idx[c - 1]] for d in date_list_idx: if d in date_stk: fill_stk.pop(d) fill_mkt_stk.pop(d) fill_trade_mkt_stk.pop(d) for e in fill_stk: insert_sql = 'insert into data.' + table3 + ' values (' + symbol_list3 + ',\'' + str( e) + '\',\'' + str(float(fill_stk[e])) + '\',\'' + str( float(fill_stk[e])) + '\',\'' + str(float( fill_stk[e])) + '\',\'' + str(float( fill_stk[e])) + '\',\'0\',\'0\',\'' + str( fill_mkt_stk[e]) + '\',\'' + str( fill_trade_mkt_stk[e]) + '\');' # print(insert_sql) try: fill_data(insert_sql) except Exception as e: print(e) print(symbol_list3 + 'is filled.') fill_stk_data()
# for n in range(years): # if n == 0: # start_year = s_time # end_year = str(int(s_time[:4]) + n) + '-12-31' # elif n == (years - 1): # start_year = str(int(s_time[:4]) + n) + '-01-01' # end_year = e_time # else: # start_year = str(int(s_time[:4]) + n) + '-01-01' # end_year = str(int(s_time[:4]) + n) + '-12-31' # # start_list.append(start_year) start_year = s_time end_year = e_time # df_idx = get_k(symbol_idx, 60, 0, start_year, end_year).drop(['open', 'high', 'low', 'close'], axis=1, inplace=False).set_index('datetime') index_data = get_data('date', 'idx_price', '\'000300.SH\'', start_year, end_year) df_idx = pd.DataFrame(list(index_data),columns=['datetime']).set_index('datetime') df_return = copy.copy(df_idx) # s_list = np.arange(0,1.00,0.1) S = 0 for N in range(5,32): df_i = copy.copy(df_idx) df_ret = copy.copy(df_return) for sym in symbol_list: sym = sym.replace('SHSE.', '').replace('SZSE.', '') if sym.startswith('6'): sym = '\'' + sym + '.SH\'' price_data = get_data('date, high,low, close', 'stk_price_forward', sym, start_year, end_year) elif sym.startswith('0') or sym.startswith('3'):
def trade_account(): # 按历史列表开始交易 for d in range(1, len(date_list)): the_date = date_list[d].strftime('%Y-%m-%d') pre_date = date_list[d - 1].strftime('%Y-%m-%d') pre_holding_list = list(etf_list[pre_date].keys()) pre_holding_list.sort() current_list = list(etf_list[the_date].keys()) current_list.sort() items = 'symbol, close' table = 'etf_price_backward' if pre_holding_list == current_list and len(pre_holding_list) != 0: db_data = get_data(items, table, current_list, the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_price_holding = {} for c in range(0, len(code_list)): stock_price_holding[code_list[c]] = round( float(stock_price[c]), 3) Account.stock_price_holding[the_date] = stock_price_holding Account.stock_position[the_date] = Account.stock_position[pre_date] Account.total_fee[the_date] = Account.total_fee[pre_date] Account.cash[the_date] = Account.cash[pre_date] Account.total_amount[the_date] = Account.cash[the_date] + round( sum([ stock_price_holding[k] * Account.stock_position[pre_date][k] for k in Account.stock_position[pre_date] ]), 2) Account.change_rate[the_date] = round( 100 * (Account.total_amount[the_date] - Account.total_amount[pre_date]) / Account.total_amount[pre_date], 3) Account.cumulative_change_rate[the_date] = round( Account.total_amount[the_date] / MONEY, 4) elif pre_holding_list == current_list and len(current_list) == 0: Account.stock_price_holding[the_date] = {} Account.stock_position[the_date] = {} Account.total_fee[the_date] = Account.total_fee[pre_date] Account.cash[the_date] = Account.total_amount[pre_date] Account.total_amount[the_date] = Account.total_amount[pre_date] Account.change_rate[the_date] = 0 Account.cumulative_change_rate[ the_date] = Account.cumulative_change_rate[pre_date] else: # sell_list = pre_holding_list buy_list = current_list total_trade_list = list(set(pre_holding_list + current_list)) same_list = list( set(pre_holding_list).intersection(set(current_list))) diff_list = list(set(total_trade_list).difference(set(same_list))) db_data = get_data(items, table, total_trade_list, the_date, the_date) code_list = [] stock_price = [] for j in range(0, len(db_data)): code_list.append(db_data[j][0]) stock_price.append(db_data[j][1]) stock_position_sell = Account.stock_position[pre_date] stock_position_buy = {} stock_price_holding = {} all_price = {} for c in range(0, len(code_list)): all_price[code_list[c]] = round(float(stock_price[c]), 3) sell_currenct_cash = round( Account.cash[pre_date] + sum([ all_price[k] * stock_position_sell[k] for k in stock_position_sell ]), 2) for f in range(0, len(buy_list)): stock_position_buy[buy_list[f]] = int( sell_currenct_cash / len(buy_list) / 100 / all_price[buy_list[f]]) * 100 stock_price_holding[buy_list[f]] = all_price[buy_list[f]] Account.stock_position[the_date] = stock_position_buy Account.stock_price_holding[the_date] = stock_price_holding Account.total_fee[the_date] = round( Account.total_fee[pre_date] + (Account.total_amount[pre_date] - Account.cash[pre_date]) * len(diff_list) * FEE_RATE / (len(same_list) * 2 + len(diff_list)), 2) Account.cash[the_date] = round( sell_currenct_cash - sum([ stock_price_holding[k] * stock_position_buy[k] for k in stock_position_buy ]) * (1 + FEE_RATE), 2) Account.total_amount[the_date] = round( Account.cash[the_date] + sum([ stock_price_holding[k] * Account.stock_position[the_date][k] for k in Account.stock_position[the_date] ]), 2) Account.change_rate[the_date] = round( 100 * (Account.total_amount[the_date] - Account.total_amount[pre_date]) / Account.total_amount[pre_date], 3) Account.cumulative_change_rate[the_date] = round( Account.total_amount[the_date] / MONEY, 4) Account.Show_Account(the_date)
import os import tushare as ts from ConnectDB import connDB, connClose, get_data, get_all_data log.basicConfig( # filename = LOG_PATH + LOG_FILENAME, level=log.DEBUG, # format="[%(asctime)s] %(name)s:%(levelname)s: %(message)s" format="%(levelname)s: %(message)s") items = 'symbol, date' startDate = '2010-01-04' endDate = '2018-03-16' table = 'idx_price' symbol_list = ['000001.SH'] db_data = get_data(items, table, symbol_list, startDate, endDate) date_list = [] for i in range(0, len(db_data)): date_list.append(db_data[i][1]) conn, cur = connDB() def fill_adj_stk(table): items2 = 'symbol, min(date), max(date)' table2 = table condition = ' group by symbol order by symbol' db_data2 = get_all_data(items2, table2, condition) for a in range(0, len(db_data2)):