def createBulksEquities(df, dt): localSaveFile = createDir(['DATA', dt, 'Equities', 'CreateGeda']) try: dfAux = pd.read_csv(localSaveFile + '/ADD_Equities_GEDA_' + dt + '.txt', sep='\t') except: pass dfFinal = pd.DataFrame() dfFinal.loc[:, 'RIC'] = df['SYMBOL'].map(lambda x: x + '.CR' if '.' not in x else x[:3] + x[-1:].lower() + '.CR') dfFinal.loc[:, 'SYMBOL'] = df['SYMBOL'] dfFinal.loc[:, 'DSPLY_NAME'] = df['emissor'].map( lambda x: transformDsplyNm(x)) dfFinal.loc[:, 'OFFCL_CODE'] = df['ISIN'] dfFinal.loc[:, 'DDS_SYMBOL'] = df['SYMBOL'] dfFinal.loc[:, 'NAME_ROOT'] = df['emissor'].map( lambda x: x.upper() + '@') # Necessary correct manually dfFinal.loc[:, 'ISSUE_DETAILS'] = df['SYMBOL'].map( lambda x: 'STK' if '.' not in x else 'STK ' + x[-1:]) dfFinal.loc[:, 'ORG_NAME'] = df['emissor'].map( lambda x: transformOrgNm(x) + ' ORD') dfFinal.loc[:, '#INSTMOD_ISIN_CODE'] = df['ISIN'] dfFinal.loc[:, '#INSTMOD_TDN_ASSET_SUB_TYPE'] = '#NULL#' dfFinal.loc[:, '#INSTMOD_TDN_CURRENCY'] = 'VES' dfFinal.loc[:, '#INSTMOD_TDN_ISSUE_DESC'] = 'ORD' dfFinal.loc[:, '#INSTMOD_TDN_SYMBOL'] = df['SYMBOL'] dfFinal.loc[:, 'EXL_NAME'] = 'CCSER_CCSEEQUITY' try: dfFinal = pd.concat([dfFinal, dfAux]) except: pass dfFinal.drop_duplicates(inplace=True) pathBulkNDA = createBulkNdaEquities(dfFinal, dt) dfFinal.to_csv(localSaveFile + '/ADD_Equities_GEDA_' + dt + '.txt', sep='\t', index=False, encoding='utf-8-sig') return [ dfFinal, localSaveFile + '/ADD_Equities_GEDA_' + dt + '.txt', pathBulkNDA ]
def main(date): date = datetime.strftime(datetime.strptime(date, '%d/%m/%Y'), '%Y-%m-%d') pathRoot = createDir(['DATA', date]) pathFileXLSX = getAttcEmailGmail(date, pathRoot) if type(pathFileXLSX) == list: for i in range(len(pathFileXLSX)): pathFileBulk = createBulk(date, pathRoot, pathFileXLSX[i]) sendBulks(date, pathFileBulk) elif type(pathFileXLSX) == str: pathFileBulk = createBulk(date, pathRoot, pathFileXLSX) sendBulks(date, pathFileBulk) else: print('\nNo email to update.')
def createCAZ(df, tp, date): localSaveCaz = createDir(['DATA', date, 'CAZ']) EffectiveDate = date try: wb = load_workbook(localSaveCaz+'/New RICs CAZ - Venezuela '+EffectiveDate+'.xlsx') except: wb = newCaz() i = contRowFill(wb) # print(i)d print(df) if tp == 'Bonds': for index, row in df.iterrows(): wb['INPUT SHEET'].cell(row=i, column=1).value = 'Create' wb['INPUT SHEET'].cell(row=i, column=3).value = 'Add' wb['INPUT SHEET'].cell(row=i, column=4).value = EffectiveDate wb['INPUT SHEET'].cell(row=i, column=6).value = row['SYMBOL'] wb['INPUT SHEET'].cell(row=i, column=8).value = row['RIC'] wb['INPUT SHEET'].cell(row=i, column=10).value = row['OFFCL_CODE'] wb['INPUT SHEET'].cell(row=i, column=11).value = 'Ticker' wb['INPUT SHEET'].cell(row=i, column=13).value = row['SYMBOL'] wb['INPUT SHEET'].cell(row=i, column=17).value = 'CCS' wb['INPUT SHEET'].cell(row=i, column=18).value = 'BON' i=i+1 wb.save(localSaveCaz+'/New RICs CAZ - Venezuela '+EffectiveDate+'.xlsx') wb.close() return localSaveCaz+'/New RICs CAZ - Venezuela '+EffectiveDate+'.xlsx' if tp == 'Equities': for index, row in df.iterrows(): wb['INPUT SHEET'].cell(row=i, column=1).value = 'Create' wb['INPUT SHEET'].cell(row=i, column=3).value = 'Add' wb['INPUT SHEET'].cell(row=i, column=4).value = EffectiveDate wb['INPUT SHEET'].cell(row=i, column=6).value = row['DSPLY_NAME'] wb['INPUT SHEET'].cell(row=i, column=8).value = row['RIC'] wb['INPUT SHEET'].cell(row=i, column=10).value = row['OFFCL_CODE'] wb['INPUT SHEET'].cell(row=i, column=11).value = 'Ticker' wb['INPUT SHEET'].cell(row=i, column=13).value = row['SYMBOL'] wb['INPUT SHEET'].cell(row=i, column=17).value = 'CCS' wb['INPUT SHEET'].cell(row=i, column=18).value = 'EQI' i=i+1 wb.save(localSaveCaz+'/New RICs CAZ - Venezuela '+EffectiveDate+'.xlsx') wb.close() return localSaveCaz+'/New RICs CAZ - Venezuela '+EffectiveDate+'.xlsx'
def createBulkGedaBonds(df, dt): localSaveFile = createDir(['DATA', dt, 'Bonds', 'CreateGeda']) try: dfAux = pd.read_csv(localSaveFile + '/ADD_Bonds_GEDA_' + dt + '.txt', sep='\t') except: pass # print('Failed') dfFinal = pd.DataFrame() dfFinal.loc[:, 'RIC'] = df['SYMBOL'].map(lambda x: x + '=CR') dfFinal.loc[:, 'SYMBOL'] = df['SYMBOL'] dfFinal.loc[:, 'DSPLY_NAME'] = df['SYMBOL'] dfFinal.loc[:, 'OFFCL_CODE'] = df['ISIN'] dfFinal.loc[:, 'COUPN_RATE'] = '0' dfFinal.loc[:, 'ISSUE_DATE'] = df['FechaInicioSesion'].map( lambda x: datetime.strftime(datetime.strptime(x, '%d/%m/%Y'), '%d-%m-%Y')) dfFinal.loc[:, 'ORG_NAME'] = df['emissor'].map(lambda x: x.upper()) dfFinal.loc[:, 'MATUR_DATE'] = df['FechaVencimientoSerie'].map( lambda x: datetime.strftime(datetime.strptime(x, '%d/%m/%Y'), '%d-%m-%Y')) dfFinal.loc[:, 'NAME_ROOT'] = df['SYMBOL'].map(lambda x: x + '@') dfFinal.loc[:, 'ISSUE_DETAILS'] = df['ISIN'] dfFinal.loc[:, '#INSTMOD_TDN_SYMBOL'] = df['SYMBOL'] dfFinal.loc[:, 'EXL_NAME'] = 'CCSER_CCSEBOND' dfFinal.loc[:, 'CHAIN_RIC'] = 'BVCBONDS.CR' try: dfFinal = pd.concat([dfFinal, dfAux]) except: pass # print('Failed') dfFinal.drop_duplicates(inplace=True) dfFinal.to_csv(localSaveFile + '/ADD_Bonds_GEDA_' + dt + '.txt', sep='\t', index=False, encoding='utf-8-sig') #dfFinal.to_csv(localSaveFile+'ADD_Bonds_GEDA_'+dt+'.csv',sep=',', index=False) return [dfFinal, localSaveFile + '/ADD_Bonds_GEDA_' + dt + '.txt']
def createReport(df, tp, date): localSaveReport = createDir(['DATA', date, 'Reports']) EffectiveDate = date try: wb = load_workbook(localSaveReport + '/New RICs Report - Venezuela ' + EffectiveDate + '.xlsx') except: wb = newReport() i = contRowFill(wb) if tp == 'Bonds': for index, row in df.iterrows(): wb['Sheet1'].cell(row=i, column=2).value = row['SYMBOL'] wb['Sheet1'].cell(row=i, column=3).value = row['DSPLY_NAME'] wb['Sheet1'].cell(row=i, column=4).value = row['RIC'] wb['Sheet1'].cell(row=i, column=5).value = row['ORG_NAME'] wb['Sheet1'].cell(row=i, column=6).value = 'ADD' wb['Sheet1'].cell(row=i, column=7).value = row['OFFCL_CODE'] wb['Sheet1'].cell(row=i, column=8).value = EffectiveDate i = i + 1 wb.save(localSaveReport + '/New RICs Report - Venezuela ' + EffectiveDate + '.xlsx') wb.close() return localSaveReport + '/New RICs Report - Venezuela ' + EffectiveDate + '.xlsx' elif tp == 'Equities': for index, row in df.iterrows(): wb['Sheet1'].cell(row=i, column=2).value = row['SYMBOL'] wb['Sheet1'].cell(row=i, column=3).value = row['DSPLY_NAME'] wb['Sheet1'].cell(row=i, column=4).value = row['RIC'] wb['Sheet1'].cell(row=i, column=5).value = row['ORG_NAME'] wb['Sheet1'].cell(row=i, column=6).value = 'ADD' wb['Sheet1'].cell(row=i, column=7).value = row['OFFCL_CODE'] wb['Sheet1'].cell(row=i, column=8).value = EffectiveDate i = i + 1 wb.save(localSaveReport + '/New RICs Report - Venezuela ' + EffectiveDate + '.xlsx') wb.close() return localSaveReport + '/New RICs Report - Venezuela ' + EffectiveDate + '.xlsx'
def createBulkNdaEquities(df, dt): localSaveFile = createDir(['DATA', dt, 'Equities', 'CreateNda']) try: dfAux = pd.read_csv(localSaveFile + '/ADD_Equities_GEDA_' + dt + '.txt', sep='\t') except: pass dfFinal = pd.DataFrame() dffinal.loc[:, 'RIC'] = df['RIC'] dffinal.loc[:, 'ASSET SHORT NAME'] = df['DSPLY_NAME'] dffinal.loc[:, 'ASSET COMMON NAME'] = df['DSPLY_NAME'] dffinal.loc[:, 'TICKER SYMBOL'] = df['SYMBOL'] dffinal.loc[:, 'TAG'] = '199' dffinal.loc[:, 'TYPE'] = 'EQUITY' dffinal.loc[:, 'CATEGORY'] = 'ORD' dffinal.loc[:, 'SETTLEMENT PERIOD'] = 'T+2' dffinal.loc[:, 'CURRENCY'] = 'VES' dffinal.loc[:, 'EXCHANGE'] = 'CCS' dffinal.loc[:, 'ROUND LOT SIZE'] = '1' #dffinal.loc[:,'PILC'] = #dffinal.loc[:,'ISIN'] = try: dfFinal = pd.concat([dfFinal, dfAux]) except: pass dfFinal.drop_duplicates(inplace=True) dfFinal = pd.DataFrame() dfFinal.to_csv(localSaveFile + '/ADD_Equities_NDA_' + dt + '.csv', sep=',', index=False, encoding='utf-8-sig') return localSaveFile + '/ADD_Equities_NDA_' + dt + '.csv'