Google Finance does not show last closing price after market hours. It just shows last *traded* pric-CodePudding

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

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.

CodePudding user response：

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))

CodePudding user response：

This is another way of achieving the result.

=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)

When I compared performance of both the solutions =LAMBDA and =QUERY on 2500 stocks, I found query to be more faster than lambda.

Cheers!

However, my main query remains unresolved as no formula of google finance gives today's close until the actual day is over.