Libraries

# Dados Financeiros 
library(GetDFPData2)

# manipulação de dados como: add novas variáveis, escolher, filtrat e etc
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
# facilidade no filtro de datas
library(lubridate)
## 
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union
# manipulação de dados: importante como pivot
library(tidyr)


#trabalhar com funções e vetores
library(purrr)

# mais uma função para arrumar a base, nesse caso limpa nomes de variaveis
library(janitor)
## 
## Attaching package: 'janitor'
## The following objects are masked from 'package:stats':
## 
##     chisq.test, fisher.test

Baixando os dados financeiros

# Filtra as empresas ativas, exceto instituições financeiras

cache_folder = "cache_folder_shiny"

info_companies <- GetDFPData2::get_info_companies(cache_folder = cache_folder)
## Fetching info on B3 companies
##  Found cache file. Loading data..
##  Got 2601 lines for 2467 companies [Actives = 696 Inactives = 1783]
names_companies <- info_companies |> 
  filter(SIT_REG == 'ATIVO' & TP_MERC == "BOLSA" & !SETOR_ATIV %in% c("Bancos",
                                                                      "Intermedia\u00e7\u00e3o Financeira",
                                                                      "Seguradoras e Corretoras")) |> 
  arrange(DENOM_SOCIAL) |> 
  pull(DENOM_SOCIAL)

# Cria lista de empresas

cvm_codes <- info_companies |> 
  filter(DENOM_SOCIAL %in% names_companies) |> 
  pull(CD_CVM)

# Define as fontes de dados

first_date = 2012
last_date = year(Sys.Date())
type_docs = c("BPA", "BPP", "DRE","DFC_MI")
type_format = "con"

# Coleta os dados das empresas

l_dfp <- get_dfp_data(companies_cvm_codes = cvm_codes, 
                                   first_year =  first_date,
                                   last_year = last_date, 
                                   type_docs = type_docs,
                                   type_format = type_format,
                                   use_memoise = TRUE,
                                   cache_folder = cache_folder
)


# Realiza a limpeza da lista e empilha em um data frame

df_dfp <- l_dfp |>
  set_names(type_docs) |> 
  bind_rows(.id = "label") |>
  select(label, DT_REFER, DENOM_CIA, ESCALA_MOEDA, CD_CONTA, DS_CONTA, VL_CONTA,CD_CVM)

Definir alguns indicadores

# Seleciona as contas inportantes e cria os indicadores

CVM <- df_dfp |> 
  filter(CD_CONTA %in% c("1","1.01.01","1.01.03","1.02","1.02.02","1.01","1.01.08","1.01.07","1.01.06",
                         "2.01.04","2.02.01","2.03","2.01.05.02.01","2.01","2.02",
                         "3.01","3.02","3.05","3.08","3.11",
                         "6.01","6.02")) |> 
  pivot_wider(values_from = VL_CONTA,
              names_from = c(CD_CONTA),
              -c(label, DS_CONTA, ESCALA_MOEDA))|> 
  clean_names() |> 
  group_by(denom_cia, dt_refer) |> 
  mutate(caixa = x6_01+x6_02)|> 
  mutate(divid_ebit = (x2_01_04+x2_02_01-x1_01_01)/x3_05)|> 
  mutate(roe = x3_11/x2_03)|> mutate(roa = x3_05/x1)|> 
  mutate(roic = (x3_05+x3_08)/(x1_01_01+x1_01_03+x1_02))|> 
  mutate(m.bruta = (x3_01+x3_02)/(x3_01))|> mutate(m.ebit = (x3_05)/(x3_01))|> 
  mutate(m.liq = (x3_11)/(x3_01))|> 
  mutate(pass_ativ = (x1-x2_03)/(x1))|> 
  mutate(pass_pt = (x1-x2_03)/(x2_03))|> 
  mutate(pas.circ_pt = (x2_01)/(x1-x2_03))|> 
  mutate(irp = (x1_02)/(x2_02+x2_03))|> 
  mutate(liq_corrente = (x1_01)/(x2_01))|> 
  mutate(liq_geral = (x1_01+x1_02_02)/(x1-x2_03))|> 
  mutate(alavancagem = (x1)/(x2_03))|> 
  mutate(wacc = ((0.2*pass_ativ)+((1-pass_ativ)*0.12)*(1-(x3_08/x3_01))
                        ))|> 
  mutate(perpet = ((caixa))/((wacc)))|> 
  mutate(v.perpet = ((perpet))/((1+wacc)))|> 
  mutate(m.cap_adj = (perpet+v.perpet))|> 
  
  ungroup()
