Google Sheets Cell Formatting Conditional on Cell Change (GOOGLEFINANCE)

Question

I am trying to achieve the following: I have a google sheet with a stock price pulled from =GOOGLEFINANCE("ticker", "price"). I want that cell containing the stock price to flash green or red conditional on that cell updating with a higher or lower price as before.

I want to avoid having any form of helper cells, storing old values of the price, etc.

Is this something a script would achieve?

Thanks a lot for your help, everyone!

Probably not because there is no trigger that responds to changes in data. — Cooper, Jan 29 '21 at 21:59
Yes, you can do it by script if you mirror your sheet with `=importrange` into a different sheet /spreadsheet and that use the onChange trigger - see [here](https://stackoverflow.com/a/59370962/11599789). — ziganotschka, Feb 01 '21 at 08:53

score 0 · Answer 1 · edited Mar 08 '23 at 13:33

I think you could use some params from googlefinance command, for example, you need a current value and an old value to compare, so get old values on googlefinance you use like that:

GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())

and to the current value:

GOOGLEFINANCE("GOOG","price")

now you need add conditional rules

So you need to select the cell where you need to apply the condition, so click on 'format' (bar menu) and click finally on 'conditional formatting' and apply your rules on the side menu that will open.

Docs:

Google Sheets Cell Formatting Conditional on Cell Change (GOOGLEFINANCE)

1 Answers1