class MonitorFund(BaseService):
def __init__(self):
super(MonitorFund, self).__init__('../log/monitor_fund.log')
self.conn = DBSelector().get_mysql_conn('db_stock', 'qq')
def fast_speed_up(self):
table = '2020-02-25' # 用于获取code列
today = datetime.datetime.now().strftime('%Y-%m-%d')
print(today)
logger = self.logger.info(f'{today}_fund_raise_monitor.log')
query = 'select `基金代码`,`基金简称` from `2020-02-25`'
# print(query)
cursor = self.conn.cursor()
cursor.execute(query)
ret = cursor.fetchall()
code_list = []
for item in ret:
code = item[0]
df = ts.get_realtime_quotes(code)
close_p = float(df['pre_close'].values[0])
b1 = float(df['b1_p'].values[0])
a1 = float(df['a1_p'].values[0])
percent = (a1 - b1) / close_p * 100
if percent > 5:
print(f'{item[0]} {item[1]} 有超过5%的委买卖的差距')
logger.info(f'{item[0]} {item[1]} 有超过5%的委买卖的差距')
time.sleep(random.random())
def save_mysql(self, insert_data, join_str):
conn = DBSelector().get_mysql_conn('db_stock', 'qq')
cursor = conn.cursor()
cursor.execute(
'SELECT id FROM db_stock.bond_overview order by id desc limit 1')
idx = cursor.fetchone()
idx = idx[0]
# insert_sql = f'''insert into `bond_overview` (`id`,`date`, `price`,`volume`,`amount`,`count`,`avg_price`,`mid_price`,`avg_premium_rt`, `avg_ytm_rt`, `increase_val`, `increase_rt`, `turnover_rt`, `price_90`, `price_90_100`, `price_100_110`, `price_110_120`, `price_120_130`, `price_130`, `idx_price`, `idx_increase_rt`）value ({join_str})'''
insert_sql = f'''insert into `bond_overview` values ({join_str})'''
insert_data.insert(0, idx + 1)
cursor.execute(insert_sql, insert_data)
conn.commit()
conn.close()
class AAStockNewStock(BaseService):
def __init__(self):
super(AAStockNewStock, self).__init__('../log/aastock.log')
self.conn = DBSelector().get_mysql_conn('db_stock')
self.cursor = self.conn.cursor()
def create_table(self):
sql = '''CREATE TABLE IF NOT EXISTS `tb_hk_new_stock` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` varchar(50) DEFAULT NULL,
`code` varchar(10) NOT NULL,
`issue_date` date DEFAULT NULL,
`each_hand_stock` varchar(50) DEFAULT NULL,
`share_value_Yi` varchar(50) DEFAULT NULL,
`margin_price` varchar(50) DEFAULT NULL,
`price` float(255,4) DEFAULT NULL,
`over_price_part` varchar(50) DEFAULT NULL,
`hit_least_num` int(255) DEFAULT NULL,
`hit_ratio` float(255,4) DEFAULT NULL,
`current_price` float(255,4) DEFAULT NULL,
`first_day_raise` float(255,4) DEFAULT NULL,
`accumulate_raise` float(255,4) DEFAULT NULL,
`crawltime` DATETIME DEFAULT NULL,
UNIQUE INDEX code_ix(`code` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'''
try:
self.cursor.execute(sql)
except Exception as e:
print(e)
self.conn.rollback()
else:
self.conn.commit()
def fetch(self, page):
options = webdriver.ChromeOptions()
options.add_experimental_option('excludeSwitches', ['enable-automation'])
prefs = {'profile.managed_default_content_settings.images': 2}
options.add_experimental_option('prefs', prefs)
options.add_argument(
'--user-agent=Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36')
driver = webdriver.Chrome(executable_path=path,
chrome_options=options)
driver.implicitly_wait(40)
url = 'http://www.aastocks.com/sc/stocks/market/ipo/listedipo.aspx?s=3&o=0&page={}'
for p in range(1, page + 1):
driver.get(url.format(p))
time.sleep(5)
yield driver.page_source
def convert_float(self, data):
if data is None:
print('数据为空')
return None
data = data.strip().replace('%', '').replace(',', '')
try:
print('解析后')
print(data)
data = float(data)
except Exception as e:
if data != 'N/A':
print('解析异常')
print(data)
data = None
return data
def convert_date(self, data_str):
try:
date = datetime.datetime.strptime(data_str, '%Y/%m/%d')
except Exception as e:
print(e)
date = None
return date
def convert_hand_int(self, data):
try:
data = int(data.strip().replace('手', ''))
except:
data = None
return data
def parse(self, content):
response = Selector(text=content)
ipo_list = response.xpath('//div[@id="IPOListed"]/table/tbody/tr')
insert_sql = '''insert into `tb_hk_new_stock` (`name`,`code`,`issue_date`,`each_hand_stock`,`share_value_Yi`,`margin_price`,`price`,`over_price_part`,`hit_least_num`,`hit_ratio`,`current_price`,`first_day_raise`,`accumulate_raise`,`crawltime`)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE `crawltime`=%s'''
for ipo_item in ipo_list:
item_list = ipo_item.xpath('.//td')
if len(item_list) < 2:
continue
name = item_list[1].xpath('.//a[1]/text()').extract_first()
code = item_list[1].xpath('.//a[2]/text()').extract_first()
issue_date = self.convert_date(item_list[2].xpath('.//text()').extract_first())
each_hand_stock = item_list[3].xpath('.//text()').extract_first()
share_value_Yi = item_list[4].xpath('.//text()').extract_first()
margin_price = item_list[5].xpath('.//text()').extract_first()
price = self.convert_float(item_list[6].xpath('.//text()').extract_first())
over_price_part = item_list[7].xpath('.//text()').extract_first()
hit_least_num = self.convert_hand_int(item_list[8].xpath('.//text()').extract_first())
hit_ratio = self.convert_float(item_list[9].xpath('.//text()').extract_first())
current_price = self.convert_float(item_list[10].xpath('.//text()').extract_first())
first_day_raise = self.convert_float(item_list[11].xpath('.//text()').extract_first())
accumulate_raise = self.convert_float(item_list[12].xpath('.//text()').extract_first())
now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
if margin_price == 'N/A':
# 上市失败的
continue
try:
self.cursor.execute(insert_sql, (
name, code, issue_date, each_hand_stock, share_value_Yi, margin_price, price, over_price_part,
hit_least_num, hit_ratio, current_price, first_day_raise, accumulate_raise, now, now))
except Exception as e:
print(e)
self.conn.rollback()
else:
self.conn.commit()
def run(self):
total_page = 25
self.create_table()
gen = self.fetch(total_page)
page = 0
for content in gen:
print('page ', page)
self.parse(content)
page += 1
self.conn.close()
def clear_data(self):
'select code from tb_hk_new_stock group by code having count(*) as n >1'
pass
class FilterStock():
def __init__(self):
self.change_work_dir()
self.today = datetime.datetime.now().strftime("%Y-%m-%d")
self.engine = DBSelector().get_engine('db_stock')
self.conn = DBSelector().get_engine('db_stock')
def change_work_dir(self):
current = os.path.dirname(__file__)
work_space = os.path.join(current, 'data')
if os.path.exists(work_space) == False:
os.mkdir(work_space)
os.chdir(work_space)
def get_location(self):
df = ts.get_area_classified()
print(df)
# df.to_excel('location.xls')
self.save_to_excel(df, 'location.xls')
def get_ST(self):
# 暂停上市
zt = ts.get_suspended()
print(zt)
# 终止上市
zz = ts.get_terminated()
print(zz)
def get_achievement(self):
fc = ts.forecast_data(2016, 4)
print(fc)
def daily_market(self):
'''
保存每天收盘后的市场行情
:return:
'''
df = ts.get_today_all()
print(df)
try:
df.to_sql(self.today, self.engine, if_exists='replace')
except Exception as e:
print(e)
print("Save {} data to MySQL".format(self.today))
def break_low(self, date):
'''
筛选出一年内创新低的股票
:param date: 某一天的日期 ‘'2017-11-11
:return:
'''
# cmd = 'select * from `{}`'.format(date)
df = pd.read_sql_table(date, self.engine, index_col='index')
# **** 这里的index需要删除一个
low_db = self.conn('db_selection')
low_cursor = low_db.cursor()
for i in range(len(df)):
code = df.loc[i]['code']
cur_low = df.loc[i]['low']
mins_date, mins = self.get_lowest(code, '2017', date)
if not mins_date:
continue
if mins and float(cur_low) <= float(mins) and float(
cur_low) != 0.0:
print(code, )
print(df.loc[i]['name'])
print('year mins {} at {}'.format(mins, mins_date))
print('curent mins ', cur_low)
create_cmd = 'create table if not exists break_low' \
'(`index` int primary key auto_increment,datetime datetime,code text,name text,low_price float,last_price float, last_price_date datetime);'
low_cursor.execute(create_cmd)
insert_cmd = 'insert into break_low (datetime,code,name,low_price,last_price,last_price_date) values (%s,%s,%s,%s,%s,%s);'
insert_data = (date, code, df.loc[i]['name'], cur_low, mins,
mins_date)
low_cursor.execute(insert_cmd, insert_data)
low_db.commit()
def get_lowest(self, code, date, current_date):
'''
返回个股某一年最低价
:param code: 股票代码
:param date: 年份
:return:
'''
date = date + '-01-01'
cmd = 'select * from `{}` where datetime > \'{}\' and datetime <\'{}\''.format(
code, date, current_date)
try:
df = pd.read_sql(cmd, self.engine, index_col='index')
except Exception as e:
print(e)
return None, None
# print(df.dtypes)
# 不知道为啥，这里的类型发生改变
if len(df) < 1:
return None, None
df['low'] = df['low'].astype('float64')
idx = df['low'].idxmin()
min_date = df.loc[idx]
return min_date['datetime'], min_date['low']
def get_highest(self, code, date):
'''
返回个股某一年最高价
:param code: 股票代码
:param date: 年份
:return:
'''
date = date + '-01-01'
cmd = 'select high from `{}` where datetime > \'{}\''.format(
code, date)
df = pd.read_sql(cmd, self.engine)
return df['high'].max()
def save_to_excel(self, df, filename, encoding='gbk'):
try:
df.to_csv('temp.csv', encoding=encoding, index=False)
df = pd.read_csv('temp.csv',
encoding=encoding,
dtype={'code': str})
df.to_excel(filename, encoding=encoding)
return True
except Exception as e:
print("Save to excel faile")
print(e)
return None
# 专门用来存储数据，数据保存为excel，不必每次都要从网络读取
def store_data(self):
# 预测
# year_2016=ts.forecast_data(2016, 4)
# self.save_to_excel(year_2016,'2016-profit.xls')
# year_2017=ts.forecast_data(2017, 4)
# self.save_to_excel(year_2017,'2017-profit.xls')
# 盈利能力
# profit_2016=ts.get_profit_data(2016,4)
# profit_2017=ts.get_profit_data(2017,3)
# self.save_to_excel(profit_2016, '2016-profit.xls')
# self.save_to_excel(profit_2017, '2017-3rdprofit.xls')
# 股票基本信息
# basic=ts.get_stock_basics()
# basic.to_csv('temp.xls',encoding='gbk')
# df=pd.read_csv('temp.xls',encoding='gbk',dtype={'code':str})
# # print(df)
# self.save_to_excel(df,'Markets.xls')
# 基本面 每股净资产<1
df = ts.get_report_data(2017, 3)
self.save_to_excel(df, '2017-3rd-report.xls')
def to_be_ST(self):
'''
df_2016=pd.read_excel('2016-profit.xls',dtype={'code':str})
df_2017=pd.read_excel('2017-3rdprofit.xls',dtype={'code':str})
loss_2016=set(df_2016[df_2016['net_profits']<0]['code'])
loss_2017=set(df_2017[df_2017['net_profits']<0]['code'])
st= list(loss_2016 & loss_2017)
basic=pd.read_excel('Markets.xls',dtype={'code':str})
# print(basic.head(5))
# for x in st:
# print(x)
# print(basic[basic['code']==st])
for i in st:
print(basic[basic['code']==i][['code','name']])
'''
# 每股净资产小于0
df_bpvs = pd.read_excel('2017-3rd-report.xls', dtype={'code': str})
# print(df_bpvs.head())
print(df_bpvs[df_bpvs['bvps'] < 0][['code', 'name']])
# 返回新股信息
def get_new_stock(self, start='2010', end='2011'):
'''
:param start: 开始年份 如 '2010'
:param end: 结束年份 如 '2011'
:return:
'''
df = pd.read_sql('tb_basic_info', self.engine, index_col='index')
df = df[df['list_date'] != 0]
df['list_date'] = pd.to_datetime(df['list_date'], format='%Y%m%d')
df = df.set_index('list_date', drop=True)
new_stock = df[start:end] # 返回df格式
return new_stock
def plot_new_stock_distibution(self, df, start, end):
years = OrderedDict()
values = []
for year in range(start, end):
years[year] = len(df[str(year)])
values.append(len(df[str(year)]))
x = np.arange(1994, 2019)
plt.figure(figsize=(10, 9))
rect = plt.bar(x, values)
self.rect_show(rect)
plt.xticks(x[::2])
plt.show()
def rect_show(self, rects):
for rect in rects:
height = rect.get_height()
plt.text(rect.get_x(), 1.05 * height, '%s' % int(height))
# 只是用于测试，展示数据
def show(self):
df = self.get_new_stock()
# print(df)
# 返回黑名单的代码
def get_blacklist(self):
# conn=self.conn('db_stock','local')
cursor = self.conn.cursor()
query = 'select CODE from tb_blacklist'
cursor.execute(query)
ret = cursor.fetchall()
return [i[0] for i in ret]
class ShareHolderInfo():
'''
十大股东与十大流通股东
'''
def __init__(self):
self.init_mongo()
self.tushare_init()
def db_init(self):
self.conn = DBSelector().get_mysql_conn('db_stock')
self.cursor = self.conn.cursor()
def init_mongo(self):
self.client = DBSelector().mongo('qq')
self.doc_holder = self.client['db_stock']['shareHolder'] # 十大
self.doc_holder_float = self.client['db_stock'][
'shareHolder_float'] # 十大
def tushare_init(self):
self.pro = pro
def exists(self, code):
result = self.doc_holder.find_one({'ts_code': code})
return False if result is None else True
def get_stock_list(self, exchange):
df = self.pro.stock_basic(exchange=exchange, list_status='L')
return dict(zip(list(df['ts_code'].values), list(df['name'].values)))
# 生产日期 2000到2018
@staticmethod
def create_date():
start_date = '20{}0101'
end_date = '20{}1231'
date_list = []
for i in range(18, 0, -1):
print(start_date.format(str(i).zfill(2)))
print(end_date.format(str(i).zfill(2)))
date_list.append(i)
return date_list
# 十大和十大流通
def get_stockholder(self, code, start, end):
'''
stockholder 十大
stockfloat 十大流通
'''
try:
stockholder = self.pro.top10_holders(ts_code=code,
start_date=start,
end_date=end)
# time.sleep(1)
stockfloat = self.pro.top10_floatholders(ts_code=code,
start_date=start,
end_date=end)
# time.sleep(1)
except Exception as e:
print(e)
time.sleep(10)
# ts.set_token(config['ts_token'])
self.pro = pro
stockholder = self.pro.top10_holders(ts_code=code,
start_date=start,
end_date=end)
# time.sleep(1)
stockfloat = self.pro.top10_floatholders(ts_code=code,
start_date=start,
end_date=end)
# time.sleep(1)
else:
if stockholder.empty or stockfloat.empty:
print('有空数据----> ', code)
return pd.DataFrame(), pd.DataFrame()
else:
return stockholder, stockfloat
def dumpMongo(self, doc, df):
record_list = df.to_json(orient='records', force_ascii=False)
record_list = json.loads(record_list)
if len(record_list) == 0:
return
try:
doc.insert_many(record_list)
except Exception as e:
exc_type, exc_value, exc_obj = sys.exc_info()
traceback.print_exc()
def valid_code(self, code):
return True if re.search('^\d{6}\.\S{2}', code) else False
def run(self):
start_date = '20{}0101'
end_date = '20{}1231'
exchange_list = ['SSE', 'SZSE']
for ex in exchange_list:
code_dict = self.get_stock_list(ex)
for code, name in code_dict.items():
# for i in range(20, 0, -1):
i = 21
if not self.valid_code(code):
print('invalid code ', code)
continue
if self.exists(code):
continue
print('crawling -->', code)
start = start_date.format(str(i).zfill(2))
end = end_date.format(str(i).zfill(2))
df_holding, df_float = self.get_stockholder(code, start, end)
self.dumpMongo(self.doc_holder, df_holding)
self.dumpMongo(self.doc_holder_float, df_float)
time.sleep(0.1)