Excel: Pesquisar cotações online? Sem Macros?

Um cenário muito comum nas empresas e até mesmo em nossas vidas é o acompanhamento de cotações de ações. Investimentos em ações estão mais populares a cada dia e o acompanhamento da evolução do patrimônio é, quase sempre, em Excel.

Rotineiramente pesquisamos ao longo do dia o valor dos papeis, alimentamos nossas planilhas com essas informações.

Quem não gostaria de poder alimentar as suas planilhas “automaticamente”? Isso mesmo, sem ter que ficar abrindo o site da Bovespa, pesquisando pelos códigos de suas ações (podem ser muitos) e redigitando na planilha?

Uma pergunta que ouço com muita frequência é: “- É possível buscar as cotações utilizando apenas fórmulas?” Sim! É possível ! Com fórmulas e sem macros! Vejamos como......

Mesmo sendo algo relativamente simples de se fazer, não podemos deixar de citar alguns conceitos fundamentais para melhor entendimento do exercício. O que são WebServices? E o que São arquivos XML?  Recomendamos fortemente a leitura do artigo O QUE SÃO OS WEBSERVICES.

Entendendo nosso ambiente

Nosso provedor de informações será a BOVESPA. Este fornecimento será através de um WEBSERVICE que retornará um arquivo XML com as informações desejadas. Entendamos cada um:

WebService da Bovespa

A BOVESPA disponibiliza diversos serviços de consulta a cotações sendo o mais popular, seu próprio site. Disponibiliza também um WEBSERVICE para esse serviço.

Exitem inúmeras maneiras de se utilizar esse serviço. Utilizaremos uma abordagem bem simples e direta de como utilizar esse WEBSERVICE no Excel, sem a necessidade de criação de macros.

Esse WEBSERVICE tem um atraso de cerca de 15 minutos, assim como ocorre com as consultas realizadas no diretamente no site.

Seu endereço e estrutura:  

http://www.bmfbovespa.com.br/Pregao-Online/ExecutaAcaoAjax.asp?CodigoPapel=VALE3|PETR4

Se vocês redigitarem este endereço no Browser, conseguirão efetuar a pesquisa e o retorno será semelhante ao exibido acima.

Notem que o endereço digitado assim é formado por duas partes distintas:

Endereço base: 

http://www.bmfbovespa.com.br/Pregao-Online/ExecutaAcaoAjax.asp?CodigoPapel=

Valor do argumento: 

VALE3|PETR4 - Podemos colocar inúmeros códigos de ações, separando-os por "|" pipe.

XML da Bovespa

Notem que no exemplo utilizado, foram passados dois códigos: VALE3 e PETR4. Observem abaixo que o XML devolvido pela BOVESPA possui dois nodes "PAPEL", uma para cada código. Se fossem 5 códigos pesquisados, seriam 5 nodes e assim sucessivamente, cada node com seus atributos. 

No nosso exemplo, a BOVESPA devolve os seguintes atributos:

• Codigo
• Nome
• Ibovespa
• Data
• Abertura
• Minimo
• Maximo
• Medio
• Ultimo
• Oscilacao

Identifiquem no XML acima cada um dos atributos citados, compreendam a sua arquitetura.

<ComportamentoPapeis>

<Papel Codigo="VALE3" Nome="VALE ON N1" Ibovespa="#" Data="29/07/2014 14:25:38" Abertura="33,00"Minimo="32,82" Maximo="33,25" Medio="33,02" Ultimo="33,05" Oscilacao="0,70"/>

<Papel Codigo="PETR4" Nome="PETROBRAS PN" Ibovespa="#" Data="29/07/2014 14:25:38" Abertura="20,10"Minimo="19,57" Maximo="20,12" Medio="19,80" Ultimo="19,80" Oscilacao="-1,74"/>

</ComportamentoPapeis>

Funções Excel 

Para efetuarmos este exercício precisaremos conhecer duas funções, duas das Novidades do Excel 2013.

