Hi all,
I need to pull stock/fund prices and dividends from Yahoo's Finance site. I've found some code that will do that and I wrote a few lines to read the ticker symbols from the "Analysis" worksheet.
The tickers in this particular project are AMZN, VFIAX, MSFT, INTC, BA, GOOGL, and SPY. All the tickers are valid.
The problem: Once the code reaches the sixth (sometimes seventh) ticker, it stops and says
Sorry, we couldn't open 'http://ichart.finance.yahoo.com/table.csv?s=GOOG&a=... etc.
When I press DEBUG it takes me to the .REFRESH BACKGROUND QUERY: =FALSE line in the bottom half of the code.
I researched this and it said that the query could be taking me to an invalid URL. However, I had the code spit out the URL which I copied and pasted into my browser and the URL was fine.
Then, I thought perhaps I needed to program a time break into the code to give it a little breather. [I was grasping at straws!] The time break did give the program a breather, but it didn't help with the problem.
Interestingly enough, I can experience the error as described and then ten minutes run it again and it works fine even though I have made no changes!
Please, please help. Also, I am somewhat of a VBA newbie, so please be gentle and patient, and pretend I know very little about VBA. :D
Thanks,
Pamela
P.S. In an attempt to see if it was GOOG that was the problem ticker, I used the following tickers: AMZN, VFIAX, MSFT, INTC, BA, MSFT, and SPY--replacing GOOG with MSFT since I knew it worked with MSFT as MSFT is also the 3rd ticker. It still gives me the error on MSFT as the sixth ticker.
I need to pull stock/fund prices and dividends from Yahoo's Finance site. I've found some code that will do that and I wrote a few lines to read the ticker symbols from the "Analysis" worksheet.
Code:
'Pull price data for each ticker
URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range(cell).Value & dateparamsA & "&h=d&ignore=.csv"
Debug.Print URL
' Stop
With Worksheets("Input")
With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("A1"))
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
.QueryTables(1).Delete
'Pamela, this next line makes the application stop and wait 10 seconds
' Application.Wait (Now + TimeValue("00:00:10"))
End With
'Pull Dividends only for each ticker
URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range(cell).Value & dateparamsB & "&g=v&ignore=.csv"
Debug.Print URL
With Worksheets("Input")
With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("I1"))
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
.QueryTables(1).Delete
End WithThe tickers in this particular project are AMZN, VFIAX, MSFT, INTC, BA, GOOGL, and SPY. All the tickers are valid.
The problem: Once the code reaches the sixth (sometimes seventh) ticker, it stops and says
Sorry, we couldn't open 'http://ichart.finance.yahoo.com/table.csv?s=GOOG&a=... etc.
When I press DEBUG it takes me to the .REFRESH BACKGROUND QUERY: =FALSE line in the bottom half of the code.
I researched this and it said that the query could be taking me to an invalid URL. However, I had the code spit out the URL which I copied and pasted into my browser and the URL was fine.
Then, I thought perhaps I needed to program a time break into the code to give it a little breather. [I was grasping at straws!] The time break did give the program a breather, but it didn't help with the problem.
Interestingly enough, I can experience the error as described and then ten minutes run it again and it works fine even though I have made no changes!
Please, please help. Also, I am somewhat of a VBA newbie, so please be gentle and patient, and pretend I know very little about VBA. :D
Thanks,
Pamela
P.S. In an attempt to see if it was GOOG that was the problem ticker, I used the following tickers: AMZN, VFIAX, MSFT, INTC, BA, MSFT, and SPY--replacing GOOG with MSFT since I knew it worked with MSFT as MSFT is also the 3rd ticker. It still gives me the error on MSFT as the sixth ticker.
Last edited:
