Click here to Skip to main content
14,640,737 members
Rate this:
Please Sign up or sign in to 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
Updated 10-Feb-11 7:15am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100