Tick History / Futures / Instrument Codes

The sheer vastness of Thomson Reuters Tick History means that finding a specific asset can be a very daunting experience, particularly for our academic members who lack familiarity with financial market conventions. This problem is sometimes compounded by poor quality descriptions of what the underlying asset actually represents. These descriptions sometimes struggle to convey what the actual instrument is due to field length limitations.

The main source of query we get in this regard is for Futures contracts. Consider for example the SFE 30 Day Interbank Cash Rate futures contract, known locally as “IB”. The contract which expires in January 2011 carries the following long hand description on the Tick History service: “30DAY INTER JAN1”, and has the Reuters Instrument Code: YIBF1.

Imagine the problem that this gives the novice user who is using the Tick History Search function to try and isolate this contract.

Here are a few handy hints. For futures contracts it is usually best to use the Tick History SpeedGuide, available on the product’s menu bar. Type in the word “futures” into the SpeedGuide and you will be presented with a well structured menu to help guide you. This will probably enable you to rapidly find the contract which you are interested in.

It is useful to ascertain the stem of the futures contract and to them ask the Tick History product to tell you all the associated contracts with this stem over the time period which you specify. Stick with the above example, the stem of the SFE 30 Day Interbank Cash Rate contract is YIB. If you enter 0#YIB: as a search request in Tick History, the database will list each individual valid futures contract associated with the stem. The “0#” before the stem is the instruction to expose a “chain” of underlying instruments associated with a master contract or instrument. The colon “:” relates to the futures asset class.

If we look at the individual contracts novice users often scratch their heads about the strange letters which appear in the instrument code. Using the example above for the Jan 2011 SFE 30 Day Interbank Cash Rate contract, the code is YIBF1…..”YIB” being the stem, “1” being the year, as in 2011, but what is the “F”.

I guess this is where a bit of market knowledge helps. The futures market convention is to use letters to designate the delivery month for a futures contract. The letter to month mappings are as follows:

January: F
February: G
March: H
April: J
May: K
June: M
July: N
August: Q
September: U
October: V
November: X
December: Z

YIB = contract stem for 30 Day Interbank Cash Rate
F = January
1 = 2011

European weather data on Thomson Reuters Tick History (academic)

Thomson Reuters Tick History does a quite amazing job recording all types of changes, obviously including what is usually described as tick data, which occur on the Thomson Reuters real time network. In addition to being a dynamic tool, it provides a treasure trove of archival data going back to January 1996.

Sirca’s role has been in helping with the normalisation process of this data, and undertaking the challenging design task of a user interface which gives Tick History users the opportunity to easily retrieve archival tick data covering all asset classes, across all geographies.

This week we received a request from an academic in Adelaide who is looking into the impact of temperature changes on electricity prices in Europe over the past decade. The assumption was that historical temperature data would need to be sourced separately from the electricity spot and futures data which can be sourced from Thomson Reuters Tick History.

Since May 2000, Thomson Reuters provided access to weather data from the UK Met Office, this offers daily observed data at 12h00 GMT, and daily forecast data at 06h00 and 11h00 GMT, for 113 cities across Europe. Thomson Reuters Tick History also records this data, meaning that the database could become a sole source of data for both Weather data and the underlying Electricity price data for our academic customer in Adelaide.

Access to further information about this is via the “SpeedGuide” feature, available from the Tick History menu bar. Type in “EU/WEATHER”, this will give access to a comprehensive listing of definitions and data structures for this series.

As an example, use instrument code “0#DK-OBSERVED”, this is a “chain” of temperatures covering many observation points across Denmark.

Given that this is weather data rather than the more frequently requested asset pricing data, there are a few tricks to be aware of in order to understand the data fields which will be returned for this code. If you go the fields tab, clear out all the preselected fields which are more relevant for financial instruments. In the right hand box headed “Available Fields” click on “Raw Format” and then click on the “Add” button to move these data fields across to “Selected Fields”.

Execute the search request for the weather data, this will return quite a lot of rather scary looking raw data, the good news though is that the raw data will be identified with a FID description. The key to the FID description is again available via the Speedguide, specifically on Speedguide page “EU/WEATHER5”, see below. If you are looking for observed temperature data, look for the data fields identified with “GN_TXT20_2” and “GN_TXT20_4” for max and min observed temperatures for that date.

