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)