Results 1 to 7 of 7

Linking Excel to Yahoo Finance (or another website)

This is a discussion on Linking Excel to Yahoo Finance (or another website) within the Excel Questions forums, part of the Question Forums category; Hi- I'm wondeing if there is a way to link Excel to Yahoo Finance so that I can enter a ...

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Location
    Boston, MA
    Posts
    119

    Default Linking Excel to Yahoo Finance (or another website)

    Hi-

    I'm wondeing if there is a way to link Excel to Yahoo Finance so that I can enter a stock ticker symbol in a cell and in another cell have it pull up the company description from Yahoo Finance.

    Does anyone know if this is possible?

    Thank you.

    Jon

  2. #2
    Board Regular MarkAndrews's Avatar
    Join Date
    May 2006
    Location
    West Yorkshire, UK
    Posts
    1,963

    Default

    Data > Get External Data > New Web Query

    Paste in the URL, then click OK

    This will paste in the Yahoo Table with a live feed from the internet, you can then link cells to this information

    Mark
    Windows 7 Professional (64Bit) & Office 2010

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Location
    Boston, MA
    Posts
    119

    Default

    Mark-

    I'm able to get it to link to the page in Yahoo Finance, however, I'd like it to link to only the Business Summary on the page. Is this possible? Please refer to the link http://finance.yahoo.com/q/pr?s=GOOG

    Thank you.

    Jon

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    Sub Get1Call()
    'Run from Sheet Module!
    'Get Call Letters and Last Quote.
    Dim xmlhttp, QuoteP$, QuoteN$, myDT$, n1%
    Dim MessageC, TitleC, DefaultC, MyCall

    MessageC = "Enter your stocks Call Letters Below:" 'Set prompt.
    TitleC = "Get Call Letters!" 'Set title.
    DefaultC = "AMD" 'Set default.
    'Display message, title, and default value.
    MyCall = InputBox(MessageC, TitleC, DefaultC)

    'Create the XML document type reference.
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

    'Open a connection to the server and get call letters info.
    strURL = "http://finance.yahoo.com/q?s=" & MyCall & "&d=t"
    'http://finance.yahoo.com/q?s=IBM&d=t
    xmlhttp.Open "GET", strURL, False, "", ""

    'Send for the information needed!
    xmlhttp.Send
    'Return the html code page information for the call letters used.
    RtnPage = xmlhttp.ResponseText

    'If call letters are missing: get the next set or exit!
    On Error GoTo myEnd

    'If the lay-out of the web page is changed you must update the search below!
    'Find the "Last Trade:" label on the web page for the current set of call letters:
    myStartPS = InStr(RtnPage, "Last Trade:")
    myStartPF = InStr(RtnPage, "Trade Time:")
    myFinP = (myStartPF - 116) - myStartPS

    'Test helper!
    'MsgBox myStartPS & vbCr & myStartPF & vbCr & myFinP

    myStartNS = InStr(RtnPage, "Summary for ")
    myStartNF = InStr(RtnPage, "- Yahoo! Finance") - 109

    'Test helper!
    'MsgBox myStartP & vbCr & myStartNS & vbCr & myStartNF

    'The Ticker value is "52" characters right of the found label.
    myDatStartP = myStartPS + 52
    myDatStartN = myStartNS + 12

    'Get the actual text value from the web page!
    QuoteP = Mid(RtnPage, myDatStartP, myFinP)
    'QuoteP1 = Mid(RtnPage, myDatStartP, 1850)
    QuoteN = Mid(RtnPage, myDatStartN, myStartNF)

    'Get Time and date!
    myDT = Format(Time, "Medium Time") & " on: " & Format(Date, "General Date")

    'Show Ticker message!
    myTicker = MsgBox(Prompt:=myDT & vbCr & vbCr & MyCall & " ==> " & _
    QuoteP & vbCr & vbCr & QuoteN, Title:="Ticker!")

    myEnd:
    End Sub
    JSW: Try and try again: "The way of the Coder!"

  5. #5
    Board Regular
    Join Date
    Nov 2005
    Location
    San Diego
    Posts
    500

    Default

    Mark-

    I'm able to get it to link to the page in Yahoo Finance, however, I'd like it to link to only the Business Summary on the page. Is this possible? Please refer to the link http://finance.yahoo.com/q/pr?s=GOOG

    Thank you.

    Jon
    Jon,
    When you set up the web query you should see little yellow boxes with arrows on the web page. Each of these boxes represent individual tables on each. Click the ones you want to import. On the page you refer to there is one for "Business Summary" and another for the accompanying report.

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    This version of the above code will look in Cell "A1" for the Call letters and in Cell "B1" post the Call Letters full name!


    Sub Get1CellCall()
    'Run from Sheet Module, like: Sheet1!
    Dim xmlhttp, QuoteP$, QuoteN$, myDT$, n1%, myCall$

    myCall = UCase(ActiveSheet.Range("A1").Value)

    'Create the XML document type reference.
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

    'Open a connection to the server and get call letters info.
    strURL = "http://finance.yahoo.com/q?s=" & myCall & "&d=t"
    'http://finance.yahoo.com/q?s=IBM&d=t
    xmlhttp.Open "GET", strURL, False, "", ""

    'Send for the information needed!
    xmlhttp.Send
    'Return the html code page information for the call letters used.
    RtnPage = xmlhttp.ResponseText

    'If call letters are missing: get the next set or exit!
    On Error GoTo myEnd

    'If the lay-out of the web page is changed you must update the search below!
    'Find the "Last Trade:" label on the web page for the current set of call letters:

    myStartNS = InStr(RtnPage, "Summary for ")
    myStartNF = InStr(RtnPage, "- Yahoo! Finance") - 109

    'The Call Name is "12" characters right of the found label.
    myDatStartN = myStartNS + 12

    'Get the actual text value from the web page!
    QuoteN = Mid(RtnPage, myDatStartN, myStartNF)

    'Post Call Name!
    ActiveSheet.Range("B1").Value = QuoteN

    myEnd:
    End Sub
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    New Member
    Join Date
    Dec 2013
    Posts
    1

    Default Re: Linking Excel to Yahoo Finance (or another website)

    You may benefit from using my add-on, Spearian for Excel, http://SpearianForExcel.com, which provides access not just to Yahoo! Finance data, but also Google, Bloomberg, Fed etc.

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
  •  


DMCA.com