def commit_asset_allocate_hk():
"""sync qdii fund asset allocate
Returns:
"""
data = read_oracle(funds.fund_allocate_hk)
date = dict(zip(data['secucode'], data['enddate']))
data['assettype'] = data['assettype'].astype('str')
ratio = data.pivot_table(index='secucode', columns='assettype', values='ratioinnv', aggfunc=sum)
ratio = ratio.fillna(0)
for col in ['10', '30', '10015', '40', '10075', '10089', '10090']:
if col not in ratio.columns:
ratio[col] = 0
ratio['stock'] = ratio['10'] / 100
ratio['bond'] = ratio['30']
ratio['metals'] = ratio['40'] / 100
ratio['fund'] = ratio['10015'] / 100
ratio['monetary'] = (ratio['10075'] + ratio['10089']) / 100
ratio['other'] = ratio['10090'] / 100
ratio = ratio[['stock', 'bond', 'fund', 'metals', 'monetary', 'other']]
ratio = ratio.reset_index()
ratio['date'] = ratio.secucode.apply(lambda x: date.get(x))
fund = models.Funds.objects.filter(secucode__in=list(ratio.secucode)).all()
fund = {x.secucode: x for x in fund}
this_year = datetime.date.today().year
latest = models.FundAssetAllocate.objects.values('secucode').annotate(mdate=Max('date'))
latest = {x['secucode']: x['mdate'] for x in latest}
ratio = ratio[ratio.agg(lambda x: x.date.date() > latest.get(x.secucode, datetime.date(this_year - 1, 1, 1)), axis=1)]
ratio['secucode'] = ratio['secucode'].apply(lambda x: fund.get(x))
if ratio.empty:
return
commit_by_chunk(data, models.FundAssetAllocate)
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 commit_industry_sw():
"""同步股票申万行业分类"""
full = read_oracle(template.stock_sw)
full = full.to_dict(orient='records')
stocks = list({x['secucode'] for x in full if not x['secucode'].startswith('X')})
all_ = models.Stock.objects.filter(secucode__in=stocks).all()
all_ = {x.secucode: x for x in all_}
with transaction.atomic():
for s in full:
# 不使用深拷贝这里导致了一个严重的问题：数据错乱
s = deepcopy(s)
c = s['secucode']
if any([c.startswith('4'), c.startswith('8'), c.startswith('X')]):
continue
f = all_.get(s.pop('secucode'))
if not f:
continue
s['secucode'] = f
m: models.StockIndustrySW = models.StockIndustrySW.objects.filter(secucode=f).last()
first = s['firstindustryname']
second = s['secondindustryname']
if m:
if m.firstindustryname == first:
continue
m.firstindustryname = first
m.secondindustryname = second
m.save()
else:
models.StockIndustrySW.objects.create(secucode=f, firstindustryname=first, secondindustryname=second)
def commit_fund_quote():
"""
同步场内基金行情数据
Returns:
"""
if not models.FundQuote.objects.exists():
data = read_oracle(funds.fund_quote_once)
else:
data = read_oracle(funds.fund_quote)
last = models.FundQuote.objects.values('secucode').annotate(mdate=Max('date'))
last = {x['secucode']: x['mdate'] for x in last}
data = data[data.agg(lambda x: x.date.date() > last.get(x.secucode, datetime.date(2021, 1, 1)), axis=1)]
instance = models.Funds.objects.all()
instance = {x.secucode: x for x in instance}
data.secucode = data.secucode.apply(lambda x: instance.get(x))
data = data[data.secucode.notnull()]
data = data.dropna(how='any')
commit_by_chunk(data, models.FundQuote)
def commit_stock():
full = read_oracle(template.stock)
full = full.to_dict(orient='records')
length = len(full)
local = stock_code_in_local()
with transaction.atomic():
for i, stock in enumerate(full):
if stock['secucode'] in local:
continue
models.Stock.objects.update_or_create(secucode=stock.pop('secucode'), defaults=stock)
progressbar(i, length)
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 commit_fund_associate():
"""更新基金代码关联表，主要针对ETF和LOF"""
sql = template.fund_associate
funds_jy = read_oracle(sql)
for _, row in funds_jy.iterrows():
exist = models.FundAssociate.objects.filter(secucode=row.secucode,
relate=row.relate).all()
if exist:
continue
fund = models.Funds.objects.filter(secucode=row.secucode).first()
if not fund:
continue
models.FundAssociate.objects.update_or_create(
relate=row.relate, secucode=fund, defaults={'define': row.define})
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_fund_asset_allocate():
"""更新基金资产配置比例
@updated: 20210706
@desc: 原有更新方式速度过慢，采用新的更新逻辑
"""
sql = template.fund_allocate
data = read_oracle(sql)
data = data.fillna(0)
latest = latest_update_date(models.FundAssetAllocate)
data = data[data.agg(lambda x: x.date.date() > latest.get(
x.secucode, datetime.date(2020, 1, 1)),
axis=1)]
data = replace_fund_instance(data)
commit_by_chunk(data, models.FundAssetAllocate)
def commit_fund_advisor():
"""同步基金管理人名称数据
Returns:
"""
data = read_oracle(funds.fund_advisor)
fund = models.Funds.objects.all()
fund = {x.secucode: x for x in fund}
exists = models.FundAdvisor.objects.values('secucode')
exists = [x['secucode'] for x in exists]
data = data[~data.secucode.isin(exists)]
data.secucode = data.secucode.apply(lambda x: fund.get(x))
data = data[data.secucode.notnull()]
commit_by_chunk(data, models.FundAdvisor)
def commit_fund_adj_nav():
"""
同步基金复权单位净值
Returns:
"""
if not models.FundAdjPrice.objects.exists():
sql = template.adj_nav_bulk
else:
sql = template.adj_nav
data = read_oracle(sql)
latest = latest_update_date(models.FundAdjPrice)
data = data[data.agg(
lambda x: x.date.date() > latest.get(x.secucode, ThisYear), axis=1)]
data = replace_fund_instance(data)
data = data.dropna()
commit_by_chunk(data, models.FundAdjPrice)
def commit_funds():
"""增量更新基金列表"""
sql = template.fund
funds_jy = read_oracle(sql)
funds_jy = funds_jy.to_dict(orient='records')
local = models.Funds.objects.all()
local = {x.secucode: x for x in local}
add = []
for f in funds_jy:
local_ = local.get(f['secucode'])
if not local_:
add.append(models.Funds(**f))
continue
if f['secuname'] == local_.secuname:
continue
local_.secuname = f['secuname']
local_.save()
models.Funds.objects.bulk_create(add)
def commit_fund_holding_stock_hk():
"""基金持仓股票-港股"""
data = read_oracle(funds.fund_holding_stock_hk)
data['ratio'] = data['ratio'] / 100
stocks = list(set(list(data.stockcode)))
stocks = models.Stock.objects.filter(secucode__in=stocks).all()
stocks = {x.secucode: x for x in stocks}
data['stockname'] = data.stockcode.apply(lambda x: stocks.get(x).secuname if stocks.get(x) else None)
data['stockcode'] = data.stockcode.apply(lambda x: stocks.get(x) if stocks.get(x) else None)
data = data[data['stockcode'].notnull()]
fund = list(set(list(data.secucode)))
fund = models.Funds.objects.filter(secucode__in=fund).all()
fund = {x.secucode: x for x in fund}
latest = models.FundHoldingStock.objects.values('secucode').annotate(mdate=Max('date'))
latest = {x['secucode']: x['mdate'] for x in latest}
this_year = datetime.date.today().year
data = data[data.agg(lambda x: x.date.date() > latest.get(x.secucode, datetime.date(this_year-1, 1, 1)), axis=1)]
data['secucode'] = data.secucode.apply(lambda x: fund.get(x))
if data.empty:
return
commit_by_chunk(data, models.FundHoldingStock)
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 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_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_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)
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)