Tick History – Market Holidays

At this time of year, in many places, thoughts turn to holidays, hence a post about how to get hold of a log of market holidays around the world.

In generating a historical time series of financial markets data it is useful to have access to a list of official market holidays so that data can be appropriately adjusted. Tick History can give you access to this via the SpeedGuide feature.

See the post below about using the SpeedGuide for some introductory details.

Once you call up the SpeedGuide tap in “HOLIDAY1” in the box next to “Page”, this is the master index of market holidays on Thomson Reuters. Choose the jurisdiction which you are interested in by either clicking through the pages and then clicking on the relevant geography, or use the two letters denominating the country you are looking for and add a “/HOLIDAY…..for example: SG/HOLIDAY for Singapore, ES/HOLIDAY for Spain, etc.

A feature worth remembering is that you can ask the database to call up market holidays from previous years by changing the year in the “Date” box. This will request the database to snapshot the market holiday page from the year which you select.

Tick History – US listed Exchange Traded Fund’s

The rise in the popularity and frequency of Exchange Traded Funds (ETFs) around the world is proving to be a popular research theme. Much attention is given to the US markets where ETF’s are obviously much more established than most others. The US model is also helpful in that it offers an Intraday Indicative Value (IIV) for each ETF, this being a value calculated every 15 seconds, throughout the trading day, and published over the Consolidated Tape. The IIV looks at the last price of all securities which make up the ETF, and also factors the value of any other assets, such as cash, contained within the ETF in question. It is useful in that this value can be compared with the prevailing share price of the ETF.

It is possible to retrieve this data, and more, via Tick History. Let’s focus on State Street’s popular SPDR S&P 500 ETF, known under ticker symbol “SPY”, and see what associated data is available.

In your Tick History New Request screen, click on the “Search…” button. Select “Funds” from the drop down menu next to “Asset Class:”. We know that SPY is a US listed ETF, so click on “Select Exchanges” and add all the US exchanges to the right hand box. Go to the “Identifier” line and, make sure RIC and “Starts With” are selected and tap in SPY into the blank box on the right, and click on the Search button.

You will see a list of all the venues on which SPY is quoted, the NYSE operated PSE exchange mnemonic is the source of the conolidated tape for SPY, with the master quote being SPY.P. However you will also see the extensions “dv”, “ec”, “iv”, “nv”, “so” and “tc” between the “SPY” and the “.P” as additional options, such as SPYiv.P.

The key for these is as follows:

“iv” is the published indicative intraday value, the literature informs me that this can also be known as “underlying trading value”, “indicative optimised portfolio value” and “intraday value”.

“nv” is the prior day’s net asset value (NAV), determined in a manner which is consistent with US mutual funds. Simply it is derived by taking the total assets of the ETF, less liabilities, and dividing by the number of ETF shares outstanding.

“so” is the number of thousands of shares oustanding. ETF’s are open-ended and as a result the number of shares on issue may change daily.

“tc” stands for the total cash required per creation unit for creations and/or redemptions executed the prior day, this in order to ensure that the creations and redemptions occur at the NAV value, thus ensuring that existing ETF shares do not experience any dilution in value as a result of creation and redemption activity.

“ec” is the estimated cash amount per creation unit for creations/redemptions executed. This is designed to give an idea of how much cash per creation unit will be needed to redeem ETF shares on a given day. The methodology for deriving this varies per investment manager, so best to read the relevant ETF prospectus for more details about this one.

“dv” is the accumulated dividend per ETF share, net of expenses, through and including the previos day’s close.

Give this a go and see how the numbers come out, but a small warning, given that the IIV is published every 15 seconds, the frequency of this data is obviously much higher than the daily data sourced from the other codes.

AusEquities database – “Qualifiers”

Sirca’s Australian members are able to request access to AusEquities a database of ASX quote and trade data dating back to the beginnings of the electronic market in Australia in 1987. The data is delivered in as raw a format as possible including the Qualifier codes which sometimes are assigned to trade and quote data via the central host.

