forked from Rockyzsu/stock
-
Notifications
You must be signed in to change notification settings - Fork 0
/
recordMyChoice.py
198 lines (168 loc) · 6.74 KB
/
recordMyChoice.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
# -*-coding=utf-8-*-
# 记录每天选股后的收益,用于跟踪每一只自选股
__author__ = 'Rocky'
'''
https://30daydo.com
Contact: [email protected]
'''
import datetime
import os
import xlrd
import time
from xlutils.copy import copy
import tushare as ts
from configure.settings import get_mysql_conn
import codecs
from configure.settings import LLogger
logger=LLogger('recordMyChoice.log')
class Prediction_rate():
def __init__(self):
self.today_stock = ts.get_today_all()
now = datetime.datetime.now()
self.today = now.strftime("%Y-%m-%d")
# weekday=now+datetime.timedelta(days=-2)
# weekday=weekday.strftime("%Y-%m-%d")
# print(weekday)
# TODAY=now.strftime('%Y-%m-%d')
self.path = os.path.join(os.getcwd(), 'data')
self.filename = os.path.join(self.path, 'recordMyChoice.xls')
def stock_pool(self, stock_list):
pass
def first_record(self, stockID):
# stockID_list=['000673']
wb = xlrd.open_workbook(self.filename)
table = wb.sheets()[0]
nrow = table.nrows
ncol = table.ncols
print("%d*%d" % (nrow, ncol))
row_start = nrow
wb_copy = copy(wb)
sheet = wb_copy.get_sheet(0)
# 调用 write 函数写入 info write(1,1,'Hello')
# content = []
mystock = self.today_stock[self.today_stock['code'] == stockID]
name = mystock['name'].values[0]
in_price = mystock['trade'].values[0]
current_price = in_price
profit = 0.0
content = [self.today, stockID, name, in_price, current_price, profit]
for i in range(len(content)):
sheet.write(row_start, i, content[i])
row_start = row_start + 1
wb_copy.save(self.filename)
def update(self):
# 对已有的进行更新
pass
'''
持股信息保存到Mysql数据库, 更新,删除
'''
class StockRecord:
def __init__(self):
self.conn = get_mysql_conn('db_stock',local=True)
self.cur = self.conn.cursor()
self.table_name = 'tb_profit'
self.today = datetime.datetime.now().strftime('%Y-%m-%d')
# self.TODAY = '2018-04-13'
def holding_stock_sql(self):
path = os.path.join(os.path.dirname(__file__), 'data', 'mystock.csv')
if not os.path.exists(path):
return
create_table_cmd = 'CREATE TABLE IF NOT EXISTS `tb_profit` (`证券代码` CHAR (6),`证券名称` VARCHAR (16), `保本价` FLOAT,`股票余额` INT,`盈亏比例` FLOAT,`盈亏` FLOAT, `市值` FLOAT);'
try:
self.cur.execute(create_table_cmd)
self.conn.commit()
except Exception as e:
# print(e)
logger.log(e)
self.conn.rollback()
with codecs.open(path, 'r', encoding='utf-8') as f:
content = f.readlines()
for i in range(1, len(content)):
code, name, safe_price, count = content[i].strip().split(',')[:4]
print(code, name, safe_price, count)
insert_cmd = 'INSERT INTO `tb_profit` (`证券代码`,`证券名称`,`保本价`,`股票余额`) VALUES(\"%s\",\"%s\",\"%s\",\"%s\");' % (
code.zfill(6), name, safe_price, count)
self._exe(insert_cmd)
def delete(self, content):
name = u"证券名称"
cmd = u"DELETE FROM `{}` WHERE `{}` = \"{}\"".format(self.table_name, name, content)
self._exe(cmd)
def insert(self, code, name, safe_price, count):
'''
:param code: 代码
:param name: 名称
:param safe_price: 保本价
:param count: 股票数目
:return: None
'''
insert_cmd = 'INSERT INTO `tb_profit` (`证券代码`,`证券名称`,`保本价`,`股票余额`) VALUES(\"%s\",\"%s\",\"%s\",\"%s\");' % (
code.zfill(6), name, safe_price, count)
self._exe(insert_cmd)
# 执行mysql语句
def _exe(self, cmd):
try:
self.cur.execute(cmd)
self.conn.commit()
except Exception as e:
# print(e)
logger.log(e)
self.conn.rollback()
return self.cur
# 更新每天的盈亏情况
def update_daily(self):
add_cols = 'ALTER TABLE `{}` ADD `{}` FLOAT;'.format(self.table_name, self.today)
self._exe(add_cols)
# self.conn.commit()
api = ts.get_apis()
cmd = 'SELECT * FROM `{}`'.format(self.table_name)
cur = self._exe(cmd)
for i in cur.fetchall():
(code, name, safe_price, count, profit_ratio, profit, values, current_price,earn) = i[:9]
df = ts.quotes(code, conn=api)
current_price = round(float(df['price'].values[0]), 2)
values = current_price * count
last_close = df['last_close'].values[0]
earn = (current_price - last_close) * count
profit = (current_price - safe_price) * count
profit_ratio = round(float(current_price - safe_price) / safe_price * 100, 2)
update_cmd = 'UPDATE {} SET `盈亏比例`={} ,`盈亏`={}, `市值` ={}, `现价` = {},`{}`={} where `证券代码`= {};'.format(
self.table_name, profit_ratio, profit, values, current_price, self.today, earn,code)
# print(update_cmd)
self._exe(update_cmd)
ts.close_apis(api)
# 删除某行
def update_item(self, code, content):
cmd = 'UPDATE `{}` SET `保本价`={} where `证券代码`={};'.format(self.table_name, content, code)
self._exe(cmd)
def update_sold(self):
cur = self.conn.cursor()
tb_name = 'tb_sold_stock'
cur.execute('select * from {}'.format(tb_name))
content = cur.fetchall()
db_daily = get_mysql_conn('db_daily')
db_cursor=db_daily.cursor()
stock_table = datetime.datetime.now().strftime('%Y-%m-%d')
# stock_table = '2018-05-02'
for i in content:
cmd='select `trade` from `{}` where `code`=\'{}\''.format(stock_table,i[0])
print(cmd)
db_cursor.execute(cmd)
ret = db_cursor.fetchone()
sold_price = i[3]
percentange =round(float(ret[0]- sold_price)/sold_price*100,2)
update_cmd = 'update `{}` set `当前价`={} ,`卖出后涨跌幅`= {} where `代码`=\'{}\''.format(tb_name,ret[0],percentange,i[0])
print(update_cmd)
cur.execute(update_cmd)
self.conn.commit()
if __name__ == "__main__":
if ts.is_holiday(datetime.datetime.now().strftime('%Y-%m-%d')):
exit(0)
# obj=Prediction_rate()
# obj.first_recode()
# holding_stock_sql()
obj = StockRecord()
# obj.delete('深F120')
# obj.insert('300580','贝斯特',19.88,200)
obj.update_daily()
obj.update_sold()
# obj.update_item('300580',32.568)