• SERVICOWEB
• Esta é a função que efetua a chamada ao WebService. Seu únito argumento é o endereço WEB do WebService. Para conhecer melhor a função  clique aqui.

• FILTROXML
• Esta é a função utilizada para extrair partes (campos) do XML devolvido pela função SERVICOWEB. Para conhecer melhor a função  clique aqui.

Desenvolvendo o Exercício.....

Para este exercício criaremos um cenário simples. Conforme abaixo, temos uma célula, no caso D8, que receberá o CÓDIGO ou CÓDIGOS das ações. Se mais de um, separá-los com "|" (pipe).

Primeiro passo: Crie a planilha básica conforme abaixo:

Segundo passo: Insira na célula que receberá o XML, no nosso caso D10, a seguinte fórmula:
=SERVIÇOWEB("http://www.bmfbovespa.com.br/Pregao-Online/ExecutaAcaoAjax.asp?CodigoPapel=" & D8)   

Notem, na tela abaixo, que a célula recebeu o XML de resposta da BOVESPA, conforme discutido anteriormente neste artigo.

Terceiro Passo: Extrair os valores desejados do XML

Lembrando que temos dois códigos de ação neste exercício, teremos então dois nodes (um para cada) em nosso XML. Relembrando o XML:

<?xml version=""1.0""?>
<ComportamentoPapeis>
<Papel Codigo=""VALE5"" Nome=""VALE PNA     N1"" Ibovespa=""#"" Data=""29/07/2014 15:28:44"" Abertura=""29,48"" Minimo=""29,33"" Maximo=""29,66"" Medio=""29,48"" Ultimo=""29,43"" Oscilacao=""0,24""/>
<Papel Codigo=""PETR3"" Nome=""PETROBRAS ON"" Ibovespa=""#"" Data=""29/07/2014 15:28:49"" Abertura=""18,95"" Minimo=""18,39"" Maximo=""18,95"" Medio=""18,60"" Ultimo=""18,54"" Oscilacao=""-2,22""/>
</ComportamentoPapeis>

Para extrairmos o NOME DA AÇÃO referente ao primeiro código, digitamos a fórmula abaixo na célula E14:
       =FILTROXML($D$10;"//Papel[1]/@Nome")

          onde....

• D10 é a célula que contém o XML da BOVESPA
• "//Papel[1]/@Nome"     --> Indica que é a linha 1, do node PAPEL, campo NOME

Obs.: Se estivermos pesquisando apenas 1 código, o número da linha "[1]" pode ser omitido.

Em seguida, repetimos a fórmula para a célula E15, alterando apenas o número da linha.

Agora, para buscarmos a cotação da primeira ação, digitamos a fórmula abaixo na célula F14:

• =FILTROXML($D$10;"//Papel[1]/@Ultimo")

Em seguida, repetimos o procedimento para a célula F15, alterando apenas o número da linha para 2.

Abaixo a planilha com todas as fórmulas prontas.

Para obtermos a última cotação, forçando um recalculo geral, teclamos CTRL+ALT+F9. Notem abaixo que a cotação foi alterada.

Se alterarmos os códigos desejados, uma nova pesquisa é feita e a planilha estará atualizada com os novos elementos.

Exercício pronto!

É muito importante atentarmos para o fato de que cada WebService é único, e seus argumentos devem ser obtidos junto à entidade que oferece o serviço. Neste artigo utilizamos um da BOVESPA. Poderíamos, por exemplo, ter utilizado algum outro, dos Correios, por exemplo, mas para isso precisaríamos entender sua estrutura, disponível no site dos Correios.

Para fazer o download do modelo utilizado, CLIQUE AQUI.

Estamos aqui, à disposição, aceitando sugestões para novos artigos.

Aproveitem e conheçam o site da nossa empresa: www.excelline.net.

Leiam também outros artigos em NOSSO BLOG.

Contem conosco!

Julho/2014

Curta a nossa página

GILBERTO GOMES

ExcelLine - Office World