Pro Features

Sheets Market Data also comes with a Pro plan for those who want to dig deeper into the data. The Pro features include:

Note

In order to use the Pro Features, you will have to subscribe to the Pro Plan and setup your license key. For more on how to do that, click here.

Data Range

The Pro features give you the ability to pass a range of symbols or data to any of our functions (except time series), instead of having to just pass in one at a time. This makes it significantly easier to deal with large spreadsheets of data. It also has the added benefit of reducing the likelihood that you bump up against Google’s quota limits.

All you have to do to use this feature is instead of passing in a string value (i.e. "GOOG") or a single cell reference (i.e. A1) for a stock or data argument in our custom functions, you can use a range of cells (i.e. A1:A25). This includes the custom functions outlined in Basic Features as well as all of the functions included below.

Note: you cannot pass a range of data to the sheet generating menu scripts, only to our spreadsheet functions.

How to Use Data Ranges (Video)

Analysis Data

To get analysis data for a particular stock, use the function:

=ANALYSIS_DATA(symbol(s), type, data, timePeriod)

This function takes four arguments. What arguments are required depends on the type of data you want. For instance, if you would like growth estimate data, then only the type and symbol are required. For all other types of data (see list below), the symbol, type, and data arguments are required.

The first argument is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second argument is the category of type of analysis data you would like—as either a string or a cell reference. The next argument is for the type of data you would like within a category or type—as either a string, cell reference or a range of cells. This argument is optional and ignored if you use “growthEstimate” as the type argument, otherwise it is required. The last argument in this formula is for the specific time period you would like the data for (see list of options below)—as either a string or a cell reference—it is not required. If it is not given, it will use the current quarter (“0q”) as the time period.

Below is a full list of each argument option and what data it refers to.

List of type arguments

  • growthEstimate - Analysts’ Growth Estimates
  • earningsEstimate - Analysts’ Earnings Estimates
  • revenueEstimate - Analysts’ Revenue Estimates
  • epsTrend - EPS Trends
  • epsRevisions - EPS Revisions

List of data arguments by type

growthEstimate

None

earningsEstimate

  • numberOfAnalysts - Number of Analysts’ Estimates
  • avg - Average Estimate
  • low - Low Estimate
  • high - High Estimate
  • yearAgoEps - Last year’s EPS
  • growth - Growth of Earnings Estimate

revenueEstimate

  • numberOfAnalysts - Number of Analysts’ Estimates
  • avg - Average Estimate
  • low - Low Estimate
  • high - High Estimate
  • yearAgoRevenue - Last year’s Revenue
  • growth - Growth of Revenue Estimate

epsTrend

  • current - Current Estimate
  • 7daysAgo - 7 Days Ago Estimate
  • 30daysAgo - 30 Days Ago Estimate
  • 60daysAgo - 60 Days Ago Estimate
  • 90daysAgo - 90 Days Ago Estimate

epsRevisions

  • upLast7days - Up Last 7 Days
  • upLast30days - Up Last 30 Days
  • downLast7days - Down Last 7 Days
  • downLast30days - Down Last 30 Days
  • downLast90days - Down Last 90 Days

List of timePeriod arguments by type

  • 0q - Current Quarter (default)
  • +1q - Next Quarter
  • 0y - Current Year
  • +1y - Next Year

The following are only available for growthEstimate:

  • +5y - Next 5 Years (per annum)
  • -5y - Previous 5 Years (per annum)

Historical Data

Sheets Market Data Pro allows you to pull historical data into your spreadsheets in a few different ways. The first way is by creating separate historical data sheets. These sheets can be generated automatically by simply providing a stock’s symbol. The generated sheet includes Date, Open, High, Low, Close, Adj Close, % Change, Volume, and Dividend as far back as data is available–for some stocks as far back as 1970.

historical-data

Generating a historical data sheet is simple. First, navigate to Add-ons > Sheets Market Data > Historical Data > Create Sheet(s).

historical-menu

This will open a dialog box asking you to enter the symbol of the stock you would like historical data for. Enter the symbol of the stock you would like this data for and click Ok. If you would like to generate multiple sheets at once, separate each symbol you want historical data for with a comma.

historical-dialog

This will run the script which will generate a new sheet with all the historical data for the given stock. You are now free to use the data however you see fit. The sheet will auto-update each time you open your spreadsheet with the latest information for you if you leave it in your spreadsheet.

Note: because of Google imposed limits, only 20 sheets will be allowed to auto-generate per spreadsheet.

Another way that Sheets Market Data Pro allows you to use historical data in your spreadsheets is through the below data functions.

Time Series Data

Don’t want to generate an entire sheet of historical data? Then use our time series data function. With this function, you can specify exact historical dates you would like data for.

=STOCK_TIME_SERIES_DATA(symbol(s), data, start, end, interval, order)

