XREF Data

XREF Data is symbology cross-reference data working at the quote rather than company or security level.

XREF data is derived from the Henry database in Datastream and delivered via GlobalTopic. The XREF system uses a local database (Access or SQL Server) to store information it gathers from GlobalTopic. It then presents a source which can be used to query the database.

Response Fields

All XREF queries return either a list of instruments that can  be requested in turn (a chain) or a field list for a specific instrument.

When returning chains, XREF returns the following fields:

Field Description
REF_COUNT The number of items in this chain
LINK_1 The 1st item in this chain
LINK_2 The 2nd item in this chain
LINK_n The nth item in this chain up to a maximum of REF_COUNT items
NEXT_LR The request to make to retrieve the next chain in the sequence. Empty indicates current chain is the last in the sequence

When returning data on a specific item, XREF returns the following fields:

Field Field in Database Description Example
SYMBOL Symbol The GlobalTopic instrument name. This is XREF's primary key ICI.L
CNTRY_CODE CountryCode The country identifier where the company is primarily registered UK
CO_ID CompanyID The GlobalTopic instrument name excluding the exchange identifier ICI
COMPANY Company The GlobalTopic identifier for company level data 192712.COM
CUG CUG The GlobalTopic Closed User Group value 475
CURRENCY CCY The currency in which the instrument is quoted GBX
DOMICILE Domicile If the instrument is the domicile quote then "D" else empty D
EVENT_DATE EventDate The date if any EVENT_TYPE is specified 20020225
EVENT_TYPE EventType Any recent or pending corporate actions on the instrument STOCK SPLIT
EXCH_CODE Suffix The exchange identifier L
IBESTicker IBESTicker The IBES ticker for this instrument @ICI
IND_CLS_MNE SectorMnemonic The industrial classification mnemonic CHMSP
IND_GROUP IndustryGroup The Datastream industry group code 033
IND_SECTOR IndustrySector Textual description of the Datastream industry group code CHEMICALS, SPECIALITY
ISIN ISIN The ISIN code GB0004594973
ISTicker ISTicker Flag (Y or empty) indicating if ticker (US) based instrument name
LAST_UPD_TIME LastModified The date and time this instrument was last updated in the database 2002/01/24 04:23:55
LCODEIND LCodeInd The type (CUSIP, SEDOL, etc) of code expressed in the LOCAL_CODE field TIDM
LOCAL_CODE LocalName The code or mnemonic used to identify the instrument on its source exchange ICI
NAME Name The name of the instrument IMPERIAL CHEMICAL INDUSTRIES PLC ORD #1
REQ_CODE DSCode The Datastream request code for the instrument 900455
SECURITY Security The GlobalTopic identifier for security level data 192712#5.SEC
SEDOL SEDOL The Stock Exchange Daily Official List code 0459497
TYPE Type The Type (Stock, Index, Bond, etc) of the instrument Stock
IBES Derived The request to make of the (Piranha catalogue based) IBES source ISIN=GB0004594973 SEDOL=0459497
WORLDSCOPE Derived The request to make of the (Piranha catalogue based) Worldscope source ISIN=GB0004594973 SEDOL=0459497

 

Request Syntax

XREF supports both simple and complex queries from the database. The Client.exe application provided with the installation of the platform is the simplest method of querying the source.

GlobalTopic Symbol Based Requests

The simplest query is for a GlobalTopic symbol (e.g. ICI.L). The returned field set describes each of the field entries within the database for the given instrument as defined above.

ISIN Based Requests

XREF also supports request using ISINs. When an ISIN is received, XREF queries the database and extracts only the domicile instrument that has a matching ISIN. However, if the request has an exchange identifier appended, XREF returns the instrument that has the matching ISIN on the specified exchange. For example:

	 GB0004594973 returns the same results as for ICI.L
	 GB0004594973.P returns the same results as for II.P

? Requests

If the incoming query begins with a '?' then XREF produces a chain of instruments that match the given criteria. This is taken to be the WHERE clause of a SQL statement. For example:

	?Suffix='AM' and SectorMnemonic='BANKS'

finds all banks listed on Amsterdam. When supplying the WHERE clause, use the field names as defined in the database. See the 'Fields in Database' column in the table above.

Although the ? request returns a simple chain of instruments, a more advanced response can be achieved using the COLUMNS syntax supplied using the request's Options property. See the advanced query section for more details.

Preceding the ? request with COUNT returns a single field COUNT that specifies the total number of entries in the database that match the specified query.

	COUNT ?Suffix='AM' and SectorMnemonic='BANKS'

* Requests

If the incoming query begins with a '*' then XREF returns the content for the first instrument that matches the given search criteria. This is taken to be the WHERE clause of a SQL statement. For example:

	*Suffix='AM' and SectorMnemonic='BANKS'

finds the first bank listed on the Amsterdam exchange. This request format should only be used where the search criteria is sufficiently well defined to isolate one instrument.

! Requests

If the incoming query begins with a '!' then XREF returns a chain of all instruments that have had corporate actions reported within a specified number of days on a particular exchange. For example:

	!L/5

