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)):
