def writePremioREF(file1='b3_premioREF'):
from .globalMongo import mds
from .mongo import metadataAsset, metadataOption
dfs = uu.load_obj(file1)
dfs = pd.concat(dfs)
dfs.dt = pd.to_datetime(dfs.dt, format='%Y%m%d')
dfs.venc = pd.to_datetime(dfs.venc, format='%Y%m%d')
del dfs['id']
del dfs['compl']
del dfs['reg']
del dfs['tipoUnderlying']
del dfs['numCasasDec']
yearStr = pd.DatetimeIndex(dfs.venc).year.astype(str)
dfs['codeSerie'] = dfs.code + dfs.serie + yearStr
dfs.set_index('codeSerie', inplace=True)
uCode = pd.unique(dfs.index)
assetLst = np.array(mds.b3VS.list_symbols())
assetLst = [s.replace('_b3_opc_1BDay', '') for s in assetLst]
#uCode = np.setdiff1d(uCode,np.array(assetLst))
uCode = [u for u in uCode if not u in assetLst]
for i, code1 in enumerate(uCode):
df_ = ds.mDataFrame(dfs.loc[code1].copy())
if isinstance(dfs.loc[code1], pd.Series):
continue
df_.set_index('dt', inplace=True)
#del df_['codeSerie']
df_.md = metadataOption(
code1 + '_b3_opc',
'option',
df_.index[0].to_pydatetime(),
None,
'BRL',
False,
df_.venc[-1].to_pydatetime(),
strike=df_.strike[-1],
underlying=df_.code[-1] +
uu.futCodeFromDate(df_.venc[-1].to_pydatetime()),
cp=df_.tipoOpc[-1].lower())
df_.md.subtype = 'option'
df_.md.optModel = df_.modeloOpc[-1].lower()
df1 = df_[['premio']]
df1.columns = ['close']
print('writing {} - {}/{}'.format(code1, i, len(uCode)))
try:
mds.delete(code1[:-4], ds.freqHelper.bday, mds.b3VS)
except:
pass
df1 = uu.drop_duplicates_index(df1)
mds.write(df1,
mds.b3VS,
check_metadata=False,
prune_previous_version=True)
def writePremioEq(file1='b3_premioEq'):
from .globalMongo import mds
from .mongo import metadataAsset, metadataOption
dfs = uu.load_obj(file1)
dfs = pd.concat(dfs)
dfs.dt = pd.to_datetime(dfs.dt, format='%Y%m%d')
dfs.venc = pd.to_datetime(dfs.venc, format='%Y%m%d')
yearStr = pd.DatetimeIndex(dfs.venc).year.astype(str)
dfs.index = dfs.index + yearStr
uCode = pd.unique(dfs.index)
assetLst = np.array(mds.b3VS.list_symbols())
assetLst = [s.replace('_b3_opc_1BDay', '') for s in assetLst]
#uCode = np.setdiff1d(uCode,np.array(assetLst))
#uCode = [u for u in uCode if not u in assetLst]
uCode = list(set(uCode) - set(assetLst))
for i, code1 in enumerate(uCode):
df_ = ds.mDataFrame(dfs.loc[code1].copy())
if isinstance(dfs.loc[code1], pd.Series):
continue
df_.set_index('dt', inplace=True)
df_.md = metadataOption(
code1 + '_b3_opc',
'option',
df_.index[0].to_pydatetime(),
None,
'BRL',
False,
df_.venc[-1].to_pydatetime(),
strike=df_.strike[-1],
underlying=code1[:4] +
uu.futCodeFromDate(df_.venc[-1].to_pydatetime()),
cp=df_.code[-1].lower())
df_.md.subtype = 'equity_option'
df_.md.optModel = df_.type[-1].lower()
df1 = df_[['premio', 'vol', 'strike']]
df1.columns = ['close', 'vol', 'strike']
print('writing {} - {}/{}'.format(code1, i, len(uCode)))
df1 = uu.drop_duplicates_index(df1)
mds.write(df1,
mds.b3VS,
check_metadata=False,
prune_previous_version=True)
def bloombergUpdateFutureLastDates(meta):
# assert srs_meta is not None
from mDataStore.mongo import metadataFundamental
from mDataStore.globalMongo import mds
from mDataStore.mongo import mDataFrame
# tb = srs_meta['intra_table']
# tb = tb[tb.subtype == 'fut_rol']
meta1 = [m for m in meta if m['subtype']=='fut_rol' and 'feeder_id' in m]
for i,m in enumerate(tqdm(meta1)):
df1 = blp.getRefData([m['feeder_id']], ['FUT_CHAIN_LAST_TRADE_DATES'], {"INCLUDE_EXPIRED_CONTRACTS": "Y"})
if df1.shape[0]==0:
continue
aa = np.array(df1.iloc[0, 0])
df_ = mDataFrame(aa[1:], columns=aa[0])
df_.md = metadataFundamental(m['name'].lower()+'_dates', type='futureLastTradeDate')
mds.write(df_, library=mds.fundamentalVS, check_metadata=False, prune_previous_version=True)
def bloombergUpdateHistory(meta,field_hist,dti, overwrite=False,doParallel=True,
prune_previous_version=True,dtm1=None,dtm3=None,c = None,initial=False,keep_metadata=False,maxQuery=64):
from mDataStore import mDataFrame, metadataFundamental, metadataOption, metadataAsset, metadataIndicator, \
metadataStrategy
from dask.distributed import Client
if doParallel:
try:
c = Client()
except:
c = None
else:
c=None
if dtm1 is None:
import pytz
tz = pytz.timezone('GMT')
dtm1=(dt.now()-datetime.timedelta(1)).replace(hour=0, minute=0, second=0, microsecond=0)
# dtm1=tz.localize(dtm1)
if dtm3 is None:
dtm3 = (dt.now()-datetime.timedelta(3)).replace(hour=0, minute=0, second=0, microsecond=0)
success={}
dt_min = dt.today()-datetime.timedelta(days=80)
meta0 = [m for m in meta if 'feeder_id' in m and isinstance(m['feeder_id'],str) and m['feeder_id'] !='']
###
for i,fh in enumerate(field_hist):
if initial:
meta1 = array([m for m in meta0 if 'field_hist' in m and m['field_hist']==fh ])
else:
#exclude series whose maturity is before dti and that have endDT and it is before the minimum thresh (dt_min)
meta1 = array([m for m in meta0 if ('field_hist' in m and m['field_hist']==fh)
and (not ('endDT' in m and not m['endDT_assetVS'] is None and m['endDT'] < dt_min))
and (not ('maturity' in m and not m['maturity'] is None and dti>m['maturity']))])
if len(meta1) ==0:
continue
N = int(np.ceil(len(meta1)/maxQuery))
fields1 = array(field_hist[fh]['field'])
options1 = field_hist[fh]['options']
out1 = array(field_hist[fh]['out'])
for k in tqdm(range(N)): #tqdm
meta1a = meta1[k*maxQuery:(k+1)*maxQuery]
II = np.array([True if not m['feeder_id'] is None else False for m in meta1])
if any(~(II)):
warn('tickers missing')
meta1a = meta1a[II]
if len(meta1a) == 0:
continue
feeder_id=array([m['feeder_id'] for m in meta1a])
df1 = blp.getHistoricData(feeder_id, fields1, dti, dtm1,**eval('dict('+options1+')'))
# desc1 = blp.getRefData(feeder_id, desc)
for i,df0 in enumerate(df1):
df1[i]=df1[i].rename(columns=dict(zip(fields1,out1)))
df1[i]=mDataFrame(df1[i])
md1=copy(meta1a[i])
df1[i].md=metadataAsset(**md1)
# a=1
# df1[i].md.stDT = df1[i].index[0]
# df1[i].md.endDT = df1[i].index[-1]
if c is not None:
addr = mds.mongoCli.address[0]+':'+str(mds.mongoCli.address[1])
fut = {}
for l, df in enumerate(df1):
fut[df.md.name] = c.submit(insert2DB, False, df,df.md['library'], addr, overwrite, prune_previous_version,
dti, dtm1, dtm3,keep_metadata)
for i,f in enumerate(fut):
success[f] = fut[f].result()
else:
# addr =
for l, df in enumerate(df1):
success[df.md.name] = insert2DB(True, df,df.md.library, mds, overwrite, prune_previous_version, dti, dtm1, dtm3,keep_metadata)
return [k for k in success if not success[k]]
def writeFromXML(fileCadastro='b3_cadastroXML', filePx='b3_pesqXML'):
from .globalMongo import mds
from .mongo import metadataAsset, metadataOption
cad = uu.load_obj(fileCadastro)
px = uu.load_obj(filePx)
#must also get dates
codes = list(set.union(*[set(p.keys()) for p in px]))
if saveClean:
saveCleanXML(fileCadastro, filePx)
#px_ = {{k:l[k] for k in l} for l in px}
#keys1 = [s for s in cad.keys()]
lst_b3 = mds.find(library=[mds.b3VS, mds.b3VS_XML], metadata=False)
lst_b3 = [l[1].replace('_1BDay', '') for l in lst_b3]
flds = ('dt', 'FrstPric', 'MinPric', 'MaxPric', 'LastPric', 'TradAvrgPric',
'FinInstrmQty', 'OpnIntrst')
cols = ['open', 'low', 'high', 'close', 'vwap', 'volume', 'open_interest']
map1 = {flds[i + 1]: c for i, c in enumerate(cols)}
for j, c in enumerate(codes):
#must use dates here
if c in cad:
cad1 = cad[c]
if c in cad and any([
cad1['code'].startswith(m) for m in mercList + otherMerc
]) or cad1['nmInf'] == 'OptnOnEqtsInf':
px1 = [{k: v for (k, v) in p[c].items() if k in flds} for p in px]
px1 = [p for p in px1 if len(p) > 0]
df1 = ds.mDataFrame(pd.DataFrame.from_dict(px1))
df1.set_index('dt', inplace=True)
df1.sort_index(inplace=True)
df1 = df1[(~np.all(np.isnan(df1.values), 1))]
if df1.shape[0] == 0:
continue
isOpt = 'ExrcPric' in cad1
if isOpt:
code1 = cad1['code'] + '_b3_opc'
optSubtype = 'equity_option' if (
'nmInf' in cad1
and cad1['nmInf'] == 'OptnOnEqtsInf') else 'option'
strike = cad1['ExrcPric'] if not isinstance(
cad1['ExrcPric'], list) else cad1['ExrcPric'][-1][-1]
cp = 'p' if 'PUT' in cad1['OptnTp'].upper() else 'c'
underlyingId = str(
cad1['UndrlygInstrmId']['OthrId']['Id'])[:-2]
underlying = cad[underlyingId]['code']
df1.md = metadataOption(code1,
'option',
df1.index[0],
fut_like=False,
maturity=cad1['XprtnDt'],
strike=strike,
underlying=underlying,
cp=cp)
df1.md.underlyingId = underlyingId
df1.md.subtype = optSubtype
df1.md.optModel = 'e' if cad1['OptnStyle'] == 'EURO' else 'a'
if optSubtype == 'equity_option':
strikes = cad1['ExrcPric']
if not isinstance(strikes, list):
strikes = [[None, strikes]]
strikes = np.array(strikes)
for i, s in enumerate(strikes[:, 0]):
strikes[i, 0] = dt(1900, 1, 1) if s is None else s
strikeSrs = pd.Series(strikes[:, 1].astype(np.float64),
strikes[:, 0])
strikeSrs1 = strikeSrs.reindex(df1.index, method='ffill')
df1['strike'] = strikeSrs1
df1.md.strikes = strikes.tolist()
# strikeSrs.index[0]=None
else:
code1 = cad1['code'] + '_b3'
df1.md = metadataAsset(code1,
'future',
df1.index[0],
fut_like=True,
maturity=cad1['XprtnDt'])
df1.md.id = c
df1 = df1.rename(columns=map1)
if code1 in lst_b3:
df0 = mds.read(code1, library=[mds.b3VS, mds.b3VS_XML])
df0_ = df0.copy()
df1_ = df1.copy()
try:
del df0_['strike']
except:
pass
try:
del df0_['open_interest']
except:
pass
try:
del df1_['strike']
except:
pass
try:
del df1_['open_interest']
except:
pass
df0 = df0.loc[(~np.all(pd.isnull(df0_.values), 1))]
df1 = df1.loc[~df1.index.isin(df0.index)
& (~np.all(pd.isnull(df1_.values), 1))]
if df1.shape[0] == 0:
continue
md = df1.md
df1 = ds.mDataFrame(pd.concat([df0, df1], 0, sort=True))
df1.md = md
df1 = df1.loc[(~np.all(pd.isnull(df1.values), 1))]
if df1.shape[0] == 0:
continue
#check if exists and append !!!
print('{} = {}/{}'.format(code1, j, len(codes)))
df1 = uu.drop_duplicates_index(df1)
if isOpt:
mds.write(df1,
mds.b3VS_XML,
check_metadata=False,
prune_previous_version=True)
else:
mds.write(df1,
mds.b3VS,
check_metadata=False,
prune_previous_version=True)
def writeDFSPregao(file1='b3_pregaoFinal'):
from .globalMongo import mds
from .mongo import metadataAsset, metadataOption
dfs = uu.load_obj(file1)
df = pd.concat(dfs)
flds = [
'dt', 'code', 'serieVenc', 'venc', 'strike', 'valPonto', 'volRS',
'qtdAberto', 'qtdNeg', 'qtdTrans', 'ultOCP', 'ultOVD', 'abtNeg',
'minNeg', 'maxNeg', 'medNeg', 'ultNeg', 'dtUltNeg', 'horaUltNeg',
'aju', 'du', 'dtLimiteNeg', 'dtLiqFin', 'vencContratoObj', 'codeGTS',
'tipoSerie'
]
newFlds = [
'dt', 'codeMerc', 'srsCode', 'maturity', 'strike', 'multiplier',
'volume_fin', 'open_interest', 'neg', 'volume', 'bid', 'ask', 'open',
'low', 'high', 'vwap', 'last', 'dtLast', 'timeLast', 'close', 'du',
'dtTradeLimit', 'dtFinSettle', 'underlyingSrsCode', 'code', 'cp'
]
df = df[flds]
df.columns = newFlds
mdfFields = [
'dt', 'code', 'bid', 'ask', 'open', 'low', 'high', 'vwap', 'last',
'dtLast', 'timeLast', 'close', 'du', 'volume_fin', 'open_interest',
'neg', 'volume'
]
descFields = np.setdiff1d(newFlds, mdfFields).tolist() + ['dt', 'code']
df = df.loc[df.codeMerc.isin(mercList)]
df0 = df[mdfFields]
dfMeta = df[descFields]
uCode = np.unique(df0.code)
for i, code1 in enumerate(uCode):
df_ = ds.mDataFrame(df0.loc[df0.code == code1])
df_.set_index('dt', inplace=True)
del df_['code']
dfm_ = dfMeta.loc[dfMeta.code == code1].copy()
dfm_.set_index('dt', inplace=True)
del dfm_['code']
# for c in dfm_.columns:
#
# try:
# if dfm_[c].dtype==object:
# dfm_[c] = pd.to_numeric(dfm_[c])
# except:
# pass
# if dfm_[c].dtype==np.float64:
# assert((np.all(dfm_[c]==dfm_[c][0]) | np.isnan(dfm_[c][0]) ))
# else:
# assert(np.all(dfm_[c] == dfm_[c][0]))
if dfm_.cp[-1] in ['C', 'V']:
df_.md = metadataOption(code1 + '_b3',
'option',
df_.index[0].to_pydatetime(),
None,
'BRL',
False,
dfm_.maturity[-1].to_pydatetime(),
strike=dfm_.strike[-1],
underlying=dfm_.codeMerc[-1] +
dfm_.underlyingSrsCode[-1],
cp=dfm_.cp[-1].lower())
df_.md.subtype = 'option'
else:
df_.md = metadataAsset(code1 + '_b3', 'future',
df_.index[0].to_pydatetime(), None, 'BRL',
False, dfm_.maturity[-1].to_pydatetime())
df_.md.subtype = 'future'
df_.md.source = 'B3'
print('writing {}. {}/{}'.format(code1, i, uCode.size))
if not df_.index.is_unique:
nm = df_.index.name if not df_.index.name is None else 'index'
df_ = df_.reset_index().drop_duplicates(subset=nm,
keep='last').set_index(nm)
mds.write(df_,
mds.b3VS,
check_metadata=False,
prune_previous_version=True)