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.

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, and Volume 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 > Create Historical Data Sheet.

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.

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 second way that Sheets Market Data Pro allows you to use historical data in your spreadsheets is through the below data functions.

Income Statements

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

=INCOME_STATEMENT_DATA(symbol, data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock you want data for—as either a string or cell reference. The second is the type of data you want. 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 (unix timestamp)
  • totalRenvenue - 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, data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock you want data for—as either a string or cell reference. The second is the type of data you want. 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 (unix timestamp)
  • 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, data, year)

This function takes three arguments, two of which are required. The first is the symbol of the stock you want data for—as either a string or cell reference. The second is the type of data you want. 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 (unix timestamp)
  • 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, data, quarter)

This function takes three arguments, two of which are required. The first is the symbol of the stock you want data for—as either a string or cell reference. The second is the type of data you want. 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 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.

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.

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.

Options Data Function

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

=OPTIONS_DATA(symbol, data)

This function takes two arguments. The first is the symbol of the option contract you want data for—as either a string or cell reference. The second is the type of data you want. 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