Recently a student asked the question if RIC codes could be used in other Databases to search for more information on companies. In a nutshell, the answer is: to a certain point and with some manual searches many codes can be indeed turned into codes for other databases if you can use Datastream.

RIC codes are Reuters Instrument codes (Wikipedia). These codes usually originate from Thomson Reuters databases like Thomson One, Eikon or Thomson Reuters News Analytics. The RIC codes for companies usually have a root code and a suffix for the market of a listing. If a company has multiple listings the root stays the same but the suffix gives indicates a different market. A theoretical example: the IBN company has the root code **IBM** and for a listing at the london stock exchange **.L** would be added to get the RIC code: **IBM.L **. If the listing is at the New York Stock exchange **.N** would be added to create **IBM.N** .

In the example of the student he got the list from a supervisor and was asked to get some data for these companies. The trouble was: most databases cannot work with RIC codes as these are proprietary and are not included as codes. If your university/organisation does not have a license to Thomson databases (like the ones mentioned above) but does have a Datastream license, the codes can still be converted (somewhat) into codes for this database. Although Datastream is a Thomson Reuters database, and RIC codes (or Thomson1 codes) can be recognised somehow, Datastream sometimes does not provide output or does not return the RIC codes.

Here follows step by step how I still managed to convert the original RIC codes into codes that could be recognised (mostly) by Datastream and return data:

**1)** Create a new list of codes where the root codes are seperated from the market codes. In excel this can be done by using the function from the **Data** tab, called: **Text to columns**. Important to remember here is: not all market codes start with a dot. Some codes stqart with the “*equal to*” character **=**. In Excel this character usually signifies the start of a function. Be sure to use the Text to columns option and seperate everything out in one or more rounds and have every column seperated as **Text**.

**2)** Create another column with the root codes seperated from the market codes in a similar way as before using the Text to columns option. This time you do not seperate the RIC code out in two text colums. The first column which carries the root code should now be left at “**general**“:

Some company RIC codes start with numbers that include **zero** characters in front. These zeros need to stay there because they are part of the code and when converting RIC codes to codes for Datastream these zeros sometimes need to still be there. In essence: we get two columns where you can choose either the original RIC root codes, or the abbreviated ones when necessary.

**3)** Now create a new column where you recreate the original full suffix code which includes the dot or equal to (=) character. The end result should look as follows:

**4)** The next step is finding out which RIC market codes represent certain exchanges. I did a few searches and found an excellent site from SIRCA that provided me with a list. *I copied and saved this as a text file and imported this text file where each column is a TEXT column*. I did not copy the list to Excel immediately from the website as this affected some market codes: specifically the ones that start with the “equal to” character: =

The end result looked as follows:

**5)** As a next step I matched the market codes from the original list with the list of market codes using 3 times the Excel function **=Vlookup()**. The result looks as follows:

**6)** What I now need to do as the final step is create two more columns in case I need to change codes from a specific market/exchange and need to add a suffix code, a prefix code, or both. For the final column I can now use the Excel funstion **=Concatenate()** to create alternative codes for the RIC codes and have Excel automatically add prefixes or suffixes as required for a list of codes.

**7)** The last step involves trial and error searches to find out for certain markets what prefix or suffix codes can be added to create either Datastream Mnemonic codes or Local codes that Datastream can actually work with. Example:

**Warning 1**: *Remember that some exchanges that numbers as codes for companies and may require either the original codes with zeroes or not. Choose the relevant Ticker column A or B (which we created) when the Mnemonic or local code requires it.*

**Warning 2**: *Most exchanges use common prefixes or suffixes but not all of them: for some exchanges you may need to look up all codes!*

**8)** Using the list or newly created codes we can now do a **Static search** in Datastream to find out if all the codes are stil current and which are not: the original RIC code list may contain codes that have changed and some codes were deleted (even though the company data is still there). *First copy the new codes to a new sheet (or column) and paste them as values to get rid of the Excel formulas. Then upload them using the Datastream option*: “**Create List(From Range)**“:

**9)** Next Do a **Static Request** search and use the list to get some basic data back that signifies which companies are associated with these codes. In the example search I requested the following codes back: DSCD (Datastream code), NAME, ISIN code. The list of codes that we uploaded at step 8 we can find/use by clicking the **List Picker** button below the orange Find series button.

If the original list of RIC codes is a recent one, chances are you will get many good codes back which can then be used to get actual data from Datastream or another database. The result looks something like this:

N.B.: *When you have access to databases like Amadeus, Orbis, Orbis Europe or Compustat you can also try uploading the RIC root company codes as tickers and see whether they are recognized there as well. This method is more quick & dirty (tricky), as you do not know whether the right listings and ticker codes will be available for the companies.*

Email

Filed under: Datastream, Eikon, Thomson One | Comments Off on Is it possible to use RIC codes in Datastream?