1

In Google Finance, none of the the returns are working for me. "return1" - One-week total return. "return4" - Four-week total return and so on are not working. I'm wondering if there's a formula that can spit out the same information. For example: what is the 1 week return on AAPL from 9/2/19. TIA

David S
  • 21
  • 1
  • 2
  • the docs indicate the return1 (etc) features are for mutual fund data. not for all stocks. =GOOGLEFINANCE(A9, "price") gives me current price (less the 20 minute delay) =GOOGLEFINANCE(A9, "returnday") gives me #N/A error. =GOOGLEFINANCE(A9, "price", "returnday") gives me #VALUE! error. I haven't seen a working example. Starting to wonder if the feature is broken / deprecated or never worked. I've seen a lot of other google features quietly withdrawn. – CodingMatters Jun 29 '20 at 22:52

2 Answers2

0

Try this

=GOOGLEFINANCE("GOOG", "price", DATE(2019,9,2), TODAY(), "WEEKLY")

You're free to modulate the price attribute by any other as listed here - https://support.google.com/docs/answer/3093281?hl=en

Sourabh Choraria
  • 2,255
  • 25
  • 64
  • @David S - did this formula help? – Sourabh Choraria Sep 24 '19 at 03:35
  • No, it gave the prices of the stock at the end of each week and not the percentage it went up or down. Thank you for your response – David S Sep 24 '19 at 12:03
  • I see. Would you mind just showcasing the expected output on a spreadsheet and sharing a screenshot of it here? I bet that would be able to help us analyse what exactly would we want the data to be represented as. – Sourabh Choraria Sep 24 '19 at 16:11
0

If it is the percentage change you are looking for then the following code should work just fine:

=IFNA(MULTIPLY(MINUS(DIVIDE(INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()); 2;2); INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()-INDIRECT(ADDRESS(ROW(); COLUMN()+1))); 2;2)); 1); 100); IFNA(MULTIPLY(MINUS(DIVIDE(INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()-1); 2;2); INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()-1-INDIRECT(ADDRESS(ROW(); COLUMN()+1))); 2;2)); 1); 100); MULTIPLY(MINUS(DIVIDE(INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()-2); 2;2); INDEX(GOOGLEFINANCE(INDIRECT(ADDRESS(ROW(); COLUMN()+2)); "price"; TODAY()-2-INDIRECT(ADDRESS(ROW(); COLUMN()+1))); 2;2)); 1); 100)))

enter image description here

All you have to do is use the same structure as in the image attached. The code should be placed under the "CHANGE(%)"-title, the desired interval in days under the "-DAYS"-title and the stock or index ticker under the "SYMBOLE"-title. The "NAME"-title however is not necessary. You can also modify the color of the cell depending on the output (red= negative & green= positive), by going to "FORMAT"-->"CONDITIONAL FORMATING"

I have also taken the weekend days in account by subtracting 1 or 2 (depending on what weekend day it is) from the current date if the code returns #N/A. Note that this code may be written more effectively, and for the 1 week period (5d) it did not return accurate results, but for the 1 month (28d) period it returned very accurate results. But as far as for your request, i think it gives you a pretty good insight.

Abdalla Roda
  • 607
  • 1
  • 6
  • 13