Planilha Excel com atualização automática de cotação

terça-feira, 5 de novembro de 2013

Atualização 22/06/2015: A planilha foi atualizada para versão 1.2, para corrigir erros após mudanças no Yahoo. Mais detalhes aqui

Em um dos meus posts eu citei que uso uma planilha Excel que faz a atualização automática das cotações pela Internet. Após vários pedidos para disponibilizá-la, resolvi compartilhar com os amigos. Eu já uso a planilha há mais de 1 ano.


(ATENÇÃO) O uso da planilha é por sua própria conta e risco


Instruções para baixar e iniciar uso: 

0) Faça o download da última versão da planilha clicando AQUI

1) Para receber os dados da Internet é necessário estar conectado e a planilha faz uso de uma macro, que na verdade é um programinha em linguagem VBA, nativa do Excel. A macro é acionada manualmente pelo usuário através de um botão. (Os dados vem do site Yahoo com delay de 15 minutos)

2) Para rodar a macro, é necessário que seja habilitada pelo usuário a função de permissão para a execução de macros. Provavelmente a seguinte mensagem deverá aparecer (no idioma do seu MS Office)

clique para ampliar

Clique em "Enable Content" para permitir e execução de Macros.

3) Antes de compartilhar o arquivo, para garantir a privacidade, eu rodei uma rotina para eliminar informações pessoais como autor, quem salvou, caminhos, etc. Esta é uma funcionalidade do MS Office. Provavelmente, enquanto esta funcionalidade estiver habilitada, uma mensagem de erro aparecerá ao salvar ou executar a macro. Minha recomendação é seguir os passos abaixo para evitar esta mensagem, principalmente se a macro não estiver funcionando. (File -> Info -> Allow this information to be saved in your file). Não compartilhe o arquivo com ninguém sem antes habilitar novamente a funcionalidade de apagar dados pessoais.

clique para ampliar


Como utilizar:

O uso da planilha é bastante simples. Ela tem 2 abas: Cotações e Controle Carteira Teste

Após seguir os passos acima para habilitar a macro. Vá até a aba Cotações e clique no botão "Atualizar" (1), conforme imagem abaixo:


clique para ampliar

Isso deverá ser o suficiente para que a planilha atualize a tabela de cotações imediatamente abaixo.

Outras informações sobre a forma de usar:

Como não sou nenhum especialista em design de planilhas Excel, algumas coisas em termos de formatação e posicionamento devem permanecer como estão, senão a macro não irá funcionar

A) A Coluna A deve conter o symbol no formado do Yahoo e por isso precisa ter um ".SA" na frente. Por exemplo a VALE5 deve estar representada por VALE5.SA

B) A coluna B contém a simbolo no padrão Bovespa (Ex. VALE5)

C) A célula B3 contém um código "snd1l1p2c1jk". Este código deve permanecer como está e cada letra representa uma informação na tabela entre as colunas D e K. Ex. "s" é Symbol, "n" é o nome, e assim por diante. Veja a lista completa à partir da linha 82.

D) A lista de ações/symbols deve estar sempre na linha 6. A linha 5 e a última linha (neste caso a 80) devem permanecer em branco.

E) O resultado sempre aparecerá na tabela entre as colunas D e K automaticamente. Não altere a menos que saiba o que está fazendo, pois a planilha pode não funcionar (pode haver necessidade de alterar a Macro)

F) Para incluir a cotação de uma ação que não está na lista você tem 2 opções:
F1) Altere a coluna A e B de uma ação que está lá e você não deseja mais atualizar
F2) Insira uma linha em qualquer local, copie e cole o conteúdo de outra linha válida e altere as colunas A e B (o restante a macro se incumbe de atualizar após clicar no botão atualizar)

G) As células E1 e E3 serão preenchidas automaticamente (E1 com a data/hora da última atualização da macro) e E3 com a referência da URL que foi executada (esta informação não serve para nada mas vai ser preenchida automaticamente nesta célula)

H) A aba "Controle Carteira Teste" pega os dados a atualiza baseado em uma carteira que calcula automaticamente rentabilidade, preço médio, etc. Esta aba foi totalmente criada por mim e eu admito que é complicada de usar, portanto não tenho nenhuma pretensão que alguém utilize. Deixei apenas para que vocês consigam ver como faço para pegar o dado automaticamente de uma aba e levar para outra.
Possivelmente, se alguém estiver interessado eu posso explicar, algum dia, como esta aba funciona através de um post específico.

I) Não está aí, mas a rentabilidade geral da carteira eu contabilizo através do sistema de cotas do Blog Além da Poupança (AdP), para utilizá-la, peço que façam o download direto do Blog do AdP AQUI.


O conteúdo original da macro foi baixado do site http://www.gummy-stuff.org/Yahoo-data.htm. A macro foi adaptada por mim para evitar alguns erros e funcionar melhor para a minha necessidade. Caso queiram compartilhar com outras pessoas fiquem à vontade, peço apenas que mencionem a referência.


