Period Offset

Period Offset:

Application Overview:

Offset value will refer to the shift or displacement of a period when the company does not report the data in the standard Period End Dates. Since in the warehouse we have the data saved in the Calendar year(1st Jan – 31st Dec) we need to add the offset settings for that company in the master table for VirtuaMaster.

Company comparing it to the Period End Date reported on Factset. We need to just refer to the Month and Date part for the Annual/Year Period End date, the Year can be anything. 

Step 1: Obtain Year Period End Date The first step is to retrieve the Year Period End Date for the latest Fiscal Year Period for the company. This information is obtained from the Factset Database, specifically from the "FACTSET"."FE_V4"."FE_BASIC_CONH_AF" or "FACTSET"."FE_V4"."FE_ADVANCED_CONH_AF" tables.

We need to just refer the Month and Date part for the Annual/Year Period End date, the Year can be anything. These tables provide the necessary data to understand the company's yearly reporting dates 

Reference Query

Step1:

1.a. We need to get the company ISIN and Company ID from the below query: - a. From VirtuaMaster follow the below query using the Company Name Using Company Name.

Select CM.CompanyID, CM.CoName, UN.ISIN, UN.Ticker From VirtuaMaster.Company CM INNER JOIN VirtuaMaster.Universe UN ON CM.CompanyID = UN.CompanyID WHERE CM.CoName Like '%Add company name here%' AND IsPrimary = 1;

Using ISIN

Select CM.CompanyID, UN.ISIN, UN.Ticker From VirtuaMaster.Company CM INNER JOIN VirtuaMaster.Universe UN ON CM.CompanyID = UN.CompanyID WHERE UN.ISIN = 'Add ISIN here' AND IsPrimary = 1

Using Ticker

Select CM.CompanyID, CM.CoName, UN.ISIN, UN.Ticker From VirtuaMaster.Company CM INNER JOIN VirtuaMaster.Universe UN ON CM.CompanyID = UN.CompanyID WHERE CS.Ticker = ‘Add ticker here’ AND IsPrimary = 1;

1.b. Getting the Period end date from "FACTSET"."FE_V4"."FE_BASIC_CONH_AF" you need to get the Company ISIN and the WareHouse CompanyID,

SELECT MT.COMPANYID, MT.ISIN, SC.PROPER_NAME, MAX(AF.FE_FP_END) AS PERIODENDDATE FROM "FACTSET"."FE_V4"."FE_BASIC_CONH_AF" AF INNER JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" SC ON AF.FSYM_ID = SC.FSYM_REGIONAL_ID INNER JOIN "DWCONTROLDB"."PUBLIC"."FSCOMPANYMAPPING" MT ON SC.FSYM_ID = MT.FSYM_ID WHERE MT.ISIN = ‘Add the ISIN here’ AND MT.COMPANYID = Add the company ID here AND AF.FE_ITEM IN ( 'SALES', 'EPS') GROUP BY MT.COMPANYID, MT.ISIN, SC.PROPER_NAME;

OR

SELECT MT.COMPANYID, MT.ISIN, SC.PROPER_NAME, MAX(AF.FE_FP_END) AS PERIODENDDATE FROM "FACTSET"."FE_V4"."FE_ADVANCED_CONH_AF" AF INNER JOIN "FACTSET"."SYM_V1"."SYM_COVERAGE" SC ON AF.FSYM_ID = SC.FSYM_REGIONAL_ID INNER JOIN "DWCONTROLDB"."PUBLIC"."FSCOMPANYMAPPING" MT ON SC.FSYM_ID = MT.FSYM_ID WHERE MT.ISIN = ' Add the ISIN here’ AND MT.COMPANYID = Add the company ID here AND AF.FE_ITEM IN ( 'EBIT','FCF') GROUP BY MT.COMPANYID, MT.ISIN, SC.PROPER_NAME

Step 2: 

Calculating the Offset The offset value needs to be calculated based on the rules mentioned below: - • Quarter Period Offset (PeriodOffset)

• FY Period Offset(FYPeriodOffset)

Step 3: Updating the Database Tables

These values are to be updated in the below tables: