mysql 5 day moving average

[rohitb]

below is my MYSQL table structure

 

ticker
_date
open
high
low
close
volume

 

below is sample data

 

ALMOND-I,2012-03-27,373.5000,395.0000,373.5000,380.2500,10

ALMOND-I,2012-03-31,391.0000,391.0000,386.0000,387.0000,5

ALMOND-I,2012-04-03,378.5000,386.0000,364.5000,378.5000,14

ALMOND-I,2012-04-11,380.0000,389.5000,370.7500,380.0000,3

ALMOND-I,2012-04-12,391.0000,391.0000,370.2500,380.7500,2

ALMOND-I,2012-04-16,392.0000,392.0000,392.0000,392.0000,1

ALMOND-I,2012-04-18,380.2500,380.2500,380.2500,380.2500,1

ALMOND-I,2012-05-02,371.0000,393.5000,371.0000,381.5000,6

ALMOND-I,2012-05-15,393.0000,393.0000,393.0000,393.0000,1

ALMOND-I,2012-05-21,416.7500,417.0000,393.0000,398.5000,10

ALMOND-I,2012-05-22,410.5000,410.5000,410.5000,410.5000,1

ALMOND-I,2012-05-30,435.2500,435.2500,435.2500,435.2500,1

ALUMINIUM-I,2012-05-22,110.8000,110.9500,110.2500,110.7000,4939

ALUMINIUM-I,2012-05-23,110.9000,111.4500,110.7500,111.2500,6996

ALUMINIUM-I,2012-05-24,111.0000,111.4500,109.9000,110.4500,7111

ALUMINIUM-I,2012-05-25,110.4500,110.6000,109.4000,109.7000,5020

ALUMINIUM-I,2012-05-26,109.6000,109.8500,109.6000,109.8000,116

ALUMINIUM-I,2012-05-28,109.4000,110.3000,109.3500,110.1500,2614

ALUMINIUM-I,2012-05-29,110.4500,111.0500,110.3000,110.8500,3956

ALUMINIUM-I,2012-05-30,110.9000,111.0500,110.1000,110.9500,5208

ALUMINIUM-I,2012-05-31,110.9500,111.7500,110.7500,111.3500,3553

ALUMINIUM-I,2012-06-01,111.1500,111.3000,108.7000,108.8500,7532

ALUMINIUM-I,2012-06-02,108.8000,108.8000,108.1000,108.2500,555

GOLD-I,2012-05-21,29018.0000,29174.0000,28956.0000,29035.0000,33330

GOLD-I,2012-05-22,29068.0000,29190.0000,28827.0000,29087.0000,45281

GOLD-I,2012-05-23,29026.0000,29084.0000,28831.0000,28969.0000,50523

GOLD-I,2012-05-24,29020.0000,29102.0000,28854.0000,28880.0000,45170

GOLD-I,2012-05-25,28841.0000,28937.0000,28748.0000,28902.0000,32176

GOLD-I,2012-05-26,28964.0000,28964.0000,28918.0000,28925.0000,1427

GOLD-I,2012-05-28,28901.0000,29055.0000,28882.0000,28965.0000,17158

GOLD-I,2012-05-29,28994.0000,29135.0000,28819.0000,28859.0000,39300

GOLD-I,2012-05-30,28845.0000,29154.0000,28755.0000,29117.0000,38733

GOLD-I,2012-05-31,29120.0000,29198.0000,29005.0000,29148.0000,22810

GOLD-I,2012-06-01,29130.0000,29871.0000,28765.0000,29724.0000,4753

GOLD-I,2012-06-02,29745.0000,29929.0000,29740.0000,29866.0000,337

SILVER-I,2012-05-21,54525.0000,54805.0000,53865.0000,54130.0000,66128

SILVER-I,2012-05-22,54187.0000,55119.0000,53873.0000,54392.0000,78147

SILVER-I,2012-05-23,54250.0000,54250.0000,53320.0000,53818.0000,87066

SILVER-I,2012-05-24,53999.0000,54785.0000,53980.0000,54317.0000,74940

SILVER-I,2012-05-25,54250.0000,54500.0000,53882.0000,54426.0000,61457

SILVER-I,2012-05-26,54481.0000,54535.0000,54465.0000,54479.0000,1535

SILVER-I,2012-05-28,54533.0000,54867.0000,54302.0000,54351.0000,33566

SILVER-I,2012-05-29,54448.0000,54885.0000,53790.0000,53964.0000,82448

SILVER-I,2012-05-30,53900.0000,54578.0000,53560.0000,54483.0000,87322

SILVER-I,2012-05-31,54377.0000,54465.0000,53820.0000,54110.0000,72225

SILVER-I,2012-06-01,54002.0000,54682.0000,52813.0000,54445.0000,107052

SILVER-I,2012-06-02,54530.0000,54610.0000,54451.0000,54487.0000,2371

WHEAT-I,2010-07-22,1276.9000,1276.9000,1228.2000,1247.9000,3

WHEAT-I,2010-07-23,1258.0000,1258.0000,1258.0000,1258.0000,2

WHEAT-I,2010-07-29,1245.0000,1245.0000,1245.0000,1245.0000,1

WHEAT-I,2010-07-30,1235.0000,1235.0000,1235.0000,1235.0000,1

WHEAT-I,2010-08-02,1245.0000,1245.0000,1240.0000,1242.5000,2

WHEAT-I,2010-08-04,1220.0000,1220.0000,1220.0000,1220.0000,1

WHEAT-I,2010-08-05,1240.0000,1240.0000,1232.0000,1237.3000,3

WHEAT-I,2011-03-19,1218.9000,1218.9000,1218.9000,1218.9000,1

WHEAT-I,2011-03-21,1182.3000,1182.3000,1182.3000,1182.3000,1

WHEAT-I,2011-03-25,1151.5000,1151.5000,1151.5000,1151.5000,2

WHEAT-I,2011-04-19,1160.0000,1160.0000,1149.5000,1152.3000,4

WHEAT-I,2011-04-28,1170.0000,1195.0000,1170.0000,1182.5000,4

ZINC-I,2012-05-21,103.8000,104.7500,103.8000,104.7000,13197

ZINC-I,2012-05-22,104.7000,105.6000,104.3000,105.5000,14996

ZINC-I,2012-05-23,105.3500,105.4500,104.5000,105.0000,16656

ZINC-I,2012-05-24,104.8000,105.3000,104.2000,104.6500,12688

ZINC-I,2012-05-25,104.6000,105.3000,104.1500,104.9500,12523

ZINC-I,2012-05-26,104.9000,105.0000,104.8000,104.9500,215

ZINC-I,2012-05-28,105.0000,105.5500,104.9000,105.2500,7387

ZINC-I,2012-05-29,105.6000,106.7000,105.4000,106.6000,14092

ZINC-I,2012-05-30,106.3000,106.5000,105.4500,106.3000,11983

ZINC-I,2012-05-31,106.0500,107.4000,105.7500,107.1500,5936

ZINC-I,2012-06-01,105.6500,105.7500,103.7000,104.6500,15231

ZINC-I,2012-06-02,104.5500,104.6500,104.2500,104.4000,551

 

i want to calculate moving average CLOSE of each item for last 5 dates example for zinc average ClOSE on 2012-06-02 would be (104.4000+104.6500+107.1500+106.3000+106.6000)/5=105.82

 

i wrote a query

SELECT x.ticker , AVG( x.close), x._date
FROM 
(
    SELECT t.ticker , t.close,t._date
    FROM mcx_eod_data t
    ORDER BY t._date DESC
) x
GROUP BY x.ticker

 

it gave me average for i think the entire close from start to end of the particular ticker

 

so i did some Research and added limit statement and modified my query

 

SELECT x.ticker , AVG( x.close), x._date
FROM 
(
    SELECT t.ticker , t.close,t._date
    FROM mcx_eod_data t
    ORDER BY t._date DESC limit 5
) x
GROUP BY x.ticker

 

but now it fetched data for only 5 tickers

 

i wanted to achieve is average of close of last 5 dates for all tickers

 

so with some help on internet modified the query to

 

SELECT x.ticker , AVG(x.close),x._date
FROM 
(
    SELECT t.ticker , t.close,t._date
    FROM mcx_eod_data t
    WHERE t._date >= DATE_SUB(now(), INTERVAL 5 DAY)
) x
GROUP BY x.ticker

 

the above code does the work but the prob with now() function is market has holidays then the entire calculation goes wrong.

 

example now 2012-06-03 is a Sunday a holiday so when 5 day average is calculated it will still take the date 2012-06-03 even if no data for that day then it will take 2012-06-02,2012-06-01,2012-05-31,2012-05-30.This is wrong. We want dates 2012-06-02,2012-06-01,2012-05-31,2012-05-30,2012-05-29

[smoseley]

Create a table to store market holidays (note, do not include Sundays - those are excluded automatically by the view below).

CREATE TABLE holidays (
    holiday_date DATE NOT NULL PRIMARY KEY,
    holiday_name VARCHAR(255)
);

INSERT INTO holidays VALUES ('2012-12-25', 'Christmas 2012');

 

Create a dynamic view that returns the last 5 open market days.

It will exclude holidays and Sundays

CREATE OR REPLACE VIEW market_days AS
SELECT _date FROM (
SELECT CURDATE() AS _date
UNION ALL CURDATE() - INTERVAL 1 DAY
UNION ALL CURDATE() - INTERVAL 2 DAY
UNION ALL CURDATE() - INTERVAL 3 DAY
UNION ALL CURDATE() - INTERVAL 4 DAY
UNION ALL CURDATE() - INTERVAL 5 DAY
UNION ALL CURDATE() - INTERVAL 6 DAY
UNION ALL CURDATE() - INTERVAL 7 DAY
UNION ALL CURDATE() - INTERVAL 8 DAY
UNION ALL CURDATE() - INTERVAL 9 DAY
UNION ALL CURDATE() - INTERVAL 10 DAY
UNION ALL CURDATE() - INTERVAL 11 DAY
UNION ALL CURDATE() - INTERVAL 12 DAY
UNION ALL CURDATE() - INTERVAL 13 DAY
) AS days
RIGHT JOIN holidays ON _date = holiday_date
WHERE holiday_date IS NULL AND DAYOFWEEK(_date) != 1
ORDER BY _date DESC
LIMIT 5;

 

Then your query becomes:

SELECT t.ticker, AVG(t.close), t._date
FROM mcx_eod_data AS t
INNER JOIN market_days AS md
    ON t._date = md._date
GROUP BY t.ticker

[Illusion]

SELECT x.ticker , AVG(x.close) from mcx_eod_data x where x._date in
(
    SELECT t._date  FROM mcx_eod_data t WHERE t.ticker = x.ticker order by t._date desc LIMIT 5
) 
GROUP BY x.ticker

[rohitb]

Illusion your query gives me

 

SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[rohitb]

Create a table to store market holidays (note, do not include Sundays - those are excluded automatically by the view below).

CREATE TABLE holidays (
    holiday_date DATE NOT NULL PRIMARY KEY,
    holiday_name VARCHAR(255)
);

INSERT INTO holidays VALUES ('2012-12-25', 'Christmas 2012');

 

Create a dynamic view that returns the last 5 open market days.

It will exclude holidays and Sundays

CREATE OR REPLACE VIEW market_days AS
SELECT _date FROM (
SELECT CURDATE() AS _date
UNION ALL CURDATE() - INTERVAL 1 DAY
UNION ALL CURDATE() - INTERVAL 2 DAY
UNION ALL CURDATE() - INTERVAL 3 DAY
UNION ALL CURDATE() - INTERVAL 4 DAY
UNION ALL CURDATE() - INTERVAL 5 DAY
UNION ALL CURDATE() - INTERVAL 6 DAY
UNION ALL CURDATE() - INTERVAL 7 DAY
UNION ALL CURDATE() - INTERVAL 8 DAY
UNION ALL CURDATE() - INTERVAL 9 DAY
UNION ALL CURDATE() - INTERVAL 10 DAY
UNION ALL CURDATE() - INTERVAL 11 DAY
UNION ALL CURDATE() - INTERVAL 12 DAY
UNION ALL CURDATE() - INTERVAL 13 DAY
) AS days
RIGHT JOIN holidays ON _date = holiday_date
WHERE holiday_date IS NULL AND DAYOFWEEK(_date) != 1
ORDER BY _date DESC
LIMIT 5;

 

Then your query becomes:

SELECT t.ticker, AVG(t.close), t._date
FROM mcx_eod_data AS t
INNER JOIN market_days AS md
    ON t._date = md._date
GROUP BY t.ticker

 

 

when i m trying to execute the dynamic view query i get

 

SQL Error (1349): View's SELECT contains a subquery in the FROM clause

[Illusion]

It is still not clear to me that  you want avg of last five records or avg of records in last five days... for each ticker?

[rohitb]

It is still not clear to me that  you want avg of last five records or avg of records in last five days... for each ticker?

 

i want last five days average for each ticker

[Illusion]

Rohit ....In that case you might be aware of fact that you get zero average if you dont have any entry in last days for a ticker

 

coming to your problem ....lets take simple approach

 

what you need is  avg = sum (close) / 5

 

but when we have holiday we need to divide it by  ( 5 - number of holidays)

 

Number of holidays = sundays + national holidays

 

with in last 5 days at max you can have only one sunday

select case when DAYOFWEEK(now()) between 6 and 7 then 1 else 0 end

 

to get other holidays ...you need a create a table and maintain the data

 

select count(*) from holidays where holiday between Date(DATE_SUB(now(), INTERVAL 5 DAY)) and Date(now())

 

so to get total number of holidays in last 5 days ...we create view

 

create or replace  view HolidaysInfo as select (case when DAYOFWEEK(now()) between 6 and 7 then 1 else 0 end) + (select count(*) from holidays where holiday between Date(DATE_SUB(now(), INTERVAL 5 DAY)) and Date(now())) as NumberOfHolidays ;

 

Now you can modify your original query to

 

set  @divider:=(5- (select NumberOfHolidays from HolidaysInfo));

SELECT x.ticker , SUM(x.close)/ @divider),x._date
FROM 
(
    SELECT t.ticker , t.close,t._date
    FROM mcx_eod_data t
    WHERE t._date >= DATE_SUB(now(), INTERVAL 5 DAY)
) x
GROUP BY x.ticker

 

 

[Illusion]

small correction . to get the number of sundays  in last 5 days

 

select case when DAYOFWEEK(now()) between 6 and 7 then 1 else 0 end

 

to

 

select case when DAYOFWEEK(now()) between 6 and 7 then 0 else 1 end

 

I think I didn't  understand the problem correctly .... whatever I explained in previous post is completely wrong or irrelevant

 

[smoseley]

CREATE TABLE holidays (
    holiday_date DATE NOT NULL PRIMARY KEY,
    holiday_name VARCHAR(255)
);

INSERT INTO holidays VALUES ('2012-12-25', 'Christmas 2012');

 

CREATE OR REPLACE VIEW last_2_weeks AS
SELECT CURDATE() AS _date
UNION ALL CURDATE() - INTERVAL 1 DAY
UNION ALL CURDATE() - INTERVAL 1 DAY
UNION ALL CURDATE() - INTERVAL 2 DAY
UNION ALL CURDATE() - INTERVAL 3 DAY
UNION ALL CURDATE() - INTERVAL 4 DAY
UNION ALL CURDATE() - INTERVAL 5 DAY
UNION ALL CURDATE() - INTERVAL 6 DAY
UNION ALL CURDATE() - INTERVAL 7 DAY
UNION ALL CURDATE() - INTERVAL 8 DAY
UNION ALL CURDATE() - INTERVAL 9 DAY
UNION ALL CURDATE() - INTERVAL 10 DAY
UNION ALL CURDATE() - INTERVAL 11 DAY
UNION ALL CURDATE() - INTERVAL 12 DAY
UNION ALL CURDATE() - INTERVAL 13 DAY;

 

CREATE OR REPLACE VIEW last_5_market_days AS
SELECT _date FROM last_2_weeks
RIGHT JOIN holidays ON _date = holiday_date
WHERE holiday_date IS NULL   /* Not open on holidays */
    AND WEEKDAY(_date) != 6 /* Not open on Sundays */
ORDER BY _date DESC
LIMIT 5;

 

SELECT t.ticker, AVG(t.close), t._date
FROM mcx_eod_data AS t
INNER JOIN last_5_market_days AS md
    ON t._date = md._date
GROUP BY t.ticker

 

[smoseley]

Actually, just noticed that you're not getting data for days the market is closed.... you can ignore my solution... it's overkill.

[smoseley]

Ok, based on the data you have and your version of MySQL, this is the simplest solution I can think of for you:

 

 

1. Make a view of last 5 market days:

CREATE OR REPLACE VIEW last_5_market_days AS
SELECT DISTINCT _date 
FROM mcx_eod_data
ORDER BY _date DESC
LIMIT 5;

 

2. Join on it to get your rolling average:

SELECT t.ticker, AVG(t.close), t._date
FROM mcx_eod_data AS t
INNER JOIN last_5_market_days AS md
    ON t._date = md._date
GROUP BY t.ticker

 

Not the fastest in terms of performance since you're joining on a view, but shouldn't be a huge issue since it's only 5 records.

[rohitb]

Ok, based on the data you have and your version of MySQL, this is the simplest solution I can think of for you:

 

 

1. Make a view of last 5 market days:

CREATE OR REPLACE VIEW last_5_market_days AS
SELECT DISTINCT _date 
FROM mcx_eod_data
ORDER BY _date DESC
LIMIT 5;

 

2. Join on it to get your rolling average:

SELECT t.ticker, AVG(t.close), t._date
FROM mcx_eod_data AS t
INNER JOIN last_5_market_days AS md
    ON t._date = md._date
GROUP BY t.ticker

 

Not the fastest in terms of performance since you're joining on a view, but shouldn't be a huge issue since it's only 5 records.

 

thanks sir for the help shown. the solution u gave me seems to be working fine till now. thanks again sir

[rohitb]

small correction . to get the number of sundays  in last 5 days

 

select case when DAYOFWEEK(now()) between 6 and 7 then 1 else 0 end

 

to

 

select case when DAYOFWEEK(now()) between 6 and 7 then 0 else 1 end

 

I think I didn't  understand the problem correctly .... whatever I explained in previous post is completely wrong or irrelevant

 

thanks sir for the efforts i didnt try ur solution right now but i will try and take it as a backup plan

[smoseley]

Cheers, Rohit.  Glad it works.

 

Illusion, the reason a "last 5 days" approach won't work is because the market is also shut on holidays.  It's a complicated problem, though made easier by the fact that the data is empty on days when the market is closed.  Your initial LIMIT 5 solution was the best possible scenario, but given that the OP is on an older version of MySQL, not available.

[rohitb]

Cheers, Rohit.  Glad it works.

 

Illusion, the reason a "last 5 days" approach won't work is because the market is also shut on holidays.  It's a complicated problem, though made easier by the fact that the data is empty on days when the market is closed.  Your initial LIMIT 5 solution was the best possible scenario, but given that the OP is on an older version of MySQL, not available.

 

one more thing now im trying to create view through php its not getting created but if i create view directly through remote mysql its getting created why so below is my code

 

 

<?PHP
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","rohjhjghb","snoyuiyk,"); // (host, username, password)
// Specify database ** EDIT THIS **
mysql_select_db("rohitb_fnchartsdb") or die("Unable to select database"); //select db
$query="CREATE OR REPLACE VIEW last_5_market_days_nse AS SELECT DISTINCT _date FROM nse_equity_eod_data ORDER BY _date DESC LIMIT 5";
$query1= "SELECT t.ticker, round (AVG(t.close),2) as 5dayavg, t._date FROM nse_equity_eod_data AS t INNER JOIN last_5_market_days_nse AS md ON t._date = md._date GROUP BY t.ticker";
$result=mysql_query($query1);
$num=mysql_num_rows($result);
mysql_close();

[smoseley]

You should not be creating a view in your php.  A view is like a table, it should only be created one time.  Do it through phpMyAdmin or mysql in a terminal.