SEDOL Enhancement Project Bulk Uploads Reference Document

SEDOL MASTERFILE™

Bulk Upload Reference Document

Version 3.0

06thAugust 2004

DOCUMENT VERSION CONTROL

Version / Amendment(s) / Date
V1.0 / FINAL VERSION / 6th Dec 2003
V2.0 / AMENDED VERSION – reference to SMF Mandatory Fields for Bulk Uploads documentation / 26th Feb 2004
V3.0 / AMENDED VERSION – updated as result of SEDOL 2 / 06th Aug 2004

TABLE OF CONTENTS

1.Overview

2.Downloading Bulk Upload TAB Template

3.Column Headings

4.Populating Bulk Upload TAB Template

4.1TAB File Specification and Validation Rules

4.2Mandatory Fields

4.3Configurable Upper Limit

5.Uploading Tab File

6.Viewing Bulk Request History & Download Report

6.1System Generated Fields & Download Report

6.2Error Generation

1.Overview

The SEDOL Masterfile™ system will allow users to create large quantities of new security records as a bulk process. This document outlines the steps that an external user must follow in order to upload new issuer and security data using a pre-populated TAB delimited file and uploading this data via the SEDOL Masterfile™ web application.

2.Downloading Bulk Upload TAB Template

Select the ‘Download Template File’ link on the ‘Bulk creation’ panel, on the ‘Bulk upload’ page (as illustrated in Figure 1 below).

Once downloaded, the bulk upload TAB template can be opened in any compatible program, such as, Microsoft Excel.

Figure 1: Bulk Upload - Download reference document

3.Column Headings

The headings below will be present in the first row of the template:

Column Heading / Pos
ActionIndicator / 1
ClientReference / 2
IssuerID / 3
IssuerName / 4
AlternateIssuerName / 5
CountryOfIncorporation / 6
SEDOLCode / 7
SecurityDescription / 8
ShortDescription / 9
ISINCode / 10
ClosingDate / 11
ClosingDateType / 12
SecurityForm / 13
SecurityType / 14
AmntOfTransfer1 / 15
AmntOfTransfer2 / 16
AmntOfTransfer3 / 17
AmntOfTransfer4 / 18
CouponInterestRate / 19
ParValue / 20
ParValueCurrency / 21
UnderlyingIssuer / 22
UnderlyingInstrument / 23
Restrictions / 24
StrikePrice / 25
StrikePriceCurrency / 26
CFICode / 27
CountryOfRegister / 28
PrimaryListing / 29
SecurityStatus / 30
OfficialPlaceOfListing / 31
UnitOfQuotationCurrency / 32
MIC / 33
NeverMakeTradable / 34
ActivationDate / 35

Note: Column headings are mandatory.

4.Populating Bulk Upload TAB Template

Each row in the upload file represents a request to create a new issuer or security record. Different upload request types are determined by Action Indicators.

The following four bulk upload request types, or Action Indicators, are accepted by the system.

Action Indicator / Description of Event
ISR / Creates a new issuer together with a new security for that issuer
SEC / Creates a new security for an existing issuer
COR / Creates a new country of registration for an existing security
OPL / Creates a new OPOL for an existing security

Note: ActionIndicator field is mandatory:

4.1TAB File Specification and Validation Rules

Each request row populated by the user must conform to the specification below:

