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
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.
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:
|c||Change & Percent Change|
|k2||Change Percent (Real-time)|
|p2||Change in Percent|
|d1||Last Trade Date|
|t1||Last Trade Time|
|d||Dividend per Share|
|r1||Dividend Pay Date|
|c8||After Hours Change (Real-time)|
|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|
|k3||Last Trade Size|
|a2||Average Daily Volume|
Ticker Related Information
|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|
|g1||Holdings Gain Percent|
|g5||Holdings Gain Percent (Real-time)|
|g6||Holdings Gain (Real-time)|
|j3||Market Cap (Real-time)|
|e||Earnings per Share|
|e7||EPS Estimate Current Year|
|e8||EPS Estimate Next Year|
|e9||EPS Estimate Next Quarter|
|p5||Price / Sales|
|p6||Price / Book|
|r2||P/E Ratio (Real-time)|
|r6||Price / EPS Estimate Current Year|
|r7||Price / EPS Estimate Next Year|
|i5||Order Book (Real-time)|
|v7||Holdings Value (Real-time)|
|w1||Day’s Value Change|
|w4||Day’s Value Change (Real-time)|
|m2||Day’s Range (Real-time)|
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
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.
'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
Application.ScreenUpdating = False
Application.DisplayAlerts = False
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
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
'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
.SaveData = True
'Determine Stock Data Range
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.Add Key:=QueryDataRange, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
'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
Application.ScreenUpdating = True
Application.DisplayAlerts = True
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!