×

Use Yahoo! Finance To Pull Stock Information Into Excel

By Chris Newman •  Updated: 12/04/16 •  9 min read
Stock Price Data From Yahoo! Finance With Microsoft Excel Spreadsheets

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:


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:

Stock Pricing

VariableDescription
aAsk
bBid
b2Ask (Real-time)
b3Bid (Real-time)
pPrevious Close
oOpen
c1Change
cChange & Percent Change
c6Change (Real-time)
k2Change Percent (Real-time)
p2Change in Percent
d1Last Trade Date
d2Trade Date
t1Last Trade Time

Averages

VariableDescription
c8After Hours Change (Real-time)
c3Commission
gDay’s Low
hDay’s High
k1Last Trade (Real-time) With Time
lLast Trade (With Time)
l1Last Trade (Price Only)
t81-Year Target Price
m5Change From 200 Day Moving Average
m6% Change From 200 Day Moving Average
m7Change From 50 Day Moving Average
m8% Change From 50 Day Moving Average
m350-Day Moving Average
m4200-Day Moving Average

Dividends

VariableDescription
yDividend Yield
dDividend per Share
r1Dividend Pay Date
qEx-Dividend Date

Volume

VariableDescription
vVolume
a5Ask Size
b6Bid Size
k3Last Trade Size
a2Average Daily Volume

52-Week Pricing

VariableDescription
k52-Week High
j52-Week Low
j5Change From 52-Week Low
k4Change From 52-Week High
j6Percent Change From 52-Week Low
k5Percent Change From 52-Week High
w52-Week Range
g1Holdings Gain Percent
g3Annualized Gain
g4Holdings Gain
g5Holdings Gain Percent (Real-time)
g6Holdings Gain (Real-time)

Ticker Related Information

VariableDescription
iMore Info
j1Market Capitalization
j3Market Cap (Real-time)
f6Float Shares
nName
n4Notes
sSymbol
s1Shares Owned
xStock Exchange
j2Shares Outstanding

Financial Ratios

VariableDescription
eEarnings per Share
e7EPS Estimate Current Year
e8EPS Estimate Next Year
e9EPS Estimate Next Quarter
b4Book Value
j4EBITDA
p5Price / Sales
p6Price / Book
rP/E Ratio
r2P/E Ratio (Real-time)
r5PEG Ratio
r6Price / EPS Estimate Current Year
r7Price / EPS Estimate Next Year
s7Short Ratio

Miscellaneous

VariableDescription
t7Ticker Trend
t6Trade Links
i5Order Book (Realtime)
l2High Limit
l3Low Limit
v1Holdings Value
v7Holdings Value (Realtime)
s6Revenue
w1Day’s Value Change
w4Day’s Value Change (Realtime)
p1Price Paid
mDay’s Range
m2Day’s Range (Realtime)
c4Currency

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:

Yahoo Finance Moderator Response

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". 

VBA Macro Pull Historical Stock Prices From Yahoo! Finance

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.

Keep Learning

Chris Newman

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.