def dayreturn(symbol):
conn, cur = connDB()
getsql = 'select Date, closeprice from his_idx where symbol =\'' + symbol + '\'' + ' order by Date desc limit 251'
# print(getsql)
exeQuery(cur, getsql)
records = list(cur)
# print(records)
pricedata = []
pricedata.append(datetime.datetime.strftime(records[0][0], '%Y-%m-%d'))
pricedata.append(float(records[0][1]))
returndays = [1, 5, 10, 15, 20, 30, 60, 120, 200, 250]
# print(symbol)
for i in returndays:
if i < len(records):
pricedata.append(
datetime.datetime.strftime(records[i][0], '%Y-%m-%d'))
pricedata.append(float(records[i][1]))
pricedata.append(
float(
round(
(records[0][1] - records[i][1]) / records[i][1] * 100,
3)))
else:
pricedata.append(
datetime.datetime.strftime(records[-1][0], '%Y-%m-%d'))
pricedata.append(float(records[-1][1]))
pricedata.append(
float(
round((records[0][1] - records[-1][1]) / records[-1][1] *
100, 3)))
# print(pricedata)
connClose(conn, cur)
return pricedata
def annualreturn(sdate, edate, symbol):
conn, cur = connDB()
if len(symbol) == 6:
symbollist = '(\'' + str(symbol) + '\')'
else:
symbollist = str(symbol)
# print(len(symbol))
getsql = 'select * from list_all where symbol in ' + symbollist + ' order by symbol'
# print(getsql)
exeQuery(cur, getsql)
sqlcontent = np.array(cur.fetchall())
content = sqlcontent[:, 0:3]
# print(content)
recorddate = '(\'' + str(sdate) + '\',\'' + str(edate) + '\')'
content3 = list()
for i in range(sqlcontent.shape[0]):
if sqlcontent[i][1] in ('Stock', 'Index'):
readsql = 'select Date,closeprice from ' + sqlcontent[i][
3] + ' where symbol = \'' + sqlcontent[i][
0] + '\' and date in ' + recorddate + ' order by date asc;'
elif sqlcontent[i][1] in ('ETF', 'LOF', 'classfund'):
readsql = 'select Date,CulNav from ' + sqlcontent[i][
3] + ' where symbol = \'' + sqlcontent[i][
0] + '\' and date in ' + recorddate + ' order by date asc;'
try:
exeQuery(cur, readsql)
content2 = list(cur.fetchall())
returnrate = round(
(content2[1][1] - content2[0][1]) / content2[0][1] * 100, 3)
content3.append(content2[0][0])
content3.append(content2[0][1])
content3.append(content2[1][0])
content3.append(content2[1][1])
content3.append(returnrate)
content3.append(sqlcontent[i][0])
except Exception as e:
print(e)
print(sqlcontent[i][0])
content4 = array(content3)
content4.resize((sqlcontent.shape[0], 6))
finalContent = hstack((content, content4))
title = [
'Symbol', 'Type', 'Name', 'StartDate', 'StartPrice', 'CloseDate',
'ClosePrice', 'ReturnRate', 'Symbol'
]
dates = pd.DataFrame(finalContent, index=None, columns=title)
# print(dates)
print(dates.sort_values(axis=0, by=['ReturnRate'], ascending=False))
connClose(conn, cur)
return
def annualreturn(sdate,edate,symbol):
conn,cur=connDB()
if len(symbol) == 6:
symbollist = '(\'' + str(symbol) +'\')'
else:
symbollist = str(symbol)
# print(len(symbol))
getsql ='select * from list_all where symbol in ' + symbollist + ' order by symbol'
# print(getsql)
exeQuery(cur,getsql)
sqlcontent = np.array(cur.fetchall())
content = sqlcontent[:,0:3]
# print(content)
recorddate = '(\'' + str(sdate) + '\',\'' + str(edate) + '\')'
content3 = list()
for i in range(sqlcontent.shape[0]):
if sqlcontent[i][1] in ('Stock','Index') :
readsql = 'select Date,closeprice from ' + sqlcontent[i][3] + ' where symbol = \'' + sqlcontent[i][0] + '\' and date in ' + recorddate + ' order by date asc;'
elif sqlcontent[i][1] in ('ETF','LOF','classfund') :
readsql = 'select Date,CulNav from ' + sqlcontent[i][3] + ' where symbol = \'' + sqlcontent[i][0] + '\' and date in ' + recorddate + ' order by date asc;'
try:
exeQuery(cur,readsql)
content2=list(cur.fetchall())
returnrate = round((content2[1][1] - content2[0][1])/content2[0][1]*100,3)
content3.append(content2[0][0])
content3.append(content2[0][1])
content3.append(content2[1][0])
content3.append(content2[1][1])
content3.append(returnrate)
content3.append(sqlcontent[i][0])
except Exception as e:
print(e)
print(sqlcontent[i][0])
content4 = array(content3)
content4.resize((sqlcontent.shape[0],6))
finalContent = hstack((content,content4))
title = ['Symbol','Type','Name','StartDate','StartPrice','CloseDate','ClosePrice','ReturnRate','Symbol']
dates = pd.DataFrame(finalContent,index=None,columns = title)
# print(dates)
print(dates.sort_values(axis=0, by=['ReturnRate'], ascending=False))
connClose(conn, cur)
return
def pricereturn():
conn, cur = connDB()
getsql1 = 'select symbol,name from list_all where type = \'Index\' order by symbol;'
exeQuery(cur, getsql1)
symboldetail = dict(cur)
symbollist = tuple(symboldetail)
for symbol in symbollist:
pricedata = dayreturn(symbol)
pricedata.insert(0, symbol)
pricedata.append(time.strftime("%Y-%m-%d %X", time.localtime()))
insertdata = str(pricedata).replace('[', '(').replace(']', ')')
insertsql = 'delete from return_idx where symbol=\'' + symbol + '\' ;' + ' insert into return_idx values ' + insertdata + ';'
# print(insertsql)
try:
exeUpdate(cur, insertsql)
except Exception as e:
print(e)
print(insertsql)
print('Return data is updated to latest records.')
connClose(conn, cur)
return
# import pandas as pd
import time
import urllib.request
import re
from decimal import Decimal
conn,cur=connDB()#连接数据库
universe = ('399102','399004', '399006', '399610', '399008', '399337', '399005', '399300','399330','399001') #定义Universe
#获取已有Return数据
getsql = 'SELECT a.symbol,b.idx_name,\
ret_1day,ret_5days,ret_10days,ret_20days,ret_30days,ret_60days,ret_120days,ret_200days,ret_250days \
FROM tradeinfo.return_idx a left join list_idx b on a.symbol =b.symbol \
where a.symbol in ' + str(universe) + ' order by ret_20days desc'
exeQuery(cur,getsql)
sqlcontent = np.array(cur.fetchall())
#获取第前20日ClosePrice
getsql2 = 'SELECT a.symbol,b.idx_name,closeprice_20days \
FROM tradeinfo.return_idx a left join list_idx b on a.symbol =b.symbol \
where a.symbol in ' + str(universe) + ' order by ret_20days desc'
exeQuery(cur,getsql2)
sqlcontent3 = np.array(cur.fetchall())
#获取Universe内所有指数实时价格
headers = {'User-Agent' : 'Mozilla/12 (compatible; MSIE 1.0; Windows NT 4.0)'}
codes = str(universe).replace('\'','').replace(',',',s_sz').replace('(','').replace(')','').replace(' ','')
url2 = 'http://qt.gtimg.cn/q=s_sz'+codes
# import pandas as pd
import time
import urllib.request
import re
from decimal import Decimal
conn, cur = connDB() #连接数据库
universe = ('399102', '399004', '399006', '399610', '399008', '399337',
'399005', '399300', '399330', '399001') #定义Universe
#获取已有Return数据
getsql = 'SELECT a.symbol,b.idx_name,\
ret_1day,ret_5days,ret_10days,ret_20days,ret_30days,ret_60days,ret_120days,ret_200days,ret_250days \
FROM tradeinfo.return_idx a left join list_idx b on a.symbol =b.symbol \
where a.symbol in ' + str(universe) + ' order by ret_20days desc'
exeQuery(cur, getsql)
sqlcontent = np.array(cur.fetchall())
#获取第前20日ClosePrice
getsql2 = 'SELECT a.symbol,b.idx_name,closeprice_20days \
FROM tradeinfo.return_idx a left join list_idx b on a.symbol =b.symbol \
where a.symbol in ' + str(universe) + ' order by ret_20days desc'
exeQuery(cur, getsql2)
sqlcontent3 = np.array(cur.fetchall())
#获取Universe内所有指数实时价格
headers = {'User-Agent': 'Mozilla/12 (compatible; MSIE 1.0; Windows NT 4.0)'}
codes = str(universe).replace('\'', '').replace(',', ',s_sz').replace(
'(', '').replace(')', '').replace(' ', '')
url2 = 'http://qt.gtimg.cn/q=s_sz' + codes
try: