How to: Obtain stock quotes from Yahoo finance (you can query them via Excel too)

You may also like...

40 Responses

  1. Paul says:

    Using Mac for Excel. Under the Data / Get External Data option menu there is no option for HTML.

    Any suggstions?

    • Juan Carlos says:

      Hi,

      I am using Excel 2011 for Mac and under Data there is a group called External Data Sources where the fourth option is HTML. If you have Office for Mac 2011 then you should have it available out of the box, but it definitively is there even if you have to activate the option manually. Please provide us with your version and a screen shot if possible, Thanks!

  2. Bob says:

    Try the following. Assume Column A has ticker symbols. Put the following wherever you want a quote to appear:

    =IF(A7″”,VALUE(CLEAN(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))),””)

    • Paul says:

      Thanks. Can’t get this to work, though.

      When I paste it into a cell, it gives me the exact formula in the cell (in other words, no calculation is taking place) and when I then change the reference cell of A7 to some other cell, it returns an error.

      Very strange.

      • Bob says:

        Somehow the copy-n-paste didn’t work right. Let me try again:

        =IF(A7″”,NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″)),””)

        Somehow the “notequal” () got dropped.

        Note that I also changed the formula slightly to use

        NUMBERVALUE(WEBSERVICE instead of
        VALUE(CLEAN(WEBSERVICE

        One less nested function call

        • Bob says:

          Well, that notequal symbol simply will not make it through. You want IF(A7 notequal “”, … but substitute the correct symbol for “notequal” — left angle bracket followed by right angle bracket.

          The heart of it is:

          NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))

          if A7 contains “MSFT” that becomes

          =NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=MSFT&f=l1”))

          I hope that works!

        • Paul says:

          Still not working.

          • Phil says:

            NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes?s=”&A7&”&f=l1″))

            This works fine if entered from the worksheet, but it gives an VBA syntax error if in a macro.
            What I’m entering is: Sheets(“Sheet1″).cells(7,”B”)=NUMBERVALUE(WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=”&A7&”&f=l1”))

    • Paul says:

      I would send you screen shots, but I don’t know how to attach it to this commenting system.

  3. Oscar Lai says:

    Thanks for the tips to capture stock quote Juan.

    Any one knows how to capture the option quotes from Yahoo Finance webpage? I am having a difficulty to figure out the table of different columns for option quote using either Excel or Google Spreadsheet.

    Any help would be greatly appreciated!

  4. Ryan says:

    Is there a way to pull figures from the “Key Statistics” section of the Yahoo finance? For example, the Quarterly Revenue Growth (YOY).

  5. Harry Todger says:

    Thanks Juan 🙂

  6. Spider says:

    Is there any tag to make it date specific? Let’s say I’m looking for the closing value on a specific date.

  7. Henry says:

    =WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=”&A4&”&f=r1”)

    This returns a dividend pay date of, for ex., “3/10/16”, including the double quotes. Excel doesn’t recognize it as a date so I can’t subtract it from another date. What must I do to have Excel recognize this as a valid date and display it in the requested format?
    Thanks for your help.

    • JCarlos says:

      I’m working with Excel in Spanish so the formula names are in Spanish (and I can’t recall the exact names in English but I hope this helps). Basically we are going to remove the first and last characters of the text you get and then convert it into a DateValue:

      =SERVICIOWEB(“http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=r1”)
      =LARGO(B3)
      =DERECHA(IZQUIERDA(B3,B4-2),B4-3)
      =FECHANUMERO(B5)

      Largo is Len if I’m not mistaken, Derecha is right, Izquierda is Left and FechaNumero is DateNumber/DateValue. Basically you get the length of the text and remove the quotes by only returning the text minus the first or last character:
      Formula Description (Result)
      =LEFT(A2, LEN(A2)-2)
      Removes last two characters from contents of A2

      =RIGHT(A3, LEN(A3)-8)
      Removes first 8 characters from A3

  8. Henry says:

    Thanks LokiKun. Your idea opened my mind to exploring Excel functions I wasn’t familiar with. The date returned from Yahoo actually looks like “m/d/yyyy” or “m/dd/yyyy” or “mm/d/yyyy” or “mm/dd/yyyy”. I ended up with:

    =VALUE(IF(LEN(H4)=11,MID(H4,2,8),IF(LEN(H4)=12,MID(H4,2,9),IF(LEN(H4)=13,MID(H4,2,10),””))))

  9. Nattapong says:

    Fail to get quote from SET (Stock Exchange of Thailand) via excel. for example
    =WEBSERVICE(“http://finance.yahoo.com/d/quotes.csv?s=CPF.BK&f=N”)

    Does this service support the information from SET or do I miss anything?

  10. T J Yuan says:

    It is no longer working from May 2017, what should we do?

  11. deios says:

    Unfortunately, Yahoo Finance no longer work, but I found nice and cheap alternative with examples how to use it with VBA – https://eodhistoricaldata.com/knowledgebase/excel-vba-example/.

  12. Henry says:

    =NUMBERVALUE(WEBSERVICE(“http://download.finance.yahoo.com/d/quotes.csv?s=”&A2&”&f=l1”))

    I was recently having problems getting stock prices for example, a ticker symbol in column A row 2, until I discovered I had to include “download.” as part of the IRL. Now it works fine.

    • gadcmc says:

      Hi Henry,

      Can you be more specific about what you did to get this working again?

      • Henry says:

        I mispoke, meant URL not IRL. Please notice I use download.finance.yahoo as opposed to finance.yahoo.

  13. Juan, thank you for this…I’ve been looking for a way to get automatic updates of current stock prices. ie: AAPL in my Excel SS. This great!

    Can you tell me if it is possible to get the same for option symbols? ie: AAPL170818C00155000. I’ve played with it some, but have not been able to get anything to work.

  14. Harz Delf says:

    I heard Yahoo Finance API had been discontinued.
    I use MarketXLS instead.

  15. Carlos says:

    Hi Juan,
    Thanks for the great info and help you have provided.
    It appears Yahoo Finance has discontinued this very good service. Is there another place we can get this info from that can be downloaded into an excel sheet?
    Carlos

  16. Cherry Maly says:

    I use MarketXLS for this. It really worked well for me.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: