# WARNING!!!

As of Q3 2017, the Yahoo! Finance capabilities in Excel have been disabled (by Yahoo!, aka Verizon). I have created a feature request directly to the Microsoft Excel team to recoup this lost functionality. The best way for them to notice this plea to get stock price functionality back into Excel is to upvote my posted request. If it gets enough votes, Microsoft will take a hard look and has already had a great track record of addressing the most popular requests.

Please take a minute and upvote Excel Uservoice: Bring Back Stock Prices

The Excel community thanks you :)

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

### 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

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

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.

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.

### 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
bBid
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

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
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
b6 Bid 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
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
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

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

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

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

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveSheet.Sort
.SetRange QueryDataRange
.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

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.

## 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 :)