CODEWITHSUNDEEP
google-bigquery
DataDives
DataDives

Reputation: 149

Trying to calculate EMA (exponential moving average) using BigQuery

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: http://www.iexplain.org/ema-how-to-calculate/

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.

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

This is the json response from the above query: https://json.datadives.com/01843e08d5127e9d26d03fe0f842e735.json

Upvotes: 2

Views: 2305

Answers (2)

Jordan Tigani
Jordan Tigani

Reputation: 26637

This isn't a full answer, but rather than doing a self JOIN to get the current date and the previous date, you can use the LEAD() function to get the previous day's time and price rather than doing the multiple joins. This can lead to more natural code and faster queries.

For example:

SELECT <calculate avg here> FROM (
SELECT DATE(time) as date, ROUND(AVG(price),2) as avg_price, 
  LEAD(time, 1) OVER (ORDER BY time DESC) as prev_day_time,
  LEAD(price, 1) OVER (ORDER BY time DESC) as prev_price,
  LEAD(time, 2) OVER (ORDER BY time DESC) as prev_2_day_time,
  LEAD(price, 2) OVER (ORDER BY time DESC) as prev_2_price,  
FROM [...])

That said this only works if you have a bounded moving average (i.e. you're only going to keep track of moving average for the last N days. If you are trying to chain the moving average indefinitely, SQL is not going to be a good solution.

Upvotes: 1

Tjorriemorrie
Tjorriemorrie

Reputation: 17280

I don't think you take the previous day avg_price. You need to take the previous day EMA.

enter image description here

I would rather calculate the EMA upfront; if not possible, maybe look into MapReduce.

Upvotes: 2

Related Questions