returns all the intruments on the London exchange that have had corporate actions (EVENT_DATE and EVENT_TYPE fields) notified within the last 5 days.

EXCH_LIST Requests

Requesting EXCH_LIST returns a chain containing all the exchanges that have had data ingested into the database. Each of the returned links is of the form EXCH/yy where yy is the exchange identifier. Directly requesting the content of each of the links allows you to iterate through the list of instruments in each exchange. See "EXCH/ Requests"

LIST Requests

Requesting LIST returns a chain containing all the instruments in the database. This command can be further defined by adding a datestamp. For example:

	LIST 2002/01/23 12:00:06

returns a chain listing every instrument within the database that was last updated (LastModified field) after 12:00:06 on 23 January 2002.

Although the LIST request returns a simple chain of instruments, a more advanced response can be achieved using the COLUMNS syntax supplied using the request's Options property. See the advanced query section for more details.

EXCH/ Requests

Requesting EXCH/yy, where yy is an exchange identifier, is identical to the LIST command but limits the search to a particular exchange. For example:

	EXCH/L 2002/01/23 12:00:06

returns a chain listing every instrument on the London exchange that was last updated after 12:00:06 on 23 January 2002.

As for the LIST request, using the COLUMNS syntax supplied using the request's Options property can provide a more advanced response. See the advanced query section for more details.

_FIELDS Request

Requesting _FIELDS simply returns a list of the fields that are returned when requesting an individual instrument. The value of each field will be the corresponding name of the field within the database. Basically, the response lists the contents of the first two fields in the previous table.

ACTIONS Request

The XREF module iterates through each listed exchange retrieving data for each member instrument. When XREF detects new, modified or deleted entries it updates the database. XREF supports an ACTIONS instrument that logs any changes to the database. This instrument can be requested and monitored for any updates. The ACTIONS request returns the same field set as the simple instrument query with the addition of an ACTION field that has a value of either SYMBOL_ADD, SYMBOL_DELETE or SYMBOL_CHANGE for adds, deletes or modifies, respectively. Prior to any database updates, the ACTIONS request will return a single empty field called ACTION.

The ACTIONS instrument should be used in conjunction with the LIST request. The ACTIONS instrument can be used to monitor changes on the fly. The LIST request, when supplied with a timestamp, can be used to recover any modifications that occurred during periods when connection to the XREF source was lost. Note, the LIST request only returns information on currently active stocks. In order to retrieve a list of instruments that were delisted during network outages, you need to use the DELETES option as described in the advanced queries section.

The ACTIONS instrument throttles updates at a maximum rate of 2 times per second.

 

Advanced Queries

DELETES Option

XREF maintains two tables. The primary table maintains the list of all active stocks. All the above requests operate on the primary table. A secondary table maintains a list of stocks that have recently been delisted. By default, delisted stocks are retained for 7 days. Queries can be made against this secondary table by prepending DELETES to all the above queries (bar _FIELDS and ACTIONS requests). For example:

	DELETES ICI.L
	DELETES ?Suffix=�AM� and SectorMnemonic=�BANKS�
	DELETES *Suffix=�AM� and SectorMnemonic=�BANKS�
	DELETES !L/5
	DELETES EXCH_LIST
	DELETES LIST 2002/01/23 12:00:06
	DELETES EXCH/L 2002/01/23 12:00:06

		

LINKSIZE Option

By default, chains returned by XREF contain 14 links. This can be extended by supplying a preferred chain size when making a request. To do this set the RTRecord's Options property to have a value "LINKSIZE=n" where n is the preferred size of the chain. The maximum returned chain size is 5000 links.

 

COLUMNS Option

When doing a complex ?, LIST or EXCH/ request, XREF returns a chain of instruments that match the criteria. Typically, you then need to request each instrument in order to get the individual data content from XREF. From version 2.2.4 Build 160 onwards, these two steps can be combined by supplying a list of desired fields in the RTRecord's Options property. For example, setting the Options property to be COLUMNS=ISIN,SEDOL,NAME would return the following field list:

	REF_COUNT = number of individual instruments returned (LINK_1 to LINK_n)
	LINK_1    = ICI.L
	ISIN_1    = GB0004594973
	SEDOL_1   = 0459497
	NAME_1    = IMPERIAL CHEMICAL INDUSTRIES PLC ORD #1
	LINK_2    = MSFT.NQ
	ISIN_2    = US5949181045
	SEDOL_2   = 2588173
	NAME_2    = MICROSOFT CORP
	....
	NEXT_LR = xxx 

The field names supplied in the Options property should be the same as the fields returned by XREF for individual queries. i.e. use the field names defined in the 1st column of the table above. Setting the Options property to COLUMNS=* will return all the fields. NB, the WORLDSCOPE and IBES fields, being calculated fields, cannot currently be requested using this format. Also, the request fails if any unknown field names are supplied.

When combined with the LINKSIZE option, the maximum number of returned fields is limited to 5000.