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_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 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)
# -*- coding: utf-8 -*-
"""
Created on Sat Mar 3 11:00:41 2018
@author: lpeng
"""
import pandas as pd
import numpy as np
from CIGRG.WindPy import w
import datetime
from pandas import DataFrame
w.start()
'''以下模块主要用于寻找期货换月日期'''
startdate = (datetime.date.today() -
datetime.timedelta(365 * 4)).strftime('%Y%m%d')
enddate = (datetime.date.today()).strftime('%Y%m%d')
i = w.wsd("I.DCE", "trade_hiscode,close", startdate, enddate, "")
i_data = DataFrame(i.Data, index=['i_HISCODE', 'CLOSE'], columns=i.Times).T
def find_change_index(data):
dif_index = []
for i in range(1, len(data)):
if data[i] != data[i - 1]:
dif_index.append(i)
return dif_index
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 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, "roic2_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 fill_suspend_stk_data():
items = 'date'
table = 'idx_price'
condition = ' where symbol= \'000001.SH\' and date > \'2015-01-02\' order by date asc'
idx_date = get_all_data(items, table, condition)
date_list = []
for j in idx_date:
date_list.append(j[0])
w.start()
request_date = date_list[-1].strftime('%Y-%m-%d')
set_info = 'startdate=' + request_date + ';enddate=' + request_date + ';field=wind_code'
suspend_symbols = w.wset("tradesuspend", set_info)
symbols = suspend_symbols.Data[0]
def fill_suspend_data_bf(tables):
for i in symbols:
if i.startswith('0') or i.startswith('3') or i.startswith('6'):
items = 'date, close'
table = tables
condition = ' where symbol=\'' + i + '\' order by date desc limit 1'
stk_data = get_all_data(items, table, condition)
if len(stk_data
) == 0 or stk_data[0][0] >= datetime.now().date():
continue
stk_date_list = date_list[date_list.index(stk_data[0][0]) + 1:]
for k in stk_date_list:
insert_sql = 'insert into data.' + tables + ' values(\'' + i + '\',\'' + k.strftime(
'%Y-%m-%d') + '\',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ');'
# print(insert_sql)
fill_data(insert_sql)
print(i + '\'s data are updated.')
def fill_suspend_data(tables):
for i in symbols:
if i.startswith('0') or i.startswith('3') or i.startswith('6'):
items = 'date, close, mktcap,trade_mktcap'
table = tables
condition = ' where symbol=\'' + i + '\' order by date desc limit 1'
stk_data = get_all_data(items, table, condition)
if len(stk_data
) == 0 or stk_data[0][0] >= datetime.now().date():
continue
stk_date_list = date_list[date_list.index(stk_data[0][0]) + 1:]
for k in stk_date_list:
insert_sql = 'insert into data.' + tables + ' values(\'' + i + '\',\'' + k.strftime(
'%Y-%m-%d') + '\',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',' + str(
float(stk_data[0][1])) + ',0,0,' + str(
float(stk_data[0][2])) + ',' + str(
float(stk_data[0][2])) + ');'
# print(insert_sql)
fill_data(insert_sql)
print(i + '\'s data are updated.')
# tbs = ['stk_price_forward', 'stk_price_backward']
fill_suspend_data_bf('stk_price_forward')
fill_suspend_data_bf('stk_price_backward')
fill_suspend_data('stk_price')
# -*- coding:utf-8 -*-
from CIGRG.WindPy import w
from __builtin__ import str # Python 3.0 以上版本的用户请注释掉这一句
#from datetime import *
w.start();
# 命令如何写可以用命令生成器来辅助完成
# 定义打印输出函数，用来展示数据使用
def printpy(outdata):
if outdata.ErrorCode!=0:
print('error code:'+str(outdata.ErrorCode)+'\n');
return();
for i in range(0,len(outdata.Data[0])):
strTemp=''
if len(outdata.Times)>1:
strTemp=str(outdata.Times[i])+' '
for k in range(0, len(outdata.Fields)):
strTemp=strTemp+str(outdata.Data[k][i])+' '
print(strTemp)
# 通过wsd来提取时间序列数据，比如取开高低收成交量，成交额数据
print('\n\n'+'-----通过wsd来提取时间序列数据，比如取开高低收成交量，成交额数据-----'+'\n')
wsddata1=w.wsd("000001.SZ", "open,high,low,close,volume,amt", "2015-11-22", "2015-12-22", "Fill=Previous")
printpy(wsddata1)
# 通过wsd来提取各个报告期财务数据
print('\n\n'+'-----通过wsd来提取各个报告期财务数据-----'+'\n')
wsddata2=w.wsd("600000.SH", "tot_oper_rev,tot_oper_cost,opprofit,net_profit_is", "2008-01-01", "2015-12-22", "rptType=1;Period=Q;Days=Alldays;Fill=Previous")
printpy(wsddata2)
# 通过wss来取截面数据
print('\n\n'+'-----通过wss来取截面数据-----'+'\n')