Getting Stock Price using GoogleFinance formula in Google Sheet

Do you know that you can actually pull all the historical stock price data using GoogleFinance formula in Google Sheet?
This is a powerful tool where you can analyze the stock price and many more info as well.

It can check for:

Stock Price
The Current Day's High Price
The Current Day's Low Price
PE Ratio
Volume
Beta
EPS
The 52-week high price
The 52-week low price
The number of outstanding shares

Refer here for the complete list: https://support.google.com/docs/answer/3093281?hl=en

Sample Usage

GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())
GOOGLEFINANCE(A2,A3)

Syntax

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
  • ticker - The ticker symbol for the security to consider.
    • Note: Reuters Instrument Codes are no longer supported. For example, ticker 123.TO or XYZ.AX would not work. Instead, use TSE:123 or ASX:XYZ.
    • Recommended: Add an exchange to avoid discrepancies. For example, use “NASDAQ:GOOG” instead of “GOOG." If an exchange is not specified, GOOGLEFINANCE will use its best judgement to choose one for you.


I have created a sample here for Google Stock:

https://docs.google.com/spreadsheets/d/1uFWr9bkjLIFpISNXc12tbUfsFxfSTvi29RF3MKhPFpA/edit?usp=sharing


Let's say I want to check the stock price from 2 Jan 2018 to 16 Jan 2018, I can just use a formula:

=GOOGLEFINANCE("GOOG", "all","1/1/2018","1/16/2018","daily")



DateOpenHighLowCloseVolume
1/2/2018 16:00:001048.341066.941045.2310651237564
1/3/2018 16:00:001064.311086.291063.211082.481430170
1/4/2018 16:00:0010881093.5710841086.41004605
1/5/2018 16:00:0010941104.2510921102.231279123
1/8/2018 16:00:001102.231111.271101.621106.941047603
1/9/2018 16:00:001109.41110.571101.231106.26902541
1/10/2018 16:00:001097.11104.61096.111102.611042793
1/11/2018 16:00:001106.31106.531099.591105.52978292
1/12/2018 16:00:001102.411124.291101.151122.261720533
1/16/2018 16:00:001132.511139.911117.831121.761575261


And here is a sample of Google Share Price chart that I plotted in Google Sheet from 1 Jan 2018 to 21 Nov 2019.







Check the price data from Investing.com do see similar data:




This is the chart for the past 30 days:

Post a Comment

5 Comments

  1. I cant seem to find the codes that work with the bursa malaysia listings. Is there any site that shows the names of the companies listed in the KLSE that work with google finance function?

    ReplyDelete
  2. same here... im also looking the ways that can check bursa malaysia stock price

    ReplyDelete
    Replies
    1. Hi, you may use this formula in google sheet to get the stock price, since Malaysia Stock.biz (delay 15 min) is quite famous now. If you want to know the meaning of the code, please google search.
      =ImportXml("https://www.malaysiastock.biz/Corporate-Infomation.aspx?securityCode=7160", "//*[@id='MainContent_lbQuoteLast']")

      Delete
  3. It works! Thank you, have been looking for a way to display market price.

    ReplyDelete

What do you think of the post?

Kindly share your input, we want to hear from you.