↑

Get help

Need to have one FactSet account in order to use data dictionaries and user guides.

FactSet Table and Data Dictionary Navigator:
https://open.factset.com/navigator/

Use the search field (🔍) on the top right $\rightarrow$ Corresponding tables will pop up $\rightarrow$ click the table to investigate data included

Tip for using Data Navigator: tick the box “My Products” to narrow down your results. This limits the results to products you have access to.

Q: How to check all tables in one Schema?
A: Enter fp_v2 in the search text field $\rightarrow$ choose Delivery Method tab $\rightarrow$ Table Overviews

Note that, there is a drop down list on the right, with 3 options:

  • All Sources
  • API (will direct to FactSet website)
  • Data Feed (stay in Data Navigator)

Delivery Method

Q: How to check all data items in one Schema?
A: Enter fp_v2 in the search text field $\rightarrow$ choose Data Item tab $\rightarrow$ Item short description.

Data Description

FactSet Marketplace provides an overview of schemas. You can search your schema of interest, then check the documents, which include data methodology

  • Search Box:

    FactSet Marketplace

  • Data Feed: include sample data, SQL queries, documentations

    FactSet Data Feed

  • API: access through Developer Portal, include code snippets to access the data

    FactSet API

How to look up documentations, help files for one theme database, eg Fundamentals? Get an overview for one theme.

  1. In the Marketplace, search for “Fundamentals”. Open the one tagged “Data Feed”.

    FaceSet Marketplace

  2. Click “RESOURCES”. You will have several options:

    FaceSet DataFeed

    • Data Methodology: general overview.
    • Data Dictionary: summary of table and included fields in pdf. 每张表包含的字段。
    • Data Items Definitions: detailed definitions for fields in pdf. 字段具体计算方式。
    • View in Data Navigator: nice portal for an overview of both tables and feild descriptions. UI做的比较好，用户友好。

Check detailed data dictionary, field definition, mappings for values and their meanings.

Online Assistant: https://my.apps.factset.com/oa/pages/8

Collection of User Guides for available data schemes: https://my.apps.factset.com/oa/pages/15222

Q: How to use the Online Assistant?
A: When you search on FactSet API or developer portal, there is usually a link pointing to “Calculation Details” found on Page #(…). Enter the page number on the Online Assistant or click the link.

FactSet OA Search

Or you can nagivate inside the OA, for instance,

  • Database Help $\rightarrow$ Financials $\rightarrow$ FactSet Fundamentals.
  • Database Help $\rightarrow$ Pricing and IPO $\rightarrow$ FactSet Equity Prices

FactSet OA Start Page

Tip: you can print the OA part which you find useful and want to save for later. Use the print button on the top-right corner.

FactSet OA Print

Main takeaway: FactSet and SQL are tools to download data. Just download the full dataset containing what you are interested. Use other programming language to process the data.

  • It’s more flexible that way. SQL is not designed to do data manipulation.
  • Only do the most necessary data cleaning job in SQL. More refined data cleaning, do it in R or Python.

Reference Data

Look up FactSet ID $\rightarrow$ Symbology Schema contains mappings between FactSet Permanent Identifiers and CUSIPs, SEDOLs, ISINs, and tickers.

  • It allows users to translate from a wide variety of public universal symbols (CUSIP, SEDOL, ISIN, Bloomberg FIGI, etc.) to FactSet’s native symbology.

Symbology

Global security identifier

fsym_id is FactSet identifier, associated with a security. There are 3 types of fym_id:

  • Security Level
  • Regional Level
  • Listing Level

Security Level

Format Six alpha-numeric characters, excluding vowels, with an -S suffix (XXXXXX-S)
Definition Identifies a security
Allocation Rules All equity and fixed income securities that exist on FactSet are allocated a security-level permanent identifier
Timeliness For equities and fixed income instruments, the security-level permanent identifier is available after the security is available in our system
Example DJC0PL-S (FactSet common stock)

Regional Level

Six alpha-numeric characters, excluding vowels, with an -R suffix (XXXXXX-R)

Identifies the security’s best regional security data series per currency

Listing Level

Six alpha-numeric characters, vowels excluded, with an -L suffix (XXXXXX-L)

sym_v1.sym_isin contains ISIN to FactSet Permanent Identifier (FSYM_ID) mappings for equity and fixed income securities included in the FactSet Symbology universe.

