WARNING!!!

As of Q3 2017, the Yahoo! Finance capabilities in Excel have been disabled (by Yahoo!, after Verizon bought them).

Luckily, more recent versions of Excel now have built-in features that can replace the need to pull data from Yahoo! Finance. Check out these articles for more information:

• Add Real-Time Stock Prices And Metrics To Excel
• Pull Historical Stock Price Data in Excel
• Determine YTD Stock Growth With Excel’s STOCKHISTORY Function

I’ve been playing around with building some Stock Tracking tools for Excel and I’ve learned a lot about how to query data from Yahoo! Finances API to get it into Excel while it’s all fresh in my memory, I figured I would take some time and document some of the techniques I’ve been using in my spreadsheets.

Breakdown Of The Yahoo! Finance Query URL

The premise behind how you query stock information in your web browser ultimately boils down to the URL address.  Here is the standard URL address formula with two inputs (the Ticker Symbol and the Data Type).

https://download.finance.yahoo.com/d/quotes.csv?s= [Ticker Symbol] &f= [Data Type]

A Few Examples

To get this data from a web address into an Excel spreadsheet, we can use the WEBSERVICE() function. I’ll also wrap a NUMBERVALUE() function around to convert the import text from Yahoo! Finance into a numerical value we can use inside Excel.

Example 1: Pull The “Current Stock Price” for Microsoft’s Stock

=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1"))

Example 2: Pull The “Current Dividend” for Microsoft’s Stock

=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=d"))

Example 3: Pull “% change From 52 Week Low” for Microsoft

This one is a little bit trickier because the results from the url have a plus sign or a negative sign in front of the resulting % change which the NUMBERVALUE() function does not like. In the following formula, I am taking the result from Yahoo! finance and removing the first character (ie +/-) from the result.

=NUMBERVALUE(REPLACE(WEBSERVICE("https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=j6"),1,1,""))

Example 4: Link your Ticker Symbols to a Spreadsheet Cell

If you want to use the same formula for a bunch of different ticker symbols, you can link your formula using a cell reference. Here is the same formula from Example 1, however it is now point to a ticker symbol in cell A2.

=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/quotes.csv?s=" & A2 & "&f=l1"))

Data Type Tables

Yahoo! has a whole collection of data points you can pull about a specific ticker symbol. Here is a list of the variables and what they pull in:

Refreshing The Formulas

As you might imagine, if you are pulling realtime data from Yahoo! Finance, Excel is not going to recalculate your WEBSERVICE() formulas every second. Therefore, you will need to manually recalculate your sheet (keyboard shortcut F9) in order to refresh your formulas.

VBA Macro To Pull Historical Data

ALERT!

As of May 2017, it has been reported that Yahoo! has terminated the iChart platform which allowed us to pull historical information into Excel for free. I found this response from a Yahoo employed moderator in their forums:

I’m not sure what the end goal is for this move, maybe they will begin offerring a paid service in the future. Due to this recent event, the below VBA code to pull historical data from Yahoo! finance will not long work unless they bring back the old API method.

Yahoo! Finance has a ton of historical data for stocks and harnessing VBA’s automating power to pull this data in seconds can allow you to perform some unbelievable tailor-made analysis. Let’s take this very simple setup in Excel where we have the Stock Ticker in Cell B2 and the Start/End date range you want to pull in Cell C4 and Cell F4 respectively. Also, the Excel Table is named “StockTable”. 

See if you can follow along with the VBA as it pulls data directly from Yahoo! Finance and places it into Excel. There is a little extra clean up you need to do since the query is pulled into excel as a CSV (comma separated values) data set.

ALSO NOTE: Through some testing, it seems you will need to have at least one extra column in your table (ie the black section in the image above) in order to retain the Table Object after the VBA performs the TextToColumns function. You can just leave this extra column blank if you do not need to perform any extra calculations.

Sub YahooFinanceQuery()
'PURPOSE: Pull Historical Stock Data From Yahoo! Finance
'SOURCE: www.TheSpreadsheetGuru.com
 
Dim EndDate As Date
Dim StartDate As Date
Dim StockTicker As String
Dim QueryURL As String
Dim QueryDataRange As Range
Dim QueryLocation As Range
Dim tbl As ListObject
 
'Optimize Code
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
     
'Query Parameters
  StartDate = Range("C4").Value
  EndDate = Range("F4").Value
  StockTicker = Range("B2").Value
 
'Store Table Object to a Variable
  Set tbl = ActiveSheet.ListObjects("StockTable")
 
'Determine Where To Place Query (Column Prior To Table)
  Set QueryLocation = tbl.Range(1, 1).Offset(0, -1)
  QueryLocation.EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Set QueryLocation = QueryLocation.Offset(0, -1)
 
'Delete all table rows except first row
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
  
'Create Web Address To Query Historic Stock Data
  QueryURL = "http://ichart.finance.yahoo.com/table.csv?s=" & StockTicker
  QueryURL = QueryURL & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
      "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
      Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & QueryLocation & "&q=q&y=0&z=" & _
      StockTicker & "&x=.csv"
  
'Pull Query Into Excel
  With ActiveSheet.QueryTables.Add(Connection:="URL;" & QueryURL, Destination:=QueryLocation)
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
  End With
   
'Determine Stock Data Range
  ActiveSheet.UsedRange
  Set QueryDataRange = ActiveSheet.Range(QueryLocation, Cells(ActiveSheet.Cells _
    (ActiveSheet.Rows.Count, QueryLocation.Column).End(xlUp).Row - 1, QueryLocation.Column))
 
'Sort Data in Ascending Order
  ActiveSheet.Sort.SortFields.Clear
  
  ActiveSheet.Sort.SortFields.Add Key:=QueryDataRange, _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
  With ActiveSheet.Sort
    .SetRange QueryDataRange
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
'Delimit Stock Data into Table
  QueryDataRange.TextToColumns Destination:=QueryLocation.Offset(0, 2), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, other:=False
 
'Delete Yahoo! Finance Stock Query
  QueryLocation.EntireColumn.Delete
 
'Optimize Code
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
 
End Sub

Get The VBA Example File

If you need a little head start or are not comfortable with VBA quite yet, I have put together a great little example Excel file with the examples covered in this article.

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Build A United States Dashboard Map Chart In Excel

Map Charts In Excel Are Limited… Let’s face it, Excel’s built-in Map Chart feature seemingly was thrown together overnight. Its...

How To Use Excel’s XLOOKUP Function [Guide 2022]

On August 28th, 2019, Microsoft unveiled a new Excel function with the intent of making 3 beloved and rivaled functions...

How To Calculate A CAGR Formula In Excel (Correctly)

Interactive Excel CAGR Calculator Tool If you are looking for a way to quickly calculate a CAGR for a PowerPoint...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.