Google Sheets offers a Google Finance function to fetch real time data about a stock you are watching.
The syntax is as follows.
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
Ticker is where you specify the Scrip ID of the stock , be it IRCTC, JSL, TATAMOTORS etc. You should use both the exchange symbol and scrip id seperated by a colon to avoid ambiquity. For example, if you are fetching data of IRCTC stock from NSE, ticker parameter should be NSE:IRCTC
Attribute is an optional parameter. This is where you specify the type of information you need about the selected stock. If you leave it blank, it will return the price by default. As this is a string, you need to enclose the value in quotes.
Supported attributes include
- price – The current market price (CMP) of the stock
- pe – The price to earnings ratio
- eps – Earnings per share
- high – Highest price on current date
- low – Lowest price on current date
- high52 – Highest price in 52 weeks
- low52 – Lowest price in 52 weeks
More attributes can be found here.
Start_date is also optional. The start date when you are fetching historical data.
End_date|num_days is another optional parameter. You can either specify an end_date or number of days from start_date when you are fetching historical data.
Interval, The final parameter is also optional and is used to specify the frequency of returned data. The supported values are DAILY and WEEKLY.
Example 1: Fetch current market price of IRCTC (NSE)
=GOOGLEFINANCE( "NSE:IRCTC", "price")
Example 2: Fetch Price to Earnings (P/E) ratio of IRCTC (NSE)
=GOOGLEFINANCE( "NSE:IRCTC", "pe")
To fetch the Scrip ID from a cell , so that you can apply the same function to other rows as well, as seen in the screenshot, change the function to
=GOOGLEFINANCE( TEXTJOIN("", TRUE, "NSE:",A2), "price")
where A2 is the cell that holds the scrib ID.