Quantitative Finance Collector is a blog on Quantitative finance analysis, methods in mathematical finance focusing on derivative pricing, quantitative trading and quantitative risk management.
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
In Matlab there is build-in function named "fetch" for requesting data from Yahoo! data servers.
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
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.
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
http://www.math.tu-berlin.de/~mkeller/index.php?target=rcode
