Watch a video on using Google Docs for stock market research.
You can get stock market quotes and other data updated to your spreadsheets by using the GoogleFinance function.
To use the GoogleFinance function, enter the following formula in a spreadsheet cell:
Syntax:
Syntax: =GoogleFinance("symbol"; "attribute");
where"symbol"represents the stock symbol of the company or mutual fund you're looking for (like GOOG, QQQ, XXXXX, and so on), and"attribute"represents the type of market data that you want (like Price, Volume, and so on). If the attribute is left blank, price is returned by default.
Here are a few examples:
- To insert the current volume of Google stock:
=GoogleFinance("GOOG"; "volume") - To insert the current price of Google stock:
=GoogleFinance("GOOG") and =GoogleFinance("GOOG"; "price") - Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
=GoogleFinance(A2; B1)
In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2.
Note: In order for the formula to compute properly, quotation marks must be used around both the "symbol" and the "attribute."
Tips:
The GoogleFinance function now has the ability to show historical data. This provides an easy way to track the performance of any stock over a certain time period.
See instructions on how to get historical stock data.Use this function:
Syntax: =GoogleFinance("symbol", "attribute", "start_date", "num_days|end_date", "interval") where:
- "symbol" - stock symbol
- "attribute" - high, low, open, close, vol, or all (quote also works, and defaults to close).
- "start_date" - the date for the historical data. When only the start_date is specified, the historical data is just for that day.
Note: The remaining parameters are optional.
- "num_days" | "end_date" - can be either the end date for the time period over which you want to see historical data, or the number of days from the start date. Any number less than 50 is considered to be num_days. Otherwise it is considered an end_date.
- "interval" - this specifies the interval "DAILY" or "WEEKLY," or 1 and 7 can also be used. This is the granularity at which the stock data is shown at daily or weekly intervals. Please note that queries for recent dates may not have information, as historical data may not be available for a day or two.
- Stock quotes and other data may be delayed up to 20 minutes. Information is provided "as is" and solely for informational purposes, not for trading purposes or advice. For more information, please read our Stock Quotes Disclaimer.
- You can enter 250 GoogleFinance functions in a single spreadsheet; two functions in the same cell count as two.
View a list of common attributes
The following types of market data are currently available:
- price: market price of the stock - delayed by up to 20 minutes.
- priceopen: the opening price of the stock for the current day.
- high: the highest price the stock traded for the current day.
- low: the lowest price the stock traded for the current day.
- volume: number of shares traded of this stock for the current day.
- marketcap: the market cap of the stock.
- tradetime: the last time the stock traded.
- datadelay: the delay in the data presented for this stock using the googleFinance() function.
- volumeavg: the average volume for this stock.
- pe: the Price-to-Earnings ratio for this stock.
- eps: the earnings-per-share for this stock.
- high52: the 52-week high for this stock.
- low52: the 52-week low for this stock.
- change: the change in the price of this stock since yesterday's market close.
- beta: the beta value of this stock.
- changepct: the percentage change in the price of this stock since yesterday's close.
- closeyest: yesterday's closing price of this stock.
- shares: the number of shares outstanding of this stock.
- currency: the currency in which this stock is traded.
View a list of attributes for mutual funds
- closeYest: the NAV of a mutual fund.
- date: date at which NAV (net asset value) was reported.
- returnytd: year-to-date return total.
- netassets: The day-end net assets of the mutual fund. Net-asset figures are useful in gauging a fund's size, agility, and popularity. They help determine whether a small company fund, for example, can remain in its investment-objective category if its asset base reaches an ungainly size.
- change: change in NAV value between the most recent reported NAV, and the NAV prior to that.
- changepct: the % change in the NAV.
- yieldpct: Also known as the distribution yield, Morningstar computes this End Yield figure by summing the trailing 12-month's income distributions and dividing the sum by the last month's ending NAV, plus capital gains distributed over the same time period. Income refers only to interest payments from fixed-income securities and dividend payments from common stocks.
- returnday: one-day total return.
- return1: one-week total return.
- return4: four-week total return.
- return13: thirteen-week total return.
- return52: 52 week total return.
- return156: 156 week total return.
- return 260: 260 week total return.
- incomedividend: the amount of the most recent cash distribution for the fund.
- incomedividenddate: the date the above occurred.
- capitalgain: the amount of the most recent capital gain distribution from the fund.
- capitalgaindate: the date of the above.
- morningstarrating: the Morningstar "star" rating.
- expenseratio: The percentage of fund assets used to pay for operating expenses and management fees, including 12b-1 fees, administrative fees, and all other asset-based costs incurred by the fund, except brokerage costs. Fund expenses are reflected in the fund's NAV. Sales charges are not included in the expense ratio.
Visit our function list for a list of currently supported functions.
The GoogleFinance function is only available in English, and most international market data is not supported. You may be able to use a few of the international symbols without difficulty. If a ticker symbol is the same in the U.S. exchange and an international one, spreadsheets will default to the symbol used in the U.S. exchange. This feature will be updated soon to support more international data/symbols.