Change a web query

D

David

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
 
J

Joel

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
 
D

Don Guillett

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
 
D

Don Guillett

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
 
D

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 said:
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 address removed)
David said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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 address removed)
Don Guillett said:
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 address removed)
David said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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 address removed)
David said:
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 said:
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 address removed)
David said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

I sent OP a file that does as desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
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 address removed)
David said:
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 said:
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 address 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

web query 3
adjust cell width change font. 4
Retrieving consecutive pages in web query What's wrong here? 1
Web Import 18
Web Query 2
Problem with the Web Query 2
Unable extract data from WEB query 2
VBA, Yahoo ichart csv file 5

Top