Downloading stock prices from Yahoo Finance using Excel VBA - Refresh BackgroungQuery:Fals

Question

5.00/5 (1 vote)

See more:

Hi
Downloading stock prices from Yahoo Finance, against a list of stock codes that are valid works fine e.g.

AA, AXP, BA, C, CAT, D etc.

The Query Quote coding used being as follows:

QueryQuote:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False

    Range("C1:I1").Select
    Selection.ColumnWidth = 8

The problem I encounter is if the list of stock codes, include an invalid code (in this example I have used 'VVV', or a stock code that is valid, but is not in the Yahoo database for the specific date that I am downloading, the following error message is displayed:

"Run Time Error 1004 Unable To open<br />
<br />
http://chart.yahoo.com/table.csv?s=vvv&a=1&b=3&c=2011&d=1&e=The Internet site reports that the item you requested could not be found (HTTP/1.0 404)"

The system stops, and I cannot move forward.

On debugging - .Refresh BackgroundQuery:=False is highlighted.

I would like to be able to add code that would skip any invalid entries such as the above, and which would then access the next valid stock code and download the stock price against this.

Any soultion that anyone could provide would be most appreciated.

Regards

Mike Simmons

Posted 10-Feb-11 7:08am

Mike Simmons UK

Updated 10-Feb-11 7:15am

Yusuf

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2011-02-11T08:25:00

Which version of MS Excel: 2007?

In my opinion, your code is not optimal and have no context.
Why?
1) Try to run this code on Sheet1, Sheet2 or Sheet3 and on any sheet with data. You'll lost your data!
2) Your application is as quick as your code.

Option Explicit 'declare variables

Sub CreateQT(qurl As String)
Dim wsh As Worksheet, dstRange As Range
Dim qt As QueryTable

'ignore errors and delete existing QueryTable
On Error Resume Next
Set qt = wsh.QueryTables(1)
If Not qt Is Nothing Then qt.Delete

'on error go to error handler
On Error GoTo Err_CreateQT

'create object variable: Worksheet
Set wsh = ThisWorkbook.Worksheets(1)
'create object variable: Range
Set dstRange = wsh.Range("C7")
'create object variable: QueryTable
Set qt = wsh.QueryTables.Add("URL;" & qurl, dstRange, sSQL)
With qt
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .SaveData = True
End With

dstRange.TextToColumns Destination:=dstRange, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, other:=False
wsh.UsedRange.ColumnWidth = 8

Exit_CreateQT:
    On Error Resume Next
    'delete every object variables
    Set dstRange = Nothing
    Set wsh = Nothing
    Set qt = Nothing
    Exit Sub
    
Err_CreateQT:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_CreateQT
End Sub