Financial market data can be downloaded into Excel for free by using Excel web queries. The market data can be automatically extracted from financial web sites that provide such data for free. This example uses Yahoo Finance; however similar approaches could be made with MSN Finance, Google Finance or other such web sites.
Download a free Excel program that uses a variation of the code below here.
Update - 07 January 2012: Given the high popularity of this solution, we have developed an updated version that resolves the issues raised in the posts here and adds the following features:
Works with all versions of Excel and OS including Excel 2011 for Mac.
Symbol look up with click to add to list of securities and indices for data download.
Progress bar shows status of data downloading.
Technical analysis sheet allows charting and calculation of return metrics for selected downloaded data.
Optimized open source code with detailed comment documentation.
Some have asked why this is free. For several reasons but the most important is that we believe that market data is public information and should be freely available to all.
This example will download both real-time quotes and historical price data for a specified stock or index ticker symbol. Suppose we have the following Excel spreadsheet.
Excel Spreadsheet:
A
B
C
D
E
1
2
Start Date:
01/01/10
Symbol:
MSFT
3
End Date:
01/01/12
Frequency:
Daily
4
Cells B2 and B3 specify the start and end dates for the historical data. Cell E2 specifies the stock or index ticker symbol. Cell E3 specifies the historical data frequency. This can be either Daily, Weekly, or Monthly or even D, W or M as only the first letter is used in the web query.
The following VBA macro code can then be loaded through the VBA editor and executed from the spreadsheet to return the data.
VBA Code:
Sub Download_Data() Application.ScreenUpdating = False Dim symb As String Dim startdate As Date Dim enddate As Date Dim a, b, c, d, e, f As Integer Dim g As String
symb = Range("E2").Value startdate = Range("B2").Value enddate = Range("B3").Value a = Month(startdate) - 1 b = Day(startdate) c = Year(startdate) d = Month(enddate) - 1 e = Day(enddate) f = Year(enddate) g = Lcase(Left(Range("E3").Value, 1))
'Historical Data With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q/hp?s=" & symb & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=" & g _ , Destination:=Range("A12")) .Name = "Quote: " & symb .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "15" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With
Application.ScreenUpdating = True End Sub
By simply changing the input cells, the same macro can be run to extract new data. This is a basic example and there are several enhancements which could be made to suit requirements such as:
Loop through the historical data query to page through data on Yahoo back to the start date. This could be done by going to the bottom observation date, resetting the end date to this date, rerun and append new data to the bottom until the reset end date is less than or equal to the start date.
Add extra code at the end to extract data and insert into other trading system or analytical applications in Excel.
Format and clean up the data such as removing dividends from the historical data or removing the date text from the last price real-time quote.
Thank you so much for this post. It's almost what I'm looking for... except for two minor (or major) differences.... 1) I need quotes on the OPTION, not the STOCK. So, for instance, the Feb $113 Call on SPY has an opra code .SPYBI. I need the Bid, Ask and Mark on that option to stream over into excel. Don't care too much for a lot of historical data, just delayed-20 minutes would be perfect. 2) I also need the option greeks (especially delta) on .SPYBI I couldn't find option pricing on Yahoo! Finance, and while MSN-Money gave me option pricing, I couldn't find delta's there. I'm hoping the wizards here have an answer to my dilemma... Thanks!
The data downloader hangs after it downloads about 10 years worth of daily data. Due to this I cannot populate the spreadsheet with several tickers. I tried clearing the Internet cache, but it does not help. Please advice. Thank you. Yuri
Hi Singapore National Environmental Agency posts its most disaggregated data on a daily basis at this website: [http://app2.nea.gov.sg/psi.aspx] I see how to do a webquery manually for this, but I want it to do this automatically everyday for the next few years. Even if I don't open Excel everyday. How is this possible? I'm a Stata user, not much of an Excel user. So I don't know Excel code. Can someone help?
No matter what I set the date range to, my results only show 3 months worth of data. Is there a way to fix this so it shows all the data requested? Thanks for your help, it is much appreciated.
I used this code many times without a problem but now when I run the macro it pulls the wrong dates and no data is pulled (even for the wrong dates). Its almost like Yahoo changed their format because this never happened before and I changed nothing. Is anybody else having this problem and/or know how to fix it? Thanks in advance.
Great spreadsheet. I was wondering if its possible to create a duplucate worsheet in the workbook. Id like to have various sectors with each having its own sheet. I tried to simply copy+paste and copy/move the whole sheet and it doesnt work very well. It wont download the symbols I input. Thanks
I have been using excel 2007 to import data from any web querry. Besides this, i also have been using livevol tools called market data excel which is an easy tool to convert the huge number of data to your excel sheet. Therefor i doesn't need any queries of this type.