This function takes up to six arguments, two of which are required. The first is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these are required.

The next three arguments deal with the timeframe you would like the data. The first of these is the start date—the date you would like the data to start from. If this argument is not provided, then it will use the farthest date in which there is data. The next is the end date—the last date you would like the data for. If this is not provided, the current date will be used. The last argument is the interval for which you would like the data for—1 day, 5 days, etc. If this argument is not provided, a 1 day interval will be used.

Note: The dates should be a string in the following format: M/D/YYYY. If you provide the exact same date for both start and end, then only the data will be returned. For all other cases, the results of the function will automatically include the date.

Finally, the last argument, order, deals with the order you would like the data to be listed in. This argument can be either asc (for ascending order, where the oldest date is first) or desc (for descending order, where the most recent date is first). It should be in a string format using double quotes “”. If this argument is not supplied, it defaults to descending order.

Below is a full list of every available option and what data it refers to.

List of data arguments

  • open - Open
  • high - High
  • low - Low
  • close - Close
  • adjclose - Adjusted Close
  • volume - Volume
  • dividend - Dividend

List of interval arguments

  • 1d - 1 day
  • 5d - 5 days
  • 1wk - 1 week
  • 1mo - 1 month
  • 3mo - 3 months

List of order arguments

  • asc - Ascending order (oldest date first)
  • desc - Descending order (most recent date first)

Income Statements

To get a stock’s annual Income Statement data, use the function:

