0

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?

0

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

0

2
  • 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 '19 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 '19 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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