Linking Excel to Yahoo Finance (or another website)

[JRCBoston]

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

 

[MarkAndrews]

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

 

[JRCBoston]

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

 

[Joe Was]

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

 

[orbea_adam]

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.

 

[Joe Was]

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

 

[jiripik]

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.