Automating and looping a Web Query

[abesimpson]

My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.

My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:\XXX\Symbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat

Any and all help would be greatly appreciated.


abe

 

[John_w]

There really isn't anything you could be doing wrong which might cause this. Try running the code from the VB Editor. First, display the Immediate window (View - Immediate Window) so that you can see the debugging events. Then place the cursor anywhere within the Retrieve_All_Symbols subroutine and press F5 to run it.

I'm sure the crash is happening in ActiveSheet.QueryTables(queryName).Refresh BackgroundQuery:=False) every time.

I Googled "Code Execution Has Been Interrupted", and http://www.mrexcel.com/forum/showthread.php?t=70620&page=2 says that Adobe Acrobat was the culprit. Maybe something like this is causing the problem.

Maybe one of the real Excel experts can help...

 

[abesimpson]

The machine I am running this in has almost nothing installed except office, SP# and various post SP3 updates. No acrobat or anything exotic.

I am running an abbreviated TickerSymbol list:
a
aapl
ibm
rimm
ge

On several runs the Immediate window indicates the first symbol is retrieved OK, the second symbol freezes and the debugger highlights "If Err.Number = 0 Then"

Can I ask if you have tried this again? If you do are not having the same problem I will reinstall WinXP and give it another go (yes i know this is a stupid idea, but I have run out of even marginally smart ones).


Thanks John

a

 

[John_w]

All the tests I have done have been successful.

I ran the full ticker list with the .iqy file method yesterday, stopping it after 4500 symbols.

Did the same with your abbreviated list. Then together with your posted .iqy file. Then with the properties in Create_Dynamic_Query_From_Iqy_File corresponding to your .iqy file commented out.

Perhaps Yahoo are blocking your IP address because you have retrieved so many symbols. Can you move the machine to a different location with a different IP address and run the program there and see if it works? If it crashes in the same way then that rules out the IP address theory and points to something on the machine itself. Therefore try it on a different machine.

Does http://finance.yahoo.com/q?s=MSFT etc. display okay in IE and Firefox?

 

[abesimpson]

John,

Thank you for taking the time to run the tests. I had considered that Yahoo might be blocking my IP address but thought it unlikely as I can retrieve tables manually with no problem.

One last question re this; did you run the version of your script that I posted? Perhaps I did something wrong (though I can't see what it might be) and messed up your code.

I will go out and run tests later today on another machine and report back.


Thank you for your time and patience.

a

 

[John_w]

The tests above were with my own script, which is basically the same as the one you posted. I've just run your version several times with the short list and it worked fine. I only had to edit your version to change the location of files.

 

[abesimpson]

John,

Thanks again for testing my script.

It appears I may not be able to get to an outside computer today; I will report my results back as soon as I do.

You have been extremely patient.

a

 

[abesimpson]

John

I finally got out to test the macro at a different IP address and it worked!!! Then I tested it back on my machine and it also work.

Sooooo, since I was always able to call an update of the spreadsheet by manually entering a new ticker in cell B2 I guess that it would be reasonable to assume that I was on some kind of a (temporary?) list that blocked rapid calls for new data; or something like that.

Unfortunately it's impossible to be sure.


Thank you for all your help.

a

 

[rafilip]

John

I finally got out to test the macro at a different IP address and it worked!!! Then I tested it back on my machine and it also work.

Sooooo, since I was always able to call an update of the spreadsheet by manually entering a new ticker in cell B2 I guess that it would be reasonable to assume that I was on some kind of a (temporary?) list that blocked rapid calls for new data; or something like that.

Unfortunately it's impossible to be sure.


Thank you for all your help.

a

Hello,

just finding this after many many months of searching - guess I was using thw rong terms.

I have a spreadsheet where I want to calculate my distance traveled on a plane - say - SFO-JFK. Instead of getting all of that data into a spreadsheet and writing the proper formula, I can pull the data from a website (Great Circle Mapper). So far I have done the web query to pull the data in and then I can write the static data into my formula.

But what about when I want to change the URL from SFO to MIA (Great Circle Mapper), how do I get the query to update automatically when an input cell is changed so that it can pull the new data into the formula?

e.g.
A1: SFO
A2: LGA
A3: +HYPERLINK("http://www.gcmap.com/dist?P="&A2&"-"&A3)
[This directs me to the proper URL]

Now I am trying to pull the information from the website and use the 2579 [the mileage] in a later part of the spreadsheet.

e.g.
Multiply 2579 by number of times route flown

Thank you for any and all help, sorry if syntax sucks - I'm just starting with automation and querying URLs.

 

[VINEXL]

Hi

I have tried out the macro and it is great my only concern regards the type of data you can download with this app. Just to be clear, I would like to download all the rest of stock data retained by Yahoo using the standard special tags, I have tried out to insert them in the web query file but I am still getting the same initial information.

This is the code I have inserted in the web query file:

WEB
1
http://finance.yahoo.com/d/quotes.csv?s=["quote symbol","Enter cell containing quote symbol, e.g. =B1"]&f=nxj1f6b4abej4rr5dqr1yt8

Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False



A second issue concerns how the output data are formatted; I get the data tabulated vertically, is it possible to have them displayed horizontally for an easier importing in other applications?

WNGRF,
Search Finance
Search Web

Prev Close: 74.61
Open: 75.76
Bid: N/A
Ask: N/A
1y Target Est: N/A
Beta: 0.803902
Next Earnings Date: N/A

Day's Range: 75.76 - 75.76
52wk Range: 68.93 - 87.20
Volume: 100
Avg Vol (3m): 1,035
Market Cap: 9.67B
P/E (ttm): 28.18
EPS (ttm): 2.69
Div & Yield: 1.28 (1.70%)

Forward P/E (1 yr): N/A
P/S (ttm): 0.29
Ex-Dividend Date: 11-Jun-15

Annual EPS Est () : N/A
Quarterly EPS Est () : N/A
Mean Recommendation*: N/A
PEG Ratio (5 yr expected): N/A

A last point regards the output saved by the macro, is it possible to have all the stocks data files saved in one single workbook?

Many thanks for your support!!!!!!!!!!