URL Cell Reference

[zeeco]

My goal is to obtain data (specifically stock information from yahoo) by using different tickers **in cell B1**.

FB Income Statement | Facebook, Inc. Stock - Yahoo! Finance

Above is the URL for quarterly financials for ticker "fb". I would like to put the ticker in cell B1, run the macro, and obtain the data from that page. Below is the macro I recorded. Please let me know how to make the ticker in the URL variable and based on B1.

Thanks!

Sub GetDataEx()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=fb", Destination:=Range("$A$10"))
.Name = "is?s=fb"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-6
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

"URL;http://finance.yahoo.com/q/is?s=" & Range("B1").Value, Destination:=Range("$A$10"))

 

[zeeco]

Thank you. That worked perfectly. Now let's say I have more text at the end of this URL string, e.g.:

FB is the ticker symbol.

"URL;No Ticker Symbol - Yahoo! FinanceFB&annual", Destination:=Range("$K$14"))


"URL;http://finance.yahoo.com/q/is?s=" & Range("B1").Value & text("&annual"), Destination:=Range("$A$10"))</pre>
^^^ Would it be something like this? I am not sure how to add the text. (Obviously missing some basics of VBA). Thanks!

 

[Andrew Poulsom]

That would be:

"URL;http://finance.yahoo.com/q/is?s=" & Range("B1").Value & "annual", Destination:=Range("$A$10"))