2012 Update to the Corporate Governance Database

The 2012 update to the Corporate Governance Database is now available via the Sirca Member Portal.

The latest edition contains Corporate Governance information for ASX listed companies with balance sheet dates to December 2012. Now included are records for foreign incorporated companies with primary listing on ASX and Australia incorporated companies reporting in foreign currencies.

Members are advised to pay close attention to the product documentation located via the Help button. If anyone requires assistance please be sure to lodge your enquiry via the Helpdesk function in the Member Portal.

AusEquities gets a boost!

Sirca Members using AusEquities should have noticed a boost in performance recently. The Sirca team have more than doubled the processing power dedicated to the AusEquities service resulting in jobs moving through the system much faster.

We’ve also added a dedicated processing system for “small” requests (50 instrument days or less – i.e. 4 instruments for 5 days history = 20 instrument days) which means that small requests get serviced quickly and are not queued behind large jobs.

As always, Sirca welcomes your feedback so feel free to let us know via the Helpdesk link in the Member Portal

Update to the Thomson Reuters Tick History API for R

We have released an update to the TRTH API for R package. This update adds additional functionality to the submitRequest and submitFTPRequest functions where it is now possible to submit and display requests in Local time. Previously this was only available in GMT.

Please contact us if you would like the updated install file and any additional information.

Market Capitalisation in Thomson Reuters Tick History

There is no direct field in TRTH for market capitalisation however it’s still possible to retrieve this using a simple two-step process.

Once you’ve populated your instrument list, under the fields tab and Corporate Actions message type, select the field Default Share Type > Thousands of Shares. This will give you the field for number of shares outstanding. You can use this along with last prices to calculate market cap figures. It will be required to retrieve two separate requests for the different Message Types, one for shares outstanding and one for last prices.

CRD Documentation

Documentation for the data in the CRD is available via the Data Library > CRD folder. The documentation provides the field descriptors for both the price and announcement files. The Bellamy report referenced on page 3 of the CRD user guide for the calculation of dilution factors in the price files is also available in the same location.

Consolidated Instruments / Tick History

Consolidated instruments, also known as composites, refer to a set of Thomson Reuters instruments that update with a message stream from multiple sources or data streams, so that all trades and the best available quotes across the multiple sources, are condensed into a single stream. These sources typically include the primary exchange or market as well as the set of regional or ECN (Electronic Communication Network) markets (often referred to as the secondary market) where the instrument trades domestically. An example of a similar service, outside Thomson Reuters, is the Option Price Reporting Authority (OPRA), which consolidates option contracts on participating exchanges.

In the USA, the primary exchanges or markets are:
  • NYSE
  • NYSE Amex
  • NYSE Arca
  • NASDAQ
The set of regional or secondary markets are:

  • OMX BX
  • National SE
  • Chicago SE
  • Third Market Corp
  • OMX PSX
  • International Securities Exchange
  • FINRA
  • BATS Trading Inc
  • Direct Exchange Holdings EDGX
  • Direct Exchange Holding EDGA
  • Chicago Board Option Exchange

Instruments that are listed on one of the primary exchanges or markets: NYSE, NYSE Amex or NYSE Arca, can trade on any of the regional exchanges, while instruments that are listed on NASDAQ can trade with Unlisted Trading Privileges (UTP) at any of the regional or secondary markets.

Stocks that trade on NYSE, NYSE Amex or NASDAQ markets are often referred to as “listed” stocks. Listed stocks cannot be traded on any of the other listed exchanges. NYSE Amex trades primarily in small-cap stocks, Exchange Traded Funds (ETFs) and derivatives, NYSE trades in medium to large cap stocks and funds, while NASDAQ trades primarily in technology listings of varying market capitalization.

