I am new at working with web queries, and I am attempting to build some code
that will download stock quotes. I know that there are some canned programs
out there, but I want to learn by building this myself. Some of my VERY basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

quartz

Open your browser and point to the page you want. Copy the URL from the
browser and paste it into your code. Don't forget to add URL; to the front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build some
code that will download stock quotes. I know that there are some canned
programs out there, but I want to learn by building this myself. Some of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Thanks Dick.

Once I have the "URL" there is usually some short string that follows the
URL that allows you to specify what stock or stocks you want data on. How do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for the page?
Do I have to search the provider's web site for it? How do I get that crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL from the
browser and paste it into your code. Don't forget to add URL; to the front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build some
code that will download stock quotes. I know that there are some canned
programs out there, but I want to learn by building this myself. Some of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

do the query manually at the yahoo site, then look at the resulting URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that follows the
URL that allows you to specify what stock or stocks you want data on. How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for the
page?
Do I have to search the provider's web site for it? How do I get that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL from the
browser and paste it into your code. Don't forget to add URL; to the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself. Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

In later versions of excel you can right click in the body of the web site
and download to an excel file. This creates a query. Then you can record a
macro while using dataedit query. Then you can see how to change the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the resulting URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that follows
the
URL that allows you to specify what stock or stocks you want data on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for the
page?
Do I have to search the provider's web site for it? How do I get that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL from
the
browser and paste it into your code. Don't forget to add URL; to the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on is
how the heck does one locate the connection string to connect, say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Thanks much Tom. I will give this a try.

"Tom Ogilvy" wrote:

do the query manually at the yahoo site, then look at the resulting URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that follows the
URL that allows you to specify what stock or stocks you want data on. How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for the
page?
Do I have to search the provider's web site for it? How do I get that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL from the
browser and paste it into your code. Don't forget to add URL; to the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself. Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Thanks Don. After right clicking, I selected "Export to Excel" is that right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of the web site
and download to an excel file. This creates a query. Then you can record a
macro while using dataedit query. Then you can see how to change the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the resulting URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that follows
the
URL that allows you to specify what stock or stocks you want data on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for the
page?
Do I have to search the provider's web site for it? How do I get that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL from
the
browser and paste it into your code. Don't forget to add URL; to the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on is
how the heck does one locate the connection string to connect, say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of the web
site
and download to an excel file. This creates a query. Then you can record
a
macro while using dataedit query. Then you can see how to change the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that
follows
the
URL that allows you to specify what stock or stocks you want data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for
the
page?
Do I have to search the provider's web site for it? How do I get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL
from
the
browser and paste it into your code. Don't forget to add URL; to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to
build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Great! That did the trick! Thanks so much!

Now I guess if I want the data in a certain order/format, I need to just
write some code to grab what I need, then loop to the next stock...any
thoughts/shortcuts on this?

Thanks again.

"Don Guillett" wrote:

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of the web
site
and download to an excel file. This creates a query. Then you can record
a
macro while using dataedit query. Then you can see how to change the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that
follows
the
URL that allows you to specify what stock or stocks you want data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code for
the
page?
Do I have to search the provider's web site for it? How do I get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the URL
from
the
browser and paste it into your code. Don't forget to add URL; to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to
build
some
code that will download stock quotes. I know that there are some
canned
programs out there, but I want to learn by building this myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

If you recorded a macro while doing a refresh and without seeing
it,something like this.

for each c in symbolslist
="htttp:etcetcetc"&c
do your refresh thing
next c

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Great! That did the trick! Thanks so much!

Now I guess if I want the data in a certain order/format, I need to just
write some code to grab what I need, then loop to the next stock...any
thoughts/shortcuts on this?

Thanks again.

"Don Guillett" wrote:

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is
that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of the
web
site
and download to an excel file. This creates a query. Then you can
record
a
macro while using dataedit query. Then you can see how to change
the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the
resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that
follows
the
URL that allows you to specify what stock or stocks you want
data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code
for
the
page?
Do I have to search the provider's web site for it? How do I get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the
URL
from
the
browser and paste it into your code. Don't forget to add URL;
to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to
build
some
code that will download stock quotes. I know that there are
some
canned
programs out there, but I want to learn by building this
myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to
connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in
advance.

Exactly!

It runs really well and fast. I'm surprised how easy it is. I'm planning on
using Excel as a front end for retrieving and reporting, charting, etc. The
historical data will be automatically ported over to an Access DB so that
trends can be tracked.

Again, thanks so much! I'm like a kid with a new toy.

"Don Guillett" wrote:

If you recorded a macro while doing a refresh and without seeing
it,something like this.

for each c in symbolslist
="htttp:etcetcetc"&c
do your refresh thing
next c

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Great! That did the trick! Thanks so much!

