Advanced Excel: Plug-In

• When you begin writing formulas, you can use the “Formula Builder” option in the top navigation bar
• For more information on the Formula Builder, please see the “Excel Basics” help page
• Note: Throughout this document you will see “SNL” versus “SPG.” SPG Refers to the new, S&P Global Market Intelligence data sets, which are Companies and Transactions (Beta). These new data sets combine Capital IQ data with Legacy SNL data.
• All other data sets will use Legacy SNL data sets/Office functions

• SPG Table: Ideal for retrieving large amounts of data in Companies and Transactions (Beta) and will present data in a grid format
• =SPGTABLE(entity ID range, field ID range, secondary key range, “options”)
• Note: If you have trouble building SPG Tables, you can go to the web platform and use the Screener to export data as a table function

• SPG: Used to pull one specific value from Companies or Transactions (Beta)
• =SPG(entity ID, field ID, secondary key, tertiary key, “options:”)
• Ex.) =SPG(“NYSE:SPGI”, “275622”, “FY2019”)

Please note that that not all formulas will require every parameter. For further clarification, you can use the Formula Builder as an alternative.

Legacy SNL Popular Functions

• SNL Table: Ideal for retrieving large amounts of data in Legacy SNL data sets and will present data in a grid format
• =SNLTABLE(data set, SNL ID range, field ID range, secondary key range, “options:”)
  
• You can also export an SNL Table form the web platform by exporting Screening Results

• SNL Data: Used to pull one specific value from Legacy SNL data sets
• =SNLDATA(data set, SNL ID, field ID, secondary key, tertiary key, “options:”)
• Ex.) =SNLDATA("1", "MSFT-US", "133859", "2019Y")

Function Definitions

• Data Set: a numerical code that identifies a specific SNL data set. See the table below for a list of data set codes
• Entity ID: identifier number assigned to every company, offering, M&A deal, branch, property, energy power plant or M&C asset.
• If the company is public, the ticker can be used interchangeably with the entity ID
• Can be found in the Identifier Lookup

• Field ID: unique code given to every data field in S&P Cap IQ Pro Office
• Can be found in the Field Selector

• Secondary Key: for End of Period financial fields, see the date formats used below
• Tertiary Key: an optional key for End of Period financial fields
• Starting Date/End Date: be sure to use date formats below for these dates

Popular Data Set Groups (to be used for Legacy SNL functions)

• Company Data
• (1) Companies
• (2) Regulated Depositories (U.S.)
• (287) Insurance Statutory Financials (U.S.)
• Capital & M&A
• (5) Mergers & Acquisitions

For additional data set groups, be sure to check out the S&P Cap IQ Pro Office Help Page and choose the “S&P Cap IQ Pro Office Reference Guide”

Date Formats

• Please note that the date formats between SPG and SNL Functions are different