How to Calculate Stock Price 200 Day Moving Average

Problem

It is a common task to calculate the moving average of a variable. For example, the 200 day stock price moving average is considered significant by some traders. We have data containing stock symbol, date and close price for 2011. The following query shows a few records for MSFT. How do we calculate 200 day stock price moving average for each symbol?

SQL> select symbol, dt, close from TBL_STOCK_2011 where symbol='MSFT' and dt between to_date('20111215','YYYYMMDD') and  to_date('20111231','YYYYMMDD'
) order by dt desc;

SYMBOL   DT             CLOSE
-------- --------- ----------
MSFT     30-DEC-11      25.25
MSFT     29-DEC-11       25.3
MSFT     28-DEC-11      25.11
MSFT     27-DEC-11      25.32
MSFT     23-DEC-11      25.31
MSFT     22-DEC-11       25.1
MSFT     21-DEC-11      25.05
MSFT     20-DEC-11      25.31
MSFT     19-DEC-11      24.83
MSFT     16-DEC-11      25.28
MSFT     15-DEC-11      24.86

11 rows selected.

Solution

One of the approaches is to use Oracle analytic window function, avg() over() as shown below.

SQL> select symbol, dt, close, avg(close) over(partition by symbol order by dt range between 199 preceding and current row) avg from TBL_STOCK_2011 or
der by dt desc;

SYMBOL   DT             CLOSE        AVG
-------- --------- ---------- ----------
MSFT     30-DEC-11      25.25 25.0448571
MSFT     29-DEC-11       25.3 25.0292143
MSFT     28-DEC-11      25.11 25.0272662
MSFT     27-DEC-11      25.32 25.0266667
MSFT     23-DEC-11      25.31 24.9653901
MSFT     22-DEC-11       25.1 24.9492199
MSFT     21-DEC-11      25.05 24.9481429
MSFT     20-DEC-11      25.31 24.9474101
MSFT     19-DEC-11      24.83 24.9302878
........................................

In the above query, "partition by symbol" means the calculation is done independently by symbols. We can verify the 200 moving average of 25.0448571 on 30-DEC-11 using the following query.

SQL> select avg(close) from tbl_stock_2011 where dt  between to_date('20111230','YYYYMMDD')-199 and to_date('20111230','YYYYMMDD');

AVG(CLOSE)
----------
25.0448571

As we can see, the 200 day average prices using two approaches are the same. A nice thing about analytic window function is that it easily creates moving average for every day. It is extremely useful when we are dealing with time series data. I have used analytic window functions extensively when building bank card or check fraud predictive models.