Excel Add-In: Coverage

The New Constructs Coverage Excel Add-In function provides access to lists of the securities in our coverage universe by type - stocks, ETFs, mutual funds, and inactive stocks. This information can be used to identify the securities available in other Excel Add-In functions.


The Excel Add-In function generally uses ticker as an identifier. Companies with multiple classes of common stock return the same information for all classes. The primary ticker appears in results that include a ticker. For example, GOOG and GOOGL are alternate classes of stock for Alphabet, Inc. Requesting data for GOOG or GOOGL returns the same results. Since the primary ticker is GOOG, it will appear in ratings request results and other endpoints that return a ticker.


Tickers that include a colon are currently inactive stocks. They are no longer traded because they were acquired, went bankrupt, etc. We assign the last used ticker to the security followed by a colon and a number that increments for each new company that becomes inactive with that ticker. For example, XYZ Corp uses ticker XYZ and goes inactive. We assign the company the ticker XYZ:1 because it is the first company in our system to go inactive using ticker XYZ. If a different company, XYZ Technology, starts using ticker XYZ and goes inactive, it will be assigned XYZ:2.

Excel Add-In: Securities by Type

Returns a list of all covered securities of the requested type.


The coverage function should be used in an array formula to show the complete coverage list by security type. The Coverage function helper is very useful in this regard, as it automatically creates a properly sized array for each type of coverage.

Function
Function Name
=NC_Coverage(security_type)
Parameters
Name
Position
Description
Example
Security Type
1
Desired security type. Options:
1 = stocks
2 = ETFs
3 = mutual funds
4 = inactive stocks
1
Return Values
Name
Description
Example
Ticker
Ticker of the listed security.
AAPL
Name
Name of the security.
Apple Inc.
Type
The security type requested in the API call.
stock
Sector
Ticker of the sector the listed security belongs to.
TECHNOLOGYSEC
Last Trade Date
The last date the security traded.
2018-12-07
Sample Function Call
      
A B C D E
1 {=NC_Coverage(1)}
2
3
4
5
Sample Response Data
      
A B C D E
1 Ticker Company Name Status Sector Last Trading Date
2 A Agilent Technologies, Inc. stock HEALTHCARESEC 2018-12-07
3 AA Alcoa Corp stock BASICMATERIALSSEC 2018-12-07
4 AABA Altaba Inc stock FINANCIALSSEC 2018-12-07
5 ...
Sample Helper Use

Excel Add-In: Single Security

Returns more information about a single security.


The single security coverage function should be used in an array formula to show the full results. The array returned is 12 cells wide by 2 cells high; please pre-select an area of that size in Excel before entering the formula.

Function
Function Name
=NC_Coverage(security_type, ticker)
Parameters
Name
Position
Description
Example
Security Type
1
Desired security type. Options:
1 = stocks
2 = ETFs
3 = mutual funds
4 = inactive stocks
1
Ticker
2
Ticker of the security.
AAPL
Return Values
Name
Description
Example
Ticker
Ticker of the listed security.
AAPL
Name
Name of the security.
Apple Inc.
Type
The security type requested in the API call.
stock
Sector
Ticker of the sector the listed security belongs to.
TECHNOLOGYSEC
Sector Name
Name of the sector the listed security belongs to.
Technology Sector
Subsector
Ticker of the subsector the listed security belongs to.
NA
Subsector Name
Name of the subsector the listed security belongs to.
Technology Equipment Subsector
Industry
Ticker of the industry the listed security belongs to.
COMPUTERSPHONESHOUSEHOLDELECTROI
Industry Name
Name of the industry the listed security belongs to.
Computers, Phones & Household Electronics Industry
Subindustry
Ticker of the subindustry the listed security belongs to.
NA
Subindustry Name
Name of the subindustry the listed security belongs to.
Phones & Handheld Devices
Last Trade Date
The last date the security traded.
2018-12-07
Sample Function Call
      
A B C D E F G H I J K L
1 ={NC_Coverage(1, "AAPL")}
2
3
4
5
Sample Response Data
      
A B C D E F G H I J K L
1 Ticker Company Name Status Sector Sector Name Subsector Subsector Name Industry Industry Name Subindustry Subindustry Name Last Trading Date
2 AAPL Apple Inc. stock TECHNOLOGYSEC Technology Sector NA Technology Equipment Subsector COMPUTERSPHONESHOUSEHOLDELECTROI Computers, Phones & Household Electronics Industry NA Phones & Handheld Devices 2018-12-07
3
4
5

Excel Add-In: Coverage Errors

Error
Message
Error Code
InvalidSecurityType
Invalid security type. Available options: stock, etf, mf, inactive, sector, and industry.
400