Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URL string, pulling in Stock Data
Below code (recorded by the macro recorder Excel 2003) pulls stock data into
Excel from Yahoo. I am SURE it can be cleaned up considerably, and I am just looking for a way to import pages until I get at least 100 days' worth of data. I can clean up the data later (remove splits, dividends, etc). My question is, for a web query to get a dump of stock prices for GM, e.g., given that up to 66 lines will be returned by Yahoo in my IE web browser, is there a clever (and with less properties than the recorder invokes) little routine that can do this? Thanks if any help. And, is there is a better site, a better method, for doing the same thing, but I need VBA to run this whole little show from just a range of stocks... Thanks!!!!!!! Dim URLString As String Sheets.Add ActiveSheet.Name = "GM" URLString = "URL;http://finance.yahoo.com/q/hp?s=GM&a=09&b=15&c=2004&d=01&e=31&f=2005&g=d" With ActiveSheet.QueryTables.Add(Connection:=URLString, Destination:=Range("A1")) .Name = "hp?s=GM&a=09&b=15&c=2004&d=01&e=31&f=2005&g=d " .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 = "31" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveCell.SpecialCells(xlLastCell).Offset(3, 0).Select Selection.End(xlToLeft).Select URLString = "URL;http://finance.yahoo.com/q/hp?s=GM&a=09&b=15&c=2004&d=01&" URLString = URLString & "e=31&f=2005&g=d&z=66&y=66" With ActiveSheet.QueryTables.Add(Connection:=URLString, Destination:=Range("A71")) .Name = "hp?s=GM&a=09&b=15&c=2004&d=01&e=31&f=2005&g=d&z=6 6&y=66" .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 = "31" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveCell.SpecialCells(xlLastCell).Offset(3, 0).Select Selection.End(xlToLeft).Select URLString = "URL;http://finance.yahoo.com/q/hp?s=GM&a=09&b=15&c=2004&d=01&" URLString = URLString & "e=31&f=2005&g=d&z=66&y=132" With ActiveSheet.QueryTables.Add(Connection:=URLString, Destination:=Range("A142")) .Name = "hp?s=GM&a=09&b=15&c=2004&d=01&e=31&f=2005&g=d&z=6 6&y=132" .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 = "31" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pulling certain characters from a string of text | Excel Discussion (Misc queries) | |||
Pulling Year from a text string | Excel Worksheet Functions | |||
Pulling stock quotes from Yahoo Finance into a spreadsheet | Excel Discussion (Misc queries) | |||
pulling stock quotes into a spreadsheet | Excel Discussion (Misc queries) | |||
pulling characters out of a string | Excel Worksheet Functions |