We are often asked what the Qualifiers mean. The AusEquities help files give a pretty good overview of these, but here is a list of the ITS flags which appear as Qualifiers since their introduction in September 2006 (for Qualifiers prior to this date see the product help files, or get in touch with us):

AK: All or Nothing
AC: Auction Trade
BB: Bulletin Board
BP: Booking Purpose Crossing Reporting
CT: Combination Trade
DR: Direct Reporting
EP: Exercised Put
EQ: Equity/Option Combination
ET: Special Crossing ETF
FK: Fill and Kill (fill as much as possible and cancel the balance)
GL: Government Loan Reporting
IB: Index Replicating Special Crossing
LN: Loan Reporting
LT: Late Trade
MK: Market Price Order
ML: Market-to-Limit Order
OD: Odd-lot Order
OS: Overseas Trade
SP: Special Crossing
SX: Portfolio Special Crossing
TM: Tailor-made Combination
UV: Undisclosed Volume
WH: Wholesale Interest Rate Reporting
XT: Crossing Trade

Tick History – time series data

Regular readers of Dinkum Data will know that I have a tendancy to blabber on about the vastness of the Thomson Reuters Tick History resource, and other Sirca datasets, and the incredible value of all this to academic research. Where else can you have a go at measuring the impact of Bulgarian football results on the price of pork bellies!

Many models lend themselves to comparing datasets which have been measured at uniform time intervals, thus removing some of the complexity and noise created by differing underlying market structures. Whilst this obviously removes much relevant rawness, it does nevertheless constitute a valid and active way forward for research.

We often get asked about how data can be retrieved from Tick History in the form of a time series. Tick History does make certain tools available for this. They will by no means please everyone, given the number of different ways time series data can de derived, but it is an important aspect of the database to understand.

After you log on to the database, set up a new request with the assets which you are researching, and set the relevant time period.

Now click on the “Fields” tab, next to “Message Type:” select “Intraday” from the drop-down menu. A further drop-down menu will appear and you will see that you can select between a time period of 1 second, 5 seconds, 1 minute, 5 minutes, 10 minutes, 15 minutes and 1 hour.

You will note in the “Selected Fields:” box on the right of your screen that the database will default in the following fields:

Open, High, Low, Last, Volume, Average Executions Price, VWAP, and No. of Trades

In otherwords, the database does quite a bit of work for you over the time period which you select, by calculating certain bands, averages and aggregates.

A word of caution though, you will need to think through the asset classes and markets which you select. The databasewill not display data where this is not available (eg. VWAP for Foreign Exchange indicative dealable prices), and it does not know when a market is open and shut (eg. large after market block trades, versus smaller average trades during market hours). Have a good think therefore about the underlying market structures of the data you are seeking to retrieve before you set this up, and obviously do not hesitate to contact us if you get stuck.

Searching for data on Tick History

We were recently asked by a Tick History academic user in Australia about ways of easily and rapidly retrieving a list every ASX listed equity over a defined time period. There are a few ways to do this but this post will highlight the way this can be done by using a regular expression within the “Search” function.
After you logon to Tick History, click on “Request” and then “New”. Click on the “Search” button, top-middlish of your screen.

The Search function is a lot more powerful than you think at first glance. It is quite happy with users searching by using regular expressions. In this case we are looking for all listed equities on the ASX over a defined time period. ASX tickers are usually combinations of three letters followed by “.AX” (ie. the identifier for the Australian Securities Exchange).

You will need to start by ensuring that you select “Equities”, next to “Asset Class:” at the top of the Search box.
Next to “Instrument Type” click on “Equities”.
Next to “Identifier”, Make sure “RIC” and “Starts With” are selected. In the third box along, type in the following string: [A-Z]+.AX$

The “[A-Z]” means that the search is for an upper case letter
The “+” means that there is at least one letter, but any number of follow up upper case letters is OK
The “.AX” is the exchange identifier for the ASX
The “$” eliminates any data after .AX, thus removing a heap of reference and duplicate entries which have the spurious .AXa identifier

