Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Ticker Symbol

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/...=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...

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/...=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
    Regards,

    Gary Hewitt-Long

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •