def _commit_stock_data(model: models.StockDailyQuote or models.CapitalFlow, sql: str):
"""同步股票行情或资金流向数据
Args:
model:
sql:
Returns:
"""
exist = model.objects.last()
if exist:
date = exist.date
else:
date = datetime.date(2020, 1, 1)
sql = render(sql, '<date>', date.strftime('%Y-%m-%d'))
data = read_oracle(sql)
stocks = models.Stock.objects.all()
stocks = {x.secucode: x for x in stocks}
data.secucode = data.secucode.apply(lambda x: stocks.get(x))
data = data[data.secucode.notnull()]
ret = [model(**x) for _, x in data.iterrows()]
ret = chunk(ret, 5000)
for r in ret:
model.objects.bulk_create(r)
def get_index_quote(secucode):
"""获取指数交易数据"""
last = models.IndexQuote.objects.filter(secucode=secucode).last()
if last:
date = last.date
else:
date = models.IndexBasicInfo.objects.get(secucode=secucode).basedate
if not date:
date = datetime.date(2005, 1, 1)
else:
date = date.date()
for tmpl in [
template.quote, template.quote_cb, template.quote_os,
template.quote_sw
]:
sql = render(tmpl, '<code>', secucode)
sql = render(sql, '<date>', date.strftime('%Y-%m-%d'))
data = read_oracle(sql)
if not data.empty:
data.columns = [x.lower() for x in data.columns]
data = data.dropna(how='any')
return data.to_dict(orient='records')
def get_index_basic_info():
"""获取指数基本信息，增量方式"""
basic = models.IndexBasicInfo.objects.values_list('secucode')
basic = [x[0] for x in basic]
indexes = get_indexes()
indexes = [x for x in indexes if '.' not in x]
added = [x for x in indexes if x not in basic]
added = "'" + "','".join(added) + "'"
sql = render(template.basic_info, '<codelist>', added)
data = read_oracle(sql)
data.basedate = data.basedate.apply(lambda x: x.strftime('%Y-%m-%d'))
data = data.to_dict(orient='records')
return data
def get_tradingdays(date=None):
"""
从gildata获取交易日数据
:param date: 日期，默认为空
:return:
"""
if not date:
date = datetime.date(1990, 1, 1)
date = date.strftime('%Y-%m-%d')
sql = render(tradingday.tradingday, '<date/>', date)
data = read_oracle(sql)
if data.empty:
return
return data
def commit_stock_expose():
"""同步因子暴露度数据"""
exist = models.StockExpose.objects.last()
if exist:
date = exist.date
else:
date = datetime.date(2020, 10, 1)
sql = render(template.stock_expose, '<date>', date.strftime('%Y%m%d'))
data = read_datayes(sql)
stocks = list(set(list(data.secucode)))
stocks = models.Stock.objects.filter(secucode__in=stocks).all()
stocks = {x.secucode: x for x in stocks}
data['secucode'] = data['secucode'].apply(lambda x: stocks.get(x))
data = data[data.secucode.notnull()]
data.date = data.date.apply(lambda x: parse(x).date())
need = []
for _, r in data.iterrows():
need.append(models.StockExpose(**r.to_dict()))
need = chunk(need, 1000)
for n in need:
models.StockExpose.objects.bulk_create(n)
def _commit_index_component(code_list: list):
"""同步指数成分列表
从聚源数据库获取股票规模指数的成分及权重，如中证800、沪深300
Args:
code_list (List[str]): 股票规模指数列表
Returns:
DataFrame: 从数据库查询到的相关数据
example:
>>> _commit_index_component(['000001', '000906'])
secucode stockcode weight date
0 000001 600000 0.717 2020-11-30
1 000001 600004 0.086 2020-11-30
2 000001 600006 0.031 2020-11-30
"""
code_list = "'" + "','".join(code_list) + "'"
sql = render(template.component, '<codelist>', code_list)
data = read_oracle(sql)
return data
def commit_announcement():
"""同步基金公告数据"""
exist = models.FundAnnouncement.objects.last()
if exist:
date = exist.date
else:
date = datetime.date.today() + datetime.timedelta(days=-30)
date = date.strftime('%Y-%m-%d')
sql = render(funds.fund_announcement, '<date>', date)
data = read_oracle(sql)
fund = models.Holding.objects.values('secucode').distinct()
fund = [x['secucode'] for x in fund]
fund = models.Funds.objects.filter(secucode__in=fund).all()
fund = {x.secucode: x for x in fund}
data.secucode = data.secucode.apply(lambda x: fund.get(x))
data = data[data.secucode.notnull()]
ret = (models.FundAnnouncement(**x) for _, x in data.iterrows())
for i in ret:
try:
i.save()
except Exception as e:
print(e.__class__)
def _commit_stock_quote():
"""同步股票行情 A股大盘、创业板、科创板"""
exist = models.StockDailyQuote.objects.last()
if exist:
date = exist.date
else:
date = datetime.date(2020, 1, 1)
dates = models.TradingDays.objects.filter(date__lt=date).order_by('-date')
date = dates[3].date
stocks = models.Stock.objects.all()
stocks = {x.secucode: x for x in stocks}
latest = models.StockDailyQuote.objects.values('secucode').annotate(mdate=Max('date'))
latest = {x['secucode']: x['mdate'] for x in latest}
stmts = [template.stock_quote, template.stock_quote_sti]
for sql in stmts:
sql = render(sql, '<date>', date.strftime('%Y-%m-%d'))
data = read_oracle(sql)
data = data[data.agg(lambda x: x.date.date() > latest.get(x.secucode, datetime.date(2021, 1, 1)), axis=1)]
data.secucode = data.secucode.apply(lambda x: stocks.get(x))
data = data[data.secucode.notnull()]
ret = [models.StockDailyQuote(**x) for _, x in data.iterrows()]
ret = chunk(ret, 5000)
for r in ret:
models.StockDailyQuote.objects.bulk_create(r)
def _commit_holding_stocks(publish: str, sql):
"""同步基金持仓数据
基金持仓来源于两张表，一为重仓股，源于季报，一为完整持仓，源于半年报或年报
"""
if models.FundHoldingStock.objects.exists():
date = datetime.date.today()
date = datetime.date(date.year - 1, 1, 1)
else:
date = datetime.date(1990, 1, 1)
exist = models.FundHoldingStock.objects.filter(publish=publish).values('secucode').annotate(max_date=Max('date'))
existed = {x['secucode']: x['max_date'] for x in exist}
full = models.Funds.objects.all()
instance = {x.secucode: x for x in full}
sql = render(sql, '<date>', date.strftime('%Y-%m-%d'))
data = read_oracle(sql)
data = data[data.publish == publish]
data = data[data.agg(lambda x: x.date.date() > existed.get(x.secucode, datetime.date(1990, 1, 1)), axis=1)]
data.secucode = data.secucode.apply(lambda x: instance.get(x))
data = data[data.secucode.notnull()]
data.ratio = data.ratio.fillna(0)
data = data.where(data.notnull(), None)
commit_by_chunk(data, models.FundHoldingStock)