CODEWITHSUNDEEP
google-finance
JGPARK
JGPARK

Reputation: 57

How monthly moving average in google sheets?

I am trying to make a system that update moving average from google sheets.

Such as 3 months moving average. Average of 2022-03-13, 2022-02-13, 2022-01-13.

The problems are below:

  1. GoogleFinance function does not provide "monthly" interval.
  2. It excludes market-closed day of course when I try to get "daily" information.

So my idea is to use "weekly" and extract only 12 weeks such as when I try to get 3 months moving average. I took 1 month as 4-weeks simply.

=average(query(sort(GoogleFinance("QQQ","price", TODAY()-320, TODAY(),"weekly"),1,0),"select Col2 limit 12"))

I think this way is incorrect at some points. What's the better way to get it?

Upvotes: 0

Views: 1442

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

The exact value should be 364,64 with the selection of date :

=average(query(GoogleFinance("QQQ","price", TODAY()-93, TODAY(),"daily"),"select Col2 where Col1>DATE'"&TEXT(date(year(today()),month(today())-3,day(today())),"yyyy-MM-dd")&"'  "))

359,34 with (12 weekly values) :

=average(query(sort(GoogleFinance("QQQ","price", TODAY()-93, TODAY(),"weekly"),1,0),"select Col2 limit 12"))

and 363,79 with (60 daily values) :

=average(query(sort(GoogleFinance("QQQ","price", TODAY()-93, TODAY(),"daily"),1,0),"select Col2 limit 60"))

enter image description here

Upvotes: 1

Related Questions