For the primary NYSE exchanges, the consolidated instrument is always indicated as the ticker symbol without an exchange id; that is, the consolidated instrument ticker is either represented as 1, 2 or 3 letters. Where the instrument is a preference share, rights issue, unit issue, or “when_issued”, the consolidated instrument has the appropriate _p, _r, _u, or _w suffix appended, e.g. JP_p represents the consolidated preference share for JP Morgan.

Where more than 3 characters are used to describe the ticker symbol on the NYSE or NYSE Amex exchanges, the consolidated instrument is indicated with a .K exchange identifier suffix, e.g. QUAD.K is the consolidate instrument for QUAD.N trading on the NYSE (QUAD is the ticker symbol for Quad/Graphics Inc.). This convention is used so that the consolidated instrument will not conflict with a possible 4 character page-based code.

Assume GE.N is the symbol for General Electric trading on the primary NYSE exchange, General Electric can trade on the following exchanges:

  • NYSE – GE.N (primary listing)
  • OMX BX – GE.B
  • National SE – GE.C
  • Chicago SE – GE.MW
  • Third Market Corp – GE.TH
  • OMX PSX – GE.PH
  • NYSE Arca – GE.P
  • FINRA – GE.DF
  • BATS Trading Inc – GE.Z
  • Direct Exchange Holdings EDGX – GE.DG
  • Direct Exchange Holding EDGA – GE.DY
  • Chicago Board Option Exchange – GE.W
The consolidated instrument is GE.

The quote and trade message stream of the consolidated instrument includes the quote and trade message stream applicable to the primary and the regional exchanges or markets where the instrument trades.

Each quote message will indicate the exchange of origin for the quote via the Field Identifiers (FID) “BID_MMID1” or “ASK_MMID1” – these FIDs will update with the appropriate exchange IDs as follows:
  • NYSE – NYS
  • NYSE Amex – ASE
  • OMX BX – BOS
  • National SE – CIN
  • Chicago SE – MID
  • Third Market Corp – THM
  • OMX PSX – XPH
  • NYSE Arca – PSE
  • FINRA – ADF
  • BATS Trading Inc – BAT
  • BATS Y EXCHANGE – BTY
  • Direct Exchange Holdings EDGX – DEX
  • Direct Exchange Holding EDGA – DEA
  • Chicago Board Option Exchange – WCB
Each trade message will indicate the exchange of origin for the trade via the FID “IRGXID”.

Any message that is reported with the consolidated instrument is also reported with the applicable regional or ECN instrument, e.g. a message reported with the consolidated instrument GE with a trade venue of NYS will also be reported as a trade in the instrument GE.N; while a quote reported with GE as quote venue PSE, will also be reported as a Quote to the instrument GE.P.

Consolidated Instruments on NASDAQ

All consolidated instruments on NASDAQ have the exchange suffix .O. These instruments will update with the message stream of trading and quote activity on the NASDAQ exchange as well as regional exchanges and markets – commonly known as Unlisted Trading Privileges (UTP). For example, MSFT.O is the consolidated instrument for Microsoft Inc. trading on NASDAQ, MSFT.OQ represents the instrument trading on NASDAQ, while MSFT.DF, MSFT.A; MSFT.B, MSFT.C, MSFT.P, MSFT.Z, MSFT.DY, MSFT.DG, MSFT.ZY, MSFT.PH, MSFT.MW, MSFT.W, MSFT.P represent Microsoft Inc. trading UTP on a regional exchange or market.

Consolidated Instruments in Germany

In Germany, a consolidated instrument exists for all instruments trading on XETRA or the Frankfurt SE. The consolidated will include all quotes and trades for instruments trading on the primary or regional exchanges:
  • XETRA
  • Frankfurt
  • Stuttgart
  • Hamburg
  • Dusseldorf
  • Berlin
  • Munich
  • Hanover
The Consolidated instrument has the exchange id suffix code .DEU. The quote message of the consolidated instrument will indicate the exchange of origin for the quote via the FID “GV5_TEXT” – where the first 3 characters indicate the exchange of the Buyer and the last 3 characters indicate the exchange of the Seller, where the following IDs are applicable:
  • GER – Xetra
  • FRA – Frankfurt
  • BER – Berlin
  • DUS – Dusseldorf
  • HAM – Hamburg
  • HAN – Hanover
  • MUN – Munich
  • STU – Stuttgart
