Discussion on:

Pull data into Microsoft Excel with Web queries

Do you use Web queries to bring data into your Excel worksheets? Have you run into any roadblocks when trying to run them?

I have always loved using web queries in Excel. The only thing I don't like is that there is no feature that stores a username and password for sites that require it. A lot of sites allow you to view information related to your company, but they want you to login first. Perhaps they have added this in Excel 2007.

Edited

Message was edited by: beth.blakely@...

I just discovered this function but it does little good without an ability to get through login screens. Is there a solution, third party utility maybe????

very similar to "pull data into microsoft excel with web queris" i have prepared a macro to download historical data on stock prices(Indian stocks) and also draw a chart of parameters I am intersted in. (based on yahoo_finance codes)
this macro is tailored to my needs.
venkat
















where it not only donwloads the stock prices(indian stocks) and also draw a chart.

Hi venkat1926,

Can you post your script that pulls the data down and creates the chart?

Thanks
Mark

I'm needing to view several hundred tables of information that i can create a web query to with the wizard in excel. My problem is that i have to get to each one the first time, the hard way.

I'm sure its easy, but i'm a little lost. I'de like to retrieve the table several hundred times, but each new table will be using a different parameter.

I'm a little lost.

the page begins here:

http://www.bcbs.com/healthtravel/finder.html

And its a few selections to get to the right place.

Problem is, i cant change the zipcode parameter from excel, and just create all the other tables.

I know it CAN be done because i can dynamically connect to it with the wizard once i get to the right page. Any help would be very appreciated.

The later parts of the article show how to dynamically change the parameters passed to the Web query; you can change them with a VBA macro, or by pulling the values from a sheet in Excel.

If it is "a few selections" in a Web wizard, you may be out of luck, unless the final pages sends all of your selections all at once, in which case you can parameterize all of them.

J.Ja

I have been using VBA to access financial Tables on selected web pages, and have written a loop to allow the macro to go through each Worksheet in turn, but I get the message that the URL cannot be found - and it's because of that ? in the URL, which is an unacceptable symbol. How can this question mark be overcome .... I am using the sheet number and tab name as my looping variables. I am sure the answer is so simple, but I cannot find it ...any help, please?

Mike, did you resolve this one? I am having a similar problem using Excel 2003 - the problem seems intermittent. An older machine using Excel 2000 does not suffer the same difficulties. I've spent quite a while trawling google and can't find anything else out there on this problem. So would love to know if you got it resolved.

Could you show me the exact code? I may be able to help.

Thanks!

J.Ja

I have spent some time trying to work out why the question mark was throwing my code. I eventually (and rather belatedly) put in the line:

Application.StatusBar = SheetName

which indicated that I had an error in previous lines of the code that was not bringing up the correct variable for attachment after the "?" in the URL.

I think the running of the macro was disrupted as we live in an area where broadband is bad for continual connection. The length of time the macro was taking to run (I think) meant that breaks were occurring with the connection, causing problems with the code.

So, I do not think the question mark was actually the problem - I think it was a combination of the above factors.

Lesson to be learned - don't jump to conclusions; use all the debugging facilities available to you and work slowly through the code!!!!

Thanks for replying to my apparent problem.

Mike -

Glad it worked out for you!

J.Ja

When I run a web update the macro randomly lets me update records from 0 to 100?s depending on how it feels.
Steps that I have taken with some success or not.

1. Cleared and increased the size of cache, took the cache problem away but did not resolve web update random error. Still didn?t figure out how to clear cache within macro, Temp Internet Files folder is write protected and when I change that status it automatically goes back and KILL command does not erase write protected files.
2. Placed a 20sec delay after Update Table function it seemed to make longer Updates possible but once the program screws up the macro is dead even if you restart it- I might be able to update a few records but it dies pretty fast.

It seems that Internet is the problem because after macro screws up the Internet connection starts glitching, and that only is fixed with restarting the computer. I don?t know if it?s Internet connection or router that might be screwing up. Can you release Internet IP and Ping it within Excel? Does anyone have any ideas how to make this update function more stable?

Regards
William

I guess now i'm the only one in the world with this problem! It's a weird one. I don't think it's code related as even doing a manual web query from the menu bars results in a problem - but the crazy thing is that it's not every time (sometimes it works, sometimes not). When it fails i get an error dialog titled: "The file could not be accessed. Try one of the following" Where one of the options is to ensure the "file" does not contain any special characters (by the way the page i'm trying to return is: http://finance.yahoo.com/q/ks?s=A)

Any ideas appreciated, i even do error handling in the vba code to retry the site when the download fails, but it just seems to get totally stuck and unable to download. My XP/Excel2003 machine at work doesn't have the same problem. I've reset modem / router etc, all to no avail. I'm leaning towards a full reinstall of Excel and maybe XP.

Thanks.

If it happens even from the menu bar, I would definitely say that the problem is local to that one machine/install of Excel! I would definitely examine my antivirus/firewall system and make sure that Excel is fully patched before going the full reinstall route, though.

J.Ja

Thanks, it's certainly a weird problem. You made me realize that one major difference between when i last ran the web query macro successfully and now, was that i went from AVG to McAfee for my AV. Any tips for tweaking McAfee? I don't really know what i could change?

Also, once before i noticed that AFTER an Office Update my ability to do web queries was affected (i had to reinstall everything then).

But at this point i guess i have nothing to lose.

Thanks again.

I run AV on my gateway using IPCop (great system, BTW), so I do not have any locally installed AV or experience with it. Sorry I can't be of help there!

J.Ja

Dave,

I have exactly the same problem. I last successfully ran my macros on 07-May-07, then when attempting on 24-May-07 began to have intermittent problems and now have pretty much continuous problem.