Field Name / Pos / Format / Description
ActionIndicator / 1 / CHAR[1] (3) / Identifies the action which the customer intends
ClientReference / 2 / CHAR (8) / Optional field identifying this data row
IssuerID / 3 / INT[2] / Unique internal identifier for issuer
IssuerName / 4 / VARCHAR[3] (35) / The issuing party legal entity name which has to be the exact name as held on SEDOL Masterfile
AlternateIssuerName / 5 / VARCHAR (35) / An alternate name for the same legal entity
CountryOfIncorporation / 6 / CHAR (2) / The country in which the issuer is incorporated
SEDOLCode / 7 / CHAR (7) / Security Identification Code
SecurityDescription / 8 / VARCHAR (40) / Unique description of this instrument for this issuer
ShortDescription / 9 / VARCHAR (18) / Abbreviated description
ISINCode / 10 / VARCHAR (12) / International Securities Identification Number
ClosingDate / 11 / DATE[4] / The expiry/maturity date of the instrument (DD-MM-YYYY)
ClosingDateType / 12 / CHAR (3) / The type of Closing date – either EXP or MAT
SecurityForm** / 13 / CHAR (1) / Indicates the form a security takes e.g. P = Bearer
SecurityType / 14 / CHAR (2) / The type of tradable instrument e.g. AA = Ordinary
AmntOfTransfer1 / 15 / DEC[5] (18,8) / The number of units that the share/stock can be traded in (1000000000.00000000)
AmntOfTransfer2 / 16 / DEC (18,8) / As above – second denomination (1000000000.00000000)
AmntOfTransfer3 / 17 / DEC (18,8) / As above – third denomination(1000000000.00000000)
AmntOfTransfer4 / 18 / DEC (18,8) / As above – fourth denomination(1000000000.00000000)
CouponInterestRate / 19 / DEC (18,8) / The interest rate applicable to that instrument when in coupon form (1000000000.00000000)
ParValue / 20 / DEC (18,8) / The nominal value of the stock (1000000000.00000000)
ParValueCurrency / 21 / CHAR (3) / ISO Currency Code in which the Par Value is shown
UnderlyingIssuer / 22 / CHAR (35) / Name of the underlying issuing party
UnderlyingInstrument / 23 / CHAR (18) / Name or identifier of the underlying instrument
Restrictions / 24 / VARCHAR (10) / Code indicating any special regulations surrounding stock trade.
StrikePrice / 25 / DEC (18,8) / Price for which an underlying stock can be purchased or sold (1000000000.00000000)
StrikePriceCurrency / 26 / CHAR (3) / ISO Currency Code in which the Strike Price is shown
CFICode / 27 / VARCHAR (6) / Classification of financial instruments, ISO standard code (mandatory for GB ISINs) used to reflect security characteristics
CountryOfRegister / 28 / CHAR (2) / The country in which the share register is maintained. ISO Country Code
PrimaryListing / 29 / TRUE or FALSE / Denotes whether an OPOL is the primary listing for a security. (TRUE or FALSE)
SecurityStatus / 30 / CHAR(1) / Indicates if the instrument is actually trading (T) or prepared for trading (C)
OfficialPlaceOfListing / 31 / VARCHAR (4) / The ISO code for the market on which the instrument is officially listed.
UnitOfQuotationCurrency / 32 / CHAR (3) / ISO Currency Code in which the Unit of Quotation is shown
MIC / 33 / VARCHAR (4) / The ISO (MIC) Market Identification Code for the place in which the instrument is traded
NeverMakeTradable / 34 / CHAR (1) / Never make Tradable flag - i.e., The instrument will remain in a Coded status– either Y or N
ActivationDate / 35 / DATE[6] / The Activation date of the instrument – i.e., the date is will become Tradable (DD-MM-YYYY)

4.2Mandatory Fields

Further details on mandatory data fields required to create a new instrument are detailed on the Exchange’s website, in the following document:

  • Version 3.0 SMF Mandatory Fields for Bulk Uploads.xls

4.3Configurable Upper Limit

The maximum number of request rows in an upload file permitted by the SEDOL Masterfile™ application, is set according to licence type and business entity. A file will fail to upload if the user has exceeded the maximum number of request rows permitted for their particular licence type or business entity.

5.Uploading Tab File

To upload a pre-populated upload file, click the ‘Browse’ button in the ‘Bulk creation’ panel, and select the desired file from the file location window.

Once selected, click the ‘Upload’ button. One of the following message types will then be displayed in the ‘Bulk Creation’ panel to indicate whether the upload has been successful or unsuccessful:

Example Message / Upload Success / Explanation
File loaded with 0 error(s) /  / All request rows have been submitted. No errors have occurred.
File loaded with n error(s) /  / n request rows have been rejected. Note, more than 21 errors may have found. For further details, user may view download report.
Column headers in the upload file were incorrect. No data has been processed. /  / Column headers were incorrectly populated. Check column headings against Bulk Upload TAB Template and refer to section 1.3 of this document for assistance.
Column headers in the upload file were missing. No data has been processed. /  / Column headers were missing. Check column headings against Bulk Upload TAB Template and refer to section 1.3 of this document for assistance.
The number of columns in the upload file were incorrect. No data has been processed. /  / Columns were in the wrong order, or were missing. Check columns against Bulk Upload TAB Template and refer to section 1.3 of this document for assistance.
The number of requests for your business entity has been exceeded. /  / User has exceeded the maximum number of request rows permitted by the SEDOL Masterfile™ application, for their particular business entity.
The number of requests for your licence type has been exceeded. /  / User has exceeded the maximum number of request rows permitted by the SEDOL Masterfile™ application, for their particular licence type.
Invalid File Type. No data has been processed. /  / Bulk upload function only accepts Text (Tab delimited) files. Refer to Bulk Upload TAB Template and section 1.3 of this document for assistance.
File was empty. No data has been processed. /  / Empty files are not accepted.