Then click Search. You will be presented with a list of valid equities, which you can automatically add to the Search box. Remember that the time period which you have specified separately will dictate the precise list of instruments which are returned, the database is looking for all valid instruments over that period regardless of whether they are still active today.

This regular expression will ensure that you retrieve all ASX tickers over the sample period, this will include things like deferred delivery issues which have an extension like “DA” after the base three letter ticker. A full listing of the ASX suffixes is available via the SpeedGuide (see the Tick History menu bar), type in page AU/CHAR1 and scroll through the options.

Tick History – Timestamps

Most of the world’s exchange based trading these days occurs on venues which use milli-second timestamps. The process of aggregating data which carries individual exchange timestamps obviously involves time, meaning that the official Thomson Reuters timestamp available to Tick History academic users will differ from the exchange specific official timestamp. Important to realise at this point that the connections between exchange venues and Thomson Reuters are of an extremely high quality meaning that Thomson Reuters is likely to receive the exchange data sooner than most, and certainly sooner than terminal based users.

The good news is that many exchanges deliver their official timestamps to Thomson Reuters along with their trade and quotes data. This means that academic users of Tick History can request the official exchange timestamp and compare this with the official Thomson Reuters timestamp. Be aware that not all exchanges offer this, and not all exchanges which do offer a millisecond timestamp.

The process to request this data is as follows. Set up a new request and enter the instrument which you are tracking. Go to “Fields”, making sure that the “Message Type” is set to “Time & Sales”, and under “Trades” in the left hand box about 11 items down you will see “Exchange Time”, select this along with the other data fields which you are after.

Let us know, by leaving a comment, if you wish us to post a list of exchanges for which this facility is available.

Tick History – Exchange Identifiers

This is going to be a rather ugly looking post I’m afraid, but we quite often get asked about whether a table exists which maps Thomson Reuters Tick History Exchange Mnemonics (eg. NSQ for Nasdaq consolidated) to the Exchange Identifier suffixes to RICS (eg. .O as is MSFT.O for Microsoft listed on Nasdaq consolidated). As a result we thought we would post the table. This is also available by tapping in EXCHID01 into the Tick History SpeedGuide. Here we go:

Exchange IDMnemonicExchange NameCountry/Region
.AAOENyse Amex (Options)United States
.AASENyse Amex (Equities)United States
.ADABDAbu Dhabi Securities Exch United Arab Emirates
.ALPALPAlpha Trading Systems (Toronto)Canada
.ALVALVAlpha Trading Systems (Ventures)Canada
.AMAMMAmman SEJordan
.ASAMS/AEXEuronext Amsterdam Netherlands
.ATATH/ADEAthens SE/Derivative SE Greece
.AXASXAustralian SE Australia
.bBFXBrussels Deriv Exchange Belgium
.BBOSBoston SE United States
.BABUEBuenos Aires SE Argentina
.BBBLGBulgarian SE Bulgaria
.BCBARBarcelona SE Spain
=BCSBCSBelarus Currency and SE Belarus
.BDBURBurgundy MTF Nordic Region
.BEBERBerlin SE Germany
.BELBELBelgrade Stock Exchange Serbia
.BFCTLXBaltic Fund Market Estonia/Latvia/Lithuania
.BGCOLColombia Stock Exchange Colombia
.BHBAHBahrain SE Bahrain
.BIBILBilbao SE Spain
.BJBNLBanja Luka SE Bosnia and Herzegovina
.BKSETThailand SE Thailand
.BNBRNBerne SE Switzerland
.BOBSEBombay SE India
.BRBRUEuronext Brussels Belgium
.BRQBUHRASDAQ Listed/RSQ Traded Romania
.BSBTEBATS Europe United Kingdom
.BSXBSXBermuda Stock Exchange Bermuda
.BTBSMBotswana SE Botswana
.BUBUDBudapest SE Hungary
HPXHungarian Power Exchange Hungary
.BVBRABratislava SE Slovakia
.BXBUHBucharest SE Romania
.BYBDBBeirut SE Lebanon
.CCINCincinnati SE United States
.CLCCNational SE United States
.CACAIEgyptian SE Egypt
.CDCNQCNSX-Canadian National SE Canada
.CEBECElectronic Exchange Chile
.CHCISXChannel Islands SE United Kingdom
.CHIINSCHI-X Europe United Kingdom
.CHIpINSChix Mifid United Kingdom
.CIABJBRVM Ivory Coast
.CJ1CHTChittagong SE Bangladesh
.CLCALCalcutta SE India
.CMCSEColombo SE Sri Lanka
.CNCOLColombia SE Colombia
.COCPHCopenhagen SE Denmark
.CRCCSCaracas SE Venezuela
.CSCASCasablanca SE Morocco
.CTCT1Ceto OTC Regulated Market Poland
.CXCCXCCHI-X Canada Canada
.CYCYSCyprus SE Cyprus
.DDUSRWB Germany
.DEGERXetra Germany
.DHDSEDhaka Stock Exchange Bangladesh
.DIDIXNasdaq Dubai(ex-DIFX) UAE
.DLDESDelhi Stock Exchange India
=DMMTSMTSGermany Germany
=DNMTSMTSDenmark Denmark
=DPBARBarcelona SE Spain
.DUDBXDubai Financial Market UAE
.EEOE/AEXAEX-Options & Futures Netherlands
.EAEDXEuropean Derivative ExchangeUnited Kingdom
=ECMTEEuroCredit MTS Italy
.EDEQDEquiductUnited Kingdom
.ESFDISpainish Investment FundsSpain
=ESMTSMTSSpain Spain
.EWEWXEuwax Germany
.EXEUXEurex Deutschland Germany
.EXEUXEurex Switzerland Switzerland
.FFRAFrankfurt SE Germany
=FLMTSMTSFinland Finland
.FUFKAFukuoka SEJapan
.FXTFXThailand FuturesThailand
.GGBKGBank of GreeceGreece
.GHGSEGhana SEGhana
.GOPTXPure TradingCanada
.GQGYQBolsa de GuayaquilEcuador
.HHAMHamburg SEGermany
.HAHANHanover SEGermany
.HEHEXHelsinki SEFinland
.HFHFEHong Kong Futures ExchangeHong Kong
.HKHKGHong Kong SEHong Kong
.HMVSEHochiminh SEVietnam
.HNHASHanoi Stock ExchangeVietnam
.HNOUPCUnlisted Public Company MarketVietnam
.IISEIrish SEIreland
.ICICXIceland SEIceland
.IIISSIntl Sec Exch – EquitiesUnited States
.IpISEIrish MifidIreland
.ISIST/TDEIstanbul SETurkey
ISTIstanbul Gold ExchangeTurkey
TDETurkish Derivatives ExchangeTurkey
.IXHIXHInstinet HKHong Kong
.JJNB/SFXJohannesburg SE/SafexSouth Africa
.JKJKTIndonesia SE (formerly JSX)Indonesia
JFXJakarta Futures ExchangeIndonesia
.KNYQNYSE ConsolidatedUnited States
.KASQNYSE Amex ConsolidatedUnited States
.KAKARKarachi SEPakistan
.KFMDXBursa Msia Derivatives ExMalaysia
.KLKLSBursa Msia Securities ExMalaysia
.KSKSCKorea SE (Koscom)South Korea
.KWKUWKuwait SEKuwait
.KYKYOKyoto SEJapan
.KZKAZKazakhstan Stock ExchangeKazakhstan
.LLSELondon SEUnited Kingdom
.LLIFLIFFEUnited Kingdom
.LALATLatino American MarketSpain
.LGLAGNigeria Stock ExchangeNigeria
.LJLJULjubljana SESlovenia
.LNLNMLe Nouveau MarcheFrance
.LSLISEuronext LisbonPortugal
.LTLSELondon Latest Touch systemBritain
.LULUXLuxembourg SELuxembourg
.LUFRCTLuxembourg Domiciled FundsLuxembourg
.LZLUSLusaka Stock ExchangeZambia
.MMONMontreal ExchangeCanada
.MAMADMadrid SESpain
=MBBNCBank Negara MsiaMalaysia
.MCMCEMercado ContinuoSpain
=MCXMCXMICEX (Currency data)Russia
=MFMFIMadrid SE Fixed IncomeSpain
=MHMSTMTS PortugalPortugal
.MIMILMilan SEItaly
.MKEMKEMacedonia Stock ExchangeMacedonia
.MNMTVMontevideo Stock ExchangeUruguay
.MOMSEMoscow SERussia
.MOTMOTMontenegro SEMontenegro
.MTMLTMalta SEMalta
.MUMUNMunich SEGermany
.MVMLSMalawi SEMalawi
.MWMIDChicago SEUnited States
.MXMEXMexico SEMexico
.MZMAUMauritius SEMauritius
.MZAMENMendoza Stock ExchangeArgentina
.NNYSNew York SEUnited States
.NFFNFFNorwegian Fund Broker AsstnNorway
.NGNGONagoya SEJapan
.NGMNGMNordic Growth MarketSweden
.NHNSXNational SE for AustraliaAustralia
.NLBUHRomanian Equities TNLRomania
.NMNSENamibian SENamibia
.NPNWXNordpool Energy Exch OptionsNorway
.NRNAINairobi SEKenya
.NSNSINational SEIndia
.NZNZENew Zealand SENew Zealand
.ONSQNasdaq ConsolidatedUnited States
.OBOBBOTC Bulletin BoardUnited States
.OJOSAHercules NipponJapan
.OLOSLOslo SENorway
.OMMUSMuscat Sercuities MarketOman
.OQNSMNASDAQ Stock MarketUnited States
.OSOSAOsaka SEJapan
.PPAOPacific OptionsUnited States
.PPSENYSE ArcaUnited States
.PLCPPacific Exchange/ARCAUnited States
.PAPAREuronext ParisFrance
.PCSPCSaint-Petersburg CurrencyRussia
.PDEDPPrimary Dealers Bond MarketEgypt
=PEXPEXPortuguese Private ExchangePortugal
.PFTPFTPFTS Stock ExchangeUkraine
.PKPNKOther-OTC (Pinksheets)United States
.PLPLSPalestinian Securities ExchPalestinian Auth
.PRPRAPrague SECzech
PXEPower Exchange Central EuropeCzech
.PSPHSPhilippine SEPhilippines
.KPCQNYSE Arca ConsolidatedUnited States
.PZPLUPLUS Markets Group PlcUnited Kingdom
.QADSMQatar ExchangeQatar
=QIMTEMTS Quasi-Government MarketItaly
.QMFQMFQuote MTF LtdHungary
=RCBUEBuenos Aires ContArgentina
.RCTReuters Contributed Exchange CodeNA
.RFRFXRosario Futures ExchangeArgentina
.RIRIXRiga Stock ExchangeLatvia
=RNMTXMTS Reference PricingItaly
.RTSRTSRussian Trading SystemRussia
.SSWXSIX Swiss ExchangeSwitzerland
.SASAOSao Paulo SEBrazil
.SESAUSaudi SESaudi Arabia
.SFSFESydney Futures ExchAustralia/New Zealand
.SGSTUStuttgart SEGermany
.SHBKBQTBe QuotedSweden
.SJSJRSarajevo SEBosnia & Herzegovina
.SNSGOSantiago SEChile
.SPSAPSapporo SEJapan
.SSSHHShanghai SEChina
.STSTOStockholm Stock ExchangeSweden
.STSTOStockholm OptionsSweden
=SUSUBIndonesia SE (formerly SSX)Indonesia
.SZSHZShenzhen SEChina
.TTYOTokyo SEJapan
.TATLVTel Aviv SEIsrael
=TATLVTel Aviv SE (Bonds)Israel
.TETEJTaiwan Economic JournalTaiwan
.THTHMThird Market StockUnited States
.TLTLXTallinn SEEstonia
.TMTIMTaiwan Futures ExchangeTaiwan
.TNTUNTunis SETunisia
.TOTORToronto SECanada
.TWTAITaiwan SETaiwan
.TZDSSDar Es Salaam SE LtdTanzania
.UOPQOPRA NBBO OptionsUnited States
.UAXUAXUkrainian ExchangeUkraine
.UEUEXUruguyan Electronic ExchangeUruguay
.UPUKPXUK Power ExchangeUnited Kingdom
.VCVETSX VentureCanada
.VNEXTSX Venture-NEXCanada
.VAVLNValencia SESpain
.VIVIEVienna SEAustria
.VLVLXVilnus SELithuania
.VXVTXSwiss Blue Chip Segment, (formerly SWX)Switzerland
.WWCBChicago OptionsUnited States
.WAWSEWarsaw SEPoland
WPGICE Futures CanadaCanada
.XPHOPhiladelphia OptionsUnited States
.XRXIMXetra International MarketGermany
EEXEuropean Energy ExchangeGermany
.YISOInternational SecuritiesUnited States
.ZLCZBATS trading For Nasdaq (OMX Global Mkt)United States
.ZAZAGZagreb SECroatia
.ZIZSEZimbabwe Stock ExchangeZimbabwe

