133 lines
5.8 KiB
Python
133 lines
5.8 KiB
Python
"""SQLite log of every signal, decision and fill, plus the built-in paper account."""
|
|
import json
|
|
import sqlite3
|
|
import threading
|
|
from datetime import datetime, timezone
|
|
|
|
_lock = threading.Lock()
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ts TEXT NOT NULL,
|
|
symbol TEXT, action TEXT, price REAL,
|
|
decision TEXT, -- executed / blocked / error / ignored
|
|
reason TEXT,
|
|
sentiment TEXT, -- JSON from the news filter
|
|
order_info TEXT -- JSON from the broker
|
|
);
|
|
CREATE TABLE IF NOT EXISTS paper_positions (
|
|
symbol TEXT PRIMARY KEY, qty REAL NOT NULL, avg_price REAL NOT NULL, last_price REAL NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS paper_account (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1), cash REAL NOT NULL, start_cash REAL NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS daily_equity (
|
|
day TEXT NOT NULL, broker TEXT NOT NULL, start_equity REAL NOT NULL,
|
|
PRIMARY KEY (day, broker)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS deposits (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT, ts TEXT NOT NULL,
|
|
amount REAL NOT NULL, -- euros; negative = withdrawal
|
|
note TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS equity_snapshots (ts REAL NOT NULL, equity REAL NOT NULL);
|
|
CREATE TABLE IF NOT EXISTS price_log (
|
|
symbol TEXT NOT NULL, ts REAL NOT NULL, price REAL NOT NULL, -- price in euros, per candle
|
|
PRIMARY KEY (symbol, ts)
|
|
);
|
|
"""
|
|
|
|
|
|
def now() -> str:
|
|
return datetime.now(timezone.utc).isoformat(timespec="seconds")
|
|
|
|
|
|
def today() -> str:
|
|
return datetime.now(timezone.utc).date().isoformat()
|
|
|
|
|
|
class Store:
|
|
def __init__(self, path: str, paper_start_cash: float):
|
|
self.conn = sqlite3.connect(path, check_same_thread=False)
|
|
self.conn.row_factory = sqlite3.Row
|
|
with _lock:
|
|
self.conn.executescript(SCHEMA)
|
|
cols = {r[1] for r in self.conn.execute("PRAGMA table_info(events)")}
|
|
if "candle_ts" not in cols: # database made by an older version
|
|
self.conn.execute("ALTER TABLE events ADD COLUMN candle_ts REAL")
|
|
self.conn.execute(
|
|
"INSERT OR IGNORE INTO paper_account (id, cash, start_cash) VALUES (1, ?, ?)",
|
|
(paper_start_cash, paper_start_cash),
|
|
)
|
|
self.conn.commit()
|
|
|
|
def q(self, sql: str, args: tuple = ()) -> list[sqlite3.Row]:
|
|
with _lock:
|
|
cur = self.conn.execute(sql, args)
|
|
self.conn.commit()
|
|
return cur.fetchall()
|
|
|
|
def log(self, symbol, action, price, decision, reason, sentiment=None, order_info=None,
|
|
candle_ts=None):
|
|
self.q(
|
|
"INSERT INTO events (ts, symbol, action, price, decision, reason, sentiment, order_info, candle_ts) "
|
|
"VALUES (?,?,?,?,?,?,?,?,?)",
|
|
(now(), symbol, action, price, decision, reason,
|
|
json.dumps(sentiment) if sentiment else None,
|
|
json.dumps(order_info, default=str) if order_info else None,
|
|
candle_ts if candle_ts is not None else datetime.now(timezone.utc).timestamp()),
|
|
)
|
|
|
|
def log_price(self, symbol: str, ts: float, price: float):
|
|
self.q("INSERT OR REPLACE INTO price_log (symbol, ts, price) VALUES (?,?,?)", (symbol, ts, price))
|
|
|
|
def log_equity(self, ts: float, equity: float):
|
|
self.q("INSERT INTO equity_snapshots (ts, equity) VALUES (?,?)", (ts, equity))
|
|
|
|
def start_cash(self) -> float:
|
|
return self.q("SELECT start_cash FROM paper_account WHERE id=1")[0]["start_cash"]
|
|
|
|
def deposits(self) -> list[dict]:
|
|
return [dict(r) for r in self.q("SELECT id, ts, amount, note FROM deposits ORDER BY id DESC")]
|
|
|
|
def invested(self) -> float:
|
|
"""All money put in: the starting balance plus every deposit, minus withdrawals."""
|
|
return self.start_cash() + self.q("SELECT COALESCE(SUM(amount), 0) AS t FROM deposits")[0]["t"]
|
|
|
|
def add_deposit(self, amount: float, note: str = "", to_paper: bool = True) -> dict:
|
|
"""Record money added (or taken out, if negative). With to_paper the built-in account's
|
|
cash changes too. Today's loss-limit baseline moves with it, so a withdrawal is not
|
|
mistaken for a trading loss."""
|
|
if to_paper:
|
|
cash = self.q("SELECT cash FROM paper_account WHERE id=1")[0]["cash"]
|
|
if cash + amount < 0:
|
|
raise ValueError(f"only €{cash:.2f} of cash is available to withdraw")
|
|
self.q("UPDATE paper_account SET cash = cash + ? WHERE id=1", (amount,))
|
|
self.q("UPDATE daily_equity SET start_equity = start_equity + ? WHERE day=? AND broker='paper'",
|
|
(amount, today()))
|
|
self.q("INSERT INTO deposits (ts, amount, note) VALUES (?,?,?)", (now(), amount, note[:120]))
|
|
return {"amount": amount, "invested": round(self.invested(), 2)}
|
|
|
|
def clear_history(self):
|
|
for t in ("paper_positions", "events", "daily_equity", "equity_snapshots", "price_log", "deposits"):
|
|
self.q(f"DELETE FROM {t}")
|
|
self.q("UPDATE paper_account SET cash = start_cash")
|
|
|
|
def events(self, limit: int = 100) -> list[dict]:
|
|
rows = self.q("SELECT * FROM events ORDER BY id DESC LIMIT ?", (limit,))
|
|
out = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
for k in ("sentiment", "order_info"):
|
|
d[k] = json.loads(d[k]) if d[k] else None
|
|
out.append(d)
|
|
return out
|
|
|
|
def day_start_equity(self, broker: str, current_equity: float) -> float:
|
|
"""Equity at the first check of the day (UTC); used for the daily loss limit."""
|
|
self.q("INSERT OR IGNORE INTO daily_equity (day, broker, start_equity) VALUES (?,?,?)",
|
|
(today(), broker, current_equity))
|
|
return self.q("SELECT start_equity FROM daily_equity WHERE day=? AND broker=?",
|
|
(today(), broker))[0]["start_equity"]
|