How locate connection information for web query?

[Guest]

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.

 

[Dick Kusleika]

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.

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Don Guillett]

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 data>edit query. Then you can see how to change the
parameters in the resulting macro.

 

[Guest]

Thanks much Tom. I will give this a try.

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

 

[Guest]

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]

Yes, how did it work for you?

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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!

 

[Guest]

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

 

[Don Guillett]

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

 

[tanso]

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

 

[Guest]

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/webquote.dll?iPage=qd&Symbol=CAL,GLW
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]

Goto internet explorer>tools>internet options>security(near the
bottom)>uncheck "do not save encrypted pages to disk">apply>OK

 

[Guest]

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.

 

[Guest]

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.