Stock ETL Pipeline

Resumen

Construí un ETL programado que extrae datos de mercado diarios y por hora de 31 tickers desde Yahoo Finance y los carga en BigQuery. El conjunto incluye instrumentos colombianos (acciones de la BVC, ADRs en NYSE y NASDAQ, y el ETF COLCAP) y acciones de EE. UU.

Todo lo que viene después lee el mismo modelo de BigQuery a través de un servicio FastAPI en Cloud Run: un dashboard en Streamlit y un agente conversacional construido sobre la API de Anthropic Claude que llama a los endpoints de la API como herramientas.

Enfoque

Modelé los precios como tablas de hechos más una dimensión de tickers, para que los datos de cada empresa puedan cambiar sin reescribir el historial de precios.

Limpié y tipé los datos en pandas antes de que lleguen a BigQuery, y los cargué mediante una tabla temporal y un MERGE por fecha y ticker. Ejecutar el pipeline dos veces el mismo día no duplica filas.

Programé el pipeline con GitHub Actions en lugar de Cloud Scheduler, así la programación queda versionada junto al código y el historial de ejecuciones es visible en el repositorio.

Arquitectura

1. Programación

   Un workflow de GitHub Actions ejecuta el pipeline todos los días a las 22:00 UTC, después del cierre de la BVC, NYSE y NASDAQ, y también se puede lanzar manualmente.

2. Extracción y transformación

   yfinance descarga barras diarias y por hora; pandas renombra, tipa y descarta filas incompletas. Un modo de backfill carga historia desde una fecha dada, y el historial por hora se limita al máximo que permite Yahoo.

3. Modelo en BigQuery

   Tablas de hechos diaria y horaria cargadas con MERGE, una dimensión de tickers que se reescribe desde una única fuente de verdad, una tabla de métricas recalculada en SQL con funciones de ventana (retorno diario, media móvil de 20 días, volatilidad de 20 días) y dos vistas con hora de Colombia y datos del ticker.

4. Capa de API

   FastAPI con 8 endpoints GET sobre consultas parametrizadas a BigQuery y modelos de respuesta tipados, empaquetada con Docker y desplegada en Cloud Run.

5. Dashboard y agente

   Un dashboard en Streamlit y Plotly consume la API: KPIs, velas con media móvil de 20 días, volumen, datos intradía y los mayores movimientos. Una página de chat usa Claude con tool use, donde cada endpoint de la API es una herramienta que el modelo puede llamar.

Alcance

• 31 tickers: 24 instrumentos colombianos (acciones de la BVC, ADRs en NYSE y NASDAQ, y el ETF COLCAP) y 7 acciones de EE. UU.
• 4 tablas y 2 vistas en BigQuery, cargadas por una ejecución diaria programada.
• 8 endpoints GET: uno de estado del servicio más tickers, precios, barras intradía, métricas, mayores movimientos y un resumen del mercado.

Tecnología

• Python
• pandas
• yfinance
• BigQuery
• FastAPI
• Docker
• Cloud Run
• GitHub Actions
• Streamlit
• Plotly
• Anthropic Claude API

Limitaciones

• Los datos vienen de Yahoo Finance a través de yfinance, una fuente no oficial, y el historial por hora se limita a unos 730 días.
• Los precios mezclan USD (ADRs y acciones de EE. UU.) y COP (instrumentos de la BVC), así que las comparaciones necesitan la moneda de la dimensión de tickers.
• Todavía no hay pruebas automatizadas.
• La API es pública y no tiene autenticación.
• Guardar los archivos en bruto en Cloud Storage está en el roadmap, pero no está implementado.