6.Viewing Bulk Request History & Download Report

A history of uploaded files is viewable in the ‘Bulk request history’ panel on the ‘Bulk upload’ page for any submissions by the user within the previous month. For each uploaded file, the ‘Bulk request history’ panel displays the following information: User, Start Date and Time, Completion Time, Submitted Rows and Rejections.

To view a Downloadreport for a previous uploaded file, select the desired previous uploaded file link in the ‘Bulk request history’ panel, and click the ‘Download Report’ button.

6.1System Generated Fields & Download Report

The same file will be returned by the system to the requestor in a download report, which is accessible via a link on the SEDOL Masterfile™ Web application. The returned file will display the original data along with the system-generated fields. The SEDOL Code will be populated on successfully executed records.

Please note that information entered by the client within the system-generated fields will be overwritten.

The download report that is returned to the requestor is as follows:

Field Name / Format / Pos / Value Returned
ActionIndicator / CHAR (3) / 1 / As entered by user
ClientReference / CHAR (8) / 2 / As entered by user
IssuerID / INT / 3 / System generated where ISR is Action Indicator
IssuerName / VARCHAR (35) / 4 / As entered by user
AlternateIssuerName / VARCHAR (35) / 5 / As entered by user
CountryOfIncorporation / CHAR (2) / 6 / As entered by user
SEDOLCode / CHAR (7) / 7 / System generated
SecurityDescription / VARCHAR (40) / 8 / System generated except for the following securities from the Security Group ‘Other’:
  • Mutual Funds
  • Futures
  • Swaps
  • Indices
  • Bill of Exchange
  • Currency
  • Interest Rate
  • Generic

ShortDescription / VARCHAR (18) / 9 / System generated
ISINCode / VARCHAR (12) / 10 / As entered by user
ClosingDate / DATE / 11 / As entered by user
ClosingDateType / CHAR (3) / 12 / As entered by user
SecurityForm / CHAR (1) / 13 / As entered by user
SecurityType / CHAR (2) / 14 / As entered by user
AmntOfTransfer1 / DEC (18,8) / 15 / As entered by user
AmntOfTransfer2 / DEC (18,8) / 16 / As entered by user
AmntOfTransfer3 / DEC (18,8) / 17 / As entered by user
AmntOfTransfer4 / DEC (18,8) / 18 / As entered by user
CouponInterestRate / DEC (18,8) / 19 / As entered by user
ParValue / DEC (18,8) / 20 / As entered by user
ParValueCurrency / CHAR (3) / 21 / As entered by user
UnderlyingIssuer / CHAR (35) / 22 / As entered by user
UnderlyingInstrument / CHAR (18) / 23 / As entered by user
Restrictions / VARCHAR (10) / 24 / As entered by user
StrikePrice / DEC (18,8) / 25 / As entered by user
StrikePriceCurrency / CHAR (3) / 26 / As entered by user
CFICode / VARCHAR (6) / 27 / As entered by user
CountryOfRegister / CHAR (2) / 28 / As entered by user
PrimaryListing / TRUE or FALSE / 29 / As entered by user
SecurityStatus / CHAR(1) / 30 / As entered by user
OfficialPlaceOfListing / VARCHAR (4) / 31 / As entered by user
UnitOfQuotationCurrency / CHAR (3) / 32 / As entered by user
MIC / VARCHAR (4) / 33 / As entered by user
NeverMakeTradable / CHAR (1) / 34 / As entered by user
ActivationDate / DATE / 35 / As entered by user
ErrorCode / N/A / 36 / System generated
ErrorDescription / N/A / 37 / System generated

6.2Error Generation

The Error Code and Error Description fields will be returned in the download report described in Section 11.2.6. The following table illustrates the errors that will be returned where a bulk upload has not been successful.

Note: Technical errors will be reported to the user on screen using standard website error management.

