Excel yahoo finance VBA run-time error

[MisterE]

I use this code to retrieve historical stock prices for about 40 tickers. I found it here http://www.mathfinance.cn/download-multiple-stock-quotes-from-yahoo-finance


It downloads about half of the symbols before a Run-time Error '1004' pops up. "Unable to open hattp://table.finance.yahoo.com/table.csv?s=Tickersymbol&a=11&b=21&c=1998 The internet site reports that the item you requested cannot be found (HTTP/1.0 404)


Can I change the code so this error won't happen? What appears to be happening is that the macro goes through each ticker, if yahoo finance doesn't connect quickly enough through each ticker, the run-time error pops up. So what I need is some way to change the code so that the spreadsheet will wait a longer period of time for the connection to yahoo finance to be made. Any ideas?


The code is below -


Sub Get_Yahoo_finance()
Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim Ticker As String
Dim StartDate As Date
Dim EndDate As Date
Dim a, b, c, d, e, f
Dim StrURL As String
Set Sh = Worksheets("Input")
Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
For Each Cell In Rng
Ticker = Cell.Value
StartDate = Cell.Offset(0, 1).Value
EndDate = Cell.Offset(0, 2).Value
a = Format(Month(StartDate) - 1, "00") ' Month minus 1
b = Day(StartDate)
c = Year(StartDate)
d = Format(Month(EndDate) - 1, "00")
e = Day(EndDate)
f = Year(EndDate)
StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
StrURL = StrURL & "s=" & Ticker & "&a=" & a & "&b=" & b
StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"
If WorksheetExists(Ticker, ActiveWorkbook) Then
Application.DisplayAlerts = False
Sheets(Ticker).Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Worksheets.Add.Name = Ticker
Else
ActiveWorkbook.Worksheets.Add.Name = Ticker
End If
With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Range("A2").Select
Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
Columns("A:F").EntireColumn.AutoFit
Next Cell
End Sub


Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

 

[BiocideJ]

I tinkered around with the code and some tickers and I was able to duplicate your error, but only by entering a ticker that did not have valid dates within the Start/End date range.

I would find out what ticker is giving you problems and I suspect you will find that you are searching for a start date before the fund's inception or have an end date after a fund was discontinued.

 

[MisterE]

Thanks for looking in to it.

I tried changing the date ranges to only dates that correspond to the tickers' histories. Unfortunately, I still get the error. The error is completely random when it pops up too. Sometimes it's in the second ticker and sometimes it's the 30th ticker I have entered.

I think it has something to do with not connecting to Yahoo in a timely fashion. But I'm really at a loss here.

Any other ideas?

 

[BiocideJ]

When you hit debug, what line is the code hanging up on?

 

[MisterE]

BIOCIDEJ - it's hanging up on this:

.Refresh BackgroundQuery:=False

 

[BiocideJ]

Found this online...
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/178395ff-c1c7-4ed5-898d-8d30ca85224c/

which seems to support that there is something wrong with the specific ticker/dates that are being passed to the url.

When you click debug and hover over the variables

Ticker, StartDate and EndDate, what values are contained in them when the code errors out? This may give a better indication as to what exactly is causing it to error in the first place.

Ticker = Cell.Value
StartDate = Cell.Offset(0, 1).Value
EndDate = Cell.Offset(0, 2).Value

 

[MisterE]

thanks for your help BIOCIDEJ but I'm not really sure what you're saying. I'm really a newbie at all of this. So unfortunately you have to explain things to me like I'm a 5 year old.

Below is what I expect is the values portion when I debug. I'm not sure what your other link was telling me to do. I'm not smart enough to figure it out. I'm looking for someone to tell me what to do.

Ticker = Cell.Value
StartDate = Cell.Offset(0, 1).Value
EndDate = Cell.Offset(0, 2).Value
a = Format(Month(StartDate) - 1, "00") ' Month minus 1
b = Day(StartDate)
c = Year(StartDate)
d = Format(Month(EndDate) - 1, "00")
e = Day(EndDate)
f = Year(EndDate)