Web Query question

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
0
Utf
4/23/2010 2:31:01 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
787 Views

Similar Articles

[PageSpeed] 36

I think you are mising (at least) one quote?

Reg Migrant

"David" wrote:

> 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
0
Utf
4/23/2010 10:17:01 AM
Maybe...

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



David wrote:
> 
> 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

-- 

Dave Peterson
0
Dave
4/23/2010 11:45:21 AM
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

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"David" <David@discussions.microsoft.com> wrote in message 
news:35CDAD32-8441-42D8-803B-279002B432F0@microsoft.com...
>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 

0
Don
4/23/2010 1:24:44 PM
Hi Dave,

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

"Dave Peterson" wrote:

> Maybe...
> 
>     With ActiveSheet.QueryTables.Add(Connection:= _
>         "URL;http://finance.yahoo.com/q/op?s=" _
>         & Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))
> 
> 
> 
> David wrote:
> > 
> > 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
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/23/2010 1:40:01 PM
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"))



David wrote:
> 
> Hi Dave,
> 
> Thanks for the try, its not working, seems like with "" marks it thinks s=
> range("b1")...
> 
> "Dave Peterson" wrote:
> 
> > Maybe...
> >
> >     With ActiveSheet.QueryTables.Add(Connection:= _
> >         "URL;http://finance.yahoo.com/q/op?s=" _
> >         & Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))
> >
> >
> >
> > David wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/23/2010 1:56:03 PM
I would type IBM in cell B1, the line will look for IBM stock

"Dave Peterson" wrote:

> 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"))
> 
> 
> 
> David wrote:
> > 
> > Hi Dave,
> > 
> > Thanks for the try, its not working, seems like with "" marks it thinks s=
> > range("b1")...
> > 
> > "Dave Peterson" wrote:
> > 
> > > Maybe...
> > >
> > >     With ActiveSheet.QueryTables.Add(Connection:= _
> > >         "URL;http://finance.yahoo.com/q/op?s=" _
> > >         & Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))
> > >
> > >
> > >
> > > David wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/23/2010 2:42:02 PM
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

"Don Guillett" wrote:

> 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
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "David" <David@discussions.microsoft.com> wrote in message 
> news:35CDAD32-8441-42D8-803B-279002B432F0@microsoft.com...
> >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 
> 
> .
> 
0
Utf
4/23/2010 2:43:01 PM
Reply:

Similar Artilces: