Calling Yahoo Finance API for Stock Data (Excel & VBA)

8.8 min read|Last Updated: February 11th, 2024|Categories: excel|

Using the Yahoo Finance API in Excel with VBA can provide real-time stock data, enriching your spreadsheets with dynamic information. By leveraging this tool, you can automate the retrieval of stock data directly into your Excel worksheets. In this guide, we’ll explore how to call the free Yahoo Finance API using Excel and VBA, giving you the tools to enhance your financial models and reports.

How to call the free Yahoo Finance API to get Stock Data

Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explains how to get stock data from Yahoo Finance using Excel & VBA. If you’re interested in a custom solution for your Excel file, please contact our Excel expert team for a free quote.

In this example, we want to obtain the following Stock info of Google, Facebook and Microsoft.

  • Name of company
  • Last trade date
  • Last trade (price only)
  • Dividend Yield
  • P/E Ratio

Important Update (2020)

Yahoo Finance API is no longer available. The VBA code, explained in this blog, can no longer connect to Yahoo Finance API and is not working.

Pre-req
Make sure Microsoft WinHTTP Services are enable. To do so , open Visual Basic Editor of your xlsm file. Click Tools >> References. And select Microsoft WinHTTP Services.

Development Process – Step 1

Range A2 to be taken the head cell. Type “GOOG” in range A3, “FB” in A4 and “MSFT” in range A5. Type “n” in range B3, “d1” in range C3, “l1” in D3, “y” in E3 and “r” in F3. A sample shown below.

Step 2

Open Microsoft Visual Basic for Applications on your Excel. Copy the code and paste it on your sheet’s VBA area.

Option Explicit

' Example: Requesting Stock info of GOOG, FB and MSFT.
'
' In this example, we want to obtain the following Stock info
' - Name of company
' - Last trade date
' - Last trade (price only)
' - Dividend Yield
' - P/E Ratio
'
' Range A2 to be taken the head cell. Type "GOOG" in range A3,
' "FB" in A4 and "MSFT" in range A5. Type "n" in range B3, "d1"
' in range C3, "l1" in D3, "y" in E3 and "r" in F3.
'
' We use the following URL for getting Stock Quote from Yahoo
' URL: http://finance.yahoo.com/d/quotes.csv?s=FB+MSFT&f=snd1l1yr
'
' The CSV file data looks like the following
'
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' "MSFT","Microsoft Corpora","9/4/2013",31.24,2.89,12.36
'
' The columns in CSV file is separated by comma. Split function
' is used to split the data on each line of CSV file at every
' occurrence of comma. However, some commas in the CSV file are
' part of a string, not separating the columns. For example
' consider the following line,
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' The second comma is not a column separator. But it's part
' of the company's name "Facebook, Inc."
' This program will identify which comma is a column separator
' and which is part of a string

' ************
' Written by http://www.msexcelvancouver.com
' Thanks to http://www.youtube.com/watch?v=iSlBE3CWg5Q

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

Dim head As Range
Set head = Range("A2")

Dim i As Integer
Dim Symbols As String: Symbols = ""
Dim SpecialTags As String: SpecialTags = ""
Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://download.finance.yahoo.com/d/quotes.csv?s="
Dim rng As Range
Dim cell As Range

' Get the Stock Symbols
Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
Symbols = Symbols & cell.Value & "+"
Next cell
Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'

' Get the Special Tags
Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
SpecialTags = SpecialTags & cell.Value
Next

' Put the desciption/name of each tag in the cell above it
Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
For Each cell In rng
cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
Next

Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags

Call Print_CSV(Yahoo_Finance_URL, head)

End Sub

' Download the CSV file and Print it in the cells
Sub Print_CSV(URL As String, head As Range)

' Get the CSV file from Yahoo Finance
Dim Http As New winhttprequest
Http.Open "GET", URL, False
Http.send

Dim Resp As String: Resp = Http.responsetext
Dim Lines As Variant: Lines = Split(Resp, vbLf)
Dim sLine As Variant
Dim Values As Variant
Dim Value As Variant

Dim i As Integer: i = 1
Dim next_column As Boolean: next_column = True
Dim col As Integer
Dim tmp As String

'