Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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.t o" .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.t o" .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.t o" 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.t o" 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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. -- Regards, Tom Ogilvy "Kent" wrote in message om... 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...9&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.t o" .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...9&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.t o" .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...9&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.t o" 2) With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://table.finance.yahoo.com/d?a=7...9&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.t o" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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 -- Don Guillett SalesAid Software "Kent" wrote in message om... 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...9&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.t o" .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...9&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.t o" .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...9&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.t o" 2) With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://table.finance.yahoo.com/d?a=7...9&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.t o" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
think you ignored my answer:
You can replace all your code with Thisworkbook.RefreshAll -- Regards, Tom Ogilvy "Kent" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries Truncated
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...9&f=2004&g=d&s =nt.to" "URL;http://table.finance.yahoo.com/d?a=7...9&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 "Kent" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link truncated | Links and Linking in Excel | |||
Hyperlinks are truncated | Excel Discussion (Misc queries) | |||
GIF File Gets Truncated | Charts and Charting in Excel | |||
Printing truncated | Excel Discussion (Misc queries) | |||
Picture will be truncated | Excel Discussion (Misc queries) |