def relatorio_risco_mercado():
import pandas as pd
import pymysql as db
import logging
import datetime
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from var.scripts.relatorio_encadeado.relatorio_risco_mercado.relatorio import relatorio
#Define Variáveis iniciais
dt_base = get_data_ultimo_dia_util_mes_anterior()
dt_base = dt_base[0] + '-' + dt_base[1] + '-' + dt_base[2]
dt_base = '2016-11-30'
logger = logging.getLogger(__name__)
cnpj_hdi = get_global_var("cnpj_hdi")
#Conecta DB
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
#Busca lista dos fundos de primeiro nivel na carteira da HDI
query = 'select * from projeto_inv.xml_header where cnpjcpf="' + cnpj_hdi + '" and dtposicao=' + '"' + dt_base + '";'
df = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
if len(df) == 0:
query = 'select * from projeto_inv.xml_header where cnpj="' + cnpj_hdi + '" and dtposicao=' + '"' + dt_base + '";'
df = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
df = df.sort(['cnpj', 'cnpjcpf', 'data_bd'], ascending=[True, True, False])
df = df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df = df.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
del df['index']
#Utiliza o header da carteira da HDI como chave para a query da lista
header_id_carteira_fundos = df.get_value(0, 'header_id').astype(str)
lista_query = 'SELECT cnpj from projeto_inv.lista_fundos where data_bd=(select max(data_bd) from projeto_inv.lista_fundos where header_id="' + header_id_carteira_fundos + '");'
lista_cnpj = pd.read_sql(lista_query, con=connection)
lista = lista_cnpj['cnpj'].tolist()
horario_bd = datetime.datetime.today()
for cnpj in lista:
relatorio(dt_base, cnpj, horario_bd)
def resumo_var():
import pandas as pd
import pymysql as db
import openpyxl
import logging
from openpyxl.styles import Font, Color, Border, Side, PatternFill, Alignment
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
global header_id_carteira
global header_id_carteira_fundos
global header_nome
global cnpj_fundo
global id_relatorio_qo
logger = logging.getLogger(__name__)
end = full_path_from_database("get_output_var")+'Relatorios/'
cnpj_hdi=get_global_var("cnpj_hdi")
dt_base = get_data_ultimo_dia_util_mes_anterior()
dt_base = dt_base[0]+'-'+dt_base[1]+'-'+dt_base[2]
#dt_base = '2016-11-30'
logger.info("Conectando no Banco de dados")
connection=db.connect('localhost',user='******',passwd='root', db='projeto_inv', use_unicode=True, charset="utf8")
logger.info("Conexão com DB executada com sucesso")
tabela_resumo_var = pd.DataFrame(columns=['cnpj','nome','pl','var_normal','var_estressado','perc_var_norm','perc_var_stress'])
tabela_resumo_credito = pd.DataFrame(columns=['cnpj','nome','pl','pl_cred','perc_esp','rtg_medio'])
#Gera lista dos CNPJs na carteira HDI
#Busca lista dos fundos de primeiro nivel na carteira da HDI
query='select * from projeto_inv.xml_header where cnpjcpf="' + cnpj_hdi +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(query, con=connection)
if len(df)==0:
query='select * from projeto_inv.xml_header where cnpj="' + cnpj_hdi +'" and dtposicao='+'"'+dt_base+'";'
df = pd.read_sql(query, con=connection)
df=df.sort(['cnpj', 'cnpjcpf','data_bd'], ascending=[True, True, False])
df=df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df=df.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
del df['index']
header_id_carteira_fundos=df.get_value(0,'header_id').astype(str) #Utiliza o header da carteira da HDI como chave para a query da lista
lista_query='SELECT cnpj from projeto_inv.lista_fundos where data_bd=(select max(data_bd) from projeto_inv.lista_fundos where header_id="'+header_id_carteira_fundos+'");'
lista_cnpj=pd.read_sql(lista_query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
lista=lista_cnpj['cnpj'].tolist()
lista_query_fidc='SELECT * from projeto_inv.lista_fidc where data_bd=(select max(data_bd) from projeto_inv.lista_fidc where header_id="'+header_id_carteira_fundos+'");'
lista_fidc=pd.read_sql(lista_query_fidc, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
lista_fidc=lista_fidc['cnpj'].tolist()
for cnpj in lista:
#Busca do id_relatorio_quaid419
query='select * from projeto_inv.xml_header where cnpjcpf="' + cnpj +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
if len(df) == 0:
query = 'select * from projeto_inv.xml_header where cnpj="' + cnpj +'" and dtposicao='+'"'+dt_base+'";'
df = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
df = df.sort(['cnpj', 'cnpjcpf','data_bd'], ascending=[True, True, False])
df = df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df = df.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
del df['index']
header_id_carteira=df.get_value(0,'header_id').astype(str)
header_nome=df.get_value(0,'nome')
cnpj_fundo=cnpj
#quadro de operaçoes
query='select a.* from projeto_inv.xml_quadro_operacoes a right join (select header_id, max(data_bd) as data_bd from projeto_inv.xml_quadro_operacoes where header_id='+header_id_carteira+' group by 1) b on a.header_id=b.header_id and a.data_bd=b.data_bd;'
qo=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
id_relatorio_qo=str(qo['id_relatorio_qo'][0])
if cnpj==cnpj_hdi:
id_relatorio_qo_hdi=id_relatorio_qo
# id_relatorio_qo = "50"
query='select a.* from projeto_inv.quaid_419 a right join (select id_relatorio_qo, tipo_relatorio, max(data_bd) as data_bd from projeto_inv.quaid_419 where id_relatorio_qo='+id_relatorio_qo+' and tipo_relatorio="G" group by 1,2) b on a.id_relatorio_qo=b.id_relatorio_qo and a.tipo_relatorio=b.tipo_relatorio and a.data_bd=b.data_bd;'
quaid_419=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
quaid_419=quaid_419[quaid_419.EMFMULTIPLOFATOR==0] #Se tem linha duplicada, mais de um fator de risco, pega apenas a referente ao indexador principal
id_quaid_419_df=quaid_419[['id_relatorio_quaid419']]
id_relatorio_quaid419=id_quaid_419_df.get_value(0,'id_relatorio_quaid419').astype(str)
#print(cnpj,id_relatorio_qo,id_relatorio_quaid419)
#Patrimônio líquido
#Informações de PL
pl_qo=qo[qo.produto!='Futuro']
pl_info=sum(pl_qo['mtm_info'])
#Risco de Mercado - VaR
query='select a.* from projeto_inv.var a right join (select id_relatorio_quaid419, tipo_var, vertice, tipo_alocacao, tipo_segmento, norm_stress, max(data_bd) as data_bd from projeto_inv.var where id_relatorio_quaid419='+str(id_relatorio_quaid419)+' and tipo_var="Total" and vertice="Total" and tipo_alocacao="Total" and tipo_segmento="Total" and norm_stress = "normal" group by 1,2,3,4,5,6) b on a.id_relatorio_quaid419=b.id_relatorio_quaid419 and a.tipo_var=b.tipo_var and a.tipo_alocacao=b.tipo_alocacao and a.tipo_segmento=b.tipo_segmento and a.norm_stress = b.norm_stress and a.data_bd=b.data_bd; '
resumo_var_normal=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
resumo_var_normal = resumo_var_normal[['id_relatorio_quaid419','var']].copy()
resumo_var_normal = resumo_var_normal.rename(columns={'var':'var_normal'})
query='select a.* from projeto_inv.var a right join (select id_relatorio_quaid419, tipo_var, vertice, tipo_alocacao, tipo_segmento, norm_stress, max(data_bd) as data_bd from projeto_inv.var where id_relatorio_quaid419='+str(id_relatorio_quaid419)+' and tipo_var="Total" and vertice="Total" and tipo_alocacao="Total" and tipo_segmento="Total" and norm_stress = "estressado" group by 1,2,3,4,5,6) b on a.id_relatorio_quaid419=b.id_relatorio_quaid419 and a.tipo_var=b.tipo_var and a.tipo_alocacao=b.tipo_alocacao and a.tipo_segmento=b.tipo_segmento and a.norm_stress = b.norm_stress and a.data_bd=b.data_bd; '
resumo_var_estressado=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
resumo_var_estressado = resumo_var_estressado[['id_relatorio_quaid419','var']].copy()
resumo_var_estressado = resumo_var_estressado.rename(columns={'var':'var_estressado'})
resumo_var = pd.merge(resumo_var_normal,resumo_var_estressado,left_on=['id_relatorio_quaid419'],right_on=['id_relatorio_quaid419'],how='left')
resumo_var['cnpj'] = cnpj
resumo_var['nome'] = header_nome
resumo_var['pl'] = pl_info
resumo_var['perc_var_norm'] = resumo_var['var_normal']/pl_info
resumo_var['perc_var_stress'] = resumo_var['var_estressado']/pl_info
del resumo_var['id_relatorio_quaid419']
#Risco de Crédito - Percentual de perda esperada e rating médio da carteira
query='select a.* from projeto_inv.resumo_credito a right join (select cnpj, max(data_bd) as data_bd from projeto_inv.resumo_credito where cnpj='+cnpj+' group by 1) b on a.cnpj = b.cnpj and a.data_bd=b.data_bd; '
resumo_credito=pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
#União das tabelas
tabela_resumo_var = tabela_resumo_var.append(resumo_var)
if len(resumo_credito)>0:
resumo_credito['pl'] = pl_info
tabela_resumo_credito = tabela_resumo_credito.append(resumo_credito)
#FIDCS e FIP
#Cria dataframe com a exposicao dos FIDCs (busca a informação no quadro consolidado)
tabela_fidc=pd.DataFrame()
for cnpj in lista_fidc:
quadro_fidc=pd.read_sql('select mtm_info, cnpjfundo_1nivel, fundo, isin from projeto_inv.xml_quadro_operacoes where data_bd=(select max(data_bd) from projeto_inv.xml_quadro_operacoes where header_id="'+header_id_carteira_fundos+'") and cnpjfundo_1nivel="'+cnpj+'" and header_id="'+header_id_carteira_fundos+'";', con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
tabela_fidc=tabela_fidc.append(quadro_fidc)
lista_isin=tabela_fidc['isin'].tolist()
#Extrai o valor do id do quadro 419 da HDI
id_quadro419=pd.read_sql('SELECT id_relatorio_quaid419 FROM projeto_inv.quaid_419 where data_bd=(select max(data_bd) from projeto_inv.quaid_419 where id_relatorio_qo="'+id_relatorio_qo_hdi+'" and tipo_relatorio="G") and id_relatorio_qo="'+id_relatorio_qo_hdi+'";', con=connection)
id_quadro419=id_quadro419.get_value(0,'id_relatorio_quaid419').astype(str)
#Extrai os valores de VaR de cada um dos ISINs presentes na lista_isin
var_fidc=pd.DataFrame()
for isin in lista_isin:
var_fidc_bd=pd.read_sql('SELECT var, vertice from projeto_inv.var where data_bd=(select max(data_bd) from projeto_inv.var where id_relatorio_quaid419="'+id_quadro419+'" and norm_stress="normal") and vertice="'+isin+'" and id_relatorio_quaid419="'+id_quadro419+'" and tipo_var="Marginal" and tipo_alocacao="Total" and tipo_segmento="Total" and norm_stress="normal";', con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
var_fidc=var_fidc.append(var_fidc_bd)
#Fecha conexão
connection.close()
var_fidc=pd.merge(var_fidc, tabela_fidc, left_on=['vertice'], right_on=['isin'], how='inner')
var_fidc['var']=var_fidc['var']*var_fidc['mtm_info']
var_fidc['VaR Estressado (R$)']=var_fidc['var']*1.14566452
var_fidc['VaR (%PL)']=var_fidc['var']/var_fidc['mtm_info']
var_fidc['VaR Estressado (%PL)']=var_fidc['VaR Estressado (R$)']/var_fidc['mtm_info']
var_fidc=var_fidc.rename(columns={'var':'VaR (R$)', 'mtm_info':'Patrimônio Líquido (R$)', 'fundo':'Nome do Fundo', 'cnpjfundo_1nivel':'CNPJ'})
var_fidc=var_fidc[['CNPJ', 'Nome do Fundo', 'Patrimônio Líquido (R$)', 'VaR (R$)', 'VaR Estressado (R$)', 'VaR (%PL)', 'VaR Estressado (%PL)']].copy()
#Geração do relatorio
writer = pd.ExcelWriter(end+'relatorio_risco_resumo_'+dt_base+'.xlsx', engine='xlsxwriter')
workbook = writer.book
numero_float = workbook.add_format({'num_format': '#,##0', 'bold': False})
percent = workbook.add_format({'num_format': '0.0000%', 'bold': False})
percent1 = workbook.add_format({'num_format': '0%', 'bold': False})
#VaR
var = pd.DataFrame(columns=['CNPJ','Nome do Fundo','Patromônio Líquido (R$)','VaR (R$)','VaR Estressado (R$)','VaR (%PL)','VaR Estressado (%PL)'])
var['CNPJ'] = tabela_resumo_var['cnpj']
var['Nome do Fundo'] = tabela_resumo_var['nome']
var['Patromônio Líquido (R$)'] = tabela_resumo_var['pl']
var['VaR (R$)'] = tabela_resumo_var['var_normal']
var['VaR Estressado (R$)'] = tabela_resumo_var['var_estressado']
var['VaR (%PL)'] = tabela_resumo_var['var_normal']/tabela_resumo_var['pl']
var['VaR Estressado (%PL)'] = tabela_resumo_var['var_estressado']/tabela_resumo_var['pl']
tabela_pl = tabela_resumo_var[['cnpj','pl']].copy()
var.to_excel(writer,index=False, sheet_name='Resumo VaR', startrow =3, startcol=1)
logger.info("Arquivo salvo com sucesso")
tamanho=len(var)
var_fidc.to_excel(writer,index=False, sheet_name='Resumo VaR', startrow =(7+tamanho), startcol=1)
logger.info("Arquivo salvo com sucesso")
worksheet = writer.sheets['Resumo VaR']
worksheet.set_column('D:D', 12, numero_float)
worksheet.set_column('E:E', 12, numero_float)
worksheet.set_column('F:F', 12, numero_float)
worksheet.set_column('G:G', 12, percent)
worksheet.set_column('H:H', 12, percent)
#Credito
credito = pd.DataFrame(columns=['CNPJ','Nome do Fundo','Patromônio Líquido Crédito (R$)','Perda Esperada (R$)','Perda Esperada (%PL - Crédito)','Rating Médio do Fundo'])
credito['CNPJ'] = tabela_resumo_credito['cnpj']
credito['Nome do Fundo'] = tabela_resumo_credito['nome']
credito['Patromônio Líquido Crédito (R$)'] = tabela_resumo_credito['pl_cred']
credito['Perda Esperada (R$)'] = tabela_resumo_credito['pl']*tabela_resumo_credito['perc_esp']
credito['Perda Esperada (%PL - Crédito)'] = tabela_resumo_credito['pl']*tabela_resumo_credito['perc_esp']/tabela_resumo_credito['pl_cred']
credito['Rating Médio do Fundo'] = tabela_resumo_credito['rtg_medio']
credito.to_excel(writer,index=False, sheet_name='Resumo Crédito', startrow =3, startcol=1)
logger.info("Arquivo salvo com sucesso")
worksheet = writer.sheets['Resumo Crédito']
worksheet.set_column('D5:E30', 12, numero_float)
worksheet.set_column('F5:F30', 12, percent1)
writer.save()
#Formatação resumo
wb = openpyxl.load_workbook(end+'relatorio_risco_resumo_'+dt_base+'.xlsx')
#####FORMATOS
#Fonte
fontObj1=Font(name='Calibri', bold=True, size =24,color='404040')
fontObj2=Font(name='Calibri', bold=False, size =11,color='404040')
#Borda
borderObj1=Border(bottom=Side(border_style='double'),top=Side(border_style='thin'))
borderObj2=Border()
#Cor
colorObj1=PatternFill(patternType='solid', fgColor=Color('FFE600'))
#Alinhamento
alinObj1=Alignment(vertical='center',horizontal='center')
alinObj2=Alignment(vertical='center',horizontal='left')
alinObj3=Alignment(vertical='center',horizontal='right')
sheet1=wb.get_sheet_by_name('Resumo VaR')
#Retira as gridlines
sheet1.sheet_view.showGridLines = False
#Formatação tamanho das linhas
sheet1.row_dimensions[1].height = 90
#Formata cor da fonte de todas as células
for row in sheet1.range('B2:H50'):
for cell in row:
cell.font=fontObj2
#Formata o título
sheet1.merge_cells('B2:H2')
sheet1['B2']='Resumo VaR'
sheet1['B2'].font=fontObj1
sheet1['B2'].alignment=alinObj2
sheet1.merge_cells('B'+str(6+tamanho)+':H'+str(6+tamanho))#Tabela FIDC
sheet1['B'+str(6+tamanho)]='Resumo VaR FIDCs'
sheet1['B'+str(6+tamanho)].font=fontObj1
sheet1['B'+str(6+tamanho)].alignment=alinObj2
for row in sheet1.range('B2:H2'):
for cell in row:
cell.border=borderObj1
for row in sheet1.range('B'+str(6+tamanho)+':H'+str(6+tamanho)):#Tabela FIDC
for cell in row:
cell.border=borderObj1
#Formata os headers da tabela
for row in sheet1.range('B4:H4'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
for row in sheet1.range('B'+str(8+tamanho)+':H'+str(8+tamanho)):#Tabela FIDC
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formatação tamanho das colunas
sheet1.column_dimensions['A'].width = 2
sheet1.column_dimensions['B'].width = 20
sheet1.column_dimensions['C'].width = 70
sheet1.column_dimensions['D'].width = 30
sheet1.column_dimensions['E'].width = 20
sheet1.column_dimensions['F'].width = 30
sheet1.column_dimensions['G'].width = 25
sheet1.column_dimensions['H'].width = 25
##CRÉDTIO
sheet1=wb.get_sheet_by_name('Resumo Crédito')
#Retira as gridlines
sheet1.sheet_view.showGridLines = False
#Formatação tamanho das linhas
sheet1.row_dimensions[1].height = 90
#Formata cor da fonte de todas as células
for row in sheet1.range('B2:G30'):
for cell in row:
cell.font=fontObj2
#Formata o título
sheet1.merge_cells('B2:G2')
sheet1['B2']='Resumo Crédito'
sheet1['B2'].font=fontObj1
sheet1['B2'].alignment=alinObj2
for row in sheet1.range('B2:G2'):
for cell in row:
cell.border=borderObj1
#Formata os headers da tabela
for row in sheet1.range('B4:G4'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formatação tamanho das colunas
sheet1.column_dimensions['A'].width = 2
sheet1.column_dimensions['B'].width = 20
sheet1.column_dimensions['C'].width = 70
sheet1.column_dimensions['D'].width = 30
sheet1.column_dimensions['E'].width = 20
sheet1.column_dimensions['F'].width = 30
sheet1.column_dimensions['G'].width = 25
wb.save(end+dt_base+' relatorio_resumo.xlsx')
def quadro90(data_relat, cnpj):
import logging
import pandas as pd
import pymysql as db
import numpy
from quadro90.scripts.quadro90_228.quadro_oper import quadro_oper
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from datetime import date
#Define variaveis
logger = logging.getLogger(__name__)
limite_fgc = int(get_global_var("limite_fgc"))
#Chama quadro de operações
qo = quadro_oper(data_relat, cnpj)
ano = data_relat[0:4]
mes = data_relat[5:7]
dia = data_relat[8:10]
dt_relat = date(int(ano), int(mes), int(dia))
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
#Load Header ID
curr_querry='select distinct a.cnpj, a.header_id, a.dtposicao, a.data_bd from projeto_inv.xml_header_org a right join (select cnpj, dtposicao,max(data_bd) as data_bd from projeto_inv.xml_header_org where dtposicao="'+ data_relat + '" group by 1,2) b on a.cnpj=b.cnpj and a.data_bd=b.data_bd;'
df = pd.read_sql(curr_querry, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
if len(df) == 0:
curr_querry='select distinct a.cnpjcpf, a.header_id, a.dtposicao, a.data_bd from projeto_inv.xml_header_org a right join (select cnpjcpf, dtposicao,max(data_bd) as data_bd from projeto_inv.xml_header_org where dtposicao="'+data_relat+'" group by 1,2) b on a.cnpjcpf=b.cnpjcpf and a.data_bd=b.data_bd;'
df = pd.read_sql(curr_querry, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
#Load Numeraca
numeraca_path = full_path_from_database('excels')
numeraca_path = numeraca_path+'numeraca.xlsx'
numeraca = pd.read_excel(numeraca_path, sheetname=0)
logger.info("Leitura do arquivo executada com sucesso")
logger.info("Tratando dados")
#Define Instituição Financeira
numeraca['IF'] = 0
siglas_IF = numeraca[numeraca['Sigla'].str.contains(r'DPGE\s|CDB\s|CCB\s|LH\s|LCI\s|LCA\s')]
descr_IF = numeraca[numeraca['Descrição'].str.contains(r'LETRA FINANCEIRA\s|LETRA DE CAMBIO\s')]
numeraca.set_value(siglas_IF.index,'IF',1)
numeraca.set_value(descr_IF.index,'IF',1)
#Corrige Tipo de Ativo com 0 no final:
numeraca['Tipo de Ativo']=numeraca['Tipo de Ativo'].str.lstrip(' ').str.rstrip(' ')
numeraca.set_value(numeraca[numeraca['Tipo de Ativo'].str.len()==1].index,'Tipo de Ativo',numeraca[['Tipo de Ativo']].astype(str)+'00')
numeraca.set_value(numeraca[numeraca['Tipo de Ativo'].str.len()==2].index,'Tipo de Ativo',numeraca[['Tipo de Ativo']].astype(str)+'0')
#Corrige Categoria
numeraca['Categoria']=numeraca['Categoria'].str.lstrip(' ').str.rstrip(' ')
#Define tipo de ativo no quadro:
qo['tipo_ativo'] = qo['isin'].str[6:9]
#Define categoria no quadro:
qo['id_q90'] = 0
#Delta 3 meses:
qo['dif'] = dt_relat - qo['dt_vencto']
# linha abaixo necessária para PyCharm, pois ele não reconhece a referência timedelta64.
# noinspection PyUnresolvedReferences
qo['dif'] = qo['dif']/(numpy.timedelta64(1, 'D'))
qo['dif'] = abs(qo['dif'])
#Classifica Produtos:
qo['cat_produto']=qo['produto']
qo['cat_produto']=numpy.where(qo.produto.str.contains('despesa'), 'despesa', qo['cat_produto'])
qo['cat_produto']=numpy.where(qo.produto.str.contains('Termo'), 'termo', qo['cat_produto'])
qo['cat_produto']=numpy.where(qo.produto.str.contains('Opções'), 'opções', qo['cat_produto'])
#Define Tesouraria
#Fora tesouraria:
qo1=qo[((qo['isin'].notnull())|(qo['produto']=='valores a receber'))&(qo['produto']!='ajuste de futuro')]
#Para tesouraria:
qo2=qo[((qo['isin'].isnull())&(qo['produto']!='valores a receber'))|(qo['produto']=='ajuste de futuro')]
##Cruzamento
#03. Aplicações em mercado Aberto
df_03=qo1[qo1['produto'].str.contains(r'compromissada - debênture|compromissada: titulo público')]
qo.set_value(df_03.index,'id_q90',3)
# 06. Tít priv de RF, com prazo de venc até 3 meses, emitidos por instituição financ
left=qo1[['mtm_info','tipo_ativo','dt_vencto']][(qo1['dif']<=90) & (qo1['produto']=='título privado') & (qo1['tipo_ativo']!='DP0')]
right=numeraca[['Tipo de Ativo','IF']][(numeraca['IF']==1)&(numeraca['Categoria']=='Renda Fixa')]
left.reset_index(inplace=True)
right.reset_index(inplace=True)
df_06=pd.merge(left,right,left_on=['tipo_ativo'],right_on=['Tipo de Ativo'],how='inner')
qo.set_value(df_06.set_index('index_x').index,'id_q90',6)
#Adicional para outros LF e CDBs:
df_06_ad=qo1[['mtm_info','tipo_ativo','dt_vencto']][((qo1['dif']<=90) & (qo1['produto']=='título privado')&(qo1['tipo_ativo']!='DP0'))&((qo1['ativo'].str.contains('CDB'))|(qo1['ativo'].str.contains('LF'))|(qo1['tipo_ativo'].str.contains(r'C\d\d')))]
qo.set_value(df_06_ad.index,'id_q90',6)
#08. Valores aplicados em DPGE garantidos pelo FGC ou c/ prazo de venc. até 3 meses (CMPID 07402):
left=qo1[['mtm_info','tipo_ativo']][(qo1['produto']=='título privado') & ((qo1['tipo_ativo']=='DP0')|(qo1['ativo'].str.contains(r'DPGE')))]
right=numeraca[['Tipo de Ativo']][numeraca['IF']==1]
left.reset_index(inplace=True)
right.reset_index(inplace=True)
df_08=pd.merge(left,right,left_on=['tipo_ativo'],right_on=['Tipo de Ativo'],how='inner')
qo.set_value(df_08.set_index('index_x').index,'id_q90',8)
#vefifica limite
if df_08['mtm_info'].sum() <= limite_fgc:
val_a8=df_08['mtm_info'].sum()
else:
val_a8='ACIMA DO LIMITE'
# 12. Tít priv de RF, com prazo de venc > 3 meses, emitidos por instituição financ
left=qo1[['mtm_info','tipo_ativo','dt_vencto']][(qo1['dif']>90) & ((qo1['produto']=='título privado')|((qo1['ativo'].str.contains('CDB'))|(qo1['ativo'].str.contains('LF')))) & (qo1['tipo_ativo']!='DP0')]
right=numeraca[['Tipo de Ativo']][(numeraca['IF']==1)&(numeraca['Categoria']=='Renda Fixa')]
left.reset_index(inplace=True)
right.reset_index(inplace=True)
df_12=pd.merge(left,right,left_on=['tipo_ativo'],right_on=['Tipo de Ativo'],how='inner')
qo.set_value(df_12.set_index('index_x').index,'id_q90',12)
#Adicional para outros LF e CDBs:
df_12_ad=qo1[['mtm_info','tipo_ativo','dt_vencto']][((qo1['dif']>90) & (qo1['produto']=='título privado')&(qo1['tipo_ativo']!='DP0'))&((qo1['ativo'].str.contains('CDB'))|(qo1['ativo'].str.contains('LF'))|(qo1['tipo_ativo'].str.contains(r'C\d\d')))]
qo.set_value(df_12_ad.index,'id_q90',12)
#24. Títulos privados de RF emitidos por instituição não financeira (CMPID 07418): + DEBENTURES
left=qo1[['mtm_info','tipo_ativo']][(qo1['produto']=='título privado')|(qo1['ativo'].str.contains('CDB'))|(qo1['ativo'].str.contains('LF'))|(qo1['produto']=='debênture')]
right=numeraca[['Tipo de Ativo','Categoria']][(numeraca['IF']==0)&(numeraca['Categoria']=='Renda Fixa')]
left.reset_index(inplace=True)
right.reset_index(inplace=True)
df_24=pd.merge(left,right,left_on=['tipo_ativo'],right_on=['Tipo de Ativo'],how='inner')
qo.set_value(df_24.set_index('index_x').index,'id_q90',24)
#26. Títulos de RV, não classificados como ações, derivativos e ouro (CMPID 07420): + TERMO
df_26=qo1[['mtm_info','tipo_ativo']][(qo1['produto']=='Termo_RF_IAP')|(qo1['produto']=='Termo_RV_BBSE3')|(qo1['produto']=='Termo_RV_CIEL3')|(qo1['produto']=='Termo_RV_CIEL3')|(qo1['produto']=='Termo_RV_DAGB33')]
qo.set_value(df_26.index,'id_q90',26)
#37. Títulos à receber
df_37=qo1[['mtm_info','produto']][(qo1['produto']=='valores a receber')]
qo.set_value(df_37.index,'id_q90',37)
#44. Quotas de Fundos de Investimento
df_44=qo1[['mtm_info','produto']][(qo1['produto']=='fundo')]
qo.set_value(df_44.index,'id_q90',44)
##LINHAS ADICIONAIS - tesouraria, titulos publicos federais, acoes, opcoes, swaps, futuro
#missing isin (exceto valores a receber), caixa, ajuste de futuro, despesas: vai para tesouraria
#48. Tesouraria
df_48=qo2
qo.set_value(df_48.index,'id_q90',48)
#49. Títulos públicos federais
df_49=df_49=qo1[['mtm_info','produto','tipo_ativo','ativo']][(qo1['produto']=='titulo público')]
qo.set_value(df_49.index,'id_q90',49)
#50. Ações
df_50=df_50=qo1[['mtm_info','produto']][(qo1['produto']=='ações')]
qo.set_value(df_50.index,'id_q90',50)
#51. Opção
df_51=df_51=qo1[['mtm_info','produto']][(qo1['produto']=='opções')]
qo.set_value(df_51.index,'id_q90',51)
#52. Swaps
df_52=df_52=qo1[['mtm_info','produto']][(qo1['produto']=='swap')]
qo.set_value(df_52.index,'id_q90',52)
#53. Futuro
df_53=df_53=qo1[['mtm_info','produto']][(qo1['produto']=='Futuro')]
qo.set_value(df_53.index,'id_q90',53)
#ERROS:
qo_err=qo[qo['id_q90']==0]
#if qo_err['isin'].str.len() < 9:
#Para homologação:
qo_homol=qo[(qo['cnpjfundo_1nivel'].notnull())&(qo['fundo'].str.contains(r'^((?!fidic).)*$'))&(qo['fundo'].str.contains(r'^((?!direitos).)*$'))]
#Salva resultados
output_path=full_path_from_database('get_output_quadro90')
writer = pd.ExcelWriter(output_path+'resultado_'+cnpj+"-"+data_relat+'_.xlsx', engine='xlsxwriter')
qo.to_excel(writer, sheet_name='Resultado',index=False)
logger.info('Arquivos salvos com sucesso')
#'''SE O ID_Q90 == 0, preste atenção, e reclassifique manualmente'''
desc = {'id_q90': [3,6,8,12,24,26,37,44,48,49,50,51,52,53,0],
'descricao': ['03. Aplicações no mercado aberto',
'06. Tit. Priv. de RF, Instituição Financeira até 3 meses',
'08. DPGE',
'12. Tit. Priv. de RF, Instituição Financeira > 3 meses',
'24. Tit. Priv. de RF, Não Instituição Financeira',
'26. Títulos de RV. não ações (inclui Termo)',
'37. Títulos e créditos a receber',
'44. Quotas de Fundos de Investimento',
'Tesouraria',
'Títulos públicos federais',
'Ações',
'Opções',
'Swap',
'Futuro',
'Outros'],
'fator_ponderacao': [0.2,
0.2,
0.2,
0.5,
1,
1,
1,
1,
0,
0,
0,
0,
0,
0,
0]}
descricao=pd.DataFrame(desc)
qo_res=pd.merge(qo, descricao, left_on='id_q90', right_on='id_q90', how='left')
qo_res=qo_res[['id_q90', 'descricao', 'fator_ponderacao','quantidade', 'mtm_info']]
qo_resumo=qo_res.groupby(['id_q90', 'descricao', 'fator_ponderacao'], as_index=False).sum()
qo_resumo=qo_resumo.reindex(columns=['id_q90', 'descricao', 'mtm_info','fator_ponderacao','quantidade'])
qo_resumo.to_excel(writer, sheet_name='Resumo', index=False, header=['Id', 'Descrição da Conta', 'Exposição', 'Fator de Ponderação', '#Ativos'])
logger.info('Arquivos salvos com sucesso')
#Advanced Formatting
wb = writer.book
ws = writer.sheets['Resumo']
#Create Formats:
currency = wb.add_format({'num_format': '$#,##0'})
ws.set_column('B2:C16', 11,currency)
percent = wb.add_format({'num_format': '0.00%'})
ws.set_column('D2:D16', 11, percent)
writer.save()
# - VALIDAÇÃO
writer = pd.ExcelWriter(output_path+"validação_q90_"+cnpj+"-"+data_relat+'_.xlsx', engine='xlsxwriter')
qo = qo.sort(['id_q90'],ascending=True)
lista = qo['id_q90'].unique()
for i in lista:
qo[qo['id_q90']==i].to_excel(writer,'id_q90_'+str(i))
logger.info('Arquivos salvos com sucesso')
writer.save()
def get_anbima_carteiras(ano, mes, dia):
import pandas as pd
import pymysql as db
import datetime
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
pagina_anbima_carteira_xml = get_global_var("pagina_anbima_carteira_xml")
#Leitura Arquivos
ima_geral = pd.read_excel(pagina_anbima_carteira_xml, sheetname="Geral", header=2)
ima_irfm = pd.read_excel(pagina_anbima_carteira_xml, sheetname="IRF-M", header=2)
ima_imab = pd.read_excel(pagina_anbima_carteira_xml, sheetname="IMA-B", header=2)
ima_imac = pd.read_excel(pagina_anbima_carteira_xml, sheetname="IMA-C", header=2)
ima_imas = pd.read_excel(pagina_anbima_carteira_xml, sheetname="IMA-S", header=2)
logger.info("Leitura da página executada com sucesso")
logger.info("Tratando dados")
###TRATAMENTO DO DATAFRAME IMA GERAL
ima_geral = ima_geral.drop([3,5,7,11,13,14,16,18,19,20,21])
for linha in range(len(ima_geral["Índice"])):
ima_geral["Índice"].iloc[linha] = str(ima_geral["Índice"].iloc[linha])
ima_geral["Unnamed: 1"].iloc[linha] = str(ima_geral["Unnamed: 1"].iloc[linha])
if ima_geral["Índice"].iloc[linha] == 'nan':
ima_geral["Índice"].iloc[linha] = ima_geral["Índice"].iloc[linha-1]
ima_geral = ima_geral.drop(4)
for linha in range(len(ima_geral["Índice"])):
ima_geral["Índice"].iloc[linha] = str(ima_geral["Índice"].iloc[linha])+" "+str(ima_geral["Unnamed: 1"].iloc[linha])
del ima_geral["Unnamed: 1"]
ima_geral.columns = ['indice',
'data_ref',
'numeroindice',
'variacaodiariaperc',
'variacaomensalperc',
'variacaoanualperc',
'variacaoultimos12mesesperc',
'variacaoultimos24mesesperc',
'duration',
'valordemercado',
'pesoperc',
'numerodeoperacoes',
'quantidadenegociada',
'valornegociado']
horario_bd = datetime.datetime.now()
ima_geral["data_bd"] = horario_bd
ima_geral = ima_geral.replace({'--': None}, regex=True)
#######################################################################################
#TRATAMENTO IMA-IRFM
ima_irfm = ima_irfm.drop([0,1,2,3,4])
del ima_irfm["Indice"]
ima_irfm = ima_irfm.where((pd.notnull(ima_irfm)), None)
ima_irfm.columns=[
'indice',
'titulo',
'codigoselic',
'isin',
'dtvencimento',
'taxaindicativa',
'pu',
'pudejuros',
'quantidade',
'quantidadeteorica',
'duracao',
'valordemercado',
'peso',
'numerodeoperacoes',
'quantidadenegociada',
'valornegociado',
'prazo'
]
horario_bd = datetime.datetime.now()
ima_irfm["data_bd"] = horario_bd
ima_irfm["data_ref"] = ano+mes+dia
ima_irfm = ima_irfm.replace({'--': None}, regex=True)
logger.info("Tratamento IMA-IRFM - OK")
##################################################################################################
##### Tratamento IMA-IMAB
#################################################################################################
ima_imab = ima_imab.drop([0,1,2,3,4])
del ima_imab["Indice"]
ima_imab = ima_imab.where((pd.notnull(ima_imab)), None)
ima_imab.columns=[
'indice',
'titulo',
'codigoselic',
'isin',
'dtvencimento',
'taxaindicativa',
'pu',
'pudejuros',
'quantidade',
'quantidadeteorica',
'duracao',
'valordemercado',
'peso',
'numerodeoperacoes',
'quantidadenegociada',
'valornegociado',
'prazo']
horario_bd = datetime.datetime.now()
ima_imab["data_bd"] = horario_bd
ima_imab["data_ref"] = ano+mes+dia
ima_irfm = ima_irfm.replace({'--': None}, regex=True)
logger.info("Tratamento IMA-IMAB - OK")
##################################################################################################
##### Tratamento IMA-IMAC
#################################################################################################
ima_imac = ima_imac.drop([0,1,2,3,4])
del ima_imac["Indice"]
ima_imac = ima_imac.where((pd.notnull(ima_imac)), None)
ima_imac.columns = [
'indice',
'titulo',
'codigoselic',
'isin',
'dtvencimento',
'taxaindicativa',
'pu',
'pudejuros',
'quantidade',
'quantidadeteorica',
'duracao',
'valordemercado',
'peso',
'numerodeoperacoes',
'quantidadenegociada',
'valornegociado',
'prazo']
horario_bd = datetime.datetime.now()
ima_imac["data_bd"] = horario_bd
ima_imac["data_ref"] = ano+mes+dia
ima_imac = ima_imac.replace({'--': None}, regex=True)
logger.info("Tratamento IMA-IMAC - OK")
##################################################################################################
##### Tratamento IMA-IMAS
#################################################################################################
ima_imas = ima_imas.drop([0,1,2])
del ima_imas["Indice"]
ima_imas = ima_imas.where((pd.notnull(ima_imas)), None)
ima_imas.columns = [
'indice',
'titulo',
'codigoselic',
'isin',
'dtvencimento',
'taxaindicativa',
'pu',
'pudejuros',
'quantidade',
'quantidadeteorica',
'duracao',
'valordemercado',
'peso',
'numerodeoperacoes',
'quantidadenegociada',
'valornegociado',
'prazo']
horario_bd = datetime.datetime.now()
ima_imas["data_bd"] = horario_bd
ima_imas["data_ref"] = ano + mes + dia
ima_imas = ima_imas.replace({'--': None}, regex=True)
logger.info("Tratamento IMA-IMAS - OK")
#############################################################
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
pd.io.sql.to_sql(ima_geral, name='xml_ima_geral', con=connection,if_exists="append", flavor='mysql', index=0)
logger.info("Tabela ima_geral salva no DB com sucesso")
pd.io.sql.to_sql(ima_irfm, name='xml_ima_irfm', con=connection,if_exists="append", flavor='mysql', index=0)
logger.info("Tabela ima_irfm salva no DB com sucesso")
pd.io.sql.to_sql(ima_imab, name='xml_ima_imab', con=connection,if_exists="append", flavor='mysql', index=0)
logger.info("Tabela ima_imab salva no DB com sucesso")
pd.io.sql.to_sql(ima_imac, name='xml_ima_imac', con=connection,if_exists="append", flavor='mysql', index=0)
logger.info("Tabela ima_imac salva no DB com sucesso")
pd.io.sql.to_sql(ima_imas, name='xml_ima_imas', con=connection,if_exists="append", flavor='mysql', index=0)
logger.info("Tabela ima_imas salva no DB com sucesso")
logger.info("Todos os dados foram salvos no DB com sucesso")
# Fecha conexão com o banco de dados
connection.close()
def dashboard(request):
# Variável que condição de where par query relativo ao tipo de atualização no banco
report_id = 4
# Variável de controle para loading da página
control_status = 0
# Pega o tipo de relatório do banco
tipo_relatorio = " > " + ReportTypes.objects.get(id=report_id).nome_report
if "fluxo_titpublicos" in request.POST:
start_time = datetime.datetime.now()
# Atualiza Fluxo de Títulos Públicos
fluxo_titpublico()
print("Fluxo de Títulos Públicos calculado com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=23).save()
control_status = 1
if "mtm_titpublicos" in request.POST:
start_time = datetime.datetime.now()
# Atualiza o fluxo Market to Market dos títulos públicos
mtm_titpublico()
print("Market to Market de Títulos Públicos calculado com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=24).save()
control_status = 1
if 'anbima_debentures_ajustes_cadastro' in request.POST:
start_time = datetime.datetime.now()
# Atualiza
anbima_debentures_ajustes_cadastro()
print("Atualização dos cadastros da Anbima com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=25).save()
control_status = 1
if 'bmf_numeraca_ajustes_cadastro' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os cadastros do BMF Numeraca
bmf_numeraca_ajustes_cadastro()
print(
"Atualização dos cadastros do BMF Numeraca efetuado com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=26).save()
control_status = 1
if 'fluxo_debentures' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Debentures
fluxo_debentures()
print("Atualização dos Fluxos de Debentures com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=27).save()
control_status = 1
if 'fluxo_titprivado' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
fluxo_titprivado()
print("Atualização dos Fluxos de Títulos Privados com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=28).save()
control_status = 1
if 'interpolacao_curvas_bmf' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
interpolacao_curvas_bmf()
print("Atualização dos interpolacao_curvas_bmf com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=29).save()
control_status = 1
if 'mtm_titulo_debenture' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
mtm_titulo_debenture()
print("Atualização dos mtm_titulo_debenture com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=30).save()
control_status = 1
if 'mtm_curva_titiprivado' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
mtm_curva_titprivado()
print("Atualização dos mtm_curva_titiprivado com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=31).save()
control_status = 1
if 'mtm_curva_debenture' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
mtm_curva_debenture()
print("Atualização dos mtm_curva_debenture com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=32).save()
control_status = 1
if 'finalizacao_fidc' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
finalizacao_fidc()
print("Atualização dos finalizacao_fidc com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=33).save()
control_status = 1
if 'tabelas_xml_final' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
tabelas_xml_final()
print("Atualização dos tabelas_xml_final com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=34).save()
control_status = 1
if 'titpublico_final_pv_ettj' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
titpublico_final_pv_ettj()
print("Atualização dos titpublico_final_pv_ettj com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=35).save()
control_status = 1
if 'xml_quadro_operacoes_hdi' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
xml_quadro_operacoes(get_global_var("cnpj_hdi"))
print("Atualização dos xml_quadro_operacoes_hdi com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=36).save()
control_status = 1
if 'xml_quadro_operacoes_gerling' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
xml_quadro_operacoes(get_global_var("cnpj_gerling"))
print("Atualização dos xml_quadro_operacoes_gerling com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=37).save()
control_status = 1
if 'quadro_operacoes_hdi' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
quadro_operacoes('hdi')
print("Atualização dos quadro_operacoes_hdi com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=38).save()
control_status = 1
if 'quadro_operacoes_gerling' in request.POST:
start_time = datetime.datetime.now()
# Atualiza os Fluxos de Títulos Privados
quadro_operacoes('gerling')
print("Atualização dos quadro_operacoes_hdi com sucesso!")
end_time = datetime.datetime.now()
ExecutionLog(start_time=start_time, end_time=end_time,
execution_id=39).save()
control_status = 1
return render(
request, 'quadro419/dashboard.html', {
'ExecutionDashboard':
ExecutionDashboard.objects.raw(
'SELECT exec_dash.*, exec_log.end_time, round(exec_log.end_time - exec_log.start_time) AS tempo_execucao FROM generator_executiondashboard AS exec_dash LEFT JOIN ( SELECT MAX(id) AS id, MAX(start_time) AS start_time, MAX(end_time) AS end_time, MAX(execution_id) AS execution_id FROM generator_executionlog GROUP BY execution_id ) AS exec_log ON exec_dash.id = exec_log.execution_id WHERE report_type_id = '
+ str(report_id)),
'control_status':
control_status,
'tipo_relatorio':
tipo_relatorio
})
def processo_quaid_expo_var():
import pymysql as db
import pandas as pd
import datetime
import logging
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from var.scripts.relatorio_encadeado.quaid419.quaid419 import quaid419
from var.scripts.relatorio_encadeado.exposicoes.exposicoes_conso import exposicoes_conso
from var.scripts.relatorio_encadeado.exposicoes.exposicoes_sep import exposicoes_sep
from var.scripts.relatorio_encadeado.var_gerencial.var_gerencial import var_gerencial
#Definitions:
logger = logging.getLogger(__name__)
dtbase = get_data_ultimo_dia_util_mes_anterior()
dtbase = dtbase[0] + '-' + dtbase[1] + '-' + dtbase[2]
#dtbase = '2016-11-30' #Caso seja necessário forçar uma data
horario_inicio= datetime.datetime.now()
#Parametros gerados pelo arquivo 17-xml_quadro_operacoes_nao_org.py ao final da função parametrização
inicio_hdi = int(get_global_var("inicio_hdi_quaid_expo_var"))
fim_hdi = int(get_global_var("fim_hdi_quaid_expo_var"))
inicio_gerling = int(fim_hdi)+1
fim_gerling = int(get_global_var("fim_gerling_quaid_expo_var"))
coent_hdi = get_global_var("coent_hdi")
coent_gerling = get_global_var("coent_gerling")
#HDI
horario_processo = datetime.datetime.today()
for i in range(inicio_hdi, fim_hdi+1):
quaid419(i, dtbase, coent_hdi, "G", horario_processo)
quaid419(i, dtbase, coent_hdi, "R", horario_processo)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv', use_unicode=True, charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'SELECT * FROM count_quadros where ENTCODIGO = "'+coent_hdi+'" and tipo_relatorio = "G" and data_bd = (SELECT max(data_bd) from count_quadros where ENTCODIGO = "'+coent_hdi+'" and tipo_relatorio = "G" )'
lista = pd.read_sql(query, connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
for j in lista['id_relatorio_quaid419']:
logger.info("Executando Relatório para HDI - ID: "+j)
print("Executando Relatório para HDI - ID: " + j)
exposicoes_conso(j)
exposicoes_sep(j)
var_gerencial(j, "normal", dtbase)
var_gerencial(j, "estressado", dtbase)
#Gerling
horario_processo = datetime.datetime.today()
for i in range(inicio_gerling, fim_gerling+1):
quaid419(i, dtbase, coent_gerling, "G", horario_processo)
quaid419(i, dtbase, coent_gerling, "R", horario_processo)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',user='******',passwd='root', db='projeto_inv', use_unicode=True, charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'SELECT * FROM count_quadros where ENTCODIGO = "'+coent_gerling+'" and tipo_relatorio = "G" and data_bd = (SELECT max(data_bd) from count_quadros where ENTCODIGO = "'+coent_gerling+'" and tipo_relatorio = "G" )'
lista = pd.read_sql(query, connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
for j in lista['id_relatorio_quaid419']:
exposicoes_conso(j)
exposicoes_sep(j)
var_gerencial(j, "normal", dtbase)
var_gerencial(j, "estressado", dtbase)
horario_fim = datetime.datetime.now()
tempo=horario_fim-horario_inicio
print(tempo)
def titpublico_final_pv_ettj():
import pandas as pd
import pymysql as db
import numpy as np
import datetime
import logging
from findt import FinDt
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
#----Declaração de constantes
# Pega a data do último dia útil do mês anterior e deixa no formato específico para utilização da função
dtbase = get_data_ultimo_dia_util_mes_anterior()
# Auxiliar para pegar vna existente na base - versão dummie
dt_vna = get_global_var(dt_vna)
dt_vna = datetime.date(int(dt_vna[0:4]), int(dt_vna[4:6]), int(dt_vna[6:8]))
# Quando tiver vna da data de posicao
# dt_vna = dt_base
dt_base = datetime.date(int(dtbase[0]), int(dtbase[1]), int(dtbase[2]))
# Diretório de save de planilhas
save_path_tpf_fluxo_final = full_path_from_database('get_output_quadro419') + 'tpf_fluxo_final.xlsx'
feriados_sheet = full_path_from_database('feriados_nacionais') + 'feriados_nacionais.csv'
#----Leitura do HEADER para pegar a data de referencia do relatório
#Informações XML
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv'
, use_unicode=True, charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'select * from projeto_inv.xml_header_org'
xml_header = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
xml_header = xml_header[xml_header.dtposicao == dt_base].copy()
xml_header = xml_header[xml_header.data_bd == max(xml_header.data_bd)].copy()
horario_bd = datetime.datetime.today()
#----Seleção da tabela xml_titpublico
# Seleção da tabela de TITPUBLICO
query = 'SELECT * FROM projeto_inv.xml_titpublico_org'
tpf_xml = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
aux = xml_header[['dtposicao','header_id']].copy()
aux['marcador'] = 1
#Seleção da carga mais recente referente à data base do relatório
tpf_xml = tpf_xml.merge(aux,on=['header_id'],how='left')
tpf_xml = tpf_xml[tpf_xml.marcador==1]
tpf_xml = tpf_xml[tpf_xml.data_bd==max(tpf_xml.data_bd)]
#Mudança de formato
tpf_xml['codativo'] = tpf_xml['codativo'].fillna(0)
tpf_xml['codativo'] = tpf_xml['codativo'].astype(int)
del tpf_xml['data_bd']
del tpf_xml['marcador']
del tpf_xml['pu_mercado']
del tpf_xml['mtm_mercado']
del tpf_xml['pu_curva']
del tpf_xml['mtm_curva']
del tpf_xml['pu_regra_xml']
del tpf_xml['mtm_regra_xml']
del tpf_xml['data_referencia']
#----Marcação na curva
#Seleção dos inputs e chaves
tpf_curva = tpf_xml[['codativo','dtvencimento','dtposicao','dtoperacao','coupom','id_papel']].copy()
#Mudanças necessárias de formato
tpf_curva['coupom'] = tpf_curva['coupom'].astype(float)
#Data de referência da posição
tpf_curva['data_posicao_curva'] = dt_base
fluxo = pd.read_sql('select * from projeto_inv.anbima_fluxo_tpf;', con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
fluxo = fluxo.sort(['titulo','dt_vencto', 'dt_ref', 'data_bd'], ascending=[True, True, True, False])
fluxo = fluxo.drop_duplicates(subset=['titulo', 'dt_vencto', 'dt_ref'], take_last=False)
fluxo = fluxo.reset_index(level=None, drop=True, inplace=False, col_level=0, col_fill='')
fluxo = fluxo.rename(columns={'dt_vencto':'dtvencimento','cod_selic':'codativo'})
#Retirada de colunas não utilizadas
del fluxo['titulo']
del fluxo['dt_emissao']
del fluxo['cupom']
del fluxo['dt_vencto2']
del fluxo['data_bd']
#Seleção do fluxo futuro à data da posição
fluxo = fluxo[fluxo.dt_ref>=dt_base].copy()
#Chamada de valores de VNA
query = 'SELECT * FROM projeto_inv.anbima_vna'
vna_virgem = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
vna = vna_virgem.sort(['codigo_selic','data_referencia','data_bd'], ascending=[True, False, False])
vna = vna.drop_duplicates(subset=['codigo_selic','data_referencia'], take_last=False)
vna = vna.reset_index(level=None, drop=True, inplace=False, col_level=0, col_fill='')
vna = vna.rename(columns={'codigo_selic':'codativo'})
#Mudança de formato
#Seleção vna na data da posição
vna = vna[vna.data_referencia==dt_vna].copy()
#Retirada de colunas não utilizadas
del vna['id_anbima_vna']
del vna['titulo']
del vna['data_referencia']
del vna['data_bd']
#Agregação do vna à tabela fluxo
fluxo = fluxo.merge(vna,on=['codativo'],how='left')
fluxo['vna'] = fluxo['vna'].fillna(1000.0)
#Agregaçào do fluxo à tabela tpf_curva
tpf_curva = pd.merge(tpf_curva,fluxo,right_on=['codativo','dtvencimento'], left_on=['codativo','dtvencimento'], how='left')
#Criação da lista de datas para trazer o fv a vp
#Começa a contar a partir da data da posição
dt_min = dt_base
tpf_curva['dt_ref']=pd.to_datetime(tpf_curva['dt_ref'])
dt_max = max(tpf_curva['dt_ref'])
dt_max = dt_max.to_datetime()
dt_max=dt_max.date()
dt_ref = pd.date_range(start=dt_min, end=dt_max, freq='D').date
serie_dias = pd.DataFrame(columns=['dt_ref'])
serie_dias['dt_ref'] = dt_ref
per = FinDt.DatasFinanceiras(dt_min, dt_max, path_arquivo=feriados_sheet)
du = pd.DataFrame(columns=['dt_ref'])
du['dt_ref'] = per.dias(3)
du['du_flag'] = 1
serie_dias = serie_dias.merge(du,on=['dt_ref'],how='left')
serie_dias['du_flag'] = serie_dias['du_flag'].fillna(0)
serie_dias['prazo_du'] = np.cumsum(serie_dias['du_flag']) - 1
del serie_dias['du_flag']
#Agregação da tabela serie_dias à tpf_curva
tpf_curva['dt_ref'] = tpf_curva['dt_ref'].dt.date
tpf_curva = tpf_curva.merge(serie_dias,on=['dt_ref'],how='left')
#Cálculo do pv
tpf_curva['pv'] = tpf_curva['fv']*tpf_curva['vna']/(1+tpf_curva['coupom']/100)**(tpf_curva['prazo_du']/252)
tpf_curva['pv'] = np.where(tpf_curva['codativo']==210100,tpf_curva['fv']*tpf_curva['vna'],tpf_curva['pv'])
#Cálculo da marcação na curva
y = tpf_curva[['id_papel','pv']].groupby(['id_papel']).agg(['sum'])
y = y.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
y1 = pd.DataFrame(columns=['id_papel','pu_curva'])
y1['id_papel'] = y['id_papel']
y1['pu_curva'] = y['pv']
tpf_curva = tpf_curva.merge(y1,on=['id_papel'],how='left')
#Seleção das colunas necessárias
tpf_curva = tpf_curva[['id_anbima_fluxo_tpf','id_papel','codativo','dtvencimento','dtoperacao','data_posicao_curva','pu_curva','fv','prazo_du']].copy()
#----Informações de mercado
#--Cria a base de valores marcados a mercado
query = 'SELECT * FROM projeto_inv.anbima_tpf'
base_anbima_virgem = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
base_anbima = base_anbima_virgem[base_anbima_virgem.dt_referencia<=dt_base]
base_anbima = base_anbima[['cod_selic','dt_vencto','pu','dt_referencia','dt_carga','tx_indicativa']].copy()
base_anbima = base_anbima.sort(['cod_selic','dt_vencto','dt_referencia','dt_carga'],ascending=[True,True,False,False])
base_anbima = base_anbima.drop_duplicates(subset=['cod_selic','dt_vencto'],take_last=False)
base_anbima = base_anbima[base_anbima.dt_referencia==dt_base].copy()
base_anbima = base_anbima.rename(columns = {'cod_selic':'codativo','dt_vencto':'dtvencimento','dt_referencia':'data_referencia','pu':'pu_mercado'})
del base_anbima['dt_carga']
#----União das colunas de marcação a mercado e marcação na curva
tpf = tpf_curva.merge(base_anbima,on=['codativo','dtvencimento'],how='left')
del tpf['codativo']
del tpf['dtvencimento']
del tpf['dtoperacao']
tpf_xml = tpf_xml.merge(tpf,on=['id_papel'],how='left')
tpf_full = tpf_xml.copy()
tpf_xml = tpf_xml.drop_duplicates(subset=['id_papel'])
#Seção Compromisso
tpf_xml['pu_mercado'] = np.where(tpf_xml['dtretorno'].notnull(),tpf_xml['puposicao'],tpf_xml['pu_mercado'])
tpf_xml['pu_curva'] = np.where(tpf_xml['dtretorno'].notnull(),tpf_xml['puposicao'],tpf_xml['pu_curva'])
tpf_xml['mtm_curva'] = tpf_xml['pu_curva']*(tpf_xml['qtdisponivel'] + tpf_xml['qtgarantia'])
tpf_xml['mtm_mercado'] = tpf_xml['pu_mercado']*(tpf_xml['qtdisponivel'] + tpf_xml['qtgarantia'])
tpf_xml['pu_regra_xml'] = np.where(tpf_xml['caracteristica']=='V',tpf_xml['pu_curva'],tpf_xml['pu_mercado'])
tpf_xml['mtm_regra_xml'] = np.where(tpf_xml['caracteristica']=='V',tpf_xml['mtm_curva'],tpf_xml['mtm_mercado'])
tpf_xml['data_bd'] = horario_bd
del tpf_xml['id_xml_titpublico']
del tpf_xml['dtposicao']
del tpf_xml['data_posicao_curva']
del tpf_xml['fv']
del tpf_xml['prazo_du']
del tpf_xml['id_anbima_fluxo_tpf']
del tpf_xml['tx_indicativa']
logger.info("Salvando base de dados - xml_titpublico")
pd.io.sql.to_sql(tpf_xml, name='xml_titpublico', con=connection, if_exists='append', flavor='mysql', index=0)
#----Trazer fv das LTN a vp, calcular o perc_mtm , DV100 e Duration
#Fator de desconto
dia = str(dt_base.day)
if len(dia) == 1:
dia = '0'+dia
mes = str(dt_base.month)
if len(mes) == 1:
mes = '0'+mes
ano = str(dt_base.year)
#dt_base_str = ano + '-' + mes + '-' + dia
tpf_full['indexador'] = np.where(tpf_full['indexador']=='IAP','IPCA',tpf_full['indexador'])
tpf_full['indexador'] = np.where(tpf_full['indexador']=='IGP','IGP',tpf_full['indexador'])
tpf_full['indexador'] = np.where(tpf_full['codativo'].isin([760100,760199]),'IPCA',tpf_full['indexador'])
tpf_full['indexador'] = np.where(tpf_full['codativo'].isin([770100]),'IGP',tpf_full['indexador'])
tpf_full['indexador'] = np.where(tpf_full['codativo'].isin([210100]),'PRE',tpf_full['indexador'])
tpf_pv = tpf_full.copy()
#Cálculo dos fatores de desconto, normal e estressado
dt_base = str(dt_base)
query = 'SELECT * FROM projeto_inv.curva_ettj_interpol_' + ano + '_' + mes + ' where day(dt_ref) = ' + dia + ' AND indexador_cod in("PRE","DIM","DIC");'
ettj = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Seleciona a última carga
ettj = ettj.sort(['indexador_cod','dt_ref', 'data_bd'], ascending = [True,False,False])
ettj = ettj.drop_duplicates(subset=['prazo','tx_spot', 'tx_spot_ano','tx_termo_dia','indexador_cod'], take_last=False)
ettj['indexador']=np.where(ettj['indexador_cod']=='DIC','IPCA',np.where(ettj['indexador_cod']=='DIM', 'IGP',np.where(ettj['indexador_cod']=='SLP','SEL','PRE')))
ettj=ettj.rename(columns={'prazo': 'prazo_du'})
ettj_filtro=ettj[['prazo_du', 'tx_spot', 'tx_spot_ano', 'indexador']]
tpf_pv = tpf_pv[tpf_pv.prazo_du>=0].copy()
tpf_pv = tpf_pv.merge(ettj_filtro,on=['indexador','prazo_du'],how='left')
tpf_pv['tx_spot'] = tpf_pv['tx_spot'].fillna(0)
tpf_pv['tx_spot_ano'] = tpf_pv['tx_spot_ano'].fillna(0)
tpf_pv['fator_desconto'] = 1/(1+tpf_pv['tx_spot'])
tpf_pv['fator_desconto_DV100'] = 1/(1+tpf_pv['tx_spot_ano']+0.01)**(tpf_pv['prazo_du']/252)
tpf_pv['pv'] = tpf_pv['fv']*tpf_pv['fator_desconto']
tpf_pv['pv_DV100'] = tpf_pv['fv']*tpf_pv['fator_desconto_DV100']
total_pv = tpf_pv[['id_papel', 'pv','fv']].copy()
total_pv = total_pv.groupby(['id_papel']).sum().reset_index()
total_pv = total_pv.rename(columns={'pv': 'pv_sum','fv':'fv_sum'})
tpf_pv = tpf_pv.merge(total_pv, on=['id_papel'], how='left')
tpf_pv['perc_mtm'] = tpf_pv['pv']/tpf_pv['pv_sum']
tpf_pv['perc_mtm_DV100'] = tpf_pv['pv_DV100']/tpf_pv['pv_sum']
#Cálculo do DV100
tpf_pv['DV100_fluxo'] = (tpf_pv['perc_mtm'] - tpf_pv['perc_mtm_DV100'])*tpf_pv['pu_mercado']
DV100 = tpf_pv[['id_papel', 'DV100_fluxo']].copy()
DV100 = DV100.groupby(['id_papel']).sum().reset_index()
DV100 = DV100.rename(columns={'DV100_fluxo':'DV100'})
tpf_pv = tpf_pv.merge(DV100, on=['id_papel'], how='left')
#Cálculo da Duration
tpf_pv['fluxo_ponderado'] = tpf_pv['perc_mtm']*tpf_pv['prazo_du']
duration = tpf_pv[['id_papel', 'fluxo_ponderado']].copy()
duration = duration.groupby(['id_papel']).sum().reset_index()
duration = duration.rename(columns={'fluxo_ponderado':'duration'})
tpf_pv = tpf_pv.merge(duration, on=['id_papel'], how='left')
#Normalizando para encaixar com as colunas de titprivado
tpf_pv['fluxo_ponderado'] = tpf_pv['fluxo_ponderado']*tpf_pv['pu_mercado']
tpf_pv['fv'] = tpf_pv['fv']*tpf_pv['pu_mercado']/tpf_pv['fv_sum']
del tpf_pv['pv_sum']
del tpf_pv['fv_sum']
del tpf_pv['DV100_fluxo']
del tpf_pv['perc_mtm_DV100']
del tpf_pv['tx_indicativa']
tpf_pv = tpf_pv.rename(columns={'isin':'codigo_isin','pu_mercado':'mtm','data_fim':'dt_ref'})
tpf_pv['data_mtm'] = dt_base
tpf_pv['data_negociacao'] = tpf_pv['data_referencia']
tpf_pv['ano_mtm'] = dt_base[0:4]
tpf_pv['mes_mtm'] = dt_base[5:7]
tpf_pv['dia_mtm'] = dt_base[8:10]
tpf_pv['indexador'] = np.where(tpf_pv['codativo'].isin([210100]),'SEL',tpf_pv['indexador'])
#Seleção das colunas finais
tpf_pv = tpf_pv[['id_anbima_fluxo_tpf',
'id_papel',
'codigo_isin',
'codativo',
'dtvencimento',
'prazo_du',
'fv',
'indexador',
'tx_spot',
'fator_desconto',
'fator_desconto_DV100',
'pv',
'pv_DV100',
'perc_mtm',
'DV100',
'fluxo_ponderado',
'duration',
'mtm',
'data_mtm',
'ano_mtm',
'mes_mtm',
'dia_mtm',
'data_negociacao']].copy()
#Cria o flag de tipo_ativo T(ítulo) P(úblico) F(eferal)
tpf_pv['tipo_ativo'] = 'TPF'
tpf_pv['data_bd'] = horario_bd
tpf_pv = tpf_pv.sort(['codigo_isin','codativo','prazo_du'],ascending=[True,True,True])
tpf_pv = tpf_pv.drop_duplicates(subset=['codigo_isin','codativo','dtvencimento','prazo_du'],take_last=True)
#Coloca na base mtmt_renda_fixa
logger.info("Salvando base de dados - mtm_renda_fixa")
pd.io.sql.to_sql(tpf_pv, name='mtm_renda_fixa', con=connection,if_exists="append", flavor='mysql', index=0)
#Fecha conexão
connection.close()
tpf_pv.to_excel(save_path_tpf_fluxo_final)
def matriz_gerencial_2():
import pymysql as db
import datetime
import numpy as np
import pandas as pd
import logging
import pickle
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from var.scripts.matriz_gerencial.definicao_lambda import definicao_lambda
from var.scripts.matriz_gerencial.definicao_nome import definicao_nome
#Define variáveis:
logger = logging.getLogger(__name__)
save_path = full_path_from_database("get_output_var")
retornos_path = full_path_from_database("pickles")
# data_final = '2016-11-30'
# dt_base = '20161130'
# data_inicial = "2010-03-31"
dt_base = get_data_ultimo_dia_util_mes_anterior()
data_final = str(dt_base[0]) + '-' + str(dt_base[1]) + '-' + str(
dt_base[2])
dt_base = dt_base[0] + dt_base[1] + dt_base[2]
data_inicial = get_global_var("dt_ini_matriz_gerencial_2")
data_inicial = str(data_inicial[0:4]) + '-' + str(
data_inicial[4:6]) + '-' + str(data_inicial[6:8])
lambda_geral = definicao_lambda('outro') #lambda_geral = 0.95
retornos = open(retornos_path + 'matriz_gerencial_retornos' + ".pkl", "w")
# Conecta no DB
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Tratando dados")
## Cálculo da matriz de correlação
matriz_correlacao = retornos.corr()
matriz_correlacao.fillna(0, inplace=True)
variancias_manual = pd.read_excel(
full_path_from_database("get_output_var") + 'variancias_' + dt_base +
'.xlsx')
#COMO QUE EU COLOCO NO FORMATO QUE ESTAVAM VINDO AS VARIANCIAS ANTES?
variancias = variancias_manual.values
desvio_padrao = np.sqrt(variancias)
desvio_padrao = np.squeeze(desvio_padrao)
## Cálculo da matriz de covariância
matriz_diagonal = np.diag(desvio_padrao)
matriz_covariancias = np.dot(matriz_diagonal,
np.dot(matriz_correlacao, matriz_diagonal))
## Salvar no banco de dados
base_final = pd.DataFrame()
for linha in range(len(retornos.columns)):
for coluna in range(linha + 1):
resultado = {}
if isinstance(retornos.columns[linha],
tuple) and len(retornos.columns[linha]) > 2:
resultado["linha"] = definicao_nome(
retornos.columns[linha][1]) + "_" + str(
retornos.columns[linha][2])
else:
resultado["linha"] = definicao_nome(retornos.columns[linha])
if isinstance(retornos.columns[coluna],
tuple) and len(retornos.columns[coluna]) > 2:
resultado["coluna"] = definicao_nome(
retornos.columns[coluna][1]) + "_" + str(
retornos.columns[coluna][2])
else:
resultado["coluna"] = definicao_nome(retornos.columns[coluna])
resultado["valor"] = matriz_covariancias[linha][coluna]
base_final = base_final.append(resultado, ignore_index=True)
max(base_final["valor"])
base_final["data_bd"] = datetime.datetime.now()
base_final["data_inicial"] = data_inicial
base_final["data_final"] = data_final
base_final["horizonte"] = "dia"
base_final["lambda"] = lambda_geral
# TESTE SE VETOR VEM ZERADO
## Inserir "matriz_id"
matriz_id = pd.read_sql_query(
'SELECT max(matriz_id) as matriz_id FROM matriz_gerencial', connection)
if matriz_id['matriz_id'][0] == None:
matriz_id_valor = 0
else:
matriz_id_valor = matriz_id.get_value(0, 'matriz_id').astype(int) + 1
base_final["matriz_id"] = matriz_id_valor
### Salvar no BD
logger.info("Salvando base de dados")
pd.io.sql.to_sql(base_final,
name='matriz_gerencial',
con=connection,
if_exists="append",
flavor='mysql',
index=0,
chunksize=5000)
new_matriz_id = pd.read_sql_query(
'select matriz_id from matriz_gerencial where data_bd = ( SELECT MAX(data_bd) from matriz_gerencial ) group by matriz_id',
connection)
new_matriz_id = new_matriz_id['matriz_id'].iloc[0]
cur = connection.cursor(db.cursors.DictCursor)
query = 'update generator_global_variables set variable_value = ' + str(
new_matriz_id) + ' where variable_name = ' + '\'matriz_id\''
logger.info("Atualizando matriz_id na base de dados")
cur.execute(query)
connection.commit()
logger.info("Base de dados atualizada com sucesso")
connection.close()
base_final.to_excel(save_path + 'matriz_gerencial_' + str(data_final) +
'.xlsx')
logger.info("Arquivo Matriz Gerencial salvo com sucesso")
retornos.to_excel(save_path + 'retornos_mensal_' + str(data_final) +
'.xlsx')
logger.info("Arquivo Retornos mensais salvo com sucesso")
def matriz_gerencial_1():
import pymysql as db
import datetime
import numpy as np
import pandas as pd
import logging
import pickle
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import get_global_var
#Define variáveis:
logger = logging.getLogger(__name__)
save_path = full_path_from_database("get_output_var")
dt_base = get_data_ultimo_dia_util_mes_anterior()
data_final = str(dt_base[0]) + '-' + str(dt_base[1]) + '-' + str(
dt_base[2])
#data_final = '2016-11-30'
dt_base = dt_base[0] + dt_base[1] + dt_base[2]
#dt_base = '20161130'
data_inicial = "2010-03-31"
retornos_path = full_path_from_database("pickles")
#################################################################################################################
# VARIÁVEL PRECISA SER PARAMETRIZADA DE ACORDO COM O CÓDIGO GERADO NO SCRIPT 17-XML_QUADRO_OPERACOES_NAO_ORG.PY
id_relatorio_quaid419 = get_global_var("id_qua419")
#id_relatorio_quaid419 = '3650' # para novembro
#################################################################################################################
#Cotas cujo histórico é ruim
lista_fundos_excluidos = [
'BRBVEPCTF002', 'BRFCLGCTF007', 'BRFCLGCTF015', 'BROLGSCTF005',
'BRSNGSCTF002', 'BRMBVACTF007', 'BRINSBCTF020', 'BRVCCDCTF000'
]
#Definição Lambdas
lista_lambdas = {
'PRE': 92,
'DIC': 94,
'DIM': 85,
'DP': 73,
'TP': 97,
"TR": 97,
"IPCA": 94,
"IGPM": 86,
"Dólar": 78,
"Bovespa": 95,
"ICB": 95
}
#Ajuste datas
data_inicial = datetime.datetime.strptime(data_inicial, "%Y-%m-%d").date()
data_final = datetime.datetime.strptime(data_final, "%Y-%m-%d").date()
#Conecta no DB
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
#start_time = time.time()
#matriz_teste = pd.read_sql_query('SELECT t.* FROM ( SELECT codigo_isin, MAX(data_bd) AS max_data FROM bmf_numeraca GROUP BY codigo_isin ) AS m INNER JOIN bmf_numeraca AS t ON t.codigo_isin = m.codigo_isin AND t.data_bd = m.max_data', connection)
#elapsed_time = time.time() - start_time
#print (elapsed_time)
logger.info("Tratando dados")
# Consulta valores das séries
dados_curvas = pd.read_sql_query(
'SELECT * FROM projeto_inv.curva_ettj_vertices_fixos', connection)
dados_bacen = pd.read_sql_query('SELECT * FROM projeto_inv.bacen_series',
connection)
dados_cotas1 = pd.read_sql_query(
'SELECT * FROM projeto_inv.valoreconomico_cotas', connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Remove duplicados
dados_cotas1 = dados_cotas1.sort(['cota', 'dt_ref', 'data_bd'],
ascending=[True, True, True])
dados_cotas1 = dados_cotas1.drop_duplicates(subset=['cota', 'dt_ref'],
take_last=True)
data_bd = dados_cotas1['data_bd'].iloc[0]
#Remove duplicados
dados_bacen = dados_bacen.sort(['data_referencia', 'codigo', 'data_bd'],
ascending=[True, True, True])
dados_bacen = dados_bacen.drop_duplicates(
subset=['codigo', 'data_referencia'], take_last=True)
#Lê arquivo BRSND2CTF000.xlsx
dados_cotas = pd.read_excel(
full_path_from_database("excels") + 'BRSND2CTF000.xlsx')
logger.info("Arquivos lidos com sucesso")
logger.info("Tratando dados")
dados_cotas['dt_ref'] = dados_cotas['dt_ref'].astype(str)
dados_cotas['dt_ref'] = dados_cotas['dt_ref'].str.split('/')
dados_cotas['ano'] = dados_cotas['dt_ref'].str[2]
dados_cotas['mes'] = dados_cotas['dt_ref'].str[1]
dados_cotas['dia'] = dados_cotas['dt_ref'].str[0]
dados_cotas['dt_ref'] = pd.to_datetime('20' + dados_cotas['ano'] +
dados_cotas['mes'] +
dados_cotas['dia']).dt.date
del dados_cotas['ano']
del dados_cotas['mes']
dados_cotas['data_bd'] = None
dados_cotas['data_bd'] = dados_cotas['data_bd'].fillna(data_bd)
dados_cotas = dados_cotas.append(dados_cotas1)
dados_cotas = dados_cotas.drop_duplicates(subset=['isin_fundo', 'dt_ref'])
## Filtrar datas para estimativa
dados_curvas = dados_curvas[(dados_curvas["dt_ref"] < data_final)
& (dados_curvas["dt_ref"] > data_inicial)]
dados_bacen = dados_bacen[(dados_bacen["data_referencia"] < data_final) &
(dados_bacen["data_referencia"] > data_inicial)]
dados_cotas = dados_cotas[(dados_cotas["dt_ref"] < data_final)
& (dados_cotas["dt_ref"] > data_inicial)]
#Ajusta o nome de duas colunas
dados_bacen['nome'] = np.where(dados_bacen['codigo'] == 7,
'Bovespa - índice', dados_bacen['nome'])
dados_bacen['nome'] = np.where(dados_bacen['codigo'] == 1,
'Dólar comercial (venda)',
dados_bacen['nome'])
#Tira os índices mensais
dados_bacen = dados_bacen[dados_bacen['frequencia'] != "M"].copy()
dados_cotas_chamber = dados_cotas[dados_cotas['isin_fundo'].isin(
lista_fundos_excluidos)].copy()
dados_cotas_chamber['codigo'] = 7
dados_cotas_chamber = dados_cotas_chamber[['isin_fundo', 'codigo']].copy()
dados_cotas_chamber = dados_cotas_chamber.drop_duplicates()
chamber_corr = dados_bacen[dados_bacen['codigo'] == 7].copy()
dados_cotas_chamber = pd.merge(chamber_corr,
dados_cotas_chamber,
right_on=['codigo'],
left_on=['codigo'],
how='left')
dados_cotas_chamber['nome'] = dados_cotas_chamber['isin_fundo']
for i in lista_fundos_excluidos:
dados_cotas_chamber['codigo'] = np.where(
dados_cotas_chamber['nome'].isin([i]), np.random.rand(),
dados_cotas_chamber['codigo'])
dados_cotas_chamber['codigo'] = dados_cotas_chamber['codigo'] * 100000
dados_cotas_chamber['codigo'] = dados_cotas_chamber['codigo'].astype(int)
del dados_cotas_chamber['isin_fundo']
dados_bacen = dados_bacen.append(dados_cotas_chamber)
dados_cotas = dados_cotas[~dados_cotas['isin_fundo'].
isin(lista_fundos_excluidos)].copy()
#Leitura e tratamento das variancias da bmfbovespa - seleciona apenas as ações da carteira
dados_vol_bmf = pd.read_excel(
full_path_from_database("bvmf") + 'volatilidade_bmf_' + dt_base +
'.xlsx')
logger.info("Arquivos lidos com sucesso")
dados_vol_bmf.columns = [
'codigo_negociacao', 'nome_empresa', 'preco_fechamento', 'ewma_anual'
]
acoes_ibov = pd.read_excel(
full_path_from_database("bvmf") + 'Composição_ibovespa_' + dt_base +
'.xlsx')
logger.info("Arquivos lidos com sucesso")
acoes_ibov.columns = ['codigo', '1', '2', '3', '4']
acoes_ibov['codigo'] = acoes_ibov['codigo']
quaid_419 = pd.read_sql_query(
'SELECT * from projeto_inv.quaid_419 where id_relatorio_quaid419=' +
str(id_relatorio_quaid419) + ' and FTRCODIGO = "AA1"', connection)
logger.info("Leitura do banco de dados executada com sucesso")
quaid_419 = quaid_419[quaid_419['data_bd'] == max(quaid_419['data_bd'])]
quaid_419 = quaid_419.rename(columns={
'EMFCODCUSTODIA': 'codigo',
'EMFCODISIN': '2'
})
quaid_419 = quaid_419[['codigo', '2']].copy()
acoes_ibov = acoes_ibov.append(quaid_419)
lista_acoes = acoes_ibov['codigo'].unique()
dados_vol_bmf = dados_vol_bmf[dados_vol_bmf.codigo_negociacao.isin(
lista_acoes)].copy()
dados_vol_bmf['codigo'] = 7
del dados_vol_bmf['nome_empresa']
del dados_vol_bmf['preco_fechamento']
del dados_vol_bmf['ewma_anual']
chamber_corr = dados_bacen[dados_bacen['codigo'] == 7].copy()
dados_vol_bmf = pd.merge(chamber_corr,
dados_vol_bmf,
right_on=['codigo'],
left_on=['codigo'],
how='left')
dados_vol_bmf['nome'] = dados_vol_bmf['codigo_negociacao'] + '_1'
for i in lista_acoes:
dados_vol_bmf['codigo'] = np.where(
dados_vol_bmf['nome'].isin([i + '_1']), np.random.rand(),
dados_vol_bmf['codigo'])
dados_vol_bmf['codigo'] = dados_vol_bmf['codigo'] * 100000
dados_vol_bmf['codigo'] = dados_vol_bmf['codigo'].astype(int)
del dados_vol_bmf['codigo_negociacao']
dados_bacen = dados_bacen.append(dados_vol_bmf)
## Lista de séries do Bacen a ser utlizada
aux1 = dados_vol_bmf[['codigo', 'nome']].copy()
aux1 = aux1.drop_duplicates()
aux2 = dados_cotas_chamber[['codigo', 'nome']].copy()
aux2 = aux2.drop_duplicates()
aux2 = aux2.append(aux1)
lista_series_bacen = aux2['codigo'].tolist()
#lista_series_bacen = lista_series_bacen.tolist()
n_len = len(lista_series_bacen)
lista_series_bacen.insert(n_len, 1)
n_len = len(lista_series_bacen)
lista_series_bacen.insert(n_len, 7)
## Ajuste para variáveis virarem colunas
tabela_ajustada = pd.pivot_table(dados_curvas,
index=["dt_ref"],
columns=["indexador_cod", "prazo"],
values=["tx_spot_ano"])
## Ajuste data Cupom Cambial (de 252 para 360)
nova_lista = []
for i in range(len(tabela_ajustada.columns)):
if tabela_ajustada.columns[i][1] == "DP":
resultado = (tabela_ajustada.columns[i][0], "DP",
int(tabela_ajustada.columns[i][2] * (360 / 252.)))
else:
resultado = tabela_ajustada.columns[i]
nova_lista.append(resultado)
tabela_ajustada.columns = nova_lista
## Ajuste data para PU
tabela_pu = tabela_ajustada
for coluna in tabela_ajustada.columns:
periodo = coluna[2]
taxa = coluna[1]
if taxa == "DP":
fator_data = (periodo / 360.)
else:
fator_data = (periodo / 252.)
tabela_pu[coluna] = 100 / ((1 + tabela_ajustada[coluna])**fator_data)
#Guarda valores para a proxy das cotas
proxy_cotas = dados_bacen[['data_referencia', 'codigo',
'valor']][dados_bacen["codigo"] == 7].copy()
teste = tabela_pu
for serie in lista_series_bacen:
try:
dados_serie = dados_bacen[dados_bacen["codigo"] == serie]
dados_serie = dados_serie.drop_duplicates(
subset=['data_referencia', 'codigo'])
teste = pd.merge(teste,
dados_serie[["valor", "data_referencia"]],
left_index=True,
right_on=["data_referencia"],
how="left")
teste.rename(columns={'valor': dados_serie["nome"].iloc[0]},
inplace=True)
teste.index = teste["data_referencia"]
del teste["data_referencia"]
except:
pass
#Ajustar a proxy das cotas - Ibovespa - serie historica que esta no bacen
dados_cotas_proxy = pd.DataFrame(columns=['codigo', 'isin_fundo'])
dados_cotas_proxy1 = []
lista_isin_fundo = dados_cotas['isin_fundo'].unique()
for i in lista_isin_fundo:
dados_cotas_proxy1.append(i)
dados_cotas_proxy['isin_fundo'] = dados_cotas_proxy1
dados_cotas_proxy['codigo'] = 7
#Dropa duplicatas bacen
proxy_cotas = proxy_cotas.drop_duplicates(
subset=['data_referencia', 'codigo'])
proxy_cotas.columns = ['dt_ref', 'codigo', 'cota']
dados_cotas_proxy = pd.merge(dados_cotas_proxy,
proxy_cotas,
left_on=['codigo'],
right_on=['codigo'],
how='left')
dados_cotas = dados_cotas_proxy[['dt_ref', 'isin_fundo', 'cota']].copy()
tabela_cotas = pd.pivot_table(dados_cotas,
index=["dt_ref"],
columns=["isin_fundo"],
values=["cota"])
teste = teste.merge(tabela_cotas, left_index=True, right_index=True)
## Preencher datas vazias com o último valor disponível
teste.fillna(method='pad', inplace=True)
## Preencher o histórico de nan com o primeiro valor da série
teste.fillna(method='bfill', inplace=True)
## Cálculo do retorno diário
retornos = teste.pct_change()
## Retirar primeira observação NaN
retornos = retornos[1:]
aux = retornos**2
aux.to_excel(save_path + 'retornos' + str(data_final) + '.xlsx')
logger.info("Arquivos salvos com sucesso")
connection.close()
pickle_file = open(retornos_path + 'matriz_gerencial_retornos' + ".pkl",
"w")
pickle.dump(retornos, pickle_file)
pickle_file.close()
def get_anbima_vna():
# endereco_vna = "C:/Users/Thiago.Mazieiro/Downloads/vna_20161230.html"
#'QUERY PARA SABER SE TEM VNA NA DATA DA POSICAO
#SELECT * FROM projeto_inv.anbima_vna where data_referencia = "2016-11-30 00:00:00" and data_bd=(select max(data_bd) from projeto_inv.anbima_vna where data_referencia = "2016-11-30 00:00:00");
import pandas as pd
import pymysql as db
import datetime
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
endereco_vna = get_global_var("endereco_vna")
pagina_vna = pd.read_html(endereco_vna, thousands=".")
logger.info("Leitura da página executada com sucesso")
logger.info("Tratando dados")
#Separacao de abas
coluna1_ntn_b = pagina_vna[4][0][0:6]
coluna2_ntn_b = pagina_vna[4][1][0:6]
coluna1_ntn_c = pagina_vna[5][0][0:6]
coluna2_ntn_c = pagina_vna[5][1][0:6]
coluna1_lft = pagina_vna[6][0][0:6]
coluna2_lft = pagina_vna[6][1][0:6]
#Data referencia
data_referencia = datetime.datetime.strptime(
coluna2_ntn_b[1], "%d/%m/%Y").strftime('%Y-%m-%d')
#Separar valores
colunas = ["data_referencia", "codigo_selic", "titulo", "vna"]
dados_vna = pd.DataFrame(columns=colunas)
resultados0 = [data_referencia, coluna1_ntn_b[3], "ntnb", coluna2_ntn_b[3]]
resultados1 = [data_referencia, coluna1_ntn_b[4], "ntnb", coluna2_ntn_b[4]]
resultados2 = [data_referencia, coluna1_ntn_c[3], "ntnc", coluna2_ntn_c[3]]
resultados3 = [data_referencia, coluna1_lft[3], "lft", coluna2_lft[3]]
dados_vna.loc[0] = resultados0
dados_vna.loc[1] = resultados1
dados_vna.loc[2] = resultados2
dados_vna.loc[3] = resultados3
# Correção valores vna
dados_vna["vna"] = [i.replace(".", "") for i in dados_vna["vna"]]
dados_vna["vna"] = [float(i.replace(",", ".")) for i in dados_vna["vna"]]
# Colocar data_bd
dados_vna["data_bd"] = datetime.datetime.now()
# Substituir eventuais nan por none
dados_vna = dados_vna.where((pd.notnull(dados_vna)), None)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
# Salva dados MySQL
pd.io.sql.to_sql(dados_vna,
name='anbima_vna',
con=connection,
if_exists="append",
flavor='mysql',
index=0)
logger.info("Dados salvos no DB com sucesso - Tabela anbima_vna")
# Fecha conexão
connection.close()
def mtm_curva_titprivado():
import datetime, time
import pandas as pd
import numpy as np
import pymysql as db
import logging
from pandas import ExcelWriter
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
# Pega a data do último dia útil do mês anterior e deixa no formato específico para utilização da função
dtbase = get_data_ultimo_dia_util_mes_anterior()
dtbase_concat = dtbase[0] + dtbase[1] + dtbase[2]
# Diretório de save de planilhas
save_path_puposicao = full_path_from_database(
'get_output_quadro419') + 'puposicao_final.xlsx'
save_path_titprivado_perc = full_path_from_database(
'get_output_quadro419') + 'titprivado_perc.xlsx'
tol = float(get_global_var("tol"))
writer = ExcelWriter(save_path_puposicao)
# 1 - Leitura e criação de tabelas
# Informações do cálculo de MTM
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'select * from projeto_inv.mtm_titprivado'
#query = "SELECT * tipo_ativo FROM mtm_titprivado WHERE tipo_ativo <> 'DBS' AND tipo_ativo <> 'CTF'"
mtm_titprivado0 = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
logger.info("Tratando dados")
mtm_titprivado = mtm_titprivado0.copy()
# Tira debentures
mtm_titprivado = mtm_titprivado[(mtm_titprivado.tipo_ativo != 'DBS')
& (mtm_titprivado.tipo_ativo != 'CTF')]
mtm_titprivado['dtrel'] = mtm_titprivado['id_papel'].str.split('_')
mtm_titprivado['dtrel'] = mtm_titprivado['dtrel'].str[0]
mtm_titprivado = mtm_titprivado[mtm_titprivado.dtrel ==
dtbase_concat].copy()
mtm_titprivado = mtm_titprivado[mtm_titprivado.data_bd == max(
mtm_titprivado.data_bd)]
del mtm_titprivado['dtrel']
mtm_titprivado = mtm_titprivado.rename(columns={
'data_fim': 'dt_ref',
'dtoperacao': 'dtoperacao_mtm'
})
mtm_titprivado['dt_ref'] = pd.to_datetime(mtm_titprivado['dt_ref'])
mtm_titprivado['dt_ref'] = np.where(
mtm_titprivado['indexador'] == 'DI1',
mtm_titprivado['dt_ref'] + pd.DateOffset(months=0, days=1),
mtm_titprivado['dt_ref'])
mtm_titprivado['dt_ref'] = mtm_titprivado['dt_ref'].dt.date
# Altera o nome do id_papel para levar em consideração o flag
mtm_titprivado['id_papel_old'] = mtm_titprivado['id_papel']
mtm_titprivado['id_papel'] = mtm_titprivado['id_papel_old'].str.split('_')
mtm_titprivado['id_papel'] = mtm_titprivado['id_papel'].str[0] + '_' + mtm_titprivado['id_papel'].str[1] + '_' + \
mtm_titprivado['id_papel'].str[2]
# Retuira papéis com valor nominal zerado ou vazio
mtm_titprivado = mtm_titprivado[
(mtm_titprivado.valor_nominal.notnull())
& (mtm_titprivado.valor_nominal != 0)].copy()
del mtm_titprivado['data_bd']
# Informações XML
query = 'SELECT * FROM projeto_inv.xml_titprivado_org'
xml_titprivado = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
xml_titprivado['dtrel'] = xml_titprivado['id_papel'].str.split('_')
xml_titprivado['dtrel'] = xml_titprivado['dtrel'].str[0]
xml_titprivado = xml_titprivado[xml_titprivado.dtrel ==
dtbase_concat].copy()
xml_titprivado = xml_titprivado[xml_titprivado.data_bd == max(
xml_titprivado.data_bd)]
del xml_titprivado['data_bd']
del xml_titprivado['dtrel']
original = xml_titprivado.copy()
del xml_titprivado['indexador']
# mtm_titprivado.to_excel(save_path+'checa_id.xlsx')
titprivado1 = mtm_titprivado.copy()
# Criação da tabela mtm + xml
titprivado = xml_titprivado.merge(mtm_titprivado,
on=['id_papel'],
how='left')
# Criação da coluna de data de referencia da posição
titprivado['data_referencia'] = titprivado['id_papel'].str[0:8]
titprivado['data_referencia'] = pd.to_datetime(
titprivado['data_referencia']).dt.date
logger.info("Escolha do melhor mtm")
# 2 - Escolha do melhor mtm
titprivado_mercado = titprivado[(titprivado.caracteristica == 'N')
& (titprivado.mtm.notnull()) &
(titprivado.mtm != 0)]
titprivado_mercado['dif_mtm'] = titprivado_mercado[
'puposicao'] / titprivado_mercado['mtm'] - 1
titprivado_mercado['dif_mtm'] = titprivado_mercado['dif_mtm'].abs()
titprivado_mercado = titprivado_mercado[
titprivado_mercado.dt_ref ==
titprivado_mercado.data_referencia].copy()
titprivado_mercado = titprivado_mercado[[
'id_papel_old', 'id_papel', 'codigo_isin', 'dif_mtm'
]].copy()
titprivado_mercado = titprivado_mercado.sort(['dif_mtm'], ascending=[True])
titprivado_mercado = titprivado_mercado.drop_duplicates(
subset=['id_papel'], take_last=False)
titprivado = titprivado.merge(
titprivado_mercado,
on=['id_papel_old', 'id_papel', 'codigo_isin'],
how='left')
titprivado = titprivado[((titprivado.caracteristica == 'N') &
(titprivado.dif_mtm.notnull())) |
(titprivado.caracteristica == 'V')].copy()
tp_aux = titprivado[['id_papel', 'isin',
'dtvencimento']][titprivado.dif_mtm.isnull()].copy()
tp_aux = tp_aux.drop_duplicates(subset=['id_papel'])
tp_aux.to_excel(writer, 'sem_mtm')
titprivado = titprivado.reset_index(level=None,
drop=True,
inplace=False,
col_level=0,
col_fill='')
logger.info("Cálculo marcação na curva")
# 3 - Cálculo marcação na curva
titprivado_curva = titprivado[titprivado.caracteristica == 'V'].copy()
del titprivado_curva['vne']
# Seleciona a parte do fluxo entre a data da compra e a data da posição
titprivado_curva = titprivado_curva[
titprivado_curva.dt_ref >= titprivado_curva.dtoperacao_mtm].copy()
titprivado_curva = titprivado_curva[
titprivado_curva.dt_ref <= titprivado_curva.data_referencia].copy()
# Preenchimento do VNE na data da compra
tp_curva_dtop = titprivado_curva[[
'id_papel_old', 'saldo_dev_juros_perc', 'pucompra'
]][titprivado_curva.dt_ref == titprivado_curva.dtoperacao_mtm].copy()
tp_curva_dtop['vne'] = tp_curva_dtop['pucompra'] * (
1 - tp_curva_dtop['saldo_dev_juros_perc'])
del tp_curva_dtop['saldo_dev_juros_perc']
del tp_curva_dtop['pucompra']
titprivado_curva = titprivado_curva.merge(tp_curva_dtop,
on=['id_papel_old'],
how='left')
titprivado_curva[
'principal_perc_acum'] = 1 - titprivado_curva['principal_perc']
titprivado_curva['principal_perc_acum'] = titprivado_curva[[
'id_papel_old', 'principal_perc_acum'
]].groupby(['id_papel_old']).agg(['cumprod'])
titprivado_curva['vne'] = titprivado_curva['vne'] * titprivado_curva[
'principal_perc_acum']
titprivado_curva['pagto_juros'] = titprivado_curva[
'vne'] * titprivado_curva['pagto_juros_perc']
titprivado_curva[
'vna'] = titprivado_curva['vne'] * titprivado_curva['fator_index_per']
titprivado_curva['vna'][titprivado_curva.indexador ==
'DI1'] = titprivado_curva['vne'][
titprivado_curva.indexador == 'DI1']
titprivado_curva['saldo_dev_juros'] = titprivado_curva[
'vna'] * titprivado_curva['saldo_dev_juros_perc']
titprivado_curva['pupar'] = titprivado_curva['vna'] + titprivado_curva[
'saldo_dev_juros'] + titprivado_curva['pagto_juros']
titprivado_curva['dif_curva'] = titprivado_curva[
'pupar'] / titprivado_curva['puposicao'] - 1
titprivado_curva['dif_curva'] = titprivado_curva['dif_curva'].abs()
titprivado_curva = titprivado_curva[
titprivado_curva.dt_ref == titprivado_curva.data_referencia].copy()
titprivado_curva = titprivado_curva[[
'id_papel_old', 'id_papel', 'codigo_isin', 'dif_curva', 'pupar'
]].copy()
titprivado_curva = titprivado_curva.sort(['dif_curva'], ascending=[True])
titprivado_curva = titprivado_curva.drop_duplicates(subset=['id_papel'],
take_last=False)
titprivado = titprivado.merge(
titprivado_curva,
on=['id_papel_old', 'id_papel', 'codigo_isin'],
how='left')
titprivado = titprivado[((titprivado.caracteristica == 'V') &
(titprivado.dif_curva.notnull())) |
(titprivado.caracteristica == 'N')].copy()
titprivado = titprivado.reset_index(level=None,
drop=True,
inplace=False,
col_level=0,
col_fill='')
logger.info("Separação dos pu's que ficaram muito distantes do informado")
# 4 - Separação dos pu's que ficaram muito distantes do informado
titprivado['dif_curva'] = titprivado['dif_curva'].fillna(0)
titprivado['dif_mtm'] = titprivado['dif_mtm'].fillna(0)
titprivado['dif'] = titprivado['dif_curva'] + titprivado['dif_mtm']
# Retirar atualizar valores de papéis cujo dif é grande em apenas uma das observações
x = titprivado[[
'codigo_isin', 'id_papel'
]][titprivado.dt_ref == titprivado.data_referencia].groupby(
['codigo_isin']).count()
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'count_all'])
x1['codigo_isin'] = x['codigo_isin']
x1['count_all'] = x['id_papel']
titprivado = titprivado.merge(x1, on=['codigo_isin'], how='left')
x = titprivado[[
'codigo_isin', 'id_papel'
]][(titprivado.dt_ref == titprivado.data_referencia)
& (titprivado.dif > tol) & (titprivado.caracteristica == 'N')].groupby(
['codigo_isin']).count()
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'count_0'])
x1['codigo_isin'] = x['codigo_isin']
x1['count_0'] = x['id_papel']
titprivado = titprivado.merge(x1, on=['codigo_isin'], how='left')
titprivado['count_0'] = np.where(titprivado['dif'] < tol,
titprivado['count_all'],
titprivado['count_0'])
titprivado['count_dif'] = titprivado['count_all'] - titprivado['count_0']
x = pd.DataFrame()
x = titprivado[['id_papel'
]][(titprivado.count_dif != 0)
& (titprivado.dt_ref == titprivado.data_referencia) &
(titprivado.caracteristica == 'N')].copy()
x['change'] = 1
titprivado = titprivado.merge(x, on=['id_papel'], how='left')
x = pd.DataFrame()
x = titprivado[['codigo_isin'
]][(titprivado.count_dif != 0)
& (titprivado.dt_ref == titprivado.data_referencia) &
(titprivado.caracteristica == 'N')].copy()
x['change_basis'] = 2
x = x.drop_duplicates()
titprivado = titprivado.merge(x, on=['codigo_isin'], how='left')
x = titprivado[['codigo_isin', 'puposicao'
]][(titprivado.change_basis == 2)
& (titprivado.change != 1)].groupby(['codigo_isin'
]).agg(['mean'])
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'puposnew'])
x1['codigo_isin'] = x['codigo_isin']
x1['puposnew'] = x['puposicao']
titprivado = titprivado.merge(x1, on=['codigo_isin'], how='left')
titprivado['puposicao1'] = np.where(titprivado['change'] == 1,
titprivado['puposnew'],
titprivado['puposicao'])
titprivado['change'] = titprivado['change'].fillna(0)
del titprivado['change_basis']
titprivado['dif'] = titprivado['mtm'] / titprivado['puposicao1'] - 1
titprivado['dif'] = titprivado['dif'].abs()
logger.info("Cálculo spread de crédito")
# 4 - Cálculo spread de crédito
titprivado_spread = \
titprivado[['id_papel', 'codigo_isin', 'data_referencia', 'puposicao1', 'dt_ref', 'prazo_du', 'pv']][
(titprivado.caracteristica == 'N')]
# Tira a média dos puposicao para calcular spread único por isin
x = titprivado_spread[['codigo_isin', 'puposicao1'
]].groupby(['codigo_isin']).agg(['mean'])
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'pumedio'])
x1['codigo_isin'] = x['codigo_isin']
x1['pumedio'] = x['puposicao1']
titprivado_spread = titprivado_spread.merge(x1,
on=['codigo_isin'],
how='left')
titprivado_spread['puposicao1'] = titprivado_spread['pumedio']
del titprivado_spread['pumedio']
# Seleciona apenas o fluxo com prazo_du positivo
titprivado_spread = titprivado_spread[
titprivado_spread.dt_ref >= titprivado_spread.data_referencia]
titprivado_spread['pv_pv_fluxo'] = np.where(
titprivado_spread['dt_ref'] == titprivado_spread['data_referencia'],
-titprivado_spread['puposicao1'], titprivado_spread['pv'])
tp_spread = titprivado_spread[[
'id_papel', 'codigo_isin', 'dt_ref', 'prazo_du', 'pv_pv_fluxo'
]].copy()
tp_spread['prazo_du'] = tp_spread['prazo_du'].astype(float)
id_papel = titprivado_spread['id_papel'].unique()
spread = np.zeros((len(id_papel)))
spread_aux = pd.DataFrame(columns=['id_papel', 'spread'])
spread_aux['id_papel'] = id_papel
start_time = time.time()
for i in range(0, len(id_papel)):
v = tp_spread['pv_pv_fluxo'][tp_spread.id_papel == id_papel[i]].values
v = np.meshgrid(v, sparse=True)
s = np.linspace(-0.9999, 0.9999, 100000)
t = tp_spread['prazo_du'][tp_spread.id_papel == id_papel[i]].values
t, s = np.meshgrid(t, s, sparse=True)
f_ts = 1. / (1 + s)**(t / 252)
f_spread = v * f_ts
f_sum = f_spread.sum(axis=1, dtype='float')
min_index = abs(f_sum).argmin()
spread[i] = s[min_index]
#print(time.time() - start_time, i, id_papel[i], spread[i])
spread_aux['spread'].iloc[i] = spread[i]
titprivado = titprivado.merge(spread_aux, on=['id_papel'], how='left')
logger.info("Seleção dos papéis cuja marcação não ficou boa")
# 5 - Seleção dos papéis cuja marcação não ficou boa
tp_bigdif = titprivado[[
'data_referencia', 'codigo_isin', 'id_papel', 'flag', 'caracteristica',
'indexador', 'dtemissao', 'data_emissao', 'dtvencimento',
'data_expiracao', 'valor_nominal', 'puemissao', 'juros_cada', 'coupom',
'taxa_juros', 'percentual_indexador', 'percindex', 'perc_amortizacao',
'dt_ref', 'vne', 'du_per', 'prazo_du', 'fator_index_per',
'fator_juros_per', 'juros_per', 'saldo_dev_juros', 'pv', 'fv', 'mtm',
'puposicao', 'puposicao1', 'change', 'count_0', 'count_all', 'dif',
'spread'
]].copy()
tp_bigdif['dif'] = tp_bigdif['mtm'] / tp_bigdif['puposicao1'] - 1
tp_bigdif[(tp_bigdif.dif > tol) | (tp_bigdif.dif < -tol) |
(tp_bigdif.spread > tol) | (tp_bigdif.spread < -tol)].to_excel(
writer, 'bigdif')
logger.info(
"Atualização do fluxo de % de mtm com o spread e carregamento da tabela"
)
# 6 - Atualização do fluxo de percentual de mtm com o spread e carregamento da tabela para preenchimento do quadro 419
titprivado_perc = titprivado.copy()
titprivado_perc = titprivado_perc.rename(
columns={
'mtm': 'mtm_old',
'pv': 'pv_old',
'pv_DV100': 'pv_DV100_old',
'fator_desconto': 'fator_desconto_old',
'fator_desconto_DV100': 'fator_desconto_DV100_old',
'DV100': 'DV100_old'
})
# Escolhe o melhor spread - SIGNIFICA O MELHOR FLUXO
titprivado_perc['spread'] = titprivado_perc['spread'].fillna(0)
# Pega penas uma linha para não ter problemas
x = titprivado_perc[[
'id_papel', 'codigo_isin', 'spread'
]][titprivado_perc.dt_ref == titprivado_perc.data_referencia].copy()
x = x.sort(['codigo_isin', 'spread'], ascending=[True, True])
x = x.drop_duplicates(subset=['codigo_isin'], take_last=False)
x['marker'] = 1
titprivado_perc = titprivado_perc.merge(
x, on=['codigo_isin', 'id_papel', 'spread'], how='left')
titprivado_perc = titprivado_perc[titprivado_perc.marker == 1].copy()
del titprivado_perc['marker']
titprivado_perc = titprivado_perc[titprivado_perc.prazo_du >= 0]
# Recalcula apenas para que está marcado a mercado
aux = titprivado_perc[titprivado_perc.caracteristica == 'V'].copy()
aux['mtm_DV100_N'] = 0.0
aux = aux.rename(columns={'mtm_old': 'mtm'})
titprivado_perc = titprivado_perc[titprivado_perc.caracteristica ==
'N'].copy()
# Cálculo do fator de desconto atualizado pelo spread
titprivado_perc[
'fator_desconto'] = titprivado_perc['fator_desconto_old'] / (
1 + titprivado_perc['spread'])**(titprivado_perc['prazo_du'] / 252)
titprivado_perc['fator_desconto_DV100'] = titprivado_perc[
'fator_desconto_DV100_old'] / (1 + titprivado_perc['spread'])**(
titprivado_perc['prazo_du'] / 252)
# Calculo do pv
titprivado_perc[
'pv'] = titprivado_perc['fv'] * titprivado_perc['fator_desconto']
titprivado_perc['pv_DV100'] = titprivado_perc['fv'] * titprivado_perc[
'fator_desconto_DV100']
# Calculo do MTM
x = titprivado_perc[['codigo_isin', 'pv',
'pv_DV100']].groupby(['codigo_isin']).agg(['sum'])
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'mtm', 'mtm_DV100_N'])
x1['codigo_isin'] = x['codigo_isin']
x1['mtm'] = x['pv']
x1['mtm_DV100_N'] = x['pv_DV100']
titprivado_perc = titprivado_perc.merge(x1, on=['codigo_isin'], how='left')
titprivado_perc['mtm_DV100'] = 0.0
# Escolhe o melhor fluxo
titprivado_perc[
'dif_new'] = titprivado_perc['mtm'] - titprivado_perc['puposicao1']
titprivado_perc['dif_new'] = titprivado_perc['dif_new'].abs()
titprivado_perc[
'dif_old'] = titprivado_perc['mtm_old'] - titprivado_perc['puposicao1']
titprivado_perc['dif_old'] = titprivado_perc['dif_old'].abs()
titprivado_perc['mtm'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['mtm_old'], titprivado_perc['mtm'])
titprivado_perc['mtm_DV100'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['mtm_DV100'], titprivado_perc['mtm_DV100_N'])
titprivado_perc['pv'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['pv_old'], titprivado_perc['pv'])
titprivado_perc['pv_DV100'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['pv_DV100_old'], titprivado_perc['pv_DV100'])
titprivado_perc['fator_desconto'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['fator_desconto_old'],
titprivado_perc['fator_desconto'])
titprivado_perc['fator_desconto_DV100'] = np.where(
titprivado_perc['dif_old'] < titprivado_perc['dif_new'],
titprivado_perc['fator_desconto_DV100_old'],
titprivado_perc['fator_desconto_DV100'])
# Cálculo do DV100
titprivado_perc = titprivado_perc.append(aux)
titprivado_perc[
'DV100'] = titprivado_perc['mtm'] - titprivado_perc['mtm_DV100']
# Cálculo do perc_mtm
titprivado_perc[
'perc_mtm'] = titprivado_perc['pv'] / titprivado_perc['mtm']
titprivado_perc['perc_mtm'] = np.where(
titprivado_perc['caracteristica'] == 'V',
titprivado_perc['pv_old'] / titprivado_perc['mtm'],
titprivado_perc['perc_mtm'])
# Cálculo da duration
titprivado_perc[
'duration'] = titprivado_perc['perc_mtm'] * titprivado_perc['prazo_du']
x = titprivado_perc[['codigo_isin',
'duration']].groupby(['codigo_isin']).agg(['sum'])
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['codigo_isin', 'duration'])
x1['codigo_isin'] = x['codigo_isin']
x1['duration'] = x['duration']
del titprivado_perc['duration']
titprivado_perc = titprivado_perc.merge(x1, on=['codigo_isin'], how='left')
titprivado_perc[[
'codigo_isin', 'puposicao1', 'mtm', 'dt_ref', 'mtm_old', 'dif_new',
'dif_old', 'duration', 'spread'
]].to_excel(writer, 'conclusao_fluxo')
titprivado_perc[
titprivado_perc.dt_ref == titprivado_perc.data_referencia][[
'codigo_isin', 'puposicao1', 'mtm', 'dt_ref', 'mtm_old', 'dif_new',
'dif_old', 'duration', 'spread'
]].to_excel(writer, 'conclusao_resumido')
finalizacao = titprivado_perc[[
'codigo_isin', 'mtm'
]][titprivado_perc.dt_ref == titprivado_perc.data_referencia].copy()
del titprivado_perc['fator_desconto_DV100_old']
del titprivado_perc['fator_desconto_old']
del titprivado_perc['mtm_old']
del titprivado_perc['mtm_DV100_N']
del titprivado_perc['pv_DV100_old']
del titprivado_perc['pv_old']
del titprivado_perc['DV100_old']
del titprivado_perc['dif_new']
del titprivado_perc['dif_old']
writer.save()
# id_mtm_titprivado
titprivado_perc['id_mtm_titprivado'] = titprivado_perc[
'id_mtm_titprivado'].astype(int)
# id_bmf_numeraca
aux = titprivado_perc[[
'id_papel', 'id_bmf_numeraca'
]][titprivado_perc.id_bmf_numeraca.notnull()].copy()
aux['id_bmf_numeraca'] = aux['id_bmf_numeraca'].astype(int)
del titprivado_perc['id_bmf_numeraca']
aux = aux.drop_duplicates()
titprivado_perc = titprivado_perc.merge(aux, on=['id_papel'], how='left')
# pagto_amortizacao
aux = titprivado_perc[[
'id_papel', 'indexador_dc_du'
]][titprivado_perc.indexador_dc_du.notnull()].copy()
aux['indexador_dc_du'] = aux['indexador_dc_du'].astype(int)
del titprivado_perc['indexador_dc_du']
aux = aux.drop_duplicates()
titprivado_perc = titprivado_perc.merge(aux, on=['id_papel'])
# juros_cada
aux = titprivado_perc[['id_papel', 'juros_cada'
]][titprivado_perc.juros_cada.notnull()].copy()
aux['juros_cada'] = aux['juros_cada'].astype(int)
del titprivado_perc['juros_cada']
aux = aux.drop_duplicates()
titprivado_perc = titprivado_perc.merge(aux, on=['id_papel'], how='left')
# indexador_dc_du
aux = titprivado_perc[[
'id_papel', 'indexador_dc_du'
]][titprivado_perc.indexador_dc_du.notnull()].copy()
aux['indexador_dc_du'] = aux['indexador_dc_du'].astype(int)
del titprivado_perc['indexador_dc_du']
aux = aux.drop_duplicates()
titprivado_perc = titprivado_perc.merge(aux, on=['id_papel'])
# juros_dc_du
aux = titprivado_perc[['id_papel', 'juros_dc_du'
]][titprivado_perc.juros_dc_du.notnull()].copy()
aux['juros_dc_du'] = aux['juros_dc_du'].astype(int)
del titprivado_perc['juros_dc_du']
aux = aux.drop_duplicates()
titprivado_perc = titprivado_perc.merge(aux, on=['id_papel'])
# flag_inclusao
titprivado_perc['flag_inclusao'] = titprivado_perc['flag_inclusao'].astype(
int)
# du_per
titprivado_perc['du_per'] = titprivado_perc['du_per'].astype(int)
# dc_per
titprivado_perc['dc_per'] = titprivado_perc['dc_per'].astype(int)
# dt_ref -> data_fim
titprivado_perc['dt_ref'] = pd.to_datetime(titprivado_perc['dt_ref'])
titprivado_perc['data_fim'] = np.where(
titprivado_perc['indexador'] == 'DI1',
titprivado_perc['dt_ref'] - pd.DateOffset(months=0, days=1),
titprivado_perc['dt_ref'])
titprivado_perc['id_papel'] = titprivado_perc['id_papel_old']
titprivado_perc['data_mtm'] = titprivado_perc['data_referencia']
titprivado_perc['data_negociacao'] = titprivado_perc['data_referencia']
# Tabelas não necessárias - MTM
del titprivado_perc['data_referencia']
del titprivado_perc['id_papel_old']
del titprivado_perc['indice_du_mtm']
del titprivado_perc['indice_dc_mtm']
del titprivado_perc['ano_dt_ref2']
del titprivado_perc['mes_dt_ref2']
del titprivado_perc['dia_dt_ref2']
del titprivado_perc['vertices_positivo']
del titprivado_perc['indice_dc_dt_ref2']
del titprivado_perc['indice_du_dt_ref2']
del titprivado_perc['prazo_dc']
del titprivado_perc['ano_inicio']
del titprivado_perc['mes_inicio']
del titprivado_perc['dia_inicio']
del titprivado_perc['indice_du_inicio']
del titprivado_perc['indice_dc_inicio']
del titprivado_perc['ano_fim']
del titprivado_perc['mes_fim']
del titprivado_perc['dia_fim']
del titprivado_perc['indice_du_fim']
del titprivado_perc['indice_dc_fim']
del titprivado_perc['dt_ref']
del titprivado_perc['dtoperacao_mtm']
del titprivado_perc['dif']
del titprivado_perc['dif_mtm']
del titprivado_perc['dif_curva']
del titprivado_perc['pupar']
del titprivado_perc['count_all']
del titprivado_perc['count_0']
del titprivado_perc['count_dif']
del titprivado_perc['change']
del titprivado_perc['puposnew']
del titprivado_perc['puposicao1']
del titprivado_perc['pu_mercado']
del titprivado_perc['pu_curva']
del titprivado_perc['mtm_mercado']
del titprivado_perc['mtm_curva']
del titprivado_perc['pu_regra_xml']
del titprivado_perc['mtm_regra_xml']
# Tabelas não necessárias - XML
del titprivado_perc['id_xml_titprivado']
del titprivado_perc['isin']
del titprivado_perc['codativo']
del titprivado_perc['dtemissao']
del titprivado_perc['dtoperacao']
del titprivado_perc['dtvencimento']
del titprivado_perc['cnpjemissor']
del titprivado_perc['qtdisponivel']
del titprivado_perc['qtgarantia']
del titprivado_perc['pucompra']
del titprivado_perc['puvencimento']
del titprivado_perc['puposicao']
del titprivado_perc['puemissao']
del titprivado_perc['principal']
del titprivado_perc['tributos']
del titprivado_perc['valorfindisp']
del titprivado_perc['valorfinemgar']
del titprivado_perc['coupom']
del titprivado_perc['percindex']
del titprivado_perc['caracteristica']
del titprivado_perc['percprovcred']
del titprivado_perc['classeoperacao']
del titprivado_perc['idinternoativo']
del titprivado_perc['nivelrsc']
del titprivado_perc['header_id']
del titprivado_perc['cusip']
del titprivado_perc['depgar']
# Remove as duplicatas de isin
titprivado_perc = titprivado_perc.sort(
['codigo_isin', 'id_papel', 'data_fim'], ascending=[True, True, True])
titprivado_perc = titprivado_perc.drop_duplicates(
subset=['codigo_isin', 'data_fim'])
# titprivado_perc['data_bd'] = horario_bd
titprivado_perc['data_bd'] = datetime.datetime.today()
titprivado_perc = titprivado_perc.where((pd.notnull(titprivado_perc)),
None)
titprivado_perc.to_excel(save_path_titprivado_perc)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados - mtm_renda_fixa")
pd.io.sql.to_sql(titprivado_perc,
name='mtm_renda_fixa',
con=connection,
if_exists='append',
flavor='mysql',
index=0)
#Fecha conexão
connection.close()
logger.info("Preenchimento tabela xml")
# 6 - Preenchimento tabela xml
del original['id_xml_titprivado']
del original['pu_mercado']
del original['mtm_mercado']
del original['pu_curva']
del original['mtm_curva']
del original['pu_regra_xml']
del original['mtm_regra_xml']
del original['data_referencia']
del titprivado['mtm']
titprivado = titprivado.merge(finalizacao, on=['codigo_isin'], how='left')
titprivado_xml = titprivado[titprivado.dt_ref ==
titprivado.data_referencia].copy()
titprivado_xml = titprivado_xml.rename(columns={'mtm': 'mtm_calculado'})
titprivado_xml['pu_mercado'] = np.where(
titprivado_xml['caracteristica'] == 'N',
titprivado_xml['mtm_calculado'], 0)
titprivado_xml['pu_curva'] = np.where(
titprivado_xml['caracteristica'] == 'V', titprivado_xml['pupar'], 0)
titprivado_xml = titprivado_xml[[
'id_papel', 'pu_mercado', 'pu_curva', 'data_referencia'
]].copy()
final = original.merge(titprivado_xml, on=['id_papel'], how='left')
final['pu_mercado'] = np.where(
(final['pu_mercado'].isnull()) | (final['pu_mercado'] == 0),
final['puposicao'], final['pu_mercado'])
final['mtm_mercado'] = final['pu_mercado'] * (final['qtdisponivel'] +
final['qtgarantia'])
final['pu_curva'] = np.where(final['pu_curva'].isnull(),
final['puposicao'], final['pu_curva'])
final['mtm_curva'] = final['pu_curva'] * (final['qtdisponivel'] +
final['qtgarantia'])
final['pu_regra_xml'] = np.where(final['caracteristica'] == 'N',
final['pu_mercado'], final['pu_curva'])
final['mtm_regra_xml'] = np.where(final['caracteristica'] == 'N',
final['mtm_mercado'], final['mtm_curva'])
final['data_bd'] = datetime.datetime.today()
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados - xml_titprivado")
pd.io.sql.to_sql(final,
name='xml_titprivado',
con=connection,
if_exists='append',
flavor='mysql',
index=0)
#Fecha conexão
connection.close()
def relatorio_risco_credito():
import datetime
import pandas as pd
import pymysql as db
import numpy as np
import openpyxl
import logging
from openpyxl.styles import Font, Color, Border, Side, PatternFill, Alignment
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
global header_id_carteira_fundos
logger = logging.getLogger(__name__)
dt_base = get_data_ultimo_dia_util_mes_anterior()
dt_base = dt_base[0]+'-'+dt_base[1]+'-'+dt_base[2]
nivel_confianca=int(get_global_var("nivel_confianca_relatorio_credito"))
end = full_path_from_database("get_output_var")+'relatorios/'
cnpj_hdi=get_global_var("cnpj_hdi")
def relatorio_credito(dt_base,cnpj,nivel_confianca,horario_bd):
global percentil
global header_id_carteira
global header_nome
global cnpj_fundo
global administrador
global gestor
global quadro_oper
global pl_info
global tp
global id_relatorio_qo
global pl_credito
global perc_credito
global pe
global duration_carteira
global rtg_lp
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(end+cnpj+"_"+dt_base+' relatorio_credito.xlsx', engine='xlsxwriter')
workbook = writer.book
numero_float = workbook.add_format({'num_format': '#,##0', 'bold': False})
percent = workbook.add_format({'num_format': '0.0%', 'bold': False})
percentil=nivel_confianca
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv', use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query='select * from projeto_inv.xml_header where cnpjcpf="' + cnpj +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(query, con=connection)
if len(df)==0:
query='select * from projeto_inv.xml_header where cnpj="' + cnpj +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(query, con=connection)
df=df.sort(['cnpj', 'cnpjcpf','data_bd'], ascending=[True, True, False])
df=df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df=df.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
del df['index']
header_id_carteira=df.get_value(0,'header_id').astype(str)
header_nome=df.get_value(0,'nome')
cnpj_fundo=cnpj
administrador=df.get_value(0, 'nomeadm')
gestor=df.get_value(0, 'nomegestor')
query='select a.* from projeto_inv.xml_quadro_operacoes a right join (select header_id, max(data_bd) as data_bd from projeto_inv.xml_quadro_operacoes where header_id='+header_id_carteira+' group by 1) b on a.header_id=b.header_id and a.data_bd=b.data_bd;'
quadro_oper=pd.read_sql(query, con=connection)
pl=quadro_oper[quadro_oper['produto']!='Futuro']
pl_info=sum(pl['mtm_info'])
#selecionar crédito
tp=quadro_oper.loc[quadro_oper['produto'].isin(['título privado','debênture'])]
#id_relatorio_qo
id_relatorio_qo=quadro_oper['id_relatorio_qo'].iloc[0]
id_relatorio_qo_str=str(id_relatorio_qo)
if len(tp)>0:
# fidc
fundos=quadro_oper.loc[quadro_oper['produto'].isin(['fundo'])].copy()
fundos['fundo_final']=np.where(fundos['fundo_ult_nivel'].isnull(),fundos['fundo'],fundos['fundo_ult_nivel'])
#INCLUIR FIDCS
fidc=fundos[fundos['fundo_final'].str.contains('FIDC|DIREITOS|CREDITÓRIO|CREDITORIOS|DIREITOS')]
tp=tp.append(fidc)
pl_credito=sum(tp['mtm_info'])
perc_credito=pl_credito/pl_info
# incluir tipo de papel
query= "select distinct codigo_isin, data_bd, tipo_ativo from projeto_inv.bmf_numeraca where tipo_ativo in ('DBS', 'LFI', 'LFN', 'DP', 'C', 'CC','CCC', 'CRI');"
caracteristica=pd.read_sql(query, con=connection)
caracteristica=caracteristica.sort(['codigo_isin', 'data_bd'], ascending=[True, False])
caracteristica=caracteristica.drop_duplicates(subset=['codigo_isin'], take_last=False)
del caracteristica['data_bd']
tp=pd.merge(tp, caracteristica, left_on='isin', right_on='codigo_isin', how='left')
tp.ix[tp.tipo_ativo == 'CC', 'tipo_prod']='CCB'
tp.ix[tp.tipo_ativo == 'CCC', 'tipo_prod']='CCCB'
tp.ix[tp.tipo_ativo =='DBS', 'tipo_prod']='Debênture'
tp.ix[tp.tipo_ativo =='C', 'tipo_prod']='CDB'
tp.ix[tp.tipo_ativo =='LFI', 'tipo_prod']='LF'
tp.ix[tp.tipo_ativo =='LFN', 'tipo_prod']='LF'
tp.ix[tp.tipo_ativo =='DP', 'tipo_prod']='DPGE'
tp.ix[tp.produto =='fundo', 'tipo_prod']='FIDC'
tp['tipo_prod']=tp['tipo_prod'].fillna('Outros')
tp['contraparte']=np.where(tp.produto=='fundo',tp.fundo_final, tp.contraparte)
tp['cnpj_fundo_final']=np.where((tp.produto=='fundo') & (tp.cnpjfundo_outros.isnull()),tp.cnpjfundo_1nivel,tp.cnpjfundo_outros)
tp['cnpj']=np.where(tp.produto=='fundo',tp.cnpj_fundo_final,tp.cnpj)
del tp['codigo_isin']
#informação de emissor (contraparte)
query='select distinct nome_emissor, cnpj_emissor, data_criacao_emissor from projeto_inv.bmf_emissor where cnpj_emissor>0;'
emissor=pd.read_sql(query, con=connection)
emissor=emissor.sort(['cnpj_emissor', 'data_criacao_emissor'], ascending=[True, False])
emissor1=emissor.drop_duplicates(subset=['cnpj_emissor'], take_last=False)
emissor1['cnpj']=emissor1['cnpj_emissor'].astype(float)
emissor2=emissor1[['cnpj', 'nome_emissor']]
tp=pd.merge(tp, emissor2, left_on='cnpj', right_on='cnpj', how='left')
tp['contraparte']=tp['contraparte'].fillna(tp['nome_emissor'])
del tp['nome_emissor']
tp['contraparte']=tp['contraparte'].fillna('a')
# selecionar simulação de perda de crédito
#query='select a.* from projeto_inv.simulacao_credito a right join (select id_relatorio_qo, max(data_bd) as data_bd from projeto_inv.simulacao_credito where id_relatorio_qo = "51" group by 1) b on a.id_relatorio_qo=b.id_relatorio_qo and a.data_bd=b.data_bd;'
query ='SELECT contraparte, isin, pv, perda_perc, pv_perda, duration, dt_base FROM projeto_inv.simulacao_credito_consolidado;'
sim=pd.read_sql(query,con=connection)
sim['perda_perc']=sim['perda_perc'].fillna(0)
# perda esperada por isin
perda_isin=sim[['isin', 'perda_perc']].copy()
#perda_isin=perda_isin.groupby(['isin'], as_index=False).mean()
perda_isin=perda_isin.rename(columns={'perda_perc':'perc_pe'})
tp_isin_mtm=tp[['cnpj','contraparte','isin','tipo_prod', 'mtm_info']].copy()
tp_isin_mtm=tp_isin_mtm.groupby(['cnpj','contraparte','isin','tipo_prod'],as_index=False).sum()
tp_isin_mtm=pd.merge(tp_isin_mtm, perda_isin, left_on='isin', right_on='isin', how='left')
tp_isin_mtm['perda_esperada'] = tp_isin_mtm['perc_pe']*tp_isin_mtm['mtm_info']
tp_isin_mtm['perda_esperada']=tp_isin_mtm['perda_esperada'].fillna(0)
# perda esperada da carteira
perda=sum(tp_isin_mtm['perda_esperada'])
pe=perda/pl_credito
pe_carteira=pe*pl_credito/pl_info
#composiçao da carteira - por tipo de produto
prod=tp_isin_mtm[['tipo_prod', 'mtm_info', 'perda_esperada']]
prod=prod.groupby(['tipo_prod'], as_index=False).sum()
prod['perc_credito']=prod['mtm_info']/pl_credito
prod['perc_pl']=prod['mtm_info']/pl_info
prod['perc_pe']=prod['perda_esperada']/prod['mtm_info']
del prod['perda_esperada']
df_aux = pd.DataFrame(columns=['Dados Institucionais'])
df_aux.to_excel(writer,index=False, sheet_name='Dados Institucionais', startrow =1, startcol=1, header =['Dados Institucionais'])
prod.to_excel(writer,index=False, sheet_name='Resumo', startrow =14, startcol=1, header =['Tipo de produto', 'Exposição (R$)', '% PL de crédito', '% PL total', '% Perda Esperada'])
# perda inesperada
sim1=sim[['isin', 'pv_perda', 'pv']]
isin_mtm=tp[['isin','mtm_info']].copy()
isin_mtm=isin_mtm.groupby(['isin'],as_index=False).sum()
sim2=pd.merge(sim1,isin_mtm, left_on='isin', right_on='isin', how='left' )
sim2['perc_perda']=sim2['pv_perda']/sim2['pv']
sim2['perda']=sim2['perc_perda']*sim2['mtm_info']
del sim2['perc_perda']
del sim2['pv']
del sim2['pv_perda']
sim_agr = sim2
sim_agr['perda']=sim_agr['perda'].fillna(0)
sim_agr['perc_perda']=sim_agr['perda']/sim_agr['mtm_info']
sim_agr=sim_agr.sort(columns=['perc_perda'], ascending=True )
sim_agr=sim_agr.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
n_conf= np.floor(len(sim_agr)*percentil)
percentil_conf=sim_agr['perc_perda'][n_conf-1]
# Relatório só a MERCADO - cálculo de duration
query='select a.* from projeto_inv.quaid_419 a right join (select id_relatorio_qo, tipo_relatorio, max(data_bd) as data_bd from projeto_inv.quaid_419 where id_relatorio_qo='+id_relatorio_qo_str+' and tipo_relatorio="R" group by 1,2) b on a.id_relatorio_qo=b.id_relatorio_qo and a.tipo_relatorio=b.tipo_relatorio and a.data_bd=b.data_bd;'
quaid_419_r=pd.read_sql(query, con=connection)
quaid_419_r['expo']=np.where(quaid_419_r['TPFOPERADOR']=='-',-1*quaid_419_r['EMFVLREXPRISCO'],quaid_419_r['EMFVLREXPRISCO'] )
quaid_419_r['prazo_ponderado']=quaid_419_r['expo']*quaid_419_r['EMFPRAZOFLUXO']
isin_credito=tp[['isin']].copy()
isin_credito=isin_credito.drop_duplicates(subset=['isin'], take_last=True)
quaid_419_credito=pd.merge(isin_credito,quaid_419_r, left_on=['isin'], right_on=['EMFCODISIN'], how='left' )
duration=quaid_419_credito[['EMFCODISIN','expo','prazo_ponderado']].copy()
duration_ativa=duration[duration.expo>=0].copy()
duration_ativa_papel=duration_ativa.groupby('EMFCODISIN', as_index=False).sum()
duration_ativa_papel['duration']=duration_ativa_papel['prazo_ponderado']/duration_ativa_papel['expo']
#duration carteira
duration_c=quaid_419_credito[['EMFPRAZOFLUXO','expo']].copy()
duration_c1=duration_c.groupby(['EMFPRAZOFLUXO'], as_index=False).sum()
duration_c1['prazo_ponderado']=duration_c1['expo']*duration_c1['EMFPRAZOFLUXO']
duration_carteira=sum(duration_c1['prazo_ponderado'])/sum(duration_c1['expo'])
#rating da carteira
query='select a.rtg, a.prazo, a.pd_acum from projeto_inv.pd_acum a right join (select max(data_bd) as data_bd from projeto_inv.pd_acum) b on a.data_bd=b.data_bd;'
regua_unica=pd.read_sql(query, con=connection)
regua_unica['pd_acum_shift']=regua_unica['pd_acum'].shift()
regua_unica['prazo_shift']=regua_unica['prazo'].shift()
regua_unica['pd_acum_min']=np.where(regua_unica['prazo_shift']==regua_unica['prazo'],regua_unica['pd_acum_shift'],0)
del regua_unica['prazo_shift']
del regua_unica['pd_acum_shift']
duration_mes=np.floor(duration_carteira/22)
if duration_mes>60:
duration_carteira_temp=60
else:
duration_carteira_temp=np.floor(duration_mes)
rtg_carteira=regua_unica[regua_unica['prazo']==duration_carteira_temp].copy()
rtg_carteira1=rtg_carteira[rtg_carteira['pd_acum_min']<=pe_carteira]
rtg_carteira2=rtg_carteira1[rtg_carteira1['pd_acum']>pe_carteira]
rtg_carteira2=rtg_carteira2.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
rtg_lp=rtg_carteira2.get_value(0,'rtg')
# rating por produto
query='select cod_rtg, rtg from projeto_inv.de_para_rtg a right join (select max(data_bd) as data_bd from projeto_inv.de_para_rtg) b on a.data_bd = b.data_bd;'
depara=pd.read_sql(query, con=connection)
#regua mestra
query='select distinct a.cod_rtg, a.agencia_rtg, a.rtg from projeto_inv.de_para_rtg a right join (select agencia_rtg, max(data_bd) as data_bd from projeto_inv.de_para_rtg where agencia_rtg="regua" group by 1) b on a.agencia_rtg=b.agencia_rtg and a.data_bd = b.data_bd;'
regua_rtg=pd.read_sql(query, con=connection)
del regua_rtg['agencia_rtg']
#rating por isin
query='select distinct a.isin, a.agencia_tipo_rtg, a.rtg from projeto_inv.rating_isin as a right join (select max(data_bd) as data_bd from projeto_inv.rating_isin where dt_ref= "'+ dt_base +'" ) as b on a.data_bd=b.data_bd;'
rtg_isin=pd.read_sql(query, con=connection)
rtg_local=rtg_isin.loc[rtg_isin['agencia_tipo_rtg'].isin(['RTG_MDY_NSR', 'RTG_MDY_NSR_SR_UNSECURED', 'RTG_MDY_NSR_SUBORDINATED','RTG_SP_NATIONAL','RTG_FITCH_NATIONAL_LT', 'RTG_FITCH_NATIONAL', 'RTG_FITCH_NATIONAL_SR_UNSECURED', 'RTG_FITCH_NATL_SUBORDINATED'])]
rtg_local=pd.merge(rtg_local, depara, left_on='rtg', right_on='rtg', how='left')
rtg_pior=rtg_local[['isin', 'cod_rtg']].copy()
rtg_pior=rtg_pior.groupby(['isin'],as_index=False).max()
rtg_pior=pd.merge(rtg_pior, regua_rtg, left_on='cod_rtg', right_on='cod_rtg', how='left')
#rating por contraparte
query='select distinct a.cnpj, a.agencia_tipo_rtg, a.rtg from projeto_inv.rating_contraparte as a right join (select max(data_bd) as data_bd from projeto_inv.rating_contraparte where dt_ref= "'+ dt_base +'" ) as b on a.data_bd=b.data_bd;'
rtg_c=pd.read_sql(query, con=connection)
rtg_c_local=rtg_c.loc[rtg_c['agencia_tipo_rtg'].isin(['RTG_MDY_NSR_ISSUER','RTG_SP_NATIONAL_LT_ISSUER_CREDIT','RTG_FITCH_NATIONAL_LT','RTG_FITCH_NATIONAL_SR_UNSECURED' ])]
rtg_c_local=pd.merge(rtg_c_local, depara, left_on='rtg', right_on='rtg', how='left')
rtg_c_pior=rtg_c_local[['cnpj', 'cod_rtg']].copy()
rtg_c_pior=rtg_c_pior.groupby(['cnpj'], as_index=False).max()
rtg_c_pior=pd.merge(rtg_c_pior, regua_rtg, left_on='cod_rtg', right_on='cod_rtg', how='left')
#agregar o rtg na base
tp_cnpj=pd.merge(tp_isin_mtm, rtg_pior, left_on='isin', right_on='isin', how='left')
tp_cnpj=tp_cnpj.rename(columns={'cod_rtg':'cod_rtg_isin', 'rtg':'rtg_isin'})
tp_cnpj=pd.merge(tp_cnpj, rtg_c_pior, left_on='cnpj', right_on='cnpj', how='left')
tp_cnpj=tp_cnpj.rename(columns={'cod_rtg':'cod_rtg_cnpj', 'rtg':'rtg_cnpj'})
tp_cnpj['cod_rtg']=np.where(tp_cnpj['cod_rtg_isin'].isnull(),tp_cnpj.cod_rtg_cnpj,tp_cnpj.cod_rtg_isin)
del tp_cnpj['cod_rtg_isin']
del tp_cnpj['cod_rtg_cnpj']
del tp_cnpj['rtg_isin']
del tp_cnpj['rtg_cnpj']
tp_cnpj=pd.merge(tp_cnpj, regua_rtg, left_on='cod_rtg', right_on='cod_rtg', how='left')
##assumir rtg padrão missing: 'Aa3' e cod_rtg=4
tp_cnpj['cod_rtg']=tp_cnpj['cod_rtg'].fillna(3)
tp_cnpj['rtg']=tp_cnpj['rtg'].fillna('Aa2')
sumario_rtg=tp_cnpj[['cod_rtg','rtg', 'mtm_info', 'perda_esperada']]
sumario_rtg['mtm_info']=sumario_rtg['mtm_info'].fillna(0)
sumario_rtg['perda_esperada']=sumario_rtg['perda_esperada'].fillna(0)
sumario=sumario_rtg.groupby(['cod_rtg','rtg'], as_index=False).sum()
sumario['perc_credito']= sumario['mtm_info']/pl_credito
sumario['perc_tot']= sumario['mtm_info']/pl_info
sumario['perc_pe']=sumario['perda_esperada']/sumario['mtm_info']
del sumario['cod_rtg']
del sumario['perda_esperada']
sumario.to_excel(writer,index=False, sheet_name='Resumo', startrow =25, startcol=1, header =['Rating', 'Exposição (R$)', '% PL de crédito', '% PL total', '% Perda Esperada'])
worksheet = writer.sheets['Resumo']
worksheet.set_column('D:F', 12, percent)
worksheet.set_column('C:C', 12, numero_float)
# 20 maiores exposiçoes por contraparte
contraparte=tp_cnpj[['cnpj', 'contraparte', 'mtm_info', 'perda_esperada']].copy()
contraparte=contraparte.groupby(['cnpj', 'contraparte'], as_index=False).sum()
contraparte['perc_exposicao']=contraparte['mtm_info']/pl_credito
del contraparte['cnpj']
contraparte=contraparte.sort(['mtm_info'], ascending=False)
contraparte=contraparte.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
contraparte['perc_pe']=contraparte['perda_esperada']/contraparte['mtm_info']
del contraparte['perda_esperada']
del contraparte['index']
contraparte1=contraparte[0:20]
contraparte1.to_excel(writer,index=False, sheet_name='Maiores Exposições', startrow =3, startcol=1, header =['Contraparte', 'Exposição', '% PL de crédito', '% Perda Esperada'])
worksheet = writer.sheets['Maiores Exposições']
worksheet.set_column('D5:E24', 12, percent)
worksheet.set_column('C5:C24', 12, numero_float)
# 20 maiores exposiçoes por produto
prod=tp_isin_mtm[['contraparte','isin' , 'tipo_prod', 'mtm_info', 'perda_esperada']]
prod=prod.groupby(['contraparte', 'isin', 'tipo_prod'], as_index=False).sum()
prod['perc_credito']=prod['mtm_info']/pl_credito
prod['perc_pe']=prod['perda_esperada']/prod['mtm_info']
prod=prod.sort(['mtm_info'], ascending=False)
prod=prod.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
del prod['perda_esperada']
del prod['index']
prod2=prod[0:20]
prod2.to_excel(writer,index=False, sheet_name='Maiores Exposições', startrow =28, startcol=1, header =['Contraparte','Isin', 'Tipo de Produto','Exposição', '% PL de crédito', '% Perda Esperada'])
worksheet = writer.sheets['Maiores Exposições']
worksheet.set_column('F32:G51', 12, percent)
worksheet.set_column('E32:E51', 12, numero_float)
writer.save()
# inserir caracteristicas
wb = openpyxl.load_workbook(end+cnpj+"_"+dt_base+' relatorio_credito.xlsx')
#####FORMATOS
#Fonte
fontObj1=Font(name='Calibri', bold=True, size =24,color='404040')
fontObj2=Font(name='Calibri', bold=False, size =11,color='404040')
#Borda
borderObj1=Border(bottom=Side(border_style='double'),top=Side(border_style='thin'))
borderObj2=Border()
#Cor
colorObj1=PatternFill(patternType='solid', fgColor=Color('FFE600'))
#Alinhamento
alinObj1=Alignment(vertical='center',horizontal='center')
alinObj2=Alignment(vertical='center',horizontal='left')
alinObj3=Alignment(vertical='center',horizontal='right')
#####Dados institucionais
# #####Resumo
sheet1=wb.get_sheet_by_name('Dados Institucionais')
#Retira as gridlines
sheet1.sheet_view.showGridLines = False
#Formatação tamanho das linhas
sheet1.row_dimensions[1].height = 90
#Formata cor da fonte de todas as células
for row in sheet1.range('B2:C20'):
for cell in row:
cell.font=fontObj2
#Formata o título
sheet1.merge_cells('B2:C2')
sheet1['B2']='Dados Institucionais'
sheet1['B2'].font=fontObj1
sheet1['B2'].alignment=alinObj2
for row in sheet1.range('B2:C2'):
for cell in row:
cell.border=borderObj1
#Cria a parte de informações institucionais e resumo do relatório de crédito
sheet1['B4']='Nome'
sheet1['C4']=header_nome
sheet1['B5']='CNPJ'
sheet1['C5']=cnpj_fundo
sheet1['B6']='Administrador'
sheet1['C6']=administrador
sheet1['B7']='Gestor'
sheet1['C7']=gestor
#Formatação tamanho das colunas
sheet1.column_dimensions['A'].width = 2
sheet1.column_dimensions['B'].width = 15
sheet1.column_dimensions['C'].width = 100
####Resumo
sheet1=wb.get_sheet_by_name('Resumo')
#Retira as gridlines
sheet1.sheet_view.showGridLines = False
#Formatação tamanho das linhas
sheet1.row_dimensions[1].height = 90
#Formata cor da fonte de todas as células
for row in sheet1.range('B2:F100'):
for cell in row:
cell.font=fontObj2
#Formata o título
sheet1.merge_cells('B2:F2')
sheet1['B2']='Relatório Quantitativo de Risco de Crédito'
sheet1['B2'].font=fontObj1
for row in sheet1.range('B2:F2'):
for cell in row:
cell.border=borderObj1
sheet1['B4']='Nível de Confiança'
sheet1['C4']=percentil
sheet1['B5']='PL do fundo (R$)'
sheet1['C5']=pl_info
sheet1['B6']='PL de crédito (R$)'
sheet1['C6']=pl_credito
sheet1['B7']='Duration - Crédito (DU)'
sheet1['C7']=duration_carteira
sheet1['B8']='% de crédito privado'
sheet1['C8']=pl_credito/pl_info
sheet1['B9']='Perda Esperada (em relação ao PL crédito)'
sheet1['C9']=pe
sheet1['B10']='Percentil da Perda ('+str(percentil)+')'
sheet1['C10']=percentil_conf
sheet1['B11']='Rating médio da carteira'
sheet1['C11']=rtg_lp
sheet1['B12']='Perda Esperada (em relação ao PL)'
sheet1['C12']=pe_carteira
#Formata os headers da tabela
for row in sheet1.range('B15:F15'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formata os headers da tabela
for row in sheet1.range('B26:F26'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formata os formatos de número
for row in sheet1.range('C8:C10'):
for cell in row:
cell.number_format = "0.0%"
cell.alignment=alinObj3
sheet1['C4'].number_format = "0.00"
#Formatação tamanho das colunas
sheet1.column_dimensions['A'].width = 2
sheet1.column_dimensions['B'].width = 40
sheet1.column_dimensions['C'].width = 25
sheet1.column_dimensions['D'].width = 25
sheet1.column_dimensions['E'].width = 25
sheet1.column_dimensions['F'].width = 25
####Maiores exposições
sheet1=wb.get_sheet_by_name('Maiores Exposições')
#Retira as gridlines
sheet1.sheet_view.showGridLines = False
#Formatação tamanho das linhas
sheet1.row_dimensions[1].height = 90
#Formata cor da fonte de todas as células
for row in sheet1.range('B2:G100'):
for cell in row:
cell.font=fontObj2
#Formata o título
sheet1.merge_cells('B2:G2')
sheet1['B2']='20 Maiores Exposições por Contraparte'
sheet1['B2'].font=fontObj1
for row in sheet1.range('B2:G2'):
for cell in row:
cell.border=borderObj1
#Formata os headers da tabela
for row in sheet1.range('B4:G4'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formata os headers da tabela
for row in sheet1.range('B29:G29'):
for cell in row:
cell.fill=colorObj1
cell.border=borderObj2
#Formata os formatos de número
for row in sheet1.range('E5:E24'):
for cell in row:
cell.number_format = "0.0%"
#Formatação tamanho das colunas
sheet1.column_dimensions['A'].width = 2
sheet1.column_dimensions['B'].width = 100
sheet1.column_dimensions['C'].width = 20
sheet1.column_dimensions['D'].width = 20
sheet1.column_dimensions['E'].width = 20
sheet1.column_dimensions['F'].width = 20
sheet1.column_dimensions['G'].width = 20
wb.save(end+cnpj+"_"+dt_base+' relatorio_credito.xlsx')
else:
cnpj_sem_credito = pd.DataFrame(columns=['cnpj','id_relatorio_qo','data_bd'])
cnpj_sem_credito['cnpj'] = cnpj
cnpj_sem_credito['id_relatorio_qo'] = id_relatorio_qo_str
cnpj_sem_credito['data_bd'] = horario_bd
pd.io.sql.to_sql(cnpj_sem_credito, name='rel_credito_log', con=connection,if_exists="append", flavor='mysql', index=0, chunksize=5000)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost', user='******', passwd='root', db='projeto_inv', use_unicode=True, charset="utf8")
logger.info("Conexão com DB executada com sucesso")
#Busca lista dos fundos de primeiro nivel na carteira da HDI
query='select * from projeto_inv.xml_header where cnpjcpf="' + cnpj_hdi +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(query, con=connection)
if len(df)==0:
x='select * from projeto_inv.xml_header where cnpj="' + cnpj_hdi +'" and dtposicao='+'"'+dt_base+'";'
df=pd.read_sql(x, con=connection)
df=df.sort(['cnpj', 'cnpjcpf','data_bd'], ascending=[True, True, False])
df=df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df=df.reset_index(level=None, drop=False, inplace=False, col_level=0, col_fill='')
del df['index']
header_id_carteira_fundos=df.get_value(0,'header_id').astype(str) #Utiliza o header da carteira da HDI como chave para a query da lista
lista_query='SELECT cnpj from projeto_inv.lista_fundos where data_bd=(select max(data_bd) from projeto_inv.lista_fundos where header_id="'+header_id_carteira_fundos+'");'
lista_cnpj=pd.read_sql(lista_query, con=connection)
lista=lista_cnpj['cnpj'].tolist()
horario_bd = datetime.datetime.today()
for cnpj in lista:
relatorio_credito(dt_base,cnpj,nivel_confianca,horario_bd)
#def simulacao_credito():
import pandas as pd
import pymysql as db
import numpy as np
import math
import logging
import random
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from findt import FinDt
feriados_sheet = full_path_from_database(
'feriados_nacionais') + 'feriados_nacionais.csv'
cnpj_hdi = get_global_var("cnpj_hdi")
logger = logging.getLogger(__name__)
dt_base = get_data_ultimo_dia_util_mes_anterior()
dt_base = dt_base[0] + '-' + dt_base[1] + '-' + dt_base[2]
#qtde_simulacao = 3000
qtde_simulacao = 1
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'select distinct nome_emissor, cnpj_emissor, data_criacao_emissor from projeto_inv.bmf_emissor where cnpj_emissor>0;'
emissor = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
def get_anbima_titpublico(ano, mes, dia):
"""
Objetivo: robô de títulos públicos federais da base disponibilizada pela ANBIMA
Exemplo de preenchimento:
robo_tit_publico('2016','02','23')
"""
import urllib
import pandas as pd
import pymysql as db
import datetime
import logging
from pandas.tseries.offsets import DateOffset
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
### Conexão com a página ###
#paginaTitulosPublicos = urllib.request.urlopen("http://www.anbima.com.br/merc_sec/arqs/ms160215.txt")
paginaTitulosPublicos = urllib.request.urlopen(
get_global_var("paginaTitulosPublicos") + str(ano)[2:4] + str(mes) +
str(dia) + ".txt")
logger.info("Conexão com URL executado com sucesso")
#fonte anbima='anb'
anbima_tpf = pd.read_table(paginaTitulosPublicos, sep='@', header=1)
logger.info("Leitura da página executada com sucesso")
logger.info("Tratando dados")
#renomear colunas
old_names = [
'Index', 'Titulo', 'Data Referencia', 'Codigo SELIC',
'Data Base/Emissao', 'Data Vencimento', 'Tx. Compra', 'Tx. Venda',
'Tx. Indicativas', 'PU', 'Desvio padrao', 'Interv. Ind. Inf. (D0)',
'Interv. Ind. Sup. (D0)', 'Interv. Ind. Inf. (D+1)',
'Interv. Ind. Sup. (D+1)', 'Criterio'
]
new_names = [
'Index', 'titulo', 'dt_referencia', 'cod_selic', 'dt_emissao',
'dt_vencto', 'tx_maxima', 'tx_minima', 'tx_indicativa', 'pu',
'desv_pad', 'inter_min_d0', 'inter_max_d0', 'inter_min_d1',
'inter_max_d1', 'criterio'
]
anbima_tpf.rename(columns=dict(zip(old_names, new_names)), inplace=True)
#alterar formato de datas
anbima_tpf['dt_referencia'] = pd.to_datetime(anbima_tpf['dt_referencia'],
format='%Y%m%d')
anbima_tpf['dt_emissao'] = pd.to_datetime(anbima_tpf['dt_emissao'],
format='%Y%m%d')
anbima_tpf['dt_vencto'] = pd.to_datetime(anbima_tpf['dt_vencto'],
format='%Y%m%d')
# DATA DE VENCIMENTO -> EXCLUSIVE
anbima_tpf['dt_vencto2'] = anbima_tpf['dt_vencto'] - DateOffset(months=0,
days=1)
'''
anbima_tpf['dt_referencia']=anbima_tpf['dt_referencia'].apply(lambda x: x.strftime('%d-%m-%Y'))
anbima_tpf['dt_emissao']=anbima_tpf['dt_emissao'].apply(lambda x: x.strftime('%d-%m-%Y'))
anbima_tpf['dt_vencto']=anbima_tpf['dt_vencto'].apply(lambda x: x.strftime('%d-%m-%Y'))
anbima_tpf['dt_vencto2']=anbima_tpf['dt_vencto2'].apply(lambda x: x.strftime('%d-%m-%Y'))
'''
# CONVERSAO DE , PARA . TRATAMENTO DE EXCECOES NUMERICAS E DE MISSING DATA
anbima_tpf = anbima_tpf.replace({',': '.'}, regex=True)
anbima_tpf = anbima_tpf.replace({' ': ''}, regex=True)
anbima_tpf = anbima_tpf.replace({'--': None}, regex=True)
#conversao para numerico
anbima_tpf['tx_maxima'] = anbima_tpf['tx_maxima'].astype(float)
anbima_tpf['tx_minima'] = anbima_tpf['tx_minima'].astype(float)
anbima_tpf['tx_indicativa'] = anbima_tpf['tx_indicativa'].astype(float)
anbima_tpf['pu'] = anbima_tpf['pu'].astype(float)
anbima_tpf['desv_pad'] = anbima_tpf['desv_pad'].astype(float)
anbima_tpf['inter_min_d0'] = anbima_tpf['inter_min_d0'].astype(float)
anbima_tpf['inter_max_d0'] = anbima_tpf['inter_max_d0'].astype(float)
anbima_tpf['inter_min_d1'] = anbima_tpf['inter_min_d1'].astype(float)
anbima_tpf['inter_max_d1'] = anbima_tpf['inter_max_d1'].astype(float)
horario_bd = datetime.datetime.now()
anbima_tpf["dt_carga"] = horario_bd
logger.info("Conectando no Banco de dados")
# Conexão com Banco de Dados
connection = db.connect('localhost',
user='******',
passwd="root",
db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando dados no DB")
#Salvar no MySQL
pd.io.sql.to_sql(anbima_tpf,
name='anbima_tpf',
con=connection,
if_exists='append',
flavor='mysql',
index=0)
logging.info("Dados salvos no DB com sucesso - anbima_tpf")
# Fecha conexão
connection.close()
def get_anbima_debentures(ano, mes, dia):
import urllib
import pandas as pd
import datetime
import pymysql as db
import numpy as np
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
pagina_debentures_anbima = get_global_var(
"pagina_debentures_anbima") + ano[2:] + mes + dia + ".txt"
# Pega o .txt e joga em uma variável
pagina_debentures = urllib.request.urlopen(pagina_debentures_anbima)
logger.info("Conexão com URL executado com sucesso")
# Parsea os dados com o pandas
dados_debentures = pd.read_table(pagina_debentures,
sep='@',
header=1,
encoding="iso-8859-1")
logger.info("Leitura da página executada com sucesso")
## Criar coluna com data_bd para a data de inserção no BD
logger.info("Tratando dados")
horario_bd = datetime.datetime.now()
dados_debentures["data_bd"] = horario_bd
## Padronizar nomes das colunas
dados_debentures.columns = [
"codigo", "nome", "repac_vencimento", "indice_correcao", "taxa_compra",
"taxa_venda", "taxa_indicativa", "desvio_padrao",
"intervalo_indicativo_minimo", "intervalo_indicativo_maximo", "pu",
"perc_pu_par", "duration", "perc_reunie", "referencia_ntnb", "data_bd"
]
## Trocar virgula por ponto e "--" por None
dados_debentures = dados_debentures.replace({np.nan: None}, regex=True)
dados_debentures = dados_debentures.replace({',': '.'}, regex=True)
dados_debentures = dados_debentures.replace({'--': None}, regex=True)
dados_debentures = dados_debentures.replace({'N/D': None}, regex=True)
# Converter formato data
dados_debentures["repac_vencimento"] = pd.to_datetime(
dados_debentures["repac_vencimento"], format="%d/%m/%Y")
dados_debentures["referencia_ntnb"] = pd.to_datetime(
dados_debentures["referencia_ntnb"], format="%d/%m/%Y")
# Colocar data de referência
dados_debentures["data_referencia"] = ano + '-' + mes + '-' + dia
dados_debentures["data_referencia"] = pd.to_datetime(
dados_debentures["data_referencia"], format="%Y-%m-%d")
dados_debentures = dados_debentures.reset_index(level=None,
drop=True,
inplace=False,
col_level=0,
col_fill='')
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
pd.io.sql.to_sql(dados_debentures,
name='anbima_debentures',
con=connection,
if_exists="append",
flavor='mysql',
index=0)
logger.info("Dados salvos no DB com sucesso - Tabela anbima_debentures")
# Fecha conexão com o banco de dados
connection.close()
def get_bmf_curvas_historico():
import pandas as pd
import pymysql as db
import datetime
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
logger = logging.getLogger(__name__)
# Retorna um array (ano, mes e dia) referente ao útimo dia útil do mês anterior configurado no banco de dados
dtbase = get_data_ultimo_dia_util_mes_anterior()
logger.info("Tratando dados")
# Retorna um range de datas do mes anterior
dt_ref = pd.date_range(start=datetime.date(int(dtbase[0]), int(dtbase[1]),
1),
end=datetime.date(int(dtbase[0]), int(dtbase[1]),
int(dtbase[2])),
freq='D').date
# Para cada dia de referência do mês anterior, puxa as informações do site da bolsa e inseri no banco
for i in dt_ref:
print("Executando para o dia: " + str(i))
dia = str(i.day)
mes = str(i.month)
ano = str(i.year)
if len(mes) == 1:
mes = "0" + mes
if len(dia) == 1:
dia = "0" + dia
lista_curvas = [
"ACC", "ALD", "AN", "ANP", "APR", "BRP", "CBD", "DCO", "DIC",
"DIM", "DOC", "DOL", "DP", "EUC", "EUR", "IAP", "INP", "IPR",
"JPY", "LIB", "NID", "PBD", "PDN", "PRE", "PTX", "SDE", "SLP",
"SND", "TFP", "TP", "TR", "ZND"
]
# Cria gabarito da tabela definitiva das curvas e também tabela aux2 que é pré-gabarito
matriz_curvas = pd.DataFrame()
aux2 = pd.DataFrame()
for j in range(0, 5):
matriz_curvas[j] = 0
aux2[j] = 0
matriz_curvas.columns = ['Prazo', '252', '360', 'Valor', 'Codigo']
aux2.columns = ['Prazo', '252', '360', 'Valor', 'Codigo']
# Acesso aos dados da página
try:
for curva in lista_curvas:
# curva = lista_curvas[0]
endereco_curvas = get_global_var(
endereco_curvas
) + dia + "/" + mes + "/" + ano + "&slcTaxa=" + curva
dados_curvas = pd.read_html(endereco_curvas, thousands=".")
if not (
len(dados_curvas)
) <= 2: # checa se essa curva tem dados, por meio do numero de linhas do dataframe, se <2, nao tem dados
# Copia o dataframe para a tabela auxiliar
aux = dados_curvas[2]
# Na tabela auxiliar, verifica-se se possui os 2 campos '252', '360', ou apenas 1 campo
# Faz-se uma copia das colunas de 'aux' para 'aux2', que é será adicionado (append) a matriz_curvas
if (len(aux.columns)) < 3:
if "252" in aux[0][1]:
aux = aux.drop(
[0, 1]
) # Elimina as 2 primeiras linhas (nao utilizaveis)
aux2['Prazo'] = aux[0]
aux2['252'] = aux[1]
elif "360" in aux[0][1]:
aux = aux.drop(
[0, 1]
) # Elimina as 2 primeiras linhas (nao utilizaveis)
aux2['Prazo'] = aux[0]
aux2['360'] = aux[1]
else:
aux = aux.drop(
[0, 1]
) # Elimina as 2 primeiras linhas (nao utilizaveis)
aux2['Prazo'] = aux[0]
aux2['Valor'] = aux[1]
else:
aux = aux.iloc[2:len(
aux
), :] # Elimina as 2 primeiras linhas (nao utilizaveis)
aux2['Prazo'] = aux[0]
aux2['252'] = aux[1]
aux2['360'] = aux[2]
aux2['Codigo'] = curva
matriz_curvas = matriz_curvas.append(aux2)
for i in range(2, len(aux2) + 2):
aux2 = aux2.drop(i)
except:
pass
horario_bd = datetime.datetime.now()
matriz_curvas["data_bd"] = horario_bd
ano = int(ano)
mes = int(mes)
dia = int(dia)
matriz_curvas["data_ref"] = datetime.date(ano, mes, dia)
# troca , .
matriz_curvas = matriz_curvas.replace({',': '.'}, regex=True)
# troca NaN por None
matriz_curvas = matriz_curvas.where((pd.notnull(matriz_curvas)), None)
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados - Tabela bmf_curvas")
pd.io.sql.to_sql(matriz_curvas,
name='bmf_curvas_' + str(ano) + '_' + str(mes),
con=connection,
if_exists="append",
flavor='mysql',
index=0)
connection.close()
def get_bmf_precos_futuros(ano, mes, dia):
import pandas as pd
import pymysql as db
import datetime
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
logger = logging.getLogger(__name__)
#Robo BMF PREÇOS FUTUROS
dataBusca = dia + "/" + mes + "/" + ano
endereco_bmf = get_global_var("endereco_bmf")
endereco_data = endereco_bmf + dataBusca
dadospd = pd.read_html(endereco_data, thousands=".")
logger.info("Leitura da página executada com sucesso")
logger.info("Tratando dados")
#Pegar a última tabela
dadospd = dadospd[len(dadospd) - 1]
#Mudar nome tabela
dadospd.columns = dadospd.iloc[0, :]
#Tirar primeira linha
dadospd = dadospd.iloc[1:len(dadospd), :]
#Padronizar nome "Mercadorias"
for i in range(len(dadospd["Mercadoria"])):
dadospd["Mercadoria"].iloc[i] = str(dadospd["Mercadoria"].iloc[i])
if dadospd["Mercadoria"].iloc[i] == 'nan':
dadospd["Mercadoria"].iloc[i] = dadospd["Mercadoria"].iloc[i - 1]
#Padronizar nomes das colunas
dadospd.columns = [
"mercadoria", "vencimento", "preco_ajuste_anterior",
"preco_ajuste_atual", "variacao", "valor_ajuste_por_contrato"
]
# Trocar virgula por ponto
lista_virgula = [
"preco_ajuste_anterior", "preco_ajuste_atual", "variacao",
"valor_ajuste_por_contrato"
]
for coluna in lista_virgula:
dadospd[coluna] = [
str(linha).replace(".", "") for linha in dadospd[coluna]
]
dadospd = dadospd.replace({',': '.'}, regex=True)
# Criar coluna com data_referencia
dadospd["data_referencia"] = ano + mes + dia
## Criar coluna com data_bd para a data de inserção no BD
horario_bd = datetime.datetime.now()
dadospd["data_bd"] = horario_bd
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv')
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
# Salvar na base de dados
pd.io.sql.to_sql(dadospd,
name='bmf_ajustes_pregao',
con=connection,
if_exists="append",
flavor='mysql',
index=0)
logger.info("Dados salvos no DB com sucesso - Tabela bmf_ajustes_pregao")
#Fecha conexão
connection.close()
def simulacao_credito():
import pandas as pd
import pymysql as db
import numpy as np
import math
import logging
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from dependencias.Metodos.funcoes_auxiliares import get_data_ultimo_dia_util_mes_anterior
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from findt import FinDt
feriados_sheet = full_path_from_database(
'feriados_nacionais') + 'feriados_nacionais.csv'
cnpj_hdi = get_global_var("cnpj_hdi")
logger = logging.getLogger(__name__)
dt_base = get_data_ultimo_dia_util_mes_anterior()
dt_base = dt_base[0] + '-' + dt_base[1] + '-' + dt_base[2]
#qtde_simulacao = 3000
qtde_simulacao = 1
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'select distinct nome_emissor, cnpj_emissor, data_criacao_emissor from projeto_inv.bmf_emissor where cnpj_emissor>0;'
emissor = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
emissor = emissor.sort(['cnpj_emissor', 'data_criacao_emissor'],
ascending=[True, False])
emissor1 = emissor.drop_duplicates(subset=['cnpj_emissor'],
take_last=False)
emissor1['cnpj'] = emissor1['cnpj_emissor'].astype(float)
emissor1 = emissor1.rename(columns={'nome_emissor': 'contraparte'})
emissor2 = emissor1[['cnpj', 'contraparte']]
#seleção da carteira
def quadro_oper(dt_base, cnpj):
#Global Variables:
global start
global header_id_carteira
global quadro_oper
global tp
global tp_expo
global tp_fluxo
global id_relatorio_qo
start = dt_base
logger.info("Conectando no Bancode dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
query = 'select * from projeto_inv.xml_header_org where cnpjcpf="' + cnpj + '" and dtposicao=' + '"' + dt_base + '";'
df = pd.read_sql(query, con=connection)
if len(df) == 0:
query = 'select * from projeto_inv.xml_header_org where cnpj="' + cnpj + '" and dtposicao=' + '"' + dt_base + '";'
df = pd.read_sql(query, con=connection)
df = df.sort(['cnpj', 'cnpjcpf', 'data_bd'],
ascending=[True, True, False])
df = df.drop_duplicates(subset=['cnpj', 'cnpjcpf'], take_last=False)
df = df.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
del df['index']
header_id_carteira = df.get_value(0, 'header_id').astype(str)
# quadro de operaçao
query = 'select a.* from projeto_inv.xml_quadro_operacoes a right join (select header_id, max(data_bd) as data_bd from projeto_inv.xml_quadro_operacoes where header_id=' + header_id_carteira + ' group by 1) b on a.header_id=b.header_id and a.data_bd=b.data_bd;'
quadro_oper = pd.read_sql(query, con=connection)
logger.info("Leitura do banco de dados executada com sucesso")
tp = quadro_oper.loc[quadro_oper['produto'].isin(
['título privado', 'debênture'])]
id_relatorio_qo = tp['id_relatorio_qo'][0]
# fidc
fundos = quadro_oper.loc[quadro_oper['produto'].isin(['fundo'])].copy()
fundos['fundo_final'] = np.where(fundos['fundo_ult_nivel'].isnull(),
fundos['fundo'],
fundos['fundo_ult_nivel'])
#INCLUIR FIDCS
fidc = fundos[fundos['fundo_final'].str.contains(
'FIDC|DIREITOS|CREDITÓRIO|CREDITORIOS|DIREITOS')]
tp = tp.append(fidc)
tp['contraparte'] = np.where(tp.produto == 'fundo', tp.fundo_final,
tp.contraparte)
tp['cnpj_fundo_final'] = np.where(
(tp.produto == 'fundo') & (tp.cnpjfundo_outros.isnull()),
tp.cnpjfundo_1nivel, tp.cnpjfundo_outros)
tp['cnpj'] = np.where(tp.produto == 'fundo', tp.cnpj_fundo_final,
tp.cnpj)
# pegar exposicao mercado
tp['expo_final'] = np.where(tp['caracteristica'] != 'N',
tp['pu_mercado'] * tp['quantidade'],
tp['mtm_info'])
tp['expo_final'] = np.where(tp['expo_final'] == 0, tp['mtm_info'],
tp['expo_final'])
tp['expo_final'] = np.where(tp.produto == 'fundo', tp['mtm_info'],
tp['expo_final'])
# incluir data de vencimento de fidc
query = 'select distinct codigo_isin, dtvencimento, data_bd, max(data_ref) as dt_ref from projeto_inv.mtm_renda_fixa group by 1,2,3; '
dfvencto = pd.read_sql(query, con=connection)
dfvencto = dfvencto.sort_values(by=['codigo_isin', 'data_bd'],
ascending=[True, False])
dfvencto1 = dfvencto.drop_duplicates(subset=['codigo_isin'],
take_last=False)
dfvencto1['dt_vencto_fim'] = np.where(dfvencto1.dtvencimento.isnull(),
dfvencto1.dt_ref,
dfvencto1.dtvencimento)
base_vencto = dfvencto1[['codigo_isin', 'dt_vencto_fim']].copy()
tp = pd.merge(tp,
base_vencto,
left_on='isin',
right_on='codigo_isin',
how='left')
tp['dt_vencto_1'] = np.where(tp.dt_vencto.isnull(), tp.dt_vencto_fim,
tp.dt_vencto)
del tp['codigo_isin']
del tp['dt_vencto']
del tp['dt_vencto_fim']
tp = tp.rename(columns={'dt_vencto_1': 'dt_vencto'})
tp_mtm = tp[[
'expo_final', 'quantidade', 'isin', 'cnpj', 'produto', 'dt_vencto',
'fundo_final'
]].copy()
tp_mtm = pd.merge(tp_mtm,
emissor2,
left_on='cnpj',
right_on='cnpj',
how='left')
tp_mtm['contraparte'] = tp_mtm['contraparte'].fillna(
tp_mtm['fundo_final'])
del tp_mtm['fundo_final']
tp_expo = tp_mtm.groupby(
['isin', 'cnpj', 'contraparte', 'produto', 'dt_vencto'],
as_index=False).sum()
tp_expo = tp_expo.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
tp_expo['cnpj'] = tp_expo['cnpj'].astype(float)
tp_expo['cnpj1'] = ""
for i in range(0, len(tp_expo)):
if tp_expo['cnpj'][i] > 0:
tp_expo['cnpj1'][i] = math.floor(tp_expo['cnpj'][i])
#i=i+1
tp_expo['cnpj2'] = tp_expo['cnpj1'].astype(str).str.zfill(14)
del tp_expo['cnpj']
del tp_expo['cnpj1']
tp_expo = tp_expo.rename(columns={'cnpj2': 'cnpj'})
# fluxos
query = 'select a.codigo_isin, a.fv, a.data_ref, a.indexador, a.data_mtm, a.data_bd from projeto_inv.mtm_renda_fixa a right join (select codigo_isin, data_mtm, max(data_bd) as data_bd from projeto_inv.mtm_renda_fixa where data_mtm="' + dt_base + '" group by 1,2) b on a.codigo_isin=b.codigo_isin and a.data_mtm=b.data_mtm and a.data_bd=b.data_bd;'
fluxos = pd.read_sql(query, con=connection)
tp_fluxo = pd.merge(tp_expo,
fluxos,
left_on='isin',
right_on='codigo_isin')
del tp_fluxo['data_bd']
tp_fluxo['fv1'] = abs(tp_fluxo['fv'] * tp_fluxo['quantidade'])
del tp_fluxo['fv']
tp_fluxo = tp_fluxo.rename(columns={'fv1': 'fv'})
del tp_fluxo['codigo_isin']
tp_fluxo = tp_fluxo.reindex(
columns=['isin', 'data_ref', 'indexador', 'fv'])
#Fecha conexão
connection.close()
quadro_oper(dt_base, cnpj_hdi)
fluxo_original = tp_fluxo.copy()
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
# LGD
query = 'select a.produto, a.lgd from projeto_inv.lgd a right join (select produto, max(data_bd) as data_bd from projeto_inv.lgd group by 1) b on a.produto=b.produto and a.data_bd=b.data_bd;'
lgd = pd.read_sql(query, con=connection)
#simulação de cenário____________________________________________________________________________
query = 'SELECT n.* FROM projeto_inv.curva_ettj_interpol n INNER JOIN ( SELECT indexador_cod, month(dt_ref ) as mes, year (dt_ref) as ano, max(dt_ref) as max FROM projeto_inv.curva_ettj_interpol GROUP BY 1,2,3) a on a.indexador_cod=n.indexador_cod and a.max=n.dt_ref;'
cen = pd.read_sql(query, con=connection)
cen = cen.sort(['indexador_cod', 'prazo', 'dt_ref', 'data_bd'],
ascending=[True, True, True, False])
cen1 = cen.drop_duplicates(subset=['indexador_cod', 'prazo', 'dt_ref'],
take_last=False)
cen1['indexador_shift'] = cen1['indexador_cod'].shift()
cen1['prazo_shift'] = cen1['prazo'].shift()
cen1['tx_spot_shift'] = cen1['tx_spot'].shift()
cen1['dif'] = np.where((cen1.indexador_cod == cen1.indexador_shift) &
(cen1.prazo == cen1.prazo_shift),
cen1.tx_spot - cen1.tx_spot_shift, 'NaN')
cen2 = cen1[cen1.dif != 'NaN']
#________________________________________________________________________________________________________
# GERACAO DE SERIE DE DIAS ÚTEIS E DIAS CORRIDOS
ano = start[0:4]
mes = start[5:7]
dia = start[8:10]
dt_inicio = dia + '-' + mes + '-' + ano
dt_max = max(tp_expo['dt_vencto'])
per = FinDt.DatasFinanceiras(dt_inicio,
dt_max,
path_arquivo=feriados_sheet)
du = pd.DataFrame(columns=['data_ref'])
dc = pd.DataFrame(columns=['data_ref'])
dc['data_ref'] = per.dias()
dc['flag_dc'] = 1
du['data_ref'] = per.dias(3)
du['flag_du'] = 1
serie_dias = pd.merge(dc,
du,
left_on=['data_ref'],
right_on=['data_ref'],
how='left')
serie_dias['flag_du'] = serie_dias['flag_du'].fillna(0)
serie_dias['indice_dc'] = np.cumsum(serie_dias['flag_dc'])
serie_dias['indice_du'] = np.cumsum(serie_dias['flag_du'])
del serie_dias['flag_du']
del serie_dias['flag_dc']
# OBS: a contagem de dias é o valor do índice menos um
#PD e matriz de migracao________________________________
query = "select * from projeto_inv.pd_mes;"
pd_mes = pd.read_sql(query, con=connection)
pd_mes = pd_mes.sort(['rtg', 'produto', 'prazo', 'data_bd'],
ascending=[True, True, True, False])
pd_mes = pd_mes.drop_duplicates(subset=['rtg', 'produto', 'prazo'],
take_last=False)
query = "select * from projeto_inv.matriz_migr_mes;"
matriz_mes = pd.read_sql(query, con=connection)
matriz_mes = matriz_mes.sort(['cod_rtg_de', 'cod_rtg_para', 'data_bd'],
ascending=[True, True, False])
matriz_mes = matriz_mes.drop_duplicates(
subset=['cod_rtg_de', 'cod_rtg_para'], take_last=False)
#TIPO DE PAPEL
query = "select distinct codigo_isin, data_bd, tipo_ativo from projeto_inv.bmf_numeraca where tipo_ativo in ('DBS', 'LFI', 'LFN', 'DP', 'C', 'CC','CCC', 'CRI');"
caracteristica = pd.read_sql(query, con=connection)
caracteristica = caracteristica.sort(['codigo_isin', 'data_bd'],
ascending=[True, False])
caracteristica = caracteristica.drop_duplicates(subset=['codigo_isin'],
take_last=False)
del caracteristica['data_bd']
df_original = pd.merge(tp_expo,
caracteristica,
left_on='isin',
right_on='codigo_isin',
how='left')
df_original.ix[df_original.tipo_ativo == 'CC', 'tipo_pd'] = 'ccb'
df_original.ix[df_original.tipo_ativo == 'CCC', 'tipo_pd'] = 'ccb'
df_original.ix[df_original.tipo_ativo == 'DBS', 'tipo_pd'] = 'deb'
df_original.ix[df_original.tipo_ativo == 'C', 'tipo_pd'] = 'cdb'
df_original.ix[df_original.tipo_ativo == 'LFI', 'tipo_pd'] = 'cdb'
df_original.ix[df_original.tipo_ativo == 'LFN', 'tipo_pd'] = 'cdb'
df_original.ix[df_original.tipo_ativo == 'DP', 'tipo_pd'] = 'cdb'
df_original['tipo_pd'] = df_original['tipo_pd'].fillna('fidc')
del df_original['codigo_isin']
#RATING________________________________
query = 'select cod_rtg, rtg from projeto_inv.de_para_rtg a right join (select max(data_bd) as data_bd from projeto_inv.de_para_rtg) b on a.data_bd = b.data_bd;'
depara = pd.read_sql(query, con=connection)
#regua mestra
query = 'select distinct a.cod_rtg, a.agencia_rtg, a.rtg from projeto_inv.de_para_rtg a right join (select agencia_rtg, max(data_bd) as data_bd from projeto_inv.de_para_rtg where agencia_rtg="regua" group by 1) b on a.agencia_rtg=b.agencia_rtg and a.data_bd = b.data_bd;'
regua_rtg = pd.read_sql(query, con=connection)
del regua_rtg['agencia_rtg']
#rating por isin
query = 'select distinct a.isin, a.agencia_tipo_rtg, a.rtg from projeto_inv.rating_isin as a right join (select max(data_bd) as data_bd from projeto_inv.rating_isin where dt_ref= "' + dt_base + '" ) as b on a.data_bd=b.data_bd;'
rtg_isin = pd.read_sql(query, con=connection)
rtg_local = rtg_isin.loc[rtg_isin['agencia_tipo_rtg'].isin([
'RTG_MDY_NSR', 'RTG_MDY_NSR_SR_UNSECURED', 'RTG_MDY_NSR_SUBORDINATED',
'RTG_SP_NATIONAL', 'RTG_FITCH_NATIONAL_LT', 'RTG_FITCH_NATIONAL',
'RTG_FITCH_NATIONAL_SR_UNSECURED', 'RTG_FITCH_NATL_SUBORDINATED'
])]
rtg_local = pd.merge(rtg_local,
depara,
left_on='rtg',
right_on='rtg',
how='left')
rtg_pior = rtg_local[['isin', 'cod_rtg']].copy()
rtg_pior = rtg_pior.groupby(['isin'], as_index=False).max()
rtg_pior = pd.merge(rtg_pior,
regua_rtg,
left_on='cod_rtg',
right_on='cod_rtg',
how='left')
#rating por contraparte
query = 'select distinct a.cnpj, a.agencia_tipo_rtg, a.rtg from projeto_inv.rating_contraparte as a right join (select max(data_bd) as data_bd from projeto_inv.rating_contraparte where dt_ref= "' + dt_base + '" ) as b on a.data_bd=b.data_bd;'
rtg_c = pd.read_sql(query, con=connection)
rtg_c_local = rtg_c.loc[rtg_c['agencia_tipo_rtg'].isin([
'RTG_MDY_NSR_ISSUER', 'RTG_SP_NATIONAL_LT_ISSUER_CREDIT',
'RTG_FITCH_NATIONAL_LT', 'RTG_FITCH_NATIONAL_SR_UNSECURED'
])]
rtg_c_local = pd.merge(rtg_c_local,
depara,
left_on='rtg',
right_on='rtg',
how='left')
rtg_c_pior = rtg_c_local[['cnpj', 'cod_rtg']].copy()
rtg_c_pior = rtg_c_pior.groupby(['cnpj'], as_index=False).max()
rtg_c_pior = pd.merge(rtg_c_pior,
regua_rtg,
left_on='cod_rtg',
right_on='cod_rtg',
how='left')
#agregar o rtg na base
df_original = pd.merge(df_original,
rtg_pior,
left_on='isin',
right_on='isin',
how='left')
df_original = df_original.rename(columns={
'cod_rtg': 'cod_rtg_isin',
'rtg': 'rtg_isin'
})
df_original = pd.merge(df_original,
rtg_c_pior,
left_on='cnpj',
right_on='cnpj',
how='left')
df_original = df_original.rename(columns={
'cod_rtg': 'cod_rtg_cnpj',
'rtg': 'rtg_cnpj'
})
df_original['cod_rtg'] = np.where(df_original['cod_rtg_isin'].isnull(),
df_original.cod_rtg_cnpj,
df_original.cod_rtg_isin)
del df_original['cod_rtg_isin']
del df_original['cod_rtg_cnpj']
del df_original['rtg_isin']
del df_original['rtg_cnpj']
df_original = pd.merge(df_original,
regua_rtg,
left_on='cod_rtg',
right_on='cod_rtg',
how='left')
#assumir rtg padrão missing: 'Aa3' e cod_rtg=4
df_original['cod_rtg'] = df_original['cod_rtg'].fillna(3)
df_original['rtg'] = df_original['rtg'].fillna('Aa2')
df_original = df_original.rename(columns={'rtg': 'rating'})
#Fecha conexão
connection.close()
#inicio da simulacao
def simulacao(id_simulacao):
global df
global fluxo
global df_resultado
global i_tempo
df = df_original.copy()
fluxo = fluxo_original.copy()
#criar a serie de tempo mensal - para simulacao
end = max(df['dt_vencto'])
end = end + pd.DateOffset(months=2)
inicio = pd.to_datetime(start) + pd.DateOffset(months=1)
tempo = pd.DataFrame()
tempo['serie'] = pd.date_range(inicio, end, freq='M')
tempo['serie'] = tempo['serie'].dt.date
df = df.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
df['dt_base'] = start
#primeira rodada
df['rtg'] = df['rating']
fim = pd.DataFrame()
#### simulacao
i_tempo = 0
while len(df) > 0:
np.random.seed(1234 + i_tempo + id_simulacao)
df['aleat_matriz'] = np.random.uniform(0, 1, len(df))
df['aleat_pd'] = np.random.uniform(0, 1, len(df))
df_matriz = pd.merge(df,
matriz_mes,
left_on=['rtg'],
right_on=['rtg_de'],
how='left')
df_rtg = df_matriz[
(df_matriz['prob_ini'] < df_matriz['aleat_matriz'])
& (df_matriz['aleat_matriz'] <= df_matriz['prob_fim'])]
df_rtg['rtg'] = df_rtg['rtg_para']
df_rtg['dt_ref'] = tempo['serie'][i_tempo]
# prazo=1 -> simulação mensal
df_rtg['prazo'] = 1
df_rtg1 = pd.merge(df_rtg,
pd_mes,
left_on=['tipo_pd', 'prazo', 'rtg'],
right_on=['produto', 'prazo', 'rtg'],
how='left')
df_rtg1['default'] = np.where(df_rtg1.pd_mensal > df_rtg1.aleat_pd,
1, 0)
df_rtg_mes = df_rtg1[[
'isin', 'cnpj', 'contraparte', 'dt_vencto', 'tipo_ativo',
'tipo_pd', 'rating', 'rtg', 'dt_ref', 'prazo', 'dt_base',
'pd_mensal', 'default'
]].copy()
df_rtg_mes['break'] = np.where(
(df_rtg_mes['default'] == 1) |
(pd.to_datetime(df_rtg_mes['dt_vencto']) <= pd.to_datetime(
df_rtg_mes['dt_ref'])), 1, 0)
default_break = df_rtg_mes[df_rtg_mes['break'] == 1].copy()
nao_default = df_rtg_mes[~(df_rtg_mes['break'] == 1)].copy()
fim = fim.append(default_break)
df = nao_default[[
'isin', 'cnpj', 'contraparte', 'dt_vencto', 'tipo_ativo',
'tipo_pd', 'rating', 'rtg', 'dt_base'
]].copy()
i_tempo = i_tempo + 1
fim = fim.rename(columns={'dt_ref': 'dt_default'})
fim['lgd'] = 0
base_default = fim[fim['default'] == 1].copy()
base_nao_default = fim[fim['default'] == 0].copy()
base_default = base_default.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
del base_default['index']
for i_lgd in range(0, len(base_default)):
np.random.seed(123 + i_lgd + id_simulacao)
lgd_base_0 = lgd[lgd['produto'] == base_default['tipo_pd'][i_lgd]]
lgd_base = lgd_base_0.copy()
lgd_base['aleat_lgd'] = np.random.uniform(0, 1, len(lgd_base))
lgd_base = lgd_base.sort(['aleat_lgd'])
lgd_base = lgd_base.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
lgd_perc = lgd_base.get_value(0, 'lgd')
base_default.loc[i_lgd, 'lgd'] = lgd_perc
##VALIDAR ESTA ITERAÇÃO:
#i_lgd=i_lgd+1
df_fim = base_nao_default.append(base_default)
df_fluxo = pd.merge(df_fim,
fluxo,
left_on=['isin'],
right_on=['isin'],
how='left')
df_fluxo = pd.merge(df_fluxo,
serie_dias,
left_on=['data_ref'],
right_on=['data_ref'],
how='left')
df_fluxo = df_fluxo.rename(columns={
'indice_du': 'du_ref',
'indice_dc': 'dc_ref'
})
df_fluxo = pd.merge(df_fluxo,
serie_dias,
left_on=['dt_default'],
right_on=['data_ref'],
how='left')
del df_fluxo['data_ref_y']
df_fluxo = df_fluxo.rename(
columns={
'indice_du': 'du_default',
'indice_dc': 'dc_default',
'data_ref_x': 'data_ref'
})
#___________________________CENARIO_______________________________________________
base_dt = pd.DataFrame(columns=['dt', 'aleat'])
base_dt['dt'] = cen2['dt_ref'].unique()
base_dt['aleat'] = np.random.uniform(0, 1, len(base_dt))
base_dt = base_dt.sort(['aleat'])
base_dt = base_dt.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
base_dt = base_dt[['dt', 'aleat']]
dt_max = max(base_dt.dt)
base_ref_0 = cen2[cen2.dt_ref == dt_max]
base_ref = base_ref_0.copy()
del base_ref['dif']
dt = base_dt.get_value(0, 'dt')
#erro
erro_0 = cen2[cen2.dt_ref == dt]
erro = erro_0.copy()
erro = erro[['indexador_cod', 'prazo', 'dt_ref', 'dif']]
del erro['dt_ref']
base_ref = pd.merge(base_ref,
erro,
how='left',
left_on=['indexador_cod', 'prazo'],
right_on=['indexador_cod', 'prazo'])
base_ref['tx_spot_sim'] = base_ref['tx_spot'].astype(
float) + base_ref['dif'].astype(float)
del base_ref['indexador_shift']
del base_ref['prazo_shift']
del base_ref['tx_spot_shift']
del base_ref['dif']
#--fluxo e cenário
df_fluxo['indexador_cod'] = ""
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'DI1',
'CDI', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'CDI',
'CDI', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IAP',
'DIC', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IPC',
'DIC', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IGM',
'DIM', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'PRE',
'PRE', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'TR', 'TP',
df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IGP',
'DIM', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IPCA',
'DIC', df_fluxo['indexador_cod'])
df_fluxo['indexador_cod'] = np.where(df_fluxo.indexador == 'IGPM',
'DIM', df_fluxo['indexador_cod'])
cenario = base_ref[['prazo', 'indexador_cod', 'tx_spot_sim']].copy()
df_fluxo_sim = pd.merge(df_fluxo,
cenario,
left_on=['du_ref', 'indexador_cod'],
right_on=['prazo', 'indexador_cod'],
how='left')
df_fluxo_sim['tx_spot_sim'] = df_fluxo_sim['tx_spot_sim'].fillna(0)
df_fluxo_sim['pv'] = df_fluxo_sim['fv'] / (1 +
df_fluxo_sim['tx_spot_sim'])
df_pv = df_fluxo_sim[['isin', 'pv']]
df_pv['pv'] = df_pv['pv'].fillna(0)
df_tot = df_pv.groupby(['isin'], as_index=False).sum()
df_fluxo_perda = df_fluxo_sim[
(pd.to_datetime(df_fluxo_sim['data_ref']) >= pd.to_datetime(
df_fluxo_sim['dt_default'])) & (df_fluxo_sim['default'] == 1)]
df_perda = df_fluxo_perda[['isin', 'tipo_ativo', 'lgd', 'pv']].copy()
df_perda['pv'] = df_perda['pv'].fillna(0)
df_perda_tot = df_perda.groupby(['isin', 'tipo_ativo', 'lgd'],
as_index=False).sum()
df_perda_tot['recuperacao'] = np.where(df_perda_tot.tipo_ativo == 'DP',
20000000, 0)
df_perda_tot['pv_perda'] = (df_perda_tot['pv'] * df_perda_tot['lgd'] -
df_perda_tot['recuperacao']).clip(0, None)
df_perda_tot1 = df_perda_tot[['isin', 'pv_perda']].copy()
df_resultado = pd.merge(df_tot,
df_perda_tot1,
left_on=['isin'],
right_on=['isin'],
how='left')
df_resultado['pv_perda'] = df_resultado['pv_perda'].fillna(0)
df_resultado[
'perda_perc'] = df_resultado['pv_perda'] / df_resultado['pv']
df_resultado['perda_perc'] = df_resultado['perda_perc'].fillna(0)
df_resultado['dt_base_sim'] = start
df_resultado['numero_simulacao'] = id_simulacao
df_resultado['data_bd'] = hoje
df_resultado['id_relatorio_qo'] = id_relatorio_qo
df_resultado['pv_perda'] = df_resultado['pv_perda'].fillna(0)
#Salvar no MySQL
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
pd.io.sql.to_sql(df_resultado,
name='simulacao_credito',
con=connection,
if_exists='append',
flavor='mysql',
index=0)
#Fecha conexão
connection.close()
hoje = pd.datetime.today()
for numero_sim in range(0, qtde_simulacao):
simulacao(numero_sim)
def var_gerencial(id_relatorio_quaid419, relatorio_tipo, dtbase):
import pandas as pd
import numpy as np
import datetime
import pymysql as db
import re
import logging
import math
from pandas import ExcelWriter
from scipy.stats import norm
from dependencias.Metodos.funcoes_auxiliares import full_path_from_database
from dependencias.Metodos.funcoes_auxiliares import get_global_var
from var.scripts.relatorio_encadeado.var_gerencial.value_at_risk import value_at_risk
matriz_id = get_global_var("matriz_id_var_gerencial")
fator_confianca = float(get_global_var("fator_confianca"))
horizonte_em_dias = 1
t = math.sqrt(horizonte_em_dias)
logger = logging.getLogger(__name__)
#Diretório de save de planilhas
save_path = full_path_from_database("get_output_var")
writer = ExcelWriter(save_path + '/var_' + dtbase + '.xlsx')
fc_normal = norm.ppf(fator_confianca)
fc_stress = norm.pdf(fc_normal) / (1 - fator_confianca)
if relatorio_tipo == "estressado":
fc_normal = fc_stress
horario = datetime.datetime.now()
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
### Dados matriz regulatória
dados_matriz = pd.read_sql_query(
'SELECT * from projeto_inv.matriz_gerencial where matriz_id=' +
str(matriz_id), connection) #CORRETO
logger.info("Leitura do banco de dados executada com sucesso")
#Salva planilha
dados_matriz.to_excel(save_path + 'matriz.xlsx')
logger.info("matriz.xlsx salva com sucesso")
## Reconstrução da Matriz
#Pega os valores únicos da coluna "linha" e cria uma coluna para cada valor distinto.
matriz_reconstruida = pd.DataFrame(columns=dados_matriz["linha"].unique())
#Pega os valores únicos da coluna "linha" e cria uma linha para cada valor distinto.
matriz_reconstruida[
matriz_reconstruida.columns[0]] = matriz_reconstruida.columns
#Popula cada linha e coluna com os valores da tabela "dados_matriz" na nova estrutura "matriz_reconstruida"
for coluna in matriz_reconstruida.columns:
for linha in range(len(matriz_reconstruida.columns)):
try:
matriz_reconstruida[coluna].iloc[linha] = dados_matriz[
(dados_matriz["coluna"] == coluna)
& (dados_matriz["linha"] ==
matriz_reconstruida.columns[linha])]["valor"].values[0]
except:
matriz_reconstruida[coluna].iloc[linha] = dados_matriz[
(dados_matriz["linha"] == coluna)
& (dados_matriz["coluna"] ==
matriz_reconstruida.columns[linha])]["valor"].values[0]
### Vetor de exposicoes
query_total = "SELECT * FROM projeto_inv.vetor_exposicoes where id_relatorio_quaid419=" + str(
id_relatorio_quaid419
) + " and data_bd= (SELECT MAX(data_bd) FROM projeto_inv.vetor_exposicoes where id_relatorio_quaid419=" + str(
id_relatorio_quaid419) + " )"
query_sep = "SELECT * FROM projeto_inv.vetor_exposicoes_sep where id_relatorio_quaid419=" + str(
id_relatorio_quaid419
) + " and data_bd= (SELECT MAX(data_bd) FROM projeto_inv.vetor_exposicoes_sep where id_relatorio_quaid419=" + str(
id_relatorio_quaid419) + " )"
vetor_exposicoes_total = pd.read_sql_query(query_total, connection)
logger.info("Leitura do banco de dados executada com sucesso")
vetor_separado = pd.read_sql_query(query_sep, connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Ajuste por causa da saida da bd que é crazy
quaid_419_aux = pd.read_sql_query(
'SELECT * from projeto_inv.quaid_419 where id_relatorio_quaid419=' +
str(id_relatorio_quaid419), connection)
logger.info("Leitura do banco de dados executada com sucesso")
if len(quaid_419_aux[quaid_419_aux['FTRCODIGO'] == 'AA1']):
vetor_separado_temp = vetor_separado[
(vetor_separado['vertice'] == 'IBOV_1')
& (~vetor_separado['categoria_alocacao'].isin(['fundo', 'Cotas'])
)].copy()
vetor_separado_temp['valor_exposicao'] = np.where(
vetor_separado_temp['categoria_alocacao'].isin(['ações', 'RV']), 0,
vetor_separado_temp['valor_exposicao'])
vetor_separado = vetor_separado[
(vetor_separado['vertice'] != 'IBOV_1')
& (~vetor_separado['categoria_alocacao'].isin(['fundo', 'Cotas']))]
if vetor_separado_temp['valor_exposicao'].sum() != 0:
vetor_separado = vetor_separado.append(vetor_separado_temp)
vetor_separado_temp['valor_exposicao'] = np.where(
vetor_separado_temp['categoria_alocacao'].isin(['ações', 'RV']), 1,
vetor_separado_temp['valor_exposicao'])
quaid_419_aux = pd.read_sql_query(
'SELECT * from projeto_inv.quaid_419 where id_relatorio_quaid419='
+ str(id_relatorio_quaid419), connection)
logger.info("Leitura do banco de dados executada com sucesso")
#Fecha conexão
connection.close()
quaid_419_aux = quaid_419_aux[quaid_419_aux['FTRCODIGO'] == 'AA1']
quaid_419_aux[
'EMFCODCUSTODIA'] = quaid_419_aux['EMFCODCUSTODIA'] + '_1'
x = quaid_419_aux.groupby(['EMFCODCUSTODIA']).agg(['sum'])
x = x.reset_index(level=None,
drop=False,
inplace=False,
col_level=0,
col_fill='')
x1 = pd.DataFrame(columns=['vertice', 'valor_exposicao'])
x1['vertice'] = x['EMFCODCUSTODIA']
x1['valor_exposicao'] = x['EMFVLREXPRISCO']
lista_acoes = quaid_419_aux['EMFCODCUSTODIA'].unique()
for i in lista_acoes:
vetor_separado_temp['vertice'] = i
vetor_separado_temp['valor_exposicao'] = vetor_separado_temp[
'valor_exposicao'] * x1['valor_exposicao'][x1['vertice'] ==
i].iloc[0]
vetor_separado = vetor_separado.append(vetor_separado_temp)
vetor_separado_temp['valor_exposicao'] = np.where(
vetor_separado_temp['categoria_alocacao'].isin(['ações',
'RV']), 1,
vetor_separado_temp['valor_exposicao'])
vetor_exposicoes_total.to_excel(save_path +
'vetor_exposicoes_conso.xlsx')
logger.info("Vetor exposições consolidado salvo com sucesso")
vetor_separado.to_excel(save_path + 'vetor_exp_sep.xlsx')
logger.info("Vetor exposições separado salvo com sucesso")
## Cálculo do VaR para posicao Total
for i in range(len(vetor_exposicoes_total["vertice"])):
vetor_exposicoes_total["vertice"].iloc[i] = re.sub(
r"_1\b", "", vetor_exposicoes_total["vertice"].iloc[i])
vetor_exposicoes_total["vertice"].iloc[i] = re.sub(
r"\bCCAMBIAL\b", "DOL", vetor_exposicoes_total["vertice"].iloc[i])
## Fundos sem cota alocados em IBOV
lista_fundos_sem_cota = []
for i in range(len(vetor_exposicoes_total["vertice"])):
if (vetor_exposicoes_total["vertice"].iloc[i]
not in matriz_reconstruida.columns):
if vetor_exposicoes_total["vertice"].iloc[i] not in [
'IGPM', 'DOL', 'COMMODITIES', 'IPCA', 'TR', 'DOL'
]:
lista_fundos_sem_cota.append(
vetor_exposicoes_total["vertice"].iloc[i])
if len(lista_fundos_sem_cota) > 0:
index_ibov = vetor_exposicoes_total[vetor_exposicoes_total["vertice"]
== "IBOV"].index.tolist()[0]
for i in range(len(vetor_exposicoes_total["vertice"])):
if vetor_exposicoes_total["vertice"].iloc[
i] in lista_fundos_sem_cota:
vetor_exposicoes_total["valor_exposicao"].iloc[index_ibov] += \
vetor_exposicoes_total["valor_exposicao"].iloc[i]
vetor_exposicoes_total.to_excel(save_path + 'vetor_exposicoes_conso.xlsx')
logger.info("Vetor exposições consolidado salvo com sucesso")
vetor_ordenado = []
for vertice in matriz_reconstruida.columns:
try:
vetor_ordenado.append(
vetor_exposicoes_total[vetor_exposicoes_total["vertice"] ==
vertice]["valor_exposicao"].values[0])
except:
vetor_ordenado.append(0)
matriz_reconstruida.fillna(0, inplace=True)
### Cálculo do Value at Risk
#fc = scipy.stats.norm.ppf(fator_confianca)
#t = math.sqrt(horizonte_em_dias)
var_total = value_at_risk(np.array(vetor_ordenado), matriz_reconstruida,
fc_normal, t)
matriz_reconstruida.to_excel(save_path + 'matriz.xlsx')
logger.info("Matriz.xlsx salva com sucesso")
### Salvar no BD
resultados_total = pd.DataFrame()
## Var Total
dados_var = {}
dados_var["var"] = var_total[0]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Total"
dados_var["vertice"] = "Total"
dados_var["tipo_alocacao"] = "Total"
dados_var["tipo_segmento"] = "Total"
resultados_total = resultados_total.append(dados_var, ignore_index=True)
## VaR Marginal
for item in range(len(var_total[1])):
dados_var = {}
dados_var["var"] = var_total[1][item]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Marginal"
dados_var["vertice"] = matriz_reconstruida.columns[item]
dados_var["tipo_alocacao"] = "Total"
dados_var["tipo_segmento"] = "Total"
resultados_total = resultados_total.append(dados_var,
ignore_index=True)
## VaR Componente
for item in range(len(var_total[2])):
dados_var = {}
dados_var["var"] = var_total[2][item]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Componente"
dados_var["vertice"] = matriz_reconstruida.columns[item]
dados_var["tipo_alocacao"] = "Total"
dados_var["tipo_segmento"] = "Total"
resultados_total = resultados_total.append(dados_var,
ignore_index=True)
### Colocar data_bd
resultados_total["data_bd"] = horario
##Colocar o tipo de relatório NORMAL ou ESTRESSADO
resultados_total['norm_stress'] = relatorio_tipo
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
## Salvar no bando de dados
pd.io.sql.to_sql(resultados_total,
name='var',
con=connection,
if_exists="append",
flavor='mysql',
index=0,
chunksize=5000)
#Fecha conexão
connection.close()
resultados_total.to_excel(writer, 'resultados_total')
logger.info("Resultados_total.xlsx salvo com sucesso")
### Cálculo do VaR Stressado
resultados_separado = pd.DataFrame()
for i in range(len(vetor_separado["vertice"])):
vetor_separado["vertice"].iloc[i] = re.sub(
r"_1\b", "", vetor_separado["vertice"].iloc[i])
vetor_separado["vertice"].iloc[i] = re.sub(
r"\bCCAMBIAL\b", "DOL", vetor_separado["vertice"].iloc[i])
for tipo_alocacao in vetor_separado["tipo_alocacao"].unique():
vetor_filtrado = vetor_separado[vetor_separado["tipo_alocacao"] ==
tipo_alocacao]
for categoria in vetor_filtrado["categoria_alocacao"].unique():
vetor_filtrado2 = vetor_filtrado[
vetor_filtrado["categoria_alocacao"] == categoria]
## Fundos sem cota alocados em IBOV
lista_fundos_sem_cota = []
if len(lista_fundos_sem_cota) > 0:
for i in range(len(vetor_filtrado2["vertice"])):
if vetor_filtrado2["vertice"].iloc[
i] not in matriz_reconstruida.columns:
lista_fundos_sem_cota.append(
vetor_filtrado2["vertice"].iloc[i])
index_ibov = vetor_filtrado2[vetor_filtrado2["vertice"] ==
"IBOV"].index.tolist()[0]
for i in range(len(vetor_filtrado2["vertice"])):
if vetor_filtrado2["vertice"].iloc[
i] in lista_fundos_sem_cota:
vetor_filtrado2["valor_exposicao"].iloc[
index_ibov] += vetor_filtrado2[
"valor_exposicao"].iloc[i]
vetor_ordenado = []
for vertice in matriz_reconstruida.columns:
try:
vetor_ordenado.append(
vetor_filtrado2[vetor_filtrado2["vertice"] ==
vertice]["valor_exposicao"].values[0])
except:
vetor_ordenado.append(0)
var_parcial = value_at_risk(np.array(vetor_ordenado),
matriz_reconstruida, fc_normal, t)
### Salvar no BD
## Var Total
dados_var = {}
dados_var["var"] = var_parcial[0]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Total"
dados_var["vertice"] = "Total"
dados_var["tipo_alocacao"] = tipo_alocacao
dados_var["tipo_segmento"] = categoria
resultados_separado = resultados_separado.append(dados_var,
ignore_index=True)
## VaR Marginal
for item in range(len(var_parcial[1])):
dados_var = {}
dados_var["var"] = var_parcial[1][item]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Marginal"
dados_var["vertice"] = matriz_reconstruida.columns[item]
dados_var["tipo_alocacao"] = tipo_alocacao
dados_var["tipo_segmento"] = categoria
resultados_separado = resultados_separado.append(
dados_var, ignore_index=True)
## VaR Componente
for item in range(len(var_parcial[2])):
dados_var = {}
dados_var["var"] = var_parcial[2][item]
dados_var["id_relatorio_quaid419"] = id_relatorio_quaid419
dados_var["vigencia_matriz"] = str(matriz_id)
dados_var["tipo_relatorio"] = "G"
dados_var["horizonte_tempo"] = horizonte_em_dias
dados_var["nivel_confianca"] = fator_confianca
dados_var["tipo_var"] = "Componente"
dados_var["vertice"] = matriz_reconstruida.columns[item]
dados_var["tipo_alocacao"] = tipo_alocacao
dados_var["tipo_segmento"] = categoria
resultados_separado = resultados_separado.append(
dados_var, ignore_index=True)
### Colocar data_bd
resultados_separado["data_bd"] = datetime.datetime.now()
comparacao_total = resultados_total[resultados_total["tipo_var"] ==
"Total"]["var"]
comparacao_segmento = resultados_separado[
(resultados_separado["tipo_var"] == "Total")
& (resultados_separado["tipo_alocacao"] == "segmento")]
comparacao_produtos = resultados_separado[
(resultados_separado["tipo_var"] == "Total")
& (resultados_separado["tipo_alocacao"] == "produto")]
resultados_separado = resultados_separado.drop_duplicates(subset=[
'tipo_segmento', 'tipo_segmento', 'tipo_var', 'var', 'vertice'
],
take_last=True)
### Colocar data_bd
resultados_separado["data_bd"] = horario
##Colocar o tipo de relatório NORMAL ou ESTRESSADO
resultados_separado['norm_stress'] = relatorio_tipo
logger.info("Conectando no Banco de dados")
connection = db.connect('localhost',
user='******',
passwd='root',
db='projeto_inv',
use_unicode=True,
charset="utf8")
logger.info("Conexão com DB executada com sucesso")
logger.info("Salvando base de dados")
## Salvar no bando de dados
pd.io.sql.to_sql(resultados_separado,
name='var',
con=connection,
if_exists="append",
flavor='mysql',
index=0,
chunksize=5000)
#Fecha conexão
connection.close()
resultados_separado.to_excel(writer, 'var_separado')
logger.info("var_separado.xlsx salvo com sucesso")
writer.save()