This is obviously quite a complex way of retrieving weather data but it is presented in a manner which is consistent with other forms of financial markets pricing data retrieved from the database, potentially therefore assisting with the research goal.

Detailed below is a detailed explanation of the European Weather Service Data.  
Database field usage is as follows –                                            
1666 GN_TXT20_2       Observed Max Temp        Max Temp               
1668 GN_TXT20_4       Observed Min Temp        Min Temp               
1670 GN_TXT20_6       Observed Rainfall        Rainfall               
1672 GN_TXT20_8       Observed Wind Speed      Wind Speed             
1673 GN_TXT20_9       Observed Wind Direction Wind dir               
1674 GN_TXT20_10      Observed Pressure        Pressure               
1676 GN_TXT20_12      Forecast Temperature     Temperature            
1678 GN_TXT20_14      Forecast Rainfall        Rainfall               
1680 GN_TXT20_16      Forecast Wind Speed      Wind Speed             
1681 GN_TXT20_17      Forecast Wind Direction Wind Direction         
1682 GN_TXT20_18      Forecast Pressure        Pressure

Access to ASX CHESS for Sirca members

Sirca member universities can access the ASX’s Clearing House Electronic Sub-register System, better known as CHESS, to support eligible research projects. Since 1995, CHESS has been performing two core functions for the ASX: (i) it facilitates the settlement and clearing of trades in shares, and (ii) it provides an electronic sub-register for shares in ASX listed companies.

Sirca is able to facilitate access to CHESS, for reasonable research requests, from qualified academics, within member universities. The first step is to contact Sirca in order to begin the process of describing the research goals and how these can be achieved via access to the CHESS database.

Members will appreciate that CHESS contains sensitive data where the privacy of market participants needs to be protected; any proposed access to the CHESS database must be designed therefore in such a way that the privacy of market participants is maintained, in line with ASX standards and Australian legislation. Both Sirca and ASX staff and processes will be vigilant in ensuring that the data extraction goals from CHESS are designed with this in mind.

Given this, we have agreed on a process with the ASX whereby any data extracted from CHESS is subjected to the following tests before it can be released to the researcher:

(i) “Threshold Test”

The “Threshold Test” checks that there are a minimum of 10 observations within each point for each data category. For example, if a researcher is seeking to identify the number of Super Funds who are invested in a particular stock, we will only be able to release data in the event that at least ten Super Funds are present on the sub-register for that company.

(ii) “Dominance Test”

The “Dominance Test” checks that no single observation accounts for more than 50% of the value for each data point for each data category; that no two observations combined account for more than 75% of the value of each data point for each data category; and that no three observations combined account for more than 90% of the value of each data point for a data category.

(iii) “Activity Test”

Where time series data is provided, the “Activity Test” checks that no single observation accounts for more than 50% of the absolute value of aggregate changes for the data category compared to the previous period, and that no two observations combined account for more than 75% of the absolute value of aggregate changes for the data category compared to the previous period.
Below are the categories for which holdings data can be extracted. Please also note that these are further subdivided into domestic vs foreign branches making 18 categories in total.

1. Bank.
2. Other Deposit Taking.
3. Nominees.
4. Insurance.
5. Super Funds.
6. Trusts.
7. Government.
8. Inc. Companies.
9. Individuals.
It is also worth noting the following frequently asked questions about CHESS data:

(i) Why don’t the categories sum to 100%?

Since CHESS only accounts for shares being registered on the CHESS sub-register, if a shareholder decides not to register his/her shares with the CHESS sub-register of a company after its IPO for whatever reasons, then a portion of the shares issued won’t be registered on CHESS and as a result the sum of categories may not account for 100% of shares on issue.

(ii) Why does the sum of the two categories, individual, and institutional, on some occasions exceed 100%?

A cross-listed (dual-listed) company will have shares issued in Australia as well as in at least one overseas jurisdiction. In the event that this company’s foreign issued shares are traded in Australia, they may be registered in CHESS, in addition to the domestically issued shares. In this case, working out the ownership percentage using the Australian issued shares as a denominator, may result in a percentage greater than 100% given that we wouldn’t have accounted for the foreign issued shares in arriving at the denominator value.

(iii)  Why can’t I get a complete breakdown of ownership holdings for a company from CHESS?