Tick History – retrieving Corporate Actions data

The Tick History database is understandably usually associated with Tick data, however it also faithfully records slower data such as Corporate Actions. We were recently asked by a subscriber in Australia about how to retrieve dividend data for ASX listed stocks over a multiyear period. Here is a copy of our explanation to this request:

Log on to TRTH and click on “Request” and “New”. This is the point at which you enter the list of companies which you wish to retrieve dividend info for. It is possible to enter this manually, ie. entering each company code individually, but it is also possible to use the “search” function on TRTH to get a comprehensive list of ASX listed equities.

As a first step make sure that the time period box at the bottom left of the New Request screen accurately reflects the period over which you wish to retrieve dividend data for. For example if you wish to retrieve dividends from the 1 Jan 2005 to present day, set the “From:” date to 01-Jan-2005, and put today’s date in the “To:” box. This will ensure that you pick up dividend data for any companies which may have delisted over that period.

The next step is to click on the “Search” button, and make sure that “Equities” is selected next to the “Asset Class:” option. Then highlight the “Equities” radio button next to “Instrument Type”.

Next to “Identifier” select “Starts With” and in the box to the right of “Starts With” type in [A-Z], indicating that you only want RICs which start with Latin letters. (this removes a heap of superfluous RICs which relate to levels of market depth, not relevant for the purposes of this subject).

