Using Yahoo Finance API in Excel

I was using Excel to model my retirement portfolio and wanted to get the stock prices from the web. Found a bunch of helpful links, which I will share below, but for a simple close price, I used a VBA function to pull the close price from Yahoo Finance API. Add it to the module, not class module, to use it from any excel sheet.

Option Explicit

Function StockQuote(ByVal ticker As String) As Double
Dim url As String
Dim closePrice As Double
Dim http As Object

    ' Compile the request URL with start date and end date
    url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=l1"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send

    StockQuote = http.responseText
    Set http = Nothing
End Function

You can read up on this API here: http://code.google.com/p/yahoo-finance-managed/wiki/csvQuotesDownload.

How to use

Use a function like any standard Excel function from any cell. Refer to another cell for the ticker value or pass a hardcoded string.

Excel1

Press enter and…

Excel1

Links to sources:

  1. API documentation on google code
  2. Video Tutorial by DontFretBred
  3. Using the Yahoo Finance API for CSV
  4. Detailed API documentation by Clif Ngan
Advertisements

3 thoughts on “Using Yahoo Finance API in Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s