""" convert_utc_to_eet.py ====================== Converts price CSV files from UTC to EET (Eastern European Time). EET = UTC+2 (winter standard time) EEST = UTC+3 (summer daylight saving time) Python's pytz handles the DST transition automatically using the Europe/Helsinki timezone (identical to Europe/Athens for our purposes). Files processed: data/raw/prices/GBPUSD/GBPUSD_15M_2003_2026.csv data/raw/prices/GBPUSD/GBPUSD_1H_2003_2026.csv data/raw/prices/USDJPY/USDJPY_15M_2003_2026.csv data/raw/prices/USDJPY/USDJPY_1H_2003_2026.csv data/raw/prices/AUDUSD/AUDUSD_15M_2003_2026.csv data/raw/prices/AUDUSD/AUDUSD_1H_2003_2026.csv Output: Same files overwritten with 'datetime' column in EET. Original UTC column ('Time (UTC)' or similar) is replaced. Place this file in: C:\\Users\\paul_\\OneDrive\\fx_macro_intraday\\src\\ingestion\\convert_utc_to_eet.py Run from project root (ONCE, before multi_pair_signals_v1.py): python src/ingestion/convert_utc_to_eet.py """ import pandas as pd from pathlib import Path import sys try: import pytz HAS_PYTZ = True except ImportError: HAS_PYTZ = False BASE_PATH = Path(__file__).resolve().parents[2] PRICES_DIR = BASE_PATH / "data" / "raw" / "prices" # Pairs and timeframes to convert PAIRS = ["GBPUSD", "USDJPY", "AUDUSD"] TIMEFRAMES = ["15M", "1H"] EET_TZ = "Europe/Helsinki" # UTC+2/UTC+3 with correct DST transitions def detect_datetime_col(df: pd.DataFrame) -> str: """Finds the datetime column regardless of broker naming.""" df.columns = [c.strip() for c in df.columns] for candidate in ["Time (UTC)", "Time (EET)", "datetime", "time", "Date", "Timestamp", "Time(UTC)", "Time(EET)"]: if candidate in df.columns: return candidate # Fallback: first column return df.columns[0] def convert_file(path: Path, is_utc: bool) -> bool: """ Reads a price CSV, converts datetime column to EET, saves back. Parameters ---------- path : Path to the CSV file is_utc : If True, convert from UTC to EET. If False (already EET), just standardise column name. Returns True if successful. """ if not path.exists(): print(f" [SKIP] File not found: {path.name}") return False try: df = pd.read_csv(path) # Strip all column whitespace df.columns = [c.strip() for c in df.columns] dt_col = detect_datetime_col(df) print(f" Detected datetime column: '{dt_col}'") # Parse the datetime column # Handle dot-format: 2003.05.05 00:00:00 raw_dt = df[dt_col].astype(str).str.replace( r"(\d{4})\.(\d{2})\.(\d{2})", r"\1-\2-\3", regex=True ) parsed = pd.to_datetime(raw_dt, format="mixed", dayfirst=False) if is_utc: if HAS_PYTZ: # Proper DST-aware conversion utc_tz = pytz.utc eet_tz = pytz.timezone(EET_TZ) # Localise to UTC then convert to EET parsed_utc = parsed.dt.tz_localize(utc_tz, ambiguous="infer", nonexistent="shift_forward") parsed_eet = parsed_utc.dt.tz_convert(eet_tz) # Strip timezone info (store as naive EET) df["datetime"] = parsed_eet.dt.tz_localize(None) else: # Fallback: simple +2h fixed offset (slightly wrong during DST) # This is acceptable for backtesting purposes print(f" [NOTE] pytz not available — using fixed UTC+2 offset") df["datetime"] = parsed + pd.Timedelta(hours=2) else: # Already EET — just standardise column name df["datetime"] = parsed # Remove the original time column if it differs from 'datetime' if dt_col != "datetime" and dt_col in df.columns: df = df.drop(columns=[dt_col]) # Standardise remaining column names rename_map = {} for col in df.columns: if col == "datetime": continue cl = col.lower() if cl.startswith("open"): rename_map[col] = "open" elif cl.startswith("high"): rename_map[col] = "high" elif cl.startswith("low"): rename_map[col] = "low" elif cl.startswith("close"): rename_map[col] = "close" elif cl.startswith("vol"): rename_map[col] = "volume" if rename_map: df = df.rename(columns=rename_map) # Keep only standard columns keep = [c for c in ["datetime", "open", "high", "low", "close", "volume"] if c in df.columns] df = df[keep] # Sort by datetime df = df.sort_values("datetime").reset_index(drop=True) # Save back — format datetime as string matching EURUSD format df["datetime"] = df["datetime"].dt.strftime("%Y.%m.%d %H:%M:%S") df.to_csv(path, index=False) print(f" ✓ Saved {path.name} " f"({len(df):,} rows | " f"{df['datetime'].iloc[0]} → {df['datetime'].iloc[-1]})") return True except Exception as e: print(f" [ERROR] {path.name}: {e}") import traceback traceback.print_exc() return False def check_eurusd_timezone(): """ Checks the EURUSD file timezone to confirm EET baseline. London open is 08:00 EET. If EURUSD has bars at 07:00 and 08:00, the timezone is correct. """ eurusd_path = PRICES_DIR / "EURUSD" / "EURUSD_1H_2003_2026.csv" if not eurusd_path.exists(): return df = pd.read_csv(eurusd_path, nrows=200) df.columns = [c.strip().lower() for c in df.columns] dt_col = "datetime" if "datetime" in df.columns else df.columns[0] df[dt_col] = pd.to_datetime( df[dt_col].astype(str).str.replace( r"(\d{4})\.(\d{2})\.(\d{2})", r"\1-\2-\3", regex=True ), format="mixed", dayfirst=False, ) hours = df[dt_col].dt.hour.value_counts().sort_index() print(f"\n EURUSD 1H hour distribution (first 200 rows):") print(f" {dict(hours)}") print(f" First bar: {df[dt_col].iloc[0]}") def main(): print("=" * 60) print("UTC → EET CONVERTER") print(f" Timezone: {EET_TZ} (UTC+2 winter / UTC+3 summer DST)") print(f" pytz available: {HAS_PYTZ}") print("=" * 60) # Install pytz if not available if not HAS_PYTZ: print("\n Installing pytz for proper DST handling...") import subprocess subprocess.run([sys.executable, "-m", "pip", "install", "pytz"], capture_output=True) try: import pytz as _pytz globals()["pytz"] = _pytz globals()["HAS_PYTZ"] = True print(" pytz installed successfully.") except ImportError: print(" pytz installation failed — using fixed UTC+2 offset.") # Check EURUSD timezone as reference print("\nChecking EURUSD timezone as reference...") check_eurusd_timezone() print(f"\nConverting new pair files from UTC to EET...") results = {} for pair in PAIRS: print(f"\n {pair}:") for tf in TIMEFRAMES: filename = f"{pair}_{tf}_2003_2026.csv" path = PRICES_DIR / pair / filename print(f"\n {tf}:") ok = convert_file(path, is_utc=True) results[f"{pair}_{tf}"] = ok # Summary print(f"\n{'='*60}") print("SUMMARY") print(f"{'='*60}") passed = sum(results.values()) failed = len(results) - passed print(f" Converted : {passed}/{len(results)}") print(f" Failed : {failed}/{len(results)}") if failed == 0: print(f"\n All files converted successfully.") print(f" Next step: run src/research/multi_pair_signals_v1.py") else: print(f"\n Some files failed. Check errors above.") for key, ok in results.items(): if not ok: print(f" {key}") if __name__ == "__main__": main()