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 = "" & ticker & "&f=l1"

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

    StockQuote = http.responseText
    Set http = Nothing
End Function

You can read up on this API here:

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.


Press enter and…


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

3 thoughts on “Using Yahoo Finance API in Excel

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s