Web Queries Truncated

[Kent]

Good Morning,

I have viewed many discussions relating to web queries from Yahoo
stock quotes but I have yet to find information on the following.
Is there a truncated code to perform the following function:

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=nt.to"
_
, Destination:=Sheets("Sheet1").Range("A3"))
.Name = "d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=nt.to"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=zl.to"
_
, Destination:=Sheets("Sheet2").Range("A3"))
.Name = "d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=zl.to"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub


I would like to perform this task to a list of stocks, however, I am
sure my coding is redundant. Is there a way to simplify the above to
state the connections such as:

1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=nt.to"
_
, Destination:=Sheets("Sheet1").Range("A3"))
.Name = "d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=nt.to"

2)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=zl.to"
_
, Destination:=Sheets("Sheet2").Range("A3"))
.Name = "d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s=zl.to"

And then the conditions to apply to all my queries:

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False

This would really help condense the coding issues. Any help would be
greatly appreciated.

Sincerely,
Charles Kent

 

[Tom Ogilvy]

Once your web queries are built (which they were when you recorded the
code), you should only need to do a refresh to get the latest data - not
rebuild the querytable.

 

[Don Guillett]

If you really want to get a separate page for each symbol you need to change
to a loop to
1. change the active sheet or use the for i=1 to 12 (or whatever number)
2. change the desitination sheet in the loop
3. refer to a list of symbols

Or, goto xltraders and get my FREE file.
http://groups.yahoo.com/group/xltraders/files/

GetYahooMultipleHistory97a_P.xls

 

[Kent]

Hi Tom,
Thanks for looking at my question, however, I think you have over
looked my concern. I am interested in knowing what I can do to avoid
rewriting is code:
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

for each and every query I create. Can you help me with this problem?

 

[Don Guillett]

Again, Do you really want a separate worksheet for EACH symbol? Do you
really want to re-create each worksheet each time you want an update? Did
you download my file with interactive chart, etc.? What is it you want to do
and what will the finished product look like? EXACTLY.
You are probably making this more difficult than it should be.

 

[Tom Ogilvy]

think you ignored my answer:

You can replace all your code with

Thisworkbook.RefreshAll

 

[Kent]

Don,
You are probably correct in that I may be making things more
difficult than is necessary. I reviewed your *.xls sheet and I was
very impressed. Using your file as a reference I hoped to build
something simular in that it retrieves historical data (open, close,
volume etc.) for variable dates x to y for each stock (of my choice)
and places this information into its specific worksheet. As time
increases I would like certain stocks in the file to update (the
ability to be more picky as t +). The reason for this is that I have
developed analysis macros to systematical review each stock relative
to the S&P 300, their sectors and independent performance set by my
personal criterias. The part in the puzzle that I require help with is
in the development of the retrieval query. As I mentioned, I reviewed
your file and I was hoping to use/feed off part of it by linking your
'enter stock symbol' to my list of symbols, however, the file was
locked and prevented me from doing so with a macro. It is your output
'data' that is of interest to me and the apparent simplicity of the
file.Could you please expain to me how to create a web query as simple
as yours with the ability to feed off a list of symbols? I know how to
save the results to the sheets I have, which are in other reference
workbooks. Thank you for your interest in my problem! If it helps to
know I am using office xp.

Regards,
Charles Kent

 

[Don Guillett]

As I mentioned in my first post to your question, you would need a loop
instead of a separate sheet for each symbol.

for each symbol in symbols

modify your url here
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s
=nt.to"
"URL;http://table.finance.yahoo.com/d?a=7&b=27&c=2004&d=10&e=29&f=2004&g=d&s
=" & symbol & ""

use the data as desired with another macro to copy to the sheet desired.
As you saw on my program, I bring to the data sheet and then extract the
values to the desired column on the main sheet. This could be modified to
copy to a new sheet if desired.

next symbol

As Tom said, you could have it set up and just modify the symbol for each
refresh.
======


Don Guillett
SalesAid Software
(e-mail address removed)