[Excel] Planilha de Cotações Bovespa / Planilha de controle de ações / Planilha de cotações Bovespa
Você precisa acompanhar cotações da Bovespa? O que acha de analisar estes dados diretamente no Excel e atualizado online sem nenhum esforço?
As técnicas e a planilha aqui demonstradas e liberada gratuitamente para download podem te ajudar a ter um controle muito melhor e a ganhar mais dinheiro com seus investimentos.
Antes de mais nada, esta funcionalidade foi explicada e a planilha disponibilizada pelo meu amigo Tiago Sayão.
Desde já agradeço por permitir a divulgação da técnica e da planilha no Guia do Excel.
O link para o artigo do Tiago Sayão é: https://www.guiadoexcel.com.br/acoes-quando-e-hora-de-comprar-tenha-seu-controle-no-excel/
Você verá neste artigo:
- Montar uma consulta de dados de ações da Bovespa e de outras bolsas online
- Retornar esta consulta diretamente para o Excel e mantê-la atualizada
- Criar um filtro dinâmico para os seus dados
- Caixas de texto com conteúdos dinâmicos
- Imagens dinâmicas para dashboards
- Classificação automática com duplo clique usando VBA
1. Montar consulta de dados de ações da Bovespa e de outras bolsas online
A consulta dos dados de ações da Bovespa foi feito usando o Google Sheets e a função GOOGLEFINANCE, conforme demonstrado primeiramente no artigo: https://www.guiadoexcel.com.br/obter-cotacoes-de-acoes-b3-bovespa-no-excel/.
Para montar os seus próprios dados de suas ações use como referência o artigo acima e também o ajuda do GoogleFinance: https://support.google.com/docs/answer/3093281?hl=pt-BR.
O grande truque está em concatenar a informação para que sejam retornados os dados da Bovespa.
2. Retornar esta consulta diretamente para o Excel e mantê-la atualizada
No link anterior você montou as informações no Google Sheets, para retorná-las ao Excel você pode seguir os seguintes passos:
a. Para compartilhar os dados da sua planilha do Google Sheets clique em Publish to the web;
b. Depois clique na opção de tipo de compartilhamento e mude para Comma-separated values (.csv) e copie este link
c. Já no Excel clique na guia Dados e na seção Obter e Transformar clique em Nova Consulta->De Outras Fontes->Da Web
d. Selecione a opção básico e cole o caminho copiado do csv.
e. Uma visualização do arquivo mostrará parte dos dados. Selecione em origem do arquivo o tipo 65001: Unicode (UTF-8) está quase no final
f. Clique na guia Transformar e clique no botão Usar a Primeira Linha como Cabeçalho. Isto fará com que a linha seja subida ao nível de nome das colunas.
g. Clique em Página Inicial, selecione as colunas de valores e Clique em Substituir Valores
h. Clique nas colunas de valores e em Arquivo Tipo de Dados, altere para Moeda
i. Clique em Página Inicial e no botão Fechar e Carregar selecione a primeira opção, os dados serão retornados á uma planilha da pasta de trabalho aberta
j. E pronto, os dados estarão na sua planilha e poderão ser utilizados como precisar. Para atualizar estes dados clique na guia Dados e no botão Atualizar Tudo, sempre que precisar.
3. Criar um filtro dinâmico para os seus dados
No nosso exemplo os dados já estão no formato de de tabela, crie uma nova planilha e use a função PROCV para retornar estas informações. Se tiver dúvidas basta baixar o download para verificar todas as fórmulas.
a. Na nova planilha selecione todos os dados e clique em Página Inicial->Formatar como Tabela.
A tabela criada possui uma série de benefícios para a análise de dados e para a sua manipulação.
No nosso caso utilizaremos o Slicer, ou em português Segmentação de dados.
b. Clique sobre a sua tabela e depois selecione na guia Inserir a opção Filtros->Segmentação de Dados
c. Selecione a opção Ação e veja que será criado um filtro automaticamente na sua planilha.
Em outro artigo falaremos mais sobre as configurações e opções da segmentação de dados no Excel, mas por hora você já tem o filtro automático e dinâmico, ao clicar em qualquer ação ou conjunto, usando CTRL para selecionar vários, etc, você conseguirá filtrar rapidamente os dados da sua tabela.
4. Caixas de texto com conteúdos dinâmicos
Na planilha de cotações da Bovespa nós utilizamos uma caixa de texto para retornar os dados da IBOVESPA, e estes dados ficaram dinâmicos.
Para fazer com que estes dados ficassem desta forma separamos estes dados em uma planilha auxiliar chamada Cálculos, da qual buscamos da nossa base de dados as informações desta origem.
Para criar o quadro abaixo, que tem os seus valores dinâmicos:
a. Clicamos na guia Inserir e Ilustrações->Caixa de texto
b. Nesta caixa de texto inclusa, clique na borda e depois na exibição da fórmula digite = e selecione a informação que deseja que apareça na sua caixa de texto.
c. Agora basta formatar com a fonte como desejar esta informação e dispor esta caixa de texto como você precisa, até que fique conforme a imagem que desejar. No nosso caso a imagem abaixo.
Na próxima sessão veremos como criar o indicador de seta de queda ou subida de cotação da bovespa na sua planilha.
5. Imagens dinâmicas para dashboards
No nosso caso temos uma imagem dinâmica, conforme citado acima, ela indica se as movimentação da bolsa foi positiva ou negativa no total. Para tanto utilizamos o seguinte procedimento.
a. Use a seguinte fórmula: =SE(C2>0;”ñ”;”ò”) a primeira parte verifica se a variação da bolsa de valores foi positiva, caso seja ele retorna ñ, senão retorna ò.
b. Selecione a célula com esta informação e mude a fonte para Wingdings. Isto fará com que apareçam as setas para cima ou para baixo, conforme a situação apresentada.
c. Clique sobre a célula e pressione Ctrl+C, depois clique em colar especial na opção Imagem vinculada.
Pronto! Agora você terá uma imagem vinculada ao resultado daquela célula e sempre que a imagem mudar a informação da sua imagem também será atualizada automaticamente.
6. Classificação automática com duplo clique usando VBA
Por último, temos uma classificação automática no Excel usando VBA. Com ela ao dar um duplo clique no cabeçalho da sua tabela, os dados das cotações da Bovespa serão automaticamente classificados no Excel. Para isso siga os passos:
a. Pressione CTRL+F11 para que o editor do VBA seja aberto, crie um módulo e cole o texto abaixo. Se tiver dúvidas de como fazer esta parte use este tutorial, inclusive em vídeo: https://www.guiadoexcel.com.br/habilitar-guia-desenvolvedor-no-excel-e-copiar-procedimentos-vba/
Public Sub lsClassificar(ByVal lNomeCampo As String, ByVal lClassificacao As String)
lNomeCampo = "[" & lNomeCampo & "]"
ActiveWorkbook.Worksheets("Comprar").ListObjects("Tabela2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Comprar").ListObjects("Tabela2").Sort.SortFields. _
Add Key:=Range("Tabela2[[#Headers]," & lNomeCampo & "]"), SortOn:= _
xlSortOnValues, Order:=lClassificacao, DataOption:=xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Comprar").ListObjects("Tabela2").Sort.Apply
End Sub
b. Este código é um procedimento que realizará a classificação recebendo o nome do campo e também se a classificação é ascendente ou descendente.
c. Clique duas vezes na planilha2 (Comprar) e cole o código abaixo:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value <> "" And Target.Row = 7 Then
lsClassificar (Target.Value), IIf(Planilha4.Range("F2").Value = 1, xlAscending, xlDescending)
End If
Cancel = True
End Sub
d. Agora volte ao Excel e veja que a sua planilha já está classificando automaticamente os dados ao dar o duplo clique sobre a linha do cabeçalho
Caso tenha dúvidas, veja passo-a-passo como fazer este processo no vídeo no topo deste artigo, e também faça o download da planilha de cotações de bolsa de valores BOVESPA clicando no botão abaixo.
