Versions Compared

Version Old Version 4 New Version 5
Changes made by

Anonymous

Anonymous

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

A Controladoria atualiza mensalmente alguns arquivos Excel disponibilizados em um no Sharepoint Relatório Controladoria.

4.2 Transformação:

Foi desenvolvida uma função para fazer o download desses arquivos e gravar no diretório do dbfs:

Code Block
languagepy
def download_arquivo(arquivo_download, nome_arquivo):
    ctx = ClientContext(site_sharepoint).with_credentials(UserCredential(usuario, senha))
    web = ctx.load(ctx.web).execute_query()
    response = File.open_binary(ctx, arquivo_download)
    response.raise_for_status()
    with open("/dbfs/FileStore/shared_uploads/arquivos_financeiros/" + nome_arquivo, "wb") as pasta:
        pasta.write(response.content)

...

Code Block
languagepy
download_arquivo("/sites/RelatriosControladoria/Documentos%20Compartilhados/Report%20Book%20Diretoria/Fechamento%20Gerencial%202022.xlsm", "Fechamento_Gerencial_2022.xlsm")
download_arquivo("/sites/RelatriosControladoria/Documentos%20Compartilhados/Report%20Book%20Diretoria/Fluxo%20de%20Caixa%20Oficial%202022.xlsx", "Fluxo_de_Caixa_Oficial_2022.xlsx")
download_arquivo("/sites/RelatriosControladoria/Documentos%20Compartilhados/Report%20Book%20Diretoria/Forecast%20Consolidador.xlsx", "Forecast_3_9_2022.xlsx")
download_arquivo("/sites/RelatriosControladoria/Documentos%20Compartilhados/Report%20Book%20Diretoria/Forecast%20-%20Fluxo%20de%20Caixa%20Livre.xlsx", "FCST_2_10___Fluxo_de_Caixa_Livre_2022.xlsx")

4.2.1 Arquivo Fechamento_Gerencial_2022.xlsm

4.2.1.1 Indicadores do DRE

Definindo função para Indicadores do DRE:

