Need help with Excel web query

[doasidont]

I've used a procedure to pull stock price historical data from Yahoo. Yahoo has changed something and now the procedure will not work. With a worksheet named 'Web Data' I execute the following. On occasion, data does get to the worksheet, but most of the time (and I cannot figure out why) I get an error stating that "The file could not be accessed ...", and then "Invalid Web Query". Here is the code that I am using:

'e.g. pull Dow Jones data for Feb 14 thru Feb 15.
smo = 2 ' Feb
sda = 14 ' 14
syr = 7 ' 2007
emo = 2 ' Feb
eda = 15 ' 15
eyr = 7 ' 2007
fund = "^DJI"
Sheets("Web Data").Activate
Range("a1").Select
Selection.Clear
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/q/hp?&a=" & smo - 1 & "&b=" & sda & "&c=" & syr _
& "&d=" & emo - 1 & "&e=" & eda & "&f=" & eyr & "&g=d&s=" & fund
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh
End With

The url for this, which works under Internet Explorer, is:
http://finance.yahoo.com/q/hp?&a=1&b=14&c=7&d=1&e=15&f=7&g=d&s=^DJI

I think the '?' is causing the problem but don't understand why it is intermittent or how I can get around it.

Any help will be much appreciated. Thanks.

[RobDog888]

Moved

[Al42]

The '?" is the break between the URL and the arguments to the CGI that runs from that URL. The request won't work without it.

Debug the program and see if the URL actually gets set to http://finance.yahoo.com/q/hp?&a=1&b=14&c=7&d=1&e=15&f=7&g=d&s=^DJI every time. It should, unless one of the numeric variables is being treated as a string, in which case you're getting something like "a=2 - 1" instead of "a=1".

[doasidont]

Thank you for trying to help. I've copied and pasted the actual url into the url argument but still get an error like the following:

"The file could not be accessed. Try one of the following:
- make sure the specified folder exists
- make sure the folder is not read-only
- make sure the file name does not contain ... < > ? [ ] " etc.

This is followed by "Invalid Web Query"

I'm sure you're right about the '?' because when I remove it, Yahoo reports back that the item can't be found (different from the Invalid Web Query error). So looks like I have to be able to pass the '?' but something is throwing up on it and not letting it pass thru to Yahoo. Any suggestions? Thanks.

[doasidont]

Another variation. When I try to Import External Data from the Data menu directly onto the Excel 2003 worksheet, passing the same url, and hitting the 'Import' button, I get the same error from Excel (File cannot be accessed. Invalid web query.)

[Al42]

Maybe Excel just can't handle a CGI query. In that case, get the data using something other than Excel (like VB) and send the data itself to Excel.

[doasidont]

You lost me. I tried to use a VBA procedure (documented above). Is VB something different and could you give me a little start as to how I would do it? Thanks.

[doasidont]

More info. Before Yahoo changed its web location, I used a similar url, which contained a '?', without problems.

[Al42]

When I hit the Download To Spreadsheet button on the page, it imports with no problem. Maybe you need an &ignore=.csv on the end?

[doasidont]

I can also download to the spreadsheet using the button you mentioned, but I cannot use that technique. My VBA procedure runs through a loop which retrieves data for a set of fund names and date ranges. After each fund's data is retrieved, I process it to other spreadsheets and go to the next fund. With a click of a button I get all the data for all the funds. It would be too cumbersome to do one fund at a time, downloading data each time.

[doasidont]

Holy sh$$, I think you fixed it for me. Adding the &ignore=.csv seems to have fixed the problem. What does that do? Thanks a bunch.

[doasidont]

Strange. It worked for several interations of retrieving prices for different funds and then stopped working for the same error reason.

[RobDog888]

Where are you querying the stock quotes from? Perhaps could it be possible that querying it too often caused you to get locked out for some duration since it seemed to work several times?

[doasidont]

From http://finance.yahoo.com/q/hp?.

