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

Quantitative Finance Collector is a blog on Quantitative finance analysis, financial engineering methods in mathematical finance focusing on derivative pricing, quantitative trading and quantitative risk management. Random thoughts on financial markets and personal staff are posted at the sub personal blog.

Apr 23
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: ,
Jul 29
This R program can be used to download option price data from Yahoo to a data frame and to plot the corresponding implied-volatility smiles.

http://www.math.tu-berlin.de/~mkeller/index.php?target=rcode
Tags: , ,
Pages: 1/1 First page 1 Final page [ View by Articles | List ]