script to execute bids

1 files changed, 112 insertions, 0 deletions

diff --git a/server/services/process_bids.py b/server/services/process_bids.py
new file mode 100644
index 0000000..7e734f1
--- /dev/null
+++ b/server/services/process_bids.py
@@ -0,0 +1,112 @@
+from datetime import datetime, timedelta
+from sqlalchemy.orm import Session
+from db import SessionLocal
+from models.bid import Bid
+from models.auth import User
+from gridstatus import ISONE
+from zoneinfo import ZoneInfo
+
+NEW_ENGLAND_TZ = ZoneInfo("America/New_York")
+iso = ISONE()
+
+def get_day_ahead_price(target_time: datetime) -> float:
+    """Fetch the Day Ahead clearing price for the hour of target_time."""
+    df = iso.get_lmp(date=target_time.date(), market="DAY_AHEAD_HOURLY", locations="ALL")
+    df = df.groupby("Interval Start")["LMP"].mean().reset_index()
+
+    for _, row in df.iterrows():
+        if abs(row["Interval Start"] - target_time.replace(minute=0, second=0, microsecond=0)) < timedelta(minutes=30):
+            return row["LMP"]
+
+    raise ValueError(f"No day ahead price found for {target_time}")
+
+def get_real_time_prices(target_time: datetime) -> list[float]:
+    """Fetch the Real Time 5-min prices during the hour of target_time."""
+    df = iso.get_lmp(date=target_time.date(), market="REAL_TIME_5_MIN", locations="ALL")
+    df = df.groupby("Interval Start")["LMP"].mean().reset_index()
+
+    start_of_hour = target_time.replace(minute=0, second=0, microsecond=0)
+    end_of_hour = start_of_hour + timedelta(hours=1)
+
+    prices = [
+        row["LMP"]
+        for _, row in df.iterrows()
+        if start_of_hour <= row["Interval Start"] < end_of_hour
+    ]
+
+    return prices
+
+def process_bids():
+    db: Session = SessionLocal()
+
+    now = datetime.now(NEW_ENGLAND_TZ)
+
+    # Step 1: Process Submitted bids
+    submitted_bids = db.query(Bid).filter(Bid.status == "Submitted").all()
+    for bid in submitted_bids:
+        try:
+            target_time = bid.timestamp.astimezone(NEW_ENGLAND_TZ)
+
+            # Only process bids whose timestamp is already in the past
+            if target_time > now:
+                print(f"Skipping future bid {bid.id} at {target_time}")
+                continue
+
+            print(f"Processing submitted bid {bid.id} for {target_time}")
+
+            day_ahead_price = get_day_ahead_price(target_time)
+
+            if bid.price >= day_ahead_price:
+                # Bid was accepted
+                bid.status = "Executed"
+                bid.pnl = None  # Will be calculated later
+                print(f"Bid {bid.id}: Executed (waiting for real-time PnL)")
+            else:
+                # Bid rejected
+                bid.status = "Fail"
+                bid.pnl = None
+                print(f"Bid {bid.id}: Failed (price too low)")
+
+            db.commit()
+
+        except Exception as e:
+            print(f"Error processing submitted bid {bid.id}: {e}")
+
+    # Step 2: Process Executed bids (calculate PnL)
+    executed_bids = db.query(Bid).filter(Bid.status == "Executed").all()
+    for bid in executed_bids:
+        try:
+            target_time = bid.timestamp.astimezone(NEW_ENGLAND_TZ)
+
+            # Only settle if the whole real-time period is over
+            if now < target_time + timedelta(hours=1):
+                print(f"Skipping bid {bid.id}, real-time window not complete yet")
+                continue
+
+            print(f"Settling executed bid {bid.id} for {target_time}")
+
+            real_time_prices = get_real_time_prices(target_time)
+
+            if not real_time_prices:
+                print(f"No real-time prices available for bid {bid.id} yet")
+                continue  # Retry later
+
+            avg_real_time_price = sum(real_time_prices) / len(real_time_prices)
+
+            day_ahead_price = get_day_ahead_price(target_time)
+
+            pnl = (day_ahead_price - avg_real_time_price) * bid.quantity
+
+            bid.status = "Settled"
+            bid.pnl = pnl
+            print(f"Bid {bid.id} Settled | PnL: ${pnl:.2f}")
+
+            db.commit()
+
+        except Exception as e:
+            print(f"Error settling executed bid {bid.id}: {e}")
+
+    db.close()
+
+if __name__ == "__main__":
+    process_bids()