Similarly, for Trades, the venue is indicated by the FID “GV6_TEXT”.

Consolidated Instruments in Japan

In Japan, three sets of composite instruments exists:

  • Exchange ID: ”.J1” – includes on-board and off-board trading for TSE, TOSTNET and OSE J-NET, e.g. instruments with suffix codes: “.T”, “0.T” and “L.OS”
  • Exchange ID: “.J2” – includes off-board trading only for TOSTNET and OSE J-NET, e.g. instruments with suffix codes: “0.T” and “L.O”
  • Exchange ID: “ .J3” – includes on-board and off board trading for TSE, OSE, Nagoya, TOSTNET and OSE J-NET, e.g. instruments with suffix codes: “ .T”, “.OS”, “.NG”, “0.T” and “L.OS”
All off-board trades are indicates by the FID “STOCK_TYPE” containing the following values:

  • OL – TOSNET Single
  • OB – TOSNET Fixed
  • OF – TOSNET Basket
All on-board trades are indicated by the FID “TRDTONEA_1” containing the following values:
  • T – TSE sourced
  • O – OSE sourced
  • N – Nagoya sourced
Quote for composite instruments are not available.

Short Sales / Short Selling

Short sales of equity is an area of increasing interest to academic researchers and we frequently receive requests for help with such data. End of day summary information on short sales data is available for several exchanges.Examples include,Australia(.AX), Hong Kong (.HK),Korea(.KQ),Taiwan(.TW),Thailand(.BK),India(.BO and .NS) andPoland(.WA).The access procedure is similar for each exchange but differs sometimes in the detail.In each case you need to use a RIC indicating you want short sale information.For .AX, .HK, .KQ and .TW that RIC contains a “stat” string.For example, BHPstat.AX specifies the end of day short sale summary for BHP.AX, 0001stat.HK identifies short sale data for 0001.HK and 000250stat.KQ corresponds to short sale data for 000250.KQ.The extra RIC string is slightly different for the other exchanges.It is “ss” for .BK, .BO and .NS and “s” for .WA.For example, ADVAss.BK, or AAAIss.NS, or ACPPs.WA. Notice these short sales RICs all contain a mixture of upper and lower case letters.Case is relevant for these RICs and must be entered correctly. Once the appropriate short sales RICs are correctly specified, you next need to select the correct data fields.These are the End of Day Message Type found under the Fields tab.Select them all and Preview a small data sample to see what is available.The range of populated fields varies from exchange to exchange but Volume is available from every exchange.Last is provided by .HK and .KQ. Short sales summary data is available too under the Time & Sales Message Type.If you choose this source then you will receive summary data as it was delivered to Thomson Reuters IDN service.That can be more than once per day.For instance, .HK has a morning and afternoon summary signal.Both are visible here.In order to see these you need to select a different range of fields.Time & Sales data has its own Short Sales category under the Transactions heading.Choose these to view the Time & Sales sourced “stat” data.A word of warning is warranted here though.The Time & Sales sourced data shows exactly when that data was received by Thomson Reuters.This could be a day after the interval it represents.For instance, if you compare Time & Sales Short Sales values with those obtained through the End of Day Message Type for BHPstat.AX you will see the Time & Sales data lags the End of Day data by one day.The End of Day data date has been reset to the interval to which it corresponds whereas the Time & Sales date shows when the summary was received.Naturally, that is after the interval ends. Information on individual short sales is also available, sometimes.The “stat”, “ss” and “s” RICs identify end-of-interval summary data but information for individual short sales can be found too, when it is reported.This last qualification is important because it seems many short sales are not reported on some exchanges.Nevertheless, when they are, they can be found by requesting data as normal, for example, BHP.AX, rather than BHPstat.AX, and by making use of the Qualifiers field under Trade Transactions.In the case of ASX listed equity, Qualifier fields containing SH[GV4_TEXT] indicate short sale trades.Qualifier codes for short sales could differ at other exchanges (see Where to get help for TRTH? for a link to more help on qualifier codes). SIRCA members have one final source for end of day net short sales summary data. That source is SIRCA’s Data Consult team.SIRCA members can submit a Data Consult request (see Sirca DataConsults for more details) to obtain the same End of Day summary data as described above for .AX, but over a longer interval.The oldest such data you source from TRTH begins with 11 June 2008.The Data Consult Team can source the same ASX data as far back as late 2001. SpeedGuide (see Speedguide Explained for help of using Speedguide) is another useful source for short sales information, either to discover instruments eligible for short selling, or to identify those that are restricted from short selling.The Speedguide page WORLD/NOSHORT01 gives RIC Chains (see What is a RIC Chain? for more on these) for various countries identifying short selling restrictions.WORLD/NOSHORT02 continues that listing and also provides RIC Chains for equity and bond securities inPoland that can be short sold.The Speedguide page ASIA-SHORTSELL gives RIC chains forIndia andThailand covering both stocks that are restricted and not restricted from short selling.Other Speedguide pages, like JK/FAQ, show stocks that are eligible for short selling.In the case of JK/FAQ, eligible stocks in Jakata are shown.

