Web Query (Excel 2003)

Topic

New Reply

Hi,

I would like to use a web query to download table relating to a number of
companies. This query will be run using the unique company identifier (ISIN)
which point to a cell where the identifier is stored.
How can I get excel to run down a column containing unique identifiers and
automatically pass it to the webquery.

Thanks in advance

cheers, francis

Reply | Quote

Replies

Does one web page contain info for all companies, or do you have to visit a different web page for each company?

If the former, does one table contain all the information, or is there a separate table for each company?

Reply | Quote

Hi Hans,

I will need to visit several web pages for the information and each company will have its table…..

The the procedure is to go to the sites. Select the menu companies, Then search by
the identifier, ISIN, to get the announcement items regarding a company and then
download it to excel.

For example, if I need to extract the announcements from this url : http://www.investegate.co.uk/
I have attached a sample list of ISINs for the London Stock Exxhange.

Appreciate your assistance.

cheers, francis

Reply | Quote

Doesn’t the site offer services to automate this kind of stuff, like direct URL’s to specific information? Send them an email, sometimes you’re surprised with detailed info on how to do things.

Reply | Quote

I’d follow Jan Karel Pieterse’s suggestion first. If you started running multiple queries to a website in quick succession, they might think you were attacking them.

Reply | Quote

Hi Hans and Jan,
Thank you for the pointer, but no response from the web. I wanna use Excel to access the web page and pull
the announcements portion into Excel. The program run like this:

I have the isin nos or company names in the column A
Once the data is downloaded, Excel checks to see if it says No
announcements. If there is an announcements, copy it to a sheet under
the company’s name.
Then runs the query again with A2 and so on….

If you have a better idea, I would like to hear from you. My main aim
is to track these companies announcements on dividend, rights issues,
tender offers, bonus, meetings, mergers and earnings.

It is important that Excel can automatically pass the identifiers /
company name’s to the web query and pull the relevant data, in this case Announcements.

thanks.

cheers, francis

Reply | Quote

I cannot help you with this, sorry.

Reply | Quote

Thanks for your response.

cheers, francis

Reply | Quote

I have a page on my site on creating a parameter webquery:

http://www.jkp-ads.com/articles/webquery.asp%5B/url%5D

Reply | Quote

Jan,
Thanks for the info. I know that there are some programs out there, but I want to learn by building this myself. Using the VBA code on your web page, How does one locate the connection string to connect, say to the following :
Sub WebQ()
Dim lRange As Long
Dim l As Long
Dim sWord As String
lRangeCount = Cells(Rows.Count, “A”).End(xlUp).Row
Range(“A1”).Select
sWord = ActiveCell.Value
For l = 0 To lRange
ActiveCell.Offset(1, 0).Select
If sWord = “” Then Exit For
Dim X As ActiveSheet.QueryTables
Dim strURL As String
Dim strResults As String
Set oX = X.Add(“URL;http://www.euronext.com/WHAT GOES IN HERE……/

I am stuck at : lRangeCount = Cells(Rows.Count, “A”).End(xlUp).Row, this supposed th loop thru column A
and also at the URL , the url which I copied shows :
http://www.euronext.com/pricesearch/0,5375,1732_
6204224_____89221,00.html?requestComesFromSearchBo xParameter=
true&instrumentType=5&instrumentSubType=&equitiesC hoice=4&mep=
89221&belongsToList=market_14&bondType=503&issuer= &couponType=
&tradingType=&NAME_FIELD1=&MIN_FIELD1=&MAX_FIELD1= &NAME_FIELD2=
&MIN_FIELD2=&MAX_FIELD2=

I am lost as to what to look for. . Appreciate your assistance in this.

Thanks in advance.

cheers, francis

Reply | Quote

Hi,

I’d change this part of the code to start with:

Sub WebQ()
Dim lRange As Long
Dim l As Long
Dim sWord As String
lRangeCount = Cells(Rows.Count, “A”).End(xlUp).Row
Range(“A1”).Select
sWord = ActiveCell.Value
For l = 0 To lRange
ActiveCell.Offset(1, 0).Select
If sWord = “” Then Exit For
Dim X As ActiveSheet.QueryTables
Dim strURL As String
Dim strResults As String
Set oX = X.Add(“URL;http://www.euronext.com/WHAT GOES IN HERE……/

To:

Sub WebQ()
Dim lRange As Long
Dim l As Long
Dim sWord As String
lRangeCount = Cells(Rows.Count, “A”).End(xlUp).Row
Range(“A1”).Select
sWord = ActiveCell.Value
For l = 0 To lRange
ActiveCell.Offset(1, 0).Select
If sWord = “” Then Exit For
Dim X As QueryTable
Dim strURL As String
Dim strResults As String
Set oX = Activesheet.querytables.Add(“URL;http://www.euronext.com/WHAT GOES IN HERE……/

Reply | Quote

Shouldn’t that either be:


Dim oX As QueryTable


or


Set X = Activesheet.querytables.Add("URL;http://www.euronext.com/WHAT GOES IN HERE....../


Reply | Quote

Well spotted Legare!

Reply | Quote