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'