How to check stock market is open with Google finance (sheets)

Question

Hi I'm using the GOOGLEFINANCE command in google sheets to return stock prices which is working but when the market is closed it still returns a value (last trade price) - I only want to return a price when the market is open. (this could be USA market or London market etc. any supported ticker)

is there any way of checking if the stock market is open or closed in a google sheet?

thanks

Answer 1

There is a way with GOOGLEFINANCE, you can get the last date of trading, you can compare it to today, and with a IF statement you can decide to display the value or 0.

You may need to adapt the date comparison based on your language but here is the formula that worked for me

=IF(LEFT(GOOGLEFINANCE("AAPL";"tradetime");10) = TEXT(TODAY();"dd/mm/yyyy");GOOGLEFINANCE("AAPL";"change");0)

Answer 2

No, there isn't a way using GOOGLEFINANCE formula. It is not a value the formula will return. A workaround is to use IF or IFS and a list of the exchanges opening and closing times that you can use in order to only get the prices when the time is between the limits in your list.