Historical Stock Quotes - Yahoo Finance

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

"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


"Jason" <[email protected]> wrote in message
news:[email protected]...
> 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...&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...&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
>
>

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
[email protected]
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> "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
>
>
> "Jason" <[email protected]> wrote in message
> news:[email protected]...
> > 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...&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...&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
> >
> >
>
>

Thanks Tom, it worked.

"Tom Ogilvy" wrote:

> "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
>
>
> "Jason" <[email protected]> wrote in message
> news:[email protected]...
> > 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...&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...&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
> >
> >
>
>
>

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

"Don Guillett" wrote:

> 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
> [email protected]
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > "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
> >
> >
> > "Jason" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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...&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...&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
> > >
> > >
> >
> >
>
>
>