Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to lookup on a part of the words?



See document. I have a bunch of company names in tab1 column A. In column C i want the ticker symbol that belongs to the company. Therefore I downloaded tickersymbols with company names from several website and stock exchanges (nasday, nsye, yahoo, amse) While the names of the companies I selected are not written EXACTLY the same as from the databases it won't match with VLOOKUP. Therefore I added in tab1. colum D F H J L N P the same formula to each tab linked and another formula with INDEX to select as much as that possible is. I know that not all of the companies of my column A are listed companies so therefore not every company wil get a symbol ticker. 

My goal is to minimize manual handling by using google for looking up symbol ticker and add manual. Is there somebody that could help me with a better formula or way to fix?

with wildcards, index, match search a combination perhaps. or a total other way with not excel help.

Many thanks!



Selected Answer

Before I can invest time in this I need to be convinced of the use. Here's my argument to say that your list is useless. Please correct me where I'm wrong.

  1. Your problem is an old one. Stock exchanges have beenn dealing with it for over 100 years. The solution they found was to ignore the name. They use the symbol instead of a name.
  2. The symol identifies a security, not a company. Some companies have different types of shares which are traded under diffferent symbols. By replacing the symbol with a name the ability to differentiate is lost.
  3. Some shares are traded at different exchanges under different symbols. However, the same symbol at different exchanges doesn't guarantee that the security is the same. A different symbol doesn't guarantee that the security is different if the exchange isn't the same.
  4. Yahoo is not an exchange. It doesn't assign symbols. Instead if uses symbols from a system supplied by someone else, perhaps Bloomberg or Reuters. But Bloomberg isn't an exchange, either. They created symbols to be able to identify securities (not companies!) regardless of the exchange where they are traded. Then Reuters did the same. Now it's your turn. Why don't you just take the list from one of these? It's guaranteed to be much better than what you can ever hope to create yourself.

Of course, that depends upon your purpose. So, what's the purpose? But if your aim were to create a list for arbitraging purposes (compare prices for the same share at different exchanges) the company name can't be made the key because symbols refer to securities, not companies.



Thanks for the answer.
I understand exactly the case. The reason I need to have a unique symbol is for WRDS (Compustat) they only make use of a unique symbol to retrieve data from companies. That unique code can be: Ticker symbol, GVkey, SIC, CIK and maybe some others, but not on the name. Therefore my suggestions was to take Ticker symbol list and match them, the list I have is from the FEC.gov of political contributions, however they only match it with their own ID and not one of those I mentiod. And therefore this was my solution.
But I agree with you're answer that it could be judgemental as some companies are already maybe non-listed and their symbol is now related to an other security of other company. As I have already figured some companies out, I think I will do the last party with Compustat database, than I take me one day or so, but its more reliable, however I think that most ticker symbols are used the same, there will be only a few different I guess.
biancocelesti (rep: 2) Jan 29, '21 at 7:25 pm
That's interesting. Thanks for explaining. Good luck!
Variatus (rep: 4889) Jan 29, '21 at 8:20 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login