Hi Group,

I have a query:
Sub Macro3()
Range("C1").Select 'Range C1 Has a date ie 5/27/2009
ThisDate = ActiveCell.Value
ThisMonth = Month(ThisDate)
ThisDay = Day(ThisDate)
ThisYear = Year(ThisDate)
ActiveCell.Offset(1, 0).Select
z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
"&c=" _
& (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
& ThisDay & "&f=" & ThisYear _
& "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
.WebSelectionType = xlSpecifiedTables
.WebTables = "20"
.Refresh BackgroundQuery:=False
End With
End Sub

C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.

I was using this in another macro and capturing Weekly data. I need to
change it so that it only capture one day. In this example 5/27/2009, but I
need to do this with the variables, since the day will change often, ThisDay,
ThisMonth and This Year.

Thank you for your help,
David

The g=w had to be changed to g=d. I change the code so it is easier to see
what is really happening


Sub NewMacro()


ThisDate = Range("C1").Value 'Range C1 Has a date ie 5/27/2009
ThisMonth = Month(ThisDate)
ThisDay = Day(ThisDate)
ThisYear = Year(ThisDate)
Z = Range("A2").Value 'This get a ticker symbol ie AA

URL = "URL;http://table.finance.yahoo.com/table.csv"
Options = "?a=0&b=" _
& ThisMonth _
& "&c=" _
& ThisYear _
& "&d=" _
& ThisMonth _
& "&e=" _
& ThisDay _
& "&f=" _
& ThisYear _
& "&g=d&s="

Connection = URL & Options & Z

'a =
"http://finance.yahoo.com/q/hp?s=AA&a=04&b=27&c=2009&d=04&e=28&f=2009&g=d"
With ActiveSheet.QueryTables.Add( _
Connection:=Connection, _
Destination:=Range("C2"))
.WebSelectionType = xlSpecifiedTables
.WebTables = "20"
.Refresh BackgroundQuery:=False
End With
End Sub


"David" wrote:

> Hi Group,
>
> I have a query:
> Sub Macro3()
> Range("C1").Select 'Range C1 Has a date ie 5/27/2009
> ThisDate = ActiveCell.Value
> ThisMonth = Month(ThisDate)
> ThisDay = Day(ThisDate)
> ThisYear = Year(ThisDate)
> ActiveCell.Offset(1, 0).Select
> z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
> With ActiveSheet.QueryTables.Add(Connection:= _
> "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
> "&c=" _
> & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
> & ThisDay & "&f=" & ThisYear _
> & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
> .WebSelectionType = xlSpecifiedTables
> .WebTables = "20"
> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
> C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.
>
> I was using this in another macro and capturing Weekly data. I need to
> change it so that it only capture one day. In this example 5/27/2009, but I
> need to do this with the variables, since the day will change often, ThisDay,
> ThisMonth and This Year.
>
> Thank you for your help,
> David

This is modified from a free file of mine that will do as many symbols as
desired for whatever period for month,week,or day for the adjusted close
value. Also, includes a graph of the history for each or all.
If you request OFF list I will send it to you.
Date Open High Low Close Volume Adj Close
5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07


Change your date from c1 to a1 as column c will be deleted.
Sub getonesymbolandoneday()'SalesAidSoftware

Application.ScreenUpdating = False

'delete name buildup
For Each n In ActiveSheet.Names
n.Delete
Next
Columns("c:j").Delete

Set startdate = Range("a1")
StartMo = Month(startdate) - 1
StartDay = Day(startdate)
StartYr = Year(startdate)
StopMo = Month(startdate) - 1
StopDay = Day(startdate)
StopYr = Year(startdate)

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=" & Range("a2") & ""

With ActiveSheet.QueryTables.Add( _
Connection:="URL;" & myurl, _
Destination:=Range("c3"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Puts into columns
Application.DisplayAlerts = False
Range("c3:c4").TextToColumns Destination:= _
Range("c3"), DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Columns("c:j").AutoFit

Application.ScreenUpdating = True
[a2].Select
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"David" <(E-Mail Removed)> wrote in message
news:09BD7103-BE6C-405C-8B6B-(E-Mail Removed)...
> Hi Group,
>
> I have a query:
> Sub Macro3()
> Range("C1").Select 'Range C1 Has a date ie 5/27/2009
> ThisDate = ActiveCell.Value
> ThisMonth = Month(ThisDate)
> ThisDay = Day(ThisDate)
> ThisYear = Year(ThisDate)
> ActiveCell.Offset(1, 0).Select
> z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
> With ActiveSheet.QueryTables.Add(Connection:= _
> "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
> "&c=" _
> & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
> & ThisDay & "&f=" & ThisYear _
> & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
> .WebSelectionType = xlSpecifiedTables
> .WebTables = "20"
> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
> C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.
>
> I was using this in another macro and capturing Weekly data. I need to
> change it so that it only capture one day. In this example 5/27/2009, but
> I
> need to do this with the variables, since the day will change often,
> ThisDay,
> ThisMonth and This Year.
>
> Thank you for your help,
> David

Joel,
Yours
builds up names with each fetch
moves the fetch over a column.
Does more than one day
does not split
& a couple of other problems

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joel" <(E-Mail Removed)> wrote in message
news:3067FA6B-F734-478A-9128-(E-Mail Removed)...
> The g=w had to be changed to g=d. I change the code so it is easier to
> see
> what is really happening
>
>
> Sub NewMacro()
>
>
> ThisDate = Range("C1").Value 'Range C1 Has a date ie 5/27/2009
> ThisMonth = Month(ThisDate)
> ThisDay = Day(ThisDate)
> ThisYear = Year(ThisDate)
> Z = Range("A2").Value 'This get a ticker symbol ie AA
>
> URL = "URL;http://table.finance.yahoo.com/table.csv"
> Options = "?a=0&b=" _
> & ThisMonth _
> & "&c=" _
> & ThisYear _
> & "&d=" _
> & ThisMonth _
> & "&e=" _
> & ThisDay _
> & "&f=" _
> & ThisYear _
> & "&g=d&s="
>
> Connection = URL & Options & Z
>
> 'a =
> "http://finance.yahoo.com/q/hp?s=AA&a=04&b=27&c=2009&d=04&e=28&f=2009&g=d"
> With ActiveSheet.QueryTables.Add( _
> Connection:=Connection, _
> Destination:=Range("C2"))
> .WebSelectionType = xlSpecifiedTables
> .WebTables = "20"
> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
>
> "David" wrote:
>
>> Hi Group,
>>
>> I have a query:
>> Sub Macro3()
>> Range("C1").Select 'Range C1 Has a date ie 5/27/2009
>> ThisDate = ActiveCell.Value
>> ThisMonth = Month(ThisDate)
>> ThisDay = Day(ThisDate)
>> ThisYear = Year(ThisDate)
>> ActiveCell.Offset(1, 0).Select
>> z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
>> With ActiveSheet.QueryTables.Add(Connection:= _
>> "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
>> "&c=" _
>> & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
>> & ThisDay & "&f=" & ThisYear _
>> & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
>> .WebSelectionType = xlSpecifiedTables
>> .WebTables = "20"
>> .Refresh BackgroundQuery:=False
>> End With
>> End Sub
>>
>> C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.
>>
>> I was using this in another macro and capturing Weekly data. I need to
>> change it so that it only capture one day. In this example 5/27/2009, but
>> I
>> need to do this with the variables, since the day will change often,
>> ThisDay,
>> ThisMonth and This Year.
>>
>> Thank you for your help,
>> David

Hi Don,

I would like to thank you for your help. I still use much of the code you
have helped with in the past.

I am actually walking down a list and bringing in many values. The query
moves the previous data over each time a new query is done. I was able to
overcome this in another macro, but not consistently, meaning that it will
work most of the time, but not all of the time. Is there a way to write this
query to Overwrite the destination and not move the data and also make this
happen with consistency?

The other problem I am having is that IE’s cache is filling up and the macro
will simply stop working. Can I address the cache and delete it from VBA?

Thank you for your help.

David

"Don Guillett" wrote:

> This is modified from a free file of mine that will do as many symbols as
> desired for whatever period for month,week,or day for the adjusted close
> value. Also, includes a graph of the history for each or all.
> If you request OFF list I will send it to you.
> Date Open High Low Close Volume Adj Close
> 5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07
>
>
> Change your date from c1 to a1 as column c will be deleted.
> Sub getonesymbolandoneday()'SalesAidSoftware
>
> Application.ScreenUpdating = False
>
> 'delete name buildup
> For Each n In ActiveSheet.Names
> n.Delete
> Next
> Columns("c:j").Delete
>
> Set startdate = Range("a1")
> StartMo = Month(startdate) - 1
> StartDay = Day(startdate)
> StartYr = Year(startdate)
> StopMo = Month(startdate) - 1
> StopDay = Day(startdate)
> StopYr = Year(startdate)
>
> 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=" & Range("a2") & ""
>
> With ActiveSheet.QueryTables.Add( _
> Connection:="URL;" & myurl, _
> Destination:=Range("c3"))
> .BackgroundQuery = True
> .TablesOnlyFromHTML = False
> .Refresh BackgroundQuery:=False
> .SaveData = True
> End With
>
> 'Puts into columns
> Application.DisplayAlerts = False
> Range("c3:c4").TextToColumns Destination:= _
> Range("c3"), DataType:=xlDelimited, Comma:=True
> Application.DisplayAlerts = True
> Columns("c:j").AutoFit
>
> Application.ScreenUpdating = True
> [a2].Select
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "David" <(E-Mail Removed)> wrote in message
> news:09BD7103-BE6C-405C-8B6B-(E-Mail Removed)...
> > Hi Group,
> >
> > I have a query:
> > Sub Macro3()
> > Range("C1").Select 'Range C1 Has a date ie 5/27/2009
> > ThisDate = ActiveCell.Value
> > ThisMonth = Month(ThisDate)
> > ThisDay = Day(ThisDate)
> > ThisYear = Year(ThisDate)
> > ActiveCell.Offset(1, 0).Select
> > z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
> > With ActiveSheet.QueryTables.Add(Connection:= _
> > "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
> > "&c=" _
> > & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
> > & ThisDay & "&f=" & ThisYear _
> > & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
> > .WebSelectionType = xlSpecifiedTables
> > .WebTables = "20"
> > .Refresh BackgroundQuery:=False
> > End With
> > End Sub
> >
> > C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.
> >
> > I was using this in another macro and capturing Weekly data. I need to
> > change it so that it only capture one day. In this example 5/27/2009, but
> > I
> > need to do this with the variables, since the day will change often,
> > ThisDay,
> > ThisMonth and This Year.
> >
> > Thank you for your help,
> > David
>
>

I think I offered a file to do exactly what you want. I will only respond
to an OFF list request to my address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:OLzM02%(E-Mail Removed)...
> This is modified from a free file of mine that will do as many symbols as
> desired for whatever period for month,week,or day for the adjusted close
> value. Also, includes a graph of the history for each or all.
> If you request OFF list I will send it to you.
> Date Open High Low Close Volume Adj Close
> 5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07
>
>
> Change your date from c1 to a1 as column c will be deleted.
> Sub getonesymbolandoneday()'SalesAidSoftware
>
> Application.ScreenUpdating = False
>
> 'delete name buildup
> For Each n In ActiveSheet.Names
> n.Delete
> Next
> Columns("c:j").Delete
>
> Set startdate = Range("a1")
> StartMo = Month(startdate) - 1
> StartDay = Day(startdate)
> StartYr = Year(startdate)
> StopMo = Month(startdate) - 1
> StopDay = Day(startdate)
> StopYr = Year(startdate)
>
> 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=" & Range("a2") & ""
>
> With ActiveSheet.QueryTables.Add( _
> Connection:="URL;" & myurl, _
> Destination:=Range("c3"))
> .BackgroundQuery = True
> .TablesOnlyFromHTML = False
> .Refresh BackgroundQuery:=False
> .SaveData = True
> End With
>
> 'Puts into columns
> Application.DisplayAlerts = False
> Range("c3:c4").TextToColumns Destination:= _
> Range("c3"), DataType:=xlDelimited, Comma:=True
> Application.DisplayAlerts = True
> Columns("c:j").AutoFit
>
> Application.ScreenUpdating = True
> [a2].Select
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "David" <(E-Mail Removed)> wrote in message
> news:09BD7103-BE6C-405C-8B6B-(E-Mail Removed)...
>> Hi Group,
>>
>> I have a query:
>> Sub Macro3()
>> Range("C1").Select 'Range C1 Has a date ie 5/27/2009
>> ThisDate = ActiveCell.Value
>> ThisMonth = Month(ThisDate)
>> ThisDay = Day(ThisDate)
>> ThisYear = Year(ThisDate)
>> ActiveCell.Offset(1, 0).Select
>> z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
>> With ActiveSheet.QueryTables.Add(Connection:= _
>> "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
>> "&c=" _
>> & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
>> & ThisDay & "&f=" & ThisYear _
>> & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
>> .WebSelectionType = xlSpecifiedTables
>> .WebTables = "20"
>> .Refresh BackgroundQuery:=False
>> End With
>> End Sub
>>
>> C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.
>>
>> I was using this in another macro and capturing Weekly data. I need to
>> change it so that it only capture one day. In this example 5/27/2009, but
>> I
>> need to do this with the variables, since the day will change often,
>> ThisDay,
>> ThisMonth and This Year.
>>
>> Thank you for your help,
>> David
>

Actually, had you tried the code I sent you will see that it does eliminate
the columns and the external name build up. It was part of a loop in the
free file I mentioned and can easily be incorporated into a looping macro
that does the fetch and then copies the desired info somewhere else....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"David" <(E-Mail Removed)> wrote in message
news:E0DB8A60-4F29-4062-AEDC-(E-Mail Removed)...
> Hi Don,
>
> I would like to thank you for your help. I still use much of the code you
> have helped with in the past.
>
> I am actually walking down a list and bringing in many values. The query
> moves the previous data over each time a new query is done. I was able to
> overcome this in another macro, but not consistently, meaning that it will
> work most of the time, but not all of the time. Is there a way to write
> this
> query to Overwrite the destination and not move the data and also make
> this
> happen with consistency?
>
> The other problem I am having is that IE’s cache is filling up and the
> macro
> will simply stop working. Can I address the cache and delete it from VBA?
>
> Thank you for your help.
>
> David
>
> "Don Guillett" wrote:
>
>> This is modified from a free file of mine that will do as many symbols as
>> desired for whatever period for month,week,or day for the adjusted close
>> value. Also, includes a graph of the history for each or all.
>> If you request OFF list I will send it to you.
>> Date Open High Low Close Volume Adj Close
>> 5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07
>>
>>
>> Change your date from c1 to a1 as column c will be deleted.
>> Sub getonesymbolandoneday()'SalesAidSoftware
>>
>> Application.ScreenUpdating = False
>>
>> 'delete name buildup
>> For Each n In ActiveSheet.Names
>> n.Delete
>> Next
>> Columns("c:j").Delete
>>
>> Set startdate = Range("a1")
>> StartMo = Month(startdate) - 1
>> StartDay = Day(startdate)
>> StartYr = Year(startdate)
>> StopMo = Month(startdate) - 1
>> StopDay = Day(startdate)
>> StopYr = Year(startdate)
>>
>> 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=" & Range("a2") & ""
>>
>> With ActiveSheet.QueryTables.Add( _
>> Connection:="URL;" & myurl, _
>> Destination:=Range("c3"))
>> .BackgroundQuery = True
>> .TablesOnlyFromHTML = False
>> .Refresh BackgroundQuery:=False
>> .SaveData = True
>> End With
>>
>> 'Puts into columns
>> Application.DisplayAlerts = False
>> Range("c3:c4").TextToColumns Destination:= _
>> Range("c3"), DataType:=xlDelimited, Comma:=True
>> Application.DisplayAlerts = True
>> Columns("c:j").AutoFit
>>
>> Application.ScreenUpdating = True
>> [a2].Select
>> End Sub
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "David" <(E-Mail Removed)> wrote in message
>> news:09BD7103-BE6C-405C-8B6B-(E-Mail Removed)...
>> > Hi Group,
>> >
>> > I have a query:
>> > Sub Macro3()
>> > Range("C1").Select 'Range C1 Has a date ie 5/27/2009
>> > ThisDate = ActiveCell.Value
>> > ThisMonth = Month(ThisDate)
>> > ThisDay = Day(ThisDate)
>> > ThisYear = Year(ThisDate)
>> > ActiveCell.Offset(1, 0).Select
>> > z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
>> > With ActiveSheet.QueryTables.Add(Connection:= _
>> > "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth)
>> > &
>> > "&c=" _
>> > & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
>> > & ThisDay & "&f=" & ThisYear _
>> > & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
>> > .WebSelectionType = xlSpecifiedTables
>> > .WebTables = "20"
>> > .Refresh BackgroundQuery:=False
>> > End With
>> > End Sub
>> >
>> > C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell
>> > A2.
>> >
>> > I was using this in another macro and capturing Weekly data. I need to
>> > change it so that it only capture one day. In this example 5/27/2009,
>> > but
>> > I
>> > need to do this with the variables, since the day will change often,
>> > ThisDay,
>> > ThisMonth and This Year.
>> >
>> > Thank you for your help,
>> > David
>>
>>

I sent OP a file that does as desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Actually, had you tried the code I sent you will see that it does
> eliminate the columns and the external name build up. It was part of a
> loop in the free file I mentioned and can easily be incorporated into a
> looping macro that does the fetch and then copies the desired info
> somewhere else....
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "David" <(E-Mail Removed)> wrote in message
> news:E0DB8A60-4F29-4062-AEDC-(E-Mail Removed)...
>> Hi Don,
>>
>> I would like to thank you for your help. I still use much of the code you
>> have helped with in the past.
>>
>> I am actually walking down a list and bringing in many values. The query
>> moves the previous data over each time a new query is done. I was able to
>> overcome this in another macro, but not consistently, meaning that it
>> will
>> work most of the time, but not all of the time. Is there a way to write
>> this
>> query to Overwrite the destination and not move the data and also make
>> this
>> happen with consistency?
>>
>> The other problem I am having is that IE’s cache is filling up and the
>> macro
>> will simply stop working. Can I address the cache and delete it from VBA?
>>
>> Thank you for your help.
>>
>> David
>>
>> "Don Guillett" wrote:
>>
>>> This is modified from a free file of mine that will do as many symbols
>>> as
>>> desired for whatever period for month,week,or day for the adjusted close
>>> value. Also, includes a graph of the history for each or all.
>>> If you request OFF list I will send it to you.
>>> Date Open High Low Close Volume Adj Close
>>> 5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07
>>>
>>>
>>> Change your date from c1 to a1 as column c will be deleted.
>>> Sub getonesymbolandoneday()'SalesAidSoftware
>>>
>>> Application.ScreenUpdating = False
>>>
>>> 'delete name buildup
>>> For Each n In ActiveSheet.Names
>>> n.Delete
>>> Next
>>> Columns("c:j").Delete
>>>
>>> Set startdate = Range("a1")
>>> StartMo = Month(startdate) - 1
>>> StartDay = Day(startdate)
>>> StartYr = Year(startdate)
>>> StopMo = Month(startdate) - 1
>>> StopDay = Day(startdate)
>>> StopYr = Year(startdate)
>>>
>>> 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=" & Range("a2") & ""
>>>
>>> With ActiveSheet.QueryTables.Add( _
>>> Connection:="URL;" & myurl, _
>>> Destination:=Range("c3"))
>>> .BackgroundQuery = True
>>> .TablesOnlyFromHTML = False
>>> .Refresh BackgroundQuery:=False
>>> .SaveData = True
>>> End With
>>>
>>> 'Puts into columns
>>> Application.DisplayAlerts = False
>>> Range("c3:c4").TextToColumns Destination:= _
>>> Range("c3"), DataType:=xlDelimited, Comma:=True
>>> Application.DisplayAlerts = True
>>> Columns("c:j").AutoFit
>>>
>>> Application.ScreenUpdating = True
>>> [a2].Select
>>> End Sub
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "David" <(E-Mail Removed)> wrote in message
>>> news:09BD7103-BE6C-405C-8B6B-(E-Mail Removed)...
>>> > Hi Group,
>>> >
>>> > I have a query:
>>> > Sub Macro3()
>>> > Range("C1").Select 'Range C1 Has a date ie 5/27/2009
>>> > ThisDate = ActiveCell.Value
>>> > ThisMonth = Month(ThisDate)
>>> > ThisDay = Day(ThisDate)
>>> > ThisYear = Year(ThisDate)
>>> > ActiveCell.Offset(1, 0).Select
>>> > z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
>>> > With ActiveSheet.QueryTables.Add(Connection:= _
>>> > "URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth)
>>> > &
>>> > "&c=" _
>>> > & (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
>>> > & ThisDay & "&f=" & ThisYear _
>>> > & "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
>>> > .WebSelectionType = xlSpecifiedTables
>>> > .WebTables = "20"
>>> > .Refresh BackgroundQuery:=False
>>> > End With
>>> > End Sub
>>> >
>>> > C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell
>>> > A2.
>>> >
>>> > I was using this in another macro and capturing Weekly data. I need to
>>> > change it so that it only capture one day. In this example 5/27/2009,
>>> > but
>>> > I
>>> > need to do this with the variables, since the day will change often,
>>> > ThisDay,
>>> > ThisMonth and This Year.
>>> >
>>> > Thank you for your help,
>>> > David
>>>
>>>
>