LIBOR OIS Spreads / Tick History

We are currently receiving quite a few requests for help from academics looking into derivative classes which up until recently would have been deemed mysterious and complex. One such group is spreads between LIBOR and OIS. The wikipedia entry for LIBOR – OIS spreads is: http://en.wikipedia.org/wiki/LIBOR-OIS_spread

Data exists for 10 currencies: EUR, GBP, USD, JPY, CHF, CAD, AUD, NZD, SEK, DKK.

6 tenors are available for EUR, USD, GBP and JPY: 1 week, 1 month, 2 months, 3 months, 6 months and 1 year.

5 tenors are available for CAD, CHF, AUD, NZD, DKK and SEK: 1 month, 2 months, 3 months, 6 onths and 1 year.
It is possible to use the Tick History database to retrieve historical LIBOR OIS Spread fix rates as well as tick data.

1. Fix’s

For the LIBOR OIS Spread Fix rates, data is calculated on each day that a LIBOR fix is published. The “ask” price for the relevant OIS instrument is snapshot at 11h00 London time, across each applicable currency and tenor. Once the LIBOR rates are published, the OIS rate is subtracted from this, and mltiplied up by 100.

A chain is available which shows the full coverage for LIBOR OIS Fix’s, enter the following RIC: 0#LIBOROISF=R

Individual instruments are available using the following RIC structure:  XXXL-YYYF=R

XXX is the three letter currency code, and YYY describes the tenor as follows:

01W for 1 week
01M for 1 month
02M for 2 months
03M for 3 months
06M for 6 months
01Y for 1 year

As an example, the RIC for the LIBOR OIS spread fixing for the Euro, with a tenor of 1 week, is “EURL-01WF=R”.

Coverage of this asset class started on 2 May 2009.

2. Tick data

LIBOR OIS Spread Tick data is updated everytime there is an update to the underlying OIS price on the Thomson Reuters network, and when LIBOR is published. Again the OIS rate is subtracted from the prevailing LIBOR rate, and the result is multiplied by 100.

A chain is available which shows the full coverage for LIBOR OIS spreads, enter the following RIC: 0#LIBOROIS=R

Individual instruments are available using the following RIC structure: XXXL-YYY=R

XXX is the three letter currency code, and YYY describes the tenor as follows:

01W for 1 week
01M for 1 month
02M for 2 months
03M for 3 months
06M for 6 months
01Y for 1 year

