MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to pull stock quote via web query

Posted by Dwight on March 20, 2001 1:15 PM

I tried to record a macro to use a web query to pull a current stock quote (let’s say for the ticker symbol “SUNW”) from something called PC Quote Inc., which was already listed in the “queries” directory, paste the resulting information in cell X1 of my worksheet (the information actually occupies the range X1:AA17), copy the “last trade” price from cell Y5 and paste into cell D2, cleanup the fills/borders which the query had pasted into X1:AA17, and finish up back at D2.

In the process of recording the macro, I answered a prompt in a dialogue box labeled “parameters” by checking “use the following value: SUNW”, and I answered a prompt in a dialogue box labeled “properties” by checking “refresh data on file open”. I thought these would result in the macro automatically designating the stock I was looking for as it ran. Unfortunately, when I run the macro, it pauses at a dialogue box labeled “enter parameter value” and I have to enter the symbol and click “ok”, after which the macro finishes up. Anybody know a way to bypass the dialogue box? Is there a better place to go for the query?

ActiveWindow.SmallScroll ToRight:=12
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Queries\Detailed Stock Quote by PC Quote, Inc.iqy" _
, Destination:=Range("X1"))
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = True
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.SmallScroll ToRight:=-16
ActiveWindow.SmallScroll ToRight:=20
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
ActiveWindow.SmallScroll ToRight:=-20

Posted by Mark W. on March 20, 2001 1:24 PM

Dwight, why the macro when you can use Excel's
Data Get External Data Run Web Query... menu
command to imbed the query into a worksheet.
Later, subsequently this data can be refreshed
at will using Excel's Data Refresh Data command.

Posted by Dwight on March 20, 2001 1:54 PM

Much more elegant solution than mine; Thanks, Mark

Posted by Artem on March 21, 2001 7:54 AM

Re: Much more elegant solution than mine; Thanks, Mark

Hi guys,

Anbody knows where i can get good financial data web queries (.iqy) files? - besides the ones from Microsoft website. Thanks!