ETL Pipeline Architecture

This module acts as the gateway to financial markets. Using the yFinance API, it iteratively requests data for 21 target companies (e.g., Apple, Tesla, JP Morgan). The script is designed to be fault-tolerant, using try-catch blocks to handle API timeouts gracefully.

• Historical Prices: Pulls 5 years of Open, High, Low, Close, Volume data.
• Financial Statements: Merges Income Statement, Balance Sheet, and Cash Flow into a single view.
• Company Profile: Captures metadata like Sector, Industry, and Website for context.

Raw data is rarely analysis-ready. This script cleans the dataset and, more importantly, generates financial intelligence. It fills missing values and converts raw numbers into meaningful ratios used by investors.

• Standardization: Renames columns to snake_case and fixes UTC timezones.
• Ratio Calculation: Automatically computes 15+ metrics including ROE, ROA, Current Ratio, and Debt-to-Equity.
• Data Hygiene: Handles NaN values and prevents "Division by Zero" errors to ensure database stability.

This step safely deposits processed data into a MySQL database using SQLAlchemy. The logic is built to support "Upsert" operations - meaning it can update existing records or insert new ones without creating duplicates.

• Integrity Checks: Uses INSERT IGNORE and ON DUPLICATE KEY UPDATE to manage data history.
• Batch Processing: Loads data in chunks (2000 rows/batch) to optimize memory usage.
• Type Mapping: Ensures Python Pandas data types align perfectly with MySQL table schemas.

To ensure the dashboard is always "Fresh", the entire pipeline is automated via GitHub Actions. It runs without human intervention.

• Daily Trigger: Scheduled to run automatically at 06:00 AM (Vietnam Time) to capture the previous day's US market close.
• Environment: Sets up Python 3.10 and installs dependencies on the fly.
• Auto-Commit: Push the newly processed CSV data back to the repository for the frontend to consume.