- eurofxref-hist.zip del BCE es solo un paquete simple de CSV de tipos de cambio, pero con
curl, gunzip y sqlite3 se puede encontrar de inmediato la fecha 2000-10-26, cuando el dólar estuvo más fuerte frente al euro
- El original está en formato ancho, con columnas por moneda después de
Date, lo que resulta incómodo para el análisis; hace falta ordenarlo y convertirlo a un formato largo del tipo Date,Currency,Rate
- Debido a la coma final al terminar cada línea, el parser de CSV lee una columna vacía; en Pandas hay que eliminar la última columna con
.iloc[:,:-1] para que el resultado de melt quede limpio
- El CSV ya ordenado puede subirse a csvbase con HTTP PUT y luego conectarse con herramientas como
gnuplot, DuckDB y sqlite3 para crear gráficos, calcular promedios móviles y cargar CSV por HTTP
- Los datos públicos que se pueden obtener sin negociación de acceso, autenticación, cuotas ni documentación compleja de API funcionan como una API abierta; incluso un simple archivo zip puede ser la base de intercambio de datos para aplicaciones financieras
Consultar tipos de cambio con un solo archivo zip
- El BCE publica datos históricos de tipos de cambio entre el euro y otras monedas en un archivo zip oficial
- El siguiente pipeline descarga los datos, los descomprime, lee el CSV en una base SQLite en memoria, lo ordena por el valor de USD y obtiene la primera fecha
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;"
- La salida es
2000-10-26
curl -s reduce el ruido en el error estándar, y gunzip descomprime el archivo zip- En Mac OS o BSD, el
gunzip de la familia BSD no soporta archivos zip, así que hay que usar bsdtar -xOf - en su lugar
sqlite3 ':memory:' usa una base de datos en memoria, y .import /dev/stdin stdin importa la entrada estándar a la tabla stdin
Ordenar la forma del CSV y usar melt en Pandas
- El encabezado del CSV original es un formato ancho, con una columna de fecha seguida por columnas por moneda, como
Date,USD,JPY,BGN,CYP,CZK,DKK,...
- Para filtrar y agregar, es más fácil trabajar con un formato largo del tipo
Date,Currency,Rate
- A la operación de convertir un formato ancho en formato largo se le suele llamar melt
- La mayoría de las bases de datos SQL no tienen una operación equivalente a melt, por lo que Pandas resulta útil para limpiar datos
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)'
- El archivo del BCE tiene una coma final al terminar cada línea, por lo que el parser de CSV lee una columna vacía adicional al final
- Como esa columna vacía genera filas inútiles al final del resultado de
melt, hay que eliminarla
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] selecciona todas las filas y todas las columnas excepto la última- Aunque los datos cambiarios del BCE requieren ordenar el formato, se pueden usar de inmediato sin negociar acceso, pagar, hablar con un vendedor, entregar email, nombre de empresa o cargo, lidiar con cuotas, autenticación ni leer documentación de API
- Como solo hay que manejar problemas básicos de formato y forma, es una publicación de datos relativamente buena entre los datos públicos
Subir los datos ordenados a csvbase
- El CSV ordenado puede subirse a una tabla de csvbase para evitar repetir el trabajo de limpieza
- Si se agrega otro
curl al final del pipeline existente, el CSV puede subirse mediante 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 - sube al URL indicado los datos recibidos desde la entrada estándar- Si la tabla no existe en csvbase, la crea; si existe, carga los datos en esa tabla
-n usa las credenciales de ~/.netrc
Graficar tipos de cambio con gnuplot
- La tabla ordenada en csvbase permite descargar el CSV con
curl y conectarlo con grep, cut y gnuplot
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'"
- Este comando dibuja más de 6,000 puntos de datos como arte ASCII, de forma más o menos legible, en una terminal de texto de 80x25
- La configuración de
gnuplot está ajustada para recibir entrada CSV y dibujar la fecha y el tipo de cambio como una gráfica de líneas
set datafile separator ',': indica que la entrada es CSVset term dumb: dibuja como arte ASCIIplot -: recibe los datos desde la entrada estándarusing 1:2 with lines: dibuja una línea usando las columnas 1 y 2, es decir, fecha y tipo de cambiotitle 'usd': nombra la línea como usd
- También se puede exportar como imagen SVG; para que se vea como datos de serie temporal, hay que indicar que el eje x es tiempo y configurar el formato de tiempo y la rotación de las marcas del eje x
- Para uso repetido, se puede envolver en una función Bash
plot_timeseries_to_svg
Calcular un promedio móvil con DuckDB
- Para ver la línea de tendencia del tipo de cambio USD, se puede calcular un promedio móvil con 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
- Si no se tiene
duckdb, tampoco es difícil adaptar la misma consulta para sqlite3
- DuckDB es parecido a SQLite, pero no está orientado a filas sino a columnas
- DuckDB puede leer CSV directamente desde HTTP y crear un archivo de tabla
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist";);
- DuckDB infiere tipos bastante bien y detecta el tamaño de la terminal para mostrar por defecto resultados grandes de forma reducida
- Puede mostrar una barra de progreso en consultas grandes y también generar salida como tablas Markdown
Cómo los datos públicos funcionan como una API abierta
- Con un CSV dentro de un archivo zip y herramientas fáciles de instalar con
brew install o apt install, se puede hacer mucho trabajo
eurofxref-hist.zip es una forma muy simple de protocolo de intercambio de datos entre organizaciones- Aunque este archivo zip parece pequeño, muchas aplicaciones financieras lo usan todos los días
- Se puede considerar que el BCE conserva la coma final porque eliminarla ahora podría romper mucho código
- Cuando los datos públicos se ofrecen de forma muy sencilla, también cumplen el rol de una API abierta
- Si muchas API se parecen más a intercambio de datos que a llamadas remotas a funciones, funcionalmente no son tan distintas de datos públicos fáciles de descargar
URL simples y verbos HTTP en csvbase
- csvbase usa un solo URL para cada tabla
https://csvbase.com/<username>/<table_name>;
- Un ejemplo es el siguiente
https://csvbase.com/calpaterson/eurofxref-hist
- Cada URL tiene cuatro verbos HTTP principales
GET: recibe el CSV; en el navegador puede recibir una página webPUT: crea una nueva tabla con un CSV nuevo o sobrescribe una tabla existentePOST: agrega en masa filas CSV a una tabla existenteDELETE: elimina esa tabla
- La autenticación usa HTTP Basic Auth
Notas sobre limpieza de datos y pipelines
- Entre las bases de datos SQL que ofrecen una función equivalente a melt están UNPIVOT de Snowflake y PIVOT/UNPIVOT de MS SQL Server
- Una de las razones importantes por las que se usan R y Pandas es que tienen funciones fuertes de limpieza de datos
- Los pipelines de Bash funcionan con múltiples procesos: cada programa se ejecuta en paralelo como un proceso independiente
- En octubre de 2000, el tipo de cambio del dólar frente al euro era
0.8252, lo que significa que con 1 dólar se podían comprar 1.21 euros
- El euro se lanzó en enero de 1999 sin billetes ni monedas; al inicio solo existía dentro de los bancos, y los billetes y monedas llegaron más tarde
1 comentarios
Opiniones de Hacker News
Recuerdo este archivo de cuando trabajé en el BCE hace unos 15 años.
Era, por lejos, el archivo más descargado del sitio web del BCE, y mucha gente e instituciones financieras lo bajaban todos los días para actualizar sus propios sistemas.
Todos los días, durante unos minutos justo después de la hora programada de publicación, el tráfico se disparaba, y fue una decisión deliberada que, al descomprimirlo, quedara como un simple archivo CSV.
Gracias a eso podían servir el archivo de forma estable y rápida, con pocos recursos, y el pequeño equipo que en ese entonces se encargaba del sitio web público del BCE tenía buenos motivos para estar muy orgulloso de la decisión técnica de entregar esos datos como un único archivo estático.
No tiene nada llamativo ni usa frameworks.
Hace unos 15 años trabajé en una gran empresa antigua, de esas cuyos productos probablemente cualquiera ha comprado alguna vez, manejando el intercambio de datos entre el sistema de registros de productos y subsistemas inferiores/paralelos que quedaron de fusiones y adquisiciones; en su mayoría eran importaciones/exportaciones masivas de archivos de ancho fijo o delimitados que se enviaban y recibían por servidores SFTP.
En ese momento el producto ya tenía 15 años, y había unas 20 o 30 fuentes de datos o exportaciones de ese tipo circulando, pero funcionaba muy bien.
Es muy probable que todavía lo estén usando sin grandes cambios, y en esa época estaban reescribiendo el frontend antiguo hecho en Smalltalk.
Era la fuente de datos más fácil de manejar entre las que usábamos.
El arquitecto diría que ZIP no es un formato que cumpla con la especificación para este propósito; cumplimiento diría que hace falta revisar posibles filtraciones de datos personales; y el área de riesgo diría que hay que impedir que actores maliciosos descarguen el archivo.
El encargado web probablemente diría que agregar algo al sitio requiere un procedimiento de cambios aprobado.
Las descargas simples de archivos y los archivos CSV son excelentes.
Ojalá más lugares publicaran datos en formatos simples como este; cada vez que tengo que llenar un “carrito” en descargas de datos del gobierno de EE. UU. siento que muero un poco por dentro.
También hay muchas herramientas wrapper que facilitan este pipeline específico, y si necesitas una vista web y funciones un poco más avanzadas, algo como Datasette está muy bien.
Puedes leer el ZIP como stream, procesar el CSV línea por línea para transformarlo y luego cargarlo en la base de datos usando COPY FROM stdin en Postgres.
Suena tan lógico y útil que no entiendo cómo no me había topado con eso hasta ahora.
Tengo muchos reportes en CSV, así que quiero probarlo pronto para ejecutar consultas rápidamente.
Por ejemplo, el manejo de comillas puede variar entre
"Look, this contains \"quotes\"!",012345y"Look, this contains ""quotes""!",012345, y también pueden aparecer ejemplos más rotos como"Look, this contains "quotes"!",012345oLook, this contains "quotes"!,012345.Como rastro de una hoja de cálculo, también pueden perderse los ceros iniciales, como en
"Look, this contains ""quotes""!",12345.En teoría, JSON también puede editarse a mano y terminar medio roto, pero en la práctica casi nunca he visto que alguien haga eso con archivos JSON; además, valores como números de serie suelen quedarse como strings en JSON, no como enteros a los que una app “amigable” les recorta los ceros iniciales.
¿Por qué demonios lo hacen? ¿Existe alguna razón legítima?
Si cambias el CSV dentro de un ZIP por un documento JSON, las ventajas son las mismas.
El verdadero problema es que ponen demasiados obstáculos para simplemente descargar un único archivo servido de forma estática.
Una vez hice una API para una agencia gubernamental, y los datos cambiaban una vez al año o se revisaban muy de vez en cuando.
Todo el dataset podía empaquetarse en un solo archivo ZIP de menos de 1 MB, pero el asunto se agrandó cuando el arquitecto de soluciones definió los requisitos.
No permitió usar caché porque los datos podrían haber cambiado justo en el momento de la solicitud, lo que terminó en una API lenta, y además apareció un sistema de webhooks excesivamente complejo para avisar a los suscriptores sobre cambios en los datos.
Un solo archivo ZIP quizá habría sido demasiado simple, pero tampoco estaba muy lejos de lo que realmente se necesitaba.
Si quieres algo más elegante, puedes agregar un webhook que se dispare cuando cambie el archivo, para que el cliente sepa cuándo volver a descargarlo sin tener que hacer polling una vez al día.
O incluso bastaría con un script que, cuando haya cambios, envíe un correo predefinido a una lista de distribución.
Si no cambió respecto de la versión anterior, recibes una respuesta HTTP 304 vacía; si cambió, vuelves a recibir el ZIP de menos de 1 MB junto con el nuevo ETag. No veo qué falta ahí.
La caché aumenta la complejidad y también introduce el riesgo de tener que revalidarla manualmente, así que puede que el arquitecto de soluciones haya tenido razón.
Si hay que descargar un archivo de 565 KB para obtener un solo resultado,
2000-10-26, es una API terrible.Si lo que quieres es traer una gran cantidad de datos para volver a ofrecérselos al usuario, un CSV empaquetado en ZIP es excelente, y lo prefiero muchísimo a un protobuf para horarios de trenes en tiempo real de transporte público con mal soporte en varios lenguajes.
Pero si se trata como una API para obtener un único valor, es un desperdicio enorme, y ojalá nadie lo meta así en una app.
El artículo en sí está muy bueno, pero el título se siente demasiado como una afirmación provocadora.
No hay ningún motivo para solicitarlos más de una vez al día, y es muy probable que quienes usan estos datos quieran filtros o agregaciones muy distintos entre sí.
Si el propósito fuera obtener el tipo de cambio actual, sí sería un mal diseño, pero para eso hay otros servicios, y este archivo encaja bien con su caso de uso típico.
No está directamente relacionado con APIs, pero hace tiempo, cuando daba soporte a una aplicación de gestión de tierras, funcionaba bien incluso en oficinas satélite lentas que quizá tenían enlaces tipo ISDN, hasta que salió una nueva versión; con la nueva, ya no funcionaba para nada.
El proveedor decía que la ejecutáramos en un servidor RDP, pero nos pareció absurdo, así que investigamos y encontramos que una llamada hacía, sin motivo alguno,
SELECT * FROM sometable, mientras que otras llamadas dentro de la misma ejecución sí usaban cláusulas select de SQL correctas.Cuando se lo dijimos al proveedor, al principio estaban muy confundidos sobre cómo lo habíamos descubierto, y al final sacaron una nueva versión corregida que podía usarse incluso en enlaces lentos.
Cuesta entender por qué no lo detectaron en sus propias pruebas y por qué intentaron empujarles a los clientes una solución cara.
Si has visto aunque sea un poco de JavaScript hoy en día, 565 KB y la lógica para encontrar un valor grande dentro de eso son minúsculos bajo cualquier criterio razonable.
Algunas personas consideran API a “una forma de obtener datos, aunque sea recibiendo todo sin filtrado”, pero personalmente veo la descarga de una tabla completa como la descarga de un modelo de datos donde no opera lógica sobre el modelo; para mí, una API es lógica que filtra y devuelve parte del modelo de la manera que me interesa.
He creado bastante software financiero tanto de backend como de frontend, y en frontend lamentablemente es común transmitir esa cantidad de “datos” incluso antes de llegar a los datos reales.
En backend es simplemente una decisión de diseño, y no hay nada más rápido que un cron nocturno que parsea los tipos de cambio y genera un
todays-rates.jsonajustado al propósito, para servirlo como archivo estático a apps móviles, web y de microservicios.En ningún lado se dice que la app móvil necesariamente tenga que consumir directamente este ZIP-CSV-over-HTTP.
Hay una optimización muy simple para quien se queja de tener que descargar un archivo grande cada vez que necesita un dato pequeño.
Si se garantiza que el archivo es solo de anexado y se usa compresión como HTTP gzip/brotli en lugar de un archivo ZIP, se pueden usar solicitudes de rango para recibir solo los datos nuevos desde la última actualización.
Si además se agrega un header de checksum para mayor tranquilidad, queda una API incremental bastante eficiente y aun así muy simple.
Claro que hay que conservar estado, pagar el costo de la primera descarga y del mantenimiento de ese estado, y es ineficiente si solo necesitas una única vez exactamente el tipo de cambio EUR/JPY del 2007-08-22.
Todavía está muy en desarrollo, pero el código actual de “calidad de investigación” está aquí: https://pypi.org/project/csvbase-client/
https://github.com/gtsystem/python-remotezip
Con un solo parche diario ya se reduciría mucho el ancho de banda necesario para mantener el archivo actualizado de mi lado.
Eso aplica cuando bajar unos cientos de KB más por día importa; probablemente, la mayoría de las veces no importe.
Hay un typo en el ejemplo de
sqlite.No aparece en la captura, pero hay que agregar el argumento -csv a
sqlite.Lo volveré a agregar e invalidaré la caché. Cuando acueste a los niños voy a revisar qué salió mal.
Corrección: la razón por la que funcionaba en mi entorno era que tenía
.separator ','en~/.sqliterc.Parece que alguna vez me di cuenta de que principalmente cargaba archivos CSV y lo configuré como valor predeterminado.
Yéndome un momento por la tangente: aunque el euro al principio solo existiera electrónicamente, tenía tipos de cambio fijos con las monedas existentes de los países miembros de la eurozona.
En particular, estaba fijado al Deutsche Mark alemán, que estaba establecido y gozaba de confianza.
Por lo tanto, para explicar “por qué el euro inicial era débil”, también habría que explicar por qué el DEM era débil en ese momento, y me parece que la explicación de ese párrafo no pasa esa comprobación.
En problemas pequeños donde puedes descargar toda la base de datos cada vez y tratarla como de solo lectura, no hay que subestimar el valor de la simplicidad.
Me gusta SQLite porque es portable como un archivo
.jsono.csv, pero está mejor preparado para interactuar como una base de datos.clickhouse-localpuedes tratar incluso archivos CSV viejos como una base de datos.El punto clave está aquí.
Cosas que en este caso no hubo que hacer: negociar permisos de acceso; por ejemplo, pagar o hablar con un vendedor; meter tu correo electrónico, nombre de empresa y cargo en la base de datos de prospectos de alguien; respetar cuotas; autenticarse; leer documentación de la API; lidiar con problemas más serios que el formato y la estructura básicos.
El ancho de banda no es gratis.
SQLite puede leer y escribir archivos ZIP.
https://sqlite.org/zipfile.html
Me pregunto si se puede descomprimir con
sqlite3en lugar degunzip.Si está bien guardar el archivo en disco, se puede hacer así:
sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \"select ...;"