Jump to content

5 day moving average

rohitb

By rohitb
in MySQL Help

 Share

Recommended Posts

rohitb Newbie

rohitb

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/243551-5-day-moving-average/
Share on other sites
rohitb Newbie

rohitb

Posted
  • Author
Posted 

<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 Regular Member

Nodral

Posted
Posted

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

Zane Prolific Member

Zane

Posted
Posted

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 Newbie

rohitb

Posted
  • Author
Posted 

<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 Prolific Member

Zane

Posted
Posted 

$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 Newbie

rohitb

Posted
  • Author
Posted 

$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>

rohitb Newbie

rohitb

Posted
  • Author
Posted 

<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 Member

kickstart

Posted
Posted

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.