## Warning: Specifying the `id_cols` argument by position was deprecated in tidyr 1.3.0.
## ℹ Please explicitly name `id_cols`, like `id_cols = -c(label, DS_CONTA,
##   ESCALA_MOEDA)`.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.
# Visualizar a tabela

library(DT)
CVM %>%
  DT::datatable()

Incluir os Tickers

# Pegar os tickers de cada empresa
# link: https://www.infomoney.com.br/planilhas/cnpj-das-empresas-listadas-na-b3-para-declarar-acoes-no-imposto-de-renda/

library(readxl)
  data <- read_excel("Planilha-de-Imposto-de-Renda-2023-FIIS-e-B3.xlsx",
                   sheet = "Empresas da B3")
## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
colnames(data)[3] <- "denom_cia"
colnames(data)[1] <- "ticker"


data <- data %>% select(,1, 3,)
data = data  %>% 
        select(denom_cia, ticker)  

CVM <- left_join(data, CVM, by="denom_cia")
## Warning in left_join(data, CVM, by = "denom_cia"): Detected an unexpected many-to-many relationship between `x` and `y`.
## ℹ Row 8 of `x` matches multiple rows in `y`.
## ℹ Row 784 of `y` matches multiple rows in `x`.
## ℹ If a many-to-many relationship is expected, set `relationship =
##   "many-to-many"` to silence this warning.
CVM = CVM[c(-1,-2,-3,-4,-5,-6,-7),]
CVM = CVM[!is.na(CVM$dt_refer) , ]

CVM %>%
  DT::datatable()

Agrupar com os dados fundamentalistas

library(rvest)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats 1.0.0     ✔ stringr 1.5.0
## ✔ ggplot2 3.4.3     ✔ tibble  3.2.1
## ✔ readr   2.1.4     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter()         masks stats::filter()
## ✖ readr::guess_encoding() masks rvest::guess_encoding()
## ✖ dplyr::lag()            masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(httr)
library(knitr)

converte <- function(x) {
   
  z <- trimws(x)
  n_digitos <- nchar(z)
  ultimo <- substr(z, n_digitos, n_digitos)
  
  z <- gsub("[^0-9,.]", "", z)
  z <- gsub("\\.", "", z)
  
  z <- as.numeric(gsub(",", ".", z))
  
  if(ultimo[1] == "%") { 
    z <- z/100 
  }
  
  return(z)
}
url <- "http://www.fundamentus.com.br/resultado.php"

dadosFund <- url %>%
  read_html() %>%
  rvest::html_table(header = TRUE) %>%
  map_df(bind_cols) %>%
  as_tibble()%>%
  mutate_at(colnames(.)[-1], converte)%>%
  clean_names()%>%
  mutate(VPA = (cotacao)/(p_vp))|> 
  mutate(n = (patrim_liq/VPA),options(scipen=999))|> 
  mutate(mkt = (cotacao*n),options(scipen=999))|> 
  
  ungroup()

colnames(dadosFund)[1] <- "ticker"
DRE <- left_join(CVM, dadosFund, by="ticker")

DRE = DRE|>
  mutate(p.adj = ((m.cap_adj)/(n/1000)))|>
  mutate(p.liq = ((m.cap_adj-x2_01_04-x2_02_01+x1_01_01)/(n/1000)))|>
  mutate(lpa = ((cotacao)/(p_l)))|>
  
  ungroup()

DRE <- DRE %>%
  dplyr::group_by(denom_cia) %>%
  dplyr::mutate(rank = rank(dt_refer))

DRE = arrange(DRE,ticker)

