0

I have the following formula to pull in the name of a stock based on the given Ticker Symbol...

=SUBSTITUTE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A2&"&f=n"),CHAR(34),"")

It returns this when I have the corresponding ticker symbol plugged in:

Hewlett-Packard Company Common

I would like to be able to get rid of the word "Common" if that is possible.

5
  • Does 'Common' always show up at the end of the name?
    – hBy2Py
    Jul 2, 2015 at 18:44
  • Not always. It depends on how yahoo fiance has it labeled. I have another one for IBM and it just shows International Business Machines. However, the one of HP shows Common and on for EMC shows EMC Corporation Common Stock. Jul 2, 2015 at 18:45
  • Stripping one instance of "Common" out of each name shouldn't be that hard. However, it will be nearly impossible to do it using formulas without also targeting any "Common" that appears in the company name, and it will be really hard to strip more than one instance of "Common," or to only strip, say, the last occurrence of "Common" in the string. In the end, a custom VBA function using regular expressions will be the most robust approach.
    – hBy2Py
    Jul 2, 2015 at 18:48
  • Okay that makes sense... Would it be possible if the four companies I am using do not have common anywhere in their name? I only need it for 2 of these 4 companies and I won't be adding any others. Jul 2, 2015 at 18:57
  • Ah, if you've got a constrained set of company names, and none of them contain "Common", sure, piece of cake.
    – hBy2Py
    Jul 2, 2015 at 19:02

1 Answer 1

2

How about:

=SUBSTITUTE(SUBSTITUTE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A2&"&f=n"),CHAR(34),""),"Common","")
1
  • Can't beat this.
    – hBy2Py
    Jul 2, 2015 at 19:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .