Having trouble with a web query that looks up historical stock prices through Google Finance. The code works for some stocks, but then it errors out for other stocks. When you manually type the stock ticker into Google or manually create the URL link (in Word, for example), it works just fine but for some reason it has issues when I run the macro. Here's the code:
qurl = "- Google Finance Search=" & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
"+" & Day(StartDate) & "+" & Year(StartDate) & _
"&enddate=" & MonthName(Month(EndDate), True) & _
"+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"
QueryQuote:
With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
It errors out at the line that says ".Refresh BackgroundQuery:=False". Here is an example of what the URL should look like...
STMicroelectronics N.V.: EPA:STM historical prices - Google Finance&startdate=2+12+2012&enddate=3+15+2012&output=csv
Again, with the ticker "EPA:STM" it doesn't work, but with the ticker "LON:FGP" it does. Any help?
qurl = "- Google Finance Search=" & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
"+" & Day(StartDate) & "+" & Year(StartDate) & _
"&enddate=" & MonthName(Month(EndDate), True) & _
"+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"
QueryQuote:
With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
It errors out at the line that says ".Refresh BackgroundQuery:=False". Here is an example of what the URL should look like...
STMicroelectronics N.V.: EPA:STM historical prices - Google Finance&startdate=2+12+2012&enddate=3+15+2012&output=csv
Again, with the ticker "EPA:STM" it doesn't work, but with the ticker "LON:FGP" it does. Any help?