Home > Forum Home > Building Market Trading Systems > Downloading Market Data For Free Share

Downloading Market Data For Free

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


Forum TopicPost Reply Login

Downloading Market Data For Free

Rate this:
(3.8/5 from 6 votes)
HappyFinancial 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.
 Excel Business Forums Administrator
 Posted by on
 
Replies - Displaying 31 to 40 of 48Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Rate this:
(3/5 from 1 vote)
We have just updated the Download Market Data program with the following features:
  • Fix for date formats based on regional settings so that data is returned regardless of the user regional date settings.
  • Ticker symbol Look up function and automatic applying of found symbols.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
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
 Posted by on
Shocked
Rate this:
(3/5 from 1 vote)
The VBA code references the sheet for the downloaded data by name.  You need to create a variable for ActiveSheet.Name and use this in order for the code to run on any new copies of the sheet added to the workbook.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
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.

http://www.livevol.com/options-excel.html
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
whoever made this spreadsheet, i love you
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
I have been using this spreadsheet and it has been great! However, I have come upon an issue when running daily close data for S&P 500 (^GSPC) with a start date of 11/1/2010 to 11/25/2010 (I have tried various dates in this range) with an end date of today, 12/7/2011. I have noticed that a beginning chunk of data from Nov 2010 is missing. The dates show but the close data does not appear. I do not receive any errors, the data just does not appear. I have been trying to figure this out with no luck. Can anyone help? Has anyone else ran into a similar issue? I tried deleting temporary internet files and this has not helped...
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
I just tested with the ^GSPC from 11/1/2010 to 11/25/2010 Daily and it ran fine with data for both Close and Adjusted Close.

Once run, try to click the RUN bitton in the Query sheet to see what is returned from the web query itself.  There should be data in cells A15 to G32.  If not, then there could be an issue with data format and regional settings, or version of Excel/OS/Browser.  If you can communicate these then we can look further into it.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
My apologies, I may not have been clear. I am able to run 11/1/2010 to 11/25/2010 on both the Data and Query tabs with no issue. However, I am unable to run 11/1/2010 to 12/7/2011. When I run the file in this instance, the close data for 11/1/2010 and 11/2/2010 is missing. As I change the start date to other dates in the month of November 2010, the amount of cells with no close data showing varies. However, when I run 12/1/2010 to 12/7/2011, all data appears with no issues. Appreciate the prompt response!
 Posted by on
Surprised
Rate this:
(3/5 from 1 vote)
Indeed, I replicated the same behavior.  On debugging the code I noticed that the first call returns data starting at the 3 Nov 2010 even through the request is for starting at 1 Nov 2010. As a workaround I chose 10/1/2010 as the start date to make sure that data was retrieved for the beginning of November.  In any case, we will look into whether the source page has changed in any way (as this happens from time to time) and update the code accordingly.
 Excel Business Forums Administrator
 Posted by on
Happy
Rate this:
(3/5 from 1 vote)
An improved version of Download Market Data solution for Excel is now available in the download link on the main post.  New features include:
  • Functions independent of operating system and version of Excel and is now compatible with Excel 2004 and 2011 for Mac.
  • Improved stability and matching of dates.
  • Progress bar shows the overall status of the download execution.
  • An analysis sheet has been added to run reports on return, risk, correlation and performance against indices and other securities.
  • Coding has been optimized for speed and efficiency.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 4 of 5 

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

Solutions: Download Market Data Download Stock Quotes Complete Trading System Real Time Stock Quotes Options Trading
Categories: Market Data Access