1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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()
|
