brilliant. many thanks!
I asked TOS support if I can have live DDE prices and they said it is not possible.
In case someone like will be looking in future, here how is it done:
http://readtheprospectus.wordpress....or-swim-for-streaming-real-time-calculations/
Hi there
I'm not sure if you can easily get Options Historical data -- I think the only way you'll realistically be able to do this is to save the data yourself via downloading from YAHOO every day
For example say you are options trading MSFT (Microsoft)
Download from YAHOO
http://finance.yahoo.com/q/op?s=MSFT
This gives you the current / future prices on a given day
You'll see say for JUNE 11 expiry TWO sets example strike price
Symbol Last Chg Bid Ask Vol Open Int
Jun 11 MSFT110603C00023000 1.46 0.00 N/A N/A 145 595
Jun 11 MSFT110618C00023000 1.55 0.00
This is because MSFT is traded WEEKLY as well as MONTHLY The symbol 110603C means 2011 june 03 (expiry) C = Call
You can download the data as a CSV file into a spreadsheet and save it but for options you would have an almost impossible amount of data - especially if the share had a wildly fluctating price.
You can easily download into excel the EOD data (and the intraday too) from Yahoo -- not difficult -- examples all over the web on doing that or purchase a neat little program which will also convert the data into Metastock format using FREE data sources
HQUOTE
http://www.hquotes.com/hquote.html
I would question the advantages or value of ANY backtesting on Call / Put options unlike the basic share price -- I think also in trading weekly's you need to do some quick thinking and "Seat of Pants" stuff.
Downloading historical data for the base price of a share (Open, Close, High, Low, Volume) is worth it for technical analysis and the look of the graph can help to decide if the stock is suitable for options trading -- but I can't actually see any value in past OPTION price data.
To download data from YAHOO
Do something like this
1) download into Column A on the spreadsheet the symbols of the stocks you are interested in
2) download with this sort of macro
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer
Clear
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set DataSheet = ActiveSheet
For iMax = 0 To 1000 Step 200
i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
' qurl = qurl + "&f=" + Range("C2")
qurl = qurl + "&f=" + "l1pjkn"
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("Q7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("Q7:Q1200").Select
Selection.TextToColumns Destination:=Range("Q7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
' Array(6, 1), _
' Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
Range("Q7:W1200").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next iMax
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
stopHere:
Range("G3") = 1
Sort
Clear2
currenttime
End Sub
Sub Clear()
'
' Clear Macro
'
'
Range("C7:H1200").Select
Selection.ClearContents
End Sub
Sub Clear2()
'
' clear2 Macro
'
Columns("Q:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
Sub Sort()
'
' Sort Macro
in_sort = "1"
'
Dim i As Integer
i = Range("G3")
If i >= 8 Then
i = 1
End If
Range("A4:G4").Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells(4, i).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A7:G1000").Select
Selection.Sort Key1:=Cells(7, i), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Range("K2").Select
'Selection.Copy
'Range("G2").Select
'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
' False, Transpose:=False
'Range("G2").Select
'ActiveCell.FormulaR1C1 = "=Today()"
in_sort = "0"
' Range("L3").Select
End Sub
selectable fields from YAHOO (Tags -- see below) "l1pjkn"
when you retrieve data from YAHOO into a CSV file --which can be processed by the excel sheet you need to supply the YAHOO tags
The tags are found here
http://www.gummy-stuff.org/Yahoo-data.htm
for example tag j = 52 week low
Cheers
jimbo
