Yahoo quotes error in Excel

Peter Benson · Feb 10, 2004

For those of you who have recently been getting "Application-defined or
object-defined error" when attepting to get a quote from Yahoo from Exce
VBA: their US site http://finance.yahoo.com/?u currently won't let you
download a CSV file. eg get a quote for IBM and click the "download data"
link. As at today it simply displays the data in your browser window rather
than letting you download the data in CSV format like it used to. Over the
last month the problem was intermittent but now it seems to be constant. As
no CSV file is downloaded Excel displays the Application-defined or object
defined error.

I have no idea whether the problem with Yahoo is temporary or permanent.

KJTFS · Feb 10, 2004

You might want to check you methods, my stock data getter a
www.kjtfs.com still works and I use Yahoo finance.

Keith
www.kjtfs.com

Sonny Kocak[MSFT] · Feb 10, 2004

Dear Peter ,

Thank you for contacting Microsoft Excel Online Support. My name is Sonny
and I will
be assisting you with this issue.

It clearly appears that you understand that the whole issue is the lack of
the CSV file. Thus, I can not speculate to Yahoo behavior on this manner.
However, I can offer some helpful suggestions. One needs to ask: does this
happen for each stock? Does it happen only for my existing stock list? Do I
need to rebuild my list? Does this occur on new stock that I haven't tried?
Is the problem isolated to just this machine?

Finally, It appears that Yahoo Finance web site still supports and provides
downloadable stock files for spreadsheets. I am including two links below
to Yahoo support pages: The first is for problems with downloadable
spreadsheet. (NOTE: with special instructions for Netscape 4.0 users). The
second link is general finance support.

1) http://help.yahoo.com/help/us/fin/quote/quote-05.html

2) http://help.yahoo.com/help/us/fin/quote/

Hope this helps,

Sonny Kocak
Microsoft Developer Support Professional
This pointing is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Peter Benson" <[email protected]>
| Subject: Yahoo quotes error in Excel
| Date: Tue, 10 Feb 2004 23:16:17 +1100
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <e0dW2#[email protected]>
| Newsgroups: microsoft.public.excel.programming
| NNTP-Posting-Host: cpe-144-132-167-133.nsw.bigpond.net.au 144.132.167.133
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.programming:461562
| X-Tomcat-NG: microsoft.public.excel.programming
|
| For those of you who have recently been getting "Application-defined or
| object-defined error" when attepting to get a quote from Yahoo from Exce
| VBA: their US site http://finance.yahoo.com/?u currently won't let you
| download a CSV file. eg get a quote for IBM and click the "download data"
| link. As at today it simply displays the data in your browser window
rather
| than letting you download the data in CSV format like it used to. Over the
| last month the problem was intermittent but now it seems to be constant.
As
| no CSV file is downloaded Excel displays the Application-defined or object
| defined error.
|
| I have no idea whether the problem with Yahoo is temporary or permanent.
|
|
|
|

Tom Ogilvy · Feb 10, 2004

Shouldn't
This pointing is provided "AS IS" with no warranties, and confers no rights.

be
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter Benson · Feb 10, 2004

My methods are fine and have been in use for years. Your stock getter
retrieves historical prices from what I see. This is a completely different
service from on-line quotes and still works fine.

You can see the quote service on Yahoo no longer lets you download a file:
go to the US Yahoo finance page, get a quote for a stock, press the
'download data' button and instead of downloading a file it now simply
displays the CSV contents in a web page. Unfortunately you cannot do a web
query on this web page as they are clearly doing some sort of redirection
which prevents using a simple web query from Excel.

KJTFS · Feb 10, 2004

I tried your method and it worked on my computer I have attached th
.csv file (in a zip that it saved for me) I am using XP pro with I
6.0

Keith
www.kjtfs.co

Attachment filename: quotes.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=43802

Sonny Kocak[MSFT] · Feb 11, 2004

Peter Benson · Feb 11, 2004

Keith,

You really must stop your style of post: which is to, firstly, invalidate
the original poster's problem (" I can get it to work therefore there is no
problem"), and, secondly, rather than offering a snippet of code or some
other useful suggestion simply referring people to your web site to download
your own products. Your own products are password protected and therefore of
no use to people in this forum which is an Excel programming forum.

But also, importantly, your products contain bugs and should not be
referred to anybody in their present state (more of that below).