Caso alguém faça uso desta planilha, peço que deixe um comentário com suas impressões, bem como em caso de dúvidas.

Abraços.

37 comentários

  1. Eu uso o openoffice no linux e usava uma imprtacao de dados semelhante. Agora uso um banco de dados openoffice que recolhe e atualiza os dados mediante um script shell :-)

    abs victor

    ResponderExcluir
    Respostas
    1. Beleza Victor, realmente em OpenOffice.Org ela não deve funcionar. Mas certamente existem mecanismos semelhantes.
      Abraços e obrigado pela visita!

      Excluir
    2. Victor,

      Voce poderia compartilhar a maneira que você utiliza para fazer o update do banco de dados? Tenho interesse em desenvolver um aplicativo para cálculo de correção e gostaria de uma forma de obter os indicadores.

      Grato,
      Cesar

      Excluir
  2. O rapa, vc fez o post mais útil do ano, valeu!

    ResponderExcluir
  3. Boa postagem.. eu particularmente acompanho o mercado através do app do android "Bolsa de Valores App" bem legal!

    Abraço,
    IM.

    ResponderExcluir
    Respostas
    1. eu tb acompanho pelas apps, mas o bom da planilha automática é que já acompanho diversos indicadores da carteira como rentabilidade, peso, etc em quase tempo real sem precisar ficar digitando na mão.
      Valeu, abraços!

      Excluir
  4. :(

    eu não sei o que ocorre com o meu office, pois nenhuma planilha do exel com atualização de cotação automática funciona nele.

    ResponderExcluir
    Respostas
    1. Vinicius, não sei como posso te ajudar, mas se puder me informe qual a versão do seu Excel. Além disso, se quiser me enviar por e-mail informações sobre os erros (print screen da tela, etc) posso tentar ver o que está acontecendo.
      mande para economicamenteincorreto@gmail.com
      Abraços

      Excluir
  5. Este comentário foi removido pelo autor.

    ResponderExcluir
  6. Bom dia,
    Cliquei no link para baixar o arquivo porém é informado que o link está desabilitado. Não está mais disponível?
    Abraço.

    ResponderExcluir
    Respostas
    1. Olá Mauro, obrigado pelo aviso.

      O erro foi corrigido e o link deve estar correto agora.

      Abraços

      Excluir
  7. Olá Mauro, primeiramente parabéns pelo post. Tentei usar a planilha mas deu erro no Excel 2013 na tentativa de abrir. Da o seguinte erro "Microsoft Excel não pode abrir nem salvar documentos porque não há memória ou espaço em disco suficiente disponível". Como os recursos estão normais na minha máquina acredito que possa ser alguma incompatibilidade, já usou esta planilha ou tem alguma outra funcional para Excel 2013 ? Antecipadamente obrigado pela atenção.

    ResponderExcluir
    Respostas
    1. Marcelo,
      Eu não tinha visto sua mensagem antes. Me desculpe, pois a notificação deve ter passado desapercebido no e-mail.
      Nunca tentei usar no Excel 2013, mas acredito que não haja restrição ou incompatibilidade, pois é uma macro relativamente simples que faz a operação e normalmente a Microsoft mantém compatibilidade total.
      Espero que tenha conseguido resolver.
      Agradeço se puder atualizar aqui sobre a situação.
      Abraços

      Excluir
  8. Exatamente o q eu procurava , porem a mesagem mesmo apos permitir clicando no botao e habilitar conteudo é : nao è possivel habilitar a macro , talvez ela nao esteja disponivel nesta pasta de trabalha ou todas as macros estejam desabilitadas , pode me ajudar a resolver isso ?

    obrigado , Marco

    ResponderExcluir
    Respostas
    1. Marco,

      Verifique se o Excel tem habilitada alguma configuração de segurança que o impeça de usar Macros. Não sei muito bem o que pode estar acontecendo, mas eu continuo usando este arquivo e muitas outras já fizeram download e não tiveram problemas.

      Me foi relatado recentemente que aparece um erro de "tipo invalido" ou "Type mismatch" ao usar a planilha original do download. Foi resolvido limpando da lista de ativos alguns que não são mais comercializados porque a empresa mudou o ticker ou fez OPA e não tem mais ações.

      Se puder dar um feedback como resolveu eu fico agradecido, pois pode ajudar outros usuários.

      Abraços

      Excluir
    2. E.I.,
      Habilitei as macros , em configuração de macros, habilitei todas conexões de dados e atualizações automaticas de links na pasta de trabalho , agora o erro que surge é 13: tipos incompativeis .
      Tentei em outa planilha que encontrei na internet , depois de permitir as macros ela funcionou , porem ela nao traz a variação da porcentagem por ex, por esse motivo ainda prefiro a sua e to tentando faze-la funcionar. Pesquisei o erro 13 ao que parece esta relacionado a macro, tem alguma ideia sobre o pode estar acontecendo ?

      Abracos , Marco

      Excluir
    3. Marco, este problema apareceu estes dias para algumas pessoas que baixaram.

      Acabei de corrigir e o arquivo que está disponível para download já não deve ter este problema. Este não é um problema com a Macro em si, mas com os dados, pois tem códigos de papeis que não existem mais. Se não quiser baixar novamente a planilha, apenas apague a linha da antiga ação da Ambev (AMBV4.SA) ou altere para ABEV3.SA que vai funcionar.

      Qualquer outro problema ou dúvida me avise.

      Abraços

      Excluir
  9. Otimo I.E. funcionou , muito bom e obrigado !!
    Uma sugestão ou ideia , tem como os índices positivos ficarem em azul e negativos em vermelhos ?

    Marco

    ResponderExcluir
    Respostas
    1. Oi Marco, que bom que funcionou.

      Sobre o azul ou vermelho, isso já deveria acontecer nas colunas H e I, está acontecendo para você?

      Estou entendendo que você quer ver a cor na coluna G, é isso? Vou verificar se consigo mudar isso. Teria que alterar o "conditional formating" destas células.

      Abraços

      Excluir
  10. E.I.

    pensei na coluna G ,H ,e I , assim destaca as variacoes positivas ou negativas do momento.

    Voce usa graficos ? Seria possivel alimentar graficos com esses dados ?

    Abracos , Marco

    ResponderExcluir
    Respostas
    1. Marco,

      As colunas H e I já estão, mas para fazer na G me parece mais complicado. Vou tentar mas não garanto.

      Sobre os gráficos, sim, é possível usar os dados para isso, mas você teria que criar os gráficos de acordo com seu objetivo.

      O blog Alem da Poupança tem uma planilha bem completa de controle de investimentos e patrimônio com gráficos, etc. Você pode usar a planilha do ADP e usar essa minha planilha para alimentar/atualizar as cotações.

      Abraços

      Excluir
    2. E.I. ,

      aqui as colunas nao mudam de cor , estando negativa ou positiva a variação dos ativos .

      Marco

      Excluir
    3. Que estranho, elas deveriam mudar. Veja o exemplo na imagem da planilha no post.
      Pra mim aparece assim. Não sei se outras pessoas baixaram e tem o mesmo problema que você. Vou tentar investigar, mas não sei como ajudar.
      Esta mudança de cor é um Conditional Format (Formatação Condicional). Lá você tem que especificar que de a célula contem "+" fica azul (ou verde) e se contém "-" fica vermelho.
      Isso já está configurado na planilha e já fiz o download em outros computadores onde esta condição também funcionou. Veja se tem mais alguma configuração (assim com nas macros) onde você pode ou não habilitar "formatação condicional"

      Excluir
    4. Marco, consegui adaptar a formatação inclusive para aparecer verde e vermelho na coluna G. Faça o download novamente da planilha e veja se está funcionando.
      Abraços

      Excluir
  11. Amigos, a versão anterior da planilha está apresentando um erro devido a uma modificação no Yahoo. Uma nova versão da planilha foi disponibilizada neste post (link foi atualizado).

    Mais detalhes em
    http://economicamenteincorreto.blogspot.com.br/2015/06/planilha-de-atualizacao-de-cotacoes-on.html

    Agradeço se puderem avisar se funcionou.

    Abraços

    ResponderExcluir
  12. Top cara!

    Só queria saber como faço pra inserir novos códigos, por exemplo BRAP3.

    Abraço.

    ResponderExcluir
    Respostas
    1. Vira lata, você pode inserir linhas (sugiro fazer um copiar e inserir linha copiadas de uma linha qualquer) ou substituir os valores da coluna A e B por

      A=BRAP3.SA
      B=BRAP3

      Caso nao funcione me avise aqui ou me mande um email.

      Abracos

      Excluir
  13. Uso uma planilha muito boa que atualiza automaticamente a cotação dos ativos em carteira e já mostra todas as taxas e IR conforme escolha da corretora.... o site é www.promova-br.com.br

    ResponderExcluir
  14. Ola, como configuro na planilha(yahoo!finanças) para ter o preço médio ?

    Obrigado !!!

    ResponderExcluir
  15. Baixei a planilha, obrigado por compartilhar, abraços!

    ResponderExcluir
  16. uma dúvida, você atualiza com as empresas fazem IPO ou OPA?

    ResponderExcluir
  17. Muito Boa a planilha, mas não tem como fazer um tutorial de como usar a aba 'controle da carteira tese', obrigado pelas ótimas informações desde já!

    ResponderExcluir
  18. Bom dia! utilizo esta planilha a 2 anos sem problemas. Esta semana ao tentar atualizar surgiu um erro. Para tentar entender o erro coloquei a linha de comando da celula E3 direto no navegador e apareceu a seguinte mensagem:
    It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com

    Não sei se vc pode me ajudar, será que o serviço deixou de funcionar?
    Desde já agradeço a ajuda.

    ResponderExcluir

 

Posts Populares

Blogs Favoritos

Estatísticas