Im trying to calculate a moving average, however I cant get it to work.

$result = @mysql_query("SELECT  close, FROM am where sdate BETWEEN '2004-03-01' AND '2004-03-30'ORDER BY sdate");     

while($row = mysql_fetch_assoc($result))
{
    $count =+1;
    $total =+$row['close'];
    if(count==5){
    $res=$total/5;
    $array[] = $res;
    $count =0;
    }
}

this will only calculate everage for every 5 days. Im strugling to make it a moving average.

a 5-day simple moving average is calculated by adding the prices for the last 5 days and dividing the total by 5.

10+11+12+13+14 = 60/5

if the next closing price in the average is 15, then this new period would be added and the oldest day, which is 10, would be dropped. The new 5-day simple moving average would be calculated as follows:

11+12+13+14 +15 = 65/5

Im not sure how to drop 10 from the equation and start while loop from 11.

Could anyone advise

    Not quite sure what you mean about droping the 10 but you can do averages in the sql

    SELECT AVG(close) FROM am WHERE sdate BETWEEN '2004-03-01' AND '2004-03-30'ORDER BY sdate LIMIT 5;
    

    HTH
    Bubble

      Thank you for your reply

      SELECT AVG(close) FROM am WHERE sdate BETWEEN '2004-03-01' AND '2004-03-30'ORDER BY sdate LIMIT 5;
      

      Not what Im trying to do.

      Im trying to calculate avg for data in a period of 5 days so I have avg for 1-5 of March then 2-6 of March then 3-7 and so on.

        this will do a rolling average.

        first few days will be averaged by the number of days in days array

        once days array reaches five elements it starts overwriting the oldest

        untested

         
        $count=0;
        while($row = mysql_fetch_assoc($result)) 
        { 
        	if($count==5)
        		$count=0; 
            $days[$count]=$row['close']; 
            $total = array_sum($days);
        	$res=$total/count($days); 
        	$count++;    
        }

          Hi Sid, thanx a lot for your help.

          it works, though dont quite understand why you put $row['close'] into $days[$count] does it create an associative array with $count as keys for 'close' as values?

          $days[$count]=$row['close'];  

          Also I need moving avg for 1-5 of march then 2-6 then 3-7 and so on untill the end of the month(e.g between 1 of march to 30th). but it should be one result for every 5 days.

             $days[$count]=$row['close']; 

            produces an array with keys 0-4 as count is set to 0 when it reaches 5

             {  
            if($count==5) $count=0;
            $days[$count]=$row['close'];
            $total = array_sum($days); $res[]=$total/count($days);
            $count++;
            } for($i=4;$i<count($res);$i++) echo $res[$i]."<br>";
              Write a Reply...