colnames(info_companies)[10] <- "cd_cvm"
info = info_companies %>% select(.,"cd_cvm","SETOR_ATIV")
info=distinct(info)
DRE <- left_join(DRE, info, by="cd_cvm")
DRE <- DRE [!is.na(DRE$p_l),]

CVM %>%
  DT::datatable()

Salvar o processo para iniciar a etapa Shiny

# Salvar
save(
  list  = ls(),
  file  = file.path(".Rdata"),
  envir = environment()
)

Criando a plataforma

O resultado final pode ser encontrado no link:https://yaguinuma19.shinyapps.io/valu/

library(shiny)
library(scales)
library(shinydashboard)
library(shinyWidgets)
library(flexdashboard)
library(ggplot2)
library(echarts4r)
library(plotly)
library(stringr)
library(quantmod)
library(tidyquant)
library(timetk)
library(scales)

# Carregar os dados salvos
load(".RData")

# Definir os layout

ui <- 
  dashboardPage(
    dashboardHeader(title = "Yukio Valuation"),
    
    dashboardSidebar(
            selectizeInput(
              inputId = "companies",
              label = "Selecione as companhias disponiveis",
              choices = unique(DRE$ticker),
              multiple = FALSE), 
            sidebarMenu(
              menuItem("Fundamentos", tabName = "cot", icon = icon("chart-bar")),
              menuItem("Estrutura de Capital", tabName = "balanco", icon = icon("chart-bar")),
              menuItem("Lucratividade", tabName = "lucro", icon = icon("chart-bar")))),
  
  dashboardBody(
    tabItems(
      tabItem(tabName = "cot", 
              fluidRow(
                box((sliderInput("slider1", ("Data Início:"), min = today()-3650, 
                          max = today()-1, value = today()-365))),
                box(textOutput(("cia")),
                  textOutput(("se")))),
              fluidRow(       
                infoBoxOutput("p"),
                infoBoxOutput("ibov"),
                infoBoxOutput("mkt"),
                
                shinydashboard::valueBoxOutput("div",width = 3),
                shinydashboard::valueBoxOutput("p_l",width = 3),
                shinydashboard::valueBoxOutput("ev.ebit",width = 3),
                shinydashboard::valueBoxOutput("vpa",width = 3),
                shinydashboard::valueBoxOutput("roic",width = 3),
                shinydashboard::valueBoxOutput("mrg_liq",width = 3),
                shinydashboard::valueBoxOutput("div.pat",width = 3),
                shinydashboard::valueBoxOutput("cresc_5",width = 3),
                 
                tabBox(width = 12,
                tabPanel("Cotação",echarts4rOutput("acao")),
                tabPanel("Preço Justo",echarts4rOutput("price")),
                tabPanel("Análise Setorial",tableOutput("setor")),
                tabPanel("Outros Ativos", DT::dataTableOutput("setor_2")),
                tabPanel("Cresc. Patrim.",echarts4rOutput("pt"))))),
      
      tabItem(tabName = "balanco",
            fluidRow(box(echarts4rOutput("Patrimonial"),width=12),
              box(echarts4rOutput("Liquidez"),width=12),
              box(echarts4rOutput("Endiv"),width=12))),
      
      tabItem(tabName = "lucro",
            fluidRow(box(echarts4rOutput("margem"),width=12),
              box(echarts4rOutput("R"),width=12),
              box(echarts4rOutput("dre"),width=12))))))


# server.R

