def fuzzy_bond_map_by_guarantor(self, guarantor):
ins = sa.select([
self.convertible_basics.c.sid, self.convertible_basics.c.guarantor
])
rp = engine.execute(ins)
bond_basics = pd.DataFrame(rp.fetchall(), columns=['sid', 'guarantor'])
bond_sids = bond_basics['sid'][bond_basics['guarantor'] == guarantor]
bond_assets = self.retrieve_asset(bond_sids.values())
return bond_assets
def fuzzy_dual_equities(self):
""" 获取A股与H股同时上市的股票"""
ins = sa.select(
[self.equity_basics.c.sid, self.equity_basics.c.dual_sid])
ins = ins.where(self.equity_basics.c.dual_sid != '')
rp = engine.execute(ins)
duals = pd.DataFrame(rp.fetchall(), columns=['sid', 'dual'])
print('duals', duals)
dual_equities = self.retrieve_asset(duals['sid'].values)
return dual_equities
def _initialize_basics_mappings(self):
table = metadata.tables['asset_router']
ins = select([table.c.asset_type, table.c.asset_name, table.c.first_traded,
table.c.last_traded, table.c.country_code, table.c.exchange])
ins = ins.where(table.c.sid == self.sid)
rp = engine.execute(ins)
basics = pd.DataFrame(rp.fetchall(), columns=['asset_type', 'asset_name', 'first_traded',
'last_traded', 'country_code', 'exchange'])
for k, v in basics.iloc[0, :].items():
self.__setattr__(k, v)
def _retrieve_array(self, sid):
edate = datetime.datetime.now().strftime('%Y-%m-%d')
sdate = '1990-01-01' if self._init else edate
tbl = metadata.tables['equity_price']
sql = sa.select([tbl.c.trade_dt, tbl.c.close]).\
where(sa.and_(tbl.c.trade_dt.between(sdate, edate), tbl.c.sid == sid))
rp = engine.execute(sql)
frame = pd.DataFrame([[r.trade_dt, r.close] for r in rp.fetchall()],
columns=['date', 'close'])
frame = self._adjust_frame_type(frame)
frame.set_index('date', inplace=True)
return frame.iloc[:, 0]
def _retrieve_ownership(self):
tbl = metadata.tables['ownership']
sql = sa.select([tbl.c.sid, tbl.c.ex_date, tbl.c.general, tbl.c.float])
# sql = sa.select([tbl.c.sid, tbl.c.ex_date, tbl.c.general, tbl.c.float]).where(tbl.c.sid == '000002')
rp = engine.execute(sql)
frame = pd.DataFrame([[r.sid, r.ex_date, r.general, r.float] for r in rp.fetchall()],
columns=['sid', 'date', 'general', 'float'])
frame.set_index('sid', inplace=True)
frame.replace('--', 0.0, inplace=True)
frame = self._adjust_frame_type(frame)
unpack_frame = unpack_df_to_component_dict(frame)
return unpack_frame
def lookup_bond_ownership_by_equity(self, sid):
"""
基于A股代码找到对应的可转债
"""
ins = sa.select([
self.convertible_basics.c.sid, self.convertible_basics.c.swap_code
])
rp = engine.execute(ins)
bond_basics = pd.DataFrame(rp.fetchall(), columns=['sid', 'swap_code'])
bond_sids = bond_basics['sid'][bond_basics['swap_code'] == sid]
bond_assets = self.retrieve_asset(bond_sids.values)
return bond_assets
def _implement_supplementary(self):
tbl = metadata.tables['equity_basics']
ins = sa.select([tbl.c.dual_sid,
tbl.c.broker,
tbl.c.district,
tbl.c.initial_price]).where(tbl.c.sid == self.sid)
rp = engine.execute(ins)
raw = pd.DataFrame(rp.fetchall(), columns=['dual',
'broker',
'district',
'initial_price'])
for k, v in raw.iloc[0, :].to_dict().items():
self.__setattr__(k, v)
def synchronize(self):
# daily synchronize ---- change every day
ins = sa.select(
[self.asset_router.c.sid, self.asset_router.c.asset_type])
rp = engine.execute(ins)
asset_frame = pd.DataFrame(rp.fetchall(),
columns=['sid', 'asset_type'])
# update
asset_frame.set_index('sid', inplace=True)
assets_updated = set(asset_frame.index) - set(
chain(*self._asset_type_cache.values()))
if assets_updated:
proxy_frame = asset_frame[asset_frame.index.isin(
assets_updated)].groupby('asset_type').groups
for asset_type, sids in proxy_frame.items():
for sid in sids:
obj = AssetTypeMappings.get(asset_type, Fund)(sid)
self._asset_type_cache[obj.asset_type].add(obj)
def _implement_supplementary(self):
tbl = metadata.tables['convertible_basics']
ins = sa.select([tbl.c.swap_code,
tbl.c.put_price,
tbl.c.redeem_price,
tbl.c.convert_price,
tbl.c.convert_dt,
tbl.c.put_convert_price,
tbl.c.guarantor]).\
where(tbl.c.sid == self.sid)
rp = engine.execute(ins)
df = pd.DataFrame(rp.fetchall(), columns=['swap_code',
'put_price',
'redeem_price',
'convert_price',
'convert_dt',
'put_convert_price',
'guarantor'])
for k, v in df.iloc[0, :].to_dict().items():
self.__setattr__(k, v)