Tuesday, January 10, 2012

Automatic Stock Quotes in Google Docs

Here is a list of Common Attributes for automatic stock quotes in Google Docs. This is the starter syntax (put this in any cell): =GoogleFinance(+A2, "Price") where A2 is your Stock Symbol (e.g. AAPL)
  • You can enter a max of 1,000 GoogleFinance functions in a single spreadsheet; two functions in the same cell count as two.
  • If you would like to try using Google Finance in Google spreadsheets, you can use this template to get started. [NOTE: This is not my template but appears to be OK. Source is at the end below.]
STOCKS - The following types of real-time 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.
  • 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.
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.
  • open: the opening price of the stock for the indicated day.
  • close: the closing price of the stock for the indicated day.
  • high: the highest price the stock traded to date.
  • low: the lowest price the stock traded to date.
  • volume: number of shares traded of this stock to date.
  • all: the concatenation of date/open/close/high/low/volume returned as a table for historical quotes.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...