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 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.
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
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.
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
It would be interesting to know the system specifications that forces the download market data program to hang. The program processes the requested data in chunks so it is unlikely to be due to memory. If the version of Excel is below 2007, then more than 65,000 rows of data will cause a problem but this should be presented as a run-time error.
In the Downloaded data, choose "Close" price to get the closing price unadjusted for dividends. The "Adjusted Closing Price" accounts for share splits, buybacks and dividends.
If you are using the VBA code here by itself then the result will be limited to the number of observations displayed on the web page requested within the web query.
In the Excel program download which uses this code, we have created a loop that gets around this limitation to make multiple requests for longer time periods. This program also has other features facilitate market data downloading into Excel.
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.
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.