Now I guess if I want the data in a certain order/format, I need to just
write some code to grab what I need, then loop to the next stock...any
thoughts/shortcuts on this?

Thanks again.

"Don Guillett" wrote:

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is
that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of the
web
site
and download to an excel file. This creates a query. Then you can
record
a
macro while using dataedit query. Then you can see how to change
the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the
resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string that
follows
the
URL that allows you to specify what stock or stocks you want
data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML code
for
the
page?
Do I have to search the provider's web site for it? How do I get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy the
URL
from
the
browser and paste it into your code. Don't forget to add URL;
to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am attempting to
build
some
code that will download stock quotes. I know that there are
some
canned
programs out there, but I want to learn by building this
myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to
connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in
advance.

Send me a copy to my email below so I can see how you did.

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Exactly!

It runs really well and fast. I'm surprised how easy it is. I'm planning
on
using Excel as a front end for retrieving and reporting, charting, etc.
The
historical data will be automatically ported over to an Access DB so that
trends can be tracked.

Again, thanks so much! I'm like a kid with a new toy.

"Don Guillett" wrote:

If you recorded a macro while doing a refresh and without seeing
it,something like this.

for each c in symbolslist
="htttp:etcetcetc"&c
do your refresh thing
next c

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Great! That did the trick! Thanks so much!

Now I guess if I want the data in a certain order/format, I need to
just
write some code to grab what I need, then loop to the next stock...any
thoughts/shortcuts on this?

Thanks again.

"Don Guillett" wrote:

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is
that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of
the
web
site
and download to an excel file. This creates a query. Then you
can
record
a
macro while using dataedit query. Then you can see how to
change
the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the
resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string
that
follows
the
URL that allows you to specify what stock or stocks you want
data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML
code
for
the
page?
Do I have to search the provider's web site for it? How do I
get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy
the
URL
from
the
browser and paste it into your code. Don't forget to add
URL;
to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am
attempting to
build
some
code that will download stock quotes. I know that there
are
some
canned
programs out there, but I want to learn by building this
myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to
connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE
HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in
advance.

Sure. I'm just building it and I have a 2-week vacation coming soon. So it
may be awhile, but I will make a note to e-mail you a copy. Don't be
surprised in a couple months to get this thing out of the blue!


"Don Guillett" wrote:

Send me a copy to my email below so I can see how you did.

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Exactly!

It runs really well and fast. I'm surprised how easy it is. I'm planning
on
using Excel as a front end for retrieving and reporting, charting, etc.
The
historical data will be automatically ported over to an Access DB so that
trends can be tracked.

Again, thanks so much! I'm like a kid with a new toy.

"Don Guillett" wrote:

If you recorded a macro while doing a refresh and without seeing
it,something like this.

for each c in symbolslist
="htttp:etcetcetc"&c
do your refresh thing
next c

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Great! That did the trick! Thanks so much!

Now I guess if I want the data in a certain order/format, I need to
just
write some code to grab what I need, then loop to the next stock...any
thoughts/shortcuts on this?

Thanks again.

"Don Guillett" wrote:

Yes, how did it work for you?

--
Don Guillett
SalesAid Software

"quartz" wrote in message
...
Thanks Don. After right clicking, I selected "Export to Excel" is
that
right?

Thanks a lot to both you and Tom for your help.

"Don Guillett" wrote:

In later versions of excel you can right click in the body of
the
web
site
and download to an excel file. This creates a query. Then you
can
record
a
macro while using dataedit query. Then you can see how to
change
the
parameters in the resulting macro.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
do the query manually at the yahoo site, then look at the
resulting
URL.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Dick.

Once I have the "URL" there is usually some short string
that
follows
the
URL that allows you to specify what stock or stocks you want
data
on.
How
do
I locate that info?

Can I go to "View" then "Source" and find it in the HTML
code
for
the
page?
Do I have to search the provider's web site for it? How do I
get
that
crucial
bit of info?

Thanks much in advance for your assistance.


"Dick Kusleika" wrote:

quartz

Open your browser and point to the page you want. Copy
the
URL
from
the
browser and paste it into your code. Don't forget to add
URL;
to
the
front
if you accidently paste over it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

quartz wrote:
I am new at working with web queries, and I am
attempting to
build
some
code that will download stock quotes. I know that there
are
some
canned
programs out there, but I want to learn by building this
myself.
Some
of
my VERY basic code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on is
how the heck does one locate the connection string to
connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE
HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in
advance.