server <- function(input, output,session) {
  
  hoje <- lubridate::today()
  ACAO = reactive ({ tq_get(paste0(input$companies,".SA"), 
                 get = "stock.prices", 
                 from = input$slider1,
                 to = as.character(hoje)) %>%
    na.omit()}) 

  ibov = reactive ({ tq_get("^BVSP", 
                            get = "stock.prices", 
                            from = input$slider1,
                            to = as.character(hoje)) %>%
     na.omit()}) 
  
  output$acao <- 
      renderEcharts4r({ 
        ACAO()%>%
          e_chart(x=date)%>%
          e_area(name="Preço R$",adjusted)%>% 
          e_tooltip(trigger = "axis")%>%
          e_theme("chalk")})  
  
  output$p<- renderInfoBox({
    t = ACAO()%>%
      filter(date == max(date))
    y=ACAO()%>%
      filter(date == min(date))
    
  infoBox("Cotação", 
            paste0("R$",round(t$adjusted,2)),
            paste0(round((t$adjusted/y$adjusted-1)*100,2),"%"),icon("usd",lib="glyphicon"),color="green")})
  
  output$ibov<- renderInfoBox({
    t_ib = ibov()%>%
      filter(date == max(date))
    y_ib=ibov()%>%
      filter(date == min(date))
    
  infoBox("Ibovespa", 
    paste0(format(round(as.numeric(t_ib$adjusted,2)),big.mark=",")),
    paste0(round((t_ib$adjusted/y_ib$adjusted-1)*100,2),"%"),icon("stats",lib="glyphicon"),color="blue")})
  
  output$mkt<- renderInfoBox({
    infoBox("Valor de Mercado", 
    paste0("R$", format(round(as.numeric(DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        select(mkt)%>% .$mkt)),big.mark=",")),
    paste0("Nº Papéis:  ",format(round(as.numeric(DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
      select(n)%>% .$n)),big.mark=",")),color = "maroon")})
  
  output$cia<- renderText({
    paste0(DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        select(denom_cia)%>% .$denom_cia)})
  
  output$se<- renderText({
    paste0("Setor: ", DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        select(SETOR_ATIV)%>% .$SETOR_ATIV)})
  
  output$p_l<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
      paste0(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(p_l)%>% .$p_l), "P/L",color="blue")})
  
  output$roic<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
    paste0(round(DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        select(roic.y)%>% .$roic.y*100,2)), "ROIC",color="olive")})
  
  output$div.pat<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
      paste0(round(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
        select(div_brut_patrim)%>% .$div_brut_patrim*100,2)), "Divida/Patrim.",color="red")})
  
  output$mrg_liq<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
      paste0(round(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(mrg_liq)%>% .$mrg_liq*100,2)), "Marg. Líq.")})
  
  output$cresc_5<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
      paste0(round(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(cresc_rec_5a)%>% .$cresc_rec_5a*100,2),"%"), "Cres. Receita 5a",color="orange")})
  
  output$vpa<- shinydashboard::renderValueBox({
    shinydashboard::valueBox( 
      paste0(round(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(VPA)%>% .$VPA),2), "VPA",color = "yellow")})
  
  output$ev.ebit <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(
      paste0(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(ev_ebit)%>% .$ev_ebit),"EV/EBITDA", color="maroon")})
  
  output$div <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(
      paste0(round(DRE%>%
        filter(ticker == input$companies) %>%
        filter(rank==max(rank)) %>%
          select(div_yield)%>% .$div_yield*100,2),"%"),"Div.Yield",color = "green")})
    
  output$price <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        mutate(p.p=(p.liq/cotacao-1),options(digits = 2))%>%
          e_chart(x=dt_refer)%>%
          e_bar(name="Valor Atual",cotacao)%>%
          e_bar(name="Valor Perpetuo",p.adj)%>%
          e_bar(name="Valor Perpetuo",p.liq)%>%
          e_bar(name="Var %",p.p)%>%
          e_labels(position = "top")%>%  
          e_x_axis(show = FALSE)%>% 
          e_theme("macarons")})
  
  output$pt <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
      filter(rank==max(rank)) %>%
        mutate(p.p=(patrim_liq/1000/x2_03)-1,options(digits = 2))%>%
        mutate(patrim.liq=(patrim_liq/1000),options(scipen=999))%>%
          e_chart(x=dt_refer)%>%
          e_bar(name="Patrimonio Atual",patrim.liq)%>%
          e_bar(name="Patrimonio Y-1",x2_03,options(scipen=999))%>%
          e_bar(name="Var %",p.p)%>%
          e_labels(position = "top")%>%  
          e_x_axis(show = FALSE)%>% 
          e_theme("macarons")})
  
  set = reactive ({DRE%>%
    filter(rank==max(rank))%>%
    filter(SETOR_ATIV == as.character(DRE%>%
    filter(ticker == input$companies) %>%
    filter(rank==max(rank)) %>%
      .$SETOR_ATIV))}) 
  
  output$setor <- 
    renderTable({         
      s = data.frame(c("min","1q","MD","3q","max"),fivenum(set()$div_yield*100),fivenum(set()$p_l),
        fivenum(set()$ev_ebitda),fivenum(set()$VPA),fivenum(set()$roic.y*100),
        fivenum(set()$div_brut_patrim), fivenum(set()$mrg_liq),
        fivenum(set()$cresc_rec_5a*100))
        colnames(s) = paste(c("","Div.Yield", "p/l","EV/EBITDA","VPA","ROIC","Div.bruta/Patrim.",
                                  "Marg. Líq.", "Cresc. Receita 5A"))
      s})  
  
  output$setor_2 <- 
    DT::renderDataTable(DT::datatable({         
      s2 = data.frame(
      set()%>%
        mutate("Div_Yield" = (div_yield*100))|>
        mutate("Margem.Líq" = (mrg_liq*100))|>
        mutate("ROIC" = (roic.y*100))|>
        mutate("Cresc.Receita.5A" = (cresc_rec_5a*100))|>
          select(ticker,"Cotação"=cotacao, "Valor de Mercado"=mkt,
          Div_Yield,"P_L"=p_l,"EV_EBITDA"=ev_ebitda,VPA,
          ROIC,"Divida/Patrim."=div_brut_patrim,Margem.Líq, Cresc.Receita.5A))
      s2 %>% 
        mutate(across(where(is.numeric), ~ round(.,2))) %>% 
        mutate(across(where(is.numeric), ~ format(.,big.mark=",")))}))
  
  output$Patrimonial <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_area(name="Ativo",x1)%>%
        e_area(name="Patrimonio",x2_03)%>%
        e_tooltip(trigger = "axis")%>%
        e_legend(right=0)%>%
        e_title("Patrimonial")})
  
  output$Liquidez <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_line(name="Liquidez Corrente",liq_corrente)%>%
        e_line(name="Liquidez Geral",liq_geral)%>%
        e_line(name="Alavancagem",alavancagem)%>%
        e_tooltip(trigger = "axis")%>%
        e_title("Liquidez")%>%
        e_legend(right=0)})
  output$Endiv <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_line(name="Divida Liq./Ebit",divid_ebit, color="red")%>%
        e_line(name="IRP",irp,color="orange")%>%
        e_line(name="Passivo/Patrimonio",pass_pt, color="pink")%>%
        e_line(name="Passivo/Ativo",pass_ativ, color="brown")%>%
        e_tooltip(trigger = "axis")%>%
        e_legend(right=0)%>%
        e_title("Endividamento")})
  
  output$margem <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_line(name="Margem Bruta",m.bruta)%>%
        e_line(name="Margem Ebit",m.ebit)%>%
        e_line(name="Margem Liquida",m.liq)%>%
        e_tooltip(trigger = "axis")%>%
        e_legend(right=0)%>%
        e_title("Eficiencia")})
  
  output$R <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_line(name="ROA",roa)%>%
        e_line(name="ROE",roe.x)%>%
        e_line(name="ROIC",roic.x)%>%
        e_tooltip(trigger = "axis")%>%
        e_legend(right=0)%>%
        e_title("Rentabilidade")})
  
  output$dre <- renderEcharts4r({
    DRE%>%
      filter(ticker == input$companies) %>%
        e_chart(x=dt_refer)%>%
        e_bar(name="Receita Bruta",x3_01,color="blue")%>%
        e_bar(name="Custos",x3_02,color="red")%>%
        e_line(name="Lucro Liq.",x3_11,color="#00CD00")%>%
        e_line(name="EBIT",x3_05,color="#528B8B")%>%
        e_line(name="FCL",caixa,color="gold")%>%
        e_tooltip(trigger = "axis")%>%
        e_legend(right=0)%>%
        e_title("Lucratividade")})
}

# Cria-se a plataformas

shinyApp(ui, server)