«

»

Aug 21

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

28 comments

Skip to comment form

  1. Paul

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

    Any suggstions?

    1. Juan Carlos

      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!

      1. Paul

        It’s version 14.4.9.

        Under Data, the option you are describing is not coming up.

        I have a screen shot, but I don’t know how to attach it to this reply.

        1. Juan Carlos

          I have version 14.5.1 so we’re basically on the same major version and not too far off. There is an alternative method for Excel for Mac, let me document that quickly.

        2. Juan Carlos

          Okay, basically you need to use something called WebQuery. I haven’t used it myself so let me know if it works for you. I’ll try it later on when I have some time. You can read about it here if you like: How to: Create Web query files for use with Excel for Mac

          1. Paul

            Yes. Tried it, couldn’t get that to work, either.

            Also, if I am tracking 100 stocks, wouldn’t I need to build a 100 Web Query files? Doesn’t seem practical.

            If you could send me a screen shot of your Excel menu I could call Microsoft and ask what is up and how to get that installed and functioning.

            Also, Mac 2015 is due out in a few months.

          2. Juan Carlos

            On the URL you can specify several quotes to get in one web call like this: http://finance.yahoo.com/d/quotes.csv?s=bac+bp+c+msft+aapl+ms+gs+ge+goog+anf+t+m+vod+tef+www&f=sl1. Would that work? Give me a few and I’ll upload a screenshot or email it to you so you can ask Microsoft about it. UPDATE: I added the screenshot to the article so you can copy it.

  2. Bob

    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″))),””)

    1. Paul

      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.

      1. Bob

        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

        1. Bob

          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!

        2. Paul

          Still not working.

    2. Paul

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

        1. mato

          Hey, how did you get that WEBSERVICE function into your Excel, please?
          When I try it in my Excel for Mac 2011, it’s simply not available. 🙁

          1. Andrew

            FYI – WEBSERVICE only works in later versions of Excel (at least after 2013). You can still do this with a VBA function – there are a number of web postings out there – for earlier versions of Mac / Windows Excel.

            Be careful, though, as I’ve heard there are some discrepancies between the VBA coding for Mac / Windows versions.

  3. Oscar Lai

    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!

    1. Juan Carlos

      I’ve had no luck with that, but you could give Yahoo Query Language a try: https://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=desc+yahoo.finance.oquote. Let me know if you get it to work, I only played with it for a few minutes but didn’t quite figure it out. Good luck!

  4. Ryan

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

    1. Juan Carlos

      From what I know, I haven’t seen an option for that (key stats into an CSV file). If you want to do .Net you could use https://code.google.com/p/yahoo-finance-managed/. Another option would be to use Yahoo! Query Language (https://developer.yahoo.com/yql/). There is an API called yahoo.finance.keystats (https://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=desc+yahoo.finance.keystats) that probably does what you’re looking for. Let us know how it goes!

  5. Harry Todger

    Thanks Juan 🙂

    1. Juan Carlos

      You’re welcome, glad it helped!

  6. Spider

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

    1. Juan Carlos

      It is an entire different call. I’ll add a post later on about it but basically here is an example: http://ichart.yahoo.com/table.csv?s=GE&a={date.addMonths(-2).format(‘MM’)}&b={date.today.format(‘dd’)}&c={date.today.format(‘yyyy’)}&d={date.addMonths(-1).format(‘MM’)}&e={date.today.format(‘dd’)}&f={date.today.format(‘yyyy’)}&g=d&ignore=.csv

      So, for AAPL dates ranging from the beginning of 2010 to the end of 2013, you would use: http://ichart.finance.yahoo.com/table.csv?s=AAPL&a=00&b=01&c=2010&d=11&e=31&f=2013&g=d&ignore=.csv

  7. Henry

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

    1. LokiKun

      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

    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),””))))

Leave a Reply

Clef two-factor authentication
%d bloggers like this: