Transcript SP Index Services Universal File Format (UFF) Overview

Standard and Poor’s Index Services
Universal File Format (UFF)
Overview
July 24, 2008
Legacy Issues
• Data across families have differing data fields.
• Constituent level identifiers are not standardized – multiple
identifiers for same constituent.
• Index level identifiers are not standardized across databases.
• Data retrieval from multiple locations - different indices have
different FTP access points.
• Addition of a new index or index family would require a client to
rewrite any load program as the file format could be different.
2.
File Formats
• Multiple legacy file formats with both overlapping and unique fields.
Database 1
Database 2
Database 3
Index
11
11
5
19
24
14
Level Fields
Constituent
Level Fields
3.
File Formats
• Leveraging the unique characteristics of each file format into one
universal file format (UFF).
New
Database
Database 1
Database 2
Database 3
Universal
File Format
Index
11
11
5
16
19
24
14
35
Level Fields
Constituent
Level Fields
4.
Identifier Mapping – Constituent Level
• Where there were multiple identifiers, there will now be one unique
identifier – Stock Key.
Database
Index
Unique Identifier
Stock Key
1
US Indices, Shariah,
ASX, etc
S&P/Citigroup,
Shariah, etc.
EMDB, IFCI, IFCG &
Frontier
RIC, Bloomberg Ticker,
CUSIP, ISIN and/or SEDOL
SP ID
12345678
Market ID and/or IFC ID
12345678
2
3
5.
12345678
Identifier Mapping – Index Level
• New Database - parsable index code (Index Key)
Refer to EDX location to view EMDB_Mapping Legacy to index key_FIN.xls
Index code
SP = Standard & Poor's
Index family
Index specific
Index currency - 3 letter
Weight schema
Maximum
Characters Notes
2
Branding
4
4
3
ISO code
FF - Free Float
2
UF = Unrestricted Float
Index attribute
2
Index Type
2
3
Region Country
6.
Size
3
GICS
Total
8
33
G = Growth
V = Value
"--" in not needed
P = Price
T = Total Return
N = Net Total Return
1 = Hedged Price
2 = Hedged Total Return
3 = Hedged Net Total Return
R for region + 2 letter region code
or
2 letter ISO code+"-"
or
C for composite…
using a look up table
"---" if not needed
GICS helps create Index Key, null if not needed
Can be reduced to under 30 by concatenating with GICS Code
EMDB Market ID (legacy identifier)
• Historic Market ID was concatenated with file specific column to
make unique ID (file specific).
Column
4
5
6
7
10
11
12
13
17
18
19
20
21
22
23
24
26
27
28
29
30
31
7.
Key
price index in local currency IFCI
price index in US dollar IFCI
Total return index in local currency IFCI
Total return index in US dollar IFCI
price index in local currency IFCG
price index in US dollar IFCG
total return index in local currency IFCG
total return index in US dollar IFCG
net return index in local currency IFCI
net return index in US dollar IFCI
price index in local currency FCI value
price index in US dollar IFCI value
total return index in local currency IFCI value
total return index in US dollar IFCI Value
net return index in local currency IFCI Value
net return index in US dollar IFCI Value
price index in local currency IFCI Growth
price index in US dollar IFCI Growth
total return index in local currency IFCI Growth
total return index in US dollar IFCI Growth
net return index in local currency IFCI Growth
net return index in US dollar IFCI Growth
EMDB Index level identifiers
• Although unique for a file, codes were not unique at a database
level.
INDEX NAME
S&P/IFCI Composite price index in US dollar
S&P/IFCI Composite total return index in US dollar
S&P/IFCG Composite price index in US dollar
S&P/IFCG Composite total return index in US dollar
S&P/IFCI Composite net return index in US dollar
S&P/IFCI Composite value price index in US dollar
S&P/IFCI Composite value total return index in US dollar
S&P/IFCI Composite value net return index in US dollar
S&P/IFCI Composite growth price index in US dollar
S&P/IFCI Composite growth total return index in US dollar
S&P/IFCI Composite growth net return index in US dollar
S&P/IFCI Large-Mid Cap Composite price index in US dollar
S&P/IFCI Large-Mid Cap Composite total return index in US dollar
S&P/IFCI Large-Mid Cap Composite net return index in US dollar
S&P/IFCI Large Cap Composite price index in US dollar
8.
Legacy File Name MARKET ID Column Number
Nyyyymmdd.ASC
100
5
Nyyyymmdd.ASC
100
7
Nyyyymmdd.ASC
100
11
Nyyyymmdd.ASC
100
13
Nyyyymmdd.ASC
100
18
Nyyyymmdd.ASC
100
20
Nyyyymmdd.ASC
100
22
Nyyyymmdd.ASC
100
24
Nyyyymmdd.ASC
100
27
Nyyyymmdd.ASC
100
29
Nyyyymmdd.ASC
100
31
NyyyymmddEL.ASC
100
5
NyyyymmddEL.ASC
100
7
NyyyymmddEL.ASC
100
18
NyyyymmddL.ASC
100
5
Concatenate
1005
1007
10011
10013
10018
10020
10022
10024
10027
10029
10031
1005
1007
10018
1005
Why transition to interim index code?
• New engine is populating Index Key.
• No direct mapping between Market ID and Index Code (1 market
ID to a 3 indices ~ price, TR & NTR).
• Create a concatenated ID, a concatenated (plus) ID and an
interim Index Code.
• Use EMDB_Mapping Legacy to Index Key_FIN.xls file
(Available on EDX site).
INDEX NAME
S&P/IFCI Composite price index in US dollar
S&P/IFCI Composite total return index in US dollar
S&P/IFCG Composite price index in US dollar
S&P/IFCG Composite total return index in US dollar
S&P/IFCI Composite net return index in US dollar
S&P/IFCI Composite value price index in US dollar
S&P/IFCI Composite value total return index in US dollar
S&P/IFCI Composite value net return index in US dollar
S&P/IFCI Composite growth price index in US dollar
S&P/IFCI Composite growth total return index in US dollar
S&P/IFCI Composite growth net return index in US dollar
S&P/IFCI Large-Mid Cap Composite price index in US dollar
S&P/IFCI Large-Mid Cap Composite total return index in US dollar
S&P/IFCI Large-Mid Cap Composite net return index in US dollar
9.
Legacy File Name
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
Nyyyymmdd.ASC
NyyyymmddEL.ASC
NyyyymmddEL.ASC
NyyyymmddEL.ASC
Concatenate
1005
1007
10011
10013
10018
10020
10022
10024
10027
10029
10031
1005
1007
10018
Concatenate Plus
100PUSIFCI
100TUSIFCI
100PUSIFCG
100TUSIFCG
100NUSIFCI
100PUSIFCIV
100TUSIFCIV
100NUSIFCIV
100PUSIFCIG
100TUSIFCIG
100NUSIFCIG
100PUSIFCI
100TUSIFCI
100NUSIFCI
INDEX CODE
SPICMD
SPICMDT
SPGCMD
SPGCMDT
SPICMDN
SPICMVD
SPICMVDT
SPICMVDN
SPICMGD
SPICMGDT
SPICMGDN
SPICMXD
SPICMXDT
SPICMXDN
Migration Timeline
• April 16, 2008 - Client Announcement.
• May 1, 2008 - UFF daily files only using Chained Paasche methodology.
• July 1, 2008 - UFF Parallel files with new Divisor methodology.
• July 31, 2008 - End of parallel run. Last day of legacy file delivery.
• August 1, 2008 - Official UFF file delivery with new Divisor Methodology.
10.
UFF File Reference Table
Daily Index Level File Summary
Current (LEGACY) files
HIFCIX.ASC
HIFCIXL.ASC
HIFCIXEL.ASC
HIFCIXM.ASC
HIFCIXS.ASC
Nyyyymmdd.ASC
NyyyymmddL.ASC
NyyyymmddEL.ASC
NyyyymmddM.ASC
NyyyymmddS.ASC
CR_GICSyy.GIX
CR_GICSyy.IIX
New (UFF) File
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yymmdd_EMDBALL.SPL
yyyymmdd_EMDBALLGICS.SPL
yyyymmdd_EMDBALLGICS.SPL
Index Month End file summary
MIXyy.ASC
MIXyyyyF.ASC
MIVyy.ASC
MIVyyL.ASC
MIVyyEL.ASC
MIVyyM.ASC
MIVyyS.ASC
MKTYY.ASC
MKTyyyyyF.ASC
GICSyy.MIX
GICSyy.MIV
CR_GICSyy.MIX
CR_GICSyy.MIV
11.
yyyymmdd_EMDBALL_EOM.SPL
Began publishing daily as of 7/3/08. yyyymmdd_EMDBFRONTIER.SPL
yyyymmdd_EMDBALL_EOM.SPL
yyyymmdd_EMDBALL_EOM.SPL
yyyymmdd_EMDBALL_EOM.SPL
yyyymmdd_EMDBALL_EOM.SPL
yyyymmdd_EMDBALL_EOM.SPL
discontinued as of 7/31/08--only available in EMDB 2000 post 8/1/08
Began publishing daily as of 7/3/08. yyyymmdd_EMDBFRONTIER.SPL
discontinued
discontinued
discontinued
discontinued
UFF File Reference Table (continued)
Constituent Detail File Summary
Current (LEGACY) files
Dyyyymmdd.ASC
ID_COMP.ASC
Dyyyymmdd.ASC
ID_COMP.ASC
New (UFF) File
yyyymmdd_EMDBGlobal_CLS.SPC and yyyymmdd_EMDBInvestable_CLS.SPC
yyyymmdd_EMDBGlobal_CLS.SPC and yyyymmdd_EMDBInvestable_CLS.SPC
yyyymmdd_EMDBGlobal_ADJ.SPC and yyyymmdd_EMDBInvestable_ADJ.SPC
yyyymmdd_EMDBGlobal_ADJ.SPC and yyyymmdd_EMDBInvestable_ADJ.SPC
ASIAyy.ASC
LATyy.ASC
EMEAyy.ASC
STKyyyyF.ASC
yyyymmdd_EMDBGLOBAL_EOM.SPC and yyyymmdd_EMDBInvestable_EOM.SPC
yyyymmdd_EMDBGLOBAL_EOM.SPC and yyyymmdd_EMDBInvestable_EOM.SPC
yyyymmdd_EMDBGLOBAL_EOM.SPC and yyyymmdd_EMDBInvestable_EOM.SPC
yyyymmdd_EMDBGLOBAL_EOM.SPC and yyyymmdd_EMDBInvestable_EOM.SPC
CCCyyyyW.ASC
Discontinued
ID99F.ASC
yyyymmdd_EMDBFrontier_CLS.SPC and yyyymmdd_EMDBFrontier_ADJ.SPC
Corporate Action File Summary
12.
Current (LEGACY) files
Dyyyymmdd.ASC
ID_COMP.ASC
New (UFF) File
yyyymmdd_EMDBGlobal_.SPA and yyyymmdd_EMDBInvestable.SPA
yyyymmdd_EMDBGlobal_.SPA and yyyymmdd_EMDBInvestable.SPA
CCCyyyyW.ASC
Discontinued
ID99F.ASC
yyyymmdd_EMDBFrontier.SPA
UFF File Reference Table (continued)
• Newly added GCCINVESTABLE files
– *_GCCINVESTABLE.SPA
– *_GCCINVESTABLE.SPC
– *_GCCINVESTABLE_ADJ.SPC
• Why add these files?
– In the legacy UFF files, these countries were included in the
EMDBINVESTABLE, but were not included in the New Intergration
System UFF parallel files.
13.
V3.1 changes to Index Level, Constituent Details, Corporate Actions
• INDEX LEVEL CHANGES
– File suffix is changed from ICL to SPL
– INDEX KEY will be newly generated ID
– Additional column added - INDEX CODE (LEGACY); will provide a definitive mapping document.
• INDEX CONSTITUENT DETAILS CHANGES
– File suffix changed from ICD to SPC
– STOCK KEY will be newly generated ID (formerly ID1)
– Additional column added LEGACY KEY; will provide a definitive mapping document.
– July 1,2008 begins ADJ.SPC
• CORPORATE ACTIONS CHANGES
– File suffix changes from ICA to SPA
– STOCK KEY will be newly generated ID (formerly ID1)
– Additional column added LEGACY KEY; will provide a definitive mapping document.
– Jul 1, 2008 begins SPA forward looking dates + 7 days

14.
e.g. Today’s EOD file (May 14, 2008) would reflect changes effective tonight through EOD May 21, 2008.
Availability of Enterprise Data eXchange (EDX)
• A more robust FTP site is now active to access files.
FTP site: ftp://@edx.standardandpoors.com
– Current technology versus Co-branding
– High Availability
– Load balanced
– Full Disaster Recovery
– Additional secure file transfer protocol
– More user-friendly website for downloading
• Download latest version of Specification file from EDX.
– ftp://Inde6262:[email protected]
• Client usernames and passwords available. Contact Client Services if
you have not received new information.
– [email protected]
(212) 438-2046
15.
Contact name
Company
Department
Address
City, State, mail code (zip code)
Country
Phone number
E-mail address
Questions & Answers
• General
– UFF sample files for all indices (ASX, TSX, S&P/CITI, etc.) are being generated.
– GICS Index code field is in the SPL , SPC and SPA files.
– Effective July 1, 2008, the LOCAL PRICE field will reflect the value of the trade currency.
Pre June 30, 2008, the value represented was in local currency.
• Weekly
– IFC weekly files will be discontinued as of August 1, 2008.
• Monthly
– IFC monthly GICS files will end the first week in August 2008.
– Parallel month end files will start the first week following May 31, 2008 and end the first week
in September 2008.
– A monthly subscriber, would receive: YYYYMMDD_INDEXAlias_EOM.SPC.
The fields appended to EOM.SPC are: market code, stock code, high price, low price, total
value traded in US dollar, number of shares traded, number of days traded, price-earnings
ration (P/E), price-book value ratio (P/BV), profit, book value, and depreciation.
– Sample *_EOM.SPL and *_EOM.SPC files have been provided.
– No month-end adjusted close file will be provided.
16.
Questions & Answers (continued)
• Effective August 1, 2008, the ADR database files will be
discontinued.
• A sample GICS file has been published to the EDX location.
• NDO (Next Day Open) files
– Adjusted market cap, adjusted divisor, and adjusted count, these are
not capabilities of the legacy system. Files created from the new system
will have these fields populated. The NDO file will begin July 1, 2008.
• GV key
– Is the S&P Compustat unique identifier (fundamental data) and is
another identifier similar to RIC, MIC, etc.
17.
Upcoming Events
• Topic: Methodology and Branding
– http://www2.standardandpoors.com/portal/site/sp/en/us/page.topic/indice
s_methodologychanges/2,3,2,2,0,0,0,0,0,0,0,0,0,0,0,0.html
• Important Dates
• October 1, 2008 – Annual Reconstitution for S&P Developed BMI
Markets; Enhancements to the S&P BMI Indices
• November 1, 2008 – Annual Reconstitution for S&P Emerging BMI
Markets; Country restructuring for Emerging and Frontier Markets
• S&P and MSCI Barra announce revisions to the Global Industry
Classification Standard (GICS)
– Constituent files will be announced on July 31 thru GICS Direct. The
information will be published and effective after the close of business on
August 29, 2008.
18.
Thank you
19.