Next, click on the “Select Exchanges” blue text next to “Exchange”, double click on Oceania, and then double click on Australia.

You have now set up your search screen to pick up all relevant RICs for listed Equities in Australia from 1 Januaury 2005 to present day. You now need to click on “Search”.

A few seconds later several thousand instruments will be returned, click “Select All” and then click “Add”.

The Tick History database is now loaded up with all Australian equities over the requested time period, the next step is to select the “Fields” which you wish to retrieve. In this case obviously you need Dividend info. Click on Fields, select the “Time & Sales” option under “Message Type”. You then need to empty the defaults which are listed in the “Selected Fields” box on the right by clicking “Remove All”.

In the left hand box under Corporate Actions, double click on “Dividends” and all data fields pertaining to Dividends will be set up in the right hand box. 

You are pretty much ready to go now, suggest that you click on “Preview” first just to get a feel for what you are about to submit. When you are ready click “Submit” and you will get the dividend data which you are looking for. 

Tick History / UK Equities

A prospective Sirca academic user in Rotterdam has been putting Thomson Reuters Tick History’s UK Equities coverage through its paces this week. He notes correctly that the London Stock Exchange made some changes to its market data output in early 2009. At this point the LSE consolidated it’s “Level 1 Plus” package into LSE Level 1 coverage, meaning that volume of orders open at the best bid/ask prices became available using a standard LSE RIC, such as VOD.L. Previously this information had only been available using a dedicated “Level 1 Plus” RIC, ie. VOD.LV.

To retrieve historical data about volume of orders open at the best bid/ask prior to March 2009 therefore, use the LSE RIC stem with a .LV extension.