Quantitative finance collector
C++ Matlab VBA/Excel Java Mathematica R/Splus Net Code Site Other
Apr 23

Download historical stock price

Posted by abiao at 15:47 | Code » VBA/Excel | Comments(22) | Reads(45167)
A friend of mine asks me how to download stock historical price automatically with Excel, here are two ways I have played:

1, using Excel 2003/2002 Add-in to download from MSN Money Stock Quotes. This add-in for Microsoft Office Excel 2003 and Microsoft Excel 2002 allows you to get dynamic stock quotes from the MSN Money Web site. The add-in allows you to easily gather and study the stocks of interest to you.
http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

2, using the following macro to download from Yahoo finance. where you have to input start, end date and stock symbol
Sub GetData()

    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    Set DataSheet = ActiveSheet
  
        StartDate = DataSheet.Range("B1").Value
        EndDate = DataSheet.Range("B2").Value
        Symbol = DataSheet.Range("B3").Value
        Range("C7").CurrentRegion.ClearContents
        
        qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"
        Range("b5") = qurl
                  
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
            
            Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
            Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"

End Sub


In Matlab there is build-in function named "fetch" for requesting data from Yahoo! data servers.


Tags: ,
hi, this is good. but if we have a lot of sheets that we want to perform the same query, how do we go about it? and the data extracted is in descending order, how do we do it in ascending order?
thanks
Hi jes,

Add the followning VBA code at the end of teh macro:

Range(Range("C8"), Range("I8").End(xlDown)).Sort Range("C7"), xlAscending
Btw, Thanks a lot for all teh work you have done here. Your blog is really useful ! :)
How can i run this macro for another stock? i need to query for 30 stock in one excel files.. pls help. thanks.
I recommend you to check this post http://www.mathfinance.cn/excellent-yahoo-finance-data-downloader/ and the site it refers.
Thanks.. but still not found what i'm looking for.. i just want to query historical price (ex: from jan 1, 2010 to this day), for 30 stock, in one spreadsheet.. that site only query for several stock in last day trade. pls help me..
Must them be on one sheet or 30 sheets of one file?
I prefer first, thanks.
not easy to put all stocks in one sheet as Yahoo finance's default output includes high, low, open, close, adjusted price & there is limitation of number of columns in Excel. I can make you a quick template to put all data in one excel file, with each stock's data in one sheet, if you want.
It's okay Sir.. Thank you very much. I need EOD, historical price (closing price only) and volume transaction. there's no problem with each stock's data in one sheet.
check the post http://www.mathfinance.cn/download-multiple-stock-quotes-from-yahoo-finance/
Mission accomplish. Thank You Very-Very Much. God Bless You Abiao...
Thanks for the useful code. However, is there an easy way to send the returned data directly to a csv file?

Many thanks.
abiao replied on 2010/12/22 18:32
You may be interested in this post, hope it is useful http://www.mathfinance.cn/download-multiple-stock-quotes-from-yahoo-finance/
Hi.  That could be useful, although it places each stock's data in a new worksheet rather than saving to file.  I was thinking more along the lines of manipulating the query a bit like an Access recordset.  Trying to modify the "Destination" part of the query does not work as it seems to insist on a sheet reference (I suppose it's not possible to send the data to an array?).

Incidentally, I've started looking at Google Finance as a source of data as Yahoo is missing a fair bit of historical data on non-US stocks, ETF's, etc.  So far, I haven't got the query to return the  data properly - annoying.
Google Finance doesn't support data downloading, as far as I know.
Pages: 1/2 First page 1 2 Next page Final page
Add a comment
Emots
Enable HTML
Enable UBB
Enable Emots
Hidden
Remember
Nickname   Password   Optional
Site URI   Email   [Register]