forked from winsert/an
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdd.py
271 lines (241 loc) · 9.15 KB
/
dd.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# 本程序用于记录可转债每日的开盘价，收盘价，溢价率，年化收益率等数据
__author__ = '[email protected]'
import sqlite3, urllib2
from datetime import datetime
# 生成日期
def getDATE():
now_time = datetime.now()
year = str(now_time.year)
month = now_time.month
if month < 10:
month = '0' + str(month)
else:
month = str(month)
day = now_time.day
if day < 10:
day = '0' + str(day)
else:
day = str(day)
today = year+month+day
#print today
return today
# 用于解析URL页面
def bsObjForm(url):
headers = {'User-Agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) ' 'Chrome/51.0.2704.63 Safari/537.36'}
req = urllib2.Request(url=url, headers=headers)
html = urllib2.urlopen(req).read().decode('gbk','ignore')
return html
# 用于查询正股的数据
def getZG(zgCode):
key = zgCode
url = "http://hq.sinajs.cn/list="+key #生成用于查询的URL
try:
resp = bsObjForm(url)
tmp_list = resp.split(',')
zg_s = tmp_list[1] #获取开盘价
zg_e = tmp_list[3] #获取收盘价
zg_h = tmp_list[4] #获取最高价
zg_l = tmp_list[5] #获取最低价
return zg_s, zg_e, zg_h, zg_l
except:
zg_s, zg_e, zg_h, zg_l = '0.000'
return zg_s, zg_e, zg_h, zg_l
# 用于查询转债的数据
def getZZ(zzCode):
key = zzCode
url = "http://hq.sinajs.cn/list="+key #生成用于查询的URL
try:
resp = bsObjForm(url)
tmp_list = resp.split(',')
zz_s = tmp_list[1] #获取开盘价
zz_e = tmp_list[3] #获取收盘价
zz_h = tmp_list[4] #获取最高价
zz_l = tmp_list[5] #获取最低价
zz_z = tmp_list[8] #获取成交张数
zz_j = tmp_list[9] #获取成交金额
return zz_s, zz_e, zz_h, zz_l, zz_z, zz_j
except:
zz_s, zz_e, zz_h, zz_l, zz_z, zz_j = '0.000'
return zz_s, zz_e, zz_h, zz_l, zz_z, zz_j
# 计算剩余年限
def getSYNX(dqr):
ymd = dqr #到期日
y = ymd.split('-')
d1 = datetime(int(y[0]), int(y[1]), int(y[2]), 0, 0)
synx = round((d1 - datetime.now()).days / 365.00, 3)
return synx
# 计算到期价值
def getDQJZ(synx, shj, ll):
synx = synx #剩余年限
shj = float(shj) #赎回价
mnlv = ll #每年的利率
dqjz = 0.0
int_synx = int(synx)
if synx > int_synx:
synx = int_synx + 1
else:
synx = int_synx
#print synx
l = mnlv.split(',') #转成列表
for i in range (len(l)-synx, len(l)-1):
dqjz = dqjz +round(float(l[i])*0.8, 2)
dqjz = dqjz + shj
return dqjz
#将查询结果写入数据库dd.db
def getRECORD(today, code, zg_s, zg_e, zg_h, zg_l, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j, yjl, dqnh):
today = today #日期
code = 'c'+code #转债代码
zg_s = zg_s #正股开盘价
zg_e = zg_e #正股收盘价
zg_h = zg_h #正股最高价
zg_l = zg_l #正股最低价
zz_s = zz_s #转债开盘价
zz_e = zz_e #转债收盘价
zz_h = zz_h #转债最高价
zz_l = zz_l #转债最低价
zz_z = zz_z #转债成交张数
zz_j = zz_j #转债成交金额
yjl = yjl #溢价率
dqnh = dqnh #年化收益率
conn = sqlite3.connect('dd.db')
create_tb_cmd = "CREATE TABLE IF NOT EXISTS %s (today text, zg_s text, zg_e text, zg_h text, zg_l text, zz_s text, zz_e text, zz_h text, zz_l text, zz_z text, zz_j text, yjl text, dqnh text);" %code
conn.execute(create_tb_cmd)
curs = conn.cursor()
sql_cmd = "select count(*) FROM %s WHERE today = %s" %(code, today)
curs.execute(sql_cmd)
lens = curs.fetchall()[0][0]
print lens
if lens == 0:
insert_dt_cmd = "INSERT INTO %s (today, zg_s, zg_e, zg_h, zg_l, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j, yjl, dqnh) VAlUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" %code
conn.execute(insert_dt_cmd, (today, zg_s, zg_e, zg_h, zg_l, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j, yjl, dqnh))
conn.commit()
curs.close()
conn.close()
# 记录转债、正股的日交易数据
def getCX(today):
today = today
try:
conn = sqlite3.connect('cb.db')
curs = conn.cursor()
sql = "select name, zzcode, zgcode, Prefix, zgj, dqr, shj, ll, ce from cb where code > 0 and ce = 'c'"
curs.execute(sql)
tmp = curs.fetchall()
curs.close()
conn.close()
i = 0
for cb in tmp:
i = i+1
name = cb[0] #转债名称
#print name
code = cb[1] #转债代码
zgcode = cb[2] #正股代码
prefix = cb[3] #前缀
zgj = float(cb[4]) #转股价
dqr = cb[5] #到期日
shj = cb[6] #赎回价
ll = cb[7] #每年的利率
ce = cb[8] #区别转债和交换债
zgcode = cb[3]+cb[2] #正股代码
zg_s, zg_e, zg_h, zg_l = getZG(zgcode) #查询正股开盘，收盘，最高，最低价数据
#print name, zg_s, zg_e, zg_h, zg_l
if zg_h != '0.000' and zg_l != '0.000': #判断正股是否停牌
zzcode = cb[3]+cb[1] #转债代码
zz_s, zz_e, zz_h, zz_l, zz_z, zz_j = getZZ(zzcode) #查询转债开盘，收盘，最高,最低价，成交张数，成交金额等数据
#print name, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j
zgjz = (100/float(zgj))*float(zg_e) #计算转股价值
yjl = str(round((float(zz_e) - zgjz)/zgjz*100, 2)) #计算溢价率
#print name, zgjz, yjl
synx = getSYNX(dqr) #计算剩余年限
dqjz = getDQJZ(synx, shj, ll) #计算到期价值
#print name, dqjz
dqsyl = round((dqjz/float(zz_e) - 1) * 100, 3) #计算到期收益率
dqnh = str(round(dqsyl/synx, 2)) #计算到期年化收益率
print today, name, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j, yjl, dqnh
getRECORD(today, code, zg_s, zg_e, zg_h, zg_l, zz_s, zz_e, zz_h, zz_l, zz_z, zz_j, yjl, dqnh)
print
print today + u" 共有 "+str(i)+u" 只可转债生成交易数据。\n"
except Exception, e:
print e
#写入cbt表
def NXSQL(record): #向数据库insert新记录
rec = record
try:
conn = sqlite3.connect('dd.db')
curs = conn.cursor()
sql = "insert into cbt (date, cjje, csum, vsum) VALUES (?, ?, ?, ?)"
curs.execute(sql, rec)
conn.commit()
curs.close()
conn.close()
print u"已记入dd.db数据库的cbt表。"
print
except Exception,e:
print "NXSQL_Error is:", e
#显示牛熊占比
def getNX():
record = []
record.append(today)
try:
conn = sqlite3.connect('cb.db')
curs = conn.cursor()
sql = "select * from cb where code>0 and ce!='e'"
curs.execute(sql)
tmp = curs.fetchall()
curs.close()
conn.close()
cbs = []
cjje = 0.0
for cb in tmp:
name = cb[3] #转债名称
zzcode = cb[5] #转债代码
prefix = cb[7] #前缀
zzCode = prefix+zzcode
zz_s, zz_e, zz_h, zz_l, zz_z, zz_j = getZZ(zzCode)
cjje = cjje + float(zz_j)
dqr = cb[19] #到期日
shj = cb[20] #赎回价
ll = cb[24] #每年的利率
synx = getSYNX(dqr) #计算剩余年限
dqjz = getDQJZ(synx, shj, ll) #计算到期价值
if float(zz_e) > dqjz:
cbs.append(cb)
#print name, zz_e, dqjz
cjje = round(cjje / 100000000, 2)
record.append(cjje)
record.append(len(tmp))
record.append(len(cbs))
rate = round(float(len(cbs))/float(len(tmp)), 4) * 100
#print len(tmp), len(cbs), rate, round(cjje / 100000000, 2)
print today + u' 共有'+str(len(tmp))+u'只转债，其中'+str(len(cbs))+u'只转债的收盘价>到期价值，占比：'+str(rate)+'%。'
NXSQL(record)
except Exception, e:
print 'getNX()_error is : ', e
if __name__ == '__main__':
today = getDATE() #生成日期
print u"\n是否增加 " + today + u" 的数据？",
yn = raw_input("(y/n) ?")
if yn == 'y':
print u"\n正在增加 " + today + u" 的数据......"
getCX(today)
getNX() #显示牛熊占比
print u"\n操作已完成。"
elif yn == 'n':
print u"\n今天是：" + today
date = int(raw_input("请输入日期，例：20180707 > "))
while date > int(today):
print u"\n今天是：" + today
date = int(raw_input("请输入正确日期，例：20180707 > "))
print u"将增加 " + str(date) + u" 的数据？",
ny = raw_input("(y/n) ?")
if ny == 'y':
print u"\n正在增加 " +str(date) + u" 的数据......"
ds = str(date)
getCX(ds)
getNX() #显示牛熊占比
print u"\n操作已完成。"
else:
print u"\n没有增加新数据！"
else:
print u"\n没有增加新数据！"