It does look like some kind of timing thing because if I restart (I have restart logic in the procedure) after the error, it sometimes does go through another iteration. Once it went through 20 funds, another time about 10, and another time would not do any at all, even after restart.

[RobDog888]

With stock quotes, you will have a 15 or 20 minute delay from real time quotes unless you are accessing a member login location where you are signed up to receive real time quotes.

You shouldnt be querying more then every 5 minutes or so I would think since its going to remain the same values for 15-20 minutes.

Or are you meaning you are having timing issues obtaining each indvidual stock?

[doasidont]

No, I understand about the 20 min delay. I am actually getting historical quotes for a number of different stocks. It seems like something in the workbook is not getting cleared out. I tried to out in a 10 second delay between queries but that did not work either.

[littlepd]

In a URL, the ampersand (&) symbol is used as a separator between parameters passed to the called page. In your example, you have the question mark followed immediately by an ampersand. This means you are passing nothing as the first parameter. Granted, since all the parameters are named, this shouldn't be a problem.

It just looks funny.

[doasidont]

I noticed that and corrected it. Here is the url I am passing:
http://finance.yahoo.com/q/hp?s=^DJI&a=1&b=14&c=07&d=1&e=20&f=07&g=d&ignore=.csv

If I paste this url into the address box on the Data/Import External Data/Edit Query box and try to 'Import' onto a new blank worksheet, I get the 'File could not be accessed' error followed by 'Invalid Web Query'.

The url gets out to Yahoo and retrieves the data. Excel just won't import it because of some file access problem (error 1004).

Are there any other http parameters that will correct this?

[doasidont]

More info. If I change the '^' character in the url to '%5E', I can manually import the data onto the worksheet using the Data/Import External Data/Edit Query. So it's the '^' that seems to be causing a problem. That's in fact what the Yahoo server does when it returns the data. You can see this in the address box. Problem is even if I change the '^' to '%5E' in the url, my VBA procedure is still not working.

[doasidont]

Sorry, but I was not correct about the '%5E' replacement. I am totally lost on this at this time. Nothing seems to be working and when it does, it's inconsistent. If someone could suggest how I can debug this, I'd appreciate it. Thanks. Or if someone can tell me what file Excel thinks is wrong and how I can correct that, it would help me a lot.

[doasidont]

This is looking like a problem with the Yahoo site itself because my original code started to work. It blazed through 30+ funds, then it started to slow down, and then it just stopped. I put in some error handling code to force a resume and if I wait long enough, the data is eventually downloaded and copied to the spreadsheet. But it looks like this has something to do with the Yahoo site and in particular this particular url dealing with historical stock prices. I've sent a message to Yahoo customer support.

[Investor]

Hello,

Is this still a problem for you?

Because I am having this problem on my home PC with excel 2003.

This problem started happening very recently and after trying everything possible... in my head.....I just googled my problem and stumbled upon your post. Noticed the date and time stamps on your posts and am convinved that something on yahoo side is causing the problem.

However, I tried taking this program to work and ran on a computer with down level of excel version and seems to be working fine.

my connection string looks like below

.Connection = "URL;http://finance.yahoo.com/q/hp?s=" & stocksymbol

if you have a solution please please pelase help!!!!!!!!

[doasidont]

I never got a resolution to this problem. My url was trying to retrieve historical stock prices. Sometimes it worked fine, sometimes it was slow, and sometimes it never worked. I have since used the url with Yahoo uses to download the data. You can see it when you right click on the 'Download to Spreadsheet' button on the Yahoo site. It gives me what I need and it works great all the time. Yahoo did response to my inquiry asking for more information but I have not yet replied.

[Monsterboy]

Same problem here, though I can provide no help. I'm trying to query company profiles from http://finance.yahoo.com/q?s= in Excel 2000 (using "http://finance.yahoo.com/q?s=" & Ticker & "&x=0&y=0" -- have also tried leaving off the "&x=0&y=0"). I get the same message. The urls open fine in my browser, of course.

