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.