Historical Stock Quotes - Yahoo Finance

[Jason]

I am beginner with VBA, so any help is appreciated. I would like to have
some code that I could run from a button on an Excel sheet which would pull
historical stock information from Yahoo Finance depending on various inputs
specified by the user (Ticker, Start Date, End Date, etc.).

I've used the macro recorder and the New Web Query functionallity to come up
with the following code, but I need help adjusting it so that I can get it to
function based upon user input specified on a sheet. Here is the initial
code from the macro recorder:

Range("A14:G2000").Clear
Range("A14").Select
With Selection.QueryTable
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=2&b=15&c=2004&d=5&e=15&f=2005&g=w&s=IBM"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub

I'm trying to adjust this to allow for the interactive inputs by defining a
name for the range where ticker is located, and then substituting this name
to where "IBM" is located in hopes that when I changed the ticker to
something else and run the code it would then update with that tickers
historical data (and same idea with other inputs). This doesn't seem to be
working. Can anyone help? I've posted the code that I have so far below.
Thanks.

Dim StartDay As Integer
Dim StartMonth As Integer
Dim StartYear As Integer
Dim EndDay As Integer
Dim EndMonth As Integer
Dim EndYear As Integer
Dim Ticker As String
Dim Period As String

StartDay = Range("C7")
StartMonth = Range("C6")
StartYear = Range("C8")
EndDay = Range("C10")
EndMonth = Range("C9")
EndYear = Range("C11")
Ticker = Range("C4")
Period = Range("C3")

Range("A14:G2000").Clear
Range("A14").Select
With Selection.QueryTable
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=2&b=15&c=2004&d=5&e=15&f=2005&g=w&s=IBM"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

 

[Tom Ogilvy]

"URL;http://table.finance.yahoo.com/" _
"d?a=" & Range("A1").Value & _
"&b=" & Range("A2").Value & _
"&c=" & Range("A3").Value & _
"&d=" & Range("A4").Value & _
"&e=" & Range("A5").Value & _
"&f=" & Range("A6").Value & _
"&g=" & Range("A7").Value & _
"&s=" & Range("A8").Value

Change the Range References to match you locations.

 

[Don Guillett]

I think using the csv method should be much faster. Here is one I use in a
free file at xltraders. You may want to go there and get it.

Goto http://groups.yahoo.com/group/xltraders/
and look for author donalb36 and download my FREE files

myurl = "http://table.finance.yahoo.com/table.csv?a=" & StartMo & "&b=" &
StartDay & "&c=" & StartYr & "&d=" & StopMo & "&e=" & StopDay & "&f=" &
StopYr & "&y=0&g=" & [e2] & "&s=" & c & ""

 

[Jason]

Thanks Tom, it worked.

"URL;http://table.finance.yahoo.com/" _
"d?a=" & Range("A1").Value & _
"&b=" & Range("A2").Value & _
"&c=" & Range("A3").Value & _
"&d=" & Range("A4").Value & _
"&e=" & Range("A5").Value & _
"&f=" & Range("A6").Value & _
"&g=" & Range("A7").Value & _
"&s=" & Range("A8").Value

Change the Range References to match you locations.

 

[Jason]

Thanks Don. I took a look at your file on xltrader, and this is exactly what
I'm trying to do as far as getting the historical data. However, it's not
useful to me in this situation as I would like to add some additional
functionallity (requiring more code), but cannot due to the source code being
password protected.

Jason

I think using the csv method should be much faster. Here is one I use in a
free file at xltraders. You may want to go there and get it.

Goto http://groups.yahoo.com/group/xltraders/
and look for author donalb36 and download my FREE files

myurl = "http://table.finance.yahoo.com/table.csv?a=" & StartMo & "&b=" &
StartDay & "&c=" & StartYr & "&d=" & StopMo & "&e=" & StopDay & "&f=" &
StopYr & "&y=0&g=" & [e2] & "&s=" & c & ""

--
Don Guillett
SalesAid Software
(e-mail address removed)

"URL;http://table.finance.yahoo.com/" _
"d?a=" & Range("A1").Value & _
"&b=" & Range("A2").Value & _
"&c=" & Range("A3").Value & _
"&d=" & Range("A4").Value & _
"&e=" & Range("A5").Value & _
"&f=" & Range("A6").Value & _
"&g=" & Range("A7").Value & _
"&s=" & Range("A8").Value

Change the Range References to match you locations.

--
Regards,
Tom Ogilvy


come it
"URL;http://table.finance.yahoo.com/d?a=2&b=15&c=2004&d=5&e=15&f=2005&g=w&s= defining
"URL;http://table.finance.yahoo.com/d?a=2&b=15&c=2004&d=5&e=15&f=2005&g=w&s=