An ISIN is uniform identification of securities that are traded and settled. It is used on shares, options, debt security, derivatives, and futures trading. It is being used in most parts of the world, especially in Europe.

ISINs are mapped to security level FactSet Permanent Identifiers.

  • Non-North American ISINs are included by default.
  • For users with access to CUSIP, North American ISINs are included as well.
Field Name Description
fsym_id FactSet security level identifier assgined to a security
isin ISIN assgined to a security

Procedure given ISIN and get price/return data:

  1. Use sym_v1.sym_isin to find security level fsym_id

    -- Given ISIN, fsym_id mapptings
SELECT * FROM sym_v1.sym_isin
    WHERE sym_v1.sym_isin.isin IN ('US89417E1091', 'US0200021014', 'US0268747849')

  2. Use sym_v1.sym_coverage to find regional level fsym_id

    Ex.1

    -- From security to regional level fsym_id
SELECT 
    cov.*,
    tr.ticker_region 
FROM sym_v1.sym_coverage AS cov
LEFT JOIN sym_v1.sym_ticker_region AS tr
    ON tr.fsym_id = cov.fsym_regional_id
WHERE cov.fsym_id IN ('DNLRLW-S')

    The column you need is fsym_regional_id. proper_name is useful too. It is suggested to merge with ticker_region because it is more easily to read.

    Tip:

    • LEFT JOIN is safer than (INNER) JOIN.

      Sometimes, you have 0 row returned. You can change to LEFT JOIN and see what is the problem. But the caveat is that you might get returned rows with all NA values for your columns of interest, which are not helpful.

    • If you use SELECT *, instead of specifying all columns explicitly as the query above, the returned result will contain repetative columns, such as fsym_id, which exists both in cov and tr.

  3. Get price (fp_v2.fp_basic_prices) and return (fp_v2.fp_total_returns_daily) data given regional level fym_id.

    -- Price and OHLC
SELECT 
    price.*, 
    sym.ticker_region, 
    cov.proper_name, 
    p.one_day_pct as DailyReturns
FROM fp_v2.fp_total_returns_daily AS p
LEFT JOIN fp_v2.fp_basic_prices AS price
    ON price.fsym_id = p.fsym_id 
    AND p.p_date = price.p_date
LEFT JOIN sym_v1.sym_ticker_region AS sym
    ON p.fsym_id = sym.fsym_id
JOIN sym_v1.sym_coverage AS cov
    ON sym.fsym_id = cov.fsym_id
WHERE p.fsym_id IN ('VS59M7-R')

    Note that when downloading data for a large number of stocks,

    1. if using Notebooks, need to change max table rows allowed in Azure. Otherwise cannot save all to csv.
    2. recommend to use .sql. It is much faster to run the query and save to csv!

sym_v1.sym_cusip

This table contains CUSIP to FactSet Permanent Identifier (FSYM_ID) mappings for those securities included in the FactSet Symbology universe.

  • A unique CUSIP identifier for each individual security stays the same, regardless of the exchange where the shares were purchased or venue on which the shares were traded.

  • A CUSIP is a nine-character numeric or alphanumeric code that uniquely identifies a North American financial security for the purposes of facilitating clearing and settlement of trades.

Name description
fsym_id Unique FactSet-generated identifier assigned to a security
cusip Current, or most recent CUSIP assigned to the security.
Mostly used in securities that are traded, cleared, and settled in North America, particularly in the US.

sym_v1.sym_fi_sedol_ticker

This table contains SEDOL to Ticker Exchange to FactSet Permanent Identifier (FSYM_ID) mappings for those listings included in the FactSet Symbology universe.

Name description
fsym_id Unique FactSet-generated identifier assigned to a security
ticker_exchange Exchange ticker assigned to the listing. The ticker is appended with the exchange code that corresponds to the exchange on which the listing trades.
psedol Primary seven-character identification code assigned to securities that trade on the London Stock Exchange and various smaller exchanges in the United Kingdom
sedol Seven-character identification code assigned to securities that trade on the London Stock Exchange and various smaller exchanges in the United Kingdom
exchange Exchange
iso_country Two-letter code for the country in which the entity is domiciled. This would be the country that corresponds to the entity’s headquarters. For funds the domicile represents where the fund is registered and established.

sym_v1.sym_ticker_region

