US Oil Stocks vs EIA Energy Data correlation

[kchandan]

📐 Architecture Diagram

          ┌───────────────────────────┐
          │       Data Sources        │
          └────────────┬──────────────┘
                       │
   ┌───────────────────┼────────────────────┐
   │                   │                    │
Stocks (Yahoo Finance) │   Energy Prices    │
 CSV (XOM, CVX, COP)   │  (EIA API / Excel) │
                       │  WTI, Natural Gas  │
   └─────────────┬─────┘                    │
                 │                          │
         ┌───────▼──────────────────────────▼───────┐
         │        Databricks Notebook (ETL)         │
         │------------------------------------------│
         │ - Fetch stock data (CSV)                 │
         │ - Fetch energy data (JSON / Excel)       │
         │ - Normalize schema (date, values)        │
         │ - Handle missing weekends/holidays       │
         │ - Join stock & energy tables on date     │
         └───────┬─────────────────────────────────┘
                 │
                 ▼
        ┌─────────────────────┐
        │ Databricks Delta    │
        │   Managed Tables    │
        └─────────┬───────────┘
                  │
        ┌─────────▼──────────────────┐
        │   Analysis & Correlation   │
        │----------------------------│
        │ - Compute returns (stocks) │
        │ - Compute daily deltas     │
        │ - Correlation (Pearson)    │
        │ - Aggregate weekly/monthly │
        └─────────┬──────────────────┘
                  │
     ┌────────────▼─────────────┐
     │  Visualization Layer     │
     │--------------------------│
     │ - Line charts (Stock vs  │
     │   Oil/NatGas prices)     │
     │ - Scatter plot (Returns) │
     │ - Heatmap (Correlations) │
     └──────────────────────────┘

---

🔹 How the Flow Works

1. Data Sources

   • Stock data from Yahoo Finance (CSV download).
   • Energy prices from EIA API (JSON) or EIA XLS reports.

2. Databricks Notebook (Python)

   • Ingests both datasets.
   • Standardizes dates & formats.
   • Resamples to daily/weekly granularity.
   • Joins stock & energy data on date.

3. Delta Tables

   • stocks_raw → raw daily stock prices.
   • energy_raw → raw oil/natural gas prices.
   • finance_energy_corr → correlation results.

4. Analysis

   • Calculate daily returns for stocks.
   • Compare with daily % change in oil/natgas prices.
   • Run Pearson correlation for each stock vs commodity.

5. Visualization

   • Time-series line plots (stock vs oil).
   • Scatter plots (daily change vs stock returns).
   • Correlation heatmap across all companies.