CODEWITHSUNDEEP
google-sheets-formulagoogle-finance
lance
lance

Reputation: 3

google sheets import year low

I am trying to import low and high of the year to google sheets and I see incorrect data been pulled

=MIN ( INDEX ( GOOGLEFINANCE ("AAPL","low",DATE(YEAR(TODAY()) -8, MONTH(TODAY()), DAY(TODAY())), TODAY()),0,2))

the above is the code I use, when I run this for individual year (from year 8 to 1). 2016 gives 24.11 2017 gives 35.5 2018 gives 35.5 2019 gives 48.15 if I check the list for the lows, I see the year 2016 is correct, but the year 2017 is incorrect and should be 28.69. for 2018 should be 37.6 get an incorrect value, same with 2019 etc.

any idea what I am doing wrong here. also if I run the code with "high" and insted min, I use max, I get incorrect values as well any idea?

Upvotes: 0

Views: 51

Answers (2)

najj
najj

Reputation: 29

Instead of using TODAY() which points to current date, you can try to enter a fixed date within the year you're interested in for example:

=MIN(INDEX(GOOGLEFINANCE("AAPL","low",DATE(2017, 1, 1), DATE(2017, 12, 31)),0,2))

You may also add IFERROR to your syntax to handle situations where no data is returned, this will help you know what error you're getting. Cheers! Hope this helps!

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 30240

Syntax

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date])
  • say the year you're picking is 2017, then the end_date should be Dec 31 2017 but currently your formula is pointing to today(), which is July 2024
  • the start_date should be Jan 1 2017 but again your formula is pointing to July 2017 as the start

Upvotes: 0

Related Questions