5 day moving average

rohitb

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

leatherback

As this is something you do not need to do more than once foir an individual date, I would not worry and just use a lot of force and processor time:

Select all data from the database, sort by date. Then loop througfh the data. Keep an array with the last 5 days of data, and insert in each loop a new value in your database.

rohitb

can someone show with coding, im pretty new to this

leatherback

This is unfortunately not a 'code for free' website. The general idea with programming is that you try to learn to code, or just hire someone. Asking someone on a forum to write your code for you is not helping you and most people are not too keen on doing it either.

So.. What have you tried sofar? Where do you get stuck?

rohitb

<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 had written earlier for getting pivot points

rohitb

rohitb;10985374 wrote:

<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 had written earlier for getting pivot points

how can i modify this to get the desired result also the ticker column has multiple tickers ie nifty,tcs,acc etc. i would like to get 5 day moving average for all on the max date

johanafm

I'd stick to SQL rather than doing all the work myself in PHP.

SELECT get_date.tdate, get_value.ticker, AVG(get_value.val)
FROM ttable AS get_date
INNER JOIN ttable AS get_values ON get_date.ticker = get_value.ticker AND get_value.tdate >= get_date.tdate AND get_value.tdate < DATE_ADD(get_date.tdate, INTERVAL 5 DAY)
GROUP BY get_date.tdate, get_value.ticker
HAVING COUNT(*) = 5

In your case, you'd have several occurences of get_value.val, where val should be replaced by each column name you want a moving average for, i.e. AVG(t.field1), AVG(t.field2) etc.

The way this query works is that it selects all dates from ttable (what I call your table containing tickers) ALIASed as get_date, since that is what we do with this table.
Then we join the table with itself, and alias the joined table get_value, since the joined table is used to retrieve 5 values per (starting) date.
For every row in get_date, you need to retrieve values for 5 days. Thus, joining the tables on equal values for ticker in both tables and allowing get_value.tdate be the same as in get_date and up to 4 days ahead, you get 5 rows (days) for each date in get_date.

To keep things properly grouped together, you need to group on the date from get_date, since this is our starting point. Remember, it's for each date value in get_date that you get 5 different dates in get_value, so we obviously can't group by get_value.date. Then you obviously also need to group by ticker, since these are different groups of data. And finally, when everything gets grouped together properly, you also state that you want the average for each such group of 5 days.

The last part, HAVING COUNT(*) = 5 just means that you tell the DBMS that you don't want the last couple of rows where you reach the end of the data series. The last date in the table will have only 1 row to join against since there are no future days yet, the 2nd highest date has only two rows to join with etc. HAVING works pretty much like WHERE does, except it operates on data AFTER the query has completed retrieving results, which also means that you should never use HAVING when you could use WHERE (even though you always can).
The reverse is that you should always use WHERE instead of HAVING if you can, but you cannot always do this. In this case, the database cannot know, in advance, how many rows will be retrieved until after all rows have been retrieved, and that is why we have to stick with HAVING to discard them after they where selected.

leatherback

Nice!

rohitb

<html>
<body>

<?PHP 
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","rohitb","snowyou1979"); // (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>

tried the above code but got following 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

bradgrafelman

Variable names can not begin with a number. This manual page describes what a valid variable name is: [man]variables.basics[/man].

rohitb

<html>
<body>

<?PHP
// Connect to your database ** EDIT THIS **
mysql_connect("localhost","rohitb","snowyou1979"); // (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>

repaired the previous mistake now new warning 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

bradgrafelman

You should always check to make sure that your SQL queries execute properly and, if not, log and/or display (as appropriate) the error message returned by the MySQL server. In other words, if [man]mysql_query/man returns boolean FALSE, you should use [man]mysql_error/man to help determine the cause of the error. (Also note that it's often beneficial to output/log the full SQL query as it was sent to the MySQL server for comparison/inspection.)

rohitb

johanafm;10985378 wrote:
I'd stick to SQL rather than doing all the work myself in PHP.
SELECT get_date.tdate, get_value.ticker, AVG(get_value.val)
FROM ttable AS get_date
INNER JOIN ttable AS get_values ON get_date.ticker = get_value.ticker AND get_value.tdate >= get_date.tdate AND get_value.tdate < DATE_ADD(get_date.tdate, INTERVAL 5 DAY)
GROUP BY get_date.tdate, get_value.ticker
HAVING COUNT(*) = 5
In your case, you'd have several occurences of get_value.val, where val should be replaced by each column name you want a moving average for, i.e. AVG(t.field1), AVG(t.field2) etc.

The way this query works is that it selects all dates from ttable (what I call your table containing tickers) ALIASed as get_date, since that is what we do with this table.
Then we join the table with itself, and alias the joined table get_value, since the joined table is used to retrieve 5 values per (starting) date.
For every row in get_date, you need to retrieve values for 5 days. Thus, joining the tables on equal values for ticker in both tables and allowing get_value.tdate be the same as in get_date and up to 4 days ahead, you get 5 rows (days) for each date in get_date.

To keep things properly grouped together, you need to group on the date from get_date, since this is our starting point. Remember, it's for each date value in get_date that you get 5 different dates in get_value, so we obviously can't group by get_value.date. Then you obviously also need to group by ticker, since these are different groups of data. And finally, when everything gets grouped together properly, you also state that you want the average for each such group of 5 days.

The last part, HAVING COUNT(*) = 5 just means that you tell the DBMS that you don't want the last couple of rows where you reach the end of the data series. The last date in the table will have only 1 row to join against since there are no future days yet, the 2nd highest date has only two rows to join with etc. HAVING works pretty much like WHERE does, except it operates on data AFTER the query has completed retrieving results, which also means that you should never use HAVING when you could use WHERE (even though you always can).
The reverse is that you should always use WHERE instead of HAVING if you can, but you cannot always do this. In this case, the database cannot know, in advance, how many rows will be retrieved until after all rows have been retrieved, and that is why we have to stick with HAVING to discard them after they where selected.

could u design the query as per the column names

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

johanafm

You only need to do simple substitution of my variable names. tdate is replaced by what you call your date field, i.e. _date. ttable is replaced by what you call your table. And then, for each field that you want to calculate the average, you put in one occurance of AVG(get_value.val) and then replace val by the name of the column you want to calculate an average for.

What have you tried? What didn't work? What's the error message?

rohitb

johanafm;10985573 wrote:
You only need to do simple substitution of my variable names. tdate is replaced by what you call your date field, i.e. _date. ttable is replaced by what you call your table. And then, for each field that you want to calculate the average, you put in one occurance of AVG(get_value.val) and then replace val by the name of the column you want to calculate an average for.

What have you tried? What didn't work? What's the error message?

SELECT get_date.date, get_value.ticker, AVG(get_value.close) FROM mcx_eod_data AS get_date INNER JOIN mcx_eod_data AS get_values ON get_date.ticker = get_value.ticker AND get_value.date >= get_date.date AND get_value.date < DATE_ADD(get_date.date, INTERVAL 5 DAY) GROUP BY get_date.date, get_value.ticker HAVING COUNT(*) = 5

this is the query as per the column names of my table but the error i get is Unknown column 'get_value.ticker' in 'field list' when i execute the query through phpmyadmin

rohitb

i have checked the structure of the table and the column name is ticker so no issues with the column name

Derokorian

... mcx_eod_data AS get_values ON get_date.ticker = get_value.ticker ...

You are assigning the alias as get_values and trying to refer to it as get_value. Either add an s to the alias as used with column names OR remove the s from the alias assignment.