How to fetch accurate monthly closing data from Google Finance in Google Sheets?

Question

I am trying to fetch historical monthly closing Google Finance data of a stock into Google Sheets using the formula :-

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

However, this formula is not displaying accurate monthly closing prices in Google Sheets.

Is there a different formula or function that I can use to fetch accurate monthly closing data from Google Finance into Google Sheets?

I tried using:-

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

But it is showing incorrect monthly closing price of a stock.

Answer 1

• Enter the start date and end date in cells A1 and A2,
• then try this formula:

Note: Cells A1 and A2 can be removed, and the dates can be hard-coded in the formula if necessary.

---

=query({
    {"Date";ArrayFormula(eomonth(EDATE(DATE(year($B$1),month($B$1),day($B$1)),SEQUENCE(DATEDIF(DATE(year($B$1),month($B$1),day($B$1)),DATE(year($B$2),month($B$2),day($B$2)),"M"),1,0)),0))},
    {"Price";MAP(
        ArrayFormula(eomonth(EDATE(DATE(year($B$1),month($B$1),day($B$1)),SEQUENCE(DATEDIF(DATE(year($B$1),month($B$1),day($B$1)),DATE(year($B$2),month($B$2),day($B$2)),"M"),1,0)),0)),
        LAMBDA(dates,index(GOOGLEFINANCE("NASDAQ:GOOG", "close",dates,), 2,2))
    )}}, 
    "select Col1, Col2"
)

---

Logic

• Generate a SEQUENCE of dates: =ArrayFormula(eomonth(EDATE(DATE(year($B$1),month($B$1),day($B$1)),SEQUENCE(DATEDIF(DATE(year($B$1),month($B$1),day($B$1)),DATE(year($B$2),month($B$2),day($B$2)),"M"),1,0)),0))

h/t @TomSharpe List each month between date range. Google Query

• GOOGLEFINANCE to return prices for given date array:

=MAP( ArrayFormula(eomonth(EDATE(DATE(2020,1,1),SEQUENCE(DATEDIF(DATE(2020,1,1),DATE(2021,2,1),"M"),1,0)),0)), LAMBDA(dates,index(GOOGLEFINANCE("NASDAQ:GOOG", "close",dates,), 2,2)) )

h/t www.liveflow.io: How to Use MAP Function in Google Sheets

• Wrap both arrays in a QUERY

---

Results

---