Use Yahoo! Finance To Pull Stock Information Into Excel

Stock Price Data From Yahoo! Finance With Microsoft Excel Spreadsheets

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

http://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("http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1"))

Example 2: Pull The "Current Dividend" for Microsoft's Stock

=NUMBERVALUE(WEBSERVICE("http://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("http://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("http://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

Averages

VariableDescription
aAsk
bBid
b2Ask (Real-time)
b3Bid (Real-time)
pPrevious Close
oOpen
c1 Change
c Change & Percent Change
c6 Change (Real-time)
k2 Change Percent (Real-time)
p2 Change in Percent
d1 Last Trade Date
d2 Trade Date
t1 Last Trade Time

Dividends

VariableDescription
yDividend Yield
dDividend per Share
r1Dividend Pay Date
qEx-Dividend Date
VariableDescription
c8 After Hours Change (Real-time)
c3 Commission
g Day’s Low
h Day’s High
k1 Last Trade (Real-time) With Time
l Last Trade (With Time)
l1 Last Trade (Price Only)
t8 1-Year Target Price
m5 Change From 200 Day Moving Average
m6 % Change From 200 Day Moving Average
m7 Change From 50 Day Moving Average
m8 % Change From 50 Day Moving Average
m3 50-Day Moving Average
m4 200-Day Moving Average

Volume

VariableDescription
v Volume
a5 Ask Size
b6 Bid Size
k3 Last Trade Size
a2 Average Daily Volume

52-Week Pricing

Ticker Related Information

VariableDescription
k 52-Week High
j 52-Week Low
j5 Change From 52-Week Low
k4 Change From 52-Week High
j6 Percent Change From 52-Week Low
k5 Percent Change From 52-Week High
w 52-Week Range
g1 Holdings Gain Percent
g3 Annualized Gain
g4 Holdings Gain
g5 Holdings Gain Percent (Real-time)
g6 Holdings Gain (Real-time)
VariableDescription
i More Info
j1 Market Capitalization
j3 Market Cap (Real-time)
f6 Float Shares
n Name
n4 Notes
s Symbol
s1 Shares Owned
x Stock Exchange
j2 Shares Outstanding

Financial Ratios

VariableDescription
e Earnings per Share
e7 EPS Estimate Current Year
e8 EPS Estimate Next Year
e9 EPS Estimate Next Quarter
b4 Book Value
j4 EBITDA
p5 Price / Sales
p6 Price / Book
r P/E Ratio
r2 P/E Ratio (Real-time)
r5 PEG Ratio
r6 Price / EPS Estimate Current Year
r7 Price / EPS Estimate Next Year
s7 Short Ratio

Miscellaneous

VariableDescription
t7 Ticker Trend
t6 Trade Links
i5 Order Book (Realtime)
l2 High Limit
l3 Low Limit
v1 Holdings Value
v7 Holdings Value (Realtime)
s6 Revenue
w1 Day’s Value Change
w4 Day’s Value Change (Realtime)
p1 Price Paid
m Day’s Range
m2 Day’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". 

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.

As always, in order to download this or any example file from this website, you will need to be a subscriber of my free tips newsletter.  If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)