google-sheetsstockgoogle-financestockquotesgoogle-finance-api

Google Finance does not show last closing price after market hours. It just shows last *traded* price instead of *closing*


My query is on stock market of India where I fetch the quotes from =googlefinance function in my google sheet. I was very disappointed to know that the function does not show last closing price. Instead, it shows the last traded price.

Lets take an example of a stock RELIANCE: =GoogleFinance("NSE:reliance", "price") result is 2498.00 which is incorrect. Actual closing on Friday, 16 Sep 2022 was 2499.20 as per official National Stock Exchange of India. You may refer to this link for the Close price and the screenshot below

The incorrect quote 2498.00 is the last traded price before market closing The correct quote is 2499.20 when the markets were closed.

This behavior is common across all stocks of Indian stock market. I have used almost all the parameters those are available in the =googlefinance function but I could not find a way to get the correct stock quote.

Then I decided to scrape the value from the official website using the following without success

<div id="closePrice">2,499.20</div>

//*[@id="closePrice"]

/html/body/div[2]/div[4]/div[2]/div[1]/div[4]/ul/li[6]/div[1]

document.querySelector("#closePrice")

I searched a lot of valuable articles on this site and also on google but could not find a solution which would fetch the desired result. Can someone help me with a way to get 2499.20 as a result for RELIANCE stock either using =googlefinance function or by scraping the value from office website?

Thank you.

enter image description here

Solution suggested by @TheMaster (Thank you so much!)

=LAMBDA(gf,INDEX(gf,ROWS(gf),2))(GOOGLEFINANCE("NSE:RELIANCE", "close",TODAY()-5,15))

Two other ways

=index(GOOGLEFINANCE("NSE:reliance","Close",today()-5,15),4,2)

=query(GOOGLEFINANCE("NSE:reliance","Close",today()-5,15),"select Col2 where Col1 < date '"&TEXT(today()+1, "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)

I am also keen to know which one of these or other methods (if any) will get the results faster without crashes or any other processing issues as my googlesheet is loaded with thousands of stock quotes which will be refreshed every 1 minute.


Solution

  • Only historical prices support close attribute. Use the start date argument to get historical prices and get the last close:

    =LAMBDA(gf,INDEX(gf,ROWS(gf),2))(GOOGLEFINANCE("NSE:RELIANCE", "close",TODAY()-5,15))