This source will only show ownership holdings for individuals/entities who trade. This means the holdings of entities that have not traded over the history of the CHESS data set, are not known.  A complete breakdown of ownership holdings is therefore not usually possible for larger companies. CHESS records for Woolworths, for example, would not record a key shareholder’s holdings if this entity had never traded them since the inception of CHESS.  Of course, as soon as a trade does occur, the holdings in that trading entity would be reported.  Holdings that this entity may control in other entities would remain unknown until some of these were also traded.

Fractions to Decimals / US Futures

Recently we have been discussing Thomson Reuters Tick History (academic) coverage of the CME with a US based academic. A reference for him was the daily pricing lists made openly available via the CME’s website. In comparing this with the CME coverage on Tick History one thing springs out, specifically the way Tick History uses pure decimals and the CME website bases its pricing off the US fractions conventions of 1/32 and 1/64’s, meaning that the prices looked different between the two sources.

For example, a quote associated with the Globex 10 Year US Treasury Note Future, expiry Dec 2010, may show a price like 126.255 on the CME website. The same quote on Tick History will show as 126.796875.

The “.255” part of the 126.255, stands for 25.5/32’s or more commonly 51/64’s. 51 divided by 64 equals .796875.

Personally… I prefer decimals!

Australian Company Announcements / IPOs

The Australian Securities Exchange has supported Sirca since its inception, and has kindly made a broad array of its datasets available to us in order to help our mission to support academic research into the financial markets. These datasets include the feed of company announcements resulting from the ASX’s listing rules in the context of the continuous disclosure regime. As a result of this Sirca has developed an online database of ASX listed company announcements with an archive going back to 1992. This is available as an additional on-line resource called “Australian Company Announcements” for Sirca academic members and subscribers.

Sirca has developed software which exposes all available reference data for each disclosure, along with all the free text terms which appear in the bodies of the disclosures. Whilst the database is not as manually indexed as many of the offerings available from commercial providers it is nevertheless a very impressive resource in terms of enabling access to raw data.

As an example of how the database can be used, we were recently asked whether we could help with some research which was attempting to nail down the definitive listing of gold sector IPOs on the ASX for the last 5 years, along with a statement about the amount of capital raised.

Each company seeking access to the ASX needs to complete/submit a range of documentation and have these filed through the ASX ComNews service, these include prospectuses and information memoranda, along with standard ASX documentation. The ASX also issues official documents which indicate that an entity has been admitted to their official list. Using the way the ASX categorise these documents, combined with Sirca’s process of exposing the text of the disclosures it was possible to narrow down the overall list of IPOs to just those pertaining to the Gold sector. Furthermore the researcher was able to access the actual disclosures to cross check the amount of capital raised.

Global News / Reuters / downloading

Thomson Reuters has supported Sirca’s mission to support academic research into the financial markets for many years. The most recent extension of this support has meant that Sirca can provide academic members and subscribers with on-line access to a deep archive of Reuters news stories. This is of particular interest to academics who may be looking into the impact of breaking news stories on underlying asset prices.

Sirca’s global news archive provides access to Reuters news stories going back to 2003, complete with Reuters editorial tags, Thomson Reuters product meta data, millisecond time stamps, along with the headline and full news story.

This database is being used quite widely now amongst relevant Australia/NZ based academics. We are finding that some users are having problems downloading the full text of the news stories, these obviously being larger than the data associated with the other downloadable fields such as timestamp, headline, editorial tags etc. These problems manifest themselves as text overflowing into adjoining database cells, or text not appearing at all.

For basic downloads into Excel, follow these steps:

Download the required data in .CSV format. Open this file in Excel.

Make sure that the column containing the full text of the story is formatted as text, ie. highlight the columns headed “ACCUMULATED_STORY_TEXT” and “TAKE_TEXT”, (For Excel 2010, press and <1> and click on the “Number” tab, then highlight “Text” and press OK).

You will then need to make sure that the “Wrap text” box is checked under “Alignment” (For Excel 2010, press and <1> and click on the “Alignment” tab, then highlight the “Wrap text” box under “Text control”).

The full body of the news story should now obediently be contained within one cell, you can obviously check this by highlighting the cell.

The Global News database help file is a good resource. You can access this by clicking the “?” on the top right of the Global News user interface.

Tick History / Futures / Continuation series

We were recently asked to help an academic user in New Zealand with the basics behind continuation RICs for Futures contracts. The manual specifies as follows:

