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

How to obtain stock quotes in Excel

I was working on creating a spreadsheet to calculate profits and losses on options positions but didn’t know how to populate excel with stock quotes. Back in the day there used to be an interface to get stock quotes with the MSN Money site but it is not supported anymore. The idea behind this spreadsheet was to use the latest and historic quotes to calculate intrinsic values of options and P&L for expired ones. Kind of just trying to keep track of my record and evaluate performance. Of course the issue we face is that stock prices move every second and maintaining all that data manually is not worth it. After some research I tried using Google Finance to populate Excel to no avail but found Yahoo Finance supports this more easily.

In order to query stock quotes from yahoo all you need to do is use the following URL and provide the required parameters:

http://finance.yahoo.com/d/quotes.csv?s={Stock symbols separated by the ‘+’ character}&f={letters which represent a column of information you wish to be provided}

Keep in mind you need to use the Excel feature of “External data source” under the “Data section”. There you’ll find the option for “HTML” or similar. I use Excel for Mac in spanish so the names might be slightly different in the PC version.

So really there are 2 pieces of information you provide via the URL to get your stock quotes. First the list of stocks you wish to get a quote on separated by a ‘+’ sign like ‘GS+BAC+MSFT+AAPL‘ etc. The second part are the columns of information you wish to be provided like the last quoted price, the symbol, the full name, and much more. Below is a table with all the different columns you may request back as part of the csv:

a Ask a2 Average Daily Volume a5 Ask Size
b Bid b2 Ask (Real-time) b3 Bid (Real-time)
b4 Book Value b6 Bid Size c Change & Percent Change
c1 Change c3 Commission c6 Change (Real-time)
c8 After Hours Change (Real-time) d Dividend/Share d1 Last Trade Date
d2 Trade Date e Earnings/Share e1 Error Indication (returned for symbol changed / invalid)
e7 EPS Estimate Current Year e8 EPS Estimate Next Year e9 EPS Estimate Next Quarter
f6 Float Shares g Day’s Low h Day’s High
j 52-week Low k 52-week High g1 Holdings Gain Percent
g3 Annualized Gain g4 Holdings Gain g5 Holdings Gain Percent (Real-time)
g6 Holdings Gain (Real-time) i More Info i5 Order Book (Real-time)
j1 Market Capitalization j3 Market Cap (Real-time) j4 EBITDA
j5 Change From 52-week Low j6 Percent Change From 52-week Low k1 Last Trade (Real-time) With Time
k2 Change Percent (Real-time) k3 Last Trade Size k4 Change From 52-week High
k5 Percebt Change From 52-week High l Last Trade (With Time) l1 Last Trade (Price Only)
l2 High Limit l3 Low Limit m Day’s Range
m2 Day’s Range (Real-time) m3 50-day Moving Average m4 200-day Moving Average
m5 Change From 200-day Moving Average m6 Percent Change From 200-day Moving Average m7 Change From 50-day Moving Average
m8 Percent Change From 50-day Moving Average n Name n4 Notes
o Open p Previous Close p1 Price Paid
p2 Change in Percent p5 Price/Sales p6 Price/Book
q Ex-Dividend Date r P/E Ratio r1 Dividend Pay Date
r2 P/E Ratio (Real-time) r5 PEG Ratio r6 Price/EPS Estimate Current Year
r7 Price/EPS Estimate Next Year s Symbol s1 Shares Owned
s7 Short Ratio t1 Last Trade Time t6 Trade Links
t7 Ticker Trend t8 1 yr Target Price v Volume
v1 Holdings Value v7 Holdings Value (Real-time) w 52-week Range
w1 Day’s Value Change w4 Day’s Value Change (Real-time) x Stock Exchange
y Dividend Yield

In my case this was a very simple query in which all I needed was the stock symbol and the last quoted price so I can do a vlookup and plug in the required price in my calculations. The URL I used looks somewhat like this (you can click it to see the resulting data set):

http://finance.yahoo.com/d/quotes.csv?s=bac+bp+c+msft+aapl&f=sl1

This downloads a CSV into your browser but if you put it in as a file connection in excel you’re able to populate a sheet with your stock quotes and then just run a vlookup against it for any information you need.

 


In Excel you can find the import HTML menu under Data, in the External Data Sources section.

Kx.CloudIngenium.com - Excel 2011 Data HTLM Import Screenshot

If you can’t find that menu, you need to use the Web Query functionality. From what I see basically you need a Web Query Definition file even for the option presented above. If you need help setting up the definition file or need help accessing that option in Excel, check this post out: How to: Create Web query files for use with Excel for Mac. This should do the trick on a Excel 2011 for Mac.


 

Related:

If you want to query historical closing prices, you should check out this post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)

 

If you happen to know cooler tricks or suggestions let me know!

Enhanced by Zemanta

You may also like...

41 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.

    • 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.

  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

Your email address will not be published. Required fields are marked *

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