5

You can use GOOGLEFINANCE("name", "return52") for mutual funds, but not for stocks. Is there another way to get the same result?

3 Answers 3

9

If the cell is formatted as a NUMBER:

=ROUND( ( GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2) 

Alternative:

=ROUND( ( GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", date(year(today())-1,month(today()),day(today()))) , 2,2) - 1 ) * 100 ,2)

If the cell formatted as PERCENT:

=GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", datevalue(today()-365) ) , 2,2) - 1

Alternative:

=GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", date(year(today())-1,month(today()),day(today()))) , 2,2) - 1
1
  • I just found this answer, and it’s exactly what I was looking for! Thanks. That said, I’m a little confused as to how everything in the formula works. Specifically, can you tell me why the “2,2) - 1” at the end is necessary? Commented Jul 26, 2021 at 4:54
1

Another alternative is using the opening price at the beginning of the week.

=ROUND( ( GOOGLEFINANCE(A$4,"price") / index(GOOGLEFINANCE(A$4,"open", datevalue(today()-7) ) , 2,2) - 1 ) * 100 ,2)
1
  • 1
    Generally on StackExchange an upvote is all the thanks one needs. Answers are generally reserved for your unique answer to the original question. Side note: The down vote isn't mine; just explaining the likely reason someone clicked down.
    – LabGecko
    Commented Mar 29, 2020 at 19:34
1

Added IFERROR this works better:

=IFERROR(ROUND((GOOGLEFINANCE(A2, "price")/index(GOOGLEFINANCE(A2,"open",datevalue(today()-365)), 2,2)- 1)*100,2))
1
  • Additionally, =IF(ISBLANK(A2),"",<expression>)
    – RSW
    Commented Dec 28, 2023 at 8:21

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