Error Code / Error Description
CFICodeMandatory / “CFI Code is mandatory for GB ISINs.”
DuplicateFieldValue / "The field '{0}' should be unique but a duplicate was found."
IncorrectFieldValueFormat / "The field '{0}' had an incorrectly formatted value."
IsuerNameNotAllowedWithIssuerID / "Cannot provide both IssuerName and IssuerID."
IssuerNotFound / "Issuer was not found."
MandatoryFieldsMissing / "The following mandatory fields were missing: {0}"
MandatoryFieldsMissingForSecurityType / "The following mandatory fields for security type '{0}' were missing: {1}."
MarketDataRequiredForOPOL / "Market data is required for securities with the given OPOL."
NotAllowedFieldsPopulatedForSecurityType / "Not allowed to submit '{1}' for this security type: {0}."
ParameterNotProvided / "Parameter {0} was not provided but is required."
PermissionDenied / "The user does not have the permissions to perform the operation."
RequestExceedsTimeLimit / "The request for changes data exceeds the time limit."
RowFormatInvalid / "The input row was not formatted correctly."
SecurityDescriptionDuplicateError / "The security description was not unique for the issuer."
SEDOLNotFound / "SEDOL code was not found."
TechnicalError / "A technical error was encountered. Please contact customer support."
UnknownFieldValue / "The field '{0}' had a value that was not recognised."
InvalidUserDates / "The date format is invalid."
CountryOfOPOLNotUnique / Country of OPOL is not unique for the country of register for this security.
OwnedOPOLNotAllowedForUser / "The user is not allowed to create securities with an OPOL that is hosted on the LSE trading database."
MICDuplicateError / "MIC was not unique for the country of register for this security."
InactiveRecord / "Cannot perform operation on an inactive record."
ISINNotAllowedForUserRole / "Cannot submit the ISIN code given with these security permissions."
InvalidCountryForSecForm / "Invalid country of register for security form."
CountryOfRegisterNotUnique / "Country of register not unique for this security."
NoLSEMarketCreationPermission / "The user is not allowed to create securities with a MIC that is hosted on the LSE trading database."
CannotCreateDefunctSec / "The user cannot create a defunct security."
CannotCreateReservedSec / "The user cannot create a reserved security."
CannotCreateTradedSec / "The user cannot create a traded security."
PrimaryListingNotAllowed / "A primary listed security already exists for the country of register for this security."
IssuerNameAltIssuerSame / "The issuer name and alternate issuer name cannot be the same."
NotAllowedToPassSEDOLCode / "Not allowed to submit a SEDOL code."
ActivationDateNotAllowed / “Activation Date must be blank where Never make Tradable = Y and Security Status = C”
ActivationDateRequired / “Activation Date is a required field where Never make Tradable = N and Security Status = C”
ActivationDateInvalidFormat / “Activation Date must be entered as DD-MM-YYYY”
NeverMakeTradableRequired / “Never make Tradable is a required field where Security Status = C”
ActivationDateInvalid / “Activation Date must be greater than today and less than one year from tomorrow”
ActivationDateMustbeNull / “Activation Date must be null where Security Status is T”
NeverMakeTradableMustbeNull / “Never make Tradable must be null where Security Status is T”

Bulk Uploads Reference Document.docPage 1 of 13 08/11/2018

[1]CHAR (X) - The length of a CHAR column is fixed to a specified length (X). When CHAR values are stored, they are right-padded with spaces to the specified length (X). When CHAR values are retrieved, trailing spaces are removed.

[2]INT – Integer. The maximum value that can be held by an integer is (2^32)/2 = 2147483647 (the divide by 2 is because it can be positive or negative and the -1 is because 0 is one of the possible values). This give 10 digits plus another one for the sign (e.g. -2147483647) which makes 11 characters.

[3]VARCHAR (X) - Values in VARCHAR columns are variable-length strings. In contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored.

[4] DATE - The date format which is accepted within a Bulk Upload template is DD-MM-YYYY

** See SMF Mandatory Fields for Bulk Uploads V5.0.xls for details on Security Form and Country of Register rules

[5]DEC (18,8) – Maximum 8 decimal places with 10 preceding digits, i.e., 1000000000.00000000

[6] DATE - The date format which is accepted within a Bulk Upload template is DD-MM-YYYY

** See SMF Mandatory Fields for Bulk Uploads V5.0.xls for details on Security Form and Country of Register rules