Linking Excel to Yahoo Finance (or another website)

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

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

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

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

Click to expand...

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.

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

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.