Excel - How to convert a list of company names to stock symbols?

[Bobalude]

I am trying to write a spreadsheet that will take a list of company names (column A) as the input, and display the stock symbol as the output (column B).

I have tried using the external data from web feature, but haven't been too successful in figuring out how this works. I am guessing I need something that will be able to:

1) Input the company name from column A into a web address/search string:
http://www.somesite.com/quote.aspx?something="[column A]"

2) Pulls data from a specific output box or section when the web search string returns the company symbol.

The question is how to get this working? thanks for any help

 

[Jiggz]

Not an expert with excel but this can be easily done with Access by using a lookup table. Maybe you ought to research on excel on how to use a lookup table.

 

[mayest]

It seems to me that you are going about this kind of backwards. There are so many opportunities to mistype the company name. You could get a list of all of the relevant company names and associated ticker symbols and then put them in a list in Excel. Then, I'd use a drop-down list control to choose a company name and then do a VLookup() to find the symbol.

Here's a good page that shows how to construct URLs to grab data from Yahoo! Finance. It requires ticker symbols, though. Yahoo! does have a ticker lookup that you might be able to tap into.

 

[Bobalude]

thanks for the replies. yeah, I have kind of gotten the grasp of how to pull data (prices/names/etc) using the ticker symbol from that link.

I'm looking at the holdings disclosed by some mutual funds, and the main thing is that I have a large list of company names (500+) but no symbols for each. From what I can tell, the listing almost always uses the same spelling/titling as yahoo finance (and others) for the company name, so that part is accurate. I am searching for a way to convert all those names to symbols so I can use those to pull more specific data for each stock. Still can't quite figure out how to get that working.

 

[jmonies]

Hi Bobalude, did you resolve this issue? i've been suffering this it too

 

[varioso]

In case someone is still struggling in this, I have found the below solution. Obviously, it won't work for alternatives and only works with exact matches. Tested with Google Spreadsheets. Must either work or have some equivalent in Excel and Open Office.

1. Place the custom list (e.g. fund holdings) in column A.
2. Then place a comparison list of company names in Col B and
3. corresponding stock symbols in Col C. You can find the latter list here: https://www.nasdaq.com/screening/company-list.aspx.
4. Select D1 or whatever is your first cell/field and apply the following formula:
=IF(ISNUMBER(MATCH(B1,A:A,0)),C1,0)
5. Drag the formula to the end of D.

This will run a search for entries on the Col B list against your original custom list and if found, will place the corresponding symbol.

For variations in spelling, you will need to do it manually. Or if anyone has a solution there, please share.

Hope it helps.