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 11 to 20 of 48Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Web Page format changed
Rate this:
(3/5 from 1 vote)
The Yahoo Finance web page layout had changed since the original post was made.  This is a common problem with web queries whereby the extraction of certain areas of the web page using the ".WebTables" parameter can cause the coded web query to fail when changes are made to the web site layout structure.

The VBA code in the original post has been modified to now work correctly with the layout of the web page. 

Should the layout change again, the solution is to record a macro by creating a new web query with the full URL and parameters and select the area in the web page for the historical data (Data > Import External Data > New Web Query).  This will populate the ".WebTables" parameter with the correct value which can then be replaced in the original code.

 Excel Business Forums Administrator
 Posted by on
Confused
Downloading Market Data
Rate this:
(3/5 from 1 vote)

Thanks alot that makes sense.

Oz
 cano geel
 Posted by on
Confused
Passing multiple stocks once
Rate this:
(3/5 from 1 vote)
How do you pass more than one stock as argument to above code.
 Otherwise, the code is working fine. Also I am seeing no matter daily monthly or yearly. I can only get 3 months worth of data. Do i need to change my excel setting? I read the code no where to modify.
 Thanks
 Dalmar
 Posted by on
Happy
A free solution to download
Rate this:
(4/5 from 2 votes)
These last two issues are indeed interesting ones:
  1. Downloading more than one security price at the same time, and
  2. Getting around the paging limit from the Yahoo Finance web page.
But, we like a challenge so we developed a VBA program that uses the original code and overcomes these issues. 

We liked it so much that we decided to host it and it can be downloaded freely here.

For interest, the first issue was not so straight forward.  This is because the dates differ for each security due to availability, dividends etc. To overcome this, we first calculated the dates required and then matched the results of each web query to the dates. We even took into account public holidays.

To overcome the second issue, we broke the date range into chunks and execute the web query multiple times to get the full date range specified.

The VBA code is open and commented.  Any feedback is much appreciated.

Enjoy.


 Excel Business Forums Administrator
 Posted by on
Confused
Re Downloading Market Data
Rate this:
(3/5 from 1 vote)
I don't see any solution to the problem dcfvaxman wrote about 20-Dec-08.  I've got an EXCEL macro that loads stock histories from Yahoo Finance and option quotes from Prophet.com but times out after approximately 50 stocks.  As a work around I put  a pause in the macro so it stops after every 50 stocks allowing me to go into Internet Explorer and do an options->Delete All and the continue the macro.  It would be nice if this wasn't necessary.  Because it pauses every 50 stocks I can't do anything else while the macro is running.  It takes a bit less than an hour for 300 stocks.  I had written a similar macro back in 2001 using Office 2000 which did not have this problem. 
 Posted by on
Confused
Downloading Market Data
Rate this:
(3/5 from 1 vote)

Thanks Admintrators and those who contributed making it happen. The macro works fine and does just what I wanted. Yes it takes little while going through looping each stock at the time, but hey, it is awsome to sit back with your arms folded behind your kneck and watch macro at work.

You guys rock!


I got another problem bit more complex than this, I will let you know next time if it is even doable.

Regards,
 cano geel
 Posted by on
Oops
Clear out internet cache with VBA
Rate this:
(3/5 from 1 vote)
The Internet cache is always a problem with web queries run from Excel.  The problem is not just with Internet Explorer but also with any other browser such as Firefox.  The solution is to clear out the cache from the VBA code before the process or loop within the process which can be done using a shell command.  The VBA code is:

VBA Code:
Sub Clear_Internet_Cache()
    Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255"
End Sub
 Excel Business Forums Administrator
 Posted by on
Confused
Newbie
Rate this:
(3/5 from 1 vote)
Hi
You gave wonderful information which is useful for me... Thanks for sharing information..

 [url=http://www.checkthatcompany.co.uk/]credit check company[/url]
 Posted by on
Confused
So close...yet not there.
Rate this:
(3/5 from 1 vote)
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!

 Posted by on
Happy
Source for Excel Option Greeks
Rate this:
(3/5 from 1 vote)
I can't remember where I found it, but there's an excel spreadsheet that you can download.  I find it very useful for figuring out the greeks on real as well as imagined options.  It's especially good when you want to predict an option price by varying the underlying price.  

http://niftyprediction.blogspot.com/2009/06/options-greeks-calculator-excel.html

If you just want to look at the greeks outside of Excel, go to the Option Industry Council site, http://www.optionseducation.org/
Hope this stuff helps.
BodyworkeR

 Posted by on
 Displaying page 2 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

© Business Spreadsheets   Legal   Follow us: