0

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.

Sabha
  • 621
  • 10
  • 32
  • Your index one will fail, if there are holidays intervening. – TheMaster Sep 18 '22 at 15:59
  • oh! i never considered that. Does that mean query is also another way of doing it? (I am not sure if I wrote it properly - you may correct it if required)? So if there is a large amount of data to be processed every minute, then which method would you prefer? – Sabha Sep 18 '22 at 16:09
  • Query should work(not tested). I don't know, which is faster, especially with `lambda` being a new function. query is usually faster, but if you're going to call it once per cell, I'm not so sure. Test and let me know. – TheMaster Sep 18 '22 at 16:20

2 Answers2

2

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))
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thank you for your reply. I never used `lambda` function so far and I was also trying to check if there were alternative methods and I came up with two more ways. Now I am going to check result of these three formulas tomorrow during live market and market close as today is stock market holiday. I will update my question with the two alternative methods so that it will be useful for other visitors to this question. Meanwihle, I am going to study the `lambda` function you have used. Thank you very much for giving me a start. See you tomorrow. Cheers ! – Sabha Sep 18 '22 at 15:42
  • @Sabha Great. If you have new answers, add them as answers instead. Don't pollute the question. – TheMaster Sep 18 '22 at 15:54
  • Sure... i will learn the method you suggested and also check the output tomorrow and then I will add a new answer and accept your answer too. For now I just upvoted your answer. I did not add the query formula as answer because I did not know if I wrote it correctly. I also tried the `=filter` along with `=large` function to achieve this result but was not successful. Haven't given up yet :) Thanks – Sabha Sep 18 '22 at 16:11
  • Ok... Monday market is closed and both the formulas are still showing Friday Close. I was looking for a formula which will give me today's close immediately after market close on that day. Today's close was 2502.45 for Reliance stock. So now if this is not possible, may i request someone to help with my scraping which was the second part of my initial question? Thanks. – Sabha Sep 19 '22 at 11:05
  • @Sabha I'm not getting any data from that website. That site maybe blocking requests. Furthermore, scraping is against their terms of service. Maybe see if any apis available? – TheMaster Sep 19 '22 at 11:45
  • I just checked, there is just an hyphen (-) in the close column. looks like the close data is not updated on the official website and they may update it later. Regarding api, I had already searched for free api but did not find any. maanavshah has made it available named stock-market-india on github and another one by vsjha18 named nsetools in github but it was too technical for me to understand and therefore I went towards the scraping way – Sabha Sep 19 '22 at 12:04
  • just wanted to let you know that the data is now updated on the official site. Appreciate if you could help me with scraping as I dont see any other way to get today's close today immediately after market close. Thanks – Sabha Sep 19 '22 at 14:40
1

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.

Sabha
  • 621
  • 10
  • 32