IPR Strategies - Value-Factor

Owned by Helena Mak
Last updated: May 31, 2021
2 min read

For the D&B Project:

  • Ignore iprstrat_IPR-ValueFactor_sample_2007-2020.csv.gz

  • Ingest: iprstrat_IPR-ValueFactor_backfile_2007_01-2021_04.csv.gz - this full backfile includes all history up until the most recent valuation slice as of 2021-04-30.

  • Monthly updates: In the future, they will upload deltas since historical data remain unchanged (until there is a new valuation version).

  • For dunsification, please see match logic file and instructions below.

 

IMPORTANT:

  1. Please check for character encoding prior to data ingestion.

  2. The file uses:

    1. Separator: comma ,

    2. Text qualifier: double quote "

    3. Escape character: backslash \

  3. Escaping special characters:

    1. Records in data files may contain both commas and double quotes, and the way the vendor chose to generate a robust CSV is to escape special characters (double quotes and commas) and add double quotes around each field on top of it.

    2. For CHAR and VARCHAR columns in the data files, an escape character (\) is placed before every occurrence of the following characters:

      1. The delimiter character specified for the unloaded data (in this case, it is ,)

      2. The escape character: \

      3. A quotation mark character: " or ' (if both ESCAPE and ADDQUOTES are specified in the UNLOAD command). See https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html for more information.

For example,

  • the company name "HEINEN ELEKTRONIK GMBH \"" should be read as HEINEN ELEKTRONIK GMBH "

  • the company name (I am just making this example up) "ABC\\DEF 123\, \"INC.\""should be read as ABC\DEF 123, "INC."

 

Data Dictionary as of 05/12/2021:

ISO Code Substitutions:

 

For Match Logic, please see the latest file on:

To match the data,

  • add a new field called ISIN_country and populate this field:

    • If primary_ISIN = ‘Unlisted’ or null, then ISIN_country = null

    • Else ISIN_Country = first two chars of primary_ISIN

  • add a new field called countryISOAlpha2Code

    • If country = null, countryISOAlpha2Code = ISO code substitution on top of ISIN_country field (e.g. if ISIN_Country = ‘PR’, then countryISOAlpha2Code = ‘US’)

    • Else countryISOAlpha2Code = ISO code substition on top of country field (e.g. if ISIN_Country = ‘VG’, then countryISOAlpha2Code = ‘GB’)

    • Please note that certain country codes will not exist - these are old country codes like CS or SU. If you can’t find a substitution, then countryISOAlpha2Code = null.


Match Input fields explanation:

  • name → name

  • countryISOAlpha2Code → countryISOAlpha2Code

  • town_or_address → addressLocality

 

Acceptance rules (confirmed)

  • Accept all confidence code >=7

  • If ConfidenceCode <7, accept matches where matchGrade has the following pattern A*

  • Apply '1' AcceptanceIndicator to all above conditions