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

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?

2 Answers 2


=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")})


  • Hej user0 and thanks for your answer! I tried it, and it produced the dates yet each value was N/A. Are there any prerequisites to using the formula such as ordering the dates before? Furthermore I'd like to repeat the process for several other companies, with the end result like the table in my question. Can your formula be extended for let's say a list of companies? Thanks in advance.
    – Daniel
    Jul 30, 2019 at 12:23
  • @Daniel well, N/A value means that there is no data recorded for that given day - eg it's not faulty formula eg. google finance is to be blamed
    – user0
    Aug 29, 2019 at 0:35

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)),
     INT(INDEX(gf,,1)),INDEX(gf,,2)})), ROWS(dates),2,1,1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.