=INCOME_STATEMENT_DATA(symbol(s), data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these are required. The last argument is the year you would like data for. This last option is optional and if you do not enter a year then it will default to the latest year data is available.

Note: historical Income Statement data go back 4 years. Also, the years correspond with the year a company’s financial year ends.

Below is a full list of every available option and what data it refers to.

List of data arguments

  • endDate - Period Ending Date
  • totalRevenue - Total Revenue
  • costOfRevenue - Cost of Revenue
  • grossProfit - Gross Profit
  • researchDevelopment - Research and Development
  • sellingGeneralAdministrative - Selling General and Administrative
  • nonRecurring - Non Recurring
  • otherOperatingExpenses - Others
  • totalOperatingExpenses - Total Operating Expenses
  • operatingIncome - Operating Income or Loss
  • totalOtherIncomeExpenseNet - Total Other Income/Expenses Net
  • ebit - Earnings Before Interest and Taxes
  • interestExpense - Interest Expense
  • incomeBeforeTax - Income Before Tax
  • incomeTaxExpense - Income Tax Expense
  • minorityInterest - Minority Interest
  • netIncomeFromContinuingOps - Net Income From Continuing Ops
  • discontinuedOperations - Discontinued Operations
  • extraordinaryItems - Extraordinary Items
  • effectOfAccountingCharges - Effect Of Accounting Changes
  • otherItems - Other Items
  • netIncome - Net Income
  • netIncomeApplicableToCommonShares - Net Income Applicable To Common Shares

Balance Sheets

To get a stock’s annual Balance Sheet data, use the function:

=BALANCE_SHEET_DATA(symbol(s), data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these are required. The last argument is the year you would like data for. This last option is optional and if you do not enter a year then it will default to the latest year data is available.

Note: historical Balance Sheet data go back 4 years. Also, the years correspond with the year a company’s financial year ends.

Below is a full list of every available option and what data it refers to.

List of data arguments

  • endDate - Period Ending Date
  • cash - Cash And Cash Equivalents
  • shortTermInvestments - Short Term Investments
  • netReceivables - Net Receivables
  • otherCurrentAssets - Other Current Assets
  • totalCurrentAssets - Total Current Assets
  • longTermInvestments - Long Term Investments
  • propertyPlantEquipment - Property Plant and Equipment
  • goodWill - Goodwill
  • intangibleAssets - Intangible Assets
  • otherAssets - Other Assets
  • deferredLongTermAssetCharges - Deferred Long Term Asset Charges
  • totalAssets - Total Assets
  • accountsPayable - Accounts Payable
  • otherCurrentLiab - Other Current Liabilities
  • longTermDebt - Long Term Debt
  • otherLiab - Other Liabilities
  • totalCurrentLiabilities - Total Current Liabilities
  • totalLiab - Total Liabilities
  • commonStock - Common Stock
  • retainedEarnings - Retained Earnings
  • treasuryStock - Treasury Stock
  • capitalSurplus - Capital Surplus
  • otherStockholderEquity - Other Stockholder Equity
  • totalStockholderEquity - Total Stockholder Equity
  • netTangibleAssets - Net Tangible Assets

Cash Flow Statements

To get a stock’s annual Cash Flow Statement data, use the function:

=CASH_FLOW_STATEMENT_DATA(symbol(s), data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these are required. The last argument is the year you would like data for. This last option is optional and if you do not enter a year then it will default to the latest year data is available.

Note: historical Cash Flow Statement data go back 4 years. Also, the years correspond with the year a company’s financial year ends.

Below is a full list of every available option and what data it refers to.

List of data arguments

  • endDate - Period Ending Date
  • netIncome - Net Income
  • depreciation - Depreciation
  • changeToNetincome - Adjustments To Net Income
  • changeToLiabilities - Changes In Liabilities
  • changeToOperatingActivities - Changes In Other Operating Activities
  • totalCashFromOperatingActivities - Total Cash Flow From Operating Activities
  • capitalExpenditures - Capital Expenditures
  • investments - Investments
  • otherCashflowsFromInvestingActivities - Other Cash flows from Investing Activities
  • totalCashflowsFromInvestingActivities - Total Cash Flows From Investing Activities
  • dividendsPaid - Dividends Paid
  • netBorrowings - Net Borrowings
  • otherCashflowsFromFinancngActivities - Other Cash Flows from Financing Activities
  • totalCashFromFinanciingActivities - Total Cash Flows From Financing Activities
  • effectOfExchangeRate - Effect Of Exchange Rate Changes
  • changeInCash - Change In Cash and Cash Equivalents

Earnings

To get a stock’s Earnings data for the last year, use the function:

=EARNINGS_DATA(symbol(s), data, quarter)

This function takes three arguments, two of which are required. The first is the symbol of the stock(s) you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these are required. The last argument is the quarter you would like data for. This last option is optional and if you do not enter a quarter then it will default to the latest quarter data is available.

Note: historical Earnings data go back 1 year (last 4 quarters). Also, the quarter argument expects the format 1Q2018.

Below is a full list of every available option and what data it refers to.

List of data arguments

  • actual - Actual earnings per share
  • estimate - Estimate earnings per share

Options Data

Sheets Market Data Pro allows you to pull options data into your spreadsheets in two ways. The first way is by generating a separate data sheet. The other way is by using a custom options function.

Options Video Overview

Options Data Sheet

The Sheets Market Data add-on can generate an option data sheet for you in just a click of the mouse.

The sheet is generated automatically just by providing the symbol of the underlying stock for an option. The generated sheet will include all option contracts for that stock with future expiration dates (both calls and puts) and put them in a new sheet with columns for:

  • Type
  • Expiration Date
  • Contract Name
  • Last Trade Date
  • Strike
  • Last Price
  • Bid
  • Ask
  • Change
  • Volume
  • Open Interest
  • Implied Volatility
  • In the Money

options-data

Generating an options data sheet is simple. First, navigate to Add-ons > Sheets Market Data > Options Data > Create Sheet(s).

options-menu

This will open a dialog box asking you to enter the symbol of the underlying stock of the option you would like data for. Enter the symbol of the stock you would like this data for and click Ok. If you would like to generate multiple sheets at once, separate each symbol you want historical data for with a comma.

options-dialog

This will run the script which will generate a new sheet with all the options data for the given stock. You are now free to use the data however you see fit. The sheet will auto-update each time you open your spreadsheet with the latest information for you if you leave it in your spreadsheet.

Options Data Function

To get data for a specific options contract, use the function:

=OPTIONS_DATA(symbol(s), data)

This function takes two arguments. The first is the symbol(s) of the option contract you want data for—as either a string, a cell reference, or a range of cells. The second is the type of data you want—as either a string, cell reference, or range of cells. Both of these arguments are required.

Note: if you don’t know what the symbol is for a specific option contract you’re interested in, try finding some by generating an option data sheet as explained above.

Below is a full list of every available data argument and what it refers to.

List of data arguments

  • strike - Strike price
  • underlyingSymbol - Symbol of the underlying stock this option is for
  • openInterest - Open Interest
  • expireDate - Expiration Date
  • exchange - Exchange option is traded on
  • ask - Ask price
  • bid - Bid price
  • regularMarketPrice - Current option price
  • regularMarketChange - Change in price, raw
  • regularMarketChangePercent - Change in price, as %
  • regularMarketOpen - Most recent market open price
  • regularMarketDayHigh - Most recent market day high
  • regularMarketDayLow - Most recent market day low
  • regularMarketVolume - Most recent market day volume
  • regularMarketDayRange - Most recent market day price range
  • regularMarketPreviousClose - Previous market day close
  • fiftyTwoWeekRange - 52 week price range
  • fiftyTwoWeekLow - 52 week low
  • fiftyTwoWeekHigh - 52 week high
  • fiftyTwoWeekLowChange - 52 week low change, raw
  • fiftyTwoWeekLowChangePercent - 52 week low change, as %
  • fiftyTwoWeekHighChange - 52 week high change, raw
  • fiftyTwoWeekHighChangePercent - 52 week high change, as %

Video Overview