A few point worthy of note:
1. The problem is only applicable to the Yahoo Key Services page (http://finance.yahoo.com/q/ks?s=) and not the other Yahoo tables such as Company Profile, Options or Summary.
2. The majority of other Yahoo pages also contain a "?" so pretty much eliminates that particular character as the cause.
3. Problem persists irrespective of running thru a macro or manually executing a webquery.
4. The problem is attached to the whole of the source webpage, as selecting xlWholePage or any particular table (no matter how insignificant) show the same error symptoms.
5. Problem is intermittent.
6. I have seen this problem before with other data sources (eg. the following worked fine up until about a year ago whence it seemed to get cut at source http://www.portfolio123.com/stock.jsp?symbol=)

I gather the problem is associated with a change at source on the Yahoo Key Services pages. Could anybody help me test a solution: I want to download these problematic web pages to a VBA string (which is almost certain to work as it will appear to the source page to be a web browser) then extract the data I want from there?

Any help greatly appreciated,

Collette

You're not alone in this one. Googling the error brings up a bunch of people with the same problem. The short version is the problem is in the way the web site is formatted (although noone seems to know the specific cause). To get around it, the only way that works for me is to keep looping the query until it works, checking for a cell which gets populated by the query:

On Error Resume Next
Do

Loop Until ThisWorkbook.Sheets("sheetname").Range("A1") ""

If this seems a little ordinary, others have found success with adding "&ignore=.csv" to the end of the url string but this didn't work for me.

After spending a day on trying to get around this, this is what I've learned.

When Excel executes a web query, it stores the query in the 'internet temporary files' folder. If the queries are of a certain type and too many accumulate in the folder, the 'file could not be accessed' error starts appearing.

Because this folder is 'special' i.e. a virtual folder, it is next to impossible to clear it out programatically. So you have two alternatives that work for me. The first is to manually clear out the internet cache through IE. The second, more surprising solution is if you reduce the space allocated to the cache through the IE advanced options to, say, 8M, the error, for me at least, goes away.

Before I could do 50 queries before the error appeared. Now, with IE open on a default page (unknown if needed) and Excel running, I'm going through 750 records and still going without error. It seems the cache limit auto-clears the web query caches sufficiently to stop the error from happening.

Bizarre bug. Hopefully Microsoft will look at how web queries are working and either fix the bug or provide the option to turn off the caching.

As part of a macro I am coding for recovering website data, I need to access websites for which I have my own passwords - for example, my bank account. Can I write a vba code macro for a web query which will allow me to access these websites; presumable the vba code will need to include the passwords but I cannot find out how to write the code. Any help gratefully received.

Mike -

For something like that, it all depends upon how the Web site implements the security. If it is using the HTTP 403 header system, you may be able to fake it out or have Excel save the username/password needed. For other sites, you may need to do something much more complex. Unfortunately, gathering the information needed usually requires finding a way to make requests and log the raw returned results, to see what HTTP headers get sent, or maintain a store of the cookies provided after authentication. For something like that, you will most likely need to "flip" the code, and use VB.Net or C# to do the authentication and downloading of the page, then dump the results into an Excel sheet via the Office tools.

J.Ja

If the website needs a password, I just open the website and login before I open the spreadsheet that contains the web queries. I leave it open and then open the spreadsheet. That seems to work for me. If I need multiple websites, I just open a tab for each of them and log in to each one.

You can take a look of the site below, it talked about how to pull stock quotes data from the web and with some ready-to-use script which you can modify to suit to your own need.

http://www.DIYTraders.com

Hi, Thanks for the site, I find it very helpful indeed. I don't know that I can do so many things with my Excel.

Thanks.

I wrote a VB Macro to pull 100's of tables off of a website and then process that information into a simple address list..

I am running into the same problem as others on here. After I get to 53 records, the Query drops the ball and pulls empty fields. If I have my script do anything while it's doing a query in the first 50, the same effect happens... I thought maybe excel was running out of memory with successive sheets being loaded, so I put a routine in to delete them as they were used, and that made the problem happen immediately.

Some where, there is a disconnect with the Queries and I don't know how to re-establish this connection... It's almost like it times out from the internet..

I have tried the deletion of the Cache memory, etc.. nothing is working for me..

I found a site that talked about doing a @HTTP function to establish a cache on all pages pulled, but I couldn't figure out how to put that into effect...

Any idea's would be appreciated..

Brian

Are you deleting the IE cache through internet options? If not, try this and also try throttling the size of the cache down to 8-16M. If no joy, go to the worksheet and manually refresh to see what the error is. Maybe this will bear fruit.

There is some sort of connection timeout or winsock kind of issue.. it's bizarre...

I'm looking all over the internet for some sort of window varable fix..

Whats so wierd is it's always after 53 webpage pulls.... i'm going to see if i can do a manual reset within the program..

http://techrepublic.com.com/5208-10879-0.html?forumID=5&threadID=200975&messageID=2365064

Not sure if I posted correctly,

Thanks for your reply.
I use Excel to calculate a few numbers which update possible 3 times a minute at certain times of the day. (Sometimes night, but mostly day).
=====================================

I have seen this kind of issue before, usually when working with huge amounts of data within a macro. Try disabling auto-calculation from within your code at the beginning of the script (re-enabling it afterwords), that may help.
Reply
Nope, makes no difference.
Personally I don't think you have seen this issue specifically.
=========================
If you are using a lot of formulas to do calculations,
Reply
Only 1 sheet has many formula, they are a type of counters
11 groups, 3 counters per group.
==========================
replace those with calculations within the macro. If that still does not do the trick, you are going to need to turn this code "inside out" and do it in Visual Basic or C# with the VSTO library.

Reply
I don't see the point, it is not that big a deal to simply do a few calculations in real-time.
However, if the problem is not solved, then I might have to wait, Novel seem to be working on a idea
http://www.linux.com/articles/58348
===================================

I may add, your code looks pretty bad. For starters, that URL doesn't work.
Reply
I did not think the URL was important for the problem of a workbook freezing when I first posted my version of this flaw, but if you need to look at the web page that I do use for the query, it's
tabonline.com.au
It's only during the day when this page is "Active".By Active, the numbers keep changing.
=========================

And second of all, it is clear that you are completely missing the point of a Web query in Excel. It is not for refreshing a page as fast as possible
Reply
Speed is not that important, stability and accuracy is though.
================================
(you didn't even put a delay in there!)
Reply
I do use Delays, 2 lots of 3 seconds.
Before and after the Loop
==============================
hoping for a value to change,
Reply
Yes, the values DO change, that is why I have a Loop and a Do Until
If certain values are met it either becomes
True or False
===========================
it is for pulling data once, or on page load, to be used in calculations or reports.
Reply
Correct, once every 5 minutes, but needs to check for changes up to 3 times a minute.
============================
The fact that Excel is not handling your infinite loop does not surprise me in the least.
Reply
No, nothing surprises me much either, all I want to do is have the machine (computer) add a few numbers from the internet.
After all, what is a computer for ?
Glorification of the humble calculator ?
========================

I have indeed seen these exact symptoms that you describe, just not under these circumstances. What they diod have in common is a ton of long-term looping.

The code you posted does not match your statements above. For example, you state that you have a delay in there. Where is the delay? This thing is repeating over and over and over again. On top of that, it keep redefining the Web query (not a great idea either, at the very least, it will merely be slow).

Infinite loops, particularly those that keep redefining objects, are often not accurate or stable. You really need to clean this code up. At the very least, no one here can help you if the code you post does not match the code you are looking at!

J.Ja

I'm trying to create the equivalent of your sample xls for the following URL:
http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=GOOG&lstStatement=Income&stmtView=Ann

Creating the IQY is no problem:
WEB
1
http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=["Stock"]&lstStatement=Income&stmtView=Ann

but when I go to edit the query to ensure I only get the table back, Excel does not use the parameter and this prevents me from editing what gets returned.

My guess is I'll have to use VBA to replace the whole connection string but I'm not yet sure how to do this.

All help appreciated.

Leon -

Unfortunately, someone at Microsoft really goofed on this on! I encountered similar problems, but there is a workaround as described in the article.

When you are editing the query to select the correct table, Excel is actually using a full blown Web browser. This lets you do what you need to do on the site to display the correct table, and then select the table. Your parameters will be retained for usage in the future, and Excel will know which table you want to be getting the data from.

J.Ja

If I'm missing something, my apologies. When the query in my case goes to:
http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=["Stock"]&lstStatement=Income&stmtView=Ann

The site redirects and the format is completely different preventing me from selecting the correct table.

Leon -

Yes, that is *exactly what we expect to happen* because Excel did not ask for and pass the parameters! But you *should* be getting a Web page on the right site (or close to it). From there, do whatever you need to do (probably find the search page and execute the search) to get the *right* page to come up. That is the workaround. The right page cannot come up when Excel did not ask for the parameter values. That's why this is a workaround, because Excel does not work correctly on its own.

J.Ja

After much angst, I got it working.

Public Sub GetHistoricalStockPrices(ByVal StockSymbol As String)
ThisWorkbook.Sheets("Profit and Loss").QueryTables("Stock Prices").Connection = "URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=[""Symbol""]&lstStatement=Income&stmtView=Ann"
ThisWorkbook.Sheets("Profit and Loss").QueryTables("Stock Prices").PostText = "Symbol=" & StockSymbol
ThisWorkbook.Sheets("Profit and Loss").QueryTables("Stock Prices").Refresh

End Sub

This effectively bulldozes over the connection string used to format the table but, as you say, Excel remembers.

Thanks for the great article. I've been wanting to do this for ages and you've now given me the nudge to make it happen

Public Sub GetHistoricalStockPrices(ByVal StockSymbol As String)
ThisWorkbook.Sheets("Profit and Loss").QueryTables("Stock Prices").Connection = "URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=" & StockSymbol & "&lstStatement=Income&stmtView=Ann"
ThisWorkbook.Sheets("Profit and Loss").QueryTables("Stock Prices").Refresh


End Sub

Leon -

I'm happy you got it working!

J.Ja

Hello,
I have been searching for a solution to my Dynamic Web Query for nearly 12 months, this thread is as best as it can get to describe a similar problem I can identify with.

Here's what happens with my version of things.
It happens on both Excel Office 2000 and 2003
Same issue on 2 different computers, both running XP Pro.
The code I use is:
Sub getmyinfo ()
Do Until _
Sheets("Sheet5").Range("D4").Value = "Apples" Or Sheets("Sheet5").Range("D9").Value = "Oranges"
On Error GoTo notfound
With Selection.QueryTable
.Connection = "URL;http://mywebaddress.com.au/" & Sheets("Sheet5").Range("A1") & ".html"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

'Code
Loop
'Code
End sub


After 20 - 30 minutes it will stop.
If I F8, it will hi-Lite in yellow, but VBA will not respond.
At times I was not able to use any cells in any sheets.
I have upgraded Office with the lates patch releases etc, still the same.
But it goes another "dimension" worse than I have read here.
When it happens;
I can close the workbook, or 2 workbooks because I have tried using a seperate workbook just for the web query to see if it could be solved.
Nothking seems to work.
Then, I have to close Excel, but after both or one workbooks are manually closed, an Error messsage will say,
"Cannot shut down Excel"
The only way to shutdown Excel is from the task manager using Cntrl/Alt/Dlete.
I then can run the macro again, for another 20 minutes, then all of a sudden it will freeze up again and manually do the same routine as described above.

I have tried much of what I have read here, including no AV or FW.
I have tried on a HD with Office installed only and still the same.

How can I solve this problem please ?

I have seen this kind of issue before, usually when working with huge amounts of data within a macro. Try disabling auto-calculation from within your code at the beginning of the script (re-enabling it afterwords), that may help. If you are using a lot of formulas to do calculations, replace those with calculations within the macro. If that still does not do the trick, you are going to need to turn this code "inside out" and do it in Visual Basic or C# with the VSTO library.

I may add, your code looks pretty bad. For starters, that URL doesn't work. And second of all, it is clear that you are completely missing the point of a Web query in Excel. It is not for refreshing a page as fast as possible (you didn't even put a delay in there!) hoping for a value to change, it is for pulling data once, or on page load, to be used in calculations or reports. The fact that Excel is not handling your infinite loop does not surprise me in the least.

J.Ja

Step (1) I set up an IQY with the following:

FILE START:
WEB
1
http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=["Stock"]&lstStatement=Income&stmtView=Ann
FILE END:

Step (2) I linked Stock=A1 and ran it. Works fine gives me a bunch of stuff i do not want. I change A1, it changes the Query below dynamically.

Then tried to do workaround and select specific table (See Desired Results tab)

Step (3) Right Clicked on Query --> Edit Query. It brought up mini window with a format that did not have any of the tables i was looking for. IF i enter a symbol and navigate to get the Annual Report Statement the query is no longer dynamic can't change symbol in A1 and get updated query

Step (4) Then tried adding the Macro:

Public Sub GetHistoricalStockPrices(ByVal Stock As String)

ThisWorkbook.Sheets("Current Results").QueryTables("Web_4").Connection = "URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=" & Stock & "&lstStatement=Income&stmtView=Ann"
ThisWorkbook.Sheets("Current Results").QueryTables("Web_4").Refresh

End Sub

Step (5) I still can't get the desired results - how can i get the the tables in the Desired Results Tab? I have followed the article and Tried selecting the right table in Step (3) but I had to change the URL with a specific symbol to see the table. HELP! I'd rather not use Macro and use the workaround with IQY and Queries. Can post excel if i figure out how.

I hate to say it, but you really need it. The Web query system is one case where Excel handles so much for you automatically that it breaks down the moment reality is different.

J.Ja

When i did the IQY and put the Macro in the public format, i still wasn't able to generate the dynamic query. It gave me the world instead of the table i wanted.

So i went into Edit the Query to find the table i wanted, i HAD to select a stock symbol to be able to navigant to the correct Table. This then replaced the original query which had Symbol=['Stock'] with Symbol=HD. So that if i changed the cell in excel to another symbol it always updates the query for HD.

I am still having problems finding the correct table when i edit the query because that breaks the dynamic search of ['Stock'] Does that make sense?

... using a macro to dynamically change the query string to include the right stock symbol, once you have the query set up. That is my best suggestion at this point.

J.Ja

hi

I wanna recreate the msnmoney web query but for yahoo cauz msnmoney doen't take OTC BB stock.

I have try to do it but it only importe the data for the first symbol in a block.

I want to do it for a batch of files and I want to have it in long and not a block. Can someone help me on this ??

is there a way to record a macro to do it ??

I have a spreadsheet whre I loop through a series of tickers. It can be done but you do need to create a VBA script which loops and then resets the active cell to the next ticker and reset the query string based on this value.