Need help with Excel web query

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.

Moved

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

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.

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

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.

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.

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

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?

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.

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.

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

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?

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.

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?

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.

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. ;)

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?

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.

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.

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.

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!!!!!!!!

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.

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.

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

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.

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

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.

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

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.

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

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

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.

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

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

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.

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?

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?

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.

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

Have you tried to refresh your stock quotes without deleting all the subfolders? Maybe what you have so far is good enough to make your macro work.

vbNormal should clear the ReadOnly attribute as well.

Hi All,
I've spent nearly 400 hours building a stock market evalation program in Excel '97 and VBA. The user writes calculation rules and Buy/Sell rules to evaluate when to buy and sell stocks.

I've included a 23 page PDF step by step.

http://www.geocities.com/bolin_chuck/index.html

I did not explain all of the features. I've have a much more updated version. I'd appreciate any feedback at [email protected].

I've started writing a specification to convert this concept program into a VB application.

Regards,
Chuck

I think I have solve the problem (I hope)...IE save the webpage under "q[*.htm", but there is a limit of how many such files can be saved (after some patch back in feb). when that limit is reached, excel can't access the webpage anymore and give a runtime error 1004. deleting these "q[*.htm" will clear up the space for excel to continue to access webpages.

I have written a two step solution:
first - to acquire the pathname of the temporary internet files folder from windows registry
second - to delete these "q[*.htm" files

Note: operating system files must be set to be visible, set it in folder option.
SetAttr to vbNormal is not going to work for the temporary internet files folder.

Hope that helps.




' Step 1
' Obtain the path name of the temporary internet files folder from windows registry
' Tested for IE version 7.0.5730.11 on Windows XP SP2 (both home and pro editions)
Dim oWSH As Object
Dim TempIntFilePath As String
Dim RegKey As String
Dim FileTypeDelete As String
Dim c As Integer
Dim answer

RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Cache"

If oWSH Is Nothing Then
Set oWSH = CreateObject("WScript.Shell")
End If

TempIntFilePath = oWSH.RegRead(RegKey) & "\Content.IE5"




' Step 2
' Delete yahoo "q[*.htm" files from the temporary internet files folder
' Note 1: Make sure operating system files is visible, otherwise will not work
' From any explorer window -> Tools -> Folder Options... -> View ->
' (Hide protected operating system files) uncheck
' Note 2: Delete all Temporary Internet Files before running this macro will improve speed
' Internet Properties -> General -> (Browsing history) Delete... ->
' (Temporary Internet Files) Delete files...
With Application.FileSearch
.LookIn = TempIntFilePath
.SearchSubFolders = True
.Filename = "q[*.htm"
.MatchTextExactly = True
If .Execute > 0 Then
For c = 1 To .FoundFiles.Count
Kill (.FoundFiles(c))
Next c
Else
MsgBox ("Error: no file found. Make sure system files is visible")
Exit Sub
End If
End With

hi guys-
i have had the same problem when querying tracking numbers from freight companies' web sites. after 50 queries it 'stopped' pulling info. what i did is downloaded the freeware application called ccleaner that erases the temporary files for you.
In Excel vba i placed this code to run every 50 web queries were made to clear the temorary files:
Call Shell("C:\Reports\ccleaner.bat", vbHide)
it opens batch file where the ccleaner is run. everything takes approx. 2 sec to clean and the bat file looks like:
cd C:\Program Files\CCleaner\
CCleaner.exe /AUTO
EXIT
the ccleaner has inbuilt option /AUTO that lets run the application out from cmd line. that's really cool.
after Excel runs the ccleaner and clears the temp files, the next batch of 50 web queries runs smoothly.
Dorota

I have been having the same problem and none of the suggestions worked to delete all the temporary internet files except dbucz solution.

The ccleaner did work. My macro still gives the runtime error 1004. but now i dont have to manually go clean the temp files. i just re-initiate the macro, ie, just hit the run button again. I have in my code to start from the last terminated position so no problem.

Although, I do wish even this problem to go away. Any clever suggestions might be helpful. I will post if I come up with something.

Thanks dbucz.

I faced similar problem after moving from XP to Vista and updating the system with a few 'Windows Updates'.
The Excel Web Query to Yahoo! finance which used to work flawless on XP, started working sporadically. While going through this thread I realized that IE's setting is culprit.

By default, IE setting for page refresh is 'Automatically'.
(Tool -> Internet Options -> General tab -> Browsing History -> Settings -> Temporary Internet Files)
I changed it to 'Every time I visit the webpage' and cleared the temporary internet files and the same excel query without any change started working.

HTH.

Hello,

i was able to overcome this problem in phases

phase 1

1 found this program on microsoft website
How To Use a 32-Bit Application to Determine When a Shelled Process Ends
http://support.microsoft.com/kb/129796
2. kept track on my queries and after a magic number of 30+ was reached i would call the ccleaner to clean my temp file

phase 2
found another poster who had sample code to download historical quotes from yahoo website using
Set occXMLHTTP = CreateObject("Microsoft.XMLHTTP")
apparently if you query a webpage using this method
you do not lauch IE in the background and that makes it very fast to download your information.

phase 3
found a yahoo group that has a file called
rch_stock_market_functions.xla
download this file, and adopt this in your code,
no need to run ccleaner

MAN THIS IS THE FASTEST WAY ON EARTH TO DOWNLOAD DATA FROM ANY WEBPAGE!!!!

hope this helps!