Code Block
languagepy
#parâmetros da função: range de células e negócio("madeira", "deca", "revestimento" ou "consolidado")
def ler_dre(celulas, negocio):
    #lendo range de células do excel utilizando o pandas.read_excel(arquivo, sheet, linhas desconsideradas, número de linhas, range células, coluna index)
    #transpondo a tabela utilizando .transpose()
    #selecionando colunas utilizando .iloc[]
    #resetando index
    df = pd.read_excel(fechamento_gerencial, "DRE CONSOL", skiprows = 1, nrows = 146, usecols = celulas, index_col = 0).transpose().iloc[:, [6, 127]].reset_index()
    #renomeando colunas
    df.columns = dre_header
    #transformando em dataframe do spark
    df = spark.createDataFrame(df)
    #criando coluna "negocio"
    df = df.selectExpr(dre_header[0], "'{}' as negocio".format(negocio), dre_header[1], dre_header[2])
    #tratando coluna "mes"
    df = df.withColumn("mes", when(df.mes.like("JAN%"), "01/01/" + ano_atual)
                           .when(df.mes.like("FEV%"), "01/02/" + ano_atual)
                           .when(df.mes.like("MAR%"), "01/03/" + ano_atual)
                           .when(df.mes.like("ABR%"), "01/04/" + ano_atual)
                           .when(df.mes.like("MAI%"), "01/05/" + ano_atual)
                           .when(df.mes.like("JUN%"), "01/06/" + ano_atual)
                           .when(df.mes.like("JUL%"), "01/07/" + ano_atual)
                           .when(df.mes.like("AGO%"), "01/08/" + ano_atual)
                           .when(df.mes.like("SET%"), "01/09/" + ano_atual)
                           .when(df.mes.like("OUT%"), "01/10/" + ano_atual)
                           .when(df.mes.like("NOV%"), "01/11/" + ano_atual)
                           .when(df.mes.like("DEZ%"), "01/12/" + ano_atual)
                           .otherwise(None))
    df = df.withColumn("mes", to_date("mes", "dd/MM/yyyy"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_dre_madeira = ler_dre("EL:EX", "madeira")
df_dre_deca = ler_dre("B:N", "deca")
df_dre_revestimento = ler_dre("HN:HZ", "revestimento")
df_dre_consolidado = ler_dre("GT:HF", "consolidado")

Fazendo união de todos os dataframes gerados:

Code Block
languagepy
df_dre = df_dre_madeira.union(df_dre_deca).union(df_dre_revestimento).union(df_dre_consolidado)

4.2.1.2 Indicadores do EVA

Definindo função para Indicadores do EVA, lendo a sheet EVA do arquivo:

Code Block
languagepy
#indicadores de valor agregado:
def ler_eva(celulas, negocio):
    df = pd.read_excel(fechamento_gerencial, "EVA", skiprows = 2, nrows = 50, usecols = celulas, index_col = 0).transpose().iloc[1:, 18].reset_index()
    df.columns = eva_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(eva_header[0], "'{}' as negocio".format(negocio), eva_header[1])
    df = df.withColumn("mes", when(df.mes.like("JAN%"), "01/01/" + ano_atual)
                           .when(df.mes.like("FEV%"), "01/02/" + ano_atual)
                           .when(df.mes.like("MAR%"), "01/03/" + ano_atual)
                           .when(df.mes.like("ABR%"), "01/04/" + ano_atual)
                           .when(df.mes.like("MAI%"), "01/05/" + ano_atual)
                           .when(df.mes.like("JUN%"), "01/06/" + ano_atual)
                           .when(df.mes.like("JUL%"), "01/07/" + ano_atual)
                           .when(df.mes.like("AGO%"), "01/08/" + ano_atual)
                           .when(df.mes.like("SET%"), "01/09/" + ano_atual)
                           .when(df.mes.like("OUT%"), "01/10/" + ano_atual)
                           .when(df.mes.like("NOV%"), "01/11/" + ano_atual)
                           .when(df.mes.like("DEZ%"), "01/12/" + ano_atual)
                           .otherwise(None))
    df = df.withColumn("mes", to_date("mes", "dd/MM/yyyy"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_eva_madeira = ler_eva("GG:GT", "madeira")
df_eva_deca = ler_eva("BA:BN", "deca")
df_eva_revestimento = ler_eva("EY:FL", "revestimento")
df_eva_consolidado = ler_eva("FP:GC", "consolidado")

Fazendo união de todos os dataframes gerados e ajustando a ordem de grandeza do campo eva_recorrente:

Code Block
languagepy
df_eva = df_eva_madeira.union(df_eva_deca).union(df_eva_revestimento).union(df_eva_consolidado).withColumn("eva_recorrente", col("eva_recorrente")/1000)

4.2.2 Arquivo Fluxo_de_Caixa_Oficial_2022.xlsx

4.2.2.1 Indicadores de Fluxo de Caixa

Definindo função para Indicadores de Fluxo de Caixa, lendo a sheet F.C. REAL do arquivo:

Code Block
languagepy
def ler_fluxo_caixa(celulas, negocio):
    df = pd.read_excel(fluxo_caixa, "F.C. REAL", skiprows = 1, nrows = 86, usecols = celulas, index_col = 0).transpose().iloc[1:, 54].reset_index()
    df.columns = fc_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(fc_header[0], "'{}' as negocio".format(negocio), fc_header[1])
    df = df.withColumn("mes", to_date("mes"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_fc_madeira = ler_fluxo_caixa("FN:GA", "madeira")
df_fc_deca = ler_fluxo_caixa("BM:BZ", "deca")
df_fc_revestimento = ler_fluxo_caixa("IT:JG", "revestimento")
df_fc_consolidado = ler_fluxo_caixa("KJ:KW", "consolidado")

Fazendo união de todos os dataframes gerados:

Code Block
languagepy
df_fc = df_fc_madeira.union(df_fc_deca).union(df_fc_revestimento).union(df_fc_consolidado)

4.2.2.2 Indicadores de PMP

Definindo função para Indicadores de PMP, lendo a sheet CGL.ROL do arquivo:

Code Block
languagepy
def ler_pmp(celulas, negocio):
    df = pd.read_excel(fluxo_caixa, "CGL.ROL", skiprows = 1, nrows = 50, usecols = celulas, index_col = 0).transpose().iloc[1:, 26].reset_index()
    df.columns = pmp_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(pmp_header[0], "'{}' as negocio".format(negocio), pmp_header[1])
    df = df.withColumn("mes", to_date("mes"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_pmp_madeira = ler_pmp("DR:EE", "madeira")
df_pmp_deca = ler_pmp("AU:BH", "deca")
df_pmp_revestimento = ler_pmp("FZ:GM", "revestimento")
df_pmp_consolidado = ler_pmp("GO:HB", "consolidado")

Fazendo união de todos os dataframes gerados:

Code Block
languagepy
df_pmp = df_pmp_madeira.union(df_pmp_deca).union(df_pmp_revestimento).union(df_pmp_consolidado)

4.2.3 Arquivo Forecast_3_9_2022.xlsx

4.2.3.1 Indicadores de Forecast

Definindo função para Indicadores de Forecast:

Code Block
languagepy
def ler_forecast(sheet, celulas, negocio):
    df = pd.read_excel(forecast, sheet, skiprows = 3, nrows = 83, usecols = celulas, index_col = 0).transpose().iloc[:, [6, 47]].reset_index()
    df.columns = frc_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(frc_header[0], "'{}' as negocio".format(negocio), frc_header[1], frc_header[2])
    df = df.withColumn("mes", to_date("mes"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_frc_dre_madeira = ler_forecast("DRE_MAD", "A:M", "madeira")
df_frc_dre_deca = ler_forecast("DRE_DEC", "A:M", "deca")
df_frc_dre_revestimento = ler_forecast("DRE_REVEST", "A:M", "revestimento")
df_frc_dre_consolidado = ler_forecast("DRE_CONSOL", "A:M", "consolidado")

Fazendo união de todos os dataframes gerados:

Code Block
languagepy
df_frc = df_frc_dre_madeira.union(df_frc_dre_deca).union(df_frc_dre_revestimento).union(df_frc_dre_consolidado)

4.2.3.2 Indicadores de Forecast EVA

Definindo função para Indicadores de Forecast EVA, lendo a sheet Base EVA ROIC:

Code Block
languagepy
def ler_forecast_eva(negocio):
    #desinindo constantantes para cada negócio para utilizar posteriormente o iloc[] e localizar os dados necessários
    #esses valores pra neg são como os negócios são identificados no arquivo
    if negocio == "madeira":
        neg = "Mad. Total"
    elif negocio == "deca":
        neg = "Deca"
    elif negocio == "revestimento":
        neg = "RC"
    elif negocio == "consolidado":
        neg = "Consol"
    df = pd.read_excel(forecast, "Base EVA ROIC", skiprows = 4, nrows = 130, usecols = "B:Q")
    #buscando a linha em que a coluna 0 é igual ao negócio, coluna 1 igual a "Mês", coluna 2 igual a "RECORRENTE" e coluna 4 igual a "EVA"
    df = df.where(
        (df.iloc[:, 0] == neg) &
        (df.iloc[:, 1] == "Mês") &
        (df.iloc[:, 2] == "RECORRENTE") &
        (df.iloc[:, 3] == "EVA")
    ).dropna()
    df = df.transpose().iloc[4: ].reset_index()
    df.columns = frc_eva_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(frc_eva_header[0], "'{}' as negocio".format(negocio), frc_eva_header[1])
    df = df.withColumn("mes", when(df.mes == 1, "01/01/" + ano_atual)
                           .when(df.mes == 2, "01/02/" + ano_atual)
                           .when(df.mes == 3, "01/03/" + ano_atual)
                           .when(df.mes == 4, "01/04/" + ano_atual)
                           .when(df.mes == 5, "01/05/" + ano_atual)
                           .when(df.mes == 6, "01/06/" + ano_atual)
                           .when(df.mes == 7, "01/07/" + ano_atual)
                           .when(df.mes == 8, "01/08/" + ano_atual)
                           .when(df.mes == 9, "01/09/" + ano_atual)
                           .when(df.mes == 10, "01/10/" + ano_atual)
                           .when(df.mes == 11, "01/11/" + ano_atual)
                           .when(df.mes == 12, "01/12/" + ano_atual)
                           .otherwise(None))
    df = df.withColumn("mes", to_date("mes", "dd/MM/yyyy"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_frc_eva_madeira = ler_forecast_eva("madeira")
df_frc_eva_deca = ler_forecast_eva("deca")
df_frc_eva_revestimento = ler_forecast_eva("revestimento")
df_frc_eva_consolidado = ler_forecast_eva("consolidado")

Fazendo união de todos os dataframes gerados e ajustando a ordem de grandeza do campo frc_eva_recorrente:

Code Block
languagepy
df_frc_eva = df_frc_eva_madeira.union(df_frc_eva_deca).union(df_frc_eva_revestimento).union(df_frc_eva_consolidado).withColumn("frc_eva_recorrente", col("frc_eva_recorrente")/1000)

4.2.4 Arquivo FCST_2_10___Fluxo_de_Caixa_Livre_2022.xlsx

4.2.4.1 Indicadores Forecast Fluxo de Caixa

Definindo função para Indicadores de Forecast fluxo de Caixa, lendo a sheet F.C. FCST:

Code Block
languagepy
def ler_fcf(celulas, negocio):
    df = pd.read_excel(fcf, "F.C. FCST", skiprows = 1, nrows = 87, usecols = celulas, index_col = 0).transpose().iloc[1:, 54].reset_index()
    df.columns = fcf_header
    df = spark.createDataFrame(df)
    df = df.selectExpr(fcf_header[0], "'{}' as negocio".format(negocio), fcf_header[1])
    df = df.withColumn("mes", when(df.mes.like("JAN%"), "01/01/" + ano_atual)
                           .when(df.mes.like("FEV%"), "01/02/" + ano_atual)
                           .when(df.mes.like("MAR%"), "01/03/" + ano_atual)
                           .when(df.mes.like("ABR%"), "01/04/" + ano_atual)
                           .when(df.mes.like("MAI%"), "01/05/" + ano_atual)
                           .when(df.mes.like("JUN%"), "01/06/" + ano_atual)
                           .when(df.mes.like("JUL%"), "01/07/" + ano_atual)
                           .when(df.mes.like("AGO%"), "01/08/" + ano_atual)
                           .when(df.mes.like("SET%"), "01/09/" + ano_atual)
                           .when(df.mes.like("OUT%"), "01/10/" + ano_atual)
                           .when(df.mes.like("NOV%"), "01/11/" + ano_atual)
                           .when(df.mes.like("DEZ%"), "01/12/" + ano_atual)
                           .otherwise(None))
    df = df.withColumn("mes", to_date("mes", "dd/MM/yyyy"))
    return df

Aplicando a função para cada negócio, passando o parâmetro de range de células onde se encontram os dados:

Code Block
languagepy
df_fcf_madeira = ler_fcf("FN:GA", "madeira")
df_fcf_deca = ler_fcf("BM:BZ", "deca")
df_fcf_revestimento = ler_fcf("HD:HQ", "revestimento")
df_fcf_consolidado = ler_fcf("IT:JG", "consolidado")

Fazendo união de todos os dataframes gerados:

Code Block
languagepy
df_frc_fc = df_fcf_madeira.union(df_fcf_deca).union(df_fcf_revestimento).union(df_fcf_consolidado)

4.2.5 Arquivo indicadores_poa_2022.csv

Foi feito o upload desse arquivo diretamente no dbfs do Databricks:

Code Block
languagepy
df_poa = spark.read.format("csv").option("delimiter", ";").option("header", "true").load("dbfs:/FileStore/shared_uploads/arquivos_financeiros/indicadores_poa_2022.csv")

Tratando alguns tipos de campos e ajustando a ordem de grandeza de outro:

Code Block
languagepy
df_poa = df_poa.select(
    "mes",
    "negocio",
    col("poa_receita_liquida_vendas").cast(DoubleType()),
    col("poa_ebitda_recorrente").cast(DoubleType()),
    col("poa_eva_recorrente").cast(DoubleType()),
    col("poa_fluxo_caixa_livre_total").cast(DoubleType())
).withColumn("poa_eva_recorrente", col("poa_eva_recorrente")/1000).withColumn("poa_fluxo_caixa_livre_total", col("poa_fluxo_caixa_livre_total")/1000)

4.2.5 Base Final

Todos os dataframes gerados nos passos anteriores agora são unidos para formar um único, utilizando os campos mes e negocio:

Code Block
languagepy
campos_join = ["mes", "negocio"]
df_indicadores_financeiros = df_dre.join(
    df_eva, campos_join
).join(
    df_fc, campos_join
).join(
    df_pmp, campos_join
).join(
    df_frc, campos_join
).join(
    df_frc_eva, campos_join
).join(
    df_frc_fc, campos_join
).join(
    df_poa, campos_join
).orderBy("negocio", "mes")

Sobrescrevendo a tabela final no database :

Code Block
languagepy
df_indicadores_financeiros.write.mode("overwrite").option("overwriteSchema", "true").saveAsTable("indicadores_mercado.tb_indicadores_financeiros_{}".format(ano_atual))

4.3 Base Final:

col_name

data_type

mes

date

negocio

string

valor_faturamento

decimal(29,2)

valor_devolucao

decimal(29,2)

...