Automatizando a extração de dados da web com Power Query para Excel: exemplo prático

No post anterior realizamos a análise dos dados de público do Campeonato Brasileiro de 2015, e criamos alguns relatórios para visualizar de forma simples os resultados. Agora vamos ver como realizar a extração destes dados acessando diretamente o site da CBF e como mantê-los atualizados utilizando mais um add-in para o Excel – o Power Query.

No Excel 2013, na aba “Power Query”, vamos escolher a opção “From Web” para extrair dados da web (imagem a seguir):

powerquery_excel

Obs: o Power Query é um add-in para o Excel 2013 (precisa ser habilitado para estar disponível).

Logo após vamos inserir a URL do site da CBF onde se encontram as informações sobre os jogos do campeonato (imagem abaixo):

powerquery_web

Como os dados desta página web estão armazenados na forma de tabela, as informações já são carregadas para o editor do Power Query, dentro da planilha do Excel.

Assim, tendo os dados carregados, podemos identificar que alguns campos não estão trazendo os valores no formato ideal para o tipo de relatório que queremos gerar, como por exemplo:

  • Os gols marcados pelo clube mandante e visitante estão na mesma coluna, o que dificultaria a realização de somatórios de gols por clube, e também a identificação de qual clube fez aquela quantidade de gols;
  • Data e hora estão na mesma coluna: para facilitar a criação de colunas calculadas é interessante separar estes valores;
  • Estádio, cidade e estado na mesma coluna: para gerar relatórios por cidade e por estado é interessante separar estes valores.

Visando atender a estas necessidades vamos utilizar a função “Split Column” do Power Query para “quebrar” estes valores em múltiplas colunas (imagem abaixo).

powerquery_split

Na opção “Split by Delimiter”, vamos escolher o delimitador de acordo com a coluna: para o número de gols iremos utilizar o “x”; e para as colunas de data e estádio iremos utilizar o ” – ” para separar os valores (imagem abaixo).

powerquery_split_delimiter

Após a transformação, foram criadas as colunas “Data”, “Hora”, “Gols Mandante”, “Gols Visitante”, “Estádio”, “Cidade” e “Estado”. Confira o resultado na imagem abaixo:

powerquery_result

Terminado o processo de transformação, podemos finalmente carregar os dados já formatados para nossa planilha do Excel. A query do Power Query que foi gerada para buscar os dados aparece no lado direito da tela, e é possível clicar no botão “Refresh” (imagem a seguir). Assim, sempre teremos na nossa planilha os dados mais recentes assim que forem publicados no site.

powerquery_queryrefresh

Mas e os dados de público e renda?

Uma das grandes dificuldades para quem trabalha com extração, tratamento e análise de dados são os diferentes formatos de onde nós precisamos buscar as informações. Quando os dados estão armazenados em tabelas ou bancos de dados estruturados a extração é realizada de forma relativamente simples. Mas muitas vezes, por diversos motivos que vão desde regras de negócio específicas, ausência de sistemas para armazenar estas informações e até mesmo desconhecimento da importância de ter os dados em um formato adequado para que seja possível gerar relatórios que irão apoiar a tomada de decisão, os dados só estão disponíveis em formatos de difícil extração.

No caso do site da CBF, os dados de público e renda são preenchidos manualmente por algum funcionário da federação estadual responsável pela organização do jogo. Estes documentos (boletins financeiros das partidas) são digitalizados e disponibilizados no site no formato PDF. Esta regra de negócio faz com que cada federação tenha um modelo próprio de boletim financeiro, além do fato dos dados estarem armazenados em arquivos (.doc, .pdf) inviabiliza a extração automática pelo Power Query ou por meio de alguma outra ferramenta (pelo menos com relação às tecnologias que eu conheço até aqui).

Sendo assim, como não temos acesso a outro sistema que possua estes dados em formato adequado, a solução que utilizei foi realizar a inclusão manual destes dados em duas novas colunas na planilha Excel para gerar os gráficos e relatórios do post anterior. Como esta foi uma análise pontual que precisamos realizar e são poucos valores que precisaram ser incluídos, foi possível cadastrar estas informações em um tempo razoável.

Este cenário acabou nos proporcionando um exemplo interessante das dificuldades encontradas para extrair e consolidar os dados, mas também de como conseguimos facilitar a nossa vida utilizando ferramentas poderosas, como é o Power Query para Excel.

Obs: Como referência, adicionei abaixo o script que realiza as tarefas de transformação exemplificadas neste post. Quem quiser reproduzir este exemplo, basta criar a conexão com a mesma URL que eu utilizei, copiar a query abaixo e colar, dentro do Power Query, na opção “Advanced Editor”:

 let
Source = Web.Page(Web.Contents("http://www.cbf.com.br/competicoes/brasileiro-serie-a/tabela/2015")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Jogo", Int64.Type}, {"Rodada", Int64.Type}, {"Data", type text}, {"Mandante", type text}, {"", type text}, {"Visitante", type text}, {"Estádio", type text}, {"2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Data",Splitter.SplitTextByDelimiter("-"),{"Data.1", "Data.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type date}, {"Data.2", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Data.1", "Data"}, {"Data.2", "Hora"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns","",Splitter.SplitTextByDelimiter("x"),{".1", ".2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{".1", Int64.Type}, {".2", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{".1", "Gols Mandante"}, {".2", "Gols Visitante"}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns1","Estádio",Splitter.SplitTextByDelimiter(" - "),{"Estádio.1", "Estádio.2", "Estádio.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Estádio.1", type text}, {"Estádio.2", type text}, {"Estádio.3", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Estádio.1", "Estádio"}, {"Estádio.2", "Cidade"}, {"Estádio.3", "Estado"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns2",{{"Jogo", Order.Ascending}})
in
#"Sorted Rows"

Sugestão de leitura relacionada:

Introdução ao Microsoft Power Query para Excel

Download Microsoft Power Query para Excel

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s