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.
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...
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!
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
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.
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?
Thats 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
