Calculate the Average True Range using SQL Server 2014

Question

I would like to calculate the Average True Range using MS SQL Server 2014.

The main table looks like this:

TIME                   BID-OPEN BID-HIGH BID-LOW BID-CLOSE
1993-05-09 21:00:00.000 1.5786  1.5786  1.5311  1.5346
1993-05-10 21:00:00.000 1.5346  1.5551  1.5326  1.5391
1993-05-11 21:00:00.000 1.5391  1.5521  1.5299  1.5306
1993-05-12 21:00:00.000 1.5306  1.5451  1.5106  1.5256
1993-05-13 21:00:00.000 1.5256  1.5416  1.5211  1.5361
1993-05-16 21:00:00.000 1.5361  1.5534  1.5318  1.5361
1993-05-17 21:00:00.000 1.5361  1.5451  1.5243  1.5361
1993-05-18 21:00:00.000 1.5361  1.5506  1.5316  1.5431
1993-05-19 21:00:00.000 1.5431  1.5686  1.5391  1.5566
1993-05-20 21:00:00.000 1.5566  1.5665  1.5401  1.5481
1993-05-23 21:00:00.000 1.5481  1.5481  1.5221  1.5356
1993-05-24 21:00:00.000 1.5356  1.5491  1.5341  1.5436
1993-05-25 21:00:00.000 1.5436  1.5567  1.5421  1.5476
1993-05-26 21:00:00.000 1.5476  1.5656  1.5421  1.5621
1993-05-27 21:00:00.000 1.5621  1.5714  1.5546  1.5621
1993-05-30 21:00:00.000 1.5621  1.5631  1.5571  1.5621
1993-05-31 21:00:00.000 1.5621  1.5706  1.5421  1.5611
1993-06-01 21:00:00.000 1.5611  1.5611  1.5341  1.5341
1993-06-02 21:00:00.000 1.5341  1.5526  1.5341  1.5411
1993-06-03 21:00:00.000 1.5411  1.5566  1.5071  1.5081
1993-06-06 21:00:00.000 1.5081  1.5306  1.5071  1.5246
1993-06-07 21:00:00.000 1.5246  1.5306  1.5142  1.5221
1993-06-08 21:00:00.000 1.5221  1.5271  1.5076  1.5148
1993-06-09 21:00:00.000 1.5148  1.5326  1.5016  1.5301
1993-06-10 21:00:00.000 1.5301  1.5401  1.5056  1.5231
1993-06-13 21:00:00.000 1.5231  1.5331  1.5226  1.5281
1993-06-14 21:00:00.000 1.5281  1.5391  1.5151  1.5171
1993-06-15 21:00:00.000 1.5171  1.5283  1.4991  1.5063
1993-06-16 21:00:00.000 1.5063  1.5186  1.4994  1.5153
1993-06-17 21:00:00.000 1.5153  1.5171  1.4901  1.4941
1993-06-20 21:00:00.000 1.4941  1.4988  1.4808  1.4888
1993-06-21 21:00:00.000 1.4888  1.4911  1.4751  1.4788
1993-06-22 21:00:00.000 1.4788  1.4846  1.4681  1.4715
1993-06-23 21:00:00.000 1.4715  1.4776  1.4645  1.4704
1993-06-24 21:00:00.000 1.4704  1.4886  1.4661  1.4806

The current query output which calculates the True Range is this:

Time                    H-L     H-Cp    L-Cp    TR
1993-05-09 21:00:00.000 0.0475  NULL    NULL    0.0475
1993-05-10 21:00:00.000 0.0225  0.0205  0.002   0.0225
1993-05-11 21:00:00.000 0.0222  0.013   0.0092  0.0222
1993-05-12 21:00:00.000 0.0345  0.0145  0.02    0.0345
1993-05-13 21:00:00.000 0.0205  0.016   0.0045  0.0205
1993-05-16 21:00:00.000 0.0216  0.0173  0.0043  0.0216
1993-05-17 21:00:00.000 0.0208  0.009   0.0118  0.0208
1993-05-18 21:00:00.000 0.019   0.0145  0.0045  0.019
1993-05-19 21:00:00.000 0.0295  0.0255  0.004   0.0295
1993-05-20 21:00:00.000 0.0264  0.0099  0.0165  0.0264
1993-05-23 21:00:00.000 0.026   0       0.026   0.026
1993-05-24 21:00:00.000 0.015   0.0135  0.0015  0.015
1993-05-25 21:00:00.000 0.0146  0.0131  0.0015  0.0146
1993-05-26 21:00:00.000 0.0235  0.018   0.0055  0.0235
1993-05-27 21:00:00.000 0.0168  0.0093  0.0075  0.0168
1993-05-30 21:00:00.000 0.006   0.001   0.005   0.006
1993-05-31 21:00:00.000 0.0285  0.0085  0.02    0.0285
1993-06-01 21:00:00.000 0.027   0       0.027   0.027
1993-06-02 21:00:00.000 0.0185  0.0185  0       0.0185
1993-06-03 21:00:00.000 0.0495  0.0155  0.034   0.0495
1993-06-06 21:00:00.000 0.0235  0.0225  0.001   0.0235
1993-06-07 21:00:00.000 0.0164  0.006   0.0104  0.0164
1993-06-08 21:00:00.000 0.0195  0.005   0.0145  0.0195
1993-06-09 21:00:00.000 0.031   0.0178  0.0132  0.031
1993-06-10 21:00:00.000 0.0345  0.01    0.0245  0.0345
1993-06-13 21:00:00.000 0.0105  0.01    0.0005  0.0105
1993-06-14 21:00:00.000 0.024   0.011   0.013   0.024
1993-06-15 21:00:00.000 0.0292  0.0112  0.018   0.0292
1993-06-16 21:00:00.000 0.0192  0.0123  0.0069  0.0192
1993-06-17 21:00:00.000 0.027   0.0018  0.0252  0.027
1993-06-20 21:00:00.000 0.018   0.0047  0.0133  0.018
1993-06-21 21:00:00.000 0.016   0.0023  0.0137  0.016
1993-06-22 21:00:00.000 0.0165  0.0058  0.0107  0.0165
1993-06-23 21:00:00.000 0.0131  0.0061  0.007   0.0131
1993-06-24 21:00:00.000 0.0225  0.0182  0.0043  0.0225

Using this SQL query: (Thanks to Lad2025)