Regarding my problem: it is a real problem with the Yahoo site. I have an
add-in used by tens of thousands of people world-wide and as from a couple
of days ago, this part of it no longer works on MOST (not all) versions of
Excel/Windows/IE. The code is standard code used by everybody who does web
queries to Yahoo:

The code, in its most stripped down version is:

URL =
"URL;http://finance.yahoo.com/d/quotes.csv?s=IBM&f=sl1d1t1c1ohgv&e=.csv"

With WB.Worksheets("Docs").QueryTables.Add(Connection:=URL,
Destination:=WB.Worksheets("Docs").Range("a1").Cells(1, 1))

.Refresh BackgroundQuery:=False

End With

No other properties need be set. The above code has been working for years.
It still works on Excel 97 under Windows 95 with IE 5.1 installed. It doesn'
t work )ie produces the Application-defined or
object-defined error on Excel 2000 under Windows 2000, Excel 2000 under
Windows 98 or Excel 2003 under Windows XP (all running IE 6.0 with latest
patches). Perhaps there are other situations where it WILL work. But there
certainly is a problem with the Yahoo link which wasn't there before. And it
can be tested manually (but like under Excel, on some versions of Windows/IE
it still works OK). Dozens of emails from my users from around the world
have confirmed the problem. It isn't solved, Keith, by your gratuitous
comments "it works for me" with no other information.

Now Keith: if you acknowledge the problem you may have a solution. If so
how about you post it here -- point out the error in my code which I'm
happy to admit is a real possibility. But don't just refer us to your web
site to promote your software.

Now to YOUR software and it's problems: I downloaded your software. Firstly
it doesn't do quotes so I'm not even sure why you referred me to it. It does
historical data, which is a completely different Yahoo service which still
works fine.

But anyway I thought I'd try it. Keith: try getting historical data for
start date 1 Aug 1999 to the present for the following stocks: N.TO, ANZ.AX,
BSY.L. Does it return all the data for these stocks? No - it only returns
the last 200 trading days. Where is the rest of the data Keith? Perhaps you
should test it for non-US exchanges - there are quite a few of them you
know. And don't tell me Yahoo only provides 200 days of data maximum for
non-US exchanges. It's not true - I use it myself all the time to extract
four and more years' of data. I won't post a code snippet here keith
because it's not an Excel issue: it's a basic programming logic issue.

So, please offer relevant suggestions by all means but stop just referring
people to your buggy password protected software which is of no use to this
forum.

KJTFS · Feb 12, 2004

Peter,
Sorry for offending you like this. Sorry for sending you to
historical quote getter when you where looking for a current stoc
quote getter. Also yes I am aware of the issue with the foreig
stocks, I haven't updated it because we use a retuters or thomson on
terminal for all our quotes now (not me but my work company) I neve
said it must work because it works for me, I was simply saying I a
getting it to work which I am... Then I gave you my system specs s
you could compare and maybe find a solution their. I dont know wha
the problem is, web queries are a pretty easy thing so I am sure yo
are not screwing up the code I was simply saying that it works for me.
I never meant to invalidate your problem I simply said "it works for m
you might want to check your methods" I dont see anyone else chimin
in with answers or even it doesn't work for me too. Just trying to pu
some more info on the table to help you. As for my other posts, I tr
to send people in the direction of where they might need to head wit
the code. I log into the boards on my spare time at work and only hav
a few mins, not enough time to go into excel and wright all the cod
and test it, but I do have enought time to try to set someone in th
right direction, they should be able to code the rest. Usually when
send people to my website it is so they can look at the logic I use fo
setting up a binomial table or a mortgage calculator. You dont nee
code for that it is a learning thing. I didn't mean to put my progra
on you I was simply saying it is working for yahoo and a way for you t
test it and see. I will refrain from posting in any thread that yo
have started or post in but if you want me to quit posting or change m
style please take it up with a moderator and if they feel fit I will b
happy to change my status to an onlooker.

Keith
www.kjtfs.co

Peter Benson · Feb 18, 2004

Just to close the loop on this. Yahoo were advised of the problem, they
acknowledged that there was a problem and they have now fixed the problem.
As at 17th Feb 2004 their quotes download now is now working correctly
again.

The problem was with the latest security patches to IE 6.0 -- their download
process wouldn't work with the latest patches.

Yahoo quotes error in Excel

Peter Benson

KJTFS

Sonny Kocak[MSFT]

Tom Ogilvy

Peter Benson

KJTFS

Sonny Kocak[MSFT]

Peter Benson

KJTFS

Peter Benson