GoogleFinance: Get History By Month

user_190925 0
Viewed 335

I use GoogleFinance quite a bit, but from the documentation it seems the only formats to get historical data is by Day or Week.

Is there any way to get the data by Month?

I guess I could add an extra column, and find the end of each month date to get the value using some calcs, but maybe there's a simpler way to do it?

google-query-language google-finance google-sheets text-formatting arrays
2 Answers

try:

=ARRAYFORMULA(SORTN(TEXT(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), DATE(2021,2,1), "daily"), 
 {"yyyy mm", "@"}), 9^9, 2, 1, 0))

enter image description here

user_56326290

You can set the start and end date, use QUERY to remove headers, then take the AVERAGE

=AVERAGE(
  QUERY(
    GOOGLEFINANCE(
      "NASDAQ:GOOG", "price", DATE(2020,1,1), DATE(2020,2,1), "DAILY"
    ), 
    "SELECT Col2 OFFSET 1"
  )
)

From inner to outer:

  • GOOGLEFINANCE returns a list of the prices each day from Jan 1 2020 to Feb 1 2020.
  • QUERY selects Col2 with the prices and offsets the data from GOOGLEFINANCE so that the headers are no longer included. Now you have a simple list of numbers
  • Finally AVERAGE just calculates the average.

Reference

user_104450170
Related