Last night I went through 100 or so before it got stuck. Today I've I manged to get IBM 's profile to load as a test, but that's about it.

[aclvsh]

same problem here too, i m pulling closing price from urls like http://finance.yahoo.com/q?s=ibm, but got the "The file could not be accessed ..." message. the error happened after 30 queries (more of less).
and i m using excel 2003 on windows XP.

what is interesting is that it happened to my laptop after i install a few updates. but apparently my desktop works fine (i was lucky i try the updates on my laptop first). so i m guessing it has something to do with the updates may have disabled something. and it seems that the problem is very specific to yahoo finance.


updates that i installed that day:
for windows: KB927779, KB927802, KB928255, KB923723, KB924667, KB931836, KB926436, KB928090, KB918118, KB928843

for office: KB894542, KB924885, KB919029, KB924085, Service Pack2, KB920813, KB924424, KB920103, KB924085, KB929060, KB929057, KB925251, KB
929064, KB907417, KB929058 (Excel), KB914455, KB923097

[retsom]

This happens to me occasionally. I shut all open Excel and Internet Explorer windows. Open a new IE window, COMPLETELY clear out the caches (tools/Internet Options/General/Browsing history--Delete... then Delete all from the Delete Browsing History). This will probably delete more than necessary (deleting passwords could probably be saved) but it seems to do the trick.

[samq12345]

Did this work? What if I have 100+ stocks to go through?

[aclvsh]

i delete all the temp internet files (IE7) everytime before i click the refresh all button, and delete them again and again as the update proceed. seems to reduce the error msg by 3/4 or more. i update 500+ entries everyday, so it is still a bit annoying.

[samq12345]

ok thanks, I am sure one can write a code to delete the Temp files automatically

[aclvsh]

haha, i was just about to ask how to delete temp files by macro (i m a newbie)

and it seems that the error is really related to temp files...just wrote a macro to do web query refresh and run into the same "run-time error 1004" problem, and the problem is gone once i delete the tem file.

[samq12345]

no I don't but am trying to search the web for it. will post something if I come across something

[retsom]

Here's something that explains how to delete temporary files within a VBA macro. (Of course, you have to delete the files from wherever YOUR computer stores the temporary files).

http://vbadud.blogspot.com/2007/04/d...ary-files.html

[samq12345]

hmm... so does this delete the entire folder? cause I have a lot of ramdom files in my temp folder, and the names of those file keep on changing.

[samq12345]

Is there a way I can go a the temp folder and select all files and then kill the files?

[retsom]

The code deletes all the files in the specified directory. That's what the wildcard character ('*') does.

[retsom]

I should say that, as written, that code will delete all the files of the form *.tmp. You can write the code however you want.

[samq12345]

thank you for the info, I am new at coding so have many dumb questions
I am using IE 7 as my default browser(which is accessed through excel while running a web query) and the code does not recognize my Temp file folder. Also I have this code in a module in excel should that make a difference?

[samq12345]

Here is what I found-the temporary file's folder for IE7 is a hidden folder and I cannot change the settings on that folder, thats the reason why the code won't work.

Is there a way around this?

[retsom]

Yeah, apparently the kill statement doesn't work on hidden files.

You could try this to unhide the folder:

Setattr "C:\Documents and Settings\.......\Local Settings\Temporary Internet Files", vbnormal

You have to replace the above path name with the specific folder in which your IE7 sames temporary files. The above command will make your Temporary Internet Files folder unhidden. You should then be able to use the kill command.

[samq12345]

The vbnormal statement works, but here is what happens-
1. the history is stored is a folder called "content.IE5" which has subfolders and the names of these subfolders (which are hidden) change so I can't kill these subfolders as they are hidden and I can't unhide them as their names change
2. the vbnormal statement unhides the folders but they are still "read-only" so I can't kill them. Is there another statement to uncheck the readonly property?

thank you in advance