Having trouble outputting GOOGLEFINANCE data

Question

My wish is to create a table in Google sheets looking something like this:

            price  
day           A         B       C       D ... Z
1997-01-02   132.54     .       .       .     .
1997-01-03   133.01     .       .       .     .
    .          .        .       .       .     .
    .          .        .       .       .     .
    .          .        .       .       .     .
2018-12-28     .        .       .       .     .

First I just tried using the call as:

=GOOGLEFINANCE("STO:ABB", "price", "01/02/1997", "12/28/2018", "DAILY")

However, this returns a 2x2 array with the dates and prices respectively, not including certain dates (presumably for dates in which no price was reported). Ideally, I'd like to create the table as described above, indexed by the dates 01/02/1997-12/28/2018, with N/A values in cells for which no price was reported for that date. Any suggestions?

Answer 1

=ARRAYFORMULA({  TEXT(ROW(INDIRECT("A"&DATEVALUE("1997-1-2")&":A"&DATEVALUE("2018-12-28"))), "dd/mm/yyyy"),
 IFERROR(VLOOKUP(TEXT(ROW(INDIRECT("A"&DATEVALUE("1997-1-2")&":A"&DATEVALUE("2018-12-28"))), "dd/mm/yyyy"), 
 {INDEX(TEXT(GOOGLEFINANCE("STO:ABB", "price", "02/01/1997", "28/12/2018", "DAILY"), "dd/mm/yyyy"),,1),
  INDEX(     GOOGLEFINANCE("STO:ABB", "price", "02/01/1997", "28/12/2018", "DAILY"),,2)}, 2, 0), "N/A")})

Answer 2

Here is a solution using the LET function released in 2023.

=LET(start,"01/02/1997", end,"12/28/2018", stock,"STO:ABB",
  gf,GOOGLEFINANCE(stock, "price", start, end, "DAILY"),
  dates,SEQUENCE(DATEDIF(start, end, "D"), 1, DATEVALUE(start)),
  SORTN(ARRAYFORMULA(SORT({dates, IFERROR(dates/0,"N/A");
     INT(INDEX(gf,,1)),INDEX(gf,,2)})), ROWS(dates),2,1,1))