This table contains FactSet Permanent Identifier (FSYM_ID) mappings to Exchange Specific Ticker.

  • fsym_id Unique FactSet-generated identifier representing a regional level instrument

  • ticker_region Current or most recent regional ticker assigned to the listing.

    • Ticker is the current or most recent global ticker assgined to the listing.

      Ticker is NOT a safe security identifier as it cannot refer to a unique security. ❌

      Securities listed on several exchanges may have the same ticker or not.

      For instance, Royal Dutch Shell (SHEL) uses different tickers depending on the exchange:

      • NYSE (U.S.): SHEL
      • LSE (UK): SHEL
      • Euronext Amsterdam (Netherlands): SHELL

      It is recommened to use SAFE identifiers which can refer to a unique security, such as ISIN. The benefits of using tickers is that it is human readable.

    • The ticker is appended with the two digit code that corresponds to the county or region in which the listing trades.

      Ticker and region connected by a hypen -. For instance, AAPL-US stands for that AAPL is traded at the US exchange.

  • You need a combination of ticker-exchange-date to uniquely identify a security.

sym_v1.sym_coverage

This table contains reference data on the securities and listings.

  • fref_security_type check security type, see the complete list here.

    • SHARE Share/Common/Ordinary

    • PREFEQ Preferred Equity
    • MF_O Open-ended Fund.
    • MF_C Closed-ended Fund.

  • fref_listing_exchange Exchange where the security trades.

    FactSet Exchange Codes table (OA page #14134)

    • NYS: New York Stock Exchange
    • NAS: Nasdaq

  • universe_type denotes whether the security is equity (EQ) or fixed income (FI).

Field Name Description
fsym_id FactSet identifier assgined to a security
Can be regional- or listing level fsym_id
currency Code representing the currency in which the security or listing trades
proper_name Name of the security associated with the identifier
fsym_primary_equity_id Security level fsym_id.
FactSet Permanent Identifier representing the primary equity on the identifier’s most liquid exchange
fsym_primary_listing_id Listing level fsym_id.
Most liquid individual listing for a currency and country or security.
active_flag Flag indicating whether the record is currently active (1) or inactive (0). Please note that this field is not supported for bank loans; therefore null values represent “unknown”.
fref_security_type Code denoting the security’s type, see OA page #(15776) for a list of security type descriptions.
fref_listing_exchange Code denoting the exchange on which the security trades. see OA page #(14134) for code representation.
listing_flag Flag indicating that the identifier represents an individual listing
regional_flag Flag indicating that the identifier represents the most liquid listing for a specific currency and country
security_flag Flag indicating that the identifier represents a security
fsym_regional_id Regional-level FactSet Permanent Identifier associated with the identifier. This is used when looking up prices and returns.
fsym_security_id Security-level FactSet Permanent Identifier associated with the identifier
universe_type Code denoting whether the security is considered equity (EQ) or fixed income (FI)

Price and Return Data

API definitions:

https://developer.factset.com/api-catalog/factset-global-prices-api#apiDefinition

Dividens, Stock Splits, and Spinoffs

Prices FAQs: https://my.apps.factset.com/oa/pages/20340

Q: Why might historical prices on FactSet not match other vendors?
A: It is FactSet’s policy to NOT adjust historical prices for cash dividends, only for splits. However, Bloomberg and Reuters do adjust historical prices for cash dividends. The cash dividend, though, is accounted for in the total return calculation. Therefore, total return values will always match with other vendors.

Q: How to adjusted for cash dividend?
A: The adjustment factor applied historically is calculated as:

\[1-\frac{\text{Cash Dividend Amount}}{\text{Previous Day's Closing Price}}\]

Q: Can I view the split-adjusted prices?
A: Yes. Prices are split-adjusted by default. Splits are applied to securities nightly on FactSet.

Price Adjustment Factors Used for Corporate Actions

Online Assistant Page #(12619)

Corporate Action Events Summary

Event Category:
CASH_DIVS = Cash Dividends
STOCK_DIST = Stock Distributions
SPLITS = Splits (include forward split and reverse split)
SPINOFFS = Spin Offs
RIGHTS = Rights Issue

Forward split: an increase in a company’s number of shares outstanding with no change in the company’s market value.

E.g., Coldwater Creek (5762736) had a 3-for-2 split on 9/9/2003.

The price adjustment factor for Coldwater Creek:

2 / 3 = 0.6667 (= 3 for 2)

Reverse split: a decrease in a company’s number of shares outstanding with no change in the company’s market value.

E.g., Marconi Corp. (333544) had a consolidation of 5 old shares into 1 new share on 9/9/2003.

The price adjustment factor for Marconi Corp. is:

5 / 1 = 5 (= 1 for 5)

Bonus issue: an increase in a company’s number of shares outstanding by an allocation of additional shares at no cost to existing shareholders, causing no change to the company’s market value.

Newsphone Hellas (753862) had a 3 for 1 bonus issue on 9/2/2003.

The adjustment factor is calculated as:

1/(3+1) = 0.25 (= 4 for 1)

Stock dividend: represents a payment of a dividend in the form of stock rather than cash, which results in an increase in the company’s number of shares outstanding.

E.g., Test Research (6338921) had a 20% stock dividend on 9/16/2003.

The adjustment factor is calculated as:

1 / (20 / 100 + 1) = 0.83333 (= 6 for 5)

Cash dividend: the stock price drops by the amount of the dividend on the ex-dividend date.

The ex-dividend date is typically the same day as the record date. If investors want to receive a stock’s dividend, they have to buy shares of stock before the ex-dividend date. The record date is the date the company determines who are shareholders who receive dividends.

For example, suppose a stock trading for $50 per share declares a $0.50 dividend. On the ex-dividend date, the price adjusts to $49.50 ($50 minus the $0.50 dividend) for each share as of the record date.

An announcement about cash dividends from Borregaard ASA

BORREGAARD ASA: SHARES TRADED EX-DIVIDEND TODAY

12/04/2024

The shares in Borregaard ASA will be traded ex-dividend of NOK 3.75 per share as from today, 12 April 2024.

Borregaard ASA Sarpsborg, 12 April 2024

Contact: Lotte Kvinlaug, Investor Relations Officer, +47 922 86 909

This information is subject to the disclosure requirements pursuant to Section 5 -12 of the Norwegian Securities Trading Act.

Dividend Cycle

Dividend cycle

  • Dividend Declaration Date: This is the date on which the AGM takes place, and the company’s board approves the dividend issue

  • Record Date: The date the company decides to review the shareholder’s register to list all eligible shareholders for the dividend. Usually, the time difference between the dividend declaration date and the record date is 30 days.

  • Ex-Date/Ex-Dividend date: With the T+1 settlement cycle, the ex-dividend date normally is on the same day as the record date. Only shareholders who own the shares before the ex-dividend date are entitled to receive the dividend. This is because, in India, the equity settlement is on a T+1 basis. So for all practical purposes, if you want to be entitled to receive a dividend, you need to ensure you buy the shares before the ex-dividend date.

    When the stock goes ex-dividend, usually, the stock drops to the extent of dividends paid.

  • Dividend Payout Date: The date on which the dividends are paid to shareholders listed in the company register.

Rights issue: represents an offering of shares to existing shareholders who hold rights that entitle them to buy newly issued shares at a discount from the current market price.

Spinoff

A spinoff represents a form of corporate divestiture that results in a subsidiary or division becoming an independent company. In a traditional spinoff, shares in the new entity are distributed to the parent corporation’s shareholders of record on a pro-rata basis. Spinoffs are treated as special dividends and FactSet calculates any special dividends that have been paid as the following:

\[\begin{align*} \text{Spinoff Dividend} = \text{Previous day’s closing price of the spun off company} \times \\ \text{ratio of shares being spun off of the parent company} \end{align*}\]

The special dividend is then used to calculate an adjustment factor:

\[\begin{align*} \text{Adjustment factor} = \frac{\text{Price of the Parent Security Before the Ex-date} - \text{Spinoff Dividend}}{\text{Price of the Parent Security Before the Ex-date}} \end{align*}\]

Ex1

CSR Ltd. (6238645) spun off Rinker Group Ltd. on 3/31/2003. CSR shareholders received one share of Rinker Group for each share of CSR held. CSR’s closing price on 3/28/2003 (the last trading day before the ex-date) was 6.49 AUD. The price of Rinker Group on 3/31/2003 was 4.775 AUD.

The special dividend is calculated as:

\[4.775 \times 1 = 4.775\]

The adjustment factor is calculated as:

\[\frac{6.49 - 4.775}{6.49} = 0.26425\]

The price adjustment factor for CSR is 0.26425.

Ex2

XTO Energy (XTO) spun off Cross Timbers Reality Trust (CRT) on 8/28/2003. Shareholders received 0.0074 shares of CRT for every one share of XTO held. The CRT price on 8/27/2003 was 22.82 USD. XTO’s price on 8/27/2003 was 20.29 USD.

The special dividend is calculated as:

\[22.82 \times 0.0074 = 0.1688868\]

The adjustment factor is calculated as:

\[\frac{20.29 - 0.1688868}{20.29} = 0.991677\]

The price adjustment factor for XTO is 0.991677.

Closing prices must be adjusted for corporate actions (mainly split and dividends) to maintain comparability over time.

Calculating total return:

  • Adjust for split and spin off

    See above.

  • Adjust for dividends

    1. No adjustment. Dividends are excluded from return.
    2. Simple return. Dividends are included but not reinvested. Two options of timepoints when dividends are recorded in return:
      1. Dividends Received on exdate
      2. Dividends Received on paydate
    3. Compound return. Dividends are included and reinvested. Two options of timepoints when dividends are reinvested:
      1. Dividends reinvested on exdate.
      2. Dividends reinvested on paydate.

See Online Assistant #(8748) for detailed formulas for calculating total return.

fp_v2.fp_basic_prices contains daily prices (OHLC) and volume of a security.

Note that the fsym_id uses a regional-level ID.

Field Name Description
fsym_id Unique FactSet-generated identifier representing a regional level instrument
p_date Date on which the security priced
currency Code representing the currency in which the security or listing trades
p_price Closing price
p_price_open Opening price
p_price_high High price
p_price_low Low price
p_volume Volume, unit: 1000;

Q: is p_price the adjusted closing price?

A: No, it is raw closing price, therefore NOT of much use. You want to use the table fp_v2.fp_total_returns_daily, which contains total return adjusted for dividends and splits.

Return calculated based on closing price is the same as the one based on adjusted price except for when there are corporate actions.

fp_v2.fp_total_returns_daily

This table contains historical daily price returns with dividends reinvested on the exdate. That is, the return is based on the adjusted closing price.

Include the following 4 columns

Field Name Description
fsym_id Unique FactSet-generated identifier representing a regional level instrument
p_date Date on which the security priced
currency Currency in which the security or listing trades
one_day_pct Returns since the previous trade date

  • currency doesn’t tell about the exchange on which the security trades as one exchange is capable of listing equitie denominated in different currencies.

  • If want to get exchange info, better to use fref_listing_exchange (exchange code) or ticker_region (country code).

  • one_day_pct is simple percentage return based on adjusted closing price: \(r_t = \frac{P_t}{P_{t-1}}-1\)

fp_v2.fp_total_returns_cagr contains instrument level return data covered by FactSetGlobal Prices.

Only include the previous trading day close price return.

P_BETA_PR calculates a beta coefficient for a company relative to it’s local index for the following periods of measure: 1M, 2M, 3M, 6M, 1Y, 2Y, 3Y, 5Y.

Fundamentals

Schema Name: ff_v3

Data Navigator — all tables: https://open.factset.com/navigator/product/3734

Online Assistant:

If need to check data item definition, use the FactSet Marketplace Catalog → FactSet Fundamentals → RESOURCES → Documentation → Data Dictionary or Data Methodology: https://www.factset.com/marketplace/catalog/product/factset-fundamentals

FactSet Fundamentals

All tables in the Fundamentals schema:

  • According to the documentation, Fsym_id can be represented at either theregional (-R) or security level (-S). [False info ❌]
  • But actually can ONLY be queried by regional level fsym_id. ✅

Frequency of fundamentals: annual, quarlty, semi-annual, LTM (last 12 mons), YTD (Year to Date)

Q: What is YTD?
A: Represents the current calendar year for U.S. corporations and fiscal year for non-U.S. Corporations. Not necessarily one whole year, whereas LTM makes sure that the last 12 months are included.

ff_v3.ff_basic_af

Includes data from three financial statements; annual data; about 130 data fields.

Field Name Unit Factor Description
ff_com_shs_out 1000000 Common Shares Outstanding

ff_v3.ff_advanced_af more detailed version of ff_basic_af; about 500 data fields;

Industry Classification

ff_v3.ff_sec_coverage.ff_gen_ind contains codes denoting the general industry classification for a company.

Issue: too crude

Classification

Code Industry
01 Industrial
02 Utility
03 Transportation
04 Bank
05 Insurance
06 Other Financial

sym_v1.sym_entity_sector

This table contains FactSet Entity Identifier (FactSet_Entity_ID) to sector and industry classification mappings.

Name description
factset_entity_id Unique FactSet-generated identifier representing an entity
primary_sic_code SIC Four-digit code that identifies the business type of the entity
industry_code FactSet proprietary industry code assigned to the entity
sector_code Four-digit code identifying FactSet broad industry sector classification

sym_v1.sym_entity_sector_rbics

This table contains the current FactSet Entity Identifier (FactSet_Entity_ID) to RBICS sector (level 2) mappings.

Name description
factset_entity_id Unique FactSet-generated identifier representing an entity
l2_id RBICS level 2 identifier
focus_flag Flag indicating whether the company exists in the RBICS Focus universe

rbics_v1.rbics_structure contains RBICS industry classification structure.

Interest Expenses

Data Field Table Definition
FF_INT_EXP_TOT ff_basic_af Interest Expense - Total
Represents the total interest expense (periodic charges) and all the cost necessary to make the loan/debt available.
It includes: Interest expense on debt and Interest capitalized.
FF_INT_INC_NET   Net Interest Income.
FF_INT_INC ff_basic_af Interest income

ESG

Overview:

https://insight.factset.com/resources/at-a-glance-factset-truvalue-sasb-scores-datafeed

https://www.factset.com/marketplace/catalog/product/factset-truvalue-scores-and-spotlights

Other Major Rating Agencies:

https://www.iriscarbon.com/a-beginners-guide-to-esg-rating-agencies-and-methodologies/

SASB: Sustainability Accounting Standards Board

Q: Difference between Material categories and All categories?

  • All: all 26 ESG topics in a single overall score.

    This value is equal to the cumulative average of all 26 SASB categories.

  • Material: this category represents a composite score of all ‘material’ SASB categories for the given entity. This is industry specific. The idea of Materiality is to identify which ESG sub-categories are financially material, and which are not.

    For example, for a hardware company like Apple, Environmental categories, such as GHG Emissions, Air quality, should be of little concern, while Data Security can be a material category for evaluating ESG.

    To help us identify which of the many sub-categories feeding into ESG scores are indeed material, we use the SASB industry-level Materiality Map as a guide. For example, feeding into an overall ESG score is performance on a variety of sub-categories such as labour practices, emissions, water and waste management, business ethics and so on. Those subcategories that are not identified as material are not included in our overall score for each industry.

More about Materiality

The material score can be used to differentiate between companies in a way that a traditional aggregated ESG score does not facilitate. It allows us to distinguish between companies who score highly on ESG issues that are financially material to their business, from those who score highly on issues that are not financially material to their business. Our evidence suggests that the Russell Investments Material ESG Scores are better predictors of return compared to traditional ESG scores.

Bottom line: Not all ESG issues matter equally

The relevance of ESG issues varies industry to industry, company by company. For example, fuel efficiency has a bigger impact on the bottom line of an airline than it does for an investment bank. So, rather than adopt a one-size-fits-all approach, we worked to develop an ESG scoring system that is specific to a company and its profitability.

Case Study

Quadrants based on Material and Immaterial categories

Materiality

Performance

Conclusion: Material categories matter.

  • Controlling for immaterial issues, high performance on material issues led to significantly higher alphas than low performance.
  • Controlling for material issues, high performance of immaterial issues is not helpful in improving returns, or even value-detracting, see the difference between portfolios 1 and 4.

Materiality

Score types

Truvalue

  • The Insight Score measures a company’s longer-term ESG track record.

    Q: What is Adjusted Insight score?

    A: Adjusted Insight Score = (# of articles / 5) x Company Insight Score + ((5 - # of articles) / 5) x Industry (or Sector) Median Insight Score.

    Scores are less sensitive to daily events and reflect the enduring performance record of a company over time. Scores are derived using an exponentially-weighted moving average of the Pulse, defined below, and the half-life of an event’s influence on the overall score is 6 months.

  • The Pulse Score measures the near-term performance changes that highlight opportunities and controversies, enabling real-time monitoring of companies. It focuses on events of the day and provides a responsive signal to alert investors to dynamic moves.

  • The Momentum Score measures a company’s ESG behavior trend over time.

    It is a unique ESG metric in the industry that gives investors a high-precision view of the trajectory of a company’s ESG performance, relative to peers. It does so by precisely articulating upward or downward movement, relative to that of all others in the universe, making it a measure that enhances quantitative workflows.

  • The (article) Volume Score measures the information flow or number of articles about a company and is represented as an aggregate value over the past 12 months.

    Note: Lots of missing values for Volume measures.

TruValue Identifiers

sym_v1.sym_coverage.fsym_id $\rightarrow$ sym_v1.sym_coverage.fsym_security_id $\rightarrow$ tv_v2.tv_sec_entity_hist.fsym_id $\rightarrow$ tv_v2.tv_sec_entity_hist.factset_entity_id $\rightarrow$ tv_v2.tv_factset_id_map.factset_id $\rightarrow$ tv_v2.tv_factset_id_map.factset_id.provider_id $\rightarrow$ tv_v2.tv_esg_ranks.tv_org_id

tv_v2.tv_sec_entity_hist

This table maps FSYM_IDs to FACTSET_ENTITY_IDS for the SASB Codified Edition universe. The mappings are provided at the security level  FSYM_ID.

Name Name
fsym_id Security level fsym_id, XXXXXX-S
Match with sym_v1.sym_coverage.fsym_id
start_date Date the identifier was first associated with the fsym_id
end_date Date the identifier was last associated with the fsym_id
factset_entity_id Unique FactSet-generated identifier representing an entity

tv_v2.tv_factset_id_map

This table maps SASB Codified Edition identifiers to FactSet identifiers.

Name description
provider_id TruValue Identifier published by the provider
provider_id_type Field name containing the provider identifier
factset_id_type Type of FactSet identifier in the factset_id field
id_start_date Date the provider identifier was first associated with the factset_id
id_end_date Date the identifier was last associated with the factset_id
factset_id FactSet identifier representing an instrument or an entity
Match with tv_v2.tv_sec_entity_hist.factset_entity_id

ESG data is identified by provider_id.

Pulse, Insight, Momentum, Volume

tv_v2.tv_pulse This table measures short-term, real-time performance across the 26 SASB categories.

Name Description
tv_org_id Unique identifier assigned by Truvalue that is applied on an organization level
Match this with tv_v2.tv_factset_id_map.provider_id
tv_instrument_id Unique identifier assigned by Truvalue that is applied on an instrument level
tv_date Indicates the date by which the scores are based on as of 11:59PM GMT. Example: a date of 9/1/2013 indicates that the corresponding score data was based on the scores as of 11:59PM GMT on 9/1/2013
all_categories_pulse All Categories Pulse: Reflects a daily Pulse Score that aggregates all 26 SASB categories using a running sum average
materiality_pulse Materiality Pulse: Reflects a daily Pulse Score that aggregates only the material SASB categories for each company using a running sum average

Note that ESG scores are consistent on an entity level, that is, all instruments (equity and FI) share the same ESG ranking.

In order to save memory, you might want to keep distinct combinations of tv_org_id and tv_data.

SELECT DISTINCT
    p.tv_org_id, 
    p.tv_date, 
    p.all_categories_pulse,
    p.materiality_pulse
FROM tv_v2.tv_pulse AS p
WHERE p.tv_org_id in ('4ae152fe-9219-4c23-b9f8-163f9873171b', '4f81c8ff-9612-41cc-8fe2-99db6ecf257b')

Q: How computationally costly is DISTINCT?
A: 2 stocks like the code above used 41 seconds.

Time used for running a query for 283 stocks given tv_org_id with DISTINCT

  • 0 rows for 19 mins
  • at 19 mins, start showing rows; rows increasing by 10’s of thousands
  • Total time used for running the query: 21 min 12 sec. Not too bad.
  • Total rows returned: 1.5 million.
  • Writing to csv is actually fast, less than one min.

tv_v2.tv_insight This table measures long-term performance across the 26 SASB categories

Name Description
tv_org_id Unique identifier assigned by Truvalue that is applied on an organization level
Match this with tv_v2.tv_factset_id_map.provider_id
tv_instrument_id Unique identifier assigned by Truvalue that is applied on an instrument level
tv_date Indicates the date by which the scores are based on as of 11:59PM GMT. Example: a date of 9/1/2013 indicates that the corresponding score data was based on the scores as of 11:59PM GMT on 9/1/2013
all_categories_insight All Categories Insight: Reflects an Insight Score that aggregates all 26 SASB categories and is derived using an exponentially-weighted moving average of the Pulse Score, with a six month half-life
materiality_insight Materiality Insight: Reflects an Insight Score that aggregates only the material SASB categories for each company using an exponentially-weighted moving average of the Pulse, with a six month half-life

tv_v2.tv_momentum This table measures the trend of a given company by measuring the slope or trajectory of the Insight Score over a trailing twelve month time period.

Name Description
tv_org_id Unique identifier assigned by Truvalue that is applied on an organization level
Match this with tv_v2.tv_factset_id_map.provider_id
tv_instrument_id Unique identifier assigned by Truvalue that is applied on an instrument level
tv_date Indicates the date by which the scores are based on as of 11:59PM GMT. Example: a date of 9/1/2013 indicates that the corresponding score data was based on the scores as of 11:59PM GMT on 9/1/2013
all_categories_momentum All Categories Momentum: Reflects a Momentum Score that aggregates all 26 SASB categories and measures the slope of the Insight Score over the trailing twelve month time period
materiality_momentum Materiality Momentum: Reflects a Momentum Score that aggregates only the material SASB categories and measures the slope of the Insight Score over the trailing twelve month time period

tv_v2.tv_volume This table measures the number of unique articles calculated for each company over a trailing twelve month period of time.

Name Description
tv_org_id Unique identifier assigned by Truvalue that is applied on an organization level
Match this with tv_v2.tv_factset_id_map.provider_id
tv_instrument_id Unique identifier assigned by Truvalue that is applied on an instrument level
tv_date Indicates the date by which the scores are based on as of 11:59PM GMT. Example: a date of 9/1/2013 indicates that the corresponding score data was based on the scores as of 11:59PM GMT on 9/1/2013
all_categories_catvol All Categories Category Volume TTM: Represents the total number of times any of the 26 SASB categories received a score over a trailing twelve month (TTM) period of time
materiality_catvol Materiality Category Volume TTM: Represents the total number of times a material category received a score over a trailing twelve month (TTM) period of time
all_categories_articlevol All Categories Article Volume TTM: Represents the total number of unique articles/events that were scored for any of the 26 categories over a trailing twelve month (TTM) period of time
materiality_articlevol Materiality Article Volume TTM: Represents the total number of unique articles/events that were scored for any of the material categories over a trailing twelve month (TTM) period of time

tv_v2.tv_esg_ranks

Most complete ESG measures.

This table features adjusted insight scores which measure a company’s ESG performance, generating scores for lower-volume and zero-volume firms by blending company scores with industry medians.

Also included are industry percentiles to gauge adjusted insight scores relative to peers as well as easy to decipher fields which rank companies from Laggards to Leaders.

Name Data Type Description
tv_org_id VARCHAR(50) Unique identifier assigned by Truvalue that is applied on an organization level
Match this with tv_v2.tv_factset_id_map.provider_id
tv_instrument_id VARCHAR(50) Unique identifier assigned by Truvalue that is applied on an instrument level
tv_date DATE Indicates the date by which the scores are based on as of 11:59PM GMT. Example: a date of 9/1/2013 indicates that the corresponding score data was based on the scores as of 11:59PM GMT on 9/1/2013
materiality_adj_insight DOUBLE Leveraging the SASB Materiality map, this field measures company ESG performance, generating scores for lower-volume and zero-volume firms by blending company scores with industry medians
materiality_ind_pctl DOUBLE Leveraging the SASB Materiality map, this field offers context on company Adjust Insight scores relative to peers in the same SICS Industry
materiality_esg_rank VARCHAR(50) Indicates if a company is a Leader, Above Average, Average, Below Average, or a Laggard, directly mapping from the SASB Materiality map Industry Percentiles
all_categories_adj_insight DOUBLE Leveraging all SASB categories, this field measures company ESG performance, generating scores for lower-volume and zero-volume firms by blending company scores with industry medians
all_categories_ind_pctl DOUBLE Leveraging all SASB categories, this field offers context on company Adjust Insight scores relative to peers in the same SICS Industry
all_categories_esg_rank VARCHAR(50) Indicates if a company is a Leader, Above Average, Average, Below Average, or a Laggard, directly mapping from all SASB category Industry Percentiles