def get_carteras_fondos(date_anterior):
path_rf = ".\\Querys\\Bonos.sql"
path_if = ".\\Querys\\Depositos.sql"
query_rf = fs.read_file(path=path_rf).replace("AUTODATE", str(date_anterior))
query_if = fs.read_file(path=path_if).replace("AUTODATE", str(date_anterior))
cartera_fondos_rf = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_rf)
cartera_fondos_rf.set_index(["codigo_ins"], inplace=True)
cartera_fondos_if = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_if)
cartera_fondos_if = comput_nemotecnico_depositos(cartera_fondos_if, perf_att=False)
cartera_fondos_if.index.name='codigo_ins'
cartera_fondos = cartera_fondos_rf.append(cartera_fondos_if)
cartera_fondos['weight'] = cartera_fondos['weight'].astype('float64')
return cartera_fondos
def get_currency_value(date, name=None):
'''
Obtiene los valores de los currency para dos fechas. Cada vez que hay una nueva moneda hay que agregarlo a mano acá.
'''
days_dif = 0
if name.strip() == 'US$':
date_inc = fs.convert_date_to_string(date)
date = fs.convert_date_to_string(get_next_weekdays(date))
else:
prev_day = get_previous_weekdays(date)
date_inc = fs.convert_date_to_string(prev_day)
#solo para calcular bien el devengo diario de la uf, los dias lunes
days_dif = (date - prev_day).days
dic_id = {'UF': 23, 'US$': 66, 'MX': 85}
currency_id = dic_id[name]
query = "Select Valor from Indices_Dinamica where Index_ID={} and fecha in ('{}', '{}')".format(
currency_id, date_inc, date)
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
return df.loc[1]['Valor'], df.loc[0]['Valor'], days_dif
def get_currency_data(date, currency):
if currency.strip() == 'US$':
date_0 = date
date_1 = get_fecha_habil_posterior(date)
else:
date_0 = get_fecha_habil_anterior(date)
date_1 = date
delta_dias = (date_1 - date_0).days
currency_id = dic_currencies[currency][0]
paridad = dic_currencies[currency][1]
query = "Select valor from Indices_Dinamica where Index_ID={} and fecha in ('{}', '{}')".format(currency_id, date_0, date_1)
df_ccy_values = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
valor_0= df_ccy_values.loc[0]['valor']
valor_1= df_ccy_values.loc[1]['valor']
return valor_0, valor_1, delta_dias
def poblate_fondos(self):
'''
Llena la lista con los fondos disponibles en la base de datos
'''
self.model1.clear()
df = get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1",
"usuario1", "Select * From Fondos")
for i in df["codigo_fdo"]:
self.model1.appendRow(QStandardItem(i))
def get_fechas_habiles():
query = "Select Fecha from Fechas_habiles"
fechas_hab = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
#fechas_hab['Fecha'] = fechas_hab['Fecha'].apply(lambda x: datetime.datetime.strftime(x, '%Y-%m-%d'))
return fechas_hab
def get_flujo_compra_venta(date):
query = "Select Fecha, nemotecnico, vende, compra where fecha_liq = 'AUTODATE'"
query = query.replace("AUTODATE",str(date))
df_flujos = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
return df_flujos
def get_dic_em():
'''
Obtiene un frame con los nombres de emisor igual al de los de riskamerica
'''
query = 'select ltrim(rtrim(Emisor)) as Emisor, ltrim(rtrim(Codigo_SVS)) as Codigo_SVS from EmisoresIIF'
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df.set_index(["Emisor"], inplace=True)
return df
def get_updated_IIF(funds_list):
spot = fs.get_ndays_from_today(0)
yesterday = fs.get_ndays_from_today(1)
query = "select codigo_ins, fondo, SUM(cantidad) as nominal, codigo_emi, moneda, fec_vcto, SUM(Monto) as monto from (select codigo_fdo COLLATE DATABASE_DEFAULT as fondo, codigo_ins COLLATE DATABASE_DEFAULT as codigo_ins, codigo_emi, moneda,fec_vcto, cantidad, monto from zhis_carteras_main where fecha='YESTERDAY' and (codigo_ins like 'PAGARE%' or codigo_ins like 'PDBC%') UNION ALL select compra COLLATE DATABASE_DEFAULT as fondo, instrumento COLLATE DATABASE_DEFAULT as codigo_ins, emisor COLLATE DATABASE_DEFAULT as codigo_emi, moneda COLLATE DATABASE_DEFAULT as moneda, DATEADD(DAY, dias, Fecha) as fec_vcto, sum(rescate) as cantidad, sum(captacion) as monto from TransaccionesIIF where fecha='SPOT_DATE' and compra is not null group by fecha, instrumento, emisor,moneda, dias, compra UNION ALL select vende COLLATE DATABASE_DEFAULT as fondo, instrumento COLLATE DATABASE_DEFAULT as codigo_ins, emisor COLLATE DATABASE_DEFAULT as codigo_emi, moneda COLLATE DATABASE_DEFAULT as moneda, DATEADD(DAY, dias, Fecha) as fec_vcto, -sum(rescate) as cantidad, -sum(captacion) as monto from TransaccionesIIF where fecha='SPOT_DATE' and vende is not null group by fecha, instrumento, emisor,moneda, dias, vende ) T1 where fondo in (FUNDS_LIST) group by fondo, codigo_ins, codigo_emi, moneda, fec_vcto"
query = query.replace("SPOT_DATE", str(spot))
query = query.replace("YESTERDAY", str(yesterday))
query = query.replace("FUNDS_LIST", str(funds_list))
iif_portfolio = fs.get_frame_sql_user(server="puyehue", database="MesaInversiones", username="******", password="******", query=query)
return iif_portfolio
def get_updated_RFI(funds_list):
spot = fs.get_ndays_from_today(0)
yesterday = fs.get_ndays_from_today(1)
query = " select codigo_ins, fondo, SUM(Cantidad) as nominal, SUM(Monto) as monto from (select codigo_fdo COLLATE DATABASE_DEFAULT as fondo, codigo_ins COLLATE DATABASE_DEFAULT as codigo_ins, cantidad, monto from zhis_carteras_main where fecha='YESTERDAY' and codigo_ins not like 'PAGARE%' and Tipo_Instrumento not in ('Cuota de Fondo','Letra Hipotecaria') UNION ALL select compra COLLATE DATABASE_DEFAULT as fondo, instrumento COLLATE DATABASE_DEFAULT as codigo_ins, sum(cantidad), sum(Monto) from TransaccionesIRF where fecha='SPOT_DATE' and compra is not null group by Instrumento, compra UNION ALL select vende COLLATE DATABASE_DEFAULT as fondo, instrumento COLLATE DATABASE_DEFAULT as codigo_ins, -sum(cantidad), -sum(Monto) from TransaccionesIRF where fecha='SPOT_DATE' and vende is not null group by Instrumento, vende) T1 where fondo in (FUNDS_LIST) group by fondo, codigo_ins"
query = query.replace("SPOT_DATE", str(spot))
query = query.replace("YESTERDAY", str(yesterday))
query = query.replace("FUNDS_LIST", str(funds_list))
print(query)
irf_portfolio = fs.get_frame_sql_user(server="puyehue", database="MesaInversiones", username="******", password="******", query=query)
return irf_portfolio
def get_dic_ins():
'''
Obtenemos el listado de los nombre de los instrumentos equivalentes para riskamerica
'''
query = 'Select ltrim(rtrim(Codigo_Ins)) as Codigo_Ins, ltrim(rtrim(Isin)) as Isin from Mapping_BBL_BCS_diego'
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df.set_index(["Isin"], inplace=True)
return df
def get_fund_names():
'''
Obtenemos los nombres de los fondos a trabajar
'''
query = "select ltrim(rtrim(codigo_fdo)) as codigo_fdo , ltrim(rtrim(moneda)) as moneda from fondosir where shore= 'onshore' and estrategia in ('renta fija' , 'credito', 'retorno absoluto') and codigo_fdo not in ('LIQUIDEZ')"
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df.set_index(['codigo_fdo'], inplace=True)
return df.index.tolist(), df
def eliminar_bonos_vencidos():
today = str(dt.datetime.now().date())
#today = dt.datetime.now()
query_cupones = "SELECT * FROM [MesaInversiones].[dbo].[RF_Cupones]" ##WHERE [Fecha_corte] <= '{}' OR [Vencimiento] <= '{}'".format(today, today)
df_cupones = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_cupones)
indices = df_cupones[(df_cupones['Fecha_corte'] <= today) |
(df_cupones['Vencimiento'] <= today)].index.tolist()
print('----------------indices----------------')
print(len(indices))
df_cupones = df_cupones.drop(indices)
insert_into_database(df_cupones)
def getInfoIndicesAFP():
path="queryAFPs.sql"
query = lib.read_file(path=path)
sqlQuery="SELECT INDEX_ID FROM INDICES_ESTATICA WHERE NOMBRE_INDEX LIKE '%FONDO E%'"
vals = lib.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
return vals
def get_liquidacion_transacciones(date):
query_iif = "Select Fecha, nemotecnico as nemo, vende, compra, captacion as monto from TransaccionesIIF where fecha_liq = 'AUTODATE'"
query_iif = query_iif.replace("AUTODATE", str(date))
query_irf = "Select Fecha, instrumento as nemo, vende, compra, monto from TransaccionesIRF where fecha_liq = 'AUTODATE'"
query_irf = query_irf.replace("AUTODATE", str(date))
df_flujos_iif = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_iif)
df_flujos_irf = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_irf)
df_flujos = df_flujos_iif.append(df_flujos_irf)
return df_flujos
def get_currency_types(date):
'''
Obtenemos los tipos de moneds de los instrumentos para una fecha
'''
date = fs.convert_date_to_string(get_previous_weekdays(date))
query = open('./querys/currency.txt', 'r').read()
query = query.replace('[DATE]', "'"+date+"'")
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
return df['moneda'].tolist()
def get_funds_currency():
'''
Obtenemos los nombres de los fondos a trabajar
'''
#query = "select ltrim(rtrim(codigo_fdo)) as codigo_fdo , ltrim(rtrim(moneda)) as moneda_fondo from fondosir where shore= 'onshore' and estrategia in ('renta fija' , 'credito', 'retorno absoluto')"
query = "select ltrim(rtrim(codigo_fdo)) as codigo_fdo , ltrim(rtrim(moneda)) as moneda_fondo from fondosir where estrategia in ('renta fija' , 'credito', 'retorno absoluto')"
df_funds_currency = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df_funds_currency = df_funds_currency.set_index('codigo_fdo')
return df_funds_currency
def get_data(name, date, new_index=False):
'''
Obtiene los leverage de los fondos
'''
date = fs.convert_date_to_string(get_previous_weekdays(date))
query = open('./querys/{}'.format(name), 'r').read()
query = query.replace('[DATE]', "'"+date+"'")
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
if new_index is not False:
df.set_index([new_index], inplace=True)
return df
def get_leverage(date):
path = ".\\Querys\\Leverage.sql"
query = fs.read_file(path=path).replace("AUTODATE", str(date))
df_leverage = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df_leverage['monto_pactado'] = df_leverage['monto_pactado'].astype('float64')
df_leverage = df_leverage.set_index('codigo_fdo')
return df_leverage
def get_posiciones_forwards(date):
path = ".\\Querys\\Forwards.sql"
query = fs.read_file(path=path).replace("AUTODATE", str(date))
df_forwards = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df_forwards = compute_forwards_net_positions(df_forwards)
df_forwards = df_forwards.set_index('codigo_fdo')
return df_forwards
def get_funds_aum(date):
path = ".\\Querys\\AUM.sql"
query = fs.read_file(path=path).replace("AUTODATE", str(date))
df_funds_aum = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
df_funds_aum['AUM'] = df_funds_aum['AUM'].astype('float64')
df_funds_aum = df_funds_aum.set_index('codigo_fdo')
return df_funds_aum
def get_currency_type(date, df_risk):
date_anterior = utiles.get_fecha_habil_anterior(date)
path = ".\\querys\\mapping_cinta.sql"
query = fs.read_file(path=path).replace("AUTODATE", str(date_anterior))
cinta_ra = fs.get_frame_sql_user(server="Puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
cinta_ra.set_index(["Codigo_Ins"], inplace=True)
df_mapped = df_risk.merge(cinta_ra, how='left', left_index=True, right_index=True)
return df_mapped
def get_currency_types(date):
'''
Obtenemos los tipos de moneds de los instrumentos para una fecha
ESTO SE PUEDE HACER MEJOR
'''
#date_anterior = fs.convert_date_to_string(get_fecha_habil_anterior(date))
date_anterior =get_fecha_habil_anterior(date)
query = "select ltrim(rtrim(c.moneda)) as moneda from ZHIS_Carteras_Recursive as c INNER JOIN fondosir as f ON f.Codigo_fdo = c.codigo_fdo collate database_default where fecha ='AUTODATE' and c.moneda not in ('$') and Tipo_instrumento in ('Bono Corporativo', 'Bono de Gobierno', 'Deposito') and f.shore= 'onshore' and f.estrategia in ('renta fija' , 'credito', 'retorno absoluto') GROUP BY c.moneda"
query = query.replace("AUTODATE", str(date_anterior))
df = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query)
return df['moneda'].tolist()
def get_tpm_and_icp(date):
date_ayer = utiles.get_fecha_habil_anterior(date)
date_ante_ayer = utiles.get_fecha_habil_anterior(date_ayer)
query_tpm = "Select Fecha, valor from Indices_Dinamica where Index_Id=459 and Fecha>='DATE_1' and Fecha<='DATE_2'"
query_tpm = query_tpm.replace('DATE_1',str(date_ante_ayer)).replace('DATE_2', str(date_ayer))
df_tpm = fs.get_frame_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_tpm)
query_icp = "Select valor from Indices_Dinamica where Index_Id=4 and Fecha='DATE'"
query_icp = query_icp.replace('DATE', str(date))
print(query_icp)
icp = fs.get_val_sql_user(server="puyehue",
database="MesaInversiones",
username="******",
password="******",
query=query_icp)
return df_tpm, icp
def check_excel():
today = str(dt.datetime.now().date())
yesterday = fs.get_prev_weekday(str(dt.datetime.now().date()))
excel = fs.get_self_path() + "Cupones_{}".format(today) + ".xlsx"
wb = fs.open_workbook(excel, True, True)
df = fs.get_frame_xl(wb, "Hoja1", 2, 3, [0])
fs.close_workbook(wb)
query_bonos = (
"SELECT [codigo_ins], [codigo_fdo], [nominal], [fec_vcto] FROM [MesaInversiones].[dbo].[ZHIS_Carteras_Main] WHERE [fecha] = '{}' AND ([codigo_ins] NOT LIKE '{}' AND [codigo_ins] NOT LIKE '{}') AND [codigo_fdo] IN ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}') AND [Tipo_Instrumento] NOT IN ('{}', '{}')"
).format(yesterday, 'PAGARE%', 'PDBC%', 'SPREADCORP', 'DEUDA CORP',
'DEUDA 360', 'LIQUIDEZ', 'M_MARKET', 'MACRO CLP3', 'MACRO 1.5',
'RENTA', 'IMT E-PLUS', 'Cuota de Fondo', 'Letra Hipotecaria')
df_bonos = fs.get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1",
"usuario1", query_bonos)
query_depositos = (
"SELECT [codigo_ins], [codigo_fdo], [nominal], [codigo_emi], [Moneda], [fec_vcto] FROM [MesaInversiones].[dbo].[ZHIS_Carteras_Main] WHERE [fecha] = '{}' AND ([codigo_ins] LIKE '{}' OR [codigo_ins] LIKE '{}') AND [codigo_fdo] in ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')"
).format( ##(Tipo_Instrumento IN ('Bono Corporativo', 'Deposito', 'Bono de Gobierno', 'Letra Hipotecaria', 'Factura', 'RF LATAM') OR Tipo_Instrumento is NULL)").format(
yesterday, 'PAGARE%', 'PDBC%', 'SPREADCORP', 'DEUDA CORP', 'DEUDA 360',
'LIQUIDEZ', 'M_MARKET', 'MACRO CLP3', 'MACRO 1.5', 'RENTA',
'IMT E-PLUS')
df_depositos = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1",
query_depositos)
query_cupones = "SELECT * FROM [MesaInversiones].[dbo].[Cupones_RF]"
df_cupones = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_cupones)
df_bonos_t = df_bonos.loc[df_bonos['fec_vcto'] == today]
df_depositos = df_depositos.loc[df_depositos['fec_vcto'] == today]
df_excel = pd.DataFrame(columns=[
'Fondo', 'Cupones', 'Vencimiento D', 'Vencimiento B', 'Total'
])
for row in df.iterrows():
fondo = row[0].strip()
vencimiento_recibido_pdf_d = float(
str(row[1]['Vencimiento D']).replace('.', '').replace(',', '.'))
vencimiento_recibido_base_d = 0
vencimiento_recibido_pdf_b = float(
str(row[1]['Vencimiento B']).replace('.', '').replace(',', '.'))
vencimiento_recibido_base_b = 0
recibido_base = 0
total_recibido_cupones = float(
str(row[1]['Cupones']).replace('.', '').replace(',', '.'))
for line in df_depositos.iterrows():
if line[1]['codigo_fdo'].strip() == fondo:
vencimiento_recibido_base_d += float(line[1]['nominal'])
for linea in df_bonos_t.iterrows():
instrumento = linea[1]['codigo_ins']
df_cupon = df_cupones.loc[df_cupones['Instrumento'] == instrumento]
for i in df_cupon.iterrows():
vencimiento_recibido_base_b += i[1]['Cantidad_corte']
if vencimiento_recibido_pdf_d != vencimiento_recibido_base_d:
### Acá agregar que pasa si es que los vencimientos del pdf no coinciden con los vencimientos de la base de datos
pass
excel_row = [
fondo,
locale.format("%f",
total_recibido_cupones,
grouping=True,
monetary=True)[:-5],
locale.format("%f",
vencimiento_recibido_base_d,
grouping=True,
monetary=True)[:-5],
locale.format("%f",
vencimiento_recibido_base_b,
grouping=True,
monetary=True)[:-5],
locale.format("%f",
total_recibido_cupones +
vencimiento_recibido_base_d +
vencimiento_recibido_base_b,
grouping=True,
monetary=True)[:-5]
]
df_excel = df_excel.append(pd.DataFrame([excel_row],
columns=[
'Fondo', 'Cupones',
'Vencimiento D',
'Vencimiento B', 'Total'
]),
ignore_index=True)
excel = resource_path("Cupones_" + today + ".xlsx")
writer = pd.ExcelWriter(excel)
#### Escribe el dataframe del resumen de los fondos corregidos en una página del excel
df_excel.to_excel(writer, sheet_name='Resumen', startrow=1, startcol=1)
writer.sheets['Resumen'].column_dimensions['C'].width = 13
writer.sheets['Resumen'].column_dimensions['D'].width = 14
writer.sheets['Resumen'].column_dimensions['E'].width = 14
writer.sheets['Resumen'].column_dimensions['F'].width = 14
writer.sheets['Resumen'].column_dimensions['G'].width = 14
writer.save()
### Imprime solo el resumen de los fondos en un PDF
#fs.export_sheet_pdf_jp(0, fs.get_self_path()+"Cupones_" + today + ".xlsx", fs.get_self_path()+"Informe_cupones_" + today + "_corregido.pdf")
fondos = [
'SPREADCORP', 'DEUDA CORP', 'DEUDA 360', 'LIQUIDEZ', 'M_MARKET',
'MACRO CLP3', 'MACRO 1.5', 'RENTA', 'IMT E-PLUS', 'None'
]
for fondo in fondos:
df_sheet = pd.DataFrame(
columns=['Fondo', 'Instrumento', 'Emisor'
'Ingreso'])
for line in df_depositos.iterrows():
if line[1]['codigo_fdo'].strip() == fondo:
row = [
fondo, line[1]['codigo_ins'].strip(),
line[1]['codigo_emi'].strip(),
locale.format("%f",
float(line[1]['nominal']),
grouping=True,
monetary=True)[:-5]
]
print(row)
df_sheet = df_sheet.append(pd.DataFrame(
[row],
columns=['Fondo', 'Instrumento', 'Emisor', 'Ingreso']),
ignore_index=True)
for fila in df_cupones.loc[df_cupones['Fecha_corte'] ==
today].iterrows():
instrumento = fila[1]['Instrumento']
for linea in df_bonos.iterrows():
if instrumento == linea[1]['codigo_ins'] and fondo == linea[1][
'codigo_fdo']:
row = [
fondo, instrumento, None,
locale.format(
"%f",
((float(linea[1]['Cantidad_corte']) / 1000000) *
linea[1]['nominal']),
grouping=True,
monetary=True)[:-5]
]
df_sheet = df_sheet.append(pd.DataFrame(
[row],
columns=['Fondo', 'Instrumento', 'Emisor', 'Ingreso']),
ignore_index=True)
print(row)
#### Escribe el detalle de todos los intrumentos de cada fondo que reportan ingresos
df_sheet.to_excel(writer, sheet_name=fondo, startrow=1, startcol=1)
print('-------------' + fondo + '-------------')
print(df_sheet)
for fon in fondos:
writer.sheets[fon].column_dimensions['C'].width = 14
writer.sheets[fon].column_dimensions['D'].width = 14
writer.sheets[fon].column_dimensions['E'].width = 14
writer.sheets[fon].column_dimensions['F'].width = 14
writer.sheets[fon].column_dimensions['G'].width = 15
writer.save()
writer.close()
#### Imprime el detalle de todos los fondos en un PDF (la primera lista indica los indices del excel que quiero pasar a PDF,
#### deberían llegar hasta el 10 para que los imprima todos, pero tira un error de list index que no he podido arreglar)
fs.export_sheet_pdf_multiple_index(
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
fs.get_self_path() + "Cupones_" + today + ".xlsx",
fs.get_self_path() + "Informe_cupones_" + today + "_detalle.pdf")
def transaccionesRVN(path, fecha):
l = [
"estado", "fecha_sesion", "codigo_secuencia", "apertura_cierre",
"base_calculo", "broker", "cam_divisa", "canones_div", "cartera",
"comision_div", "corretaje_div", "cot_recompra", "cot_rf_cup",
"cotizacion", "cupon_corrido_div", "cupon_per", "cupon_per_div",
"dias", "divisa", "efectivo_div", "efectivo_vto_div",
"ent_contrapartida", "ent_depositaria", "ent_liquidadora",
"ent_mediadora", "fec_comunicacion_be", "fec_liquidacion",
"fec_operacion", "fec_recompra", "fec_valor", "fec_vto", "gasto_extra",
"gastos_div", "gestor_ordenante", "ind_nominal_srn",
"ind_periodificar", "instrumento", "liquido_div", "mercado",
"minusvalia", "minusvalia_div", "neto_div", "nominal_div", "operacion",
"otros_gastos_div", "previa_compromiso_plazo", "plusvalia",
"plusvalia_div", "prima_div", "primario_secundario", "regularizar_sn",
"repo_dia", "tipo_interes", "titulos", "ventana_tratamiento",
"ind_proceso", "enlace_sucursal", "enlace_tipo_ap",
"enlace_plan_subplan", "retencion_div", "ind_cambio_asegurado",
"ind_depositario", "simple_compuesto", "tex_pago", "tex_pagare_cod",
"tex_pagares", "num_comunica_be", "iva", "iva_div", "cuenta_ccc",
"ind_cobertura", "fecha_saldo", "libre_n1", "libre_n2", "libre_x1",
"libre_x2", "fecha_ejecucion", "codigo_uti", "comision_ecc_div",
"tir_mercado"
]
excel = path
diccionario = []
contador = 0
with open(excel, "r") as datos:
for linea in datos:
oracion = linea.strip()
oracion = oracion.split(";")
diccionario.append(oracion)
date = fecha[6:10] + '-' + fecha[3:5] + '-' + fecha[0:2]
#instrumentos = get_frame_sql_user("Puyehue", "MesaInversiones","usrConsultaComercial" , "Comercial1w","Select * from Instrumentos where tipo_instrumento not like '%RF LATAM%' AND tipo_instrumento not like '%accion%' AND tipo_instrumento not like '%cuota%' AND tipo_instrumento not like '%ETF%'")
instrumentos = get_frame_sql_user(
"Puyehue", "MesaInversiones", "usrConsultaComercial", "Comercial1w",
"Select * from Instrumentos where tipo_instrumento not like '%Bono%' AND tipo_instrumento not like '%letra%' AND tipo_instrumento not like '%deposito%' and tipo_instrumento != ';D15;' and Tipo_Instrumento != '' AND tipo_instrumento not like '%pagare%' AND tipo_instrumento not like '%Factura%'"
)
df = get_frame_sql_user(
"Puyehue", "MesaInversiones", "usrConsultaComercial", "Comercial1w",
"select * from [MesaInversiones].[dbo].[Perfil Clientes] WHERE not Rut IS NULL"
)
df = df.drop([
"Nombre", "Tipo", "Orientacion", "Perfil_riesgo", "Tracking Objetivo",
"RVL", "RVG", "RFL", "RFG", "Liq", "LIQ_USD", "Fwd", "Cuenta Pershing",
"RutConVerificador", "Administracion", "FechaTermino",
"Codigo_Recomendacion"
],
axis=1)
nombres_ins = instrumentos["Codigo_Ins"].values.tolist()
new_df = pd.DataFrame(columns=[
'Folio_operacion', 'Fecha operacion', '1', '2', '3', 'Rut',
'secuencia', 'monto', 'Precio', 'Cantidad', '4', 'Operacion', '5',
'instrumento', '6', 'Moneda', 'fecha_liq', '7', 'ent_depositaria',
'liquidacion', '8', '9', '10', '11', '12', '13'
],
data=diccionario)
new_df = new_df.drop(
["1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13"],
axis=1)
new_df2 = new_df.copy()
eliminados = 0
eliminados2 = 0
largo = len(new_df.index)
for indice in range(largo):
if not new_df.iloc[indice - eliminados]["instrumento"] in nombres_ins:
new_df = new_df.drop(new_df.index[[indice - eliminados]])
eliminados += 1
else:
new_df2 = new_df2.drop(new_df2.index[[indice - eliminados2]])
eliminados2 += 1
#print(new_df)
if new_df.empty:
df1 = pd.DataFrame(columns=l)
return df1
else:
fecha_operaciones = date
cambio_diario = get_frame_sql_user(
"Puyehue", "replicasCredicorp", "usrConsultaComercial",
"Comercial1w",
"select RTRIM(LTRIM(CODIGO_MONEDA_ORIGEN)) AS Moneda,VALOR_PARIDAD from VISTA_PARIDADES_CDP WHERE FECHA_PARIDAD = '{}' AND CODIGO_MONEDA = 'CLP' AND (CODiGO_MONEDA_ORIGEN = 'USD' OR CODiGO_MONEDA_ORIGEN = 'EUR') AND GRUPO_COTIZACION = 1 "
.format(fecha_operaciones))
df1 = pd.DataFrame(columns=[
"estado", "fecha_sesion", "codigo_secuencia", "apertura_cierre",
"base_calculo", "broker", "cam_divisa", "canones_div", "cartera",
"comision_div", "corretaje_div", "cot_recompra", "cot_rf_cup",
"cotizacion", "cupon_corrido_div", "cupon_per", "cupon_per_div",
"dias", "divisa", "efectivo_div", "efectivo_vto_div",
"ent_contrapartida", "ent_depositaria", "ent_liquidadora",
"ent_mediadora", "fec_comunicacion_be", "fec_liquidacion",
"fec_operacion", "fec_recompra", "fec_valor", "fec_vto",
"gasto_extra", "gastos_div", "gestor_ordenante", "ind_nominal_srn",
"ind_periodificar", "instrumentox", "liquido_div", "mercado",
"minusvalia", "minusvalia_div", "neto_div", "nominal_div",
"operacion", "otros_gastos_div", "previa_compromiso_plazo",
"plusvalia", "plusvalia_div", "prima_div", "primario_secundario",
"regularizar_sn", "repo_dia", "tipo_interes", "titulos",
"ventana_tratamiento", "ind_proceso", "enlace_sucursal",
"enlace_tipo_ap", "enlace_plan_subplan", "retencion_div",
"ind_cambio_asegurado", "ind_depositario", "simple_compuesto",
"tex_pago", "tex_pagareS_cod", "tex_pagares", "num_comunica_be",
"iva", "iva_div", "cuenta_ccc", "ind_cobertura", "fecha_saldo",
"libre_n1", "libre_n2", "libre_x1", "libre_x2", "fecha_ejecucion",
"codigo_uti", "tir_mercado"
])
df1 = df1.assign(fecha_sesion=new_df['Fecha operacion'])
vago = new_df["Rut"]
new_df["codigo_secuencia"] = new_df.index
df1 = df1.assign(codigo_secuencia=new_df.index)
df1 = df1.assign(estado='T')
#df1 = df1.assign(fecha_sesion = new_df['Fecha operacion'])
df1 = df1.assign(apertura_cierre='NULL')
df1 = df1.assign(base_calculo=3) # modificar
df1 = df1.assign(broker='CCCAPITAL')
df1 = df1.assign(cam_divisa=new_df['Moneda'])
#print(cambio_diario['VALOR_PARIDAD'].loc[cambio_diario['Moneda']== 'USD'] )
df1.loc[df1['cam_divisa'] == 'US$',
'cam_divisa'] = cambio_diario['VALOR_PARIDAD'].loc[
cambio_diario['Moneda'] == 'USD']
df1.loc[df1['cam_divisa'] == '$', 'cam_divisa'] = '1'
df1.loc[df1['cam_divisa'] == 'UF',
' cam_divisa'] = cambio_diario['VALOR_PARIDAD'].loc[
cambio_diario['Moneda'] == 'UF']
df1.loc[df1['cam_divisa'] == 'EUR',
'cam_divisa'] = cambio_diario['VALOR_PARIDAD'].loc[
cambio_diario['Moneda'] == 'EUR']
df1 = df1.assign(canones_div=None)
df = df.astype({"Rut": int, "Secuencia": int})
new_df[['Rut', 'no']] = new_df['Rut'].str.split('-', expand=True)
new_df = new_df.astype({"Rut": int, "secuencia": int})
df["Dig Ver"] = df["Dig Ver"].str.strip()
df_join = dataframe_join(df, new_df, 'Rut')
df_join = df_join.loc[(df_join["Secuencia"] == df_join["secuencia"])
& (df_join["no"].to_string(index=False).upper()
== df_join["Dig Ver"].to_string(
index=False).upper())]
df1 = dataframe_join(df_join, df1, 'codigo_secuencia')
df1['Codigo_Fdo'] = df1['Codigo_Fdo'].str.strip()
instrumentos = instrumentos.rename(
columns={"Codigo_Ins": "instrumento"})
instrumentos["instrumento"] = instrumentos["instrumento"].str.strip()
instrumentos["Codigo_Emi"] = instrumentos["Codigo_Emi"].str.strip()
df1 = dataframe_join(df1, instrumentos[["instrumento", "Codigo_Emi"]],
"instrumento")
df1 = df1.drop(["cartera"], axis=1)
df1 = df1.drop(["cotizacion"], axis=1)
df1 = df1.drop(["instrumentox"], axis=1)
df1 = df1.drop(["efectivo_div"], axis=1)
df1 = df1.drop(["ent_liquidadora"], axis=1)
df1 = df1.drop(["nominal_div"], axis=1)
df1 = df1.drop(["operacion"], axis=1)
df1 = df1.rename(columns={"Codigo_Emi": "ent_liquidadora"})
df1 = df1.rename(columns={"Precio": "cotizacion"})
#df1 = df1.assign(cotizacion =0 )
df1 = df1.rename(columns={"monto": "efectivo_div"})
df1 = df1.rename(columns={"Cantidad": "nominal_div"})
df1 = df1.rename(columns={"Operacion": "operacion"})
df1 = df1.rename(columns={"Codigo_Fdo": "cartera"})
df1 = df1.assign(comision_div=0)
df1 = df1.assign(corretaje_div=0)
df1 = df1.assign(cot_recompra=0)
df1 = df1.assign(cot_rf_cup=0) # cotizacion renta fija con cupon
df1 = df1.assign(cupon_corrido_div=None)
df1 = df1.assign(cupon_per_div=0)
df1 = df1.assign(cupon_per=0)
df1 = df1.assign(dias=0)
df1 = df1.assign(divisa=new_df['Moneda'])
#print(cambio_diario['VALOR_PARIDAD'].loc[cambio_diario['Moneda']== 'USD'] )
df1.loc[df1['divisa'] == 'US$', 'divisa'] = '5'
df1.loc[df1['divisa'] == '$', 'divisa'] = '39'
df1.loc[df1['divisa'] == 'UF', ' divisa'] = '84'
df1.loc[df1['divisa'] == 'EUR', 'divisa'] = '4'
df1 = df1.assign(efectivo_vto_div=0) #
df1 = df1.assign(ent_contrapartida='CCCAPITAL')
df1 = df1.assign(ent_depositaria='DCV') # deposito central de valores
# sicav sociedad de inversion de capital variable
# sicaf socidad de inversion de capital fijo
df1 = df1.assign(ent_mediadora=None) # consultar
df1 = df1.assign(fec_comunicacion_be=None) # consultar
## ponerle el assign con el loc aca segun el tipo de liquidacion
df1.loc[df1["liquidacion"] == "PH", "fec_liquidacion"] = date
df1.loc[df1["liquidacion"] == "PM",
"fec_liquidacion"] = get_next_weekday(date)
df1.loc[df1["liquidacion"] == "PM",
"fec_liquidacion"] = get_nex2_weekday(date)
df1 = df1.assign(fec_operacion=date) # consultar
df1 = df1.assign(fec_recompra=None) # consultar
df1 = df1.assign(fec_valor=date) # consultar
df1 = df1.assign(fec_vto=None)
df1 = df1.assign(gasto_extra=0)
df1 = df1.assign(gastos_div="")
df1 = df1.assign(gestor_ordenante=2960) # Consultar
# consultar suma o resta segun que
df1.loc[df1["operacion"] == "C", 'ind_nominal_srn'] = "S"
df1.loc[df1["operacion"] == "V", "ind_nominal_srn"] = "R"
df1 = df1.assign(ind_periodificar=None)
df1 = df1.assign(liquido_div=df1['efectivo_div'])
# Renta variable Nacional
df1 = df1.assign(mercado='RVN')
df1 = df1.assign(minusvalia=0)
df1 = df1.assign(minusvalia_div=0)
df1 = df1.assign(neto_div=df1['efectivo_div'])
df1 = df1.assign(otros_gastos_div='')
df1 = df1.assign(
previa_compromiso_plazo='D') # compromiso diario segun que
df1 = df1.assign(plusvalia=0)
df1 = df1.assign(plusvalia_div=0)
df1 = df1.assign(prima_div=None)
df1 = df1.assign(primario_secundario='P')
df1 = df1.assign(regularizar_sn='S') # CONDICIONES
df1 = df1.assign(repo_dia='N')
df1 = df1.assign(tipo_interes=0)
df1 = df1.assign(titulos=df1['nominal_div']) # consultar
df1 = df1.assign(ventana_tratamiento='RVN')
df1 = df1.assign(ind_proceso='D')
df1 = df1.assign(enlace_sucursal='')
df1 = df1.assign(enlace_tipo_ap='')
df1 = df1.assign(enlace_plan_subplan='')
df1 = df1.assign(retencion_div=0)
df1.loc[df1["liquidacion"] != "PH", "fec_liquidacion"] = "S"
df1.loc[df1["liquidacion"] == "PH", "ind_cambio_asegurado"] = "N"
df1 = df1.assign(ind_depositario='')
df1 = df1.assign(simple_compuesto='')
df1 = df1.assign(tex_pago='')
df1 = df1.assign(tex_pagareS_cod=None)
df1 = df1.assign(tex_pagares=None)
df1 = df1.assign(num_comunica_be=None)
df1 = df1.assign(iva=None)
df1 = df1.assign(iva_div=None)
df1 = df1.assign(cuenta_ccc='')
df1 = df1.assign(ind_cobertura=None)
df1 = df1.assign(fecha_saldo='')
df1 = df1.assign(libre_n1='')
df1 = df1.assign(libre_n2='')
df1 = df1.assign(libre_x1='')
df1 = df1.assign(libre_x2='')
df1 = df1.assign(fecha_ejecucion='')
df1 = df1.assign(codigo_uti='')
df1 = df1.assign(tir_mercado=0.000000000) # consultar
df1 = df1.drop([
"Rut", "Dig Ver", "Secuencia", "Status", "Folio_operacion",
"Fecha operacion", "secuencia", "Moneda", "liquidacion", "no"
],
axis=1)
columnas = list(df1.columns)
for columna in columnas:
if not columna in l:
df1 = df1.drop(["{}".format(columna)], axis=1)
for col in l:
if not col in df1.columns:
pass
#print(col)
duplicateRowsDF = df1[df1.duplicated(
['codigo_secuencia', 'efectivo_div'])]
df1.drop_duplicates(subset=['codigo_secuencia', 'efectivo_div'],
keep=False,
inplace=True)
df1 = df1.reindex(columns=l)
duplicateRowsDF = duplicateRowsDF.reindex(columns=l)
df1["tir_mercado"] = None
df1["comision_ecc_div"] = None
data = pd.concat([df1, duplicateRowsDF])
data["tir_mercado"] = None
data["comision_ecc_div"] = None
duplicateRowsDF1 = data[data.duplicated(
['codigo_secuencia', 'efectivo_div'])]
data.drop_duplicates(subset=['codigo_secuencia', 'efectivo_div'],
keep=False,
inplace=True)
data = data.reindex(columns=l)
duplicateRowsDF1 = duplicateRowsDF1.reindex(columns=l)
data["tir_mercado"] = None
data["comision_ecc_div"] = None
data1 = pd.concat([data, duplicateRowsDF1])
#print_full(data1)
print(data1['operacion'])
#data1.to_csv("hola.csv", index=False,sep = ';',encoding='latin-1')
return data1
#transaccionesRVN('GEO_ap/Geo/2020-01-03geo.txt', '03-01-2020')
def refreh_sql():
yesterday = fs.get_prev_weekday(str(dt.datetime.now().date()))
funds = utiles.get_FI_funds()
df_bonos = utiles.get_updated_RFI(funds)
df_depositos = utiles.get_updated_IIF(funds)
#query_f = "SELECT Codigo_Fdo FROM [MesaInversiones].[dbo].[FondosIR]"
#df_fondos = fs.get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1", "usuario1", query_f)
#query_c = "SELECT Codigo_Fdo FROM [MesaInversiones].[dbo].[Perfil Clientes]"
#df_carteras = fs.get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1", "usuario1", query_c)
#fondos = df_fondos['Codigo_Fdo'].values.tolist()
#carteras = df_carteras['Codigo_Fdo'].values.tolist()
#df_f = df.loc[(df['codigo_fdo'].isin(fondos))]
#df_c = df.loc[(df['codigo_fdo'].isin(carteras))]
query_cupones = "SELECT * FROM [MesaInversiones].[dbo].[RF_Cupones]"
df_cupones = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_cupones)
cupones = df_cupones['Instrumento'].values.tolist()
print('----------------cupones----------------')
print(len(cupones))
df_emisor = fs.get_frame_sql_user(
"Puyehue", "MesaInversiones", "usuario1", "usuario1",
"SELECT RTRIM(LTRIM([Emisor])) AS [Emisor], [Codigo_SVS] FROM EmisoresIIF"
)
set_bonos = {
row[1]['codigo_ins'].strip() + ' Corp'
for row in df_bonos.iterrows()
}
print(set_bonos)
set_depositos = {
nemo_depo(row[1]['codigo_emi'].strip(), row[1]['moneda'].strip(),
row[1]['fec_vcto'].strip(), df_emisor) + ' M-Mkt':
[row[1]['fec_vcto'], row[1]['moneda']]
for row in df_depositos.iterrows()
}
LocalTerminal = v3api.Terminal('localhost', 8194)
fails = []
for instrumento in set_bonos:
if instrumento[:-5] not in cupones:
try:
final_row = []
response = LocalTerminal.get_reference_data(
instrumento, [
'TRADE_CRNCY', 'NEXT_CASH_FLOW_DT', 'NEXT_CASH_FLOW',
'MATURITY'
])
bloombergData = response.as_frame().replace('nan', 0)
final_row.append(instrumento[:-5])
final_row.append(bloombergData.iloc[0]['TRADE_CRNCY'])
final_row.append(
timestamp_to_string(
bloombergData.iloc[0]['NEXT_CASH_FLOW_DT']))
final_row.append(bloombergData.iloc[0]['NEXT_CASH_FLOW'])
final_row.append(
timestamp_to_string(bloombergData.iloc[0]['MATURITY']))
df_cupones = df_cupones.append(pd.DataFrame(
[final_row],
columns=[
'Instrumento', 'moneda', 'Fecha_corte',
'Cantidad_corte', 'Vencimiento'
]),
ignore_index=True)
except Exception as e:
print(e)
print(instrumento)
fails.append(instrumento)
for instrumento in set_depositos:
if instrumento[:-6] not in cupones:
try:
final_row = []
final_row.append(instrumento[:-6])
moneda = set_depositos[instrumento][1].strip()
if moneda == '$':
moneda = 'CLP'
elif moneda == 'US$':
moneda = 'USD'
elif moneda == 'UF':
moneda = 'CLF'
final_row.append(moneda)
final_row.append(None)
final_row.append(1000000)
final_row.append(set_depositos[instrumento][0])
df_cupones = df_cupones.append(pd.DataFrame(
[final_row],
columns=[
'Instrumento', 'moneda', 'Fecha_corte',
'Cantidad_corte', 'Vencimiento'
]),
ignore_index=True)
except Exception as e:
print(e)
print(instrumento)
fails.append(instrumento)
insert_into_database(df_cupones)
print('----------------fails----------------')
print(len(fails))
def generar_resumen_carteras():
byesterday = fs.get_prev_weekday(
fs.get_prev_weekday(str(dt.datetime.now().date())))
tomorrow = fs.get_next_weekday(str(dt.datetime.now().date()))
today = str(dt.datetime.now().date())
query_c = "SELECT Codigo_Fdo FROM [MesaInversiones].[dbo].[Perfil Clientes]"
df_carteras = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_c)
carteras = df_carteras['Codigo_Fdo'].values.tolist()
query_cupones = "SELECT * FROM [MesaInversiones].[dbo].[RF_Cupones]"
df_cupones = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_cupones)
query_carteras = (
"SELECT [codigo_ins], RTRIM(LTRIM([codigo_fdo])) AS [codigo_fdo], [nominal], [codigo_emi], [moneda], [fec_vcto] FROM [MesaInversiones].[dbo].[ZHIS_Carteras_Main] WHERE [fecha] = '{}' AND [codigo_fdo] in ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')"
).format( ##(Tipo_Instrumento IN ('Bono Corporativo', 'Deposito', 'Bono de Gobierno', 'Letra Hipotecaria', 'Factura', 'RF LATAM') OR Tipo_Instrumento is NULL)").format(
byesterday, 'SPREADCORP', 'DEUDA CORP', 'DEUDA 360', 'LIQUIDEZ',
'M_MARKET', 'MACRO CLP3', 'MACRO 1.5', 'RENTA', 'IMT E-PLUS')
df_carteras = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_carteras)
### Agregarle los fines de semana
if not is_weekend(today, tomorrow):
cupones = {
row[1]['Instrumento'].strip(): row[1]['Cantidad_corte']
for row in df_cupones.iterrows()
if row[1]['Fecha_corte'] == tomorrow
and row[1]['Vencimiento'] != tomorrow
}
vencimientos = {
row[1]['Instrumento'].strip(): row[1]['Cantidad_corte']
for row in df_cupones.iterrows()
if row[1]['Vencimiento'] == tomorrow
}
else:
sabado = fs.get_next_day(today)
domingo = fs.get_next_day(sabado)
cupones = {
row[1]['Instrumento'].strip(): row[1]['Cantidad_corte']
for row in df_cupones.iterrows()
if (row[1]['Fecha_corte'] == tomorrow and row[1]['Vencimiento'] !=
tomorrow) or (row[1]['Fecha_corte'] == sabado
and row[1]['Vencimiento'] != sabado) or (
row[1]['Fecha_corte'] == domingo
and row[1]['Vencimiento'] != domingo)
}
vencimientos = {
row[1]['Instrumento'].strip(): row[1]['Cantidad_corte']
for row in df_cupones.iterrows()
if (row[1]['Vencimiento'] == tomorrow) or (
row[1]['Vencimiento'] == sabado) or (
row[1]['Vencimiento'] == domingo)
}
df_excel = pd.DataFrame(columns=[
'Cartera', 'Cupones', 'Vencimiento', 'Total', 'R Vencimiento'
])
df_emisor = fs.get_frame_sql_user(
"Puyehue", "MesaInversiones", "usuario1", "usuario1",
"SELECT RTRIM(LTRIM([Emisor])) AS [Emisor], [Codigo_SVS] FROM EmisoresIIF"
)
for cartera in carteras:
df_cartera = df_carteras[df_carteras['codigo_fdo'] == cartera]
total_recibido_vencimiento = 0
total_recibido_cupones = 0
vencimiento_recibido = False
for row in df_cartera.iterrows():
instrumento = row[1]['codigo_ins'].strip()
if 'PAGARE' in instrumento or 'PDBC' in instrumento:
instrumento = nemo_depo(row[1]['codigo_emi'].strip(),
row[1]['moneda'].strip(),
row[1]['fec_vcto'].strip(), df_emisor)
if instrumento in vencimientos:
print(instrumento)
print(float(row[1]['nominal']))
total_recibido_vencimiento += (
(vencimientos[instrumento] / 1000000) *
float(row[1]['nominal']))
vencimiento_recibido = True
elif instrumento in cupones:
total_recibido_cupones += ((cupones[instrumento] / 1000000) *
row[1]['nominal'])
excel_row = [
cartera, total_recibido_cupones, total_recibido_vencimiento,
total_recibido_cupones + total_recibido_vencimiento,
vencimiento_recibido
]
df_excel = df_excel.append(pd.DataFrame([excel_row],
columns=[
'Cartera', 'Cupones',
'Vencimiento', 'Total',
'R Vencimiento'
]),
ignore_index=True)
print(excel_row)
print('----------------------------------------------')
excel = resource_path("Cupones_carteras_" + tomorrow + ".xlsx")
writer = pd.ExcelWriter(excel)
df_excel.to_excel(writer, sheet_name='Hoja1', startrow=1, startcol=1)
writer.sheets['Hoja1'].column_dimensions['C'].width = 15
writer.sheets['Hoja1'].column_dimensions['D'].width = 15
writer.sheets['Hoja1'].column_dimensions['E'].width = 15
writer.sheets['Hoja1'].column_dimensions['F'].width = 15
writer.sheets['Hoja1'].column_dimensions['G'].width = 15
writer.save()
writer.close()
fs.export_sheet_pdf_jp(
0,
fs.get_self_path() + "Cupones_carteras_" + tomorrow + ".xlsx",
fs.get_self_path() + "Informe_cupones_carteras_" + tomorrow + ".pdf")
def Pershing(fecha):
strcuentas = [
'HMT082415', 'HMT090053', 'HMT090418', 'HMT090830', 'HMT090848',
'HMT090921', 'HMT090939', 'HMT090947', 'HMT090962', 'HMT090970',
'HMT100019', 'HMT100043', 'HMT100068', 'HMT100092', 'HMT270002',
'HMT390008', 'HMT090996', 'HMT100100', 'HMT091010', 'HMT091036',
'HMT086655', 'HMT086663', 'HMT086671', 'HMT086689', 'HMT087083',
'HMT091028', 'HMT091044', 'HMT087430'
]
strSQL = "SET DATEFORMAT DMY SELECT mov.ID , mov.Numero_Cuenta, cue.Nombre_cuenta,mov.Instrumento,mov.Simbolo_Instrumento, ins.CUSIP, ins.Codigo_Ins, ins.Nombre, ins.Codigo_Emi ,mov.Precio, mov.Simbolo_Instrumento, mov.Codigo_compraventa,mov.Cantidad, mov.Valorizacion , mov.Moneda, mov.fecha_transaccion, mov.fecha_liquidacion FROM [CONCILIACION_INTERNACIONAL].[dbo].[TBL_MOVIMIENTO] mov INNER JOIN [CONCILIACION_INTERNACIONAL].[dbo].[TBL_CUENTAS_INTERNACIONALES] cue ON mov.Numero_Cuenta=cue.Numero_Cuenta LEFT OUTER JOIN [lagunillas].PFMIMT3.dbo.FM_INSTRUMENTOS ins ON LTRIM(RTRIM(mov.Instrumento)) = LTRIM(RTRIM(ins.CUSIP)) collate SQL_Latin1_General_CP1_CI_AS WHERE mov.Fecha_transaccion = '" + fecha + "' and ins.CUSIP is not NULL"
df = get_frame_sql_user("Farellones", "CONCILIACION_INTERNACIONAL",
"usuario1", "usuario1", strSQL)
#Extrae la tabla con el valor de las paridades segun la fecha ingresada
eliminados = 0
largo = df.shape[0]
for i in range(largo):
row = df.iloc[i - eliminados]
if not row['Numero_Cuenta'].strip() in strcuentas:
df = df.drop(df.index[i - eliminados])
eliminados += 1
else:
pass
for row in df['Numero_Cuenta']:
row = row.strip()
rut = get_frame_sql_user(
"Farellones", "CONCILIACION_INTERNACIONAL", "usuario1", "usuario1",
"SELECT Rut,Nro_Secuencia FROM [CONCILIACION_INTERNACIONAL].[dbo].TBL_CUENTAS_INTERNACIONALES where Numero_Cuenta = '"
+ row + "'")
rut['Rut'] = rut['Rut'].to_string(index=False).strip()
rut['Nro_Secuencia'] = rut['Nro_Secuencia'].astype(int)
sec = rut['Nro_Secuencia']
sec = sec.to_string(index=False)
rut[['Rut', 'no']] = rut['Rut'].str.split('-', expand=True)
numero = rut['Rut'].astype(int)
digitover = rut['no'].astype(int)
codigo = get_frame_sql_user(
"Puyehue", "MesaInversiones", "usuario1", "usuario1",
"select * from [Perfil Clientes] where Rut = '" +
numero.to_string(index=False).strip() + "' and [Dig Ver] = '" +
digitover.to_string(index=False).strip() + "' and Secuencia = '" +
sec.strip() + "'")
df.loc[df["Numero_Cuenta"] == row,
"Numero_Cuenta"] = codigo['Codigo_Fdo'].to_string(
index=False).strip()
df['Estado'] = 'T'
## creamos una columna con la fecha de la session, es decir con la fecha actual
date = fecha[6:10] + '-' + fecha[3:5] + '-' + fecha[0:2]
df['fechasess'] = date
paridades = get_frame_sql_user(
"Puyehue", "replicasCredicorp", "usrConsultaComercial", "Comercial1w",
"Select * from VISTA_PARIDADES_CDP where CODIGO_MONEDA = 'CLP' AND FECHA_PARIDAD ='"
+ date + "' ORDER BY CODIGO_MONEDA_ORIGEN ASC")
paridades['CODIGO_MONEDA_ORIGEN'] = paridades[
'CODIGO_MONEDA_ORIGEN'].str.strip()
dolar = paridades['VALOR_PARIDAD'].loc[
(paridades['CODIGO_MONEDA_ORIGEN'] == 'USD')
& (paridades['GRUPO_COTIZACION'] == 1)]
dolar = dolar.to_string(index=False)
## creamos el dataframe con las columnas que utiliza RD
l = [
"estado", "fecha_sesion", "codigo_secuencia", "apertura_cierre",
"base_calculo", "broker", "cam_divisa", "canones_div", "cartera",
"comision_div", "corretaje_div", "cot_recompra", "cot_rf_cup",
"cotizacion", "cupon_corrido_div", "cupon_per", "cupon_per_div",
"dias", "divisa", "efectivo_div", "efectivo_vto_div",
"ent_contrapartida", "ent_depositaria", "ent_liquidadora",
"ent_mediadora", "fec_comunicacion_be", "fec_liquidacion",
"fec_operacion", "fec_recompra", "fec_valor", "fec_vto", "gasto_extra",
"gastos_div", "gestor_ordenante", "ind_nominal_srn",
"ind_periodificar", "instrumento", "liquido_div", "mercado",
"minusvalia", "minusvalia_div", "neto_div", "nominal_div", "operacion",
"otros_gastos_div", "previa_compromiso_plazo", "plusvalia",
"plusvalia_div", "prima_div", "primario_secundario", "regularizar_sn",
"repo_dia", "tipo_interes", "titulos", "ventana_tratamiento",
"ind_proceso", "enlace_sucursal", "enlace_tipo_ap",
"enlace_plan_subplan", "retencion_div", "ind_cambio_asegurado",
"ind_depositario", "simple_compuesto", "tex_pago", "tex_pagare_cod",
"tex_pagares", "num_comunica_be", "iva", "iva_div", "cuenta_ccc",
"ind_cobertura", "fecha_saldo", "libre_n1", "libre_n2", "libre_x1",
"libre_x2", "fecha_ejecucion", "codigo_uti", "comision_ecc_div",
"tir_mercado"
]
df2 = pd.DataFrame(columns=l)
df['Preciooo'] = df['Precio']
## agregamos columna estado
df2 = df2.assign(estado=df['Estado'])
## agregamos columna de fecha de session
df2 = df2.assign(fecha_sesion=date)
## agregamos columna con el codigo de secuencia
df2 = df2.assign(codigo_secuencia=df['ID'])
df2 = df2.assign(apertura_cierre='NULL')
#
#
df2 = df2.assign(
base_calculo=df['Moneda']
) #Agregamos columna base calculo segun las condiciones #Para RV dejaremos fijo "3". Para RFN debe ser "2" (Act/365).
#Para IIF sería "14" (Act/30) y si el papel es en moneda distinta de $ debe ser "3" (Act/360).
df2['base_calculo'] = df2['base_calculo'].str.strip()
df2.loc[df2['base_calculo'] == 'USD', 'base_calculo'] = '3' #
df2.loc[df2['base_calculo'] == 'EUR', 'base_calculo'] = '3' #
## agregamos columna broker fija para cccapital
df2 = df2.assign(broker='CCCAPITAL')
#
## agregamos columna cambio de divisa con el tipo de cambio actualizado segun los datos que se obtienen
## de la tabla paridades
df2 = df2.assign(cam_divisa=df['Moneda'])
df2['cam_divisa'] = df2['cam_divisa'].str.strip()
df2.loc[df2['cam_divisa'] == 'USD', 'cam_divisa'] = float(dolar)
df2.loc[df2['cam_divisa'] == 'EUR', 'cam_divisa'] = '860.44'
df2 = df2.assign(canones_div='NULL')
#
## agregamos la columna cartera con el codigo de fondo de cada instrumento
df2 = df2.assign(cartera=df['Numero_Cuenta'])
df2['cartera'] = df2['cartera'].str.strip()
df2 = df2.assign(comision_div=0)
df2 = df2.assign(corretaje_div=0)
df2 = df2.assign(cot_recompra=0)
df2 = df2.assign(cot_rf_cup=0) # cotizacion renta fija con cupon
## agregamos cotizacion segun las condiciones parqa renta variable es el precio de la operacion y para renta fija es 0
df2 = df2.assign(cotizacion=df['Preciooo'])
df2 = df2.assign(cupon_corrido_div=0) # FALTA AGREGAR
df2 = df2.assign(cupon_per_div=0)
df2 = df2.assign(cupon_per=0)
#
## agregamos la columna dias
#df['duration'].astype(float)
df2 = df2.assign(dias=0)
## agregamos la columna divisa con el ID de tipo de moneda
df2 = df2.assign(divisa=df['Moneda'])
df2['divisa'] = df2['divisa'].str.strip()
df2.loc[df2['divisa'] == 'USD', 'divisa'] = '5'
df2.loc[df2['divisa'] == 'EUR', 'divisa'] = '4'
#
df2 = df2.assign(efectivo_div=df['Valorizacion']) # monto, falta validar
df2 = df2.assign(efectivo_vto_div=0)
df2 = df2.assign(ent_contrapartida='CCCAPITAL') # valor por defecto
df2 = df2.assign(
ent_depositaria='DCV'
) # deposito central de valores, va como defecto v # sicav sociedad de inversion de capital variable # sicaf socidad de inversion de capital fijo
#
df2 = df2.assign(ent_liquidadora='PERSHING') # valor por defecto
#
df2 = df2.assign(ent_mediadora=None) # Valor por defecto
#
df2 = df2.assign(fec_comunicacion_be=None) # valores por defecto
df2 = df2.assign(
fec_liquidacion=df['fecha_liquidacion']) # valores por defecto
df2 = df2.assign(fec_operacion=df['fechasess']) # valores por defecto
df2 = df2.assign(fec_recompra=df['fechasess']) # valores por defecto
df2 = df2.assign(fec_valor=df['fechasess']) # valores por defecto
df2 = df2.assign(fec_vto=df['fecha_liquidacion'])
df2 = df2.assign(gasto_extra=0)
df2 = df2.assign(gastos_div='')
df2 = df2.assign(gestor_ordenante=2960) # valor por defecto
# suma o resta segun si es venta o compra
df2 = df2.assign(ind_nominal_srn=df['Codigo_compraventa'])
df2['ind_nominal_srn'] = df2['ind_nominal_srn'].str.strip()
#df2['operacion'] = df2['operacion'].str.strip()
df2.loc[df2['ind_nominal_srn'] == 'B', 'ind_nominal_srn'] = 'R'
df2.loc[df2['ind_nominal_srn'] == 'S', 'ind_nominal_srn'] = 'S'
df2 = df2.assign(ind_periodificar='NULL')
df2 = df2.assign(instrumento=df['Codigo_Ins'])
df2['instrumento'] = df2['instrumento'].str.strip()
df2 = df2.assign(liquido_div=df['Valorizacion']) # falta validar
df2 = df2.assign(mercado='RVE')
df2 = df2.assign(minusvalia=0)
df2 = df2.assign(minusvalia_div=0)
df2 = df2.assign(neto_div=df['Valorizacion'])
df2 = df2.assign(nominal_div=df['Cantidad']) # consultar
# venta o compra segun la tasa de compra, si existe es compra si no es venta
df2 = df2.assign(operacion=df['Codigo_compraventa'])
df2['operacion'] = df2['operacion'].str.strip()
df2.loc[df2['operacion'] == 'B', 'operacion'] = 'C'
df2.loc[df2['operacion'] == 'S', 'operacion'] = 'V'
df2 = df2.assign(otros_gastos_div='')
df2 = df2.assign(
previa_compromiso_plazo='D') # compromiso diario segun que
df2 = df2.assign(plusvalia=0)
df2 = df2.assign(plusvalia_div=0)
df2 = df2.assign(prima_div='NULL')
df2 = df2.assign(primario_secundario='S') # VALORES POR DEFECTO
df2 = df2.assign(regularizar_sn='S') # VALORES POR DEFECTO
df2 = df2.assign(repo_dia='N') # VALORES POR DEFECTO
df2 = df2.assign(tipo_interes=0)
df2 = df2.assign(titulos=df2['nominal_div'])
df2 = df2.assign(ventana_tratamiento='RVE')
df2 = df2.assign(ind_proceso='D')
df2 = df2.assign(enlace_sucursal='')
df2 = df2.assign(enlace_tipo_ap='')
df2 = df2.assign(enlace_plan_subplan='')
df2 = df2.assign(retencion_div=0)
# Ind_cambio_asegurado: si la fecha_operacion es distinta a fecha_liquidacion el campo debe ser “S”, si son iguales debe ser “N”
df2 = df2.assign(ind_cambio_asegurado=df2['nominal_div'])
df2 = df2.assign(ind_cambio_asegurado='N')
df2 = df2.assign(ind_depositario='')
df2 = df2.assign(simple_compuesto='')
df2 = df2.assign(tex_pago='')
df2 = df2.assign(tex_pagare_cod='NULL')
df2 = df2.assign(tex_pagares='NULL')
df2 = df2.assign(num_comunica_be='NULL')
df2 = df2.assign(iva='NULL')
df2 = df2.assign(iva_div=' NULL')
df2 = df2.assign(cuenta_ccc='')
df2 = df2.assign(ind_cobertura='NULL')
df2 = df2.assign(fecha_saldo='')
df2 = df2.assign(libre_n1='')
df2 = df2.assign(libre_n2='')
df2 = df2.assign(libre_x1='')
df2 = df2.assign(libre_x2='')
df2 = df2.assign(fecha_ejecucion='')
df2 = df2.assign(codigo_uti='')
df2 = df2.assign(comision_ecc_div='')
df2 = df2.assign(tir_mercado=0) # consultar
df2['cam_divisa'] = df2['cam_divisa'].astype(str)
df2['cotizacion'] = df2['cotizacion'].astype(str)
df2['efectivo_div'] = df2['efectivo_div'].astype(str)
df2['liquido_div'] = df2['liquido_div'].astype(str)
df2['neto_div'] = df2['neto_div'].astype(str)
df2['nominal_div'] = df2['nominal_div'].astype(str)
df2['titulos'] = df2['titulos'].astype(str)
df2['mercado'] = df2['mercado'].astype(str)
df2['codigo_secuencia'] = df2['codigo_secuencia'].astype(str)
df2['cam_divisa'] = df2['cam_divisa'].str.replace('.', ',')
df2['cotizacion'] = df2['cotizacion'].str.replace('.', ',')
df2['efectivo_div'] = df2['efectivo_div'].str.replace('.', ',')
df2['liquido_div'] = df2['liquido_div'].str.replace('.', ',')
df2['neto_div'] = df2['neto_div'].str.replace('.', ',')
df2['nominal_div'] = df2['nominal_div'].str.replace('.', ',')
df2['titulos'] = df2['titulos'].str.replace('.', ',')
df2['mercado'] = df2['mercado'].str.replace('.', ',')
df2['codigo_secuencia'] = df2['codigo_secuencia'].str.replace('.', ',')
df2 = df2.reindex(columns=l)
print(df2['operacion'])
return df2