Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Google query
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,560
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
O.T. Google Search RagDyeR Excel Discussion (Misc queries) 4 May 12th 09 11:26 PM
using the google spreadsheets compactstu Excel Worksheet Functions 12 August 20th 07 08:34 PM
Problems with Google Harlan Grove Excel Discussion (Misc queries) 6 March 12th 05 11:23 PM
Google Bob Phillips Excel Discussion (Misc queries) 4 December 2nd 04 05:44 PM
A pun on Google steve Excel Programming 6 August 4th 03 03:47 PM


All times are GMT +1. The time now is 04:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement