↑

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)

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

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

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.

Symbology

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

sym_v1.sym_coverage

This table contains reference data on the securities and listings.

  • fref_security_type check security type, see 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
  • universe_type denotes 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

sym_v1.sym_ticker_region

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

  • 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.

    • 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-US stands for that AAPL is traded at the US exchange.

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)

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 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) of a security.

fp_v2.fp_total_returns_daily

This table contains historical daily price returns with dividends reinvested on the exdate.

Include the following four columns

Field Name Description
fsym_id FactSet identifier assgined to a security
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).

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

Classification

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

rbics_v1.rbics_structure contains RBICS industry classification structure.