Web Query question

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david
 
Try this or send your file to me at the address below
Sub GetOptionsSAS()'Use to establish the query
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" & _
Range("b1") & "&m=" & Range("c1") & "", _
Destination:=Range("A3"))
.Refresh BackgroundQuery:=False
End With
End Sub
Name the sheet Options
Use below to refresh with different symbols in b1 and different months in
c1. Do NOT use the first one again.
Sub RefreshWithVariablesSAS()'Assign to a button on your sheet
With Sheets("Options").QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/op?s=" & Range("b1") &
"&m=" & Range("c1")
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "11,14,15,16,19"
'.WebPreFormattedTextToColumns = False
'.WebConsecutiveDelimitersAsOne = False
'.WebSingleBlockTextImport = False
'.WebDisableDateRecognition = False
'.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With
End Sub
If I can be of further assistance, contact me privately at the address below
 
Hi Dave,

Thanks for the try, its not working, seems like with "" marks it thinks s=
range("b1")...
 
I don't understand.

If you were looking for IBM, how would that line of code look?

I would have guessed:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& "IBM" & ";&m=2010-05", Destination:=Range("$A$11"))
 
Hi Don,

you are great, it works like a charm, not only i was able to figure out the
separator, and also you macro gave me better ideas, once again thank you

David
 
Back
Top