- Utilizar folhas do Google ou Excel, pode construir uma folha de cálculo personalizada que lhe permitirá ver a informação sobre os seus investimentos que mais importa
- Existem muitas ferramentas online para os investidores controlarem as suas carteiras
- podem não ter a informação formatada em da forma que deseja ou ilustrado da forma que deseja
- Folhas de cálculo permitem-lhe fazer uma ferramenta de análise de carteira que é exactamente da forma que deseja
Guardar algum tempo e descarregar uma cópia da Folha de Cálculo da Carteira aqui!
Não lhe apetece fazer bricolage?
Completar o formulário abaixo e clicar em Submit.
Confirmação de e-mail do componente, a pasta de trabalho será aberta num novo separador.
Como fazer uma carteira de acções em Excel, Google Sheets, ou qualquer outro software de folha de cálculo
Este “como fazer” pode ser seguido tanto em Excel como em Google Sheets. Realmente, qualquer software de folha de cálculo serve. As fórmulas devem ser as mesmas. Além disso, as opções de formatação e gráficos devem ser muito semelhantes.
Prefere ver um vídeo do que ler um tutorial? Veja este post:
TRACK YOUR STOCK PORTFOLIO RETURNS USING GOOGLE SHEETS (OR EXCEL) – STEP BY STEP TUTORIAL
I will be using Google Sheets in this tutorial. Gosto do Excel e utilizo-o frequentemente. Particularmente com alguns dos meus modelos mais “intensivos”. Neste caso, contudo, penso que o Google Sheets é uma opção melhor. Antes de mais, devido à sua funcionalidade GOOGLEFINANCE, que actualizará automaticamente certos campos para si (preço, volume, PE, EPS, e assim por diante, e assim por diante…). Em segundo lugar, como o Google Sheets é baseado na nuvem, pode aceder-lhe em qualquer lugar – incluindo o seu dispositivo móvel.
Microsoft tem uma versão baseada na nuvem do Office (Excel), mas eu não a recomendaria. Sou um fã da Microsoft em geral e um grande fã do Excel em particular. Mas, pensei que o Office 365 (ou como quer que se chame) ficou muito aquém das minhas expectativas. Contudo, apenas a minha opinião, use aquilo com que se sente mais à vontade.
Para poder seguir no Google Sheets, precisará de uma conta Google. Se ainda não tiver uma, clique aqui para instruções sobre como configurar uma.
Após ter a sua conta Google configurada, vá a Google Drive e seleccione “New” no canto superior esquerdo. Clique em “Google Sheets > Folha de cálculo em branco”.
Okay, deve estar pronto para ir, por isso vamos a isso.
As primeiras coisas primeiro – introduza os seus cabeçalhos
Antes de introduzir qualquer informação sobre os seus stocks ou quaisquer fórmulas para cálculos, vai querer lançar os alicerces da folha de cálculo determinando que informação quer ver.
Para este exemplo, as coisas foram mantidas relativamente simples. A quantidade de informação que se pode obter dos dados actuais ou históricos de um investimento é quase ilimitada.
Mas, uma vez que este “como fazer” se destina a servir de ponto de partida, tentei manter as coisas elementares. Eis o que vamos analisar neste exemplo:
- Nome da empresa (fundo)
- Símbolo de um ticker
- Data de compra
- Preço de compra
- Quotas
- Custo de compra
- Preço actual
- Valor corrente bruto
- Dividendos totais recebido
- Valor actual líquido
- Ganho ou perda total em dólares
- Ganho ou perda total percentual
- Ganho ou perda anual
Introduzir estes cabeçalhos nas células ao longo do topo da folha de cálculo. Pessoalmente, gosto de deixar um pequeno espaço no topo e no lado esquerdo das minhas folhas de cálculo. Assim, vou começar em B5 e entrar em N5.
Adicionalmente, gosto de saber a data de quando actualizei pela última vez uma folha de cálculo como esta. Portanto, em B2, introduzirei “Portfolio updated:” e ousarei tudo o que acabei de introduzir (Ctrl+B). Eis o que isto parece:
Introduzir alguns dados básicos de stock
Como mencionado anteriormente, esta folha de cálculo da carteira consistirá em informação que já conhece e informação que precisa de calcular.
Os campos Nome, Símbolo, Data de Compra, Preço de Compra, e Acções são todas informações que já deve ter. Portanto, vá em frente e introduza que.
Toda esta informação, no meu exemplo, foi escolhida ao acaso. Pode ter mais acções ou menos acções.
Google Sheets e Excel podem certamente tratar de tudo o que tem na sua carteira.
Pode ser preciso alguma escavação da sua parte para desenterrar esta informação – mesmo que tenha um corretor online. Particularmente a Data de Compra. Mas, se quiser um cálculo exacto do seu Ganho/Perda Anualizado faça o seu melhor para o encontrar.
Quer saber como adicionar stocks de qualidade à sua carteira? Leia este post:
PONTOS DE VALORIZAÇÃO DETALHADOS COM WALK-THROUGH
E se eu tiver datas de compra diferentes para lotes diferentes do mesmo stock?
Isso é um pouco complicado. Para ser completamente honesto, talvez queira uma folha de cálculo de carteira mais sofisticada. Mas, há algumas formas de trabalhar em torno dela.
P>Primeiro, poderia apenas listar cada lote separadamente. O único problema potencial aqui é quando se trata de atribuir dividendos (se os houver) aos diferentes lotes.
Segundo, poderia agrupar todos os lotes. Mas, a menos que calcule uma média ponderada da Data e Preço de Compra, muitos dos seus cálculos serão errados.
Pessoalmente, separaria os lotes e depois agruparia todas as informações sobre os dividendos sob o lote que comprou primeiro – para simplificar.
Aqui está o que temos até agora:
Tempo de formula! Custo de compra
Comecemos simples.
Custo de compra = Preço de compra × Acções
No risco de simplificar demasiado as coisas – esclarecerei para aqueles que são completamente novos em folhas de cálculo…
Todas as fórmulas começam com um sinal igual (=). É assim que o Google Sheets e o Excel sabem fazer um cálculo.
Então, na célula G6, escreva “=E6*F6” e prima Enter. O asterisco (*) significa multiplicar.
No meu exemplo, para o símbolo de acções ZF, o resultado é $1,990 ($15.31 Preço de compra vezes 130 Acções).
Não digite mais do que precisa, copie para baixo!
Agora, precisa de duplicar esta fórmula para cada acção na sua carteira.
Mas, não vá à linha 7 e digite “=E7*F7”. Depois, fila 8 e digite “=E8*F8”. E assim por diante…
Copie a fórmula em G6 (Ctrl + C) e depois cole (Ctrl + V) nas células G7 até G50 (ou qualquer linha em que terminar).
Or, alternativamente, Google Sheets e Excel dão-lhe a capacidade de duplicar o que está numa célula (a fórmula), clicando no pequeno quadrado na parte inferior direita da célula activa, mantendo o botão do rato pressionado, e arrastando-o para onde quiser que ele copie. Esta é a forma mais rápida e fácil de o fazer.
Automate Current Price and Gross Current Value in Google Sheets
Uma das razões pelas quais escolhi utilizar o Google Sheets para este tutorial é devido à função GOOGLEFINANCE. Eu sei que o Excel tinha a funcionalidade MSN Money. Mas, se actualmente têm algo semelhante, não estou familiarizado com ele.
O que é GOOGLEFINANCE?
Dá-lhe a capacidade de se ligar ao Google Finance através de uma fórmula e preencher o Google Sheets com informação sobre um stock individual.
Por exemplo, se, na célula H6, escrever “=GOOGLEFINANCE(C6, “preço”)” o Preço Actual do stock introduzido na linha 6 será preenchido e automaticamente actualizado. Note o sinal de igual (=), e as cotações em torno de “preço”. Não digite a primeira e a última cotação, no entanto. Digite apenas o que está em negrito.
Com a função GOOGLEFINANCE, não tem de se preocupar em procurar o preço (e outras informações básicas, se desejar) e depois digitar manualmente no Google Sheets! Sempre preciso e sempre actualizado (embora não em tempo real).
Copiar a função GOOGLEFINANCE para baixo para todos os seus stocks. Se estiver a utilizar o Excel, ou optar por não utilizar a função GOOGLEFINANCE, terá de introduzir manualmente o Preço Actual.
Com o Preço Actual, pode agora calcular um Valor Actual Bruto. Porquê “Valor Bruto”? Porque, mais tarde, vamos adicionar dividendos para obter um Valor Actual Líquido.
Valor Actual Bruto = Acções × Preço Actual
Na célula I6, digite a seguinte fórmula: “=F6*H6”. Copie essa fórmula para baixo para todas as suas acções.
Aqui está como deve ser até agora:
Dividendos são uma parte importante dos seus retornos – não se esqueça de os incluir!
Felizmente, não há maneira (que eu saiba) de importar automaticamente os dados dos dividendos para as acções que detém. Actualizar esta informação é de longe o passo mais trabalhoso neste tutorial.
Sim, a função GOOGLEFINANCE pode importar a percentagem de rendimento para um determinado stock. Mas, uma vez que esta folha de cálculo da carteira de acções está centrada nos rendimentos totais, isso não vai ajudar muito.
Quer identificar as acções que pagam (qualidade) rendimentos elevados? Leia este post:
ELEVADOS DIVIDENDOS – CARACTERÍSTICAS DE QUALIDADE DE EXERCÍCIOS
Se possuir as suas acções através de uma corretagem online, como a maioria das pessoas faz, deverá poder aceder ao histórico de pagamento de dividendos das acções individuais que possui.
Por exemplo, TD Ameritrade permite-lhe exibir os dividendos pagos por uma acção específica no seu histórico de transacções.
Se esta informação não puder ser facilmente recuperada da sua corretora, então terá de fazer algum trabalho de casa. Um bom lugar para começar seria a página de História dos Dividendos Nasdaq. Aqui, pode encontrar a Data de Pagamento e por acção os montantes de dividendos pagos por cada acção. Terá de multiplicar os montantes por acção pelo número de acções detidas para obter o dividendo total pago.
Total os seus dividendos recebidos por cada uma das suas acções e introduza essa informação na rubrica Total de Dividendos Recentes (coluna J).
Por uma questão de exactidão, certifique-se de que inclui apenas os dividendos pagos a si enquanto era proprietário das acções. Certifique-se também de actualizar esta informação sempre que uma acção paga um dividendo.
É mais problemático do que deveria ser, com certeza. Mas, como eu disse, os dividendos podem dar uma enorme contribuição para os retornos recebidos por uma determinada acção. Se isto parecer demasiado incómodo, pode-se renunciar a incluir dividendos. Basta saber que os seus números de retorno não serão 100% exactos.
Totaling all of your returns
Com a informação sobre dividendos recolhida, pode agora calcular o Valor Corrente Líquido. Isto totalizará os seus retornos dos ganhos de capital e dos dividendos e dar-lhe-á uma imagem precisa do desempenho das acções.
Valor Corrente Líquido = Valor Corrente Bruto + Total de Dividendos Rec’d
Na célula K6, introduza o seguinte: “=I6+J6”. Depois, copie essa fórmula para baixo para o resto dos seus stocks.
Só faltam mais algumas colunas! Aqui está como deve ser agora a sua carteira de acções:
Como estão realmente as suas acções?
As três últimas colunas serão utilizadas para calcular os retornos de cada stock. Vamos concentrar-nos nas duas primeiras colunas primeiro.
Ganho/Perda Total $ = Valor Corrente Líquido – Custo de Compra
Esta é a diferença entre o valor do stock agora (incluindo dividendos recebidos) e o que pagou por ele.
Na célula L6, introduza a seguinte fórmula: “=K6-G6”. Como habitualmente, copie isso para o resto das acções.
Ganho/Perda Total % = Ganho/Perda Total $ ÷ Custo de Compra
Na célula M6, digite a seguinte fórmula: “=L6/G6”. Copie-a para baixo…
Esta fórmula compara o seu Ganho/Perda em dólares com o que pagou pelas acções que possui. O resultado é uma percentagem e diz-lhe qual tem sido o desempenho total do seu stock – até agora.
Mas, o que é um bom Ganho/Perda Total %? Isso é difícil de responder. Um ganho de 5% é bom ao longo de um dia ou de uma semana. Um ganho de 50% não é tão bom se for ao longo de vinte anos.
Veja analisar a sua carteira de investimentos? Leia este post:
AREJA SATISFEITO COM O SEU PORTO DE INVESTIMENTO?
Como comparar ganhos e perdas – maçãs com maçãs
É por isso que penso que deve calcular o Ganho/Perda Anualizado.
Vou avisar-te, a fórmula é um pouco complicada. Mas, quando a conseguir, fornecerá uma visão valiosa sobre o verdadeiro desempenho de cada stock. Permitirá comparar melhor os stocks uns contra os outros.
Digite o seguinte na célula N6: “=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1”.
Yikes!
Não tem de saber necessariamente porque funciona. Basta saber que funciona. Não o deixe assustar, isto fará mais sentido se eu o avariar.
Antes de o fazermos, certifique-se de que tem uma data actualizada do Portfólio na célula C2. Esta data é utilizada para determinar a quantidade de tempo que passou desde que o stock foi comprado e é crítica para o cálculo do Ganho/Perda Anual.
A primeira parte da fórmula, “(K6/G6)”, compara o Valor Corrente Líquido com o Custo de Compra. Calcula a relação entre o valor agora e quando comprou o stock.
A parte seguinte, “(1/(YEARFRAC(D6,$C$2))” parece complicada, mas, na sua maioria, tudo o que está a fazer é calcular a quantidade de tempo que passou desde que comprou o stock. O que é crítico, porém, é que inclua os sinais do dólar ($) em frente do “C” e do “2”. Isto assegura que a fórmula está sempre a comparar a Data de Compra de uma acção com a mesma Data de Actualização da Carteira. Os sinais do dólar ($) bloqueiam essa célula na fórmula para que esta não mude quando a copia para baixo.
Finalmente, o “-1” no final transforma o resultado da equação numa percentagem que faz sentido.
É isso! Mais uma vez, a fórmula para a célula N6 é: “=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1”. Digite isso em. Copie-a. E, essa parte desagradável do negócio acabou!
Aqui está como deve ser com toda a informação preenchida para cada stock:
Analizámos cada acção – e a carteira global?
Enquanto que poderia desistir aqui e (com um pouco de formatação) ter uma folha de cálculo de carteira de acções perfeitamente boa – vamos avançar um pouco mais e dar os toques finais nesta coisa.
Para a maioria dos nossos campos (excepto Preço Actual), podemos analisar a carteira em conjunto. Dessa forma podemos comparar as nossas acções individuais umas com as outras e compreender como elas contribuem para o retorno global da carteira.
Para a maioria dos campos, isto é bastante fácil. É simplesmente uma questão de totalizar a coluna inteira. Outros utilizarão exactamente a mesma fórmula que utilizou para os stocks individuais. Um último campo é um pouco complicado de calcular para toda a carteira – Data de Compra. Mas, não nos adiantemos, vamos começar com os fáceis…
Campos que são resumidos com SUM
Custo de Compra, Valor Corrente Bruto, Total de Dividendos Recentes, e Valores Correntes Líquidos para toda a carteira são simplesmente somas dos valores para cada stock.
Para somar estes valores, usaremos a função SUM no Google Sheets/Excel. Comece na célula G4 (acima Custo de Compra) e introduza o seguinte: “=SUM(G6:G50)”.
Não é necessário parar na célula G50. Se a sua lista de stocks vai além da linha 50, então pode ir para G100, G500, G1000, ou por mais baixo que seja necessário. Basta certificar-se de que inclui o Custo de Compra de cada stock na equação. Não fará mal incluir células em branco na fórmula.
p>Introduzir a função SUM para o Valor Corrente Bruto, Dividendos Totais Rec’d, e Valor Corrente Líquido na linha 4. Por exemplo “=SUM(I6:I50)”, “=SUM(J6:J50)”, e “=SUM(K6:K50)”.
Campos que são resumidos com uma equação
Os campos Ganho/Perda Total $, Ganho/Perda Total %, e Ganho/Perda Anualizado usam as mesmas equações para calcular para toda a carteira e para os stocks individuais dentro de.
Lembrar como copiou essas fórmulas para baixo em vez de as reentrar para cada stock? O mesmo pode ser feito com os cálculos da carteira para poupar tempo.
Click and drag (highlight) across cells L6, M6, and N6, then press Ctrl+C (copy) on your keyboard. Depois clique na célula L4 e prima Ctrl+V (colar). Viola! A folha de cálculo utilizará automaticamente os totais previamente calculados para determinar os valores de Ganho/Perda para toda a carteira de acções.
Uma última fórmula…
Talvez esteja a perguntar-se como pode ser calculado um Ganho/Perda anualizado para toda a carteira se não houver Data de Compra para a carteira como um todo?
Como diabo se estabeleceria uma Data de Compra para um monte de stocks diferentes?
Para conseguir isto, e obter um Ganho/Perda Anualizado preciso para toda a carteira de stocks, precisamos de calcular uma Data de Compra média ponderada para toda a carteira de stocks.
Por quanto serão ponderadas as Datas de Compra? Por Custo de Compra.
Isto é feito usando uma função no Google Sheets/Excel chamada SUMPRODUCT. Não há necessidade de entrar nos detalhes sobre como/porquê isto funciona. Basta saber que funciona. O resultado é uma única Data de Compra para toda a sua carteira.
Na célula D4, introduza a seguinte fórmula: “=SUMPRODUCT(D6:D50,G6:G50)/G4”. Lembre-se – se a sua lista de stocks ultrapassar a linha 50, altere D50 e G50 para D100/G100, D150/G150…o que precisar.
Não se assuste se o resultado dessa fórmula for um grande número e não uma data. Isso é simplesmente uma questão de formatação e entraremos nisso a seguir.
Aqui está como a sua folha de cálculo de carteira de acções (quase concluída) deve ser agora:
Esta parece ser uma boa informação, mas parece uma porcaria!
Yep, estás correcto. É altura de ajeitar um pouco as coisas e tornar esta informação mais legível.
Formatting é tudo uma questão de preferência. Não há uma única forma correcta de o fazer.
P>Passarei por algumas das coisas que gosto de fazer em termos de formatação e poderá copiar o que quiser. Além disso, sinta-se à vontade para explorar por si próprio. A maioria das opções de formatação estão disponíveis abaixo do menu principal, no topo da folha de cálculo. Passe o rato por cima dos pequenos ícones, e verá aparecer um texto a explicar o que cada opção faz.
Datas de Compra como datas – se ainda não as tiver. Seleccione essas células e clique no ícone que tem “123▼”. Abaixo na lista, deverá ver algumas opções para um formato de data.
Outra coisa que quase sempre faço quando formato uma folha de cálculo é colorir nas células que têm fórmulas. Isso serve como um lembrete visual, para mim, para não as digitar. As células que ficam brancas são as variáveis que podem ser alteradas. O pequeno ícone da lata de tinta é o que se usa para mudar a cor da célula.
P>Pode formatar os dados numéricos como números ou moeda. Mostrar mais ou menos decimais com os ícones que têm “.0←” ou “.00→” neles.
P>P>Posicionarei também o Ganho/Perda Total % e o Ganho/Perda Anual para um formato percentual. Use o botão “%” no menu.
Por último, eu gosto de negrito os meus cabeçalhos, e, normalmente, os meus totais.
Ganho, a formatação é uma questão de preferência, por isso não há respostas erradas. Brinquem com ela e façam-na parecer como quiserem. Se alguma vez quiser limpar a tabela – seleccione as células que pretende alterar, clique em “Formatar” no menu principal, e depois em “Limpar formatação”
Below é como optei por formatar a minha folha de cálculo de carteira de acções. Vai notar os gráficos do lado direito. O que, entraremos no próximo…
Utilizar gráficos para melhor compreender a sua carteira e retornos
P>Pode fazer qualquer coisa na sua folha de cálculo. Pode até criar um gráfico de barras comparando Preços de Compra ou Acções, se quiser. No entanto, não tenho a certeza porque o faria?
Na minha folha de cálculo, como pode ver acima, optei por desenhar duas coisas.
- Uma comparação do Ganho/Perda Anual entre todas as minhas acções usando um simples gráfico de coluna
- Um gráfico de tarte que mostra a composição da minha carteira – por acção
Criar estes gráficos foi rápido e fácil no Google Sheets. Fazê-lo no Excel também é fácil (“Inserir”, depois “Gráficos”). Mas, haverá algumas diferenças entre os dois visualmente.
Para fazer o primeiro gráfico, basta destacar todos os Símbolos do seu stock (não o cabeçalho, C5). Depois, premindo o botão Ctrl, seleccione todas as percentagens de ganho/perda anualizadas (mais uma vez, não o cabeçalho, N5). O botão Ctrl permite-lhe seleccionar várias células que não estão ao lado umas das outras.
No menu “Inserir”, seleccione “Gráfico”. Deverá ver aparecer um gráfico de colunas com os Símbolos no eixo x e as percentagens no eixo y.
Click and drag the chart where you want it. Se fizer duplo clique, verá o editor de gráficos. Depois de seleccionar “Personalizar” e depois “Chart & títulos de eixo” pode introduzir um título no campo “Título de texto”. Como pode ver, acabei de nomear o meu Ganho/Perda Anual.
Se seleccionar os Símbolos e Valores Correntes Líquidos e inserir um gráfico, provavelmente obterá outro gráfico de coluna. Penso que um gráfico de tarte representa melhor esta informação. Felizmente, mudar o tipo de gráfico é fácil. Faça duplo clique neste novo gráfico e no editor de gráficos clique em “Setup” e altere o “Tipo de gráfico”, seleccionando um gráfico de tarte a partir do menu pendente. Pode estar sob o título “SUGESTÃO”.
Aí vamos nós. Uma folha de cálculo de carteira com informação útil e bem formatada.
Como fazer uma carteira de acções em Excel (ou Folhas)
Folhas de cálculo permitem aos utilizadores analisar as suas carteiras e retornos de praticamente qualquer forma imaginável. A função GOOGLEFINANCE em Folhas automatiza actualizações para muitas informações frequentemente referenciadas sobre acções e fundos mútuos.
Tentei explicar em detalhe como a minha carteira de acções de exemplo foi feita. Mas em que (se alguma coisa) ficou pendurado,?
Existe informação adicional que gostaria de ter na sua folha de cálculo da carteira de acções? O que gostaria de analisar numa base contínua?
Junte-se à conversa no Twitter!