Ações no Excel – Como fazer análises da bolsa de valores no Excel
Nessa publicação vou te ensinar a criar uma análise de ações no Excel, ou seja, vamos criar indicadores que nos ajudam a entender algumas variações da bolsa!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
O que você aprende neste vídeo
Resposta rápida: A aula monta uma análise do BOVA11 de 2011 a 2018 no Excel a partir de datas e cotações. Você aprende a calcular a variação diária com =B3/B2-1, extrair ano e dia da semana, achar a variação média por dia da semana com MÉDIASE e contar altas e quedas, no total e por ano, com CONT.SE e CONT.SES.
Neste vídeo (40 min):
- 4:05 - Variação percentual é o valor de hoje dividido pelo de ontem, menos 1: de 100 para 200, a alta é de 100%
- 6:05 - Aplicar
=B3/B2-1na coluna C, formatar como porcentagem e aumentar as casas decimais para ler 0,66% - 10:05 - Extrair o ano da data com
=ANO(A2), usandoTabpara completar o nome da função - 12:00 -
=DIA.DA.SEMANA(A2)devolve de 1 a 7, com 1 para domingo, e a base só tem de 2 a 6 porque a bolsa não abre no fim de semana - 13:50 - Selecionar a coluna com
Ctrl + Shift + ↓e aplicar o formato personalizadoddddpara exibir segunda-feira no lugar do número - 18:10 - Selecionar a tabela inteira com
Ctrl + Shift + Espaço, aplicar Todas as Bordas e congelar a linha superior - 21:05 -
MÉDIASEpor dia da semana: intervalo na coluna do dia, critério na célula com segunda-feira e intervalo de média na coluna de variação - 24:00 - Com o intervalo de média na coluna C, o resultado mostra que às segundas-feiras a bolsa caiu em média 0,06%
- 26:15 -
CONT.SEcom critério entre aspas:">0"conta 999 dias de alta, e">=0"sobe para 1.012 com os 13 dias sem variação - 28:20 -
"<0"conta 965 dias de queda, e a soma dá 1.977 pregões, com 47 dias de alta a mais que de queda - 34:00 -
CONT.SEScom dois critérios, variação e ano, para contar os dias de alta de cada ano: 128 em 2011 - 36:35 - Travar as colunas C e D com
F4, que insere o cifrão, e arrastar a fórmula até 2018 deixando livre a célula do ano
Trechos do vídeo:
- O menos 1 é o que transforma a razão entre as cotações em alta ou queda:
=112/100-1resulta em 12% de alta,=20/100-1em 80% de queda, e o sinal negativo na coluna de variação indica que o preço caiu de um dia para o outro DIA.DA.SEMANAdevolve um número de 1 a 7, com 1 para domingo; com o formato personalizadodddd, o mesmo número aparece como segunda-feira sem mudar o valor da célula- Em
CONT.SE,CONT.SESeMÉDIASE, critério com operador vai entre aspas, como">=0"ou"<0"; na base do BOVA11 foram 1.012 dias de alta ou estabilidade contra 965 de queda - Nos dias de alta ou estabilidade, a variação média do BOVA11 no período ficou em cerca de 1,1%, calculada com
MÉDIASEusando a própria coluna de variação como critério e como média - Ao arrastar um
CONT.SESpela linha dos anos,F4fixa as colunas de variação e de ano com cifrão, enquanto a célula do ano fica relativa e avança de 2011 a 2018
Para fazer uma análise de ações no Excel, organize o histórico de cotações e crie colunas de apoio: Variação % (=B3/B2-1), ANO e DIA.DA.SEMANA formatada como dddd. Depois use MÉDIASE para a variação média por dia, CONT.SE para contar altas e quedas e CONT.SES para comparar o saldo ano a ano.
O que é a Bolsa de Valores?
A bolsa de valores é um ambiente de negociação no qual investidores podem comprar ou vender seus títulos emitidos por empresas, sejam elas com capitais públicos, mistos ou privados. Esse processo é intermediado com auxílio de correspondentes de negociações através de corretoras.
Para ter uma ideia mais clara do que é a bolsa, é possível fazer uma analogia. Imagine uma feira, onde produtores expõem seus produtos para a clientela comprar. A bolsa de valores opera de forma semelhante: se você decide vender uma ação e outro investidor tem interesse em comprá-la, ela será o ponto de encontro entre vocês.
O objetivo principal é configurar um ambiente seguro e organizado para essas negociações. Dessa forma, garantir que os investidores recebam as ações compradas de maneira eficiente e segura e que as transações sejam rápidas e práticas.
Fonte: https://www.btgpactualdigital.com/blog/investimentos/tudo-sobre-bolsa-de-valores
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Como criar uma análise de Ações no Excel?
Para começar a fazer a análise dos dados vamos começar com os dados iniciais da aba Base BOVA11.
Dados inicias da BOVA11
Esses são os dados que temos inicialmente, e a partir deles vamos criar uma análise mais detalhada de todos eles. Para isso serão adicionadas algumas colunas e utilizadas algumas fórmulas para obter essa análise mais completa.
O primeiro passo é criar uma coluna de Variação %, para que seja possível verificar a variação percentual da cotação de um dia para o outro.
Nesta coluna vamos utilizar uma fórmula simples para descobrir a porcentagem que variou podendo ser positiva ou negativa, vamos começar da segunda célula, pois a primeira representa o primeiro valor e ele não será comparado a nada. Fórmula =B3/B2 – 1, lembrando que essa coluna deverá ser formatada como percentual.
Variação das Ações no Excel
Como não temos valor inicial de variação vamos começá-lo com 0. Após escrever a fórmula na segunda célula basta dar um clique duplo no quadrado verde no canto direito inferior da célula para que a fórmula seja copiada para todas as células.
Agora serão adicionadas mais duas colunas, a primeira delas é de Ano, ou seja, vamos pegar o ano da data que já temos utilizando a fórmula ANO. A segunda coluna a ser criado é Dia da Semana, para essa será utilizada a fórmula DIA.DA.SEMANA, mas será feita uma alteração depois, pois essa fórmula retorna um número e não o dia da semana em si.
Inserindo as colunas de ano e dia da semana
O procedimento para estender as fórmulas para todas as outras células é o mesmo. Agora vamos organizar os dados da coluna E para que fiquem com os nomes dos dias da semana. Para isso é necessário selecionar todos os dados, para isso basta clicar na célula E2 e utilizar o atalho CTRL + SHIFT + SETA PARA BAIXO.
Ao selecionar toda a coluna vamos abrir a janela de formatação com o atalho CTRL + 1 e dentro dela selecionar a opção Personalizado. Dentro dessa parte vamos colocar no tipo dddd, desta forma vamos ter os nomes dos dias da semana nas células que foram selecionadas.
Alterando o formato das células de dia da semana utilizando a formatação personalizada
Feito isso teremos o seguinte resultado.
Variação das Ações no Excel por Dia da Semana
Para melhorar a visualização desses dados vamos fazer algumas modificações na parte de formatação. Utilizando a parte de fonte, alinhamento e número do Excel para isso.
Painel para formatação dos dados
Após fazer as modificações de acordo com o gosto do usuário essa é uma opção de resultado.
Tabela formatada para melhor visualização
Essas formatações são feitas para que os dados fiquem dispostos de uma melhor forma para que sejam analisados, não fica desorganizado e os cabeçalhos ficam destacados para verificar qual dado aquela coluna representa.
Para a utilização da opção de bordas é possível selecionar todos os dados utilizando o atalho CTRL + SHIFT + BARRA DE ESPAÇO, isso irá economizar muito tempo caso o usuário queira selecionar com o mouse.
Mais um detalhe importante, como temos uma tabela com muitos dados, a medida em que vamos descendo para observar os outros dados os cabeçalhos ficam lá em cima e ficamos sem referência do que significa cada coluna, para isso vamos ir até a guia Exibir no menu de congelar painéis e escolher a opção Congelar Linha Superior.
Opção para congelar a linha superior e manter o cabeçalho quando descer a tela
Desta forma a medida em que o usuário desce na tabela a primeira linha será mantida.
Verificando o congelamento na tabela
Agora serão feitas algumas análises a partir destes dados que foram organizados, para isso vamos colocar na coluna G os dias da semana e na coluna H utilizar a fórmula MÉDIASE para verificar a média da variação de cada um dos dias da semana.
Fórmula MÉDIASE para calcular a média de cada dia da semana
Em seguida essa fórmula será copiada para as outras células, desta forma teremos as médias das variações de cada dia.
IMPORTANTE: os dados da coluna G são colocados da mesma forma que na coluna E, ou seja, são números que correspondem a esses dias, caso o usuário escreva essa fórmula não irá funcionar.
Resultado da fórmula MÉDIASE para todos os dias da semana
A próxima análise a ser feita é sobre a quantidade de vezes que a bolsa subiu ou caiu e a média desses valores. Para calcular a quantidade que subiu será utilizada a fórmula CONT.SE com o critério de “>=0” enquanto para a quantidade que caiu será utilizada a fórmula CONT.SE com o critério de “<0”.
Quantas Ações no Excel subiram
Fórmula CONT.SE para a quantidade que caiu
Para a coluna de média de variação será utilizada a fórmula MÉDIASE com os mesmos critérios utilizados na fórmula CONT.SE.
A média da variação das Ações no Excel que subiram
Fórmula MÉDIASE para a quantidade que caiu
Agora será feita uma última análise, que será a análise de quantas vezes a variação subiu ou caiu para cada um dos anos analisados e a diferença entre esses valores para verificar se essa variação subiu mais do que caiu.
Análise das quantidade que subiu e desceu em cada um dos anos
Para essa análise será utilizada a fórmula CONT.SES, pois serão utilizados dois critérios para essa análise, uma para verificar se subiu ou caiu e o outro para verificar o ano.
Quantas Ações no Excel subiram em 2011
Fórmula CONT.SES para obter a quantidade que caiu em cada ano
Saldo das Ações no Excel
Em seguida basta arrastar as fórmulas para as outras células, desta forma não será necessário escrever diversas vezes a mesma fórmula. Feito isso teremos o seguinte resultado.
Resultado final da análise por ano
Feito isso temos uma análise mais completa do que tínhamos no início com apenas duas colunas de dados, ou seja, com poucos dados é possível obter diversas informações e ainda facilitar a análise desses dados. E ainda foi possível aprender algumas fórmulas do Excel para criar essas análises.
Caso você queira uma publicação sobre a fórmula CONT.SE, acesse: https://www.hashtagtreinamentos.com/cont-ses-excel
Nosso curso de excel funciona para qualquer um, desde a pessoa que não sabe absolutamente nada de Excel até aquela pessoa que quer aquele algo a mais para impressionar!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?
Perguntas frequentes
1. Como calcular a variação percentual de uma ação no Excel?
Divida a cotação do dia pela cotação do dia anterior e subtraia 1, como em =B3/B2-1, formatando a coluna como porcentagem. A primeira linha fica sem variação, porque não existe um dia anterior para comparar. Depois, um clique duplo na alça de preenchimento copia a fórmula para toda a coluna.
2. Como mostrar o nome do dia da semana a partir da data no Excel?
A fórmula DIA.DA.SEMANA devolve um número de 1 a 7. Para ver o nome escrito, selecione a coluna, abra a formatação com CTRL + 1, escolha Personalizado e digite dddd no campo Tipo. O conteúdo continua sendo o número, o que permite usar a coluna em fórmulas como MÉDIASE.
3. Como contar quantas vezes a ação subiu ou caiu no Excel?
Use CONT.SE na coluna de variação com o critério maior ou igual a zero para as altas e menor que zero para as quedas. Para saber a variação média em cada situação, repita os mesmos critérios com MÉDIASE. Para separar o resultado por ano, troque por CONT.SES e acrescente o critério do ano.
4. Quais fórmulas do Excel são usadas na análise de ações?
A análise usa fórmulas simples: ANO e DIA.DA.SEMANA para separar as datas, MÉDIASE para a variação média por dia da semana e CONT.SE e CONT.SES para contar altas e quedas por período. A formatação personalizada e o recurso Congelar Linha Superior ajudam a visualizar a base sem perder o cabeçalho.
