Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve stock quotes in Excel 2007
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MSN Stock Quotes Addin for Excel 2007 | Excel Discussion (Misc queries) | |||
How to edit MSN Stock Quotes in Excel 2007 | Excel Discussion (Misc queries) | |||
Get stock quotes in Excel 2007 | Excel Discussion (Misc queries) | |||
Stock Quotes for Excel 2007 | Excel Discussion (Misc queries) | |||
MSN Stock Quotes in Excel 2007 Beta | Excel Discussion (Misc queries) |