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_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_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_index_component():
"""同步指数成分列表
从聚源数据库获取股票规模指数的成分及权重，如中证800、沪深300
:return:
"""
ib = models.IndexBasicInfo
indexes: List[ib] = ib.objects.filter(category='规模类指数',
component='股票').all()
indexes: List[models.Index] = [x.secucode for x in indexes]
indexes_code: List[str] = [x.secucode for x in indexes]
data = _commit_index_component(indexes_code)
latest = latest_update_date(models.IndexComponent)
data = data[data.agg(lambda x: x.date.date() > latest.get(
x.secucode, datetime.date(2021, 1, 1)),
axis=1)]
indexes: Dict[str, models.Index] = {x.secucode: x for x in indexes}
data.secucode = data.secucode.apply(lambda x: indexes.get(x))
commit_by_chunk(data, models.IndexComponent)
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 _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)