Google query

D

David

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
 
D

David

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
 
R

r

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html
 
D

David

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 said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


David said:
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
Click to expand...
Click to expand...
 
R

r

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


David said:
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 said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


David said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

r

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


David said:
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 said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

r

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


David said:
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



:

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


:

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
Click to expand...
Click to expand...
Click to expand...
 
D

David

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 said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


:

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



:

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


:

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
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

Query brings in wrong date 4
Index in google finance 1
Google finance 7
Convert Excel vba to Access 3
VBA, Yahoo ichart csv file 5

Top