“All commodities have a ‘contract life’, with defined start and end dates. As deliveries in the commodity market are made, the associated contracts expire. Around the same time a new contract for delivery at a future date is issued by the commodity exchange. This, too, will expire on delivery. Continuation series were created to simulate the life of contracts as though they never expire. 
The “n-position continuation” (where n=1-9), will follow the life of contract until expiry. The 1-position continuation follows the nearby contract, the one for the earliest delivery. When it expires, the next contract for delivery, previously tracked by the 2-position contract becomes the 1-position contract. The 3-position continuation tracks contracts three time periods into the future and so on, up to the 9-position continuation.

An n-position continuation has a switch date, this being the last trading day of the life of contract which the 1-position continuation is tracking.

The “n-position continuation active” (where n=1-4) is similar to the n-position contract but tracks only the actively traded trading months. For example, New York silver on the COMEX has a high trading volume in the delivery months 1, 2, 4, 6, 8, 10, 11, and a relatively negligible volume in the months 3, 5, 7, 9 and 12. The n-position continuation active ignores these thinly traded contracts.

The n-position continuation active has a switch date — the last business day of the  month before the delivery month of the life of contract 1-position continuation active series is tracking.”
Our friend in NZ wanted some specific examples from Hong Kong (Hang Seng Index future), Singapore (Straits Times Index future) and Malaysia (KL Composite Index future). Step 1 is to use the contract prefix (ie. HSI for Hang Seng, SST for Straits Times and KLI for KL Composite, and to add “c1” for the 1st continuation RIC, as follows:
HSIc1 for the Hang Seng index futures contract, first continuation series
SSTc1 for the Straits Times index futures contract, first continuation series
KLIc1 for the KL composite index futures contract, first continuation series
There is an online resource to help you learn more about this, go to the Tick History “SpeedGuide” on the menu bar and enter “RULES1”, and follow the menu prompts.

Tick History / Futures / Tokyo Grain Exchange data

The Tokyo Grain Exchange has a long and wonderful history and serves an important function in the Japanese agricultural commodities futures segment. It also highlights some of the problems academic users of Tick History users face in retrieving Futures data.

If we consider the TGE soybeans contract for 27th September 2010…….

The Reuters Instrument Code (RIC) prefix for this contract is JAS. If you set up a “request”, “new” in Tick History and enter the following code: 0#JAS: , you will get the full “chain” of codes referencing the different expiry months for the contracts, eg. JASV0 for Oct10 expiry, as well as some TGE specific codes which reference the various daily auction sessions, eg. JAS11 for the 1st morning session.

As a first step towards retrieving data delete the RICs relating to the morning and afternoon sessions, meaning that you will be left just with the following RICs in the new request set up window: JASV0, JASZ0, JASG1, JASJ1, JASM1, JASQ1

Then click on the “Fields” tab in order to select the appropriate data fields pertaining to these instruments. In the left hand most column select “Price” and “Volume” from under the Transactions, Trades headings. Then select “Settlement Price” and “Open Interest” from under the Transactions heading. Adjust the date (bottom left of screen) to 27-Sep-10.

Given that this request is not too large from a data volume perspective, all you need to do to retrieve the data is to hit the “Preview” button (bottom right), and you will get the relevant data through. 

Looking at this from the perspective of volumes per auction session, re-enter 0#JAS: as a new data request. This time delete all the actual futures contracts which will leave the following RICs: JAS11, JAS12, JAS13, JAS21, JAS22, JAS23. Associate these RICs with the following Field: “Volume” (under Transactions and Trades). Again ensure that the date is set for the 27th Sep, and hit Preview. This will deliver the remaining volume data required to complete the data request.

Thom Reuters Tick History (academic) archives and stores the actual data which is sent to Thomson Reuters by the TGE. Anything added by TR is usually in the form of Qualifiers, which are also available to be downloaded, which help with the massive task of normalising the worlds financial markets into the Tick History resource.

There are some challenges for academic users of Tick History in interpreting and accessing data, both from the perspective of instrument code descriptions, and associated data fields. There are however some resources available which are probably of some use, in this instance, click on the “SpeedGuide” option on the menu bar, and then enter the following: TGE/JAS , this gives some assistance about TGE specific data, there is a similar page for each futures exchange. Tap in “FUTURES” and you will see the master speed guide index for Futures exchanges.