def setUp(self):
qa.QA_util_log_info('Listing.importStockListing')
listing = Listing.importStockListing()
# Listing.importIndexListing()
assert Listing.getlist('stock').count() > 2000
qa.QA_util_log_info('Block.initBlock')
Block.initBlock()
def test_QA_fetch_indexlisting(self):
# 从网上获取指数/基金日线
qa.QA_util_log_info('从网上获取指数/基金日线')
df = qa.QAFetch.QATdx.QA_fetch_get_stock_list('index')
querysetlist = []
for i in df.index[:10]:
a = df.loc[i]
# 本地获取指数日线数据
data = qa.QA_fetch_index_day_adv(a.code, '1990-01-01', str(datetime.date.today()))
"""
从本地数据库获取数据
data = qa.QA_fetch_index_day_adv(a.code, '1990-01-01', datetime.now().strftime("%Y-%m-%d"))
从网络获取数据
data = qa.QAFetch.QATdx.QA_fetch_get_index_day(code, '2017-01-01', '2017-09-01')
"""
d = data.data.date[0].strftime("%Y-%m-%d")
if a.sse == 'sh':
market = 1
else:
market = 0
category = 11
querysetlist.append(
Listing(code=a.code, name=a['name'], timeToMarket=d, volunit=a.volunit, decimalpoint=a.decimal_point,
category=category, market=market))
Listing.objects.bulk_create(querysetlist)
self.assertTrue(Listing.getlist('index').count() > 0, '未插入成功:{}'.format(querysetlist))
def test_loop_QA_fetch_stock_day_adv(self):
# 导入股票列表
# from .test_listing import TestListing
# tsc = TestListing()
Listing.importStockListing()
listings = Listing.objects.all()
df = pd.DataFrame()
# codelist = []
# for a in listings[:100]:
# codelist.append(a.code)
for a in listings[:100]:
code = a.code
try:
data = qa.QA_fetch_stock_day_adv(code, '2015-12-01', '2017-10-01').to_qfq() # [可选to_qfq(),to_hfq()]
print(data.code)
dftmp = pd.DataFrame()
# 120日rps
# b = pd.DataFrame(data.data.close / data.data.close.shift(120))
dftmp['rps120'] = pd.DataFrame(data.data.close / data.data.close.shift(120)).close
# 250日rps
b = pd.DataFrame(data.data.close / data.data.close.shift(250))
dftmp['rps250'] = b.close
df = df.append(dftmp.reset_index())
except Exception as e:
# print(e.args)
qa.QA_util_log_info('{} {}'.format(code, e.args))
df.set_index('date', inplace=True)
day = '2017-9-29'
# dfd = caculateRPS(df, day, [121, 251])
dfd = self.caculateRPS(df, day, [120, 250])
self.assertTrue(dfd[dfd['code'] == '000001'] is not None, 'result must not None!')
def test_saveStockDay(self):
a, _ = MARKET_CHOICES[0]
code = 'tt0001'
up_date = datetime.datetime.now()
sc = Listing(code=code,
name='Test0001',
timeToMarket=up_date,
market=a)
sc.save()
sdlist = StockDay.objects.all().count(
) # 此处不能为StockDay.objects.all()，否则由于lazy算法，并不会马上获取数据，导致更新数据后才查询数据
code = Listing.objects.get(code=code)
sd = StockDay(code=code,
open=1,
close=2,
high=3,
low=0,
volumn=100000,
amount=230000,
tradedate=datetime.datetime.now())
sd.save()
print(sdlist, code, sd)
self.assertTrue(
StockDay.objects.all().count() > sdlist,
'未保存成功, {} > {}：{}'.format(StockDay.objects.all().count(), sdlist,
sd.__dict__))
def test_getCodelist(self):
oldcounts = Listing.getCodelist().count()
self.assertTrue(Listing.getCodelist('all').count() >= oldcounts,
'所有类型代码数量比股票代码数量多, after :{} before : {}; '.format(Listing.objects.all().count(), oldcounts))
counts = 0
for category, b in STOCK_CATEGORY:
counts += Listing.objects.all().filter(category=category).count()
count = Listing.getCodelist('all').count()
self.assertTrue(counts == count, '明细汇总应该和总体数量一样：{} - {}'.format(counts, count))
def test_Stockcode(self):
a, b = MARKET_CHOICES[0]
up_date = timezone.now()
sc = Listing(code='tt0001', name='Test0001', timeToMarket=up_date, market=a)
sc.save()
# i = Listing.objects.all().count()
i = Listing.getCodelist().count()
self.assertTrue(i > 0, 'Listing count:{}'.format(i))
# object to json
print('sc.__dict__ : {}'.format(sc.__dict__))
print('json sc.__dict__ : {}'.format(json.dumps(sc, default=str)))
def test_importStocklisting(self):
"""
插入所有的A股股票代码
:return:
"""
oldcounts = Listing.getCodelist().count()
df = Listing.importStockListing()
print('before:{} after: {}'.format(oldcounts, Listing.getCodelist().count()))
self.assertTrue(Listing.getCodelist().count() == oldcounts + len(df),
'插入未成功, after :{} before : {}; 应插入：{}条记录'.format(Listing.objects.all().count(), oldcounts,
len(df)))
# 再次导入，不会报错
df = Listing.importStockListing()
def test_importStockList(self):
Listing.importStockListing()
filename = ''
RPSprepare.loadModelFromFile()
RPSprepare.importStockListing(
RPSprepare.getNearestTradedate('2016-6-1'))
qscounts = RPSprepare.getlist('stock').filter(
tradedate='2018-01-04').count()
self.assertTrue(qscounts > 2000,
'当天股票数量大于2000, 实际：{}'.format(qscounts))
RPS.importStockListing()
qscounts = RPS.getlist('stock').filter(tradedate='2018-01-04').count()
self.assertTrue(qscounts > 2000,
'当天股票数量大于2000, 实际：{}'.format(qscounts))
def populate_trades(stock):
try:
#print(stock.security)
fd = open('bsedata/{name}.csv'.format(name=stock.security), 'r')
reader = csv.DictReader(fd,delimiter=",", quotechar="'")
#print(stock)
listings = []
for row in reader:
listing = Listing(stock=stock,
date=datetime.strptime(row.get('Date').strip(), '%d-%B-%Y'),
opening=float(row.get('Open Price').strip()),
high = float(row.get('High Price').strip()),
low = float(row.get('Low Price').strip()),
closing = float(row.get('Close Price').strip()),
wap = float(row.get('WAP').strip()),
traded = int(row.get('No.of Shares').strip()),
trades = int(row.get('No. of Trades').strip()),
turnover = float(row.get('Total Turnover (Rs.)').strip()),
deliverable = float(row.get('Deliverable Quantity').strip()) if len(row.get('Deliverable Quantity').strip()) > 0 else 0,
)
listings += [listing]
try:
Listing.objects.bulk_create(listings)
except Exception as e:
print(e)
print(("Unexpected error:", sys.exc_info()[0]))
except IOError:
pass
def firstHigh(code,
start=None,
end=datetime.datetime.now().date(),
n=120,
m=250):
""" 返回第一次新高
:param code:
:param start:
:param end:
:param n:
:param m:
:return:
"""
def cmpfuncPeriod(df, days):
# 标记创半年新高
return pd.DataFrame(df.high == df.high.rolling(days).max())
# 半年新高
tdate = Listing.getNearestTradedate(days=-(n + m))
data = qa.QA_fetch_stock_day_adv(code, start=tdate,
end=end).to_qfq()
ind = data.add_func(lambda x: cmpfuncPeriod(x, m))
results = ind[ind['high']]
df = data[ind.high].data.high.reset_index()
# gg = df.groupby('code').date.first() # 更快速？
usedcode = []
fh = []
for v in [df.iloc[a] for a in df.index]:
if not (v.code in usedcode):
# 创新高的股票代码
usedcode.append(v.code)
fh.append([v.date, v.code])
return pd.DataFrame(fh, columns=['date', 'code'])
def test_importIndexListing(self):
oldcount = RPSprepare.getCodelist('index').count()
# 测试时插入指数基础数据
qs = Listing.importIndexListing()
RPSprepare.importIndexListing()
count = RPSprepare.getCodelist('index').count()
self.assertTrue(count - oldcount > 500,
'2018-05 指数数量应大于500， {}'.format(count - oldcount))
print(RPSprepare.getCodelist('index')[0])
def importTDXList(cls):
""" 导入通达信版块
导入通达信版块及版块明细
:return: 通达信版块第一层
"""
bkname = '通达信'
bk = Block.objects.all().filter(name=bkname, parentblock=None)
if len(bk) == 1:
# 数据库中有通达信版块
# data = qa.QAFetch.QATdx.QA_fetch_get_stock_block()
data = qa.QA_fetch_stock_block_adv().data
datatypeset = set(data.type)
with transaction.atomic():
for v in datatypeset:
# 保存版块类型
cls(code=v, name=v, parentblock=bk[0]).save()
querysetlist = []
try:
for v in datatypeset:
# 保存版块名称
bl = cls.getlist().filter(name=v)
if len(bl) == 1:
# 第一级版块
blockdf = data[data.type == v].blockname
blockset = set(blockdf)
with transaction.atomic():
for m in blockset:
block, _ = cls.objects.get_or_create(
code=m, name=m, parentblock=bl[0])
bdetail = blockdf.reset_index()[
blockdf.reset_index().blockname == m].code
for d in bdetail:
# 版块明细
code = Listing.getlist('stock').filter(
code=d)
if len(code) == 0:
# 不属于股票，则跳过
continue
querysetlist.append(
BlockDetail(code=code[0],
blockname=block))
print('版块明细 {}：\n{}'.format(len(querysetlist),
querysetlist))
# bulk_create放在循环里面会报错
BlockDetail.objects.bulk_create(querysetlist)
except Exception as e:
print('{}\n{} 版块： {} 版块类型： {}'.format(e.args, code, d, bl[0]))
raise Exception
else:
# 初始化版块
cls.initBlock()
return cls.importTDXList()
return cls.getlist(bk[0])
def get_queryset(self):
queryset = []
queryset = RPS.getlist()
self.code = self.request.GET.get('code', '')
if self.code:
try:
queryset = RPS.getlist('stock').filter(
code=Listing.getlist('stock').get(code=self.code))
except:
queryset = RPS.getlist('stock').filter(code=None)
return queryset.order_by('-tradedate', '-rps250', '-rps120')
def test_IndexDuplocate(self):
Listing.importIndexListing()
indexlist = Listing.getlist('index').filter(isactived=True)
df = pd.DataFrame(list(indexlist.values('code', 'name')))
dup = df[df.duplicated('name')]
for v in dup.name:
x = None
for d in df[df.name == v].index:
if not x:
x = qa.QA_fetch_index_day_adv(df.iloc[d].code, '2018-1-1',
'2018-3-1')
else:
y = qa.QA_fetch_index_day_adv(df.iloc[d].code, '2018-1-1',
'2018-3-1')
if (x.data.close[-1] == y.data.close[-1]):
print('重复的index: {} {}'.format(
x.data.index[0][1], df.iloc[d].code)) # 重复的index
Listing.objects.all().filter(code=df.iloc[d].code)
# 更新重复代码isactived=False
else:
print('未重复的index: {}'.format(
df.iloc[d].code)) # 重复的index
def populate_trades(stock):
try:
#print(stock.security)
fd = open('bsedata/{name}.csv'.format(name=stock.security), 'r')
reader = csv.DictReader(fd,delimiter=",", quotechar="'")
#print(stock)
listings = []
for row in reader:
#print(stock)
#print(row)
try:
listing = Listing.objects.get(stock=stock, date=datetime.strptime(row.get('Date'), '%d-%B-%Y'))
except Listing.DoesNotExist:
listing = Listing(stock=stock,
date=datetime.strptime(row.get('Date').strip(), '%d-%B-%Y'),
opening=float(row.get('Open Price').strip()),
high = float(row.get('High Price').strip()),
low = float(row.get('Low Price').strip()),
closing = float(row.get('Close Price').strip()),
wap = float(row.get('WAP').strip()),
traded = int(row.get('No.of Shares').strip()),
trades = int(row.get('No. of Trades').strip()),
turnover = float(row.get('Total Turnover (Rs.)').strip()),
deliverable = float(row.get('Deliverable Quantity').strip()) if len(row.get('Deliverable Quantity').strip()) > 0 else 0,
ratio = float(row.get('% Deli. Qty to Traded Qty').strip()) if len(row.get('% Deli. Qty to Traded Qty').strip()) > 0 else 0,
spread_high_low = float(row.get('Spread High-Low').strip()),
spread_close_open = float(row.get('Spread Close-Open').strip()))
try:
#listing.save()
#print('Adding to list')
listings.append(listing)
except:
print(stock)
print(row)
print(listing)
for e in sys.exc_info():
print(("Unexpected error:", e))
except:
print('Something went wrong')
print(stock)
print(row)
print(("Unexpected error:", sys.exc_info()[0]))
try:
Listing.objects.bulk_create(listings)
except:
print(("Unexpected error:", sys.exc_info()[0]))
except IOError:
pass
def test_getCategory(self):
c = Listing.getCategory('stock')
self.assertTrue(c == 10, '类型不对：{}'.format(c))
c = Listing.getCategory('index')
self.assertTrue(c == 11, '类型不对：{}'.format(c))
c = Listing.getCategory('etf')
self.assertTrue(c == 12, '类型不对：{}'.format(c))
c = Listing.getCategory('ZQ')
self.assertTrue(c == 13, '类型不对：{}'.format(c))
c = Listing.getCategory('NHG')
self.assertTrue(c == 14, '类型不对：{}'.format(c))
c = Listing.getCategory(10)
self.assertTrue(c == 10, '类型不对：{}'.format(c))
c = Listing.getCategory(11)
self.assertTrue(c == 11, '类型不对：{}'.format(c))
def get_queryset(self):
queryset = RPS.getlist('stock')
self.form = RPSForm(self.request.GET)
# print(self.form)
if self.form.is_valid():
self.code = self.form.cleaned_data['code']
self.rps120 = self.form.cleaned_data['rps120']
self.rps250 = self.form.cleaned_data['rps250']
self.column_num = self.form.cleaned_data['column_num']
# todo form choice 赋值
# self.days = int(form.cleaned_data['days'])
try:
self.page = int(self.form.cleaned_data['page'])
except:
print('exception page:{}'.format(self.page))
print('self.form.cleaned_data:{}'.format(
self.form.cleaned_data['page']))
# self.page = 1
pass
print(self.form.cleaned_data)
if self.code:
try:
queryset = queryset.filter(
code=Listing.getlist('stock').get(code=self.code))
except:
pass
if self.rps120:
try:
queryset = queryset.filter(rps120__gte=self.rps120)
except:
pass
if self.rps250:
try:
queryset = queryset.filter(rps250__gte=self.rps250)
except:
pass
if self.days > 0:
try:
tdate = RPS.getNearestTradedate(days=-self.days)
queryset = queryset.filter(tradedate__gte=tdate)
except:
pass
return queryset.order_by('-tradedate', '-rps250', '-rps120')
def get_queryset(self):
queryset = Block.getlist()
self.form = BlockForm(self.request.GET)
# print(self.form)
if self.form.is_valid():
self.code = self.form.cleaned_data['code']
self.name = self.form.cleaned_data['name']
print(self.form.cleaned_data)
if self.code:
try:
queryset = queryset.filter(
code=Listing.getlist('stock').get(code=self.code))
except:
pass
if self.name:
try:
queryset = queryset.filter(name__icontains=self.name)
except:
pass
return queryset.order_by('code', 'name')
def importIndexListing(cls):
"""
插入所有股票指数
:return:
"""
codelist = Listing.getlist('index')
# todo 如果已经插入，则判断是否有更新
try:
# 批量创建对象，减少SQL查询次数
querysetlist = []
delisted = [] # quantaxis中无数据list
for v in codelist.values():
print('dealing: {}'.format(v))
# get stockcode
code = Listing.objects.get(code=v['code'], category=11)
# 本地获取指数日线数据
data = qa.QA_fetch_index_day_adv(v['code'], '1990-01-01', datetime.datetime.now().strftime("%Y-%m-%d"))
if len(data) > 120:
df = pd.DataFrame(data.close)
df['rps120'] = df.close / df.close.shift(120)
df['rps250'] = df.close / df.close.shift(250)
del df['close']
df = df[120:]
for d, _, a, b in df.reset_index().values:
# 下面两行代码，合并写在一行，会造成tradedate错误
r = RPSprepare(code=code, rps120=a, rps250=b if b > 0 else None,
tradedate=d.to_pydatetime())
querysetlist.append(r)
else:
# quantaxis中无数据
delisted.append(a)
print('delisted count {} :\n {}'.format(len(delisted), delisted))
RPSprepare.objects.bulk_create(querysetlist)
except Exception as e:
print(e.args)
return cls.getlist('index')
def test_getCodelist(self):
# using quantaxis
oldcount = RPSprepare.getCodelist('index').count()
# 测试时插入指数基础数据
qs = Listing.importIndexListing()
d = (datetime.datetime.now() - datetime.timedelta(300)).date()
rps = RPSprepare(code=qs[0], rps120=1.1, rps250=1.2, tradedate=d)
rps.save()
count = RPSprepare.getCodelist('index').count()
self.assertTrue(count - oldcount > 0,
'指数数量应大于0， {}'.format(count - oldcount))
print('数据库中有{}记录。'.format(count))
qsrps = RPSprepare.getCodelist('index')
self.assertTrue(qsrps[0].tradedate == d,
'日期保存失败：{} {}'.format(qsrps[0].tradedate, d))
qsrps.delete()
# 测试tradedate保存
d = (datetime.datetime.now() - datetime.timedelta(300)).date()
querysetlist = []
n = 10
for i in range(n):
rps = RPSprepare(code=qs[1],
rps120=1 + i,
rps250=1.2,
tradedate=d + datetime.timedelta(i + 1))
querysetlist.append(rps)
RPSprepare.objects.bulk_create(querysetlist)
qsrps = RPSprepare.getCodelist('index')
for i in range(1, n, 1):
self.assertTrue(
qsrps[i].tradedate == d + datetime.timedelta(i + 1),
'数据库中{} ！= {}'.format(qsrps[i].tradedate,
d + datetime.timedelta(i + 1)))
self.assertTrue(qsrps[i].rps120 == 1 + i,
'数据库中{} ！= {}'.format(qsrps[i].rps120, i + 1))
def test_importIndexlisting(self):
oldcount = Listing.getlist('index').count()
Listing.importIndexListing()
count = Listing.getlist('index').count()
self.assertTrue(count - oldcount > 500,
'2018-05 指数数量应大于500， {}'.format(count - oldcount))
def setUpClass(cls):
super(TestRPS, cls).setUpClass()
cls.qslist = Listing.importIndexListing()
cls.listing = Listing.getlist('index')
def load_intraday_data_to_db(first_time=False):
filelist = load_file_list()
stock = None
for f in filelist:
print('Reading: {}'.format(f))
with open(f, 'r') as fd:
reader = csv.DictReader(fd,
fieldnames=[
'instrument', 'date', 'time', 'open',
'high', 'low', 'close'
],
delimiter=",",
quotechar="'")
listings = []
for row in reader:
try:
if stock is None:
stock = Stock.objects.get(name=row.get('instrument'))
if first_time:
listing = Listing(
stock=stock,
date=datetime.strptime(
row.get('date') + ' ' + row.get('time'),
'%Y%m%d %H:%M'),
opening=float(row.get('open').strip()),
high=float(row.get('high').strip()),
low=float(row.get('low').strip()),
closing=float(row.get('close').strip()),
wap=0,
traded=0,
trades=0,
turnover=0,
deliverable=0)
try:
listings.append(listing)
except:
print(row)
print(listing)
for e in sys.exc_info():
print(("Unexpected error:", e))
else:
listing = Listing.objects.get(
stock=stock,
date=datetime.strptime(
row.get('date') + ' ' + row.get('time'),
'%Y%m%d %H:%M'))
except Stock.DoesNotExist:
print(f'Entry for {row.get("instrument")} not found.')
break
except Listing.DoesNotExist:
listing = Listing(stock=stock,
date=datetime.strptime(
row.get('date') + ' ' +
row.get('time'), '%Y%m%d %H:%M'),
opening=float(row.get('open').strip()),
high=float(row.get('high').strip()),
low=float(row.get('low').strip()),
closing=float(row.get('close').strip()),
wap=0,
traded=0,
trades=0,
turnover=0,
deliverable=0)
try:
listings.append(listing)
except:
print(row)
print(listing)
for e in sys.exc_info():
print(("Unexpected error:", e))
except:
print(("Unexpected error:", sys.exc_info()[0]))
try:
Listing.objects.bulk_create(listings)
except:
print(("Unexpected error:", sys.exc_info()[0]))
def importStockListing(cls, start=None):
""" 插入所有股票RPS预备数据
:return:
"""
if start is None:
# 数据库中最大的已计算日期
latest = cls.getlist('stock').aggregate(
Max('tradedate'))['tradedate__max']
if latest:
start = cls.getNearestTradedate(latest, -5)
else:
start = '2014-1-1'
codelist = Listing.getlist('stock')
# todo 如果已经插入，则判断是否有更新
try:
# 批量创建对象，减少SQL查询次数
querysetlist = []
delisted = [] # quantaxis中无数据list
qssaved = []
tdate = cls.getNearestTradedate()
realStart120 = cls.getNearestTradedate(start, -120)
realStart = cls.getNearestTradedate(start, -250)
# with transaction.atomic():
# for v in codelist.values()[11:100]:
for v in codelist.values():
print('Dealing {} {} {}'.format(format(v['id'], '05d'),
v['code'], v['name']))
try:
# get stockcode
code = Listing.objects.get(code=v['code'], category=10)
# 本地获取指数日线数据
data = qa.QA_fetch_stock_day_adv(
v['code'], realStart,
datetime.datetime.now().strftime("%Y-%m-%d")).to_qfq()
if len(data) > 120:
df = pd.DataFrame(data.close)
df['rps120'] = round(df.close / df.close.shift(120), 3)
df['rps250'] = round(df.close / df.close.shift(250), 3)
del df['close']
if code.timeToMarket > realStart120:
# 上市日期较早
cutDay = 120
else:
cutDay = 250
df = df[cutDay:]
df.reset_index(inplace=True)
df.columns = ['tradedate', 'code', 'rps120', 'rps250']
del df['code']
df['tradedate'] = df['tradedate'].apply(
lambda x: convertToDate(str(x)[:10])).astype(
datetime.date)
df['code_id'] = code.id
df, dfsaved = cls.dfNotInModel(df, code.id,
df['tradedate'].min())
if len(df) > 0:
# print(df)
cls.savedf(df)
if len(dfsaved) > 0:
# 日期在原来保存区间的数据
qssaved.append(dfsaved)
except Exception as e:
delisted.append(v['code'])
print(len(delisted), e.args)
# print(df)
cls.updateSaved(qssaved)
print('保存过的数据更新数量 {} \n {}'.format(len(qssaved), qssaved))
print('delisted count {} :\n {}'.format(len(delisted), delisted))
# RPSprepare.objects.bulk_create(querysetlist)
except Exception as e:
print(e.args)
return cls.getlist('stock')
def importIndexListing(cls, start='2014-1-1'):
""" 插入所有股票指数
qa.QA_fetch_index_day_adv(v['code'], '1990-01-01', datetime.datetime.now().strftime("%Y-%m-%d"))
已知重复的指数代码：
000300 399300
000901 399901
000903 399903
000922 399922
000925 399925
000928 399928
000931 399931
000934 399934
000935 399935
000939 399939
000944 399944
000950 399950
000951 399951
000957 399957
000958 399958
000961 399961
000963 399963
000969 399969
000977 399977
000979 399979
:return:
"""
codelist = Listing.getlist('index')
# todo 如果已经插入，则判断是否有更新
try:
# 批量创建对象，减少SQL查询次数
querysetlist = []
delisted = [] # quantaxis中无数据list
with transaction.atomic():
for v in codelist.values():
print('dealing: {}'.format(v))
# get stockcode
code = Listing.objects.get(code=v['code'], category=11)
# 本地获取指数日线数据
data = qa.QA_fetch_index_day_adv(
v['code'], '1990-01-01',
datetime.datetime.now().strftime("%Y-%m-%d"))
if len(data) > 120:
df = pd.DataFrame(data.close)
df['rps120'] = df.close / df.close.shift(120)
df['rps250'] = df.close / df.close.shift(250)
del df['close']
df = df[120:]
for d, _, a, b in df.reset_index().values:
# 下面两行代码，合并写在一行，会造成tradedate错误
r = RPSprepare(code=code,
rps120=a,
rps250=b if b > 0 else None,
tradedate=d.to_pydatetime())
querysetlist.append(r)
else:
# quantaxis中无数据
delisted.append(a)
print('delisted count {} :\n {}'.format(len(delisted), delisted))
RPSprepare.objects.bulk_create(querysetlist)
except Exception as e:
print(e.args)
return cls.getlist('index')
def test_useraddfunc(self):
from django.db.models import Q
from stocks.models import Listing, RPS
def cmpfunc(df):
# 标记创新高
return pd.DataFrame(df.high == df.high.max())
def cmpfunc120(df):
# 标记创半年新高
return pd.DataFrame(df.high == df.high.rolling(120).max())
def cmpfuncPeriod(df, days):
# 标记创半年新高
return pd.DataFrame(df.high == df.high.rolling(days).max())
def higher(df):
return ind[ind['high']]
# tdate = HSGTCGHold.getNearestTradedate(days=-120)
tdate = '2018-1-2'
end = datetime.datetime.now().date()
code = qa.QA_fetch_stock_list_adv().code.tolist()
data = qa.QA_fetch_stock_day_adv(code, start=tdate, end=end).to_qfq()
ind = data.add_func(lambda x: x.tail(20).high.max() == x.high.max())
code = list(ind[ind].reset_index().code)
# 半年新高
code = pd.DataFrame(list(RPS.getlist().filter(tradedate__gte='2018-1-1').filter(
(Q(rps120__gte=90) & Q(rps250__gte=80)) | (Q(rps120__gte=80) & Q(rps250__gte=90))). \
values('code__code').distinct()))['code__code'].values.tolist()
n = 120
m = 120 # 新高范围
df = self.firstHigh(code, end, m, n)
for v in [df.iloc[a] for a in df.index]:
print(v.code, v.date)
try:
day_data = qa.QA_fetch_stock_day_adv(v.code, v.date, end).to_qfq()
ddf = day_data.data[['high', 'close', 'low']].reset_index()
except Exception as e:
print(e.args)
n = 120
m = 250
# tdate = Listing.getNearestTradedate(days=-(n * 2))
tdate = Listing.getNearestTradedate(days=-(n + m + 2))
data = qa.QA_fetch_stock_day_adv(code, start=tdate, end=end).to_qfq()
ind = data.add_func(cmpfunc120)
# ind1 = data.add_func(lambda x: cmpfuncPeriod(x, n))
ind1 = data.add_func(lambda x: cmpfuncPeriod(x, m))
results = ind[ind['high']]
assert ind[ind['high']].equals(ind1[ind1['high']])
df = data[ind.high].data.high.reset_index()
usedcode = []
for v in [df.iloc[a] for a in df.index]:
if not (v.code in usedcode):
# 创新高的股票代码
usedcode.append([ v.date, v.code,v.high])
data = qa.QA_fetch_stock_day_adv(code, start=tdate, end=end).to_qfq()
# 收盘在50日线之上
ind = data.add_func(lambda x: float(qa.QA_indicator_MA(x.tail(50), 50)[-1:].MA) <= float(x.tail(1).close))
code = list(ind[ind].reset_index().code)
# 前100日内有连续两天wr==0
day_data = qa.QA_fetch_stock_day_adv(code, '2018-01-03', '2018-07-04')
ind = day_data.add_func(qa.QA_indicator_WR, 10, 6)
res = ind.groupby(level=1, as_index=False, sort=False, group_keys=False).apply(
lambda x: (x.tail(100))['WR1'].rolling(2).sum() == 0)
print(res[res])
def get_data_for_date(stock, dateObj):
from bs4 import BeautifulSoup
import urllib
try:
listing = Listing.objects.filter(date__contains = dateObj.date(), stock=stock)
print('{da} exists'.format(da = stock.security))
if len(listing) == 0:
#open and read from URL
try:
response = urllib.request.urlopen(stock.get_quote_url())
html_page = response.read()
soup = BeautifulSoup(html_page, "html.parser")
company_name = soup.find('span', id='ctl00_ContentPlaceHolder1_lblCompanyValue').find('a').string
stockdata = soup.find('span', id='ctl00_ContentPlaceHolder1_spnStkData')
table_vals = stockdata.find('table').findAll('tr')
if datetime.today().date() == dateObj.date():
row = table_vals[2]
tds = row('td')
if not (tds[0].string == dateObj.strftime(u'%-d/%m/%y')):
print ("{code} No data available for today".format(code=stock.security))
return False
elif datetime.today().date() > dateObj.date():
#print ("Look for old date {date}".format(date = day.strftime(u'%-d/%m/%y')))
for row in table_vals:
tds = row('td')
if(tds[0].string == dateObj.strftime(u'%-d/%m/%y')):
break
else:
tds = None
else:
raise Exception('Cannot fetch data for future')
if tds is not None:
listing = Listing()
listing.stock = stock
listing.date = datetime.strptime(tds[0].string, "%d/%m/%y")
listing.opening = float(tds[1].string.replace(',', ''))
listing.high = float(tds[2].string.replace(',', ''))
listing.low = float(tds[3].string.replace(',', ''))
listing.closing = float(tds[4].string.replace(',', ''))
listing.wap = float(tds[5].string.replace(',', ''))
listing.traded = float(tds[6].string.replace(',', ''))
listing.trades = float(tds[7].string.replace(',', ''))
listing.turnover = float(tds[8].string.replace(',', ''))
listing.deliverable = float(tds[9].string.replace(',', ''))
listing.ratio = float(tds[10].string.replace(',', ''))
listing.spread_high_low = float(tds[11].string.replace(',', ''))
listing.spread_close_open = float(tds[12].string.replace(',', ''))
listing.save()
print ("{code} OK[{d}] [{id}]".format(code=stock.security, d=listing.date, id=listing.id))
return True
else:
print ("{code} No data available for the day".format(code=stock.security))
return False
except:
print ("{code} Failed".format(code=stock.security))
le = len(sys.exc_info())
print(le)
for i in range(0,le):
print("Unexpected error:", sys.exc_info()[i])
return False
except:
print ("{code} Failed".format(code=stock.security))
le = len(sys.exc_info())
print(le)
for i in range(0,le):
print("Unexpected error:", sys.exc_info()[i])
return False
def get_data_for_date(stock, dateObj):
def fix_float(value):
if value.string is not None and len(value.string) > 0:
return float(value.string.replace(',', ''))
else:
return 0
from bs4 import BeautifulSoup
import urllib.request, urllib.parse, urllib.error
time.sleep(0.5)
try:
listing = Listing.objects.filter(date__contains=dateObj.date(),
stock=stock)
#print(('{da} exists'.format(da = stock.security)))
if len(listing) == 0:
#open and read from URL
try:
header = {
'authority':
'api.bseindia.com',
'method':
'GET',
'scheme':
'https',
'accept':
'application/json, text/plain, */*',
'accept-encoding':
'gzip, deflate, br',
'accept-language':
'en-IN,en;q=0.9,en-GB;q=0.8,en-US;q=0.7,hi;q=0.6',
'dnt':
'1',
'origin':
'https://www.bseindia.com',
'referer':
stock.get_quote_url(),
'user-agent':
'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.135 Safari/537.36',
}
req = urllib.request.Request(stock.get_quote_url(),
headers=header)
response = urllib.request.urlopen(req)
coder = response.headers.get('Content-Encoding', 'utf-8')
#print('Coder: {}'.format(coder))
if coder == 'br':
html_page = brotli.decompress(
response.read()).decode('utf-8')
elif coder == 'gzip':
buf = BytesIO(response.read())
html_page = gzip.GzipFile(
fileobj=buf).read().decode('utf-8')
else:
html_page = response.read().decode('utf-8')
soup = BeautifulSoup(html_page, "html.parser")
if soup.find('span', id='ContentPlaceHolder1_lblCompanyValue') is None or \
soup.find('span', id='ContentPlaceHolder1_lblCompanyValue').find('a') is None:
print('Content object not found.')
return False
company_name = soup.find(
'span',
id='ContentPlaceHolder1_lblCompanyValue').find('a').string
stockdata = soup.find('div',
id='ContentPlaceHolder1_divStkData')
table = stockdata.find('table')
if table is not None:
table_vals = table.findAll('tr', {'class': 'TTRow'})
if datetime.today().date() >= dateObj.date():
for row in table_vals:
tds = row('td')
if (tds[0].string == dateObj.strftime('%-d/%m/%y')
):
break
else:
tds = None
else:
raise Exception('Cannot fetch data for future')
if tds is not None:
listing = Listing()
listing.stock = stock
listing.date = datetime.strptime(
tds[0].string, "%d/%m/%y")
listing.opening = fix_float(tds[1])
listing.high = fix_float(tds[2])
listing.low = fix_float(tds[3])
listing.closing = fix_float(tds[4])
listing.wap = fix_float(tds[5])
listing.traded = fix_float(tds[6])
listing.trades = fix_float(tds[7])
listing.turnover = fix_float(tds[8])
listing.deliverable = fix_float(tds[9])
#listing.ratio = fix_float(tds[10])
#listing.spread_high_low = fix_float(tds[11])
#listing.spread_close_open = fix_float(tds[12])
listing.save()
print(
("{code} OK[{d}] [{id}]".format(
code=stock.security,
d=listing.date,
id=listing.id)))
return True
else:
print(("{code} No data available for the day".format(
code=stock.security)))
return True
except Exception as e:
print(("{code} Failed".format(code=stock.security)))
print(e)
le = len(sys.exc_info())
#print(le)
for i in range(0, le):
print(("Unexpected error:", sys.exc_info()[i]))
return False
else:
print('Listing exists for the date')
except:
print(("{code} Failed".format(code=stock.security)))
le = len(sys.exc_info())
print(le)
for i in range(0, le):
print(("Unexpected error:", sys.exc_info()[i]))
return False
def importList(cls,
start=None,
end=datetime.datetime.now().date(),
n=120,
m=250):
""" 创一年新高后，最低点和最高价记录
:param start:
:param end:
:param n:
:param m:
:return:
"""
def firstHigh(code,
start=None,
end=datetime.datetime.now().date(),
n=120,
m=250):
""" 返回第一次新高
:param code:
:param start:
:param end:
:param n:
:param m:
:return:
"""
def cmpfuncPeriod(df, days):
# 标记创半年新高
return pd.DataFrame(df.high == df.high.rolling(days).max())
# 半年新高
tdate = Listing.getNearestTradedate(days=-(n + m))
data = qa.QA_fetch_stock_day_adv(code, start=tdate,
end=end).to_qfq()
ind = data.add_func(lambda x: cmpfuncPeriod(x, m))
results = ind[ind['high']]
df = data[ind.high].data.high.reset_index()
# gg = df.groupby('code').date.first() # 更快速？
usedcode = []
fh = []
for v in [df.iloc[a] for a in df.index]:
if not (v.code in usedcode):
# 创新高的股票代码
usedcode.append(v.code)
fh.append([v.date, v.code])
return pd.DataFrame(fh, columns=['date', 'code'])
if not start:
# 默认根据n，m周期计算获取数据开始日期
start = cls.getNearestTradedate(days=-(n))
# 查找大于start日期的RPS强度
code = pd.DataFrame(list(RPS.getlist().filter(tradedate__gte=start, tradedate__lte=end).filter(
(Q(rps120__gte=90) & Q(rps250__gte=80)) | (Q(rps120__gte=80) & Q(rps250__gte=90))). \
values('code__code').distinct()))['code__code'].values.tolist()
df = firstHigh(code, start, end, n, m)
for v in [df.iloc[a] for a in df.index]:
print(v.code, v.date)
try:
day_data = qa.QA_fetch_stock_day_adv(v.code, v.date,
end).to_qfq()
ddf = day_data.data[['high', 'close', 'low']].reset_index()
ddf['high'] = ddf['high'].apply(lambda x: round(x, 3))
ddf['close'] = ddf['close'].apply(lambda x: round(x, 3))
ddf['low'] = ddf['low'].apply(lambda x: round(x, 3))
ddf['date'] = ddf['date'].apply(lambda x: x.date())
c = Listing.getlist('stock').get(code=v['code'])
qs = cls.objects.all().filter(code=c).aggregate(
Max("ltradedate"))
if qs['ltradedate__max']:
ddf = ddf[ddf['date'].apply(
lambda x: x >= qs['ltradedate__max'])]
entries = ddf.to_dict('records')
cls.updateHigh(c, entries)
except Exception as e:
print(e.args)
def read_bhav_file(filename, dateval):
try:
with open(filename, 'r') as fd:
csv_reader = csv.DictReader(fd, delimiter=',')
i = 0
for row in csv_reader:
try:
stock = Stock.objects.get(security=row['SC_CODE'])
listing = Listing()
listing.stock = stock
listing.date = dateval
listing.opening = fix_float(row['OPEN'])
listing.high = fix_float(row['HIGH'])
listing.low = fix_float(row['LOW'])
listing.closing = fix_float(row['CLOSE'])
#listing.wap = fix_float(tds[5])
listing.traded = fix_float(row['NO_OF_SHRS'])
listing.trades = fix_float(row['NO_TRADES'])
listing.turnover = fix_float(row['NET_TURNOV'])
#listing.deliverable = fix_float(tds[9])
listing.save()
except Stock.DoesNotExist:
print('{} does not exist in DB'.format(row['SC_NAME']))
continue
except IOError as e:
print(("I/O error({0}): {1}".format(e.errno, e.strerror)))
pass
def setUpTestData(cls):
Stocktradedate.importList()
Listing.importIndexListing()
rpspreparelist = RPSprepare.importIndexListing()
