tradetally/calculate_r_value.sql at main · ryansavara/tradetally · GitHub

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
-- Calculate and set R-Value for trade with stopLoss in executions
-- Trade ID: 1d652fad-d7af-4d21-9ce2-4dc5df2f1731

\echo 'Calculating R-Value for trade 1d652fad-d7af-4d21-9ce2-4dc5df2f1731...'
\echo ''

-- Calculate weighted averages from executions
WITH execution_data AS (
  SELECT
    t.id,
    t.symbol,
    t.side,
    t.entry_price,
    t.exit_price,
    t.stop_loss as trade_level_stop_loss,
    jsonb_array_elements(t.executions) as execution
  FROM trades t
  WHERE t.id = '1d652fad-d7af-4d21-9ce2-4dc5df2f1731'
),
executions_with_stop_loss AS (
  SELECT
    id,
    symbol,
    side,
    entry_price,
    exit_price,
    trade_level_stop_loss,
    (execution->>'stopLoss')::numeric as exec_stop_loss,
    (execution->>'entryPrice')::numeric as exec_entry_price,
    (execution->>'exitPrice')::numeric as exec_exit_price,
    (execution->>'quantity')::numeric as exec_quantity
  FROM execution_data
  WHERE execution->>'stopLoss' IS NOT NULL
),
weighted_averages AS (
  SELECT
    id,
    symbol,
    side,
    SUM(exec_quantity) as total_quantity,
    SUM(exec_entry_price * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_entry_price,
    SUM(exec_stop_loss * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_stop_loss,
    SUM(exec_exit_price * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_exit_price
  FROM executions_with_stop_loss
  GROUP BY id, symbol, side
),
r_value_calculation AS (
  SELECT
    id,
    symbol,
    side,
    weighted_entry_price,
    weighted_stop_loss,
    weighted_exit_price,
    total_quantity,
    CASE
      -- For long positions: R = (exit - entry) / (entry - stop)
      WHEN side = 'long' THEN
        (weighted_exit_price - weighted_entry_price) / NULLIF(ABS(weighted_entry_price - weighted_stop_loss), 0)
      -- For short positions: R = (entry - exit) / (stop - entry)
      WHEN side = 'short' THEN
        (weighted_entry_price - weighted_exit_price) / NULLIF(ABS(weighted_stop_loss - weighted_entry_price), 0)
      ELSE NULL
    END as calculated_r_value
  FROM weighted_averages
)
-- Display calculation details
SELECT
  symbol,
  side,
  total_quantity as "Total Qty",
  ROUND(weighted_entry_price::numeric, 2) as "Avg Entry",
  ROUND(weighted_stop_loss::numeric, 2) as "Avg Stop Loss",
  ROUND(weighted_exit_price::numeric, 2) as "Avg Exit",
  ROUND(calculated_r_value::numeric, 3) as "R-Value"
FROM r_value_calculation;

\echo ''
\echo 'Updating R-Value in database...'

-- Update the trade with calculated R-Value
WITH execution_data AS (
  SELECT
    t.id,
    t.side,
    jsonb_array_elements(t.executions) as execution
  FROM trades t
  WHERE t.id = '1d652fad-d7af-4d21-9ce2-4dc5df2f1731'
),
executions_with_stop_loss AS (
  SELECT
    id,
    side,
    (execution->>'stopLoss')::numeric as exec_stop_loss,
    (execution->>'entryPrice')::numeric as exec_entry_price,
    (execution->>'exitPrice')::numeric as exec_exit_price,
    (execution->>'quantity')::numeric as exec_quantity
  FROM execution_data
  WHERE execution->>'stopLoss' IS NOT NULL
),
weighted_averages AS (
  SELECT
    id,
    side,
    SUM(exec_entry_price * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_entry_price,
    SUM(exec_stop_loss * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_stop_loss,
    SUM(exec_exit_price * exec_quantity) / NULLIF(SUM(exec_quantity), 0) as weighted_exit_price
  FROM executions_with_stop_loss
  GROUP BY id, side
),
r_value_calculation AS (
  SELECT
    id,
    CASE
      WHEN side = 'long' THEN
        (weighted_exit_price - weighted_entry_price) / NULLIF(ABS(weighted_entry_price - weighted_stop_loss), 0)
      WHEN side = 'short' THEN
        (weighted_entry_price - weighted_exit_price) / NULLIF(ABS(weighted_stop_loss - weighted_entry_price), 0)
      ELSE NULL
    END as calculated_r_value
  FROM weighted_averages
)
UPDATE trades
SET r_value = r.calculated_r_value,
    updated_at = NOW()
FROM r_value_calculation r
WHERE trades.id = r.id
RETURNING trades.symbol, ROUND(trades.r_value::numeric, 3) || 'R' as "Updated R-Value";

\echo ''
\echo 'Done! R-Value has been calculated and saved.'