1

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

coder787
  • 21
  • 5
  • 1
    What are the parameters you are passing to the function? And, you can use simple IFS if you know the opening and closing time of the exchanges you are interested in – Aerials Oct 22 '20 at 11:19
  • *"is there any way of checking if the stock market is open or closed in a google sheet?"* **No**. Unless **you** know the times, enter them manually and follow @Aerials logic. – marikamitsos Oct 22 '20 at 11:26
  • it's just a simple command like this =GOOGLEFINANCE("NASDAQ:MSFT", "price") in a cell. – coder787 Oct 22 '20 at 12:04
  • opening and closing times are one thing, but there are for instance bank holidays in UK, USA holidays etc. so I guess it gets complicated and many exchanges supported by Google finance. :( – coder787 Oct 22 '20 at 12:09
  • 2
    a shortcut idea I just had was to use =GOOGLEFINANCE("NASDAQ:MSFT","tradetime") to get the last trade time and compare this to current time, this would give an idea of if the market was open but not foolproof I think. working on it. – coder787 Oct 22 '20 at 12:17

2 Answers2

0

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.

Aerials
  • 4,231
  • 1
  • 16
  • 20
0

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)

Kanjiroushi
  • 223
  • 2
  • 4
  • thanks, as I mentioned in the comments earlier I ended up doing this solution but the 'tradetime' for some stocks is sometimes showing as the day before even though the market is open today. where the 'tradetime' comes from is a mystery and no details are given by google at all and makes no sense to me how it can be delayed so much (less volume stocks obviously but still surprising) – coder787 Feb 17 '21 at 22:33
  • @coder787 some mutual funds only trade once per day after market close - an example would be a Fidelity fund like FDGRX – Matt Billenstein Sep 06 '21 at 20:41