  # 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

Posted by abiao at 15:47 | Code » VBA/Excel | Comments(22) | Reads(56511)
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.

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.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

#### download option price data from Yahoo

Posted by abiao at 07:57 | Code » R/Splus | Comments(4) | Reads(30770)
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: , ,