The Sales Engineer

I am going through an exercise where I’m trying to find a way to compute stock metrics (technical indicators). What I need is a way to iterate over the rows in a database, computing each technical indicator, then putting that value into the table’s row for that date.

I found an example, and modified it to work on SQL SERVER 2008 R2. I hope you, too, find this solution extremely interesting:

DROP TABLE #google_stock
GO
create table #google_stock
(
quote_date [datetime],
open_price [decimal](6,2),
close_price [decimal](6,2),
high_price [decimal](6,2),
low_price [decimal](6,2)
)
GO

INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091026', 555.75, 554.21, 561.64, 550.89)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091027', 550.97, 548.29, 554.56, 544.16)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091028', 547.87, 540.30, 550.00, 538.25)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091029', 543.01, 551.05, 551.83, 541.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091030', 550.00, 536.12, 550.17, 534.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091102', 537.08, 533.99, 539.46, 528.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091103', 530.01, 537.29, 537.50, 528.30)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091104', 540.80, 540.33, 545.50, 536.42)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091105', 543.49, 548.65, 549.77, 542.66)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091106', 547.72, 551.10, 551.78, 545.50)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091109', 555.45, 562.51, 562.58, 554.23)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091110', 562.73, 566.76, 568.78, 562.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091111', 570.48, 570.56, 573.50, 565.86)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091112', 569.56, 567.85, 572.90, 565.50)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091113', 569.29, 572.05, 572.51, 566.61)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091116', 575.00, 576.28, 576.99, 572.78)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091117', 574.87, 577.49, 577.50, 573.72)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091118', 576.65, 576.65, 578.78, 572.07)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091119', 573.77, 572.99, 574.00, 570.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091120', 569.50, 569.96, 571.60, 569.40)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091123', 576.49, 582.35, 586.60, 575.86)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091124', 582.52, 583.09, 584.29, 576.54)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091125', 586.41, 585.74, 587.06, 582.69)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091127', 572.00, 579.76, 582.46, 570.97)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091130', 580.63, 583.00, 583.67, 577.11)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091201', 588.13, 589.87, 591.22, 583.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091202', 591.00, 587.51, 593.01, 586.22)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091203', 589.04, 585.74, 591.45, 585.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091204', 593.02, 585.01, 594.83, 579.18)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091207', 584.21, 586.25, 588.69, 581.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091208', 583.50, 587.05, 590.66, 582.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091209', 587.50, 589.02, 589.33, 583.58)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091210', 590.44, 591.50, 594.71, 590.41)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091211', 594.68, 590.51, 594.75, 587.73)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091214', 595.35, 595.73, 597.31, 592.61)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091215', 593.30, 593.14, 596.38, 590.99)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091216', 598.60, 597.76, 600.37, 596.64)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091217', 596.44, 593.94, 597.64, 593.76)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091218', 596.03, 596.42, 598.93, 595.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091221', 597.61, 598.68, 599.84, 595.67)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091222', 601.34, 601.12, 601.50, 598.85)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091223', 603.50, 611.68, 612.87, 602.85)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091224', 612.93, 618.48, 619.52, 612.27)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091228', 621.66, 622.87, 625.99, 618.48)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091229', 624.74, 619.40, 624.84, 618.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091230', 618.50, 622.73, 622.73, 618.01)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091231', 624.75, 619.98, 625.40, 619.98)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100104', 626.95, 626.75, 629.51, 624.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100105', 627.18, 623.99, 627.84, 621.54)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100106', 625.86, 608.26, 625.86, 606.36)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100107', 609.40, 594.10, 610.00, 592.65)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100108', 592.00, 602.02, 603.25, 589.11)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100111', 604.46, 601.11, 604.46, 594.04)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100112', 597.65, 590.48, 598.16, 588.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100113', 576.49, 587.09, 588.38, 573.90)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100114', 583.90, 589.85, 594.20, 582.81)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100115', 593.34, 580.00, 593.56, 578.04)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100119', 581.20, 587.62, 590.42, 576.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100120', 585.98, 580.41, 585.98, 575.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100121', 583.44, 582.98, 586.82, 572.25)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100122', 564.50, 550.01, 570.60, 534.86)
GO
CREATE CLUSTERED INDEX ix_goog on #google_stock(quote_date)
GO
drop table #mod_goog_data
GO
with t1 as (select row_number() over (order by quote_date) n
    ,quote_date
    ,close_price
    from #google_stock)
    select a.n
       ,a.quote_date
       ,a.close_price
       ,CAST(null as decimal(8,2)) [sma]
       --add the close_price from 20 row prior to this one
       ,CAST(b.close_price as decimal(8,2)) [20_day_old_close]
    into #mod_goog_data
    from t1 a
        left join t1 b
        on a.n - 20 = b.n
GO        
declare @intervals int, @initial_sum decimal(8,2)
declare @anchor int, @moving_sum decimal(8,2)
set @intervals = 20

 --Retrieve the initial sum value at row 20
 select @initial_sum = sum(close_price)
    from #mod_goog_data
    where n <= @intervals

 update t1
    --case statement to handle @moving_sum variable
    --depending on the value of n
   set @moving_sum = case when n < @intervals then null
             when n = @intervals then @initial_sum
             when n > @intervals then
                        @moving_sum + [close_price] - [20_day_old_close]
             end,
    sma = @moving_sum/Cast(@intervals as decimal(8,2)),
    @anchor = n    --anchor so that carryover works
    from #mod_goog_data t1 with (TABLOCKX)
    OPTION (MAXDOP 1)
GO    
  select quote_date
   ,close_price
   ,sma from #mod_goog_data
GO

This produces this clean output in the temp table created:

quote_date              close_price  sma
2009-10-26 00:00:00.000    554.21    NULL
2009-10-27 00:00:00.000    548.29    NULL
2009-10-28 00:00:00.000    540.30    NULL
2009-10-29 00:00:00.000    551.05    NULL
2009-10-30 00:00:00.000    536.12    NULL
2009-11-02 00:00:00.000    533.99    NULL
2009-11-03 00:00:00.000    537.29    NULL
2009-11-04 00:00:00.000    540.33    NULL
2009-11-05 00:00:00.000    548.65    NULL
2009-11-06 00:00:00.000    551.10    NULL
2009-11-09 00:00:00.000    562.51    NULL
2009-11-10 00:00:00.000    566.76    NULL
2009-11-11 00:00:00.000    570.56    NULL
2009-11-12 00:00:00.000    567.85    NULL
2009-11-13 00:00:00.000    572.05    NULL
2009-11-16 00:00:00.000    576.28    NULL
2009-11-17 00:00:00.000    577.49    NULL
2009-11-18 00:00:00.000    576.65    NULL
2009-11-19 00:00:00.000    572.99    NULL
2009-11-20 00:00:00.000    569.96    557.72
2009-11-23 00:00:00.000    582.35    559.13
2009-11-24 00:00:00.000    583.09    560.87
2009-11-25 00:00:00.000    585.74    563.14
2009-11-27 00:00:00.000    579.76    564.58
2009-11-30 00:00:00.000    583.00    566.92
2009-12-01 00:00:00.000    589.87    569.71
2009-12-02 00:00:00.000    587.51    572.23
2009-12-03 00:00:00.000    585.74    574.50
2009-12-04 00:00:00.000    585.01    576.31
2009-12-07 00:00:00.000    586.25    578.07
2009-12-08 00:00:00.000    587.05    579.30
2009-12-09 00:00:00.000    589.02    580.41
2009-12-10 00:00:00.000    591.50    581.46
2009-12-11 00:00:00.000    590.51    582.59
2009-12-14 00:00:00.000    595.73    583.78
2009-12-15 00:00:00.000    593.14    584.62
2009-12-16 00:00:00.000    597.76    585.63
2009-12-17 00:00:00.000    593.94    586.50
2009-12-18 00:00:00.000    596.42    587.67
2009-12-21 00:00:00.000    598.68    589.10
2009-12-22 00:00:00.000    601.12    590.04
2009-12-23 00:00:00.000    611.68    591.47
2009-12-24 00:00:00.000    618.48    593.11
2009-12-28 00:00:00.000    622.87    595.26
2009-12-29 00:00:00.000    619.40    597.08
2009-12-30 00:00:00.000    622.73    598.73
2009-12-31 00:00:00.000    619.98    600.35
2010-01-04 00:00:00.000    626.75    602.40
2010-01-05 00:00:00.000    623.99    604.35
2010-01-06 00:00:00.000    608.26    605.45
2010-01-07 00:00:00.000    594.10    605.80
2010-01-08 00:00:00.000    602.02    606.45
2010-01-11 00:00:00.000    601.11    606.93
2010-01-12 00:00:00.000    590.48    606.93
2010-01-13 00:00:00.000    587.09    606.50
2010-01-14 00:00:00.000    589.85    606.34
2010-01-15 00:00:00.000    580.00    605.45
2010-01-19 00:00:00.000    587.62    605.13
2010-01-20 00:00:00.000    580.41    604.33
2010-01-21 00:00:00.000    582.98    603.55
2010-01-22 00:00:00.000    550.01    600.99

And THAT is how its done!

Related Articles

• No Related Post