Ticker Symbol

[scottbro]

Hi guys...I posted about this earlier, and have taken your advice about giving an example!

I would like to import the stock price for a ticker symbol in excel 2000 from the following webpage:

http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=GPS

The thing is, I know how to get the ticker onto my sheet, but along with it comes additional info contained within the same table. I however, would only like the current stock price imported (identified as "Last" in this case). I tried importing the whole table and deleting all the rest, but upon refresh, it refreshes the entire table. Any ideas as to how to import only the current ("Last") stock price!? Thanks guys, I appreciate the help...

 

[gplhl]

On 2002-02-28 12:10, scottbro wrote:
Hi guys...I posted about this earlier, and have taken your advice about giving an example!

I would like to import the stock price for a ticker symbol in excel 2000 from the following webpage:

http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=GPS

The thing is, I know how to get the ticker onto my sheet, but along with it comes additional info contained within the same table. I however, would only like the current stock price imported (identified as "Last" in this case). I tried importing the whole table and deleting all the rest, but upon refresh, it refreshes the entire table. Any ideas as to how to import only the current ("Last") stock price!? Thanks guys, I appreciate the help...

Could you not use a seperate sheet for getting the data, then delete all except the values you require. This should leave:

"Last" in cell A1 and the Value in B1.

Then link those cells to the sheet you want the data in.

You could have the sheet that gets the raw information hidden if needs be and then unhide and hide when the macro is running.

Sub TickerInfo()

Application.ScreenUpdating = False
Sheets("TickerInfo").Select
Range("A1").Activate

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=GPS", _
Destination:=Range("A1"))
.Name = "webquote.dll?ipage=qd&Symbol=GPS_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
'Remove all other data in query sheet
Columns("A:D").Select
Selection.Delete Shift:=xlToLeft
Columns("C:I").Select
Selection.Delete Shift:=xlToLeft
Rows("1:18").Select
Range("A18").Activate
Selection.Delete Shift:=xlUp
Rows("2:19").Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Sheets("Main").Select
Range("A1").Formula = "=TickerInfo!A1" ' puts information in main sheet
Range("B1").Formula = "=TickerInfo!B1"

Application.ScreenUpdating = True

End Sub

 

[scottbro]

gplhl,

Thanks for the tip...you know I never even thought of that workaround! It shouldn't bother me to have the extra sheet either...I'll give it a try no doubt! thanks again...