Excel Help for Downloading Market Data For Free

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

'Real-time Quotes

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q/ecn?s=" & symb, Destination:=Range("A5"))
    .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 = """table1"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

'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.
Add a button at the top to launch the macro.

Downloading Market Data For Free

Excel Help for Downloading Market Data For Free in Building Market Trading Systems

Downloading Market Data For Free

Excel templates and solutions matched for Downloading Market Data For Free:

	Can't get it to work anymore Rate this: (3/5 from 1 vote) 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.
	Posted by stmi on 06 Apr 2012

	Changes to source web page layout Rate this: (3/5 from 1 vote) From time to time the code posted here fails to work due to changes to the page layout on Yahoo Finance. This has now been updated by replacing .WebTables = "16" with .WebTables = "15". The best solution is the download file as it does not rely on the layout but rather finds the price data wherever it resides on the page. There are also many other enhancements and advantages with this solution.
	Excel Business Forums Administrator
	Posted by Excel Helper on 06 Apr 2012

	Latest Market Data downloader Rate this: (3/5 from 1 vote) Thank you for latest file. I managed to download it but however I experinced the following and if it is possible can you help me rectify the issues or perhaps upload an improved file; On the Query tab the test historical data seem to be outputting stuff that I am not understanding
	Posted by algohead on 25 Apr 2012

	Query tab functionality Rate this: (3/5 from 1 vote) The Query tab is a holding space for data downloaded for both historical prices and real time quotes. This sheet downloads the entire page for each URL required and then extracts the data required for the Data sheet. The reason that the entire page is downloaded is to make this solution compatible with all versions of Excel and HTML rendering including those on the Macintosh operating system. Focus should be made on the Data sheet which is provides the downloaded data in a structured and continuous format.
	Excel Business Forums Administrator
	Posted by Excel Helper on 25 Apr 2012

	It does not work Rate this: (3/5 from 1 vote) Dear all, The Market Data Downloader does not work. When I push the Download buttom only the setted range of dates are appeared in the corresponding cells. There is no any data about prices, the corresponding cells are empty. If anybody has the working version of the programm please send me, because I don't know how to make a VBA project.
	hrach
	Posted by hrach on 28 Jun 2012

	Download working status Rate this: (3/5 from 1 vote) We've just tested the download and it is working fine for us. In the Query sheet, there are two buttons for testing the web query. Test these first to ensure that data is downloading. If not, then the issue is with the internet connection. If data is there then the issue is with the date matching.
	Excel Business Forums Administrator
	Posted by Excel Helper on 28 Jun 2012

	New updated version of the market data download template Rate this: (3/5 from 1 vote) We’ve updated this solution so with many new features, faster processing and full support for Excel 2004 and 2011 for Mac. We created a new thread for the new version here.
	Excel Business Forums Administrator
	Posted by Excel Helper on 19 Oct 2012

	Downloading Market Data Rate this: (4/5 from 2 votes) MarketXLS works for me just fine for this. It's great. http://marketxls.com/stock-quotes-in-excel/
	Posted by Harrymon12 on 02 Jun 2016