The following expressions return the S&P Credit Ratings along with the dates for all of the Credit Ratings changes for a company using the S&P Capital IQ Excel Plug-In:
This expression returns the most recent date of a Credit Ratings Action:
=CIQ($A$1, "IQ_SP_ISSUER_RATING",,,"RATING_DATE")
Where cell A1 references the Identifier.
This expression returns the date of the previous Credit Ratings Action:
=IF(B$1=0,"No Additional History",CIQ($A$1,"IQ_SP_ISSUER_RATING",B1-1,,"RATING_DATE"))
Where cell A1 references the Identifier and where cell B1 is the more recent date of a Credit Rating Action, prior expression.
Note: Drag and copy this formula across Row 1 for the desired number of dates.
This expression returns the Credit Rating:
=IF(B$1=0,"Initial Rating",CIQ($A$1,"IQ_SP_ISSUER_RATING",B$1))
Where cell A1 references the Identifier and where B1 is the date of the Credit Rating Action.
To return the most recent date of a Credit Rating Action using the Formula Builder:
- Right-click cell B1. Note: Any cells can be used to insert CIQ Identifiers and Formulas.
- Select Insert Formula from the shortcut menu.
- Type the desired company or cell reference in the Identifier field.
- Select the Market Data tab.
- Click the plus sign (+) to expand the S&P Credit Ratings folder in the pick list.
- Select Issuer Credit Rating.
- Select the desired As-Of Date.
- Select the desired Rating Type from the Rating Type drop-down menu.
- Select Rating Date from theValue drop-down under Rating Option.
- Click the down arrow to expand Advanced Options.
- Select the desired National Scale Rating (NSR) or Regional Scale Rating (RSR) from the NSR/RSR Designation drop-down menu.
- Click Add Formula. Click OK.
