Ok basically I have a list of 5300 stock codes.
What I need to do is grab certain data (market cap, average vol) from Yahoo Finance, for each one of them...
I've managed to record a macro that get's this info for me....I then cut and paste the parts I need where they need to go, and discard the rest.
Here is the code:
Sub getInfo()
'
' getInfo Macro
'
' Keyboard Shortcut: Ctrl+g
'
ActiveCell.Select
Selection.Copy 'This copies the current cell which is the stock code
ActiveCell.Offset(0, 6).Range("A1").Select
Application.CutCopyMode = False ' but then it forgets my copy when I go to import data from web.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=GS", Destination:=Range("$G$2"))
.Name = "q?s=GS"
.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 = """table2"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'This section just gets the market cap an av volume info which is what I need, discards the rest
ActiveCell.Offset(4, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(-4, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-3, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1:E7").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub
I have a couple of problems.
(1) As you can see I have attempted to copy the code then paste in the query field in yahoo finance....but the code is lost from copy when I go to import data from web. Obviously the data I am looking to grab is relative to the cell that is currently active so I need to get around that someway. I think we need to somehow reference the active cell in this line
.Name = "q?s=GS"
instead of GS we need to reference the active cell.
(2) It is pasting the data in an absolute cell....rather it needs to be 6 columns right of the active cell
(3) I then need this macro to run down the next 5200 lines of data and repeat the process
if anyone can help I would GREATLY appreciate it....I understand my code is a little messy with the recorder but it's 2am in Oz right now and things a getting a little fuzzy lol.
Bookmarks