Macro to retrieve stock quotes in Excel 2007

[Andy]

According to MS Web site, the old Excel 2002/2003 macro to retrieve stock
quotes is not supported in Excel 2007 (although some, me not included,
supposedly got it to work on their Excel 2007). So I wrote a new one for
Excel 2007 which retrieves from Yahoo (not MSN). Posting here for others in
same situation I was in. To use the macro, select a cell with a stock or
mutual fund symbol and execute the macro GETSTOCK. The resulting stock quote
will be written into cell to the right.

-Andy




Sub GetStock()
Dim s As String
Dim ret As Double
Dim tempSheet As Worksheet
s = Selection.Value
Set tempSheet = ActiveWorkbook.Worksheets.Add
tempSheet.Visible = xlSheetHidden
With tempSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/cq?s=" & s,
Destination:=tempSheet.Range("$A$1"))
.Name = "cq?s=msft"
.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 = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ret = tempSheet.Range("C2").Value
Application.DisplayAlerts = False
tempSheet.Delete
Application.DisplayAlerts = True
ActiveCell.Offset(0, 1).Value = ret
End Sub

 

[Don Guillett]

So, for each quote you have to select a cell with the symbol>fire the macro which creates a sheet>creates an external query>copies the cell(testing in showed cell d2 instead of cell c2)>delete the temp sheet. At least you could loop it for each symbol in a list

You may like mine better which uses a yahoo web fetch to get all at once with ONE mouse click. If you want the file ask OFF list. I will NOT respond to requests made here as that is rude to the other users.
Updated> 07/09/08 10:18 AM $ 200.00
$ 15,550.00 $ 15,350.00
Symbol Name Basis Shares Last Trade Value Profit
IBM INTL BUSINESS MAC $ 100.00 100.00 $ 122.39 $ 12,239.00 $ 12,139.00
T AT&T INC. $ 100.00 100.00 $ 33.11 $ 3,311.00 $ 3,211.00
MSFT MICROSOFT CP - $ 25.65 $ -
FE FIRSTENERGY CP - $ 82.73 $ -