Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query - Pulling Tables
I am trying to put together a 1-2 page snapshot for stocks which will include
everything you wanted to know about a stock before you buy or sell. So, far, I have been able to write the web querys to pull down all the information I need with the exception of the financial statements. What is different about the financial statement is that all the web sites have a toggle switch for yearly and quarterly data. I want to be able to pull both down. Also, the site I am trying to pull the data from is Reuters.com. I like this site better than Yahoo since they provide more data. This is an example of the code I used to pull one of the data tables. This code works fine, I just wanted to show you how I set it up using a variable for the stock symbol. Sub Estimates() ' Reuters - Estimates Dim StkSym As String StkSym = Sheets("Summary").Range("E1") €˜Cell where stock symbol is entered Sheets("Data").Activate With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://stocks.us.reuters.com/stocks/estimates.asp?symbol" & StkSym _ , Destination:=Range("AC59")) .Name = "estimates" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub This is the code I am having trouble with. How do I insert the variable so that when I enter in the stock symbol it will pull the table? Again, what complicates this is the fact that the link needs to identify if it is the annual table or the quarterly table. Dim StkSym As String StkSym = Sheets("Summary").Range("E1") Worksheets("Data").Activate With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://stocks.us.reuters.com/stocks/incomeStatement.asp?period=A", _ Destination:=Range("BB1")) .Name = "incomeStatement.asp?period=A" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub Sorry for the long message but I wanted to try to explain as best as I could. I appreciate any help you can provide Scott J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web Query Pulling Date Incorrectly. | Excel Discussion (Misc queries) | |||
Formulas pulling from other tables | Excel Worksheet Functions | |||
excel 2003 having problems pulling top table from web query | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming |