5 day moving average

rohitb · August 2, 2011

hello friends

im new to this

i have a database which looks like below

NIFTY,2011-07-21,5554.6001,5578.8999,5532.7002,5541.6001

NIFTY,2011-07-22,5576.9502,5642.2002,5567.1001,5633.9502

NIFTY,2011-07-25,5633.7998,5700.5498,5616.7002,5680.2998

NIFTY,2011-07-26,5688.4502,5702.2500,5560.1499,5574.8501

NIFTY,2011-07-27,5588.5498,5591.7002,5521.5000,5546.7998

NIFTY,2011-07-28,5492.3999,5512.1001,5475.6499,5487.7500

NIFTY,2011-07-29,5479.0000,5520.2998,5453.9502,5482.0000

NIFTY,2011-08-01,5527.5000,5551.8999,5486.4502,5516.7998

the columns are as follows ticker, date, open, high, low, close

i want to calculate a 5 day moving average of the close column.

can neone help me pls

regards

btherl · August 2, 2011

What code do you have so far?

rohitb · August 2, 2011

im stuck @ the start

done just the basic connectivity to mysql database

aah n one more thing the ticker field has multiple symbols ie nifty,acc,abb i would want to calculate averages for all

btherl · August 2, 2011

Have you used arrays before?

rohitb · August 2, 2011

<html>
<body>

<?PHP 
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","sdfsdfaa","ssdf"); // (host, username, password)

// Specify database ** EDIT THIS **
mysql_select_db("rohitb_fnchartsdb") or die("Unable to select database"); //select db
$query = "select * from nse_equity_eod_data where _date=(SELECT MAX(_date)from nse_equity_eod_data) ORDER BY ticker ASC";
//$result = mysql_query("select DISTINCT ticker from eod_data ORDER BY ticker ASC");

$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

echo "<b><font size=2 face=verdana>Classic Pivot Calculator</b><br><br>";

$i=0;
echo "<table border=0 cellspacing=2 cellpadding=5>";
echo "<tr><th bgcolor=#DBB84D align=left><font size=1 face=verdana>Ticker</th>";
echo "<th bgcolor=#DBB84D align=left><font size=2 face=verdana>Date</th>";
//echo "<th>Open</th>";
//echo "<th>High</th>";
//echo "<th>Low</th>";
//echo "<th>Close</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Supp4</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Supp3</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Supp2</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Supp1</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Pivot</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Resi1</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Resi2</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Resi3</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Resi4</th></tr>";
while ($i < $num) {
echo '<tr>';
$ticker=mysql_result($result,$i,"ticker");
$date=mysql_result($result,$i,"_date");
$open=mysql_result($result,$i,"open");
$high=mysql_result($result,$i,"high");
$low=mysql_result($result,$i,"low");
$close=mysql_result($result,$i,"close");
$volume=mysql_result($result,$i,"volume");
$pivot=(mysql_result($result,$i,"high")+ mysql_result($result,$i,"low")+ mysql_result($result,$i,"close"))/3;
$supp4=$pivot-(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"))*3;
$supp3=$pivot-(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"))*2;
$supp2=$pivot-(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"));
$supp1=(2*$pivot)-mysql_result($result,$i,"high");
$resi1=(2*$pivot)-mysql_result($result,$i,"low");
$resi2=$pivot+(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"));
$resi3=$pivot+(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"))*2;
$resi4=$pivot+(mysql_result($result,$i,"high")-mysql_result($result,$i,"low"))*3;
$s1=sprintf("%.2f", $supp1);
$s2=sprintf("%.2f", $supp2);
$s3=sprintf("%.2f", $supp3);
$s4=sprintf("%.2f", $supp4);
$p=sprintf("%.2f", $pivot);
$r1=sprintf("%.2f", $resi1);
$r2=sprintf("%.2f", $resi2);
$r3=sprintf("%.2f", $resi3);
$r4=sprintf("%.2f", $resi4);
$o=sprintf("%.2f", $open);
$h=sprintf("%.2f", $high);
$l=sprintf("%.2f", $low);
$c=sprintf("%.2f", $close);
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$ticker</td>";
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$date</td>";
//echo "<td>$o</td>";
//echo "<td>$h</td>";
//echo "<td>$l</td>";
//echo "<td>$c</td>";
echo "<td bgcolor=#FF6600><font size=1 face=verdana>$s4</td>";
echo "<td bgcolor=#FF6633><font size=1 face=verdana>$s3</td>";
echo "<td bgcolor=#FF6666><font size=1 face=verdana>$s2</td>";
echo "<td bgcolor=#FF6699><font size=1 face=verdana>$s1</td>";
echo "<td bgcolor=#FFCCCC><font size=1 face=verdana>$p</td>";
echo "<td bgcolor=#00CC99><font size=1 face=verdana>$r1</td>";
echo "<td bgcolor=#00CC66><font size=1 face=verdana>$r2</td>";
echo "<td bgcolor=00CC33><font size=1 face=verdana>$r3</td>";
echo "<td bgcolor=#00CC00><font size=1 face=verdana>$r4</td>";
echo '</tr>';

$i++;

}
echo '</table>';
?>

</body>
</html>

this is what i have done before it calculates daily pivotes

Nodral · August 2, 2011

To make this easier,

Change your date format to a Unix timestamp, then you can create a script which will take today's date and subtract 5 days from it (call it $date_calc or something similr). Then you would do something like

//get the values from the table which are 5 days old or younger.
$sql="SELECT close FROM tablename WHERE date > $date_calc";
//run sql query
$sql=mysql_query($sql);
while($row=mysql_fetch_array($sql)){

//add the last 5 into an array
$close_total[]=$row[close];
}

//calculate the average of the array
$output=(array_sum($close_total))/5

//then do what every you want with $output

Pikachu2000 · August 2, 2011

A unix timestamp is not an easy or efficient way to work with dates in MySQL. There are plenty of date/time functions built in to MySQL.

Maq · August 2, 2011

rohitb, in the future, please place

OR

tags around your code.

Zane · August 2, 2011

i want to calculate a 5 day moving average of the close column.

5 day average? As in, every five days? You could accomplish this through a single SQL query, no need for arrays or any PHP functions.

Something like this

select ticker,  avg(close) as close_avg from eod_data where `date` >= current_date() - 5 group by ticker order by close_avg

rohitb · August 3, 2011

<html>
<body>

<?PHP 
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","adfs","sadf9"); // (host, username, password)

// Specify database ** EDIT THIS **
mysql_select_db("rohitb_fnchartsdb") or die("Unable to select database"); //select db
$query="SELECT avg(close), ticker FROM nse_equity_eod_data GROUP BY ticker LIMIT 5 ORDER BY date DESC";
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();

echo "<b><font size=2 face=verdana>5 day sma</b><br><br>";

$i=0;
echo "<table border=0 cellspacing=2 cellpadding=5>";
echo "<tr><th bgcolor=#DBB84D align=left><font size=1 face=verdana>Ticker</th>";
echo "<th bgcolor=#DBB84D align=left><font size=2 face=verdana>Date</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>5D-SMA</th></tr>";
while ($i < $num) {
echo '<tr>';
$ticker=mysql_result($result,$i,"ticker");
$date=mysql_result($result,$i,"_date");
$5daysma=(mysql_result($result,$i,"AVG(close)");
$sma=sprintf("%.2f", $5daysma);
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$ticker</td>";
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$date</td>";
//echo "<td>$o</td>";
//echo "<td>$h</td>";
//echo "<td>$l</td>";
//echo "<td>$c</td>";
echo "<td bgcolor=#00CC00><font size=1 face=verdana>$sma</td>";
echo '</tr>';

$i++;

}
echo '</table>';
?>

</body>
</html>

i tried this but got error Parse error: syntax error, unexpected T_LNUMBER, expecting T_VARIABLE or '$' in /home/rohitb/public_html/technical analysis/5day sma.php on line 26

Zane · August 3, 2011

$5daysma=(mysql_result($result,$i,"AVG(close)");

You can't have variables that start with a number.

Also, in your query, you should give avg(close) an alias so it's easier to get out of the array.

$query="SELECT avg(close) as 5dayavg, ticker FROM nse_equity_eod_data GROUP BY ticker LIMIT 5 ORDER BY date DESC";

Then you can do this.

$fivedays_ma =(mysql_result($result,$i,"5dayavg");

rohitb · August 3, 2011

$5daysma=(mysql_result($result,$i,"AVG(close)");
You can't have variables that start with a number.

Also, in your query, you should give avg(close) an alias so it's easier to get out of the array.
$query="SELECT avg(close) as 5dayavg, ticker FROM nse_equity_eod_data GROUP BY ticker LIMIT 5 ORDER BY date DESC";
Then you can do this.
$fivedays_ma =(mysql_result($result,$i,"5dayavg");

i did the above now started getting this error Parse error: syntax error, unexpected ';' in /home/rohitb/public_html/technical analysis/5day sma.php on line 26

<html>
<body>

<?PHP
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","rosd","snortert9"); // (host, username, password)

// Specify database ** EDIT THIS **
mysql_select_db("rohitb_fnchartsdb") or die("Unable to select database"); //select db
$query="SELECT avg(close) as 5dayavg, ticker FROM nse_equity_eod_data GROUP BY ticker LIMIT 5 ORDER BY date DESC";
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();

echo "<b><font size=2 face=verdana>5 day sma</b><br><br>";
echo "$result";
$i=0;
echo "<table border=0 cellspacing=2 cellpadding=5>";
echo "<tr><th bgcolor=#DBB84D align=left><font size=1 face=verdana>Ticker</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Date</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>5D-SMA</th></tr>";
while ($i < $num) {
echo '<tr>';
$ticker=mysql_result($result,$i,"ticker");
$date=mysql_result($result,$i,"_date");
$fivedays_ma =(mysql_result($result,$i,"5dayavg");
$sma=sprintf("%.2f", $fivedays_ma);
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$ticker</td>";
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$date</td>";
//echo "<td>$o</td>";
//echo "<td>$h</td>";
//echo "<td>$l</td>";
//echo "<td>$c</td>";
echo "<td bgcolor=#00CC00><font size=1 face=verdana>$sma</td>";
echo '</tr>';

$i++;

}
echo '</table>';
?>

</body>
</html>

Nodral · August 3, 2011

You've missedc a closing bracket from this line

$fivedays_ma =(mysql_result($result,$i,"5dayavg");

should be

$fivedays_ma =(mysql_result($result,$i,"5dayavg"));

rohitb · August 4, 2011

now funny stuff

im getting Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/rohitb/public_html/technical analysis/5day sma.php on line 12

rohitb · August 4, 2011

<html>
<body>

<?PHP
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","radfsasfb","snowrwerqr"); // (host, username, password)

// Specify database ** EDIT THIS **
mysql_select_db("rohitb_fnchartsdb") or die("Unable to select database"); //select db
$query="SELECT avg(close) as 5dayavg, ticker,_date FROM nse_equity_eod_data GROUP BY ticker ORDER BY _date asc LIMIT 5";
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();

echo "<b><font size=2 face=verdana>5 day sma</b><br><br>";
$i=0;
echo "<table border=0 cellspacing=2 cellpadding=5>";
echo "<tr><th bgcolor=#DBB84D align=left><font size=1 face=verdana>Ticker</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>Date</th>";
echo "<th bgcolor=#DBB84D align=left><font size=1 face=verdana>5D-SMA</th></tr>";
while ($i < $num) {
echo '<tr>';
$ticker=mysql_result($result,$i,"ticker");
$date=mysql_result($result,$i,"_date");
$fivedays_ma =(mysql_result($result,$i,"5dayavg"));
$sma=sprintf("%.2f", $fivedays_ma);
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$ticker</td>";
echo "<td bgcolor=#DBB84D><font size=1 face=verdana>$date</td>";
//echo "<td>$o</td>";
//echo "<td>$h</td>";
//echo "<td>$l</td>";
//echo "<td>$c</td>";
echo "<td bgcolor=#00CC00><font size=1 face=verdana>$sma</td>";
echo '</tr>';

$i++;

}
echo '</table>';
?>

</body>
</html>

finally got working code but the sql query seems to be prob its givien me average of entire close column for first five tickers i want only average of the close column for last five dates of all tickers

my table name is nse_eod_equity_data and the column names are ticker,_date,open,high,low,close,volume

would someone help me in the sql query

kickstart · August 4, 2011

Hi

Had a play and think this will do it:-

SELECT AllDays, AVG(open), AVG(high), AVG(low), AVG(close)
FROM nse_eod_equity_data
CROSS JOIN (
SELECT DATE_ADD(MinDate, INTERVAL NumDays DAY) AS AllDays, DATE_ADD(MinDate, INTERVAL NumDays-5 DAY) AS AllDays5, MinDate, MaxDate
FROM ((SELECT a.i+b.i*10+c.i*100 AS NumDays FROM integers a, integers b, integers c) Deriv1
INNER JOIN (SELECT MIN(_date) AS MinDate, MAX(_date) AS MaxDate FROM nse_eod_equity_data) Deriv2)
HAVING AllDays BETWEEN MinDate AND MaxDate) Deriv4
WHERE _date BETWEEN AllDays5 AND AllDays
GROUP BY AllDays

This is using a table called integers (one column called 1, 10 rows with values of 0 to 9) to generate a number between 0 and 999 (can easily be extended). This is JOINed to a select of the min an max dates on nse_eod_equity_data to get every date between them. This way it will give a figure for the 23rd and 24th despite them not being in the original list. Also gives the day minus 5 days to get the date range for the average.

This is then CROSS JOINed with the original table with the result narrowed down to be between the date returned and the date 5 days before it. Then uses AVG on the various columns to get the 5 day rolling average.

Seems to work on a test table

All the best

Keith

Sign In

5 day moving average

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information