I'd be careful about using WebQueries for anything serious. I have been using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet site
reports that a connection was established but the data is not available".
Nothing about this error in the Knowledge Base. There are about 20 posts
about this problem in several MSDN newsgroups, and nobody ever got an answer
that solved their problem. The only answer I saw is "make sure your URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build some code
that will download stock quotes. I know that there are some canned programs
out there, but I want to learn by building this myself. Some of my VERY basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions on is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Might be because the site changed. goto it manually and see what you get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet site
reports that a connection was established but the data is not available".
Nothing about this error in the Knowledge Base. There are about 20 posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions on
is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

The site was never changed for my case..yesterday, the xp computer
did a windows update automatically without my knowledge and the site I
used to query for years returned this same message that there was no
data.. I know it is due to critcal security updates of the service
packs.. I am still trying to find a quick solution without me having
to re-install or repair the xp window operating system...Any help
would be appreciated

"Don Guillett" wrote in message ...
Might be because the site changed. goto it manually and see what you get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet site
reports that a connection was established but the data is not available".
Nothing about this error in the Knowledge Base. There are about 20 posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions on
is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Maybe the site changed, but not in a way that I noticed. Could be cookies or
ads. The site is MSN's moneycentral.com, and I get my quotes manually just
the same as 6 months ago.
http://moneycentral.msn.com/scripts/...mbol=CAL%2CGLW
Besides, in the "Edit Web Query" dialog, I can navigate to the page and see
the table I need, without the yellow arrows and the "Import" button is
disabled.
I then have to click the yellow-arrow-red-cross icon twice, and the yellow
arrows appear plus the "Import" button becomes enabled. Then, when I select
the table I need and click Import, I get the error again.


"Don Guillett" wrote:

Might be because the site changed. goto it manually and see what you get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet site
reports that a connection was established but the data is not available".
Nothing about this error in the Knowledge Base. There are about 20 posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions on
is
how the heck does one locate the connection string to connect, say, to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Goto internet explorertoolsinternet optionssecurity(near the
bottom)uncheck "do not save encrypted pages to disk"applyOK

--
Don Guillett
SalesAid Software

"James" wrote in message
...
Maybe the site changed, but not in a way that I noticed. Could be cookies
or
ads. The site is MSN's moneycentral.com, and I get my quotes manually just
the same as 6 months ago.
http://moneycentral.msn.com/scripts/...mbol=CAL%2CGLW
Besides, in the "Edit Web Query" dialog, I can navigate to the page and
see
the table I need, without the yellow arrows and the "Import" button is
disabled.
I then have to click the yellow-arrow-red-cross icon twice, and the yellow
arrows appear plus the "Import" button becomes enabled. Then, when I
select
the table I need and click Import, I get the error again.


"Don Guillett" wrote:

Might be because the site changed. goto it manually and see what you
get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have
been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet
site
reports that a connection was established but the data is not
available".
Nothing about this error in the Knowledge Base. There are about 20
posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your
URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build
some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my
VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on
is
how the heck does one locate the connection string to connect, say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

I'll be sure to try that in a couple of days when I get back to my home PC.
Sounds weird though, afaik the MSN page isn't encrypted.

"Don Guillett" wrote:

Goto internet explorertoolsinternet optionssecurity(near the
bottom)uncheck "do not save encrypted pages to disk"applyOK

--
Don Guillett
SalesAid Software

"James" wrote in message
...
Maybe the site changed, but not in a way that I noticed. Could be cookies
or
ads. The site is MSN's moneycentral.com, and I get my quotes manually just
the same as 6 months ago.
http://moneycentral.msn.com/scripts/...mbol=CAL%2CGLW
Besides, in the "Edit Web Query" dialog, I can navigate to the page and
see
the table I need, without the yellow arrows and the "Import" button is
disabled.
I then have to click the yellow-arrow-red-cross icon twice, and the yellow
arrows appear plus the "Import" button becomes enabled. Then, when I
select
the table I need and click Import, I get the error again.


"Don Guillett" wrote:

Might be because the site changed. goto it manually and see what you
get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have
been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet
site
reports that a connection was established but the data is not
available".
Nothing about this error in the Knowledge Base. There are about 20
posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your
URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build
some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my
VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on
is
how the heck does one locate the connection string to connect, say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

I just made the test:
- from the office, do not save encrypted pages set: worked
- from home, do not save encrypted pages set: did not work
- from home, do not save encrypted pages cleared: work (I was amazed at this
stage)
- tried again, do not save encrypted pages cleared: worked again
- tried again, this time with do not save encrypted pages set: worked again
!!! = disappointment. So, what it looks to me is either it is a "random"
phenomenon, or moneycentral has some add server with an add rotator that
appears only once in a while and screws up things. Since I have an add
blocker, I cannot tell which it is.

In conclusion, I'm going to look into writing a program to pull the quotes
in plain html text and forget about Excel.

Anybody here knows a way of getting the html string served by a web site?
I used to be VB programmer, but the WebBrowser object doesn't navigate
outside of your domain since IE 6.0.



"Don Guillett" wrote:

Goto internet explorertoolsinternet optionssecurity(near the
bottom)uncheck "do not save encrypted pages to disk"applyOK

--
Don Guillett
SalesAid Software

"James" wrote in message
...
Maybe the site changed, but not in a way that I noticed. Could be cookies
or
ads. The site is MSN's moneycentral.com, and I get my quotes manually just
the same as 6 months ago.
http://moneycentral.msn.com/scripts/...mbol=CAL%2CGLW
Besides, in the "Edit Web Query" dialog, I can navigate to the page and
see
the table I need, without the yellow arrows and the "Import" button is
disabled.
I then have to click the yellow-arrow-red-cross icon twice, and the yellow
arrows appear plus the "Import" button becomes enabled. Then, when I
select
the table I need and click Import, I get the error again.


"Don Guillett" wrote:

Might be because the site changed. goto it manually and see what you
get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have
been
using
them for more than 6 months now in Excel 2003, and 2 days ago my query
suddenly stopped working. You get a silly error message "The internet
site
reports that a connection was established but the data is not
available".
Nothing about this error in the Knowledge Base. There are about 20
posts
about this problem in several MSDN newsgroups, and nobody ever got an
answer
that solved their problem. The only answer I saw is "make sure your
URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to build
some
code
that will download stock quotes. I know that there are some canned
programs
out there, but I want to learn by building this myself. Some of my
VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any discussions
on
is
how the heck does one locate the connection string to connect, say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.

Use Yahoo!

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I just made the test:
- from the office, do not save encrypted pages set: worked
- from home, do not save encrypted pages set: did not work
- from home, do not save encrypted pages cleared: work (I was amazed at
this
stage)
- tried again, do not save encrypted pages cleared: worked again
- tried again, this time with do not save encrypted pages set: worked
again
!!! = disappointment. So, what it looks to me is either it is a "random"
phenomenon, or moneycentral has some add server with an add rotator that
appears only once in a while and screws up things. Since I have an add
blocker, I cannot tell which it is.

In conclusion, I'm going to look into writing a program to pull the quotes
in plain html text and forget about Excel.

Anybody here knows a way of getting the html string served by a web site?
I used to be VB programmer, but the WebBrowser object doesn't navigate
outside of your domain since IE 6.0.



"Don Guillett" wrote:

Goto internet explorertoolsinternet optionssecurity(near the
bottom)uncheck "do not save encrypted pages to disk"applyOK

--
Don Guillett
SalesAid Software

"James" wrote in message
...
Maybe the site changed, but not in a way that I noticed. Could be
cookies
or
ads. The site is MSN's moneycentral.com, and I get my quotes manually
just
the same as 6 months ago.

http://moneycentral.msn.com/scripts/...mbol=CAL%2CGLW
Besides, in the "Edit Web Query" dialog, I can navigate to the page
and
see
the table I need, without the yellow arrows and the "Import" button is
disabled.
I then have to click the yellow-arrow-red-cross icon twice, and the
yellow
arrows appear plus the "Import" button becomes enabled. Then, when I
select
the table I need and click Import, I get the error again.


"Don Guillett" wrote:

Might be because the site changed. goto it manually and see what you
get.

--
Don Guillett
SalesAid Software

"James" wrote in message
...
I'd be careful about using WebQueries for anything serious. I have
been
using
them for more than 6 months now in Excel 2003, and 2 days ago my
query
suddenly stopped working. You get a silly error message "The
internet
site
reports that a connection was established but the data is not
available".
Nothing about this error in the Knowledge Base. There are about 20
posts
about this problem in several MSDN newsgroups, and nobody ever got
an
answer
that solved their problem. The only answer I saw is "make sure
your
URL
includes .html".

"quartz" wrote:

I am new at working with web queries, and I am attempting to
build
some
code
that will download stock quotes. I know that there are some
canned
programs
out there, but I want to learn by building this myself. Some of
my
VERY
basic
code so far is below.

QUESTION: The one question that I can't seem to find any
discussions
on
is
how the heck does one locate the connection string to connect,
say,
to
Yahoo.com or to Microsoft.com to import stock info?

Set qts = ActiveSheet.QueryTables
Set qt = qts.Add("URL;http://www.HOW DO i FIND WHAT THE HECK
GOES
HERE",
Application.Range(ActiveCell.Address))
With qt
.WebSelectionType = xlSpecifiedTables
'.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = False
.AdjustColumnWidth = True
.Refresh 'BackgroundQuery:=False
End With

Please help and be gentle to this starter...thanks in advance.