PLANILHA DE AÇÕES V2.50 Manual do usuário Disponível em: http://planilhadeacoes.blogspot.com/
INTRODUÇÃO ........................................... .................................................................. .............................................. .............................................. ........................... 3 TERMOS E CONDIÇÕES CONDIÇÕES DE USO.................... USO ............................................ ............................................... .................................... ............. 3 GUIA RÁPIDO ............................................ ................................................................... .............................................. .............................................. ........................... 4 REGISTRO DE ORDENS E NOTAS .............................................. ..................................................................... ................................ ......... 6 PLANILHA NOTA........................................................................ PLANILHA NOTA....................................................................................................................... ............................................... 6 BOTÕES .................................................. .............................................................................................................. ............................................................ ................................... 6 MOVER DADOS DAS OPERAÇÕES ....................................................... .................................................................................................... ............................................. 7 INSERIR / EXCLUIR LINHA.......................................................... LINHA .......................................................... ....................................................... 7 INSERIR / EXCLUIR COLUNA.................................. COLUNA............................................................................................. ........................................................... ................ 7 CONSULTA DE NOTAS ........................................................ ................................................................................................................... ........................................................... ...... 8 AJUSTE DO LUCRO DAY TRADE .......................................................... ............................................. 9 DETALHES DA OPERAÇÃO DAY TRADE ........................................................ ......................................................................................... ................................. 10 IRRF – IMPOSTO DE RENDA RETIDO NA FONTE .................................................. .......................................................................... ........................ 10 DIGITAR LÍQUIDO DA NOTA OU DESPESA...................................................... DESPESA .............................................................................. ........................ 10
PLANILHA ORDENS............................................................................................ ORDENS................................................................................................................ .................... 11
COPIAR DADOS DE UM ARQUIVO PARA OUTRO .............................................. .............................................. 12 DESDOBRAMENTO DE AÇÕES ............................................... ...................................................................... .................................. ........... 13 CONSULTANDO A CARTEIRA CARTEIRA DE AÇÕES .............................................. ............................................................. ............... 14 APURANDO O LUCRO E IMPOSTO IMPOSTO A PAGAR..................... PAGAR ............................................ .................................. ........... 18 RELATÓRIO R1 – R1 – SOMENTE SOMENTE MERCADO À VISTA (PLANILHA “VENDAS”)............... ”)..................... ........ 18 IRRF, DARF E PREJUÍZOS DE ANOS ANTERIORES ...................................................... .................................................................... .............. 19
RELATÓRIOS R2 - MERCADO À VISTA E OPÇÕES.................................................... OPÇÕES............................................................ ........ 20 COMO GERAR OS RELATÓRIOS R2: ..................................................... ................................................................................................ ........................................... 20 VALORES E FÓRMULAS.................................................... FÓRMULAS............................................................................................................... ........................................................... .... 21 DADOS QUE PODEM SER ALTERADOS/DIGITADOS: ..................................................... ................................................................... .............. 23 INSERIR / EXCLUIR LINHA E/OU COLUNA ................................................... .................................................................................... ................................. 23 RESUMO FINANCEIRO ....................................................... .................................................................................................................. ........................................................... .... 24 DATAS DAS OPERAÇÕES........................................ OPERAÇÕES................................................................................................... ........................................................... .............. 24 RESUMO MENSAL ..................................................... ................................................................................................................ ........................................................... .............. 24 PREJUÍZOS DE ANO ANTERIORES........................................................................... ANTERIORES................ ........................................................... ........................ 24 PLANILHA rGER - TABELA DO IRRF e DARF............................................................... DARF... ............................................................ .............. 25 RENOMEAR OU EXCLUIR RELATÓRIOS ........................................................ ......................................................................................... ................................. 25 GRAVAR DADOS ....................................................... .................................................................................................................. ........................................................... .............. 26 TABELA DE VENCIMENTO DE OPÇÕES .................................................... ..................................................................................... ................................. 26 INICIAR NOVO ANO ............................................................ ........................................................... .... 27 PLANILHAS “r”, “rr”, “rp” ......................................................... ..................................................... 28 RELATÓRIO DAY TRADE RESULTADO MENSAL .................................................... ............................................................................ ........................ 28 RELATÓRIO DAY TRADE E NÃO DAY TRADE – RESULTADO DIÁRIO ......................................... 29 TABELAS BMF e TERMO em “rGER”...................................................................... “rGER”........... ........................................................... ........................ 29
PERGUNTAS E RESPOSTAS RESPOSTAS ............................................. .................................................................... .......................................... ................... 30 2
Introdução Em 2007 eu e minha namorada começamos a negociar ações no mercado à vista da bovespa. Algumas semanas depois, ela me pediu que a ajudasse a fazer uma planilha capaz de calcular o preço médio de compra e o lucro na alienação das ações. Fizemos alguns esboços no restaurante da FEA-USP... e passados alguns anos, a planilha ganhou mais de 8 mil linhas de programação em visual basic, eu um belo prejuízo e minha namorada um esposo chato. Ao longo desses anos, a idéia sempre foi deixar a planilha cada vez mais automatizada. Hoje precisamos apenas digitar as notas de corretagem ou lançar as ordens de compra e venda diretamente em uma lista de ordens. A automatização da planilha se encarrega de todo o resto: informa a carteira em qualquer data do ano, o lucro na carteira com respectivas cotações atualizadas pela internet, monta um relatório de vendas detalhado com volume, lucro, IRRF a compensar e DARF a pagar, consolidados mês a mês, etc. A pedido de meu sogro, desenvolvi entre 2008 e 2009 um segundo formato de relatório de vendas, que aceita tanto mercado à vista como mercado de opções (página 20 manual). Eu e minha esposa não utilizamos este segundo formato, mas sim o primeiro que desenvolvi para ela (página 18 do manual), que particularmente gosto mais. A escolha de qual formato utilizar é uma questão de gosto, já que os resultados apresentados apresentados são idênticos.
Habilitando a automatização da planilha: Como a planilha contém programação em visual basic, é necessário habilitá-la para que automatização funcione. Macro Segurança... Segurança... Nível Nível de segurança selecione: selecione: MÉDIO. No excel 2003, vá em : Menu Ferramentas Macro Informações Config. Config. da central de confiabilidade Configurações Configurações de Macro No excel 2010, vá em: Arquivo Informações : Desabilitar selecione : Desabilitar
todas as macros com notificação.
Feche o Excel e abra a planilha novamente. A seguinte pergunta será feita: (Excel 2003)
(Excel 2010)
Escolha Habilitar Macros / Habilitar Conteúdo.
Termos e condições de uso O uso de meu trabalho é gratuito - desde que para uso pessoal ou de instrução - e de inteira responsabilidade do usuário. A utilização de meu trabalho com finalidade comercial - por exemplo, em empresas de contabilidade, não está autorizada e está terminantemente proibida a partir de 01 de maio de 2012. 2012.
3
GUIA RÁPIDO Lançando Notas e/ou Ordens: Na planilha “Nota”, digite a nota de corretagem, confira os dados e depois clique no botão gravar nota:
Se você não tiver a nota em mãos, pode adiantar o trabalho lançando diretamente suas ordens na planilha “Ordens”. Os custos serão estimados de acordo com a tabela da planilha “C”. Abaixo, por exemplo, escolheu-se corretagem “p” (padrão).
Depois, quando receber a nota de corretagem destas operações, vá na planilha “Nota”, carregue a nota e confira tudo. Se houver diferença, corrija-a (para que os dados digitados fiquem idênticos aos da nota de corretagem) e clique em “Gravar Nota”. Com isso, você dá um respaldo documental à sua contabilidade.
Migrando sua carteira para esta planilha: Se você já possui uma posição acionária e quer migrar seu controle de ações para esta planilha, pode fazer isto de duas formas: (i) a partir do “zero”, digitando todas as notas de corretagem que formam sua carteira, ou (ii) criando uma nota que contenha sua posição acionária. Para esta nota, coloque “C” para posições compradas, “V” para vendidas, ativos com respectivas quantidades e preços médios unitários com custos embutidos. No campo de despesa geral da nota, deixe ZERO (já que todas as despesas já deverão estar embutidas nos preços médios unitários de cada ativo). Grave esta nota com o identificador que quiser (ex: “posição”) e na data coloque a data de sua carteira. Para a planilha, é como se toda sua carteira tivesse sido adquirida nesta data. A partir daí, passe a lançar seus dados pela interface da planilha.
4
Consultando a Carteira: Para consultar a carteira na data de hoje, dê um duplo clique na própria data:
Apuração do Lucro: - Relatório R1: somente para mercado à vista. É gerado na planilha “Vendas”. duploclique na data final para gerar o rel. de apuração do lucro.
- Relatórios R2: para mercado à vista e de opções. Clique em R2: Vista + Opções . Dependendo do mercado e do tipo de operação, poderão ser criadas até quatro planilhas específicas e uma geral (o processo é todo automático): Planilha rPOS-V..: Relatório de vendas finais (não day trade) no mercado à vista; ** Planilha rDT-V....: R elatório de vendas day trade no mercado à vista; Planilha rPOS-O.: Relatório de vendas finais (não day trade) no mercado de opções; * ** Planilha rDT-O...: R elatório de vendas day trade no mercado de opções; * Planilha rGER ....: Relatório Geral, consolidando todas as informações e imposto a pagar.
* para que a planilha possa identificar um ativo como uma opção, seu nome deve ter sido digitado pelo código de negociação bovespa – exemplo: petrh30. ** para ver a posição em carteira, clique em Rel.Posição em rPOS-V ou rPOS-O.
5
REGISTRO DE ORDENS E NOTAS A entrada de dados é feita através das planilhas “Ordens” e “Nota”.
PLANILHA NOTA Permite ao usuário digitar, alterar, consultar ou excluir notas de corretagem de suas operações com ações, opções e termos (para termos – consulte página 30 deste manual). Após digitar ou consultar uma nota de corretagem, você deve: (i) verificar os volumes negociados, o líquido da nota e – caso exista – o lucro day trade. Eles devem bater rigorosamente com os valores informados na nota de corretagem de sua corretora. Isso dá um respaldo documental a sua contabilidade; (ii) clicar no botão Gravar Nota .
BOTÕES Botão Ordens :
Vai para a planilha “Ordens” (pg. 11).
Botão Carteira :
Vai para a planilha “Carteira” (pg. 14).
Botão R1: Vista :
Vai para a planilha “Vendas”, que gera os relatórios R1 (pg. 18).
Botão R2: Vista + Opções : Gera os relatórios R2 (pg. 20). Botão Gravar Nota : Após terminar de digitar uma nota, deve-se apertar esse botão, para que a nota seja gravada na base de dados (planilha "Ordens" e planilha "Diversos"), esperar que apareça a mensagem de confirmação da gravação e depois desapareça. Botão Limpar : - Botão esquerdo: limpa a nota da tela, mas não a exclui da base de dados; - Botão direito....: limpa toda a tela, também sem excluir da base de dados; Botão Consultar :
- Botão esquerdo..............: - Botão direito..................: - Botão direito + CTRL....: - Botão do meio................: - Botão esquerdo + CTRL:
Botão Excluir :
- botão esquerdo: exclui da base de dados a nota exibida;
lista TODAS as notas; lista as notas dos últimos 30 dias; lista as notas dos últimos 90 dias; lista as notas mês corrente; lista SOMENTE as notas que batem com os critérios de busca digitados em e .
- botão direito....: exclui TODAS as notas da base de dados,
deixando todas as planilhas limpas, sem dados;
6
MOVER DADOS DAS OPERAÇÕES Pode-se mover dados das operações para cima ou para baixo, DESDE DE QUE ESTEJAM “DENTRO” DAS 4 COLUNAS VERDE, como mostra a figura abaixo:
INSERIR / EXCLUIR LINHA A inserção/exclusão de UMA LINHA POR “INTEIRO” NÃO É PERMITIDA, porque interfere na automatização. Porém, PODE-SE INSERIR/EXCLUIR UM “TRECHO” DE LINHA, da seguinte maneira: Seleciona-se a região onde se deseja inserir as linhas, SEMPRE DELIMITADO PELAS 4 COLUNAS VERDE. Não selecionar dados para fora deste intervalo de colunas. Apertar sobre a seleção o botão da direita do mouse e selecionar “inserir...”, depois “deslocar células para baixo”:
As linhas são inseridas apenas DENTRO DAS COLUNAS do trecho selecionado: A idéia é que a inserção / exclusão de linhas fique dentro destas 4 colunas verdes. Nenhuma linha deve ser inserida para fora dessas colunas.
INSERIR / EXCLUIR COLUNA A inserção/exclusão de colunas NÃO É PERMITIDA, porque interfere na automatização.
7
CONSULTA DE NOTAS Exemplo 1: para mostrar na tela TODAS as notas, clique em Consultar :
Exemplo 2: para mostrar na tela somente as notas de fevereiro de 2008, digite: Data ...............:
*02/2008
Identificador ......:
Exemplo 3: para mostrar na tela somente as notas de 2007, digite: Data ...............:
*2007
Identificador ......:
Exemplo 4: para mostrar na tela a nota no. 50001 Data ...............: Identificador ......:
*50001
Exemplo 5: para mostrar na tela notas de 02/2008 com numeração 001 Data ...............: Identificador ......:
02/2008 001
... e clique em Consultar com o botão esquerdo + CTRL.
Exemplo 6: para mostrar na tela as notas dos últimos 30 dias, clique em Consultar com o botão direito. Exemplo 7: para mostrar na tela as notas dos últimos 90 dias, clique em Consultar com o botão direito + CTRL. Exemplo 8: para mostrar na tela as notas do mês corrente, clique em Consultar com o botão do meio.
8
AJUSTE DO LUCRO DAY TRADE
Máximo lucro possível Lucro Day trade ....:
+
Digite “+” para visualizar o máximo lucro day trade que pode ser obtido.
>
Idem.
-
Digite “-” para visualizar o mínimo lucro day trade que pode ser obtido.
Ou Lucro Day trade ....:
Mínimo lucro possível Lucro Day trade ....:
Ou Lucro Day trade ....:
<
Idem.
Valor específico Lucro Day trade ....:
valor
Digite um valor (o valor projetado na nota de corretagem de sua corretora) a ser buscado para o lucro day trade.
Escolha manual das ordens c c
petr4
1.000
19,00
petr4
100
20,00
c
petr4
200
c c
petr4
300
21,00 22,00
petr4
400
23,00
c
petr4
2.000
v
petr4
100
24,00 20,00
v
petr4
200
21,00
v
petr4
300
22,00
v
petr4
400
23,00
v
petr4
500
19,00
Duploclique move a linha (se possível) para fora das operações day trade. Duploclique move a linha (se possível) para dentro das operações day trade. OBS: - Linhas sublinhadas são operações day trade - Linha sublinhada e pintada são operações parte day trade, parte não day trade.
9
DETALHES DA OPERAÇÃO DAY TRADE IRRF
Final ........:
duploclique: mostra/esconde detalhes das operações daytrade
Lucro Day trade ....: IRRF
Day Trade ....:
IRRF – IMPOSTO DE RENDA RETIDO NA FONTE IRRF
Final ........:
Lucro Day trade ....: IRRF
Day Trade ....:
Se o IRRF estiver indicado na nota como debitado, digite “D”. Caso contrário, deixe vazio. Idem, para IRRF day trade.
DIGITAR LÍQUIDO DA NOTA OU DESPESA
DIGITAR LÍQUIDO DA NOTA:
Clique aqui
Clique aqui
DIGITAR DESPESA:
OBS: (i) a escolha de digitação da despesa, apesar de dar um pouco mais de trabalho ao usuário, diminui a chances de erros de digitação, pois permite confrontar o líquido da nota informado pela planilha com o informado na nota de corretagem. Qualquer diferença é um alerta: algo foi digitado errado. Sempre verifique o líquido da nota. (ii) Quanto as despesas, o usuário pode digitá-las como quiser – por exemplo, somar todas e digitá-las em corretagem, ou somar o ISS na corretagem, já que algumas corretoras repassam a cobrança do ISS ao cliente, etc - o importante é que o líquido da nota informado pela planilha bata com o informado na nota de corretagem.
10
PLANILHA ORDENS As planilhas “Diversos” e “Ordens” são o banco de dados da planilha de ações. Na planilha “Ordens”, o usuário pode digitar os dados diretamente nas colunas A:G (as colunas restantes são preenchidas automaticamente), ou indiretamente via planilha “Nota”. Neste último caso, as ordens são automaticamente inseridas em “Ordens” quando se pressiona o botão “Gravar Nota”. Quando deve-se digitar as ordens de compra e venda diretamente na planilha “Ordens”? Por exemplo, quando ainda não se dispõe da nota de corretagem. As ordens digitadas são registradas como “[SEM NOTA]”. Posteriormente, recomenda-se que o usuário - assim que receber a nota de corretagem - vá até a planilha “Nota”, carregue a nota e a compare com os valores informados na nota que recebeu de sua corretora. Se algo estiver diferente, deve-se efetuar as correções e clicar no botão “Gravar nota”. Isto é importante para se garantir que os valores registrados em sua planilha correspondam exatamente àqueles informados pelos documentos oficiais. A digitação direta pode ser feita nestas colunas, de acordo com os códigos especificados
Preenchimento automático
Planilha “Ordens”
X p bb f i N
X Ou
Valor TOTAL negociado
c v T
Custo TOTAL: Não daytrade + daytrade
Quantidade e custos da parcela daytrade
Formato do nome da nota na planilha:
dd/mm/aaaa,nota
Preço unitário da negociação
vc
vazio
; x = copia valor da célula de cima.
; d = data de hoje; x = copia valor da célula de cima.
vide Planilha “C”
11
COPIAR DADOS DE UM ARQUIVO PARA OUTRO exemplo: “Origem.XLS” (com dados)
“Destino.XLS” (vazio)
1) COPIAR PLANILHA: “ORDENS” arquivo “Origem.xls”
clicar arquivo “Destino.xls”
Selecionar esta célula; colar
2) COPIAR PLANILHA: “DIVERSOS” arquivo “Origem.xls”
clicar arquivo “Destino.xls”
(deixar clicado)
Selecionar esta célula, colar
12
DESDOBRAMENTO DE AÇÕES É possível fazê-lo de duas maneiras distintas (use uma ou outra): 1) PELA PLANILHA “NOTA”
A idéia aqui é criar uma “nota” na planilha capaz de representar o desdobramento. Por ex: se você tem 200 ações vale5 que se desdobrarão em 3 (suas 200 ações se desdobrarão em 600), faça o seguinte: como você já possui 200 ações em sua carteira, então crie uma nota de compra de 400 ações vale5 a preço unitário ZERO. Despesas, volumes negociados, Líquido da nota, deverão ficar ZERADOS. No identificador da nota, digite o que quiser – por ex, “desdobramento”. A data é a do dia em que se desdobram. Grave a nota. Ela será o evento que representa o desdobramento para a planilha. Para desfazer este desdobramento, basta apagar esta “nota”. 2) PELA PLANILHA “DIVERSOS”
Abra a planilha “Diversos”. Digite em desdobramentos o nome do ativo, a data do desdobramento e a quantidade “ X” a se desdobrar. Clique em “executar”. A planilha irá então alterar todas as negociações deste ativo ocorridas anteriores à data do desdobramento: irá multiplicar por ‘ X” as quantidades e dividir por “ X” os preços unitários. Se por qualquer motivo quiser desfazer este desdobramento, faça o seguinte: faça um novo desdobramento, na mesma data e para o mesmo ativo, mas ao invés de “X” digite “1/ X”. clique em executar. OBS: Observe que na primeira maneira não há qualquer modificação nos dados anteriores ao desdobramento. Para fins de cálculo do imposto a pagar e/ou apuração do lucro pela planilha, os resultados são os mesmos para qualquer uma das maneiras escolhidas.
13
CONSULTANDO A CARTEIRA DE AÇÕES PLANILHA CARTEIRA:
INFORMA A CARTEIRA NA DATA ESPECIFICADA. Digite uma data ou dê um duploclique (para data de hoje). Atenção: não confundir data da carteira com data das cotações.
Fuso horário de Nova Iorque
carteira
consulta
Quando digitados preços precedidos de “*” (ex: *10,00) aparecem duas linhas abaixo para cálculo de como ficaria o preço médio numa possível compra.
No [Lucro] e [%] já se encontram descontados os custos de venda (estimados por regra de três entre custo de compra, valor de compra e valor de venda). Quando digitados valores para [Lucro] e [%], a planilha calcula qual deve ser o preço de venda para se atingir a meta.
Atualiza Simples : mostra a CARTEIRA HOJE. A fonte dos dados é a planilha "Ordens". Equivale a dar um duploclique na data da barra azul Atualiza Detalhada : idem, porém listando todas as ordens de compra que contribuem para montar o preço médio de compra de cada ativo; Bens I.R. : Copia valores de compra dos ativos (incluindo custos) em formato texto para colar no programa de declaração do I.R. Maiores informações apertando o botão... %dma... : Extrapola ou interpola (dependendo do caso) o lucro obtido no período de dias para o lucro diário, mensal ou anual. Clicando com o botão da direita sobre as palavras “dia”,“mês” ou “ano” alterna-se entre estas três opções. Pode ser digitado manualmente. Os valores são calculados no instante em que se clica no botão, e não são atualizados com o passar dos dias. Para um novo cálculo, deve-se clicar novamente no botão. Nota : Vai para a planilha “Notas” (pg. 06). Ordens : Vai para a planilha “Ordens” (pg. 11). R1: Vista : Vai para a plan. “Vendas”, que gera o relatório R1 p/ merc. à vista (pg. 18). R2: Vista + Opções : Gera os relatórios R2, p/ merc. à vista e opções (pg. 20).
14
Nas linhas da carteira (branco e azul claro): Botão da direita para mostrar ou ocultar previsão do IR (15%)
Se aparecer uma data, dê um clique nela para visualizar a nota desta operação.
Duploclique para mostrar ou ocultar o detalhe das compras (lista todas as ordens de compra que contribuem para montar o preço médio de compra).
Fonte de cotações na internet
Precisam de software específico fornecido por Cedro Finances ou Agência Estado.
Para receber as cotações da internet, os ativos devem estar digitados por seu código de negociação. Ex: petr4, vale5, csna3, etc. Yahoo Finance e Infomoney: • •
não informam cotações de opções (pelo menos até julho/2010); cotações com delay de 15 minutos para os ativos, e sem delay para o índice bovespa.
Cedro Finances versão gratuíta • •
informa cotação de opções; cotações com delay de 15 minutos.
Região de consulta (amarela) Não faz parte da carteira, é utilizada para se fazer contas ou acompanhar preços de outros ativos. Toda linha digitada abaixo da região da carteira torna-se amarela. Para apagar uma linha da consulta, basta deletar o nome do ativo.
O código para o cálculo do custo da operação é digitado na célula a direita do nome do ativo. São os mesmos códigos aceitos na planilha Ordens: “X”,“BB”,“I”. (vide planilha “C” para detalhes sobre os custos de corretagem) Pode-se adicionar “DT”, sempre à direita do código, se a operação for day trade. Ex: “XDT”, “IDT”. Indiferente se maiúscula ou minúscula.
Duploclique - altera entre: - cotação automática pela internet (amarelo forte: 6,05 ) ; - cotação digitada pelo usuário (amarelo fraco: 5,00 ).
15
Informações sobre o desempenho do ativo no dia
OBS: Nem todas as informações acima são fornecidas por todos os servidores de cotação.
16
duploclique
Exemplo de gráficos (somente excel v.2003). 18.422,11 Ativo
Data
Qtde.
V.C.+ Custo Unit. [R$]
V.C.+ Custo Total [R$]
V.Mercado Unit. [R$] *
15.137,00
TOTAL
606,12
3,3
V. Mercado Total [R$]
Dias
Lucro [R$]
%
Mês
duploclique R$ 6.000
Valor de compra x lucro: 22 de outubro de 2009 bbdc4
R$ 5.000
vale5 goll4
R$ 4.000
itsa4
R$ 3.000
R$ 2.000
R$ 1.000
R$ 0
-R$ 1.000
Lucro na carteira: 22 de outubro de 2009
itsa4: $125 vale5: $89
bbdc4: -$120
Outros: $120
goll4: $381
17
APURANDO O LUCRO E IMPOSTO A PAGAR Existem hoje em minha planilha dois formatos de relatório para a apuração do lucro e imposto a pagar. O primeiro deles (R1) fiz para minha esposa entre 2007 e 2008 e funciona somente para o mercado à vista. Posteriormente, entre 2008 e 2009, criei um segundo formato (R2) para a empresa de meu sogro, que apura o lucro e imposto a pagar tanto para o mercado à vista quanto para o mercado de opções. Se você opera somente no mercado à vista, qual usar é uma questão de gosto, já que os resultados são os mesmos. Se opera no mercado de opções, então deve usar o segundo formato (página 20 deste manual).
RELATÓRIO R1 – SOMENTE MERCADO À VISTA (Planilha “Vendas”) O relatório R1 apura o lucro e o imposto a pagar somente no mercado à vista de ações. Ele é gerado na planilha “Vendas”. Para gerá-lo, dê um duplo clique na data final da barra azul. Para outras datas, basta digitá-las na própria barra azul.
Duploclique: volta para planilha carteira. Extrapola ou interpola (dependendo do caso) o lucro obtido no período de dias para o lucro diário, mensal ou anual. Digitar manualmente no título da coluna: “dia”, “mês” ou “ano”. mostra as vendas detalhadamente, listando todas as ordens de compra que contribuem para montar o preço médio de compra do ativo; mostra as vendas feitas no período especificado na barra azul;
18
Botão da direita: para agrupar ou desagrupar por ATIVO ou DATA a lista de vendas
Se for uma data, dê um clique nela para visualizar a nota desta operação.
Duploclique para mostrar ou ocultar os detalhes da venda (como foi montado o preço de compra dos ativos vendidos).
IRRF, DARF E PREJUÍZOS DE ANOS ANTERIORES
19
RELATÓRIOS R2 - MERCADO À VISTA E OPÇÕES Desenvolvi os relatórios R2 entre 2008 e 2009. Os resultados são idênticos àqueles apresentados R1. As únicas duas diferenças são: o formato de apresentação dos resultados e a inclusão do mercado de opções no processamento das vendas realizadas.
COMO GERAR OS RELATÓRIOS R2: Para gerar os relatórios de apuração do lucro e imposto a pagar neste segundo formato, faça o seguinte: - clique no botão R2: Vista + Opções ; - digite o ano ou período e clique “OK.” Dependendo do mercado e do tipo de operação, poderão ser criadas até quatro planilhas específicas e uma planilha Geral (o processo é todo automático): Planilha rPOS-V..: Relatório com as operações finais (não day trade) no mercado à vista; Planilha rDT-V....: R elatório com as operações day trade no mercado à vista; Planilha rPOS-O.: Relatório com as operações finais (não day trade) no mercado de opções; Planilha rDT-O...: R elatório com as operações day trade no mercado de opções; Planilha rGER ....: Relatório Geral, consolidando todas as informações e imposto a pagar.
* para que a planilha possa identificar um ativo como uma opção, seu nome deve ter sido digitado pelo código de negociação bovespa – exemplo: VALEH30. ** para ver a posição em carteira, clique em Rel.Posição em rPOS-V ou rPOS-O.
20
VALORES E FÓRMULAS Todos os valores e fórmulas são inseridos automaticamente quando se gera o relatório. Os valores podem ser alterados a posteriori. As fórmulas não. Seguem algumas explicações. DATA
NOTA
NEGOCIAÇÃO
OP
QTDE
CUSTO
VALOR
REGIÃO DOS VALORES . Podem ser: - Digitados pelo usuário ou - Inseridos automaticamente.
LUCRO / (PREJU ZO)
SALDOS QTDE
VALOR
P.U.
REGIÃO DAS FÓRMULAS . São sempre inseridas automaticamente e mudam de acordo com OP (CP,T,VD,SD,VC) e sinais de algumas células.
COMO FUNCIONAM AS FÓRMULAS AUTOMÁTICAS: Quando analisei o sinal (positivo ou negativo) da quantidade de negócios (QTD NEG) e quantidade de saldo (QTD SD), a cada duas linhas (i) e (i-1) notei que: HÁ RESULTADO SEMPRE QUE:
QTDNEG(i) × QTDSD(i-1) < 0
QTDSALDO(i) INVERTE SINAL SEMPRE QUE:
QTDSD(i) × QTDSD(i-1) < 0
(essa é obvia!)
A escolha da fórmula apropriada a se usar para cada caso foi automatizada em cima destas duas relações. Por exemplo, são três as fórmulas para cálculo do Valor do Saldo [V SD]. Eu automatizei a escolha da seguinte maneira:
FÓRMULA DO VALOR DO SALDO fórmula 1:
≤0
VSD(i)= QTDSD(i) × VNEG(i) ÷ QTDSD(i)
SE QTDSD(i) × QTDSD(i-1)
fórmula 2:
≤0 >0
SE QTDNEG(i) × QTDSD(i-1)
VSD(i)= VSD(i-1) - CUSTO(i) fórmula 3:
>0
VSD(i)= VSD(i-1) + VNEG(i)
No Excel ficou assim:
fórmula 1:
fórmula 2:
fórmula 3:
Célula I40 = SE ( H40*H39 <=0; H40*E40/D40; SE( D40*H39 <=0; I39-F40; I39+E40 ) )
21
fórmula 1:
FÓRMULA DO CUSTO
≤0
<0
CUSTO(i)= VSD(i-1)
SE QTDSD(i) × QTDSD(i-1) fórmula 2:
SE QTDNEG(i) × QTDSD(i-1)
>0
CUSTO(i)= -VSD(i-1) × QTDNEG(i) ÷ QTDSD(i-1)
fórmula 3:
≥0
CUSTO(i)= “” (sem resultado, não há custo)
No Excel ficou assim:
fórmula 2:
fórmula 1:
Célula F40 = SE ( D40*H39<0 ; SE ( H40*H39<=0;
I39
; -I39/H39*D40 ) ;
fórmula 3:
""
)
FÓRMULA DO LUCRO / (PREJUÍZO) fórmula 1:
≤0
<0
LP(i)= QTDSD(i-1) × VNEG(i) ÷ QTDNEG(i-1) -CUSTO(i)
SE QTDSD(i) × QTDSD(i-1) fórmula 2:
SE QTDNEG(i) × QTDSD(i-1)
>0
LP(i)= -CUSTO(i) - VNEG(i)
fórmula 3:
≥0
No Excel ficou assim:
LP(i)= “” (sem resultado, não há custo)
fórmula 1:
fórmula 2:
Célula G40 = SE ( D40*H39<0 ; SE ( H40*H39<=0; H39*E40/D40-F40 ; -F40-E40
fórmula 3:
) ; ""
)
22
DADOS QUE PODEM SER ALTERADOS/DIGITADOS: DATA
Data e No. da nota de corretagem
NOTA
OP
NEGOCIAÇÃO QTDE
Operação: “CP”: compra “VD”: venda “VC”: vencimento “SD”: saldo
VALOR
CUSTO
LUCRO / (PREJU ZO)
SALDOS QTDE
VALOR
P.U.
Quantidade e valor total com custos despesas inclusas
INSERIR / EXCLUIR LINHA E/OU COLUNA LINHAS: A inserção/exclusão é permitida SOMENTE ABAIXO DA LINHA 32:
Quando inserida ou excluída uma linha, as fórmulas das linhas superior e inferior são automaticamente refeitas para se referirem a nova linha inserida/excluída. Por exemplo, quando se insere a linha 55, os processos automáticos da planilha refazem as fórmulas das linhas 54 e 56, que passam a se referir a nova linha inserida:
COLUNAS: A inserção NÃO É PERMITIDA, porque interfere na automatização.
23
RESUMO FINANCEIRO RESUMO FINANCEIRO SALDO INICIAL COMPRAS VENDAS LUCRO (PREJUIZO) SALDO FINAL VERIFICAÇÃO
4.212,35 (5.341,21) (2.532,98) 1.404,12
Não pode ser alterado manualmente, e é sempre recalculado quando existir qualquer alteração nos dados abaixo da linha 32:
OK
DATAS DAS OPERAÇÕES Nunca deixar sem data uma linha que contenha uma operação, porque os resumos (mensal e financeiro) dependem da presença de datas nas operações. Uma linha com operação e sem data compromete os valores calculados nos resumos. RESUMO MENSAL O resumo mensal é sempre recalculado quando existir qualquer alteração nos dados a partir da linha 32:
PREJUÍZOS DE ANO ANTERIORES Podem ser digitados nos relatórios “rPOS”, “rDT” ou “rGER” (conforme ilustrações abaixo). Ao clicar no botão R2: Vista + Opções , os prejuízos digitados são automaticamente armazenados (na planilha “Diversos – item DARFs/PREJUÍZOS) e transferidos aos novos relatórios gerados. Porém, os prejuízos digitados não serão armazenados se os relatórios forem excluídos pelo botão Excluir Relatórios .
24
PLANILHA rGER - TABELA DO IRRF e DARF O trecho (em vermelho) indicado permite digitação de fórmulas. Uma vez digitadas, elas serão mantidas nos futuros relatórios gerados.
RENOMEAR OU EXCLUIR RELATÓRIOS Somente podem ser renomeadas as planilhas criadas pelo botão R2: Vista + Opções . Exemplo: rPOS-V, rPOS-O, rDT-V, rDT-O, rGER, rCRT, etc . As demais não podem ser renomeadas. Para que renomear? Por exemplo, para se preservar os dados do relatório. Um relatório renomeado nunca mais é apagado, reescrito ou atualizado pelos processos automáticos da planilha.
PODEM SER RENOMEADAS
NÃO PODEM SER RENOMEADAS
BOTÃO Excluir Relatórios Este botão exclui todos os relatórios sem armazenar qualquer valor que eventualmente tenha sido digitado em seus cabeçalhos (prejuízos de anos anteriores e valores de lucro/prejuízo das tabelas Termo e BMF). Para preservar valores digitados nos cabeçalhos, sempre gere novos relatórios pelo botão R2: Vista + Opções .
25
GRAVAR DADOS
Deixar “ticado” para que qualquer alteração nos dados - abaixo da linha 32 do relatório, seja automaticamente marcada para gravação (pintada de laranja).
Duploclique nesta região marca /desmarca a nota especificada nesta linha para gravação. Necessário data e nome ou número da nota já digitados..
O duploclique da linha acima irá marcar a “nota123” de “30/06/09” para gravação. Se existirem mais linhas (em qualquer um dos relatórios) com “nota123” e data “30/06/09”, elas serão encontradas e automaticamente marcadas pelos processos automáticos da planilha. Para se gravar os dados marcados (em laranja), deve-se clicar no botão Gravar. Por exemplo, linhas de vencimento de opções aparecem pintadas de laranja quando há indício de vencimento naquela data. Para que o “indício” se torne um “fato”, deve ser confirmado pelo usuário, clicando-se no botão laranja Gravar . Feito isto, no próximo relatório os vencimentos gravados não mais aparecerão em laranja, pois já foram incorporados aos dados da planilha.
TABELA DE VENCIMENTO DE OPÇÕES A tabela com as datas de vencimento de opções é calculada automaticamente para cada ano. Se o usuário não concordar com uma ou mais datas de vencimento, ele pode alterá-las na planilha “v” (de vencimento):
26
INICIAR NOVO ANO O botão Inicia novo ano move da planilha “Ordens” todas as notas com data igual ou anterior a 31/12/ano (ano do relatório em “rGER”) para uma nova planilha. Na planilha “Ordens” ficam somente notas lançadas após essa data e a posição acionária, que é automaticamente inserida via nota fictícia, denominada “SALDO”. Quando se deve usar este recurso? Por exemplo, quando a planilha “Ordens” estiver tão grande que torne lento o processo de gerar relatórios, quando planilha “Ordens” estiver perto de atingir o seu limite de linhas (65536), etc.
Exemplo: Inicia novo ano move as notas anteriores à 31/12/ano para uma planilha nomeada com a data do dia em que foi criada. Esta planilha fica inativa: não participa mais da geração dos relatórios ou da consulta de notas e seus dados não podem ser alterados pelo botão Gravar Nota . Ela pode ser reativada com um duploclique onde está escrito “duploclique para ATIVAR esta planilha”:
A planilha ativa - onde é possível gravar/excluir/alterar notas, sempre será a denominada “Ordens”. Após clicar em Inicia novo ano , na planilha “Ordens” ficam somente a posição acionária e eventuais notas lançadas após 31/12 do ano do relatório:
Nota fictícia com posição acionária em 31/12/ano
27
PLANILHAS “r”, “rr”, “rp” Estas planilhas são utilizadas como referência para a criação dos relatórios. Algumas poucas alterações podem ser feitas, e serão mantidas nos relatórios gerados. O que pode ser alterado: os textos (títulos de colunas, de tabelas, nome do fulano, etc); as cores de fundo das células. a escolha de quais mercados devem ser exibidos ou ocultos. • • •
O que NÃO PODE alterado nestas planilhas: Inserir/Excluir linhas e/ou colunas. •
RELATÓRIO DAY TRADE RESULTADO MENSAL clique no botão Relatórios com o botão da direita e depois selecione:
São mostrados os resultados diários e o total no mês das operações day trade: DAYTRADE MAIO / 2009 DATA
NOTA
06/05/09
123 456 355 350 356 357 TOTAL
07/05/09 14/05/09 15/05/09 18/05/09 19/05/09
RESULTADO
IRRF
(1.000,00) 3.500,00 3.000,00 (1.000,00) 3.000,00 (1.000,00) 6.500,00
50,00 30,00 21,30 101,30
JUNHO / 2009 DATA
NOTA
15/06/09
877 878 351 TOTAL
27/06/09 28/05/09
RESULTADO
1.000,00 3.500,00 (1.000,00) 3.500,00
IRRF
50,00 50,00
28
RELATÓRIO DAY TRADE E NÃO DAY TRADE – RESULTADO DIÁRIO clique no botão Relatórios com o botão da direita e depois selecione:
TABELAS BMF e TERMO em “rGER” São tabelas vazias, para simples para digitação de valores. Eu as fiz na época em que ainda permitia o uso de meu trabalho em empresas de contabilidade, para apresentação padronizada dos resultados para clientes que operassem nos 4 mercados. Sua única função era essa: digitação manual de resultados calculados fora de minha planilha.
29
Perguntas e Respostas (retiradas em sua maioria do livro de visitas do site de minha planilha)
Esta planilha está automatizada para o caso de aluguel de ações ? Não, eu não programei esta planilha para este caso. Como descubro a rentabilidade mensal ? (Marcello – Fortaleza/CE) Clique no botão "% dma..." (tanto na planilha “carteira” quanto em “vendas”). Ao clicar nele, aparecerá uma coluna extra. No título desta coluna, você pode digitar " dia", "mês" ou "ano". A planilha então projeta (extrapolando ou interpolando, dependendo do caso) como seria a rentabilidade obtida para um destes períodos.
Onde encontro a planilha de opções e termo? (Fernando - RS) Opções: Primeiro, lance na planilha suas operações (pg. 6 do manual). Depois, clique no botão RELATÓRIOS (pg. 20 do manual). O resto sai automaticamente: o resultado das operações, o DARF, etc. Termo: Não existe uma planilha automatizada para termos. Porém, dependendo se o termo foi ou não liquidado, é possível apurar o lucro destas operações no mercado à vista. Lance as operações termo (na planilha Nota) com o código "T" (na coluna "C/V"). Depois, quando gerar o relatório (pg. 20 do manual), no mercado à vista estas operações aparecerão automaticamente discriminadas a parte: por exemplo, para "Petr4" aparecerá um "Petr4 (T)". Então, dependendo se o termo foi ou não liquidado, dá-se um duplo-clique na linha da operação termo e ela sobe para o papel a vista... Fiz este recurso automatizado a pedido de meu sogro, que na época fazia manualmente este processo.
Como apontar as opções que viram pó (vencidas)? (Davison) Antes de tudo, as opções devem ter sido lançadas com seu nome de negociação bovespa (ex: valek48), para que a automatização da planilha possa entender que este ativo é uma opção. Outra coisa: para apurar lucro com opções, deve-se utilizar o formato 2 de relatórios, pg. 20 do manual. 1º) clique no botão relatórios... (pg. 20 do manual !) 2º) Após gerado o relatório, irá notar que, se a opção estiver vencida, a planilha sugerirá para você em uma linha laranja o vencimento. Se concordar, clique no botão laranja "gravar", para gravar este vencimento como definitivo no banco de dados da planilha (pg. 26 do manual). Esta é a maneira automática. Há outras maneiras, porém manuais: por exemplo, crie uma nota na planilha "notas" com valor de venda zero e custos zero. Ou então, no próprio relatório “ rPOS-O”, adicione uma linha e nela digite "VC" na coluna "OP". digite uma data e um nome para a nota. A linha será pintada automaticamente de laranja. Clique no botão laranja "gravar" do relatírio de opções... enfim, estas são algumas das maneiras possíveis de se registrar uma opção que virou pó. Tabela BMF em “rGER” fica vazia quando gero relatório e onde digitei os valores na planilha “rr” os mesmos desparecem. (Rafael – S. Miguel Oeste – SC) Você deve digitar os valores da tabela BMF diretamente na planilha "rGER" (e não na "rr", como está fazendo). Verá que os valores de "lucro/(prejuízo)" digitados assim serão transferidos aos novos relatórios após clicar no botão R2: Vista + Opções . Nunca exclua os relatórios pelo botão Excluir Relatórios se quiser preservar os valores que digitou.
30
