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()