| name | sp-capital-iq-excel-pro |
| description | Use this skill any time a financial analysis spreadsheet is needed that pulls live or historical data from S&P Capital IQ. This means any task where the user wants to create financial models using Capital IQ data, build company profiles with financial metrics, construct peer analysis or comparable company tables, pull historical financial statements, analyze market data and trading multiples, or generate financial reports using Capital IQ datasets. Trigger when the user mentions Capital IQ, S&P data, company financials, peer analysis, or needs institutional-quality financial data in Excel. Also trigger for creating DCF models, LBO models, merger models, or any valuation analysis that benefits from live financial data feeds. The deliverable must be an Excel file with S&P Capital IQ formulas. Do NOT trigger when the user just needs basic Excel operations without financial data integration, or when they specifically request other data sources. |
CRITICAL RULES
1. NO HALLUCINATION OF DATA ITEMS
ONLY use data items documented in this skill file or in docs/SPG_OfficeReferenceGuide_v2_RANGEV.xlsx
- NEVER guess or invent data item names
- If uncertain, check the metric reference tables below or the Excel reference guide
- Calculate missing ratios manually from documented items
2. ZERO FORMULA ERRORS
Every Excel model MUST have ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?, #INVALID COMPANY ID)
- Use
IFERROR() to handle missing data gracefully
- Use absolute references ($) for company identifier cells
- Always use exchange-qualified identifiers (e.g.,
"NASDAQ:NVDA", "NYSE:MCD") for reliability -- bare tickers can cause #INVALID COMPANY ID errors
3. NEVER USE @ IN FORMULAS
When writing formulas via Python/openpyxl, write =SPG(...) NOT =@SPG(...).
- The
@ implicit intersection operator is added automatically by Excel at display time
- Including
@ in the stored formula causes it to be treated as a text string instead of executing
- This applies to all functions:
SPG, SPGRangeV, SPGScreen, SPGTable
Correct: cell.value = '=SPG("NASDAQ:NVDA","IQ_TOTAL_REV","FY2024")'
Wrong: cell.value = '=@SPG("NASDAQ:NVDA","IQ_TOTAL_REV","FY2024")'
4. NO EMOJIS
Maintain professional tone in all outputs
5. PRESERVE EXISTING TEMPLATES
When modifying existing files, EXACTLY match existing format and conventions
FORMULA REFERENCE GUIDE
Complete Documentation: docs/SPG_OfficeReferenceGuide_v2_RANGEV.xlsx
This Excel file contains:
- FrequentFormulas sheet: Complete catalog of valid formulas by category
- RangeV sheet: SPGRANGEV syntax examples
Always verify formulas and data items against this reference guide.
CORE CONCEPTS
Identifier (Company Lookup)
The first parameter in every SPG formula identifies which company or entity to look up.
| Format | Example | Description |
|---|
| Exchange:Ticker | "NYSE:SPGI" | Exchange-qualified ticker (PREFERRED) |
| Ticker | "SPGI" | Bare ticker (less reliable -- can cause #INVALID COMPANY ID) |
| Market Intelligence ID | "4023623" | Numeric MI identifier |
| S&P Capital IQ ID | "IQ21719" | CIQ-prefixed identifier |
| Rate symbol | "%TCMSY10" | Treasury/rate identifier |
| Currency pair | "$EURUSD" | FX pair identifier |
ALWAYS prefer Exchange:Ticker format (e.g., "NASDAQ:NVDA", "NYSE:MCD") to avoid ambiguity.
Metric (Data Item)
The second parameter is the mnemonic code for the specific data point to retrieve (e.g., "IQ_TOTAL_REV" for Total Revenue). See the full metric reference tables below.
Cell Referencing
All parameters in every SPG function support cell references in addition to direct inputs:
=SPG(A1, "IQ_TOTAL_REV", A2)
Where A1 = NASDAQ:AMZN, A2 = LTM
Model Setup Pattern
Every spreadsheet MUST place the company identifier in a dedicated, clearly labeled cell and reference it with absolute references throughout:
Cell B2: "Company Identifier" (label)
Cell C2: "NASDAQ:NVDA" (value -- exchange-qualified)
All formulas reference $C$2:
=SPG($C$2, "IQ_TOTAL_REV", "FY2024")
=SPG($C$2, "SP_MARKETCAP")
=IFERROR(SPG($C$2, "SP_HEADCOUNT_LATEST"), "-")
- The identifier cell must contain a plain text value (e.g.,
NASDAQ:NVDA), NOT a formula
- Always use absolute references (
$C$2) so formulas don't break when rows/columns shift
- Wrap non-critical data items in
IFERROR() to degrade gracefully
SPG FUNCTIONS
=SPG() -- Single Value
Retrieves one specific data point for one specific time period.
Syntax: =SPG("Identifier", "Metric", "Period", "Options:")
Some metrics accept an additional As-Of Date parameter.
Basic:
=SPG("AMZN", "IQ_TOTAL_REV", "LTM")
=SPG("NYSE:MCD", "SP_PRICE_CLOSE", TODAY())
=SPG("NYSE:MCD", "IQ_TEV") -- TEV, no period needed
=SPG("NYSE:MCD", "IQ_TEV_EBITDA") -- TEV/EBITDA, no period needed
With Options:
=SPG("NYSE:IBM", "IQ_TOTAL_REV", "FY2018", "Options: Curr=EUR, Mag=Millions")
Consensus Estimates:
=SPG("NYSE:MCD", "Total Revenue", "FY2025", "Options:DataType=SD")
TEV Note: TEV data items (IQ_TEV, SNL_TEV, IQ_TEV_EBITDA, IQ_TEV_TOTAL_REV, etc.) do NOT require period parameters -- they return the current total enterprise values.
=SPGRangeV() -- Range of Values (Time Series)
Pulls a time series of data (e.g., the last 5 years of revenue). The formula spills across multiple cells.
Syntax: =SPGRangeV("Identifier", "Metric", "Beginning Period", "As-Of Date", "Options:")
=SPGRangeV("NYSE:MCD", "IQ_TOTAL_REV", "FY-4")
=SPGRangeV("NYSE:IBM", "IQ_TOTAL_REV", "FQ-5", "04/01/2020", "Options: Curr=EUR, Mag=Millions, Dates=Before")
| Parameter | Description | Example |
|---|
| Identifier | Company ticker or S&P identifier | "NYSE:IBM" |
| Metric | Data mnemonic | "IQ_TOTAL_REV" |
| Beginning Period | Starting period for the time series | "FQ-5" |
| As-Of Date | Optional: historical as-of date | "04/01/2020" |
| Options | Optional: Currency, Magnitude, Dates position | "Options: Curr=EUR, Mag=Millions, Dates=Before" |
Detailed Ownership Range
SPGRangeV can also pull a list of a company's top shareholders. Instead of a Beginning Period, provide a Start Rank and End Rank:
=SPGRangeV("NYSE:BAC", "SP_INSTITUTIONAL_VALUE", "1", "10", "10/5/2020", "SUMMARY", "Options: Rank=1/1/2020, Curr=GBP, Mag=Millions")
=SPGScreen() -- Saved Screens
Embeds a saved screen or company list from the S&P Capital IQ Pro Data Wizard directly into Excel.
Syntax: =SPGScreen("Query Name", "Direction", Field ID Range, "Options:")
=SPGTable() -- Multi-Company Grids
Typically auto-generated when exporting a screen from the Data Wizard into Excel. Refreshes a large table of data across multiple entities, periods, and fields at once.
=SPGTable($B$26:$B$35, $C$23:$D$23, $C$24:$D$24, "Options:Curr=EUR, Mag=Thousands, ConvMethod=Recommended")
AVAILABLE PERIOD TYPES
Historical / Current Periods (for IQ_* and SNL_* fundamentals)
| Period Type | Actual | Relative |
|---|
| Calendar Year | CY2018 | CY0, CY-1, ... |
| Fiscal Year | FY2018 | FY0, FY-1, ... |
| Calendar Quarter | CQ42018 | CQ0, CQ-1, ... |
| Fiscal Quarter | FQ42018 | FQ0, FQ-1, ... |
| Last-Twelve-Months | LTM42018 | LTM, LTM-1, ... |
| Year-To-Date | YTD42018 | YTD |
| Half/Semi-Annual | FH12018 | FH0 |
Forward Periods (ONLY for consensus estimate mnemonics: SP_*_EST)
| Period Type | Relative |
|---|
| Next-Twelve-Months | NTM |
| Forward Fiscal Year | FY+1, FY+2, ... |
| Forward Fiscal Quarter | FQ+1, FQ+2, ... |
CRITICAL: Forward periods (FY+1, NTM, etc.) cause #INVALID FUNCTION PARAMETER when used with IQ_* or SNL_* fundamental data items. These periods ONLY work with consensus estimate mnemonics (SP_REV_EST, SP_EBITDA_EST, SP_EPS_EST, SP_NI_EST, SP_EBIT_EST, SP_CFPS_EST).
Building a model with historical + forecast columns:
Historical (FY-4 to FY0): =SPG($C$2, "IQ_TOTAL_REV", "FY-4")
Forecast (FY+1, FY+2): =SPG($C$2, "SP_REV_EST", "FY+1")
You MUST switch from IQ_* mnemonics to SP_*_EST mnemonics for forward periods.
OPTIONS STRING REFERENCE
The Options string is an optional final parameter in any SPG formula. Arguments are comma-separated inside quotes, always beginning with "Options:".
"Options: Curr=EUR, Mag=Millions, ConvMethod=Recommended"
Currency (Curr= or Currency=)
Converts monetary data into a target currency using a 3-character ISO code.
=SPG("AMZN", "IQ_TOTAL_REV", "FY2023", "Options: Curr=EUR")
Magnitude (Mag= or Magnitude=)
Scales monetary values to reduce trailing zeros.
| Value | Alias |
|---|
Standard | (default -- uses source's native magnitude) |
Actuals | 0 |
Thousands | 3 |
Millions | 6 |
Billions | 9 |
Trillions | 12 |
=SPG("AMZN", "IQ_TOTAL_REV", "FY2023", "Options: Mag=Millions")
Conversion Method (ConvMethod=)
Controls which exchange rate is used when converting currencies.
| Value | Description |
|---|
Recommended | Uses the exchange rate from the date the data was reported |
MRSpot | Uses the most recent spot rate available today |
=SPG("AMZN", "IQ_TOTAL_REV", "FY2023", "Options: Curr=GBP, ConvMethod=Recommended")
Terminology (Term= or Terminology=)
Translates text data outputs into different languages.
| Value | Language |
|---|
zh-CN | Chinese |
en-GB | British English |
ja-JP | Japanese |
es-HN | Spanish |
pt-BR | Portuguese |
de-DE | German |
=SPG("AMZN", "SP_COUNTRY_NAME", , "Options: Terminology=ja-JP")
Other Common Options
| Option | Description |
|---|
DataType=SD or DataType=CD | Source data type (SD = S&P Data, CD = Consensus) |
Fill=EOM | Fill end-of-month for time series |
Dates=Before | Place date labels before values in SPGRangeV |
METRIC REFERENCE
Market Data
| Metric | Mnemonic |
|---|
| Day Close Price | SP_PRICE_CLOSE |
| Intraday Price | SP_PRICE_INTRADAY |
| Volume Weighted Average Price | SP_VWAP |
| Market Capitalization | SP_MARKETCAP |
| Total Enterprise Value (SNL) | SNL_TEV |
| Total Enterprise Value (CIQ) | IQ_TEV |
| Shares Outstanding | SP_SHARES_OUT |
| Volume | SP_VOLUME |
| Dividend Yield (%) | SP_DIV_YIELD |
| LTM Dividends Announced | SP_DIV_LTM |
Sample:
=SPG("AMZN", "SP_MARKETCAP") -- Latest Market Capitalization
=SPG("AMZN", "SP_MARKETCAP", "04/15/2020") -- Market Cap as of 4/15/2020
Exclude the as-of date parameter for latest available.
Corporate
| Metric | Mnemonic |
|---|
| Entity Name | SP_COMPANY_NAME |
| Ticker | SP_TICKER |
| Exchange | SP_EXCHANGE |
| Primary Industry (MI) | MI_PRIMARY_INDUSTRY |
| Primary Industry (CIQ/GICS) | IQ_PRIMARY_INDUSTRY |
| SIC Code | SP_SIC_CODE |
| State | SP_STATE |
| Country / Region Name | SP_COUNTRY_NAME |
| S&P Capital IQ ID | SP_CIQ_ID |
| Company Type | SP_COMPANY_TYPE |
| Company Status | SP_COMPANY_STATUS |
| Business Description | SP_BUSINESS_DESCRIPTION |
| Ownership Structure | SP_OWN_STRUCTURE |
| Month of Fiscal Year End | SP_MONTH_FYE |
| Web Address | SP_WEBSITE |
Valuation / Multiples
Trailing Multiples
| Metric | Mnemonic |
|---|
| TEV/Total Revenue | IQ_TEV_TOTAL_REV |
| TEV/EBITDA | IQ_TEV_EBITDA |
| TEV/EBIT | IQ_TEV_EBIT |
| TEV/Unlevered FCF | IQ_TEV_UFCF |
| Price/EPS | IQ_PE |
| Price/Book Value | IQ_PBV_X |
| Price/Tangible Book Value | IQ_PTBV_X |
Sample:
=SPG("AMZN", "IQ_PE", "LTM") -- Latest trailing PE
=SPG("AMZN", "SP_PE_FWD", "LTM", "4/15/2020") -- Trailing PE as of 4/15/2020
Forward Trading Multiples
| Metric | Mnemonic |
|---|
| TEV/Forward Total Revenue | SP_TEV_TOTAL_REV_FWD |
| TEV/Forward EBITDA | SP_TEV_EBITDA_FWD |
| TEV/Forward EBIT | SP_TEV_EBIT_FWD |
| Price/Forward EPS | SP_PE_FWD |
Sample:
=SPG("AMZN", "SP_PE_FWD", "NTM") -- Latest forward PE
=SPG("AMZN", "SP_PE_FWD", "NTM", "4/15/2020") -- Forward PE as of 4/15/2020
Supported periods for forward multiples: NTM, FY+1, FQ+1, FY2021, etc.
Capital IQ Consensus Estimates
| Metric | Mnemonic |
|---|
| CIQ Avg Broker Recommendation (Text) | SP_AVG_BROKER_REC_TEXT |
| CIQ Avg Broker Recommendation (#) | SP_AVG_BROKER_REC |
| CIQ Mean Price Target | SP_PRICE_TARGET |
| Mean EPS Estimate | SP_EPS_EST |
| Mean Revenue Estimate | SP_REV_EST |
| Mean EBITDA Estimate | SP_EBITDA_EST |
| Mean EBIT Estimate | SP_EBIT_EST |
| Mean Net Income Estimate | SP_NI_EST |
| Mean Cash Flow per Share Estimate | SP_CFPS_EST |
Non-Periodic:
=SPG("AMZN", "SP_PRICE_TARGET") -- Latest Price Target
=SPG("AMZN", "SP_PRICE_TARGET", "04/15/2020") -- Price Target as of 4/15/2020
Periodic:
=SPG("AMZN", "SP_EPS_EST", "FY+1") -- EPS Estimate for next fiscal year
=SPG("AMZN", "SP_EPS_EST", "FY+1", "04/15/2020") -- Estimate as of 4/15/2020
Supported periods for estimates: NTM, FY+1, FQ+1, FY2021, etc.
Capital IQ Fundamentals
Balance Sheet
| Metric | Mnemonic |
|---|
| Cash and Equivalents | IQ_CASH_EQUIV |
| Short Term Investments | IQ_ST_INVEST |
| Cash & Short Term Investments | IQ_CASH_ST_INVEST |
| Accounts Receivable | IQ_AR |
| Total Receivables | IQ_TOTAL_RECEIV |
| Inventory | IQ_INVENTORY |
| Total Current Assets | IQ_TOTAL_CA |
| Gross Property, Plant & Equipment | IQ_GPPE |
| Net Property, Plant & Equipment | IQ_NPPE |
| Long-term Investments | IQ_LT_INVEST |
| Total Goodwill and Intangibles | IQ_TOTAL_GW_INTAN |
| Total Assets | IQ_TOTAL_ASSETS |
| Accounts Payable | IQ_AP |
| Short-term Borrowings | IQ_ST_DEBT |
| Curr. Port. of LT Debt | IQ_CURRENT_PORT_DEBT |
| Current Portion of Leases | IQ_CURRENT_PORT_LEASES |
| Total Current Liabilities | IQ_TOTAL_CL |
| Long-Term Debt | IQ_LT_DEBT |
| Long-Term Leases | IQ_CAPITAL_LEASES |
| Total Liabilities | IQ_TOTAL_LIAB |
| Total Minority Interest | IQ_MINORITY_INTEREST |
| Total Preferred Equity | IQ_TOTAL_PREF_EQUITY |
| Common Stock | IQ_COMMON_STOCK |
| Retained Earnings | IQ_RETAINED_EARNINGS |
| Treasury Stock | IQ_TREASURY |
| Total Common Equity | IQ_TOTAL_COMMON_EQUITY |
| Total Equity | IQ_TOTAL_EQUITY |
| Total Liabilities And Equity | IQ_TOTAL_LIAB_EQUITY |
| Total Debt | IQ_TOTAL_DEBT |
| Net Debt | IQ_NET_DEBT |
| Total Capital | IQ_TOTAL_CAP |
Balance Sheet -- Supplemental
| Metric | Mnemonic |
|---|
| Filing Date | IQ_FINL_FILING_DATE |
| Period Date | IQ_PERIOD_END |
| Total Shares Out. on Filing Date | IQ_SHARES_OUT_FILING_DATE |
| Total Shares Out. on BS Date | IQ_SHARES_OUT_PERIOD_END |
| Book Value/Share | IQ_BVPS |
| Tangible Book Value | IQ_TANG_EQUITY |
| Tangible Book Value/Share | IQ_TBVPS |
| Net Goodwill | IQ_GW_NET |
| Net Intangibles | IQ_INTAN_NET |
| Total Leases | IQ_TOTAL_LEASES |
| Total Current Assets, As Reported | IQ_TOTAL_CA_REPORTED |
| Total Current Liabilities, As Reported | IQ_TOTAL_CL_REPORTED |
| Total Shareholders Equity, As Reported | IQ_TOTAL_EQUITY_REPORTED |
| Part-Time Employees | IQ_PART_TIME |
| Full Time Employees | IQ_FULL_TIME |
Income Statement
| Metric | Mnemonic |
|---|
| Total Revenue | IQ_TOTAL_REV |
| Cost of Goods Sold / Cost of Revenue | IQ_COGS (ALWAYS use this -- never use IQ_COST_REV) |
| Gross Profit | IQ_GP |
| Selling General & Admin Exp. | IQ_SGA_SUPPL |
| R & D Exp. | IQ_RD_EXP |
| Depreciation & Amort. | IQ_DA_SUPPL |
| Amort. of Goodwill and Intangibles | IQ_GW_INTAN_AMORT |
| Operating Income | IQ_OPER_INC |
| Net Interest Exp. | IQ_NET_INTEREST_EXP |
| EBT Excl Unusual Items | IQ_EBT_EXCL |
| Other Unusual Items | IQ_OTHER_UNUSUAL_SUPPL |
| EBT Incl. Unusual Items | IQ_EBT |
| Income Tax Expense | IQ_INC_TAX |
| Earnings from Cont. Ops. | IQ_EARNINGS_CONT_OPS |
| Earnings of Discontinued Ops. | IQ_EARNINGS_DISCONTINUED_OPS |
| Extraord. Item & Account. Change | IQ_EXTRA_ACC_ITEMS |
| Net Income to Company | IQ_NET_INC |
| Pref. Dividends and Other Adj. | IQ_PREF_DIV_OTHER |
| Merger & Related Restruct. Charges | IQ_MERGER_RESTRUCTURE |
| NI to Common Incl Extra Items | IQ_NI_AVAIL_INCL |
| NI to Common Excl. Extra Items | IQ_NI_AVAIL_EXCL |
| Basic EPS | IQ_BASIC_EPS_AFTER_EXTRA |
| Basic EPS Excl. Extra Items | IQ_BASIC_EPS_BEFORE_EXTRA |
| Weighted Avg. Basic Shares Out. | IQ_AVG_BASIC_SHARES_OUT |
| Diluted EPS Incl. Extra Items | IQ_DILUT_EPS_AFTER_EXTRA |
| Diluted EPS Excl. Extra Items | IQ_DILUT_EPS_BEFORE_EXTRA |
| Weighted Avg. Diluted Shares Out. | IQ_AVG_DILUT_SHARES_OUT |
| Normalized Basic EPS | IQ_BASIC_EPS_NORM |
| Normalized Diluted EPS | IQ_DILUT_EPS_NORM |
Income Statement -- Supplemental
| Metric | Mnemonic |
|---|
| EBITDA | IQ_EBITDA |
| EBITDA Incl Eqty Inc from Affil Excl | IQ_EBITDA_EQ_INC_EXCL_OPER_LEASE_ADJ |
| EBITDA, As Reported | IQ_EBITDA_REPORTED |
| EBITA | IQ_EBITA |
| EBIT | IQ_EBIT |
| EBITDAR | IQ_EBITDAR |
| As Reported Total Revenue | IQ_TOTAL_REV_AS_REPORTED |
| Total Interest Income, As Reported | IQ_INT_INC_TOTAL_REPORTED |
| Total Interest Expense, As Reported | IQ_INT_EXPEN_TOTAL_REPORTED |
| Net Interest Income, As Reported | IQ_NET_INT_INC_REPORTED |
| Net Rental Expense, Total | IQ_NET_RENTAL_EXP_FN |
| Normalized Net Income | IQ_NI_NORM |
| Total Same Store Sales Growth | IQ_SAME_STORE_TOTAL |
| Earnings before Taxes, As Reported | IQ_EBT_REPORTED |
| Effective Tax Rate | IQ_EFFECT_TAX_RATE |
| Payout Ratio | IQ_PAYOUT_RATIO |
| Interest on Long-Term Debt | IQ_INT_EXP_LTD |
| Total Current Taxes | IQ_CURR_TAXES |
| Total Deferred Taxes | IQ_DEFERRED_TAXES_TOTAL |
Cash Flow
| Metric | Mnemonic |
|---|
| Net Income - CF | IQ_NI_CF |
| Depreciation & Amort., Total | IQ_DA_CF |
| Asset Writedown & Restructuring Costs | IQ_ASSET_WRITEDOWN_CF |
| Stock-Based Compensation | IQ_STOCK_BASED_CF |
| Net Cash From Discontinued Ops. | IQ_DO_CF |
| Change in Acc. Receivable | IQ_CHANGE_AR |
| Change In Inventories | IQ_CHANGE_INVENTORY |
| Change in Acc. Payable | IQ_CHANGE_AP |
| Change in Unearned Rev. | IQ_CHANGE_UNEARN_REV |
| Change In Income Taxes | IQ_CHANGE_INC_TAX |
| Change in Def. Taxes | IQ_CHANGE_DEF_TAX |
| Cash from Ops. | IQ_CASH_OPER |
| Capital Expenditure | IQ_CAPEX |
| Sale of Property, Plant and Equipment | IQ_SALE_PPE_CF |
| Cash Acquisitions | IQ_CASH_ACQUIRE_CF |
| Divestitures | IQ_DIVEST_CF |
| Sale (Purchase) of Intangible assets | IQ_SALE_INTAN_CF |
| Invest. in Marketable & Equity Securt. | IQ_INVEST_SECURITY_CF |
| Cash from Investing | IQ_CASH_INVEST |
| Short Term Debt Issued | IQ_ST_DEBT_ISSUED |
| Long-Term Debt Issued | IQ_LT_DEBT_ISSUED |
| Total Debt Issued | IQ_TOTAL_DEBT_ISSUED |
| Short Term Debt Repaid | IQ_ST_DEBT_REPAID |
| Long-Term Debt Repaid | IQ_LT_DEBT_REPAID |
| Total Debt Repaid | IQ_TOTAL_DEBT_REPAID |
| Issuance of Common Stock | IQ_COMMON_ISSUED |
| Repurchase of Common Stock | IQ_COMMON_REP |
| Issuance of Pref. Stock | IQ_PREF_ISSUED |
| Repurchase of Preferred Stock | IQ_PREF_REP |
| Common Dividends Paid | IQ_COMMON_DIV_CF |
| Pref. Dividends Paid | IQ_PREF_DIV_CF |
| Common and/or Pref. Dividends Paid | IQ_COMMON_PREF_DIV_CF |
| Total Dividends Paid | IQ_TOTAL_DIV_PAID_CF |
| Cash from Financing | IQ_CASH_FINAN |
| Net Change in Cash | IQ_CASH_NET_CHANGE |
Cash Flow -- Supplemental
| Metric | Mnemonic |
|---|
| Cash Interest Paid | IQ_CASH_INTEREST |
| Cash Taxes Paid | IQ_CASH_TAXES |
| Net Debt Issued | IQ_NET_DEBT_ISSUED |
| Levered Free Cash Flow | IQ_LEVERED_FCF |
| Unlevered Free Cash Flow | IQ_UNLEVERED_FCF |
| Change in Net Working Capital | IQ_CHANGE_NET_WORKING_CAPITAL |
As-of Dates for CIQ Fundamentals
Capital IQ Fundamentals support an "as-of" parameter for pulling historical financial data as of a specific date. This applies to both Actual and Relative period types:
=SPG("AMZN", "IQ_TOTAL_ASSETS", "FY2018", "6/30/2019") -- Actual period with as-of date
=SPG("AMZN", "IQ_TOTAL_ASSETS", "FY0", "6/30/2019") -- Relative period with as-of date
SNL Financials
Key Financials
| Metric | Mnemonic |
|---|
| Market Capitalization | SP_MARKETCAP |
| Total Enterprise Value (SNL) | SNL_TEV |
| Total Revenue | SNL_TOTAL_REVENUE |
| Interest Expense | SNL_INT_EXP |
| Net Income | SNL_NET_INC |
| Net Income Attributable to Parent | SNL_NET_INC_PARENT |
| Avg Diluted Shares | SNL_AVG_DIL_SHARES |
| Basic EPS after Extra | SNL_BASIC_EPS_AFTER_EXTRA |
| Diluted EPS after Extraordinary | SNL_DILUT_EPS_AFTER_EXTRA |
| EBITDA | SNL_EBITDA |
| EBIT | SNL_EBIT |
| Common Dividends Declared per Share | SNL_COMMON_DIV_DECLARED |
| Dividend Payout Ratio | SNL_PAYOUT_RATIO |
| Recurring Revenue Growth | SNL_REC_REV_GROWTH |
| EPS Growth, before Extraordinary | SNL_EPS_GROWTH_BEFORE_EXTRA |
| EPS Growth, after Extraordinary | SNL_EPS_AFTER_EXTRA_GROWTH |
| ROAA | SNL_ROA |
| ROAE | SNL_ROE |
| ROACE | SNL_ROCE |
| ROATCE | SNL_ROATCE |
| Gross Margin | SNL_GROSS_MARGIN |
| EBITDA Margin | SNL_EBITDA_MARGIN |
| EBIT Margin | SNL_EBIT_MARGIN |
| Net Income Margin | SNL_NET_INC_MARGIN |
| Recurring EBITDA Margin | SNL_REC_EBITDA_MARGIN |
| EBITDA/ Interest Expense | SNL_EBITDA_TO_INT_EXP |
| Cash and Cash Equivalents | SNL_CASH_EQUIV |
| Current Assets | SNL_CURRENT_ASSETS |
| Net PP&E | SNL_NPPE |
| Total Assets | SNL_TOTAL_ASSETS |
| Total Debt | SNL_TOTAL_DEBT |
| Total Equity | SNL_TOTAL_EQUITY |
| Total Capitalization | SNL_TOTAL_CAP |
| Common Shares Outstanding | SNL_SHARES_OUT_PERIOD_END |
| Book Value per Share | SNL_BVPS |
| Tangible Book Value per Share | SNL_TBVPS |
| Total Equity/ Total Assets | SNL_EQUITY_TO_ASSETS |
| Total Debt/ Total Equity | SNL_DEBT_TO_EQUITY |
| Total Debt/ Total Capitalization | SNL_TOTAL_DEBT_TO_TOTAL_CAP |
| Debt/ EBITDA | SNL_DEBT_TO_EBITDA |
| Cash Flow from Operating Activities | SNL_CASH_OPER |
| Cash Flow: Capital Expenditures | SNL_CAPEX |
SNL Key Financials -- Supplemental
| Metric | Mnemonic |
|---|
| Period Ended (mm/dd/yyyy) | SNL_PERIOD_END |
| Earnings Release Date (mm/dd/yyyy) | SNL_EARNINGS_RELEASE_DATE |
| Financials Reported Currency Code | SNL_CURRENCY_FINANCIALS |
| Fiscal Period | SNL_FISCAL_PERIOD |
Banking
| Metric | Mnemonic |
|---|
| Net Customer Loans | SNL_NET_CUSTOMER_LOANS |
| Total Deposits | SNL_TOTAL_DEPOSITS |
| Tangible Equity | SNL_TANG_EQUITY |
| Net Income | SNL_NET_INC |
| ROACE | SNL_ROCE |
| ROATCE | SNL_ROATCE |
| Net Loans/ Assets | SNL_NET_LOAN_TO_ASSETS |
| Amortized Loans/ Deposits | SNL_AMORT_LOANS_TO_DEPOSITS |
| Securities/ Assets | SNL_SECS_TO_ASSETS |
| Loans/ Deposits | SNL_LOAN_TO_DEP |
| Net Interest Income/ Avg Assets | SNL_NET_INT_INC_TO_AVG_ASSETS |
| Net Interest Margin | SNL_NIM |
| Tier 1 Common Capital (CET1) Ratio | SNL_TIER_1_COMMON_RATIO |
| Tier 1 Ratio | SNL_TIER_1_RATIO |
| Total Capital Ratio | SNL_TOTAL_CAPITAL_RATIO |
Insurance
| Metric | Mnemonic |
|---|
| Investments | SNL_INVESTMENTS |
| Total Policy Reserves | SNL_FIN_TOTAL_POLICY_RESV |
| Total Policy Income | SNL_TOTAL_POLICY_INC |
| Net Investment Income | SNL_NET_INVEST_INC |
| Total Policy Expense | SNL_TOTAL_POLICY_EXP |
| Underwriting & Other Expense | SNL_UNDERWRITING_OTHER_EXPENSE |
| Gross Premiums Written | SNL_GPW |
| Gross Premiums Earned | SNL_GROSS_PREMS_EARNED |
| Ceded Premiums Earned | SNL_CEDED_PREMS_EARNED |
| Net Premiums Earned | SNL_NPE |
| Loss Ratio | SNL_LR |
| Expense Ratio | SNL_ER |
| Combined Ratio | SNL_CR |
| Policy Expense/ Expense | SNL_POLICY_EXP_TO_EXP |
| Insurance Investment Yield | SNL_INSURANCE_INVEST_YLD |
Energy (Coal, Gas Utilities, Midstream, Power)
| Metric | Mnemonic |
|---|
| Coal Sales Revenue | SNL_COAL_SALES_REV |
| Electric Revenue | SNL_ELEC_REV |
| Oil & Natural Gas Revenue | SNL_OIL_NAT_GAS_REV |
| Gas Distribution Revenue | SNL_GAS_DIST_REV |
| Oil & Gas Exploration & Production | SNL_OIL_GAS_EXPLORATION_PRODUCTION |
| Oil & Gas Products Sales | SNL_OIL_GAS_PRODUCTS_SALES |
| Oil & Gas Midstream Operating Rev. | SNL_OIL_GAS_MIDSTREAM_OPER_REV |
| Energy Operating Revenue | SNL_ENERGY_OPER_REV |
| Electric Revenue/ Operating Revenue | SNL_ELEC_REV_TO_OPER_REV |
| Gas Revenue/ Operating Revenue | SNL_GAS_REV_TO_OPER_REV |
| Op. & Maint./ Operating Expense | SNL_OPS_AND_MAINT_TO_OPER_EXP |
| Electric Generation/ Operating Expense | SNL_ELEC_GEN_TO_OPER_EXP |
| Gas Cost/ Operating Expense | SNL_GAS_COST_TO_OPER_EXP |
| Midstream Revenue/ Operating Revenue | SNL_MIDSTREAM_REV_TO_OPER_REV |
| Midstream Costs/ Operating Expense | SNL_MIDSTREAM_COSTS_TO_OPER_REV |
Media & Communications
| Metric | Mnemonic |
|---|
| Radio Content and Broadcasting Revenue | SNL_RADIO_CONTENT_BROADCASTING_REV |
| TV/ Cable Network Revenue | SNL_TV_TO_CABLE_NETWORK_REV |
| Filmed Entertainment & TV Revenue | SNL_FILMED_ENTERTAINMENT_TV_REV |
| Internet Content and Distribution Revenue | SNL_INTERNET_CONTENT_DIST_REV |
| Content, Broadcasting and Distribution Revenue | SNL_CONTENT_BROADCASTING_DIST_REV |
| Magazine Revenue | SNL_MAGAZINE_REV |
| Newspaper Revenue | SNL_NEWSPAPER_REV |
| Book Publishing Revenue | SNL_BOOK_PUBLISHING_REV |
| Publishing Revenue | SNL_PUBLISHING_REV |
| Owned & Operated Radio Stations | SNL_OWNED_OPERATED_RADIO_STATIONS |
| Number of Radio Stations | SNL_NUM_RADIO_STATIONS |
| Net Radio Revenue | SNL_NET_RADIO_REV |
| Basic Subscribers | SNL_BASIC_SUBSCRIBERS |
| Digital Subscribers | SNL_DIGITAL_SUBSCRIBERS |
| High Speed Data Subscribers | SNL_HIGH_SPEED_DATA_SUBSCRIBERS |
Financial Services
| Metric | Mnemonic |
|---|
| Assets Under Management | SNL_AUM |
| Mutual Funds under Management | SNL_MUTUAL_FUNDS_UNDER_MGMT |
| AUM Growth | SNL_AUM_GROWTH |
| Management Fees/ Avg AUM | SNL_MGMT_FEES_TO_AVG_AUM |
| Loans Originated | SNL_LOAN_ORIGINATED |
| Loans Sold | SNL_LOAN_SOLD |
| Total Net Loans | SNL_NET_LOANS |
| Investments | SNL_INV_CO_INVEST |
| Managed Assets | SNL_MNGD_ASSETS |
| Managed Receiv. for Self and Others | SNL_MNGD_RECV_FOR_SELF_OTHERS |
| Managed NCOs/ Avg Managed Rec | SNL_MNGD_NCO_TO_AVG_MNGD_REC |
| Investments/ Assets | SNL_INVEST_TO_ASSETS |
| Return on Avg. AUM | SNL_RETURN_ON_AVG_AUM |
| Return on Avg. Managed Receivables | SNL_RETURN_ON_AVG_MNGD_REC |
| Return on Avg. Managed Assets | SNL_RETURN_ON_AVG_MNGD_ASSETS |
Real Estate
| Metric | Mnemonic |
|---|
| Gross Depreciable Property | SNL_GROSS_DEPRECIABLE_PPTY |
| Net Property Investment | SNL_NET_PPTY_INVEST |
| Finance Leases | SNL_FIN_LEASES |
| Mortgage Loans | SNL_MRTG_LOAN |
| Rental Revenue | SNL_RENTAL_REV |
| Rental Net Operating Income | SNL_RENTAL_NET_OPER_INC |
| NOI | SNL_NOI |
| FFO | SNL_FFO_GAAP |
| Adjusted FFO | SNL_AFFO |
| Gain on Sale of Real Estate | SNL_GAIN_ON_SALE_OF_RE |
| FFO Payout (%) | SNL_FFO_PAYOUT |
| FFO/ Total Revenue (%) | SNL_FFO_TO_TOTAL_REV |
| Same-store NOI: Change | SNL_SS_NOI_CHANGE |
| Same-store Revenue: Chg. from Prior | SNL_SS_REV_CHANGE_FROM_PRIOR |
| NAV per Share, as Reported | SNL_NAV_PER_SHARE |
Other
| Metric | Mnemonic |
|---|
| Topic Tags | SP_TOPICTAG |
| Headcount | SP_HEADCOUNT_LATEST |
KNOWN NON-EXISTENT ITEMS
These items do NOT exist. NEVER use them:
| What You Might Guess | Correct Alternative |
|---|
SP_52WK_HI / SP_52WK_LO | Not documented -- use SPGRANGEV with SP_PRICE_CLOSE to derive |
SP_NUM_EST_BROKER_REC | Not documented -- use SP_AVG_BROKER_REC or SP_AVG_BROKER_REC_TEXT |
SP_AVG_VOLUME / SP_VOLUME_30D | Use SP_VOLUME with date range via SPGRANGEV |
IQ_COMPANY_TICKER | Use SP_TICKER |
IQ_ASSET_TURNOVER | Calculate: IQ_TOTAL_REV / IQ_TOTAL_ASSETS |
IQ_INVENTORY_TURNOVER | Calculate: IQ_COGS / IQ_INVENTORY |
IQ_CURRENT_RATIO | Calculate: IQ_TOTAL_CA / IQ_TOTAL_CL |
IQ_QUICK_RATIO | Calculate: (IQ_TOTAL_CA - IQ_INVENTORY) / IQ_TOTAL_CL |
IQ_ROIC / IQ_WACC | Calculate manually from documented items |
IQ_GROSS_MARGIN | Use SNL_GROSS_MARGIN or calculate: IQ_GP / IQ_TOTAL_REV |
IQ_EBITDA_MARGIN | Use SNL_EBITDA_MARGIN or calculate: IQ_EBITDA / IQ_TOTAL_REV |
IQ_FCF_MARGIN | Calculate: IQ_LEVERED_FCF / IQ_TOTAL_REV |
IQ_NET_DEBT_TO_EBITDA | Use SNL_DEBT_TO_EBITDA or calculate: IQ_NET_DEBT / IQ_EBITDA |
IQ_ENTERPRISE_VALUE | Use IQ_TEV or SNL_TEV |
IQ_FREE_CASH_FLOW | Use IQ_LEVERED_FCF or IQ_UNLEVERED_FCF |
IQ_OPERATING_MARGIN | Use SNL_EBIT_MARGIN or calculate: IQ_OPER_INC / IQ_TOTAL_REV |
IQ_NET_MARGIN | Use SNL_NET_INC_MARGIN or calculate: IQ_NET_INC / IQ_TOTAL_REV |
IQ_DEBT_TO_EQUITY | Use SNL_DEBT_TO_EQUITY or calculate: IQ_TOTAL_DEBT / IQ_TOTAL_EQUITY |
IQ_EV_TO_SALES | Use IQ_TEV_TOTAL_REV |
IQ_EV_TO_EBITDA | Use IQ_TEV_EBITDA |
IQ_COST_REV | Use IQ_COGS |
When in doubt: Check the metric reference tables above or the Excel reference guide. If not found, it does NOT exist.
COMMON PITFALLS
1. Never use IQ_COST_REV
IQ_COST_REV is unreliable and returns #INVALID FUNCTION PARAMETER for many companies. Always use IQ_COGS for Cost of Goods Sold / Cost of Revenue.
2. Using forward periods with fundamental mnemonics
IQ_* and SNL_* data items only support historical/current periods (FY0, FY-1, LTM, etc.). Using FY+1, NTM, or any forward period with them returns #INVALID FUNCTION PARAMETER. For forward-looking data, switch to consensus estimate mnemonics:
| Historical (IQ_*) | Forward (SP_*_EST) |
|---|
IQ_TOTAL_REV | SP_REV_EST |
IQ_EBITDA | SP_EBITDA_EST |
IQ_EBIT | SP_EBIT_EST |
IQ_NET_INC | SP_NI_EST |
IQ_DILUT_EPS_BEFORE_EXTRA | SP_EPS_EST |
Only the above line items have consensus estimate mnemonics. All other income statement line items (COGS, R&D, SGA, D&A, Interest Expense, Tax Expense, Effective Tax Rate, Basic EPS, Share Counts, etc.) have NO estimate equivalent. For estimate columns (FY+1E, FY+2E), leave cells blank for line items without a consensus mnemonic -- do NOT write "-" or any placeholder.
3. Using =@SPG() in openpyxl
Never include @ in formula strings. Write =SPG(...) -- Excel adds @ at display time.
4. Using bare tickers as identifiers
Always use exchange-qualified format ("NASDAQ:NVDA") to avoid #INVALID COMPANY ID.
5. Writing "-" for unavailable data
Never write "-" or any placeholder into cells where data is known to be unavailable. Leave the cell blank. This applies to estimate columns where no consensus mnemonic exists, and to any line item where the data item is not applicable.
