| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a web query
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
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 "David" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
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 IEs 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 "David" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
I sent OP a file that does as desired.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 "David" wrote in message ... 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 IEs 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 "David" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change a web query
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 "Don Guillett" wrote in message ... 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 "David" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change Server for Query | Excel Discussion (Misc queries) | |||
| How to change MS Query via VBA | Excel Programming | |||
| Database change in Query | Excel Discussion (Misc queries) | |||
| How to change an existing query | Excel Discussion (Misc queries) | |||
| can't change field name in query? | Links and Linking in Excel | |||
Hybrid Mode
