«

»

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 maintaing 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 funcionality. 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.


 

 

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

Enhanced by Zemanta

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

  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!

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

Leave a Reply

%d bloggers like this: