I don't use Excel's web  query when accessing data via the internet. I
use XML and HTML strings to call web sites and parse data. However, I do
use lists within the workbooks, in my case ISBN'S, to send data, and
then get the return data via web forms. Looking at the way a web query
is executed via VBA I would imagine you could pass  each of your criteria
in a similar manner. Where your URL appears in the web query  change the
string like so..



"www.rittenhouse.com//product/product.asp?sku=" & sStringData



Use your web address and where you pass your data replace it with a
variable as shown. You can loop through the spreadsheet and pass each
criteria to this way. Hope this helps.

I have a free open-source add-in I wrote to get financial data off the
Internet. I originally wrote the add-in because of my frustrations
with web  queries in EXCEL.

For example, if I wanted to get the "Market Capitalization" of "IBM"
from the Yahoo Key Statistics page, I would use this function and
parameters from the add-in:

=RCHGetElementNumber("IBM", 941)

The first parameter passes the ticker symbol (which could be a cell
reference) and the second parameter is just an assigned element number
for that particular data element (which could also be passed as a cell
reference). That function has nearly 15000 available elements
preprogrammed. However, there are general usage functions that allow
you to retrieve web page source data, extract HTML tables, even
individual cells from and HTML table.

Thanks all.. I will have a look .. i have about 3500 companies so i
really have to get this thing automated.

my iqy file looks like this

WEB
1
www.investegate.co.uk/Index.aspx["words","Enter
or select isin:"]&Go=search&os=["os","enter or select the page no:"]

Selection=10
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

unfortunately the webpage display only the first 60 records or so..
and the page numbers os goes 0, 60, 120 etc(therefore teh last bit
after the go=search)

anyway i will have a look and will let you guys know..
thanks again..

Then you won't want to use my add-in. It has an arbitrary limit set
for about 1000 web  page retrievals, because it saves the source code
of each web page in case multiple data items are being retrieved from
the page. Unless I'm misinterpreting your statement. But if you're
really getting that much data, I would think there would be an easier
way to go about it?

Can you give an example URL? I couldn't tell what the valid values
might be for the parameters you were using in the web query.

my iqy file looks like this

WEB
1
http://www.investegate.co.uk/ Index.aspx? searchtype= 5&words=["words","Enter
or select isin:"]&Go=search& os=["os", "enter or select the page no:"]

Selection=10
Formatting=None
PreFormattedTextToC olumns=True
ConsecutiveDelimite rsAsOne=True
SingleBlockTextImpo rt=False
DisableDateRecognit ion=False
DisableRedirections =False

unfortunately the webpage displays only the first 60 records or so..
and the page numbers (os) goes 0, 60, 120 etc (therefore the last bit
after the go=search)

So that is the url. http://www.investegate.co.uk/ .What the site provides is the
RNS feeds of companies listed on the london stock exchange. It is provided free
of charge.

I am working on a paper which uses the news announcements data. My primary
interest is in earnings announcements data but i thought i would get the whole
thing and then filter it out, incase I need to use the other news announcements
later.

So the procedure is to go to the site. Select the menu companies, Then search by
ISIN ( the identifier) to get the news items regarding a company  and then
download it to excel. But with 3500 companies i need to enter the isins and the
page numbers manually each time and scrolling up and down to select the
destination cells. Just the kind of thing u want to get automated.

So ideally my program would look like this

I have the isin nos in the column A, the page numbers say 0,60,120,180...300
max. in column B.

The program selects C1. and then runs the webquery with cell  contents A1 and B1.
A1 for isin and B1 for os.

One the data is downloaded. The active cell moved down to the end of data in
column C. Checks to see if it says No announcements. If dosent say No
announcements Then runs the query  again with A1 and B2.

After that is done say there was only one of data for this company. So when the
program runs with A1 and B2 the value No announcement is returned. So the
program Moves to A2 ( a new isin number) and B1. Then A2 and B2 so and so forth.

I wouldnt mind just getting the program to run  down the isin column alone with
fixed values for the page number. As i can one page at a time for all the
companies. so os is fixed at 0 for the first 3500 runs. Then fixed to 60 for the
nest 3500 runs etc.

The key point is to get the program to move down automatically  to read and pass
the isins to the query.

My problem is that I have no idea what values would be likely for you
to use for the "words" parameter since I have never used ISINs.
However, using a different example, I was able to use one of my add-in
functions to work on a similar URL. This function call from my
add-in:

=RCHGetHTMLTable("www.investegate.co.uk/Index.aspx\
ndard&Go=search",">Announcement",-1,"",1)

..returns a range of data that contains 61 rows and 5 columns of data.
You could also use that function within VBA by using the add-in as a
reference library. For example:

Sub Test()
aData =
RCHGetHTMLTable("www.investegate.co.uk/Index.aspxn\
dard&Go=search",
">Announcement", -1, "", 1, pDim1:=61, pDim2:=5)
End Sub

"aData" would be a 61 row by 5 column array from which you could
extract the data you want. For example, it appears to me you'll need
to "fill in" the dates, since they don't exist on every row, just when
the date changes. I would think you'd want it populated on every row
if you are creating a "database".

So, you could wrap that statement up within a loop of "os" and "words"
to repetitively get and extract all of your data, generating the new
URL on each iteration. However, I would "batch" it up into manageable
chunks in case something happens during the processing. Or, you could
write the extracted data to a sheet on each iteration, appending to
the "database" as you go...probably adding the "ISIN" as an additional
column in the "database" you are creating?

i think i am begining to see some light now.. I have
to look closely at what u are suggesting as i am not really the world expert on
VBA..

The ISINS are a combination of alphabets and numbers .. for example
BMG0440M1029, GB0000021690, GB0000043694 etc..

This seem like the solution to which I have been looking for. Would
you guide me how to do this. I try pasting the formula into a cell  but
nothing work.

Thanks in advance.

The RCHGetHTMLTable() function is part of the add-in I mentioned in an
earlier message in the thread.

I pasted the same formula you provided earlier into a cell  and all it
appear in the cell is " Date ". I was expecting rows of data appearing
as you have mentioned .
Appreciate that you can describe the process.

The function has to be array-entered because it is returning a
2-dimensional array of values. That is, you have to highlight a
range, type  in the formula, then hit Ctrl-Shift-ENTER to array-enter
the formula. That's because an EXCEL function can only return a value
or values to a cell  or range that it has been given access to. When
you just enter the formula and hit ENTER, you've only given the
function the ability to return a single value into a single cell --
which is what you got: "Date".

The easiest way to expand that is to:

1. Highlight that cell again
2. Hold down the shift key and use the arrow keys to expand the
highlighted area over additional rows and columns
3. Press F2 to edit the formula
4. Hit Ctrl-Shift-ENTER to array-enter the function over the highlighted range
5. If you are missing parts of the table  and need more rows or
columns, hit Ctrl-Z to undo and repeat steps (2) through (4).

I sometimes use that technique as a trial and error process to see how
many rows and columns I actually need.

Thank you very much. I am able to extract the data from the web  site,
www.investegate.com provided above, but notice that its is static in
nature. How can I have Excel read the companies names or an
identifiers in Column A and give me the result of announcement or no
announcement in column B.

You have also provide a VBA code for the above using the addin as a
reference in VBA, when I try running the code, its give me an error at
the line : aData. I don't understand this, would appreciate that you
enlighten me on this as I am new to VBA.

You'd have to concatenate the ticker value to the URL address being
used in the function. For example, if your identifier is in cell  A1,
instead of this formula:

=RCHGetHTMLTable("www.investegate.co.uk/Index.aspx\
ndard&Go=search",">Announcement",-1,"",1)

...you'd use something like:

=RCHGetHTMLTable("www.investegate.co.uk/Index.aspx\
&words="&A1,">Announcement",-1,"",1)

> You have also provide a VBA code for the above using the addin as a
> reference in VBA, when I try running the code, its give me an error at
> the line : aData. I don't understand this, would appreciate that you
> enlighten me on this as I am new to VBA.

I would immediately suspect one of two potential problems:

1. The add-in was not set up as a reference library (but you indicate
that has been done?)

2. You pasted the code as shown in the email. It should have only
been three lines -- the SUB statement, the aData statement, and the
END SUB statement. The aData statement wrapped in my email reader, so
it might have in yours. You need to make it a single line (or use
continuation characters if going to multiple lines). For example:

Sub Test()
aData =
RCHGetHTMLTable("http://www.investegate.co.uk/Index.aspx?searchtype=2"
& _
"&words=standard&Go=search", ">Announcement", -1, "", 1, _
pDim1:=61, pDim2:=5)
End Sub

In column A1 of sheet 1, I paste the word 'standard' and paste the 3
line code in the worksheet code module.
When I run  the VBA, its shows the following dialog box :

The SMF addin had a web  page retieval error! Status code=500, Return
date :
<html>
<head>
<title>You have an error in your SQL syntax near "Limit 61" at line
1</title>

I placed the sample file in the files area of the SMF Add-in group:

finance.groups.yahoo.com/.../Requested%20Samples/Invest\
egate-Data-Collector.xls

I added a number  of comments. Hopefully they will help. I also added
a loop so multiple words could be retrieved on the same invocation of
the macro, since you'll need something similar.

[I wouldn't mind just getting the program to run  down the isin column
alone with fixed values for the page number]



Count the number  of rows in your column...

Dim lRangeCount as Long

lRangeCount = Cells(Rows.Count, 1).End(xlUp).row

The number 1 represents column A, 2 = B, 3 = C etc. The above statement
counts the number of rows with values in column A. Once you have this
you may loop through your code and select each value.

Dim l as Long

Dim sValue as String

Range ("A1").Select 'Assuming Column A is where you are beginning,
otherwise select appropriate column.

For l = 0 to lRangeCount

sValue = ActiveCell.Value

[Your Code Here]

ActiveCell.Offset (1,0).Select 'Selects next cell  which in
this example would be A2

Next



This is a basic example, but should lead you in the right direction.