Repository navigation
Expand file tree
/
Copy pathverify_trades.py
More file actions
307 lines (266 loc) · 12.2 KB
/
Copy pathverify_trades.py
File metadata and controls
307 lines (266 loc) · 12.2 KB
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
"""
One-time verification script to audit historical trades against independent price data.
Reads the SQLite database, fetches yfinance prices, compares, and reports discrepancies.
Run via: python verify_trades.py [path_to_db]
"""
import sqlite3
import sys
import os
import yaml
from datetime import datetime, timedelta
import json
try:
import yfinance as yf
HAS_YFINANCE = True
except ImportError:
HAS_YFINANCE = False
print("WARNING: yfinance not installed. Install with: pip install yfinance")
try:
import pandas as pd
except ImportError:
print("ERROR: pandas not installed. Install with: pip install pandas")
sys.exit(1)
def load_config():
base_dir = os.path.dirname(os.path.abspath(__file__))
config_path = os.path.join(base_dir, "config.yaml")
with open(config_path, "r") as f:
return yaml.safe_load(f) or {}
def get_db_path(config):
db_rel = config["data"]["cache_path"]
if os.path.isabs(db_rel):
return db_rel
base_dir = os.path.dirname(os.path.abspath(__file__))
return os.path.join(base_dir, db_rel)
def fetch_prices_yfinance(symbol, start_date, end_date):
if not HAS_YFINANCE:
return None
try:
ticker = yf.Ticker(symbol)
df = ticker.history(start=start_date, end=end_date, auto_adjust=False)
if df.empty:
return None
return df
except Exception as e:
print(f" WARNING: yfinance fetch failed for {symbol}: {e}")
return None
def get_market_price(symbol, date_str, price_type="Open"):
if not HAS_YFINANCE:
return None
try:
dt = datetime.strptime(date_str, "%Y-%m-%d")
start = dt - timedelta(days=5)
end = dt + timedelta(days=2)
df = fetch_prices_yfinance(symbol, start.strftime("%Y-%m-%d"), end.strftime("%Y-%m-%d"))
if df is None or df.empty:
return None
target = dt.date()
for idx in range(len(df)):
row_date = df.index[idx].date()
if row_date >= target:
return float(df.iloc[idx][price_type])
return float(df.iloc[-1][price_type])
except Exception as e:
print(f" WARNING: Failed to get price for {symbol} on {date_str}: {e}")
return None
def compute_total_pnl_dollars(conn, table_name):
rows = conn.execute(
f"SELECT side, entry_price, exit_price, quantity FROM {table_name} WHERE status='CLOSED'"
).fetchall()
total = 0.0
for side, entry_price, exit_price, qty in rows:
try:
entry = float(entry_price or 0.0)
exit_ = float(exit_price or 0.0)
q = float(qty or 0.0)
except Exception:
continue
s = str(side or "LONG").strip().upper()
if s == "SHORT":
total += (entry - exit_) * q
else:
total += (exit_ - entry) * q
return float(total)
def verify_trades(db_path, output_path=None):
if not os.path.exists(db_path):
print(f"ERROR: Database not found: {db_path}")
return False
conn = sqlite3.connect(db_path)
tables = ["positions_ai", "positions"]
all_results = []
total_discrepancies = 0
grand_actual_pnl = 0.0
grand_stored_pnl = 0.0
for table_name in tables:
print(f"\n{'='*60}")
print(f"VERIFYING TABLE: {table_name}")
print(f"{'='*60}")
cursor = conn.cursor()
cursor.execute(f"SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}'")
if not cursor.fetchone():
print(f"Table {table_name} does not exist. Skipping.")
continue
query = f"""
SELECT id, symbol, side, entry_date, entry_price, exit_date,
exit_price, quantity, realized_pnl,
exit_reason, created_at
FROM {table_name}
WHERE status='CLOSED'
ORDER BY exit_date
"""
try:
trades = pd.read_sql(query, conn)
except Exception as e:
print(f"ERROR reading {table_name}: {e}")
continue
if trades.empty:
print(f"No closed trades in {table_name}.")
continue
# Calculate realized_pnl_dollars since it's not stored in the table
trades["realized_pnl_dollars"] = 0.0
for idx, trade in trades.iterrows():
side_str = str(trade["side"] or "LONG").upper()
entry_p = float(trade["entry_price"] or 0.0)
exit_p = float(trade["exit_price"] or 0.0)
qty = float(trade["quantity"] or 0.0)
if side_str == "SHORT":
trades.at[idx, "realized_pnl_dollars"] = (entry_p - exit_p) * qty
else:
trades.at[idx, "realized_pnl_dollars"] = (exit_p - entry_p) * qty
print(f"Found {len(trades)} closed trades")
discrepancies = []
verified_count = 0
stored_total = 0.0
actual_total = 0.0
for _, trade in trades.iterrows():
symbol = trade["symbol"]
side = trade["side"]
entry_date = trade["entry_date"]
exit_date = trade["exit_date"]
stored_entry = float(trade["entry_price"])
stored_exit = float(trade["exit_price"])
qty = float(trade["quantity"])
stored_pnl_pct = float(trade["realized_pnl"])
stored_pnl_dollars = float(trade["realized_pnl_dollars"])
stored_total += stored_pnl_dollars
print(f"\n #{trade['id']} {symbol} {side} | Entry: {entry_date} ${stored_entry:.2f} -> Exit: {exit_date} ${stored_exit:.2f} | P&L: ${stored_pnl_dollars:.2f}")
if HAS_YFINANCE:
actual_entry = get_market_price(symbol, entry_date, "Open")
actual_exit = get_market_price(symbol, exit_date, "Open")
if actual_entry is not None and actual_exit is not None:
if side == "SHORT":
actual_pnl_pct = (actual_entry - actual_exit) / actual_entry
actual_pnl_dollars = (actual_entry - actual_exit) * qty
else:
actual_pnl_pct = (actual_exit - actual_entry) / actual_entry
actual_pnl_dollars = (actual_exit - actual_entry) * qty
actual_total += actual_pnl_dollars
verified_count += 1
entry_diff_pct = abs(stored_entry - actual_entry) / actual_entry if actual_entry else 0
exit_diff_pct = abs(stored_exit - actual_exit) / actual_exit if actual_exit else 0
pnl_diff = abs(stored_pnl_dollars - actual_pnl_dollars)
DISCREPANCY_THRESHOLD = 0.001
has_disc = entry_diff_pct > DISCREPANCY_THRESHOLD or exit_diff_pct > DISCREPANCY_THRESHOLD or pnl_diff > max(abs(actual_pnl_dollars) * 0.01, 0.50)
if has_disc:
discrepancies.append({
"trade_id": int(trade["id"]),
"symbol": symbol,
"side": side,
"entry_date": entry_date,
"exit_date": exit_date,
"stored_entry": stored_entry,
"actual_entry": actual_entry,
"entry_diff_pct": round(entry_diff_pct, 6),
"stored_exit": stored_exit,
"actual_exit": actual_exit,
"exit_diff_pct": round(exit_diff_pct, 6),
"stored_pnl_dollars": round(stored_pnl_dollars, 2),
"actual_pnl_dollars": round(actual_pnl_dollars, 2),
"pnl_diff": round(pnl_diff, 2),
})
print(f" *** DISCREPANCY: entry stored={stored_entry:.2f} actual={actual_entry:.2f} ({entry_diff_pct:.4%}) | exit stored={stored_exit:.2f} actual={actual_exit:.2f} ({exit_diff_pct:.4%}) | P&L stored=${stored_pnl_dollars:.2f} actual=${actual_pnl_dollars:.2f}")
else:
print(f" OK (entry={actual_entry:.2f}, exit={actual_exit:.2f})")
else:
print(f" SKIP: could not fetch yfinance prices")
else:
if side == "SHORT":
expected_pct = (stored_entry - stored_exit) / stored_entry if stored_entry else 0
else:
expected_pct = (stored_exit - stored_entry) / stored_entry if stored_entry else 0
if abs(stored_pnl_pct - expected_pct) > 0.001:
discrepancies.append({
"trade_id": int(trade["id"]),
"symbol": symbol,
"issue": "P&L calculation mismatch",
"stored_pct": stored_pnl_pct,
"expected_pct": expected_pct,
})
print(f" *** DISCREPANCY: stored P&L {stored_pnl_pct:.4%} != expected {expected_pct:.4%}")
else:
print(f" OK (basic check)")
actual_total += stored_pnl_dollars
total_discrepancies += len(discrepancies)
realized_query = f"SELECT side, entry_price, exit_price, quantity FROM {table_name} WHERE status='CLOSED'"
realized_rows = pd.read_sql(realized_query, conn)
if not realized_rows.empty:
long_mask = realized_rows["side"].fillna("LONG").str.upper().eq("LONG")
short_mask = realized_rows["side"].fillna("LONG").str.upper().eq("SHORT")
pnl_long = ((realized_rows["exit_price"] - realized_rows["entry_price"]) * realized_rows["quantity"]).where(long_mask, 0.0)
pnl_short = ((realized_rows["entry_price"] - realized_rows["exit_price"]) * realized_rows["quantity"]).where(short_mask, 0.0)
db_realized_total = float((pnl_long.sum() + pnl_short.sum()) or 0.0)
else:
db_realized_total = 0.0
print(f"\n{'-'*60}")
print(f"SUMMARY for {table_name}:")
print(f" Total closed trades: {len(trades)}")
print(f" Verified against yfinance: {verified_count}")
print(f" Discrepancies found: {len(discrepancies)}")
print(f" Stored realized P&L (from trade rows): ${stored_total:,.2f}")
print(f" Recalculated realized P&L (entry/exit/qty): ${db_realized_total:,.2f}")
if verified_count > 0:
print(f" Actual P&L (yfinance verification): ${actual_total:,.2f}")
print(f" Stored vs Actual diff: ${abs(stored_total - actual_total):,.2f}")
grand_stored_pnl += stored_total
grand_actual_pnl += actual_total
all_results.append({
"table": table_name,
"total_trades": len(trades),
"verified": verified_count,
"discrepancies": len(discrepancies),
"stored_pnl": round(stored_total, 2),
"actual_pnl": round(actual_total, 2),
"details": discrepancies,
})
print(f"\n{'='*60}")
print(f"FINAL VERIFICATION SUMMARY")
print(f"{'='*60}")
print(f"Total discrepancies: {total_discrepancies}")
print(f"Grand stored P&L: ${grand_stored_pnl:,.2f}")
if HAS_YFINANCE:
print(f"Grand actual P&L (yfinance): ${grand_actual_pnl:,.2f}")
print(f"Grand difference: ${abs(grand_stored_pnl - grand_actual_pnl):,.2f}")
print()
if output_path:
with open(output_path, "w") as f:
json.dump({
"timestamp": datetime.now().isoformat(),
"total_discrepancies": total_discrepancies,
"grand_stored_pnl": round(grand_stored_pnl, 2),
"grand_actual_pnl": round(grand_actual_pnl, 2) if HAS_YFINANCE else None,
"results": all_results,
}, f, indent=2)
print(f"Detailed results saved to: {output_path}")
conn.close()
return total_discrepancies == 0
if __name__ == "__main__":
if len(sys.argv) > 1:
db_path = sys.argv[1]
else:
config = load_config()
db_path = get_db_path(config)
output_path = os.path.join(
os.path.dirname(os.path.abspath(__file__)),
"verification_results.json"
)
success = verify_trades(db_path, output_path)
sys.exit(0 if success else 1)