Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Hi Group,
The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Hi Again,
Guess I am a little tired and failed to specify some of the parameters. The Start and Stop dates are the same, it is only a one day history I am trying to get. And Sym is the Symbol ie. GM or AA, etc. Hope I have not left more out, big yawn. Thanks again, David "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Sub test_Web_Qry()
Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Hi,
Revised to handle a ticker symbol it fails, but is close Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] 'Web_Qry 13117657, "May", 29, 2007, "May", 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) 'Id As String, _ (Comment 'Start_m As String, _ 'Start_d As Long, _ 'Start_y As Long, _ 'Stop_m As String, _ 'Stop_d As Long, _ 'Stop_y As Long, _ 'Destination_Rng As Excel.Range) to comment END) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) ..WebTables = "2" ..Refresh BackgroundQuery:=False End With End Sub "r" wrote: Sub test_Web_Qry() Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Sub test_Web_Qry()
Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=NYSE:" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) ..WebTables = "2" ..Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi, Revised to handle a ticker symbol it fails, but is close Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] 'Web_Qry 13117657, "May", 29, 2007, "May", 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) 'Id As String, _ (Comment 'Start_m As String, _ 'Start_d As Long, _ 'Start_y As Long, _ 'Stop_m As String, _ 'Stop_d As Long, _ 'Stop_y As Long, _ 'Destination_Rng As Excel.Range) to comment END) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub "r" wrote: Sub test_Web_Qry() Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
More correct
Sub test_Web_Qry() Web_Qry "NYSE:GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) ..WebTables = "2" ..Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "r" wrote: Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=NYSE:" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi, Revised to handle a ticker symbol it fails, but is close Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] 'Web_Qry 13117657, "May", 29, 2007, "May", 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) 'Id As String, _ (Comment 'Start_m As String, _ 'Start_d As Long, _ 'Start_y As Long, _ 'Stop_m As String, _ 'Stop_d As Long, _ 'Stop_y As Long, _ 'Destination_Rng As Excel.Range) to comment END) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub "r" wrote: Sub test_Web_Qry() Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
or ...
Sub test_Web_Qry() Web_Qry "NYSE", "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Exchange As String, _ Symbol As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=" & _ Exchange & ":" & _ Symbol & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) ..WebTables = "2" ..Refresh BackgroundQuery:=False End With End Sub Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "r" wrote: More correct Sub test_Web_Qry() Web_Qry "NYSE:GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "r" wrote: Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=NYSE:" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi, Revised to handle a ticker symbol it fails, but is close Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] 'Web_Qry 13117657, "May", 29, 2007, "May", 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) 'Id As String, _ (Comment 'Start_m As String, _ 'Start_d As Long, _ 'Start_y As Long, _ 'Stop_m As String, _ 'Stop_d As Long, _ 'Stop_y As Long, _ 'Destination_Rng As Excel.Range) to comment END) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub "r" wrote: Sub test_Web_Qry() Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Google query
Hi,
If I substitute variables in the place of actuals, I get "Byref argument type mismatch". The other potential problem is that not all symols will be from NYSE: Sub test_Web_Qry() StartDate = Range("C1").Value StartMo = Month(StartDate) StartDay = Day(StartDate) StartYr = Year(StartDate) StopMo = Month(StartDate) StopDay = Day(StartDate) StopYr = Year(StartDate) Sym = "GM" Web_Qry Sym, StartMo, StartDay, StartYr, StopMo, StopDay, StopYr, [E3] End Sub Sub Web_Qry( _ Id As String, _ StartMo As Long, _ StartDay As Long, _ StartYr As Long, _ StopMo As Long, _ StopDay As Long, _ StopYr As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=NYSE:" & _ Sym & "&startdate=" & _ StartMo & "+" & _ StartDay & "%2C+" & _ StartYr & "&enddate=" & _ StopMo & "+" & _ StopDay & "%2C+" & _ StopYr With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub "r" wrote: or ... Sub test_Web_Qry() Web_Qry "NYSE", "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Exchange As String, _ Symbol As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=" & _ Exchange & ":" & _ Symbol & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "r" wrote: More correct Sub test_Web_Qry() Web_Qry "NYSE:GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "r" wrote: Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?q=NYSE:" & _ Id & "&startdate=" & _ MonthName(Start_m, True) & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ MonthName(Stop_m, True) & "+" & _ Stop_d & "%2C+" & _ Stop_y Debug.Print sUrl With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi, Revised to handle a ticker symbol it fails, but is close Sub test_Web_Qry() Web_Qry "GM", 5, 29, 2007, 5, 29, 2007, [E3] 'Web_Qry 13117657, "May", 29, 2007, "May", 29, 2007, [E3] End Sub Sub Web_Qry( _ Id As String, _ Start_m As Long, _ Start_d As Long, _ Start_y As Long, _ Stop_m As Long, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) 'Id As String, _ (Comment 'Start_m As String, _ 'Start_d As Long, _ 'Start_y As Long, _ 'Stop_m As String, _ 'Stop_d As Long, _ 'Stop_y As Long, _ 'Destination_Rng As Excel.Range) to comment END) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub "r" wrote: Sub test_Web_Qry() Web_Qry 13117657, "May", 1, 2009, "Jul", 1, 2009, [a1] End Sub Sub Web_Qry( _ Id As Long, _ Start_m As String, _ Start_d As Long, _ Start_y As Long, _ Stop_m As String, _ Stop_d As Long, _ Stop_y As Long, _ Destination_Rng As Excel.Range) Dim sUrl As String sUrl = "URL;http://www.google.com/finance/historical?cid=" & _ Id & "&startdate=" & _ Start_m & "+" & _ Start_d & "%2C+" & _ Start_y & "&enddate=" & _ Stop_m & "+" & _ Stop_d & "%2C+" & _ Stop_y With ActiveSheet.QueryTables.Add _ (Connection:=sUrl, _ Destination:=Destination_Rng) .WebTables = "2" .Refresh BackgroundQuery:=False End With End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "David" wrote: Hi Group, The following quesry works, but is not bringing in the data I expected and want. It bring in May 29, 2009 through Jun 2, 2008. Looking for a single day. 5/29/2007, the parameters are as follows: StartMo = Month(startdate) StartDay = Day(startdate) StartYr = Year(startdate) StopMo = Month(startdate) StopDay = Day(startdate) StopYr = Year(startdate) The code is below: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.google.com/finance/historical?histperiod=daily&q=" _ & sym & "&startdate=" _ & StartMo & "+" & StartDay & "%2C+" & StartYr _ & "&enddate=" _ & StopMo & "+" & StopDay & "%2C+" & StopYr & "&output=csv", Destination:=Range("E3")) .Refresh BackgroundQuery:=False End With Thanks for your help, David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
O.T. Google Search | Excel Discussion (Misc queries) | |||
using the google spreadsheets | Excel Worksheet Functions | |||
Problems with Google | Excel Discussion (Misc queries) | |||
Excel Discussion (Misc queries) | ||||
A pun on Google | Excel Programming |