Running Average of 3 Days Trading and then Populate that Value for Trading Holidays

Question

I have a table with Data for Different Companies and Closing Price for Trading Days. I need to calculate a 3 Day Running Average for every Company. I need to then join with a calendar table to populate Close Price and Avg3DayClosePrice for all dates including Trading Holidays. For trading holidays the values should be of previous trading day.

Part of this is already answered in the post SQL for Dates with no ClosePrice for all companies

3 Day average before including Trading Holiday

select d.date as tdate, d.datekey, t.ticker, fsdc.ClosePrice as cp, 
coalesce(fsdc.ClosePrice,
            lag(fsdc.ClosePrice, 1) over (partition by t.ticker order by d.date),
            lag(fsdc.ClosePrice, 2) over (partition by t.ticker order by d.date),
            lag(fsdc.ClosePrice, 3) over (partition by t.ticker order by d.date),
            lag(fsdc.ClosePrice, 4) over (partition by t.ticker order by d.date)
           ) as ClosePrice
 -- This is the new addition
,AVG(fsdc.ClosePrice) OVER (partition by t.ticker order by d.date 
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avrg
-- new addition ends
from dimdates d join
 (select ticker, min(datekey) as min_datekey
  from factStockDividendCommodity fsdc
  --where ticker <> 'BP'
  group by ticker
 ) t
 on d.datekey >= t.min_datekey left join
 factStockDividendCommodity fsdc
 on fsdc.ticker = t.ticker and
    fsdc.datekey = d.datekey
where d.Date <= GETDATE()
order by ticker, d.Date;

Screenshot with issue

Updated Script:

            select d.date as tdate, d.datekey, t.ticker, fsdc.ClosePrice as cp, 
            coalesce(fsdc.ClosePrice,
                        lag(fsdc.ClosePrice, 1) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 2) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 3) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 4) over (partition by t.ticker order by d.date)
                       ) as ClosePrice,
            coalesce(   lag(fsdc.ClosePrice, 1) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 2) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 3) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 4) over (partition by t.ticker order by d.date),
                        lag(fsdc.ClosePrice, 5) over (partition by t.ticker order by d.date)
                       ) as OpenPrice, av3,
            coalesce(fsdc.av3,
                        lag(fsdc.av3, 1) over (partition by t.ticker order by d.date),
                        lag(fsdc.av3, 2) over (partition by t.ticker order by d.date),
                        lag(fsdc.av3, 3) over (partition by t.ticker order by d.date),
                        lag(fsdc.av3, 4) over (partition by t.ticker order by d.date)
                       ) as Avg3


            from 
            (select * from dimdates where datekey <=20181231) d join
             (select ticker, min(datekey) as min_datekey
              from factStockDividendCommodity 
              where ticker <> '5X10TR'
              group by ticker
             ) t
             on d.datekey >= t.min_datekey left join
             (
              select ticker, datekey, ClosePrice, AVG(ClosePrice) OVER (partition by ticker order by datekey 
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) Av3
              from factStockDividendCommodity 
              where ticker <> '5X10TR'
             ) fsdc
             on fsdc.ticker = t.ticker and
                fsdc.datekey = d.datekey
            where d.Date <= GETDATE()
            order by ticker, d.Date;

I think I am almost there with this Code. Only problem is that I don't want so see value for 2nd day since it is not a 3 day Average. I want to see Null for that day. — ITNube, Aug 26 '19 at 14:44

Popeye · Answer 1 · 2019-08-26T01:29:15.243

0

You can use average analytical function with preceding clause.

AVG(fsdc.ClosePrice ignore nulls) OVER (partition by t.ticker order by d.date 
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avrg

-- update --

You should use lag with ignore null as following to fetch values individyally

Lag(fsdc.ClosePrice,1) ignore nulls OVER (partition by t.ticker order by d.date) as prev1,
Lag(fsdc.ClosePrice,2) ignore nulls OVER (partition by t.ticker order by d.date) as prev2,
Lag(fsdc.ClosePrice,3) ignore nulls OVER (partition by t.ticker order by d.date) as prev3

Cheers!!

edited Aug 26 '19 at 01:29

answered Aug 24 '19 at 18:48

Popeye

35,427
4
10
31

This helps...but it doesn't give me correct value. I am probably putting it at a wrong spot I am editing my initial query as I am unsure where I can put the edited SQL I have also attached a screenshot with issue described. – ITNube Aug 24 '19 at 19:14
Sorry but this syntax giving an error. I hope I could fix it myself but my lack of knowledge doesn't help. I appreciate your help. – ITNube Aug 25 '19 at 20:55
May be ignore nulls are not allowed with avg. Let me try to achieve it using something else – Popeye Aug 26 '19 at 01:19
Ohh.. you cam try updated average syntax and if it do not work then lag with ignore nulls as shown in answer – Popeye Aug 26 '19 at 01:30

Running Average of 3 Days Trading and then Populate that Value for Trading Holidays

1 Answers1