Le pipeline complet avec CTE

Partie 3 · ★★★

Reproduis la chaîne du cours : prix → rendements → moyenne locale → signal → rendement de stratégie → performance cumulée. Les trois premières CTE sont fournies (returns, ma, signal). Complète la CTE strategy (strategy_ret = signal * ret) puis la requête finale qui renvoie, pour chaque actif, la performance cumulée à la dernière date (2024-01-06), colonnes ticker, cum, triées par ticker.

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE prices(date TEXT, ticker TEXT, close REAL)")
A = [100, 102, 101, 105, 107, 108]
B = [50, 49, 51, 50, 52, 55]
C = [80, 90, 83, 110, 79, 120]
for i in range(6):
    d = f"2024-01-0{i + 1}"
    for t, s in (("A", A), ("B", B), ("C", C)):
        con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, t, s[i]))

sql = """
WITH returns AS (
  SELECT *, close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) AS ret
  FROM prices
),
ma AS (
  SELECT *, AVG(close) OVER (PARTITION BY ticker ORDER BY date
                             ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5
  FROM returns
),
signal AS (
  SELECT *, CASE WHEN close > ma5 THEN 1 ELSE 0 END AS signal
  FROM ma
),
strategy AS (
  SELECT *, ___ * ret AS strategy_ret
  FROM signal
),
cumul AS (
  SELECT *, SUM(strategy_ret) OVER (PARTITION BY ticker ORDER BY date
                                    ROWS BETWEEN ___ AND CURRENT ROW) AS cum
  FROM strategy
)
SELECT ticker, cum FROM cumul WHERE date = '2024-01-06' ORDER BY ticker
"""
rows = con.execute(sql).fetchall()

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE prices(date TEXT, ticker TEXT, close REAL)")
A = [100, 102, 101, 105, 107, 108]
B = [50, 49, 51, 50, 52, 55]
C = [80, 90, 83, 110, 79, 120]
for i in range(6):
    d = f"2024-01-0{i + 1}"
    for t, s in (("A", A), ("B", B), ("C", C)):
        con.execute("INSERT INTO prices VALUES (?, ?, ?)", (d, t, s[i]))

sql = """
WITH returns AS (
  SELECT *, close - LAG(close) OVER (PARTITION BY ticker ORDER BY date) AS ret
  FROM prices
),
ma AS (
  SELECT *, AVG(close) OVER (PARTITION BY ticker ORDER BY date
                             ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5
  FROM returns
),
signal AS (
  SELECT *, CASE WHEN close > ma5 THEN 1 ELSE 0 END AS signal
  FROM ma
),
strategy AS (
  SELECT *, ___ * ret AS strategy_ret
  FROM signal
),
cumul AS (
  SELECT *, SUM(strategy_ret) OVER (PARTITION BY ticker ORDER BY date
                                    ROWS BETWEEN ___ AND CURRENT ROW) AS cum
  FROM strategy
)
SELECT ticker, cum FROM cumul WHERE date = '2024-01-06' ORDER BY ticker
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Exercice suivant → Croisement de deux moyennes