Em nosso primeiro post tentamos, sem sucesso analisar a relação do dólar (valor do Real em relação ao restante do mercado) com o preço de alguns componentes de Hardware. Entretanto não obtivemos sucesso, pois não tínhamos dados o suficiente para ver a volatilidade de preços diariamente.
Neste post vamos tentar nos redimir montando a nossa própria base histórica de preços com uma atualização incemental. Entretanto para isso, vamos precisar atualizar o Power query manualmente todos os dias.
Percebam que o Power BI tem uma solução de atualização incremental, mas apenas para as versões Power BI Pro, Premium e Shared. Saiba mais aqui.
Para simplificar a nossa query vamos utilizar uma estrutura de dados tipo Star. Não vou entrar em detalhes sobre o por que o Star Schema não é o ideal para trabalhar, isso será assunto de um posto futuro, mas para quem tiver interesse em pesquisar, recomento sempre utilizar o Snowflake Schema.
Essa simplificação será feita pois na verdade esse post será feito em duas etapas, primeiro vamos fazer uma gambiarra onde teremos que atualizar manualmente pelo Excel, em seguida vamos fazer da forma correta, transportando o nosso código M para o SSDT e agendar uma atualização em SQL.
Índice
- Pré-requisitos;
- Encontrar uma API;
- Consumir a API;
- Criar a função personalizada;
- Criar uma tabela para consultar os produtos;
- Gabiarra para atualizar;
- Agradecimentos.
Pré-requisitos
Para a gambiarra funcionar todo o código M precisa estar no Excel, e caso você deseje não utilizar a gambiarra e sim fazer esse processo pelo SSDT vamos precisar que na etapa de criação da tabela
Encontrar uma API
Este processo é o mais difícil de todos, já que cada desenvolvedor cria cada site de uma forma. Os sites pode trabalhar com uma página HTML já com os dados embutidos (neste caso o back-end monta a página HTML com todos os dados) ou a página HTML pode utilizar variáveis e exibir os dados com base em uma solicitação AJAX (assim seria o front-end que colocaria o preço do produto na página).
Por questões de segurança, a maioria dos e-commerces colocam o preço já no back-end, o que dificulta bastante a localização de uma API para consultar os dados. Felizmente nestes momentos contamos com a ajuda da comunidade.
No meu caso eu tentei localizar a API de 3 sites de E-commerce, Kabum, Terabyte e Pichau, e graças ao Felipe Guimarães conseguimos ao menos a API da Kabum.
Abaixo eu cheguei até o do Felipe, para caso você tente realizar algo similar com outros sites tenha ao menos um ponto de partida.
1. Pelo Google Chome, inspecionei a página de um produto qualquer (Ctrl+I) e pesquisei no código HTML (Ctrl+F) a expressão “.api.”. Com isso obtive o seguinte resultado:
2. Com conhecimento do subdomínio da API (services.prod.api.aws.grupokabum.com.br), fiz uma pesquisa no Google junto ao termo “produto” e encontrei o código do Felipe no GitHub e com isso à URL “https://servicespub.prod.api.aws.grupokabum.com.br/descricao/v1/descricao/produto/${productId}”.
3. O campo productId será o número que fica dentro da URL da página de um produto. Exemplo na URL abaixo o código é 86441: https://www.kabum.com.br/cgi-local/site/produtos/descricao_ofertas.cgi?codigo=86441
Consumir a API como teste
Agora com a API em mãos vamos consumi-la dentro do Power Query (pode ser no Power BI ou no Excel): E preencher conforme abaixo: B. Editar a fórmula M removendo o 93134 por KabumCode. C. Renomear a consulta para TesteKabum.
1. Criar um parâmetro chamado “KabumCode” tipo texto.
Como
Em Página Inicial > Parâmetros > Gerenciar Parâmetros > Novo Parâmetro:
2. Adicionar uma nova consulta tipo Web para a URL da API e trocamos o valor ${productid} por 93134. Após carregada vamos editar a fórmula M trocando o código 93134 para o parâmetro KabumCode, e renomear a consulta para TesteKabum.
Como
A. Adicionar a Consulta: vamos em Página Inicial > Nova Consulta > Nova Fonte > Outras Fontes > Web.
3. Converter os registros como tabela
Como
Em Converter > Converter > Na Tabela
4. Na coluna “Name”, filtrar as linhas codigo, name, preco, preco_antigo, preco_desconto, preco_desconto_prime, preco_prime.
Como
Clique na seta ao lado da coluna Name, desmarque todas as outras opções e marque apenas as listadas abaixo:
5. Transpor a tabela
Como
Em Transformar > Tabela > Transpor.
6. Renomear as colunas conforme abaixo:
Como Digite 1 e clique em OK na próxima tela. Revise o cadastro e se for o caso altere colunas que estejam com o formato incorreto. Essa alteração pode ser feita selecionando a coluna incorreta e indo em Transformar > Qualquer Coluna > Tipo de Dados. Vamos clicar com o botão direto na consulta TesteKabum e selecionar “Criar Função…”. Na próxima tela informamos o nome FnKabum e clicamos em “OK”. A tabela deve conter pelo menos o Código do produto, mas é interessante colocar algumas categorias para podermos aplicar filtros ao analisar. 1. Para alimentar essa tabela fixa no power query você pode seguir este link explico esse procedimento. Aqui o processo será o mesmo do Power BI., ele precisa ser feito no Power BI, mesmo que depois essa tabela volte para o Excel, isso pois esse projeto será convertido em SSDT no próximo post. Se você não pretende fazer essa conversão. Pode usar o procedimento indicado para o Excel que também está no link citado. 2. Verifique se o formato de todas as colunas estão como tipo Text principalmente a Coluna Parâmetros (Código do produto). 3. Adicionar Coluna com a DataHora Local e chama-la de “DataHora”. Digite a Formula DateTime.LocalNow(). Em seguida coloque null e pressione OK. Por fim edite a fórmula trocando o nome da coluna: Por fim pode fechar o power query e carregar os dados. Só vamos precisar da Query chamada Atual, todas as demais podem ser ocultadas (PBI) ou configuradas como “apenas conexão” (Excel). Esse processo só vai funcionar para o Excel, então se você tem como objetivo fazer algo mais definitivo aguarde a próxima publicação onde vamos converter essa Query para SSDT. Como o foco deste post é a linguagem M, não entrarei em detalhes já que daqui em diante vamos usar liguagem MS-DOS e VBA. 1. Em uma nova planilha (aba), crie uma tabela chamada “Histórico”. Como Como Fico muito feliz de fazer parte da comunidade developer, o pessoal é sempre muito solicito e gostaria de agradecer à ao Felipe Guimarães que ajudou neste post a localizar a API para a consulta. Sem ele esse post não seria possível 🙂
Basta clicar duas vezes no nome da coluna antigo e digitar o novo nome.
7. Remover a 1ª linha.
Como
Em Página Inicial > Reduzir Linhas > Remover Linhas > Remover Linhas Principais.
8. Detectar tipos de dados. Código deve ser Int64.Type, Produto deve ser type text e os demais type number.
Como
Com todas as colunas selecionadas, Vá em Transformar > Qualquer Coluna > Detectar Tipos de Dados.
Criar a função personalizada
As consultas deverão ficar assim:
Criar uma tabela para invocar a função personalizada
Neste caso estou utilizando as colunas Site (origem), Parâmetro (código do produto), Grupo (tipo de peça), Produto (descrição completa), Categoria (exs: Ryzen 5, Ryzen 3), e 3 colunas para indicar valores que qualificam o produto, esse valores podem ser frequência, espaço da unidade entre outros. Segue um exemplo, você pode acompanhar o preço de qualquer produto do site que escolher:
Vamos nomear essa Query como “Atual”.
Como
Em Adicionar coluna > Geral > Coluna Personalizada
4. Adicionar Coluna invocando a função personalizada, vamos adicionar uma coluna personalizada igual ao passo anterior. Entretanto desta vez com a seguinte fórmula:
if [Site] = “Kabum” then try FnKabum([Parâmetro]) otherwise null else null
Vamos nomeá-la “Fn”.
Porquê
A. Colocamos o if [Site] = “Kabum” para quisermos criar mais funções personalizadas e consultar mais sites, termos essa opção.
B. Colocamos a sintaxe try FnKabum([Parâmetro]) otherwise null para não puxarmos os dados caso a consulta apresente erro.
C. E por fim colocamos else null para tratar possíveis erros de digitação no siste, ou seja caso inserirmos um site que não criamos função ainda.
5. Filtrar linhas com erro (nulas).
Como
Filter uma coluna diferente da Coluna Fn, clicando na seta ao lado do cabeçalho > Filtro de Texto > Diferente de…
6. Expandir coluna Fn.
Como
Clique ao lado da seta do cabeçalho Fn > desmarque usar o nome como prefixo e pressione “OK”.
7. Reordenar colunas, no meu caso eu coloquei na ordem: “DataHora”, “Site”, “Produto”, “Código”, “Preço”, “Preço Prime”, “Preço Desconto”, “Preço Desc Prime”, “Preço Antigo”, “Parâmetro”, “Grupo”, “Categoria”, “Unidade1”, “Unidade2”, “Unidade3”.
Fiz desta forma, pois achei que ficaria melhor assim.
Como
Arraste as colunas até que fiquem na ordem desejada
8. Alterar o tipo das colunas, esse procedimento é igual ao passo 8 da quey TesteKabum.Gambiarra para Automatizar a atualização da query
Como
2. Copie o toda a tabela resultado da consulta “Atual” e cole para a tabela recém criada.
Como
3. Abra o editor de VBA e cole o código abaixo em um novo módulo:Sub AtualparaHist()
Application.Goto Reference:="Atual"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:="Atual"
Selection.Copy
Application.Goto Reference:="Histórico"
Selection.End(xlDown).Offset(1, 0).Select
n = Range("Histórico[DataHora]").Count + 1
n = n + Range("Atual[DataHora]").Count
ActiveSheet.ListObjects("Histórico").Resize Range("$A$1:$P$" & n)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ThisWorkbook.Save
Application.Quit
End Sub
Atalho: Alt + F11
4. Adicione o código abaixo no código da Workbook:Private Sub Workbook_Open()
Call AtualparaHist
End Sub
5. Salve a planilha em xlsm.
6. Feche a planilha. Agora sempre que você abrir ela o Power query vai executar a consulta e a planilha será salva e fechada em seguida. Se você não quiser que ela feche, precisará ficar pressionando a tecla “Esc” para para a macro antes do fechamendo.
7. Agende uma tarefa no Windows para abrir a planilha todos os dias. Caso precise, você pode ver esse post do blog ExcelVBA, que explica como realizar esse procedimento.Agradecimento especial:
Linkedin – Githug