def update_w_idx_price():
w.start()
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = 'idx_price'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
w_idx_data = dict(data_info)
end_date = datetime.now().date().strftime('%Y-%m-%d')
for i in w_idx_data:
start_date = (w_idx_data[i] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
w_data = w.wsd(i, "open,high,low,close,pct_chg,amt", start_date,
end_date, "")
# if len(w_data.Data) <= 1 or w_data.Data[0][1] == None:
# continue
for s in range(0, len(w_data.Times)):
temp = []
temp.append(i)
temp.append(w_data.Times[s].strftime('%Y-%m-%d'))
for r in range(0, len(w_data.Fields)):
temp.append(w_data.Data[r][s])
insert_sql = 'insert into data.idx_price values(' + str(
temp).replace('[', '').replace(']', '') + ');'
# print(insert_sql)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(i + ' is inserted. ')
conn.commit()
connClose(conn, cur)
def load_data_to_db():
format_files()
path = 'C:/temp/stk_price/'
conn, cur = connDB()
for filename in os.listdir(path):
stock = open(path + filename,
mode='r',
encoding=None,
errors=None,
newline=None,
closefd=True,
opener=None)
hisprice = stock.readlines()
for j in range(0, len(hisprice)):
istsql = 'insert into data.stk_price (date,symbol,close,high,low,open,chgrate,volume,mktcap,trade_mktcap) values ' + hisprice[
j]
# print(istsql)
try:
conn.cursor().execute(istsql)
except Exception as e:
print(e)
print(istsql)
conn.commit()
print(filename + ' is loaded into database.')
stock.close()
try:
os.remove(path + filename)
print(filename + ' is deleted.')
except Exception as e:
print(e)
connClose(conn, cur)
print('''########################################''' + '\n' +
' data.stk_price is updated to latest status.' + '\n' +
'''########################################''')
def update_cb_tdx():
path = 'C:\\tdx\\T0002\\export\\'
conn, cur = connDB()
files = os.listdir(path)
for filename in files:
stockprice = open(path + filename, mode='r', encoding=None, errors=None, newline=None, closefd=True,
opener=None)
content = stockprice.readlines()
if len(content) <= 3:
continue
symbol = content[0][0:6]
type = content[0].split(' ')[2][0]
content.pop()
for i in range(2,len(content)):
m_data = content[i].strip().split(',')
insert_sql = 'insert into data.cb_price (datetime,open,high,low,close,volume,symbol,type) VALUES(\''\
+ m_data[0].replace('/','-') + ' ' + m_data[1][0:2] + ':' + m_data[1][2:] +':00\',' \
+ str(m_data[2:7]).replace('[','').replace(']','') + ',\'' + symbol + '\',\'' + type +'\');'
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(insert_sql)
print(e)
conn.commit()
print(symbol + '(' + str(files.index(filename)+1) + '/' + str(len(files)) + ') is inserted into data.cb_price')
connClose(conn, cur)
def Get_Future_Price():
conn, cur = connDB()
items = 'a.symbol,b.datetime'
table = 'fur_info a left join (select symbol, max(datetime) as datetime from data.fur_price_5m group by symbol) b on a.symbol=b.symbol'
condition = 'order by a.symbol asc'
list_data = get_all_data(items, table, condition)
symbol_datetime = dict(list_data)
for symbol in symbol_datetime.keys():
if symbol.startswith('IF') or symbol.startswith(
'IC') or symbol.startswith('IH') or symbol.startswith(
'TF') or symbol.startswith('TS') or symbol.startswith(
'T1'):
symbol_url = 'http://stock2.finance.sina.com.cn/futures/api/json.php/CffexFuturesService.getCffexFuturesMiniKLine5m?symbol=' + symbol
else:
symbol_url = 'http://stock2.finance.sina.com.cn/futures/api/json.php/IndexService.getInnerFuturesMiniKLine5m?symbol=' + symbol
symbol_page = urllib.request.urlopen(symbol_url).read().decode('utf-8')
symbol_data = symbol_page.replace('[[', '').replace(']]',
'').split('],[')
for k in symbol_data:
if symbol_datetime[symbol] is not None:
if k[1:20] <= symbol_datetime[symbol].strftime(
'%Y-%m-%d %H:%M:%S'):
continue
insert_sql = 'insert into data.fur_price_5m values(\'' + symbol + '\',' + k + ')'
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
conn.commit()
connClose(conn, cur)
def update_cb(symbol_list, scale):
conn, cur = connDB()
for symbol in symbol_list:
if symbol.startswith('12'):
sym = 'sz' + symbol
else:
sym = 'sh' + symbol
for s in scale:
url = 'http://money.finance.sina.com.cn/quotes_service/api/json_v2.php/CN_MarketData.getKLineData?symbol=' + sym + '&scale=' + s +'&ma=no&datalen=50'
response = rq.urlopen(url, timeout=20)
r_data = response.read().decode('utf-8', 'ignore')
if r_data == 'null' or len(r_data) <= 4:
continue
r_data = r_data.replace('[','').replace (']','').replace('{','{"').replace(':"','":"').replace('",','","').replace('},','},,').split(',,')
for rec in r_data:
insert_data = json.loads(rec)
insert_sql = 'insert into data.cb_price (datetime,open,high,low,close,volume,symbol,type) VALUES(' + str(list(insert_data.values())).replace(']','').replace('[','') + ',\'' + symbol + '\',\'' + s + '\');'
# print(insert_sql)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
conn.commit()
print(symbol + '(' + str(symbol_list.index(symbol)+1) + '/' + str(len(symbol_list)) +') is inserted into data.cb_price')
time.sleep(3)
connClose(conn, cur)
def update_stk_adj_factor():
w.start()
conn, cur = connDB()
items = 'symbol, max(date)'
tables = 'stk_adj_factor'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
stk_data = dict(data_info)
end_date = datetime.now().date().strftime('%Y-%m-%d')
for i in stk_data:
start_date = (stk_data[i] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
w_data = w.wsd(i, "adjfactor", start_date, end_date, "")
for s in range(0, len(w_data.Times)):
insert_sql = 'insert into data.stk_adj_factor values(\'' + i + '\', \'' + w_data.Times[
s].strftime('%Y-%m-%d') + '\',\'' + str(
w_data.Data[0][s]) + '\');'
# print(insert_sql)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(i + ' is inserted in stk_adj_factor. ')
conn.commit()
connClose(conn, cur)
def update_etf_price():
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = 'etf_price'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
end = datetime.now().date().strftime('%Y-%m-%d')
for k in range(0, len(data_info)):
start = (data_info[k][1] + timedelta(days=1)).strftime('%Y-%m-%d')
if start > end:
continue
pricedata = ts.get_k_data(data_info[k][0].replace('.OF', ''),
ktype='D',
start=start,
end=end)
for h in range(0, len(pricedata)):
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.OF\'')
insql = 'insert into data.etf_price (date,open,close,high,low,volume,symbol) values (' + values + ');'
# print(insql)
try:
conn.cursor().execute(insql)
except Exception as e:
print(e)
conn.commit()
log.info(str(data_info[k][0]) + ' is inserted in etf_price')
connClose(conn, cur)
def download_financial_data():
conn, cur = connDB()
items = 'symbol'
table = 'stk_info'
condition = ' order by symbol'
symbol_list = []
symbol_data = get_all_data(items, table, condition)
for i in symbol_data:
symbol_list.append(i[0])
file_list = []
while len(symbol_list) != len(file_list):
file_list = file_name()
download(symbol_list, file_list)
connClose(conn, cur)
def get_hs300():# 获取沪深300交易日期和涨跌幅作为基准参考
conn, cur = connDB()
query_sql = 'select date, chgrate from data.idx_price where symbol = \'399300.SZ\' and date between \'' + startDate + '\' and \'' + endDate + '\' order by date'
try:
cur.execute(query_sql)
hs300_data = cur.fetchall()
except Exception as e:
print(e)
trade_date = []
hs300_chgrate = []
for g in range(0, len(hs300_data)):
trade_date.append(hs300_data[g][0])
hs300_chgrate.append(hs300_data[g][1])
connClose(conn, cur)
return(trade_date, hs300_chgrate)
def Update_Symbol_List():
symbols_url = 'http://finance.sina.com.cn/iframe/futures_info_cff.js'
url_page = urllib.request.urlopen(symbols_url).read().decode('gb2312')
raw_list = [i for i in url_page.split() if i.startswith('Array(')]
raw_list.pop(0)
conn, cur = connDB()
for j in raw_list:
insert_sql = 'insert into data.fur_info values' + j.replace(
'Array', '')
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
print(insert_sql)
conn.commit()
connClose(conn, cur)
def load_financial_data():
for filename in os.listdir(path):
with open(path + filename, newline='') as csvfile:
reader_csv = csv.reader(csvfile)
csv_content = {}
for i in reader_csv:
if i[0] != '\t\t':
csv_key = i[0]
csv_value = i[1:]
csv_value.pop()
csv_content[csv_key] = csv_value
data_content = pd.DataFrame(
csv_content,
columns=[
'报告日期', '基本每股收益(元)', '每股净资产(元)', '每股经营活动产生的现金流量净额(元)',
'净资产收益率加权(%)', '主营业务收入(万元)', '主营业务利润(万元)', '营业利润(万元)',
'投资收益(万元)', '营业外收支净额(万元)', '利润总额(万元)', '净利润(万元)',
'净利润(扣除非经常性损益后)(万元)', '经营活动产生的现金流量净额(万元)',
'现金及现金等价物净增加额(万元)', '总资产(万元)', '流动资产(万元)', '总负债(万元)',
'流动负债(万元)', '股东权益不含少数股东权益(万元)'
])
for j in range(0, data_content.index.size):
# finance_value = []
finance_value = list(data_content.loc[j])
finance_value = [
'NULL' if x == '--' else x for x in finance_value
]
finance_value.insert(0, filename.replace('.csv', ''))
insert_sql = 'insert into data.stk_finance values (' + str(
finance_value).replace('[', '').replace(']', '').replace(
'\'NULL\'', 'default') + ');'
conn, cur = connDB()
try:
cur.execute(insert_sql)
except Exception as e:
print(e)
connClose(conn, cur)
log.info(filename.replace('.csv', '') + 'is loaded.')
def update_etf_price_backward():
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = 'etf_price_backward'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
etf_info = dict(data_info)
end_date = datetime.now().strftime('%Y-%m-%d')
w.start()
for i in etf_info:
start_date = (etf_info[i] + timedelta(1)).strftime('%Y-%m-%d')
if start_date > end_date:
continue
etf_price = w.wsd(i, "close,pct_chg", start_date, end_date,
"PriceAdj=B")
for r in range(0, len(etf_price.Times)):
etf_value = tuple([
etf_price.Codes[0], etf_price.Times[r].strftime('%Y-%m-%d'),
etf_price.Data[0][r], etf_price.Data[1][r]
])
insert_sql = 'insert into data.etf_price_backward values ' + str(
etf_value)
try:
conn.cursor().execute(insert_sql)
except Exception as e:
print(e)
update_sql = 'update data.etf_price_backward set close =\'' + str(
etf_price.Data[0][r]) + '\', chg_rate = \'' + str(
etf_price.Data[1][r]
) + '\' where symbol = \'' + etf_price.Codes[
0] + '\' and date = \'' + str(etf_price.Times[r]) + '\''
try:
conn.cursor().execute(update_sql)
except Exception as e:
print(e)
print(i + ' is inserted in etf_price_backward. ')
conn.commit()
connClose(conn, cur)
def update_adj_price(table, type):
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.')
conn, cur = connDB()
items = 'symbol, max(date) as date'
tables = table
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
edate = datetime.now().date().strftime('%Y-%m-%d')
for k in range(0, len(data_info)):
sdate = (data_info[k][1] + timedelta(1)).strftime('%Y-%m-%d')
if sdate > edate:
continue
try:
pricedata = ts.get_k_data(data_info[k][0].replace('.SH',
'').replace(
'.SZ', ''),
ktype='D',
autype=type,
start=sdate,
end=edate)
del pricedata['volume']
except Exception as e:
print(e)
for h in range(0, len(pricedata)):
if data_info[k][0].startswith('6'):
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.SH\'')
else:
values = str(pricedata.values[h].tolist()).replace(
'[', '').replace('\']', '.SZ\'')
insql = 'insert into data.' + table + ' (date,open,close,high,low,symbol) values (' + values + ');'
# print(insql)
try:
conn.cursor().execute(insql)
except Exception as e:
print(e)
conn.commit()
log.info(str(data_info[k][0]) + ' is inserted')
fill_adj_stk(table)
connClose(conn, cur)
def calc_gm(g, m, start_date, end_date):
conn, cur = connDB()
price_sql = 'select a.*, b.m_close from (select date, close as g_close from stk_price_forward where symbol =\'000651.SZ\' ) a inner join (select date, close as m_close from stk_price_forward where symbol =\'000333.SZ\' ) b on a.date =b.date where a.date > \'' + start_date + '\' and a.date <= \'' + end_date + '\'order by a.date asc'
try:
cur.execute(price_sql)
db_data = cur.fetchall()
except Exception as e:
print(e)
df_price = pd.DataFrame(list(db_data),
columns=['date', 'g_close', 'm_close'])
hold_signal = []
hold_return = [0]
hold_cl_ret = [1]
g_cl_ret = [1]
m_cl_ret = [1]
gm_cl_ret = [1]
max_dd = [0]
## 持仓换仓信号，g为格力电器，m为美的集团
for i in range(len(df_price)):
if float(df_price['g_close'].iloc[i]) > (1 - m) * float(
df_price['m_close'].iloc[i]):
hold_signal.append(m)
elif float(df_price['g_close'].iloc[i]) < (1 - g) * float(
df_price['m_close'].iloc[i]):
hold_signal.append(g)
else:
hold_signal.append(0)
## 补充持仓信号，确保换仓后持仓信号的一致性
for j in range(1, len(hold_signal)):
if hold_signal[j] == 0:
hold_signal[j] = hold_signal[j - 1]
## 计算策略组合的每日收益
for k in range(1, len(hold_signal)):
if hold_signal[k - 1] == g:
hold_return.append((float(df_price['g_close'].iloc[k]) -
float(df_price['g_close'].iloc[k - 1])) /
float(df_price['g_close'].iloc[k - 1]) * 100)
elif hold_signal[k - 1] == m:
hold_return.append((float(df_price['m_close'].iloc[k]) -
float(df_price['m_close'].iloc[k - 1])) /
float(df_price['m_close'].iloc[k - 1]) * 100)
else:
hold_return.append(0)
## 计算其他情况的累计收益
for h in range(1, len(hold_return)):
hold_cl_ret.append(hold_cl_ret[h - 1] * (1 + hold_return[h] / 100))
g_cl_ret.append(
round(df_price['g_close'].iloc[h] / df_price['g_close'].iloc[0],
6))
m_cl_ret.append(
round(df_price['m_close'].iloc[h] / df_price['m_close'].iloc[0],
6))
gm_cl_ret.append(
0.5 *
float(df_price['g_close'].iloc[h] / df_price['g_close'].iloc[0]) +
0.5 *
float(df_price['m_close'].iloc[h] / df_price['m_close'].iloc[0]))
## 计算每日最大回撤
for x in range(1, len(hold_cl_ret)):
temp = (hold_cl_ret[x] - max(hold_cl_ret[0:x])) / max(hold_cl_ret[0:x])
if temp <= 0:
max_dd.append(temp)
else:
max_dd.append(0)
max_draw_down = str(round(min(max_dd) * 100, 2)) + '%' # 获得最大回测
df_price['hold_signal'] = pd.DataFrame(hold_signal) # 持有信号，g为格力电器，m为美的集团
df_price['hold_return'] = pd.DataFrame(hold_return) # 持有的日收益
df_price['hold_cl_ret'] = pd.DataFrame(hold_cl_ret) # 持仓的累计收益
df_price['g_cl_ret'] = pd.DataFrame(g_cl_ret) # 单持有格力电器的累计收益
df_price['m_cl_ret'] = pd.DataFrame(m_cl_ret) # 单持有美的集团的累计收益
df_price['gm_cl_ret'] = pd.DataFrame(gm_cl_ret) # 持有格力电器和美的集团各50%仓位的组合累计收益
df_price['max_dd'] = pd.DataFrame(max_dd) # 每日的最大回测数据
# for s in range(0,len(df_price)):
# print(df_price.iloc[s])
# 计算总收益率和年化收益率
total_return = str(round((hold_cl_ret[-1] - 1) * 100, 2)) + '%'
annualized_return = str(
round(
(hold_cl_ret[-1]**(365.25 / (datetime.today() - datetime.strptime(
start_date, '%Y-%m-%d')).days) - 1) * 100, 2)) + '%'
connClose(conn, cur)
print(
str(g) + ' & ' + str(m) + ': ' + total_return + ', ' +
annualized_return + ', ' + max_draw_down)
# 做图
x = np.array(df_price['date'])
y1 = np.array(df_price['hold_cl_ret'])
y2 = np.array(df_price['g_cl_ret'])
y3 = np.array(df_price['m_cl_ret'])
y4 = np.array(df_price['gm_cl_ret'])
y5 = np.array(df_price['max_dd'])
plt.figure(figsize=(9, 6))
## 收益子图
plt.subplot(211)
plt.plot(x, y1, 'r', label='Cigrg_006')
plt.plot(x, y2, 'm', label='000651', linewidth=0.7)
plt.plot(x, y3, 'g', label='000333', linewidth=0.7)
plt.plot(x, y4, 'y', label='0.5*[000651,000333]', linewidth=0.7)
plt.grid(True)
plt.axis('tight')
plt.ylabel('Return')
pic_txt = 'Cumulative Return for Cigrg_006: ' + start_date + ' ~ ' + end_date
plt.legend(loc='upper left', frameon=False)
font_set = FontProperties(fname=r"c:\windows\fonts\simsun.ttc",
size=11.5,
weight='bold')
plt.title(pic_txt, loc='left', fontproperties=font_set)
## 最大回撤子图
plt.subplot(212)
plt.plot(x, y5, 'b')
# plt.xticks(rotation=30)
plt.grid(True)
plt.axis('tight')
plt.xlabel('Date')
plt.ylabel('MaxDrawDown')
plt.ylim(min(max_dd), 0)
PNG_FILENAME = 'CIGRG_002_' + datetime.now().strftime(
'%Y%m%d_%H%M%S') + '.png'
# plt.savefig(PNG_FILENAME)
# plt.show()
return (total_return, annualized_return, max_draw_down, df_price)
# -=-=-=-=-=-=-=-=-=-=-=
# coding=UTF-8
# __author__='Guo Jun'
# Version 1..0.0
# -=-=-=-=-=-=-=-=-=-=-=
from ConnectDB import connDB, connClose, get_data, get_all_data
from datetime import datetime as dt, timedelta
conn, cur = connDB()
# symbol = 'IC1806.CFE'
def fill_fur_price(symbol):
## 获取缺失的datetime列表
req_sql = 'select std_time from (select datetime as std_time from data.fur_price where symbol =\'000300.SH\') a left join (select datetime as symbol_time FROM data.fur_price where symbol =\'' + symbol + '\') b on a.std_time =b.symbol_time where b.symbol_time is null and a.std_time > (SELECT min(datetime) FROM data.fur_price where symbol =\'' + symbol + '\')'
try:
cur.execute(req_sql)
std_date = cur.fetchall()
except Exception as e:
print(e)
std_time = []
for i in range(0, len(std_date)):
std_time.append((std_date[i][0] -
timedelta(minutes=1)).strftime('%Y-%m-%d %H:%M:%S'))
## 获取前一分钟收盘价，准备注入
close_sql = 'select datetime, close from data.fur_price where datetime in (' + str(
std_time).replace('[', '').replace(
']', '') + ') and symbol = \'' + symbol + '\''
try:
def update_stk_fina_calc():
conn, cur = connDB()
request_date = datetime.now().date().strftime('%Y%m%d')
items = 'symbol, max(date) as date'
tables = 'stk_fina_calc'
condition = ' where (rpt_date is not null and eps_ttm is not null and roe_ttm is not null and div_yield is not null) group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
update_info = {}
for i in data_info:
update_info[i[0]] = i[1].strftime('%Y-%m-%d')
symbol_list = list(update_info.keys())
# symbol_list2 = ['002456.SZ','000001.SZ','601318.SH']
w.start()
w_data = w.wsd(symbol_list, "stm_predict_issuingdate", "ED-5M",
request_date, "rptYear=2016;Period=Q;Days=Alldays")
report_info = []
for s in range(0, len(w_data.Codes)):
for r in range(0, len(w_data.Times)):
report_info_temp = []
if w_data.Data[s][r] == None or w_data.Data[s][r].strftime(
'%Y-%m-%d') > request_date:
continue
report_info_temp.append(w_data.Codes[s])
report_info_temp.append(w_data.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data.Data[s][r].strftime('%Y-%m-%d'))
report_info.append(report_info_temp)
for k in report_info:
insert_sql = 'insert into data.stk_fina_calc(symbol, date,rpt_date) values(' + str(
k).replace('[', '').replace(']', '') + ');'
insert_sql = insert_sql.replace('nan', 'default')
update_sql = 'update data.stk_fina_calc set rpt_date = \'' + k[
2] + '\' where symbol = \'' + k[0] + '\' and date = \'' + k[
1] + '\''
update_sql = update_sql.replace('\'nan\'', 'default')
try:
cur.execute(insert_sql)
except Exception as e:
print(e)
try:
cur.execute(update_sql)
except Exception as e:
print(e)
conn.commit()
request_list = []
for t in range(0, len(report_info)):
request_list.append(report_info[t][0])
request_list = list(set(request_list))
# request_list2 = ['000001.SZ','601318.SH']
w_data2 = w.wsd(request_list, "eps_ttm", "ED-5M", request_date,
"rptYear=2016;Period=Q;Days=Alldays")
eps_info = []
for s in range(0, len(w_data2.Codes)):
for r in range(0, 2):
report_info_temp = []
if w_data2.Data[s][r] == None or (
w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '12-31'
and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '03-31'
and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '06-31'
and w_data2.Times[r].strftime('%Y-%m-%d')[5:] != '09-30'
) or np.isnan(w_data2.Data[s][r]):
continue
report_info_temp.append(w_data2.Codes[s])
report_info_temp.append(w_data2.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data2.Data[s][r])
eps_info.append(report_info_temp)
for j in eps_info:
update_sql = 'update data.stk_fina_calc set eps_ttm = \'' + str(
j[2]
) + '\' where symbol = \'' + j[0] + '\' and date = \'' + j[1] + '\''
update_sql = update_sql.replace('\'nan\'', 'default')
try:
cur.execute(update_sql)
except Exception as e:
print(e)
conn.commit()
w_data3 = w.wsd(request_list, "roe_ttm2", "ED-5M", request_date,
"rptYear=2016;Period=Q;Days=Alldays")
roe_info = []
for s in range(0, len(w_data3.Codes)):
for r in range(0, 2):
report_info_temp = []
if w_data3.Data[s][r] == None or (
w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '12-31'
and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '03-31'
and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '06-31'
and w_data3.Times[r].strftime('%Y-%m-%d')[5:] != '09-30'
) or np.isnan(w_data3.Data[s][r]):
continue
report_info_temp.append(w_data3.Codes[s])
report_info_temp.append(w_data3.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data3.Data[s][r])
roe_info.append(report_info_temp)
for g in roe_info:
update_sql = 'update data.stk_fina_calc set roe_ttm = \'' + str(
g[2]
) + '\' where symbol = \'' + g[0] + '\' and date = \'' + g[1] + '\''
update_sql = update_sql.replace('\'nan\'', 'default')
try:
cur.execute(update_sql)
except Exception as e:
print(e)
conn.commit()
w_data4 = w.wsd(request_list, "dividendyield2", "ED-5M", request_date,
"rptYear=2016;Period=Q;Days=Alldays")
div_info = []
for s in range(0, len(w_data4.Codes)):
for r in range(0, 2):
report_info_temp = []
if w_data4.Data[s][r] == None or (
w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '12-31'
and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '03-31'
and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '06-31'
and w_data4.Times[r].strftime('%Y-%m-%d')[5:] != '09-30'
) or np.isnan(w_data4.Data[s][r]):
continue
report_info_temp.append(w_data4.Codes[s])
report_info_temp.append(w_data4.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data4.Data[s][r])
div_info.append(report_info_temp)
for f in div_info:
update_sql = 'update data.stk_fina_calc set div_yield = \'' + str(
f[2]
) + '\' where symbol = \'' + f[0] + '\' and date = \'' + f[1] + '\''
update_sql = update_sql.replace('\'nan\'', 'default')
try:
cur.execute(update_sql)
except Exception as e:
print(e)
conn.commit()
w_data5 = w.wsd(request_list, "roic_ttm", "ED-5M", request_date,
"Period=Q;Days=Alldays")
for s in range(0, len(w_data5.Codes)):
for r in range(0, len(w_data5.Times)):
if np.isnan(w_data5.Data[s][r]):
continue
update_sql = 'update data.stk_fina_calc set roic_ttm =\'' + str(w_data5.Data[s][r]) + '\' where symbol =\'' + \
w_data5.Codes[s] + '\' and date = \'' + w_data5.Times[r].strftime('%Y-%m-%d') + '\';'
try:
conn.cursor().execute(update_sql)
except Exception as e:
print(e)
conn.commit()
log.info('stk_fina_calc is updated.')
connClose(conn, cur)
def update_stk_ratio():
conn, cur = connDB()
request_date = datetime.now().date().strftime('%Y-%m-%d')
items = 'symbol'
tables = 'stk_ratio'
condition = ' group by symbol order by symbol'
data_info = get_all_data(items, tables, condition)
symbol_list = []
for i in data_info:
symbol_list.append(i[0])
w.start()
w_data = w.wsd(symbol_list, "pe_ttm", "ED-1TD", request_date,
"") # "ED-1TD"
# w_data = w.wsd(symbol_list, "pe_ttm", '2018-04-02', '2018-04-02', "")
pe_info = []
for s in range(0, len(w_data.Codes)):
for r in range(0, len(w_data.Times)):
report_info_temp = []
if np.isnan(w_data.Data[s][r]):
continue
report_info_temp.append(w_data.Codes[s])
report_info_temp.append(w_data.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data.Data[s][r])
pe_info.append(report_info_temp)
w_data2 = w.wsd(symbol_list, "pb_lf", "ED-1TD", request_date,
"") # "ED-1TD"
pb_info = []
for s in range(0, len(w_data2.Codes)):
for r in range(0, len(w_data2.Times)):
report_info_temp = []
if np.isnan(w_data2.Data[s][r]):
continue
report_info_temp.append(w_data2.Codes[s])
report_info_temp.append(w_data2.Times[r].strftime('%Y-%m-%d'))
report_info_temp.append(w_data2.Data[s][r])
pb_info.append(report_info_temp)
for k in pb_info:
insert_sql = 'insert into data.stk_ratio (symbol, date, pb) values (' + str(
k).replace('[', '').replace(']', '') + ');'
try:
cur.execute(insert_sql)
except Exception as e:
print(e)
conn.commit()
for j in pe_info:
update_sql = 'update data.stk_ratio set pe_ttm = \'' + str(
j[2]
) + '\' where symbol = \'' + j[0] + '\' and date = \'' + j[1] + '\';'
try:
cur.execute(update_sql)
except Exception as e:
print(e)
conn.commit()
connClose(conn, cur)
log.info('stk_ratio is updated.')
def fill_ratio_data():
conn, cur = connDB()
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])
conn, cur = connDB()
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:
cur.execute(insert_sql)
except Exception as e:
print(e)
conn.commit()
log.info(symbol_list3 + 'is filled.')
connClose(conn, cur)
fill_ratio_data()
def fill_ratio_data():
conn, cur = connDB()
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])
conn, cur = connDB()
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:
cur.execute(insert_sql)
except Exception as e:
print(e)
conn.commit()
log.info(symbol_list3 + 'is filled.')
connClose(conn, cur)