As an example, the RIC for the LIBOR OIS spread fixing for the Euro, with a tenor of 1 week, is “EURL-01W=R”.

Coverage of this asset class started on 2 May 2009.

Tick History – the dreaded “Settings” button

Most academic users of the Tick History database are interested in efficient extraction of data, rather than the technical intricacies of the database. At times however it just pays to spend a few minutes understanding how to configure the database to help with data requests. The good news is that this configuration is usually a set & leave, rather than something which has to be frequently updated.

Click on the Settings button from the product menu bar, you will be presented with “Preferences”, “Default Settings”, “Change Password” and “Change Security Question”. For obvious reasons we will concentrate on the “Preferences” and “Default Settings”.

1. Preferences

Click on “Preferences” and you will be presented with three sub categories.

(i) Expand Chain RIC

As we consider this it is important to remember that the date which is showing in the bottom left of the set up screen defines the point in time over which the database is operating. A chain has been defined in a previous post, click here for more details. It is obviously made up of a group of assets which can, and usually do, vary over time. Imagine how the constituents of a stock market index change over time for instance. This setting tells the database how to handle chain expansion requests, either by snapshoting the constituents of the chain at the “From:” date, or at the “To:” date (obviously both of these dates are defined by the user). Alternatively, you can collect all the constituents of the chain over the entire date range. Finally, it is possible to eliminate the chain expansion capability fully by checking the “Never” button.

This earlier post gives you more about this setting.

(ii) Verify RICs

RIC verification checks whether your specified RICs actualy existed. It safeguards against unwanted data requests both from mistyped RICs and mistaken specifications, either in tickers or exchange identifiers. So it saves you from requesting data that does not exist. It is important to realise that RIC verification occurs over specified intervals. Three options are specified here. If you are confident about when a RIC exists you can choose “Over date range:”. TRTH then searches for your RIC only in the sample period defined by your “From” and “To” dates. This option is faster than searching the entire data set, which is what happens when you choose from “1996 onwards”. However, choosing “1996 onwards” is a better choice when you are less sure of trading dates because it is equivalent to asking if your RIC ever existed in TRTH. A second advantage of choosing “1996 onwards” is that all available information is gathered about changes connected with each RIC over the life of TRTH. This change related information is what you see reported in the New Request screen. In contrast, “Over date range” only delivers that change data as it was observed in your sample period. The final RIC verification option is “Never”. It is appropriate when you do not want RIC verification and its attendant reporting of changes.

(iii) Others, including timeout default

The timeout default is pretty self explanatory. The other three check boxes are worth considering. The first and second box ask the user whether they want to be presented with the previous search request amd results, this reflects the fact that users tend to spend a bit of time fine tuning their requests before submitting them, and as a result it helps not having to set up the search again every time a refinement is required. The third box asks for confirmation that you are happy working with the Reuters Instrument Code symbology.

2. Default Settings

Click on “Default Settings” and you will be presented with three sub categories.

(i) General

This tab is important in that it sets the clock at either “Local Exchange Time” or “GMT/UTC Time”. Local Exchange Time will relate to the clock associated with a specific exchange, it is obviously pretty meaningless for the purposes of comparing exchanges which sit across different timezones, or for asset classes and data which does not sit on an exchange.

Users can also set their preferred date format (European versus US etc).

The three check boxes are important in that they populate otherwise empty fields that have not changed over time, allow you to include the current RIC for the instrument in the data request, and, if available, will adjust the data for any applicable corrections and cancellations which may have been published by a venue subsequent to the original data being recorded.

(ii) Scheduling

Where a user would like to set up a regularly scheduled database retrieval request, this is where the defaults for this are established.

(iii) Data Delivery

This allows you to define maximum individual files sizes from 10MB up to a maximum of 500MB. Under file format you can choose to split large data requests into separate files per instrument. Finally, you can avail yourself of FTP push functionality if the default FTP pull from your university email address restricts you. This, along with a post about API access to the database, will be subject of a future post.