Realtime data in Sheets app in mobile.

In Pi one can send realtime data to an Excel spreadsheet. Is it possible to view 1 minute realtime data (values only not chart) in mobile while trading with kite.zerodha app as one can do in Google Drive Spreadsheet by pulling data like this importData(concatenate(“https://finance.google.com/finance/getprices?q=NIFTY&x=NSE&i=60&p=1d&f=d,c,v,o,h,l”))…I need it of first 15 minutes only…google’s data comes very late.
I have doubts but in mobile with sheets app one can work offline…all I want is to pull/transfer data somehow to Sheets…to do some calculations on the spot.
Thanks & regards

If you can elaborate your calculations, I might be able to integrate it start to end … :slight_smile:

No earth shaking idea…just need this type of data of first 15 minutes real time in mobile


Thanks

Hmm. That is real-time data for exchanges - NSE and BSE. US/UK based exchanges have 20 minutes delay - Not indian. I use it all the time via the google spreadsheet mechanism you are talking about.

For mobile, I think it will load a cached state which is why you are seeing the issue. You can use the following under File > SpreadSheet Settings > On change and every minute - for auto-refresh and avoid those stale data.

44%20pm

You can also try and check GOOGLEFINANCE functions rather than calling it from URL. It is more direct.

sir, google finance data is delayed about 15 minutes. can i directly capture the data from NSE website into google finance sheet. but the problem is it is updated on the same cell.

i want to take a snapshot of the data and store it elsewhere every 15 minutes. so that i can know what is happening in the last 15 minutes with reference to previous 15 minutes. like SHORT COVERING, LONG UNWINDING etc.

please help if possible.

Thanks…i don’t have access to a desktop or laptop, can do whatever is possible by using my android phone. One can view Drive Spreadsheets, but to be able to do editing etc one has to use Google Sheets App which has limitations…so the settings etc also are not visible…will figure out something…Google Spreadsheet attributes pulls eod data. To get delayed minute by minute data one needs to fetch using, importdata, importhtml, importurl etc etc…thats why using url…don’t know of any other simpler way of downloading data…thanks anyways.

Check this link - Disclaimer – Google Finance
NSE and BSE are listed as realtime data feeds. I will double check during market hours though …

You need OI data to determine / differentiate that … depending only on price/volume is not that reliable herein.

Hmmm … Got your point … Did you try - https://support.google.com/docs/answer/3093281?hl=en

can we capture Open Interest data for futures and options from NSE to google finance excel.

Yup … Google Spreadsheet has many functions -

  1. IMPORTDATA: Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format.
  2. IMPORTXML: Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
  3. IMPORTRANGE: Imports a range of cells from a specified spreadsheet.
  4. IMPORTHTML: Imports data from a table or list within an HTML page.
  5. IMPORTFEED: Imports a RSS or ATOM feed.

Explore and one of them should do the trick. The URLs to import from for OI -

Futures -
https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=DLF&instrument=FUTSTK&expiry=28JUN2018&type=-&strike=-
Options -
https://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=2622&symbol=DLF&symbol=DLF&instrument=-&date=-&segmentLink=17&symbolCount=2&segmentLink=17

Change the symbol in the URL to get the OI data for the stock in question.

IMPORTHTML(“url”,“table”,0)
Something like this…don’t know whether it auto or not

Importhtml(“https://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=-10006&symbol=NIFTY&symbol=NIFTY&instrument=-&date=-&segmentLink=17&symbolCount=2&segmentLink=17","table”,0)

1 Like

Yeah … That should do it … Just check the screenshot i pasted above if you wish to have auto-refresh every minute.

DOESN’T WORK!!

There are some inverted quotes parsing mistakes in @Amukh formula … but it works like a charm dude … Next time … Try harder … :wink:

=IMPORTHTML(“https://www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbolCode=2622&symbol=DLF&symbol=DLF&instrument=-&date=-&segmentLink=17&symbolCount=2&segmentLink=17","table”,0)

For reference -

Cheers!

thanks sir. but can this be done for FUTURES. Because option chain we can access through the table. but futures open interest how to access.

Another simpler URL
https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbol=NIFTY

You can use data validation in Excel to create a drop down menu and change the symbol part…suppose your dropdown in in CELL A1…you can replace NIFTY in the URL with this

https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbol="&A1&"

So you can just check all options data at one place without typing it.

So the final will look like this
IMPORTHTML(“https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?symbol="&A1&"","TABLE”,0)