Converting company name to stock ticker

[Giles German]

I am trying to create a new string variable of stock ticker that I can then use as a macro in my STATA analysis. Examples can be seen below.

The assumption should be that the information I want is before the ":" but after the final space " ". The exchange comes after the ":" and the stock can be and combination of 3 or 4 letters or numbers. I've tried to give a broad range of examples as follows: -

Aaron's Inc AAN:NYQ ------> AAN

Adamis Pharmaceuticals Corp CY3B:FRA -----> CY3B

Ab Dynamics PLC ABDP:LSE ------> ABDP

Adtec Plasma Technology Co Ltd 6668:TYO ------> 6668 Addus Homecare Corp A41:FRA ------> A41

[Clyde Schechter]

gen last_space = strrpos(stock_name, " ")
gen first_colon = strpos(stock_name, ":")
assert last_space < first_colon
gen ticker = substr(stock_name, last_space+1, first_colon-last_space-1)

Note: Not tested. In addition to the possibility of typos, this code will fail if there are any stock names that don't contain a space before a colon.

[William Lisowski]

Giles, please do follow the advice you have been given before and review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, and to use the dataex command to provide sample data, as described in section 12 of the FAQ. Note also that the program we are using is named Stata, not STATA.

With that said, I transformed your stuff from post #1 into a usable data extract for Stata, and then ran Clyde's code, confirming that, on these samples, it does indeed work as expected.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str39 stock_name
"Aaron's Inc AAN:NYQ"                    
"Adamis Pharmaceuticals Corp CY3B:FRA"   
"Ab Dynamics PLC ABDP:LSE"               
"Adtec Plasma Technology Co Ltd 6668:TYO"
"Addus Homecare Corp A41:FRA"            
end
gen last_space = strrpos(stock_name, " ")
gen first_colon = strpos(stock_name, ":")
assert last_space < first_colon
gen ticker = substr(stock_name, last_space+1, first_colon-last_space-1)

. list, clean noobs abbreviate(12)

                                 stock_name   last_space   first_colon   ticker  
                        Aaron's Inc AAN:NYQ           12            16      AAN  
       Adamis Pharmaceuticals Corp CY3B:FRA           28            33     CY3B  
                   Ab Dynamics PLC ABDP:LSE           16            21     ABDP  
    Adtec Plasma Technology Co Ltd 6668:TYO           31            36     6668  
                Addus Homecare Corp A41:FRA           20            24      A41

[Giles German]

This works great...,

For the European stocks only I then want to create another variable with the stock exchange attached so for the London item which will also be used in my analysis later in the code. This would convert as follows: -

Adamis Pharmaceuticals Corp CY3B:FRA -----> CY3B.F

Ab Dynamics PLC ABDP:LSE ------> ABDP.L

Adler Real Estate AG ADLX:GER-----> ADL.DE AB Science SA AB:PAR----> AB.PA

I don't have a code as I have no idea where to start with this. I suspect a find replace on the String is what I am after.

[Clyde Schechter]

You need to provide a crosswalk between the three-letter country codes that follow the colon character and the other country codes you want to append to the stock ticker. There is no code that can figure out that FRA corresponds to .F but GER corresponds to .DE and PAR to .PA. Please post an example Stata data set (-use dataex-) that gives this correspondence.

[William Lisowski]

Well, you can start by adding

gen exchange = substr(stock_name, first_colon+1,.)

to your code to extract the exchange code following the colon

. list, clean noobs abbreviate(12)

                                 stock_name   last_space   first_colon   ticker   exchange  
                        Aaron's Inc AAN:NYQ           12            16      AAN        NYQ  
       Adamis Pharmaceuticals Corp CY3B:FRA           28            33     CY3B        FRA  
                   Ab Dynamics PLC ABDP:LSE           16            21     ABDP        LSE  
    Adtec Plasma Technology Co Ltd 6668:TYO           31            36     6668        TYO  
                Addus Homecare Corp A41:FRA           20            24      A41        FRA

Then you will want generate a new variable based on the values of the ticker and exchange variables, with something like this untested code.

generate str8 te = ""
replace te = ticker+":F" if exchange=="FRA"
replace te = ticker+":DE" if exchange=="GER"
[and so on]
// make sure you haven't missed any exchanges
assert !missing(te)

[Timme Ariens]

Hi

My question is in line with this question but I can't quite figure out how I can apply this to my dataset. I am researching M&As. However, I need to have a ticker variable in my transaction data in order to merge with another dataset. Currently, I have a collum named BuyersInvestors and each observation is like this:

Boston Scientific Corporation (NYSE:BSX)

or

Windtree Therapeutics, Inc. (NasdaqCM:WINT)

or

Mistras Group, Inc. (NYSE:MG)

so I want to get a new variable with the ticker BSX WINT and MG seperately for each observation.

Thanks in Advance!

[Clyde Schechter]

Assuming that BuyersInvestors is a string variable:

gen ticker_location = strpos(BuyersInvestors, ":") + 1
gen ticker = substr(BuyersInvestors, ticker_location, .)
replace ticker = subinstr(ticker, ")", "", .)

Note: This assume that a colon never appears as an actual character in a company's name, and that parentheses do not appear in ticker symbol.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Timme Ariens]

Thank you so much! This works!

I will use -datanext- time. I didn't know how to use this function and this is the first time of me posting something.