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
0
votes
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
2 Answers
0
votes
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
0
votes
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)))
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.
