- O eurofxref-hist.zip do BCE é apenas um pacote simples de CSVs de câmbio, mas com
curl, gunzip e sqlite3 já dá para encontrar diretamente a data em que o dólar esteve mais forte em relação ao euro: 2000-10-26
- O original está em wide format, com colunas por moeda depois de
Date, o que é inconveniente para análise; é preciso fazer uma limpeza para convertê-lo para long format, no formato Date,Currency,Rate
- Por causa da trailing comma no fim de cada linha, o parser de CSV lê uma coluna vazia; no Pandas, é preciso remover a última coluna com
.iloc[:,:-1] para que o resultado do melt fique limpo
- O CSV limpo pode ser enviado ao csvbase via HTTP PUT e depois combinado com ferramentas como
gnuplot, DuckDB e sqlite3 para gerar gráficos, calcular médias móveis e carregar CSVs via HTTP
- Dados públicos que podem ser obtidos sem negociação de acesso, autenticação, cotas ou documentação complexa de API funcionam como uma open API; até um simples arquivo zip pode servir de base para troca de dados em aplicações financeiras
Consultando câmbio com um único arquivo zip
- O BCE publica dados históricos de câmbio entre o euro e outras moedas em um arquivo zip oficial
- O pipeline abaixo baixa os dados, descompacta o arquivo, lê o CSV em um banco SQLite em memória, ordena pelo valor de USD e obtém a primeira data
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
| gunzip \
| sqlite3 ':memory:' '.import /dev/stdin stdin' \
"select Date from stdin order by USD asc limit 1;"
- A saída é
2000-10-26
curl -s reduz o ruído no erro padrão, e gunzip descompacta o arquivo zip- No Mac OS ou em BSD, o
gunzip da família BSD não oferece suporte a arquivos zip, então é preciso usar bsdtar -xOf - no lugar
sqlite3 ':memory:' usa um banco em memória, e .import /dev/stdin stdin importa a entrada padrão para a tabela stdin
Ajuste do formato do CSV e melt no Pandas
- O cabeçalho do CSV original está em wide format, como
Date,USD,JPY,BGN,CYP,CZK,DKK,..., com colunas por moeda depois da coluna de data
- Para filtrar e agregar, o long format no formato
Date,Currency,Rate é mais fácil de manipular
- A operação de transformar wide format em long format é comumente chamada de melt
- A maioria dos bancos de dados SQL não tem uma operação equivalente a melt, então o Pandas é útil para limpeza de dados
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).melt("Date").to_csv(sys.stdout, index=False)'
- O arquivo do BCE tem uma trailing comma no fim de cada linha, então o parser de CSV lê uma coluna vazia extra no final
- Essa coluna vazia cria linhas inúteis no fim do resultado do
melt, por isso precisa ser removida
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)'
.iloc[:, :-1] seleciona todas as linhas e todas as colunas exceto a última- Os dados de câmbio do BCE exigem ajuste de formato, mas podem ser usados diretamente sem negociação de acesso, pagamento, conversa com vendedor, envio de e-mail, nome da empresa ou cargo, cotas, autenticação nem leitura de documentação de API
- Como só é preciso lidar com problemas básicos de formato e estrutura, eles são relativamente bons entre os releases de dados públicos
Enviando os dados limpos ao csvbase
- O CSV limpo pode ser enviado para uma tabela csvbase, evitando repetir o trabalho de limpeza
- Basta anexar mais um
curl ao fim do pipeline existente para fazer upload do CSV por HTTP PUT
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)' | \
curl -n --upload-file - \
'https://csvbase.com/calpaterson/eurofxref-hist?public=yes'
--upload-file - faz upload dos dados recebidos pela entrada padrão para a URL especificada- Se a tabela não existir no csvbase, ela é criada; se existir, os dados são colocados nessa tabela
-n usa as credenciais em ~/.netrc
Desenhando um gráfico de câmbio com gnuplot
- A tabela limpa no csvbase pode ser combinada com
curl, grep, cut e gnuplot para receber o CSV e processá-lo
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut -d, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term dumb; \
plot '-' using 1:2 with lines title 'usd'"
- Esse comando desenha mais de 6.000 pontos de dados como arte ASCII de forma razoavelmente legível em um terminal de 80x25 caracteres
- A configuração do
gnuplot é ajustada para receber entrada CSV e desenhar a data e a taxa de câmbio como um gráfico de linhas
set datafile separator ',': especifica que a entrada é CSVset term dumb: desenha em arte ASCIIplot -: recebe os dados da entrada padrãousing 1:2 with lines: desenha uma linha usando a coluna 1 e a coluna 2, ou seja, data e câmbiotitle 'usd': define o nome da linha como usd
- Também é possível gerar uma imagem SVG; para que ela pareça uma série temporal, é preciso especificar que o eixo x é temporal, definir o formato de tempo e configurar a rotação dos rótulos do eixo x
- Para uso repetido, isso pode ser agrupado em uma função Bash
plot_timeseries_to_svg
Calculando média móvel com DuckDB
- Para ver a linha de tendência da taxa de USD, é possível calcular uma média móvel com DuckDB
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
duckdb -csv -c "select Date, avg(value) over \
(order by date rows between 100 preceding and current row) \
as rolling from read_csv_auto('/dev/stdin')
where variable = 'USD';" | \
plot_timeseries_to_svg rolling
- Se você não tiver
duckdb, não é difícil adaptar a mesma consulta para sqlite3
- O DuckDB é parecido com o SQLite, mas é orientado a colunas, não a linhas
- O DuckDB consegue ler CSVs diretamente via HTTP e criar um arquivo de tabela
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist";);
- O DuckDB faz uma inferência de tipos bastante boa e detecta o tamanho do terminal para, por padrão, mostrar resultados grandes de forma resumida
- Ele também pode exibir uma barra de progresso em consultas grandes e gerar saída em tabela Markdown
Como dados públicos funcionam como uma open API
- Dá para fazer muita coisa usando apenas um CSV dentro de um arquivo zip e ferramentas fáceis de instalar com
brew install ou apt install
eurofxref-hist.zip é um protocolo extremamente simples de troca de dados entre organizações- Esse arquivo zip pode parecer pequeno, mas muitas aplicações financeiras o usam todos os dias
- É possível entender que o BCE mantém a trailing comma porque removê-la agora poderia quebrar muito código
- Quando dados públicos são oferecidos de forma muito fácil, eles também cumprem o papel de uma open API
- Se muitas APIs são mais próximas de troca de dados do que de chamada remota de função, elas não são funcionalmente tão diferentes de dados públicos fáceis de obter
URLs simples e verbos HTTP do csvbase
- O csvbase mantém uma URL para cada tabela
https://csvbase.com/<username>/<table_name>;
https://csvbase.com/calpaterson/eurofxref-hist
- Cada URL tem quatro verbos HTTP principais
GET: recebe o CSV; no navegador, pode receber uma página webPUT: cria uma nova tabela com um novo CSV ou sobrescreve uma tabela existentePOST: adiciona em lote linhas CSV a uma tabela existenteDELETE: remove a tabela
- A autenticação usa HTTP Basic Auth
Observações sobre limpeza de dados e pipelines
- Entre os bancos de dados SQL, os que oferecem uma funcionalidade equivalente a melt incluem o UNPIVOT do Snowflake e o PIVOT/UNPIVOT do MS SQL Server
- Um dos motivos importantes para o uso de R e Pandas é a força de suas funcionalidades de limpeza de dados
- Pipelines Bash funcionam em multiprocessamento: cada programa é executado em paralelo em seu próprio processo
- Em outubro de 2000, a taxa do dólar em relação ao euro era
0.8252, o que significa que 1 dólar comprava 1,21 euro
- O euro foi lançado em janeiro de 1999 sem cédulas nem moedas; no início, existia apenas internamente nos bancos, e as cédulas e moedas vieram depois
1 comentários
Opiniões no Hacker News
Lembro desse arquivo de quando trabalhei no ECB uns 15 anos atrás
Esse arquivo era, de longe, o mais baixado no site do ECB, e muitas pessoas e instituições financeiras o baixavam todos os dias para atualizar seus próprios sistemas
Nos poucos minutos logo depois do horário fixo de publicação diário, o tráfego aumentava bastante, e foi uma decisão deliberada fazer com que, ao descompactá-lo, ele virasse um simples arquivo CSV
Graças a isso, era possível servir o arquivo de forma estável e rápida, com poucos recursos, e a pequena equipe que cuidava do site público do ECB na época tinha todos os motivos para se orgulhar da decisão técnica de disponibilizar esses dados como um único arquivo estático
Não é chamativo, nem tem framework
Uns 15 anos atrás, trabalhei com troca de dados entre um sistema de registros de produtos e subsistemas inferiores/paralelos herdados de fusões e aquisições em uma grande empresa antiga da qual provavelmente todo mundo já comprou algum produto; era, em sua maior parte, importação/exportação em massa de arquivos de largura fixa ou delimitados enviados e recebidos por servidores SFTP
Na época, o produto já tinha 15 anos, e havia algo como 20 a 30 dessas fontes de dados ou exportações circulando, mas funcionava muito bem
É bem provável que ainda estejam usando isso hoje sem grandes mudanças, e naquela época o front-end antigo em Smalltalk estava sendo reescrito
Entre as fontes de dados que usávamos, era a mais fácil de lidar
O arquiteto diria que ZIP não é um formato adequado à especificação para esse propósito, compliance diria que é preciso verificar vazamento de dados pessoais, e o pessoal de risco diria que precisamos impedir que agentes maliciosos baixem o arquivo
O responsável pelo site provavelmente diria que adicionar qualquer coisa ao site exige um processo de mudança aprovado
Downloads simples de arquivos e arquivos CSV são ótimos
Gostaria que mais lugares publicassem dados nesse tipo de formato simples, e sinto que morro um pouco toda vez que preciso encher um “carrinho” para baixar dados do governo dos EUA
Há muitas ferramentas wrapper que facilitam esse pipeline específico, e, se você precisar de uma visualização web e de recursos um pouco mais avançados, algo como o Datasette também é bom
Você pode ler o arquivo ZIP como stream, processar o CSV linha a linha para transformá-lo e depois, no caso do Postgres, usar COPY FROM stdin para carregar os dados no banco
Parece tão lógico e útil, mas eu ainda não tinha me deparado com isso
Tenho muitos relatórios em CSV, então quero testar logo para rodar consultas rapidamente
Por exemplo, o tratamento de aspas diverge entre
"Look, this contains \"quotes\"!",012345e"Look, this contains ""quotes""!",012345, e podem aparecer exemplos ainda mais quebrados, como"Look, this contains "quotes"!",012345ouLook, this contains "quotes"!,012345Como vestígio de planilha, os zeros à esquerda também podem ser cortados, como em
"Look, this contains ""quotes""!",12345Em teoria, JSON também pode ser editado à mão e acabar como um arquivo meio quebrado, mas, na prática, quase nunca vi alguém fazer isso com arquivos JSON; e valores como números de série tendem a permanecer como strings no JSON, não como inteiros dos quais um app “prestativo” cortaria os zeros à esquerda
Por que diabos fazem isso? Existe algum motivo legítimo?
Mesmo que você troque o CSV por um documento JSON dentro de um ZIP, as vantagens são as mesmas
O problema real é haver tantos obstáculos para simplesmente baixar um único arquivo servido de forma estática
Já criei uma API para uma agência governamental, e os dados mudavam uma vez por ano ou eram revisados muito raramente
O conjunto de dados inteiro podia ser empacotado em um único arquivo ZIP de menos de 1 MB, mas o trabalho cresceu quando o arquiteto de soluções definiu os requisitos
Como os dados poderiam ter mudado exatamente no momento da requisição, ele proibiu o uso de cache, o que resultou em uma API lenta, e ainda surgiu um sistema de webhooks excessivamente complexo para avisar assinantes sobre mudanças nos dados
Um único arquivo ZIP talvez fosse simples demais, mas também não ficava muito longe do que realmente era necessário
Se quiser algo mais sofisticado, basta adicionar um webhook disparado quando o arquivo mudar, para que o cliente saiba quando baixá-lo de novo sem precisar fazer polling uma vez por dia
Ou então até um simples script que envie um e-mail predefinido para uma lista de distribuição quando houver mudança já seria suficiente
Se nada tiver mudado desde a última vez, você recebe uma resposta HTTP 304 vazia; se tiver mudado, recebe de novo o arquivo ZIP de menos de 1 MB junto com um novo ETag, e não sei o que estaria faltando aí
Cache aumenta a complexidade e também traz o risco de precisar revalidá-lo manualmente, então é possível que o arquiteto de soluções estivesse certo
Se for preciso baixar um arquivo de 565 KB só para obter um único resultado,
2000-10-26, isso é uma API terrível.Se a ideia é buscar uma grande quantidade de dados e repassá-la ao usuário, um CSV empacotado em ZIP é excelente, e eu prefiro muito mais isso do que protobuf para horários de trens em tempo real de transporte público, que não tem bom suporte em várias linguagens.
Mas, se for tratado como uma API para obter um único valor, é um desperdício enorme, e espero que ninguém coloque algo assim em um app.
O texto em si é ótimo, mas o título parece uma afirmação provocativa demais.
Não há absolutamente nenhum motivo para consultá-los mais de uma vez por dia, e é bem provável que as pessoas que usam esse tipo de dado queiram filtros ou agregações muito diferentes entre si.
Para obter a cotação atual, seria mesmo um design ruim, mas para esse uso existem outros serviços, e este arquivo se encaixa bem no caso de uso típico.
Não tem relação direta com APIs, mas, anos atrás, quando eu dava suporte a uma aplicação de gestão de terrenos, ela funcionava bem até sair uma nova versão, inclusive em escritórios satélite lentos que podiam ter conexão no nível de ISDN; a nova versão, porém, não funcionava de jeito nenhum.
O fornecedor disse para rodá-la em um servidor RDP, mas achei isso absurdo e fui investigar; descobri que uma chamada fazia, sem motivo algum,
SELECT * FROM sometable, enquanto outras chamadas na mesma execução usavam cláusulas SQL select adequadas.Quando contei isso ao fornecedor, primeiro eles ficaram muito confusos sobre como tínhamos descoberto, e no fim lançaram uma nova versão corrigida que podia ser usada também em conexões lentas.
É difícil entender por que os testes internos deles não pegaram isso e por que empurraram uma solução cara para o cliente.
Se você viu um pouco que seja de JavaScript hoje em dia, 565 KB e a lógica para achar um valor grande ali são minúsculos por qualquer critério razoável.
Algumas pessoas veem como API “um modo de obter dados, mesmo recebendo todos os dados sem filtragem”, mas, pessoalmente, considero o download de uma tabela inteira como o download de um modelo de dados no qual a lógica não opera; API, para mim, é a lógica que filtra e retorna parte do modelo da maneira que me interessa.
Já desenvolvi bastante software financeiro tanto no backend quanto no frontend, e, no frontend, infelizmente é comum transmitir essa quantidade de “dados” antes mesmo de chegar aos dados reais.
No backend, é apenas uma decisão de design, e não há nada mais rápido do que um cron noturno fazer o parse das cotações, gerar um
todays-rates.jsonadequado ao objetivo e servi-lo como arquivo estático para apps móveis, web e de microsserviços.Não há nada dizendo que o app móvel precise necessariamente consumir esse ZIP-CSV-over-HTTP diretamente.
Há uma otimização muito simples para quem reclama de ter que baixar um arquivo grande toda vez que precisa de um pequeno dado.
Se houver garantia de que o arquivo é append-only e for usada compressão como HTTP gzip/brotli em vez de um arquivo ZIP, dá para usar requisições de intervalo para baixar apenas os novos dados desde a última atualização.
Somando a isso um cabeçalho de checksum para dar segurança, vira uma API incremental bastante eficiente e ainda muito simples.
Claro que é preciso manter estado e pagar o custo do primeiro download e da manutenção desse estado; e, se você só precisa exatamente uma vez da cotação EUR/JPY de 2007-08-22, é ineficiente.
Ainda está em estágio bem inicial, mas o código atual, de “qualidade de pesquisa”, está aqui: https://pypi.org/project/csvbase-client/
https://github.com/gtsystem/python-remotezip
Só um patch diário já reduziria bastante a largura de banda necessária do meu lado para manter o arquivo atualizado.
Isso vale quando baixar algumas centenas de KB a mais por dia faz diferença; na maioria dos casos, provavelmente não faz.
Há um erro de digitação no exemplo com
sqlite.Não aparece no screenshot, mas é preciso adicionar o argumento -csv ao
sqlite.Vou adicioná-lo de volta e invalidar o cache. Depois que eu colocar as crianças para dormir, pretendo verificar o que deu errado.
Atualização: o motivo de ter funcionado no meu ambiente é que eu tinha
.separator ','configurado em~/.sqliterc.Parece que, em algum momento, percebi que eu importava principalmente arquivos CSV e deixei isso como padrão.
Fazendo um breve desvio: embora o euro inicialmente existisse apenas eletronicamente, havia uma taxa de câmbio fixa com as moedas existentes dos países membros da zona do euro.
Em especial, ele era fixado em relação ao Deutsche Mark alemão, já consolidado e confiável.
Portanto, para explicar “por que o euro era fraco no início”, também seria preciso explicar por que o DEM era fraco naquela época, e a explicação daquele parágrafo não parece passar por essa verificação.
Em problemas pequenos nos quais é possível baixar o banco de dados inteiro toda vez e tratá-lo como somente leitura, não se deve subestimar o valor da simplicidade.
Gosto do SQLite porque ele é portátil como arquivos
.jsonou.csv, mas está mais pronto para interagir como um banco de dados.clickhouse-local, também dá para tratar arquivos CSV antigos como um banco de dados.O ponto central está aqui.
Coisas que não foram necessárias neste caso: negociar acesso, por exemplo pagar ou falar com um vendedor; colocar seu e-mail, nome da empresa e cargo no banco de prospects de alguém; respeitar cotas; autenticar-se; ler documentação de API; lidar com problemas mais sérios do que formato e estrutura básicos.
Largura de banda não é de graça.
O SQLite consegue ler e escrever arquivos ZIP.
https://sqlite.org/zipfile.html
Fico curioso se é possível descompactar com
sqlite3em vez degunzip.Se puder salvar o arquivo no disco, dá para fazer assim:
sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \"select ...;"