FactSet Tutorial
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
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)
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.
How to look up documentations, help files for one theme database, eg Fundamentals? Get an overview for one theme.
-
In the Marketplace, search for “Fundamentals”. Open the one tagged “Data Feed”.
-
Click “RESOURCES”. You will have several options:
- 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
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.
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 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.
Global security identifier
sym_v1.sym_isin contains ISIN to FactSet Permanent Identifier (FSYM_ID) mappings.
| Field Name | Description |
|---|---|
fsym_id |
FactSet identifier assgined to a security |
isin |
ISIN assgined to a security |
fsym_id is 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_cusip
This table contains CUSIP to FactSet Permanent Identifier (FSYM_ID) mappings for those securities included in the FactSet Symbology universe.
| Name | description |
|---|---|
| fsym_id | Unique FactSet-generated identifier assigned to a security |
| cusip | Current, or most recent CUSIP assigned to the security. |
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_idUnique FactSet-generated identifier representing a regional level instrument -
ticker_regionCurrent or most recent regional ticker assigned to the listing.-
Ticker is the current or most recent global ticker assgined to the listing.
-
The ticker is appended with the two digit code that corresponds to the county or region in which the listing trades.
Ticker and region are separated by a hypen
-. For instance,AAPL-USstands for that AAPL is traded at the US exchange.
-
sym_v1.sym_coverage
This table contains reference data on the securities and listings.
fref_security_typecheck security type, see complete list here.-
SHAREShare/Common/Ordinary PREFEQPreferred EquityMF_OOpen-ended Fund.MF_CClosed-ended Fund.
-
fref_listing_exchangeExchange where the security trades. Contains lots of NA values, not helpful after all.universe_typedenotes whether the security is equity (EQ) or fixed income (FI).
| Field Name | Description |
|---|---|
fsym_id |
FactSet identifier assgined to a security |
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 | FactSet Permanent Identifier representing the primary equity on the identifier’s most liquid exchange |
| fsym_primary_listing_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 |
| 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) by FactSet Symbology |
Price and Return Data
API definitions:
https://developer.factset.com/api-catalog/factset-global-prices-api#apiDefinition
Dividens, Stock Splits, and Spinoffs
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 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
- No adjustment. Dividends are excluded from return.
- Simple return. Dividends are included but not reinvested. Two options of timepoints when dividends are recorded in return:
- Dividends Received on exdate
- Dividends Received on paydate
- Compound return. Dividends are included and reinvested. Two options of timepoints when dividends are reinvested:
- Dividends reinvested on exdate.
- 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 |
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.
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 |
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 |
currencydoesn’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) orticker_region(country code).
fp_v2.fp_total_returns_cagr contains instrument level return data covered by FactSetGlobal Prices.
Only include the previous trading day close price return.
Fundamentals
Schema Name: ff_v3
Data Navigator — all tables: https://open.factset.com/navigator/product/3734
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
| 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.
ESG
Overview: https://insight.factset.com/resources/at-a-glance-factset-truvalue-sasb-scores-datafeed
Other Major Rating Agencies: https://www.iriscarbon.com/a-beginners-guide-to-esg-rating-agencies-and-methodologies/
tv_v2.tv_esg_ranks
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 |
| 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 |
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
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.
Score types
-
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.