WITH cte AS
(
  SELECT 
    t.[Time]
   ,[H-L]    = Round([BID-HIGH]-[BID-LOW],5) 
   ,[H-Cp]   = Abs(Round([BID-HIGH]-[prev_BID-CLOSE],5)) 
   ,[L-Cp]   = Abs(Round([BID-LOW]-[prev_BID-CLOSE],5))
  FROM (SELECT *, 
               [prev_BID-CLOSE]  = LAG([BID-CLOSE]) OVER(ORDER BY [Time])
        FROM [#tbl_GBP-USD_1-Day]) AS t
)
SELECT *
FROM cte
CROSS APPLY (SELECT MAX(v) AS v
             FROM ( VALUES ([H-L]),([H-Cp]),([L-Cp])) AS value(v)
) AS sub([TR]);

#

The example ATR query below is a 10 DAY ATR (Average True Range).

As you can see the first 9 rows are blank, as this data is required to calculate the first 10 day average [TR]. Then, each row after the first ATR calculation is an average of the last [TR] 10 days and so on.

TIME                    H-L     H-Cp    L-Cp    TR      ATR
1993/05/09 21:00:00.000 0.0475  NULL    NULL    0.0475
1993/05/10 21:00:00.000 0.0225  0.0205  0.002   0.0225  
1993/05/11 21:00:00.000 0.0222  0.013   0.0092  0.0222  
1993/05/12 21:00:00.000 0.0345  0.0145  0.02    0.0345  
1993/05/13 21:00:00.000 0.0205  0.016   0.0045  0.0205  
1993/05/16 21:00:00.000 0.0216  0.0173  0.0043  0.0216  
1993/05/17 21:00:00.000 0.0208  0.009   0.0118  0.0208  
1993/05/18 21:00:00.000 0.019   0.0145  0.0045  0.019   
1993/05/19 21:00:00.000 0.0295  0.0255  0.004   0.0295  
1993/05/20 21:00:00.000 0.0264  0.0099  0.0165  0.0264  0.02645
1993/05/23 21:00:00.000 0.026   0       0.026   0.026   0.0243
1993/05/24 21:00:00.000 0.015   0.0135  0.0015  0.015   0.02355
1993/05/25 21:00:00.000 0.0146  0.0131  0.0015  0.0146  0.02279
1993/05/26 21:00:00.000 0.0235  0.018   0.0055  0.0235  0.02169
1993/05/27 21:00:00.000 0.0168  0.0093  0.0075  0.0168  0.02132
1993/05/30 21:00:00.000 0.006   0.001   0.005   0.006   0.01976
1993/05/31 21:00:00.000 0.0285  0.0085  0.02    0.0285  0.02053
1993/06/01 21:00:00.000 0.027   0       0.027   0.027   0.02133
1993/06/02 21:00:00.000 0.0185  0.0185  0       0.0185  0.02023
1993/06/03 21:00:00.000 0.0495  0.0155  0.034   0.0495  0.02254
1993/06/06 21:00:00.000 0.0235  0.0225  0.001   0.0235  0.02229
1993/06/07 21:00:00.000 0.0164  0.006   0.0104  0.0164  0.02243
1993/06/08 21:00:00.000 0.0195  0.005   0.0145  0.0195  0.02292
1993/06/09 21:00:00.000 0.031   0.0178  0.0132  0.031   0.02367
1993/06/10 21:00:00.000 0.0345  0.01    0.0245  0.0345  0.02544
1993/06/13 21:00:00.000 0.0105  0.01    0.0005  0.0105  0.02589
1993/06/14 21:00:00.000 0.024   0.011   0.013   0.024   0.02544
1993/06/15 21:00:00.000 0.0292  0.0112  0.018   0.0292  0.02566
1993/06/16 21:00:00.000 0.0192  0.0123  0.0069  0.0192  0.02573
1993/06/17 21:00:00.000 0.027   0.0018  0.0252  0.027   0.02348
1993/06/20 21:00:00.000 0.018   0.0047  0.0133  0.018   0.02293
1993/06/21 21:00:00.000 0.016   0.0023  0.0137  0.016   0.02289
1993/06/22 21:00:00.000 0.0165  0.0058  0.0107  0.0165  0.02259
1993/06/23 21:00:00.000 0.0131  0.0061  0.007   0.0131  0.0208
1993/06/24 21:00:00.000 0.0225  0.0182  0.0043  0.0225  0.0196

Just to further expand.

[H-L] = High (minus) Low, 
[H-Cp] = High (Minus) Previous Day Close, 
[L-Cp] = Low (Minus) Previous Day Close
[TR] = MAX Value of [H-L],[H-Cp],[L-Cp]
[ATR] = Average of TR over X amount of days

X in this case is 10 days

I require some SQL statement to add to the current TR query code above.

Thankyou for your help!

Answer 1

This calculation of the ATR accomodates Paul Easter's comment:

, cteAvgTrueRangePrior AS (
    SELECT 
        t.*,
        CASE WHEN 
            LAG(t.[TR], @intervals) OVER (ORDER BY t.[dateTimePDT]) IS NOT NULL THEN (
                SELECT SUM([TR]) FROM cteTrueRange t2 
                WHERE 
                    (t2.ix > t.ix-@intervals) AND 
                    (t2.ix < t.ix)
            ) END AS [ATRPriorSum],
        CASE WHEN 
            LAG(t.[TR], @intervals) OVER (ORDER BY t.[dateTimePDT]) IS NOT NULL THEN (
                SELECT ROUND(AVG([TR]), 5) FROM cteTrueRange t2 
                WHERE 
                    (t2.ix > t.ix-@intervals) AND 
                    (t2.ix <= t.ix)
            ) END AS [ATRCurrent]
FROM cteTrueRange t
)

--,cteAvgTrueRange 
SELECT 
        a.*,
        (ATRPriorSum+ATRCurrent)/@Intervals AS ATR
FROM cteAvgTrueRangePrior a

Answer 2

The above answer is correct. However, the way ATR is being calculated here is incorrect. The True Range calculation is fine, but the formula for ATR (for 14 days in this example) is:

ATR = [(Prior ATR* x 13) + Current TR] / 14

*The first ATR uses simple moving average (SMA) of "True Range" for last 14 days)

As you can see, it's a bit more complex.

I know this is old, but I thought I'd correct the calculation before anyone (else) uses it for analysis.

Answer 3

Something like this may do, using the same approach as with the TR:

WITH cteRange AS (
    SELECT 
        t.[Time],
        Round(t.[BID-HIGH]-t.[BID-LOW],5) AS [H-L],
        Abs(Round(t.[BID-HIGH]-t.[prev_BID-CLOSE],5)) AS [H-Cp],
        Abs(Round(t.[BID-LOW]-t.[prev_BID-CLOSE],5)) AS [L-Cp]
    FROM (
        SELECT *, [prev_BID-CLOSE]  = LAG([BID-CLOSE]) OVER (ORDER BY [Time])
        FROM [#tbl_GBP-USD_1-Day]
    ) AS t
), cteTrueRange AS (
    SELECT *
    FROM cteRange
    CROSS APPLY (
        SELECT MAX(v) AS v
        FROM (VALUES ([H-L]), ([H-Cp]), ([L-Cp])) AS value(v)
    ) AS sub([TR])
), cteTrueRange10 AS (
    SELECT
        *,
        LAG([TR], 1) OVER (ORDER BY [Time]) AS [TR1],
        LAG([TR], 2) OVER (ORDER BY [Time]) AS [TR2],
        LAG([TR], 3) OVER (ORDER BY [Time]) AS [TR3],
        LAG([TR], 4) OVER (ORDER BY [Time]) AS [TR4],
        LAG([TR], 5) OVER (ORDER BY [Time]) AS [TR5],
        LAG([TR], 6) OVER (ORDER BY [Time]) AS [TR6],
        LAG([TR], 7) OVER (ORDER BY [Time]) AS [TR7],
        LAG([TR], 8) OVER (ORDER BY [Time]) AS [TR8],
        LAG([TR], 9) OVER (ORDER BY [Time]) AS [TR9]
    FROM cteTrueRange
)
SELECT [Time], [H-L], [H-Cp], [L-Cp], [TR], [TRA]
    FROM cteTrueRange10
    CROSS APPLY (
        SELECT CASE WHEN [TR9] IS NOT NULL THEN AVG(v) END AS v
        FROM (VALUES ([TR]), ([TR1]), ([TR2]), ([TR3]), ([TR4]), ([TR5]), ([TR6]), ([TR7]), ([TR8]), ([TR9])) AS value(v)
    ) AS sub([TRA]);

And here the other solution explained in the comment where you can have it average over any number of days (100 in this case):

WITH cteRange AS (
    SELECT 
        t.[Time],
        ROUND(t.[BID-HIGH]-t.[BID-LOW],5) AS [H-L],
        ABS(ROUND(t.[BID-HIGH]-t.[prev_BID-CLOSE],5)) AS [H-Cp],
        ABS(ROUND(t.[BID-LOW]-t.[prev_BID-CLOSE],5)) AS [L-Cp]
    FROM (
        SELECT *, [prev_BID-CLOSE]  = LAG(u.[BID-CLOSE]) OVER (ORDER BY u.[Time])
        FROM [#tbl_GBP-USD_1-Day] u
    ) AS t
), cteTrueRange AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [time]) AS ix
    FROM cteRange
    CROSS APPLY (
        SELECT MAX(v) AS v
        FROM (VALUES ([H-L]), ([H-Cp]), ([L-Cp])) AS value(v)
    ) AS sub([TR])
)
SELECT t.[Time], t.[H-L], t.[H-Cp], t.[L-Cp], t.[TR], CASE WHEN LAG(t.[TR], 100) OVER (ORDER BY t.[time]) IS NOT NULL THEN (
        SELECT ROUND(AVG([TR]), 5) FROM cteTrueRange t2 WHERE (t2.ix > t.ix-100) AND (t2.ix <= t.ix)
    ) END AS [TRA]
    FROM cteTrueRange t;

The LAG([TR], 100) call here is used to block averages from ignoring NULL - the normal behavior of AVG() is to just ignore nulls, which would not satisfy your requirement to return null for the first n-1 days.