I calculate SMA(10) using last 9 bars Close + current Close. I have done it in Google Sheet and this is the correct values I also get in my trading platform.
Date Time Open High Low Close Volume SMA_Close10 SMA_Close20
2022-01-02 18:01:00 17535.75 17601 17535.75 17564.25 1024 #REF! #REF!
2022-01-02 18:02:00 17562.25 17578.5 17560.75 17573.25 610 #REF! #REF!
2022-01-02 18:03:00 17573.25 17579.25 17566 17566.75 347 #REF! #REF!
2022-01-02 18:04:00 17566.5 17568.75 17563 17565 206 #REF! #REF!
2022-01-02 18:05:00 17564.25 17567.75 17558.5 17565 257 #REF! #REF!
2022-01-02 18:06:00 17565 17570.5 17563.25 17567.5 283 #REF! #REF!
2022-01-02 18:07:00 17567.25 17573.75 17566.75 17573 141 #REF! #REF!
2022-01-02 18:08:00 17573.25 17574.25 17567 17568.25 209 #REF! #REF!
2022-01-02 18:09:00 17568.5 17571.5 17564.25 17566.25 261 17567.69 #REF!
2022-01-02 18:10:00 17565 17565 17552.5 17557 342 17566.63 #REF!
2022-01-02 18:11:00 17557.5 17558.5 17555 17557 83 17565.90 #REF!
2022-01-02 18:12:00 17557 17562 17556 17559.25 162 17564.50 #REF!
2022-01-02 18:13:00 17558.5 17559.75 17556.75 17558.25 56 17563.65 #REF!
2022-01-02 18:14:00 17558.5 17558.75 17552.25 17554.25 160 17562.58 #REF!
2022-01-02 18:15:00 17555.25 17555.25 17552.25 17552.25 68 17561.30 #REF!
2022-01-02 18:16:00 17552 17559.75 17551.5 17559.5 103 17560.50 #REF!
2022-01-02 18:17:00 17558.75 17560.25 17556.75 17559.5 122 17559.15 #REF!
2022-01-02 18:18:00 17559.75 17564.5 17559.75 17564 179 17558.73 #REF!
2022-01-02 18:19:00 17563.75 17564.25 17561.75 17564 50 17558.50 17562.86
2022-01-02 18:20:00 17564.25 17565.25 17561.5 17562 70 17559.00 17562.81
2022-01-02 18:21:00 17562.5 17562.5 17560.5 17561.5 59 17559.45 17562.68
2022-01-02 18:22:00 17561.5 17563 17556.75 17562.25 112 17559.75 17562.13
2022-01-02 18:23:00 17562 17567.75 17562 17563.25 168 17560.25 17561.95
2022-01-02 18:24:00 17564 17564 17559 17559.75 90 17560.80 17561.69
2022-01-02 18:25:00 17560 17560.25 17557.25 17558.75 86 17561.45 17561.38
2022-01-02 18:26:00 17558.5 17558.5 17550.5 17551.5 186 17560.65 17560.58
2022-01-02 18:27:00 17550.75 17556 17550.75 17555.5 76 17560.25 17559.70
2022-01-02 18:28:00 17555.5 17556.5 17554 17554 38 17559.25 17558.99
2022-01-02 18:29:00 17554.5 17558.75 17551.5 17558.25 85 17558.68 17558.59
2022-01-02 18:30:00 17558.5 17560.5 17557.25 17559.75 73 17558.45 17558.73
2022-01-02 18:31:00 17560.25 17561 17554.75 17556 123 17557.90 17558.68
2022-01-02 18:32:00 17556.5 17556.75 17554 17555.25 70 17557.20 17558.48
2022-01-02 18:33:00 17555 17555 17549 17550 108 17555.88 17558.06
2022-01-02 18:34:00 17551 17552.25 17549.75 17551.25 72 17555.03 17557.91
2022-01-02 18:35:00 17552.25 17554.75 17552.25 17554 79 17554.55 17558.00
2022-01-02 18:36:00 17554.5 17554.75 17551.25 17551.5 46 17554.55 17557.60
2022-01-02 18:37:00 17551.5 17555.75 17551.5 17555 41 17554.50 17557.38
2022-01-02 18:38:00 17554.5 17557.25 17552.75 17553.5 76 17554.45 17556.85
2022-01-02 18:39:00 17553.5 17557 17553 17555.5 64 17554.18 17556.43
2022-01-02 18:40:00 17555.75 17557.25 17555.25 17556.5 35 17553.85 17556.15
2022-01-02 18:41:00 17556.25 17556.25 17552.25 17555 74 17553.75 17555.83
2022-01-02 18:42:00 17555.25 17556 17554.75 17556 25 17553.83 17555.51
2022-01-02 18:43:00 17555.75 17555.75 17553.5 17553.75 34 17554.20 17555.04
2022-01-02 18:44:00 17554.5 17557.25 17554.5 17556.5 41 17554.73 17554.88
2022-01-02 18:45:00 17556.75 17557.75 17556.25 17557 32 17555.03 17554.79
2022-01-02 18:46:00 17557 17561.25 17556 17556.75 119 17555.55 17555.05
2022-01-02 18:47:00 17556.25 17559.25 17555.25 17559.25 61 17555.98 17555.24
2022-01-02 18:48:00 17558.5 17560 17558 17559 49 17556.53 17555.49
2022-01-02 18:49:00 17559 17559.75 17558 17559.75 27 17556.95 17555.56
2022-01-02 18:50:00 17559.5 17563 17559.5 17561.75 148 17557.48 17555.66
2022-01-02 18:51:00 17561.75 17562.5 17559.5 17559.75 44 17557.95 17555.85
2022-01-02 18:52:00 17559.5 17561.25 17559 17561.25 39 17558.48 17556.15
2022-01-02 18:53:00 17561 17563 17561 17562.5 51 17559.35 17556.78
2022-01-02 18:54:00 17563.25 17564.5 17562.5 17564.25 74 17560.13 17557.43
2022-01-02 18:55:00 17564 17564.75 17563.5 17564 27 17560.83 17557.93
2022-01-02 18:56:00 17563.75 17563.75 17560.75 17562.75 98 17561.43 17558.49
2022-01-02 18:57:00 17562.25 17563.5 17562.25 17563 29 17561.80 17558.89
2022-01-02 18:58:00 17563 17563 17561 17561.75 37 17562.08 17559.30
2022-01-02 18:59:00 17562.25 17567.75 17562.25 17566.5 126 17562.75 17559.85
2022-01-02 19:00:00 17565.5 17566.25 17563.25 17563.5 56 17562.93 17560.20
2022-01-02 19:01:00 17564 17570.75 17564 17564.75 155 17563.43 17560.69
2022-01-02 19:02:00 17565.5 17568.5 17565.5 17566.75 69 17563.98 17561.23
2022-01-02 19:03:00 17567.75 17570.25 17566.75 17568.5 103 17564.58 17561.96
2022-01-02 19:04:00 17568.25 17573 17567 17569 109 17565.05 17562.59
2022-01-02 19:05:00 17569.25 17570.5 17567 17568.25 75 17565.48 17563.15
2022-01-02 19:06:00 17567.5 17571 17567.5 17568.5 55 17566.05 17563.74
2022-01-02 19:07:00 17569 17570.5 17569 17570 33 17566.75 17564.28
2022-01-02 19:08:00 17570 17570.25 17567 17568.5 48 17567.43 17564.75
2022-01-02 19:09:00 17567.5 17567.5 17566 17566.75 66 17567.45 17565.10
2022-01-02 19:10:00 17566.75 17571 17566.75 17571 79 17568.20 17565.56
2022-01-02 19:11:00 17571 17572 17569.75 17572 95 17568.93 17566.18
2022-01-02 19:12:00 17571.75 17572.75 17570.25 17571 75 17569.35 17566.66
2022-01-02 19:13:00 17570.5 17570.5 17566.75 17566.75 93 17569.18 17566.88
2022-01-02 19:14:00 17567.5 17567.75 17565.5 17566.5 38 17568.93 17566.99
2022-01-02 19:15:00 17566.75 17567.25 17564.25 17565 68 17568.60 17567.04
2022-01-02 19:16:00 17565.25 17569.5 17564 17568.5 109 17568.60 17567.33
WITH Calculations AS (
SELECT
Date,
Time,
Close,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY Date, Time) >= 10 THEN
AVG(Close) OVER (ORDER BY Date, Time ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
ELSE NULL
END AS SMA_Close10,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY Date, Time) >= 20 THEN
AVG(Close) OVER (ORDER BY Date, Time ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
ELSE NULL
END AS SMA_Close20
FROM
NQ_06_24_1_Minute
)
UPDATE NQ_06_24_1_Minute
SET
SMA_Close10 = CASE WHEN Calculations.SMA_Close10 IS NULL THEN 0 ELSE Calculations.SMA_Close10 END,
SMA_Close20 = CASE WHEN Calculations.SMA_Close20 IS NULL THEN 0 ELSE Calculations.SMA_Close20 END
FROM
Calculations
WHERE
NQ_06_24_1_Minute.Date = Calculations.Date
AND NQ_06_24_1_Minute.Time = Calculations.Time;
What do I need to fix on the code to calculate skip the first rows (only when I have enough data) I should start calculate SMA_Close10 and SMA_Close20?
