3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
maxhugen
  • 1,870
  • 4
  • 22
  • 44

2 Answers2

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
1

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

iansedano
  • 6,169
  • 2
  • 12
  • 24
  • The use of QUERY to get rid of the Headers was helpful, although the solution from @player0 was the answer I needed. Cheers. – maxhugen Jul 09 '21 at 01:26