Wednesday, November 10, 2010

Acquiring stock information in Excel from Yahoo Finance

Yahoo Finance has a nice feature where you can request stock information in csv format. For instance, want the current earnings per share value for Bank of America? You can get that in a pretty raw form suited for automation.

http://finance.yahoo.com/d/quotes.csv?s=BAC&f=e

There you go. Click that, save it, and open it with Excel, and up will come the information that you asked for.

You can request multiple pieces of information at a time, and a number of different data fields for them. The list is here: http://www.gummy-stuff.org/Yahoo-data.htm

Let's take this a step further. We can make Excel automatically load this information from the Internet. Not only will it eliminate the need to go look up this information and fill it in, but it will reload it whenever the worksheet is refreshed.

To do this, we're going to write a VBA function that takes a ticker symbol and the field we are looking for.


Public Function GetQuote(ByVal ticker As String, ByVal field As String)
'Valid field options can be found at http://www.gummy-stuff.org/Yahoo-data.htm
Dim objHttp As Object
Set objHttp = CreateObject("MSXML2.ServerXMLHTTP") 'Create an XMLHTTP object to access the web
Dim url As String
url = "http://finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=" & field 'Build our query consisting of our ticker symbol and the field we want
objHttp.Open "GET", url, False 'Open up our connection to Yahoo
objHttp.Send 'And get Yahoo's response

'Before returning, knock off the last two characters from the response--they are junk
GetQuote = Left(objHttp.ResponseText, Len(objHttp.ResponseText) - 2)
End Function


For those of you new to VBA, press Alt-F11 to bring up the VBA window. Right-click in the Project window, and insert a new Module. Then, just copy and paste the above code into the code window.



Now that we have our function, we can make use of it. Returning to our worksheet, we can start entering our formula. If you have Excel 2010 or later, Excel should fill in the name of the function you have added.



Fill in the ticker symbol and field you are looking up. In this example, I'm using the function to create a matrix of values with the ticker symbols along the left and the field symbols along the top.




Excel will go out to Yahoo and get the requested data.




In this example, we can drag the cell border to fill the values down and over.

5 comments:

  1. That's some good work there! I'm using your code as we speak. I wrote an Excel spreadsheet to download historical stock quotes from Yahoo. You can get it here

    ReplyDelete
  2. Thats really very nice tutorial. I need a vba macro, that retrieve data from the url in excel. column a (url of the website) column b, c should have retrieved data. can you please write this macro for me. I will be very thankful to you. gmsyial2011@gmail.com

    ReplyDelete
  3. Using this works for me. Its great.
    http://marketxls.com/stock-quotes-in-excel/

    ReplyDelete
  4. useful information on topics that plenty are interested on for this wonderful post.Admiring the time and effort you put into your b!.. Milton Barbarosh

    ReplyDelete
  5. I use MarketXLS for this and it's great.
    http://marketxls.com/stock-quotes-in-excel/

    ReplyDelete