title: Trying to calculate EMA (exponential moving average) using BigQuery
body: <p>I am trying to calculate the exponential moving average (EMA) of a stock price. I am using a formula to calculate EMA from this site: <a href="http://www.iexplain.org/ema-how-to-calculate/" rel="nofollow">http://www.iexplain.org/ema-how-to-calculate/</a></p>
  
  <p>I am totally stuck calculating the proper EMA. The query runs and returns results but does not return an accurate EMA. Help is greatly appreciated. You can see the attempt to calculate EMA using the EMA formula starting at line 4.</p>
  
  <pre><code>SELECT current_day.date as current_date, current_day.avg_price as current_date_price,       
  prev_day.prev_avg_price as previous_date_price, prev_2_day.prev_avg_price,
  
  (  ( current_day.avg_price * (2 / (22 + 1) ) ) +  ( ( ( prev_day.prev_avg_price * (2 / (22     
  + 1) ) ) +   
  ( prev_2_day.prev_avg_price * (2 / (22 + 1) ) ) ) * (1 -( 2 / (22 + 1) ) ) ) ) as EMA
  
  
  FROM
  (select DATE(time) as date, ROUND(AVG(price),2) as avg_price FROM ds_5.tb_4978 group by     
  date) as    
  current_day
  JOIN
  (select DATE(USEC_TO_TIMESTAMP(time- 86400000000)) as prev_date, ROUND(AVG(price),2) as    
  prev_avg_price from ds_5.tb_4978 GROUP BY prev_date) as prev_day
  ON current_day.date=prev_day.prev_date
  
  JOIN
  (select DATE(USEC_TO_TIMESTAMP(time- (86400000000*2))) as prev_2_date, ROUND(AVG(price),2)     
  as    
  prev_avg_price from ds_5.tb_4978 GROUP BY prev_2_date) as prev_2_day
  ON current_day.date=prev_2_day.prev_2_date
  
  GROUP  BY current_date, current_date_price, previous_date_price,    
  prev_2_day.prev_avg_price, EMA
  </code></pre>
  
  <p>This is the json response from the above query: <a href="https://json.datadives.com/01843e08d5127e9d26d03fe0f842e735.json" rel="nofollow">https://json.datadives.com/01843e08d5127e9d26d03fe0f842e735.json</a></p>