Sub FastLookUp()
Range("G3").Select
Range("C1").Value = "LastTrade"
Range("D1").Value = "PrevClose"
Range("E1").Value = "Change"
Range("F1").Value = "Vol"
If Range("B2").Value <> "^GSPC" Then
Rows("2:2").EntireRow.Select
Selection.Insert Shift:=xlDown
Range("G3").Select
Range("B2").Value = "^GSPC"
Else
End If
ThisurSP500 = "http://finance.yahoo.com/q?s=^GSPC"
Set QT = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & ThisurSP500, _
Destination:=Range("G2"))
With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
End With
LastTrade = Range("H2").Value
PreviousClose = Range("H5").Value
Change = (LastTrade - PreviousClose) / PreviousClose
Range("C2").Value = LastTrade
Range("D2").Value = PreviousClose
Range("E2").Value = Change
Range("e2").NumberFormat = ".00%"
For Each n In ActiveSheet.Names
n.Delete
Next
For Each QT In Worksheets(ActiveSheet.Name).QueryTables
QT.Delete
Next QT
Columns("G:H").Delete
'End SP500
For Each sym In Range("b3:b" & Cells(Rows.Count, 1).End(xlUp).Row)
Thisurl = "http://finance.yahoo.com/q?s=" & sym
Set QT = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & Thisurl, _
Destination:=Range("G2"))
With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = """table1"",""table2"""
.Refresh BackgroundQuery:=False
End With
For Each n In ActiveSheet.Names
n.Delete
Next
For Each QT In Worksheets(ActiveSheet.Name).QueryTables
QT.Delete
Next QT
LastTrade = Range("H2").Value
sym.Offset(, (1)).Value = LastTrade
PrevClose = Range("H5").Value
sym.Offset(, 2).Value = PrevClose
TheChange = (LastTrade - PrevClose) / PrevClose
sym.Offset(, 3).Value = TheChange
sym.Offset(, 3).NumberFormat = ".00%"
Volumn = Range("H13").Value
sym.Offset(, 4).Value = Volumn
Columns("G:H").Delete
Next sym
End Sub
XML