Can I edit a web query?

A

Alex

I want to download some data from the web which is in a
simple table format (7 columns x 50 rows). I use MS Web
Query to do this.

Is there anyway that I can use VBA to just download the
first row of the table? Web Query only allows the whole
table to be downloaded and I cannot find a way to edit
this using Excel to just get specific bits of data.

As always, advice is well received...
 
D

Don Guillett

If you give me the url and your present code, I will take a look at it. OR,
import to one sheet and use a macro to extract to another sheet.
 
G

Guest

Don Guillet

Ok. Take a look at...

http://finance.yahoo.com/q/hp?s=^SPX

You will see the main table giving historical data for
the S&P500 US Stock Market. My actual problem is as
follows.

Look at the bottom of the table. There is a line which
says '*Close price adjusted for dividens and splits'.
This line gets imported with the table. Every time I use
<Refresh Data> in my Excel spreadsheet, the inclusion of
that bottom line makes my first column (colA) expand to
accomodate that text. So I want to either

1) Just import the top line and add it to the table as I
go

2)Import the whole table minus the last line of text.

I agree with you that I could build a Macro to extract to
another sheet. It just seems to make the whole operation
one more step too many. Don't sweat on this, but if you
can help I would appreciate it.
 
A

Alex

Don Guillet

I apologise for multi-posting. I wasn't sure what the
best category was for my question. I shall refrain in
future.

Alex
 
D

Don Guillett

As to the multiple posting. It is usually just a waste of your time and
ours. The best place would have been programming or query. Your promise to
"not do it again" is appreciated.

AFAIK you cannot do what you want. As I suggested, you could create a macro
to copy to another sheet. OR, a macro to delete the objectionable line.
Something like:

rows(cells(rows.count,"a").end(xlup).row).delete

OR
You will like this group
http://groups.yahoo.com/group/xltraders/files/
Go here>join (won't hurt you)>download my FREE
"GetYahooMultipleHistory97a_P.xls" file. I think you will like it.
You can specify dates, type, as many symbols as you like. You have a chart
of the data by a simple doubleclick on the symbol, etc. Or, I can custom
design something just for you.
 
A

Alex

Don

I have created a Macro to eliminate the offending line.

However, another problem has occurred. In Excel, on a
single worksheet, I have 30 web queries. (Like the URL
link I showed you). When I select <Refresh All> not all
the data is updated correctly.

Going back to basics, I am in Excel and I select <New Web
Query> and then type in the URL (e.g. then one I showed
you). Here I can import the data table by selecting it.
As I understand it, I do not need to save the query so
long as I do not want the same data available to other
Excel workbooks.

However, come Tuesday 3rd August, there will be a new
line of data in the table (for data relating to Monday
2nd August). Does the URL have to change to accommodate
this? I wondered whether this was the source of the error
i.e. the URL I initially gave the Web Query only relates
to the original table and cannot 'dynamically' update.

Any thoughts on this? Otherwsie I just wondered whether
trying to <Refresh> the data for all 32 queries at once
was too much for Excel and this was causing the errors.
Some seem to update fine, others not at all.

Regards

Alex
 
D

Don Guillett

It appears you are trying to re-invent my wheel. Take my advice and goto
xltraders and download my FREE program.
 
A

Alex

Don

I took a look at your Excel sheet on xltraders group. A
useful download.

The problem I have is that I want to be able to see
multiple charts at the same time. I have embedded 4
charts onto a single chart sheet to do this. I think on
your design I can only see one at a time.

Nonetheless, can you still help with how to get external
data to refresh correctly? I am still having problems
with my Excel sheets.

I appreciate any further advice that you have.

Many Thanks


Alex
 
Top