Linking OptionMetrics with Refinitiv

Using WRDS to connect OptionMetrics data with Refinitive data

Identifiers within OptionMetrics

On the WRDS platform, there are two OptionMetrics data packages covering options market activities in the US and European markets: IvyDB US and IvyDB Europe. In addition to option pricing data, each package also carries information on the accompanying security, including pricing as well as historical identifiers associated with the security.

IvyDB US - Security Name File

The security name data for the IvyDB US package is stored on the WRDS server at: /wrds/optionm/sasdata/secnmd. Users can find OptionMetrics unique security identifier for the US data, secid, linked with other identifying information such as CUSIP, TICKER, and Issuer name. The table below shows how Citi Group's security information appears in the dataset:

secid effect_date cusip ticker issuer issue sic
103049 8-Oct-98 17296710 CCI CITIGROUP
INC
103049 4-Dec-98 17296710 C CITIGROUP
INC
103049 28-Nov-00 17296710 C CITIGROUP
INC
COM 6211
103049 22-Feb-02 17296710 C CITIGROUP
INC
COM 6021
103049 9-May-11 17296742 C CITIGROUP
INC
COM
NEW
6021
103049 31-Jul-14 17296742 C CITIGROUP
INC
COM
NEW
6211
103049 5-Feb-15 17296742 C CITIGROUP
INC
COM
NEW
6021
103049 6-Aug-20 17296742 C CITIGROUP
INC
ORDINARY
SHARES
6021

The effect_date column reports the starting date of any changes of security information. For instance, the May 9th, 2011 record above indicates a CUSIP change from the old record of 17296710 to the new record of 17296742.

IvyDB Europe - Security Name File

In a similar manner to the IvyDB US data, the European OptionMetrics data also carries a securityname file that documents all historical identifiers of the issuing company, including VALOR and ISIN. The file path on the WRDS server is /wrds/optionm/sasdata/europe/security_name. The table below reports all Volkswagen related entities in the IvyDB Europe security name data.

SecurityID VALOR Issuer ISIN EffectiveDate
501283 352780 VOLKSWAGEN DE0007664005 20010101
501284 352781 VOLKSWAGEN
VZ
DE0007664039 20010101
740977 352785 VOLKSWAGEN
SP ADR
US9286624021 20100219
740985 352786 VOLKSWAGEN
SP ADR
US9286623031 20100222
757303 20345243 VOLKSWAGEN
VZ
DE000A1R1C65 20121228
760861 23431169 VOLKSWAGEN
VZ
DE000A1YDAH5 20140117
763670 26580105 VOLKSWAGEN
VZ E15
DE000A13ST10 20150107
773641 42805829 VOLKSWAGEN
UNSP ADR
US9286625010 20180815
773642 42805834 VOLKSWAGEN
UNSP ADR
US9286626000 20180815

Top of Section

Connecting with Refinitiv

To connect the universe of option issuing entities with Refinitiv data, we recommend the use of CUSIP for US and ISIN for European entities, respectively. On the Refintiv front, users can rely on the vw_securitymasterx table to look up company identifier information such as CUSIP and ISIN. For a detailed discussion of the Refinitiv mastertables, please refer to the Linking within Refinitiv documentation. .

One caveat we want to draw attention to here has to do with the timing of CUSIP/ISIN. While OptionMetrics provides a record of historical CUSIP, Refinitiv provides only the most current CUSIP and the latest obsolete CUSIP, prevCusip, as well as only the most recent ISIN. Therefore, one should expect a high linking percentage using CUSIP or ISIN for the current period and most recent past. As users go further back into historical data, they should expect to see a lower linking percentage. This is because older CUSIPs might not be recorded by Refinitiv's prevCusip variable.

Below is a sample code snippet using Citi as an example to link between IvyDB US and Refinitiv:

# Linking using header/current CUSIP;
proc sql;
 create table link as select distinct 
 a.*, b.secCode, b.id, b.typ, b.sedol, b.name, b.country
 from optionm.secnmd as a,
 tfnref.vw_securitymasterx as b
 where (a.secid = 103049) and (a.cusip = b.cusip) ;
quit;

In addition, this SQL exercise provides the lined SECID and SecCode:

secid effect_date cusip ticker class issuer issue sic SecCode Id typ Sedol Name Country
103049 9-May-11 17296742 C CITIGROUP
INC
COM
NEW
6021 14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 31-Jul-14 17296742 C CITIGROUP
INC
COM
NEW
6211 14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 5-Feb-15 17296742 C CITIGROUP
INC
COM
NEW
6021 14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 6-Aug-20 17296742 C CITIGROUP
INC
ORDINARY
SHARES
6021 14360 C 1 229790 CITIGROUP
INC COM NEW
USA

If we were to link using the prevCusip option for Citi, the linked output is:

#  Linking using previous CUSIP;
proc sql;
 create table linkprev as select distinct a.*, b.secCode, b.id, b.typ, b.sedol, b.name, b.country
 from optionm.secnmd as a,
 tfnref.vw_securitymasterx (where=(prevCusip ne '')) as b
 where (a.secid = 103049) and (a.cusip = b.prevcusip) ;
quit;
secid effect_date cusip ticker class issuer issue sic SecCode Id typ Sedol Name Country
103049 8-Oct-98 17296710 CCI CITIGROUP
INC
14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 4-Dec-98 17296710 C CITIGROUP
INC
14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 28-Nov-00 17296710 C CITIGROUP
INC
COM 6211 14360 C 1 229790 CITIGROUP
INC COM NEW
USA
103049 22-Feb-02 17296710 C CITIGROUP
INC
COM 6021 14360 C 1 229790 CITIGROUP
INC COM NEW
USA

Notice that although two different CUSIPs for Citi ( 17296710 and 17296742 ) are linked, they nevertheless point to the same set of permanent identifers across the two database: secid = 103049 is linked with SecCode = 14360.

Top of Section

Top