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
|