Factset Documentation

Created by Shivani Zinjurke, Modified on Tue, 25 Mar at 1:15 PM by Shivani Zinjurke

Content

1) Overview of Factset and its elements.

2) Flowchart of Factset workflow.

3) Process workflow of the Factset

4) Queries and steps to check the data in the Snowflake

--------------------------------------------------------------------------------------------------------------

1) Overview

FactSet is a leading provider of financial data and analytical software to investment professionals. It offers a wide range of services including real-time data feeds, financial analytics, and market insights. It works on the assumption of the company's future projected numbers.

It provides business data to help with the workflow, valuable market analytics and global market insights to give a better perspective.

The data is usually reported in 4 Quarters and 2 Annuals which are forecasted periods

-------------------------------------------------------------------------------------------------------------------------------

Items fetched in Factset data:

1) EBIT: Earnings before interest and taxes (EBIT) indicate a company's profitability. EBIT is calculated as revenue minus expenses, excluding tax and interest. EBIT is also called operating earnings, operating profit, and profit before interest and taxes.

2) EBITDA: (Earnings Before Interest, Taxes, Depreciation and Amortization)

3) EPS: (earnings per share)

4) EPS_GAAP: GAAP EPS is the earnings figure derived from generally accepted accounting principles (GAAP)

5) EPS_NONGAAP: GAAP earnings per share (EPS) includes all items but discontinued operations and the cumulative effect of accounting changes. Non-GAAP EPS generally excludes asset impairments, usually gains/losses on asset sales (at non-financials), and often other lumpy items like large restructuring, tax or litigation charges.

6) FCF: Free cash flow (FCF) represents the cash that a company generates after accounting for cash outflows to support operations and maintain its capital assets.

7) SALES

* NOTE: Excel Data Upload: FactSet data fetched by Virtua is saved and uploaded to the following OneDrive location: Here's the link OneDrive - Virtua Exchange

Access to this drive must be requested from the DBA.

----------------------------------------------------------------------------------------------------------------------------------

2) Flowchart of Factset workflow

1) The data from FACTSET is dumped in the Daily Factset shared drive(The link is shared above) shared through a process which runs at 1 AM.

2) The data is stored in 6 sheets which is explained in above diagram.

3) After the celery process runs, the data is added in the Virua DWH tables and the same is displayed in the Benchmarking.

---------------------------------------------------------------------------------------------------------------------------------

3) Process workflow of Factset

-----------------------------------------------------------------------------------------------------------------------------

4) Queries and steps to check the data in the Snowflake

Useful links:

Snowflake link: https://app.snowflake.com/jua05830/ega44896/w4DcyuAJlLOV#query

Username and Password: https://mw.virtuaresearch.com/mediawiki/index.php/Main_Page/DBA/Database_Environments_and_Access_Details

1) First, check if the data is mapped correctly in the FSCOMPANYMAPPING table. The same needs to be checked in the MYSQL table as well.

Snowflake: SELECT * FROM "DWCONTROLDB"."PUBLIC"."FSCOMPANYMAPPING" WHERE ISIN IN ('ENTER_ISIN');

MySQL: SELECT * FROM VirtuaMaster.Universe where Ticker = 'TICKER_NAME';

* Check if the CompanyID, SecID, and ISIN should be properly mapped.

2) The data present in the source table is divided into Basic and Advance. The output is different for both. This is used for getting the Factset Source information.

Snowflake:

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_BASIC_CONH_AF" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064')// AND CONS_END_DATE is null
ORDER BY CONS_START_DATE DESC;

SELECT i.isin,un.UNITFACTOR,cz.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_BASIC_CONH_QF" cz ON cz.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON cz.fe_item = un.fe_item
WHERE i.isin in ('US5658491064')// AND CONS_END_DATE is null
ORDER BY CONS_START_DATE DESC;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_ADVANCED_CONH_AF" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null
ORDER BY FE_FP_END DESC;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_ADVANCED_CONH_QF" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null
ORDER BY FE_FP_END DESC;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_BASIC_CONH_LT" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null ;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_BASIC_CONH_REC" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null ;

3) Check if data is present in the DWH (dataware house) or not. The parameter that needs to be passed is the DWCompanyID.

Select * from Public.DWESTFINALVALUES_Q where dwcompanyid = DWCompanyID ;

Select * from Public.DWESTIMATEVALUES_Y where dwcompanyid = DWCompanyID ;

StoredProcedure: CALL SPT_GetCompEstPostReleaseDateValues_QandY();

*NOTE: Cons_Start_Date in Source Table is same as Reported date in DWH.

------------------------------------------------------------------------------------------------------------------------------------

*Piece of information* -

If any factset element (EPS_GAAP, EPS_NONGAAP, SALES, FCF, EBIT and EBITDA) is not available in source table, no need to create redmine case. If data is not there in source, please mention analyst that we have received the data from FactSet and will not show in BM.

Basic table contain - EPS and SALES

Advance table contain - EPS_GAAP, EPS_NONGAAP, FCF, EBIT and EBITDA

SELECT i.isin,un.UNITFACTOR,cz.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_BASIC_CONH_QF" cz ON cz.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON cz.fe_item = un.fe_item
WHERE i.isin in ('US5658491064')// AND CONS_END_DATE is null
ORDER BY CONS_START_DATE DESC;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_ADVANCED_CONH_AF" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null
ORDER BY FE_FP_END DESC;

SELECT i.isin,un.UNITFACTOR,d.* FROM "FACTSET"."SYM_V1"."SYM_ISIN" i
JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" c ON i.fsym_id = c.fsym_id
JOIN "FACTSET"."FE_V4"."FE_ADVANCED_CONH_QF" d ON d.fsym_id = c.fsym_regional_id
JOIN "FACTSET"."REF_V2"."FE_ITEM_MAP" un ON d.fe_item = un.fe_item
WHERE i.isin in ('US5658491064') // AND CONS_END_DATE is null
ORDER BY FE_FP_END DESC;

-------------------------------------------------------------------------------------------------------------------------------------

4) Reported Scenarios

1) https://virtuaresearchinc.freshdesk.com/a/tickets/10648

Resolution- The subscription of the Factset was expired, because of which data was not updating correctly.

2) https://virtuaresearchinc.freshdesk.com/a/tickets/10622
Resolution - This is not an issue. It has been observed that this corresponds that mapping of the FCF factset element missing, due to which FCF data is not being seen.

3) https://virtuaresearchinc.freshdesk.com/a/tickets/10103

Resolution - Mapping was not correct because of which correct was not showing correctly

4) https://virtuaresearchinc.freshdesk.com/a/tickets/10112

Resolution - Sometimes DWH table takes time to update the data from the source table and the data could not be seen.

5) https://virtuaresearchinc.freshdesk.com/a/tickets/10918

Resolution - The factset access also needs to be given to peers in order to view the data in BM.

6) https://virtuaresearchinc.freshdesk.com/a/tickets/9962

Resolution - In this case, the factset has not updated the quarter data, and therefore it is not reflecting in the excel file. Also, company's actual data was added and we'll receive data of next 4 quaters.

There could be scenarios where only limited data is showing in the BM. In that case, we need to check if the data is available in source table. If no, the same will be updated in DWH table and will not show in the BM.

7) https://virtuaresearchinc.freshdesk.com/a/tickets/9369

Resolution - In this case, the company's ISIN was updated and therefore, the data was not updating. We need to update the data in Suport page and create a case for DBA to update in the database as well.