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:
(4.5/5 from 4 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 1 to 10 of 48Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Rate this:
(2/5 from 2 votes)
is there an example to use this type of approach to get the ticker symbol for a stock?
 Posted by on
Confused
Rate this:
(3.2/5 from 4 votes)
Hi,
  I have been trying for a while to find something like this, but this code seems to have some issue.  When i try to run it on my computer, it only downloads the string with todays data, and it doesnt pull in historical data.  Another issue i have been having is when a macro similar to this run my web queries to pulll in this historical data, it crashes vba and gives the error "error web querry is still refreshing" 

I need a query such as this to pull in dynamickly the price history for the a list of stocks,

someone please help!!!

 Valentine
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
You could try to first record a macro that executes the web query that downloads the historical data.

After this you can modify the VBA code with snippets from the example above to automate the web query from cell values.
 Excel Business Forums Administrator
 Posted by on
AngryThats what i was thinking as well!!. Except that macro thats up there right now, doesnt work for my computer. DOes it work for you?? or can you help me figure out whats wrong?? It only shows one days data
 Valentine
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
you might want to take a look of the website below, it's all about using excel to extract stock market information, like historical data, Options data, daily quotes and others.

There are some ready-to-use script which you can download, view and modify to suit to your need.

[http://excelstock.googlepages.com/]

cheers
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
 To get right to the point here I've wirtten a routine, nearly exactly what you show below, to grab data from yahoo finance. I list a number of stock sybmols on a worksheet, the routine goes and gets finance data on those symbols from yahoo finance and places them onto a worksheet so I can compare the finanancials in a given industry grou, sector, or between stocks I'm looking at...it depends whats in the list. It works great except for a small little problem.

My querytables are not consistent in returning data. For example, if I create a list of stock symbols which are the same, say SLB, and list, oh say 30 of the same symbol, I may get 95% of my data.  I've created an error handler to grab the errors and you would think I would get the same error but it don't.  Sometimes it's error 438 or 1004 or something else.


This is driving me nuts. Each time I have the routine grab a list of stocks it returns the correct info but doesn't always grab all of it.  It's almost like the routine is grabbing the data too fast and Excel can't keep up.


Have you had experience with this or know a fix? If you would like me to submit my code I'm more than happy to do that too.

Thanks.

Paul  
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Paul, I have pretty much the same problem. I thought the same problem with the speed concerns and tried putting in a wait() command and it seems to make little difference. Have you solved this problem yet?

Thanks


 Posted by on
Sad
Rate this:
(3/5 from 1 vote)
I too am having the same problem.  My web query is the same as what's shown in the original post in the VBA code box.  When I let the code run without an "on error" statement it sometimes errs on the ".Refresh BackgroundQuery:=False" statement and when I choose debug and then resume it executes fine till a few queries down the road and it fails the same way.  When I add an on error statement and have it do a resume with or without a 5 second wait it often fails the second and third times.

I had a similar macro working in 7 years ago under office 2000 using a dial up modem.  Now that I have Office 2007 and DSL my upgraded version works very spottily.  If anyone has a solution please post it.   
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
For those of you that are trying to run a macro to retrieve stock quotes and are only getting a few returned before getting an error 1004, here is a work around.  The problem is that whenever a retrieval is completed, a file is created in your Temporary Internet Files directory.  I can only get between 17 and 20 of these files created before I start getting the macro error.  The name is in the form of hp?s=AAPL for example.  If you go in and delete these files and restart your macro, it will then run again until you get another 17 to 20 files built up.  You can also just go in and clear your Temporary Internet Files directory which will also work.  I would like to find a way to have the macro do this for me automatically.  This problem just started happening a few month ago.
 Posted by on
Confused
Rate this:
(2.5/5 from 2 votes)

Hi all,

I have two questions about the code.

1.  The code gives me Run-time Error "5". Invalid procedure or argument. The URL link is the right one, but I can not figure out where this error originates. Is Excel 2007 different from the 2003

2. I wanted to download the S&P 500 stocks for 10 years of period with this code by modifiying it something like a loop that goes from. Colum E2  to E501 etc where each cell in E has a ticker symbol. Do you think it is a good idea?

Thanks
 cano geel
 Posted by on
 Displaying page 1 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