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(56467)
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: ,
Google will return a limited set (max 200 data points) - basically, the data displayable on the webpage.  

Seems you can't get to the table part which you can download manually for a longer data range.
historical stock prices Email Homepage
Nice site here.

We fetch our data from yahoo and have the same info.
Here's an Excel spreadsheet to download Forex data: http://investexcel.net/222/automatically-download-historical-forex-data-into-excel/

It also uses VBA
Business_Spreadsheets Email Homepage
There is a free and open source VBA solution to extract current and historical stock prices for multiple securities over long time periods that works in all versions of Excel including Excel for Mac (2004 and 2011) here.
MPierce486
Hey everyone. I tried modifying the macro to allow the historical prices from 20 stocks to populate in one sheet. Since this is a headache, I have created 20 sheets, each with one stock on it, and used the macro that way. Now, my issue. I want a main macro to attach to a button that, once i click it, will run the macro for all 20 sheets. Please help!
is the macro in this post useful? http://www.mathfinance.cn/download-multiple-stock-quotes-from-yahoo-finance/
Hi
Could anyone please help with a similar requirement. What I need to do through  vba is to search a ticketing url, for all open tickets of the month and press on an Extract button, which has a drop down. I need to select Excel to get the data to be extracted to a new excel file. The confusion is how to get the url of the Search->Extract->Excel process. Any help would be great.
Pages: 2/2 First page Previous page 1 2 Final page
Add a comment
Emots
Enable HTML
Enable UBB
Enable Emots
Hidden
Remember
Nickname   Password   Optional
Site URI   Email   [Register]