WebQuery Issue: Unable to download complete table.

[jdc2k2]

I'm trying to create a macro that will download corporate financial data from a website for the purposes of stock research.

I believe I have it working for the most part, but one table specifically is giving me trouble. The balance sheet is not downloading the complete table. I've tried manipulating the code to include the whole webpage, various tables, or the specific table (2). It seems to stop at the same point no matter how I go about it.

The table in question is a balance sheet, and the last row should be "Diluted Shares Outstanding". When I use Excel's webquery (either manually or various ways through a macro), the last row is "Other Long-Term Assets". That is only the first 1/4th of the table.

For the example below, the URL that I am trying to pull information from is http://www.smartmoney.com/eqsnaps/i...=YB&isFinprint=1&framework.view=smi_plainView.

Please look over the code below and see if I may have made a mistake. I also wonder if the HTML on the site may not be formated correctly. I took a look at the source of the webpage, but couldn't spot anything.

I should also note that this is not an issue with the Cash Flow and Income Statement data from the same site.

Thank you for any assistance that is provided.

Sub BalanceSheet()

    Dim QT As QueryTable

       
    'Balance Sheet 1
        MyName = "Balance Sheet 1"
        ConnectString = "URL;http://www.smartmoney.com/eqsnaps/index.cfm?called=1&story=financials&timewindow=1&symbol=F&opt=YB&isFinprint=1&framework.view=smi_plainView"

        Sheet2.Activate

        Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("A1"))
        With QT
            .Name = MyName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingRTF
            .WebTables = "2"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
        End With
        
        QT.Refresh BackgroundQuery:=True
        
End Sub

 

[sunnyland]

Hello there

It seems to be one line in the HTML code that causing the problem. There is 2 "" on 1 line . I saved the source on my computer and try to use the original html code and it only import as you said.

By removing on ", it import everything. I don't think nothing you can do about it, if not may be letting them know that this error this is causing a problem for import.

If you want you can still copy and paste it will paste fine.

The line in question is

<tr bgcolor="#FFFFFF"">

Just before:

<td width=1 bgcolor="#7799bb" height="20">[img]/s.gif[/img]</td>

Sorry not to provide a solution.

Regards
Francoise

 

[jdc2k2]

Ah, I see it now. I thank you very much for spotting the cause. Now I just have to find a work around.

I don't know that alerting the administrators of the site would have a speedy resolution.

I am wondering if there is any trick in having Excel ignore specific errors such as this.

I spent time this evening exploring the Open Office Calc program, and it's link to the site populates correctly. The big headache there is that I'd need to familiarize myself with a different scripting language.

Does anyone know if there's a work around for web queries in Excel that are being interrupted by a minor html coding issue.?

 

[jdc2k2]

Well, after doing a lot of researching (primarily from these forums), I was able to piece together code that gave me the desired result.

I still need to do some tweaking, but it overcomes the issue in the html that was hanging up the regular web query.

Thanks to everyone who posted relevant information for this issue sometime in the past. After reviewing the many sources of information, I was able to fix this issue that was plaguing me.

Sub BalanceSheet()

Dim IE As New InternetExplorer
Dim MyTable As HTMLTable
Dim cntRow As Long, cntCell As Long
Dim arrTable() As Variant


    IE.navigate ("http://www.smartmoney.com/eqsnaps/index.cfm?called=1&story=financials&timewindow=1&symbol=F&opt=YB&isFinprint=1&framework.view=smi_plainView")
    Do Until IE.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    Set iTables = IE.Document.all.tags("TABLE")
    Set MyTable = iTables(1)
    
    ReDim arrTable(MyTable.Rows.Length, 13)
    
    cntRow = 1
    For cntRow = 1 To MyTable.Rows.Length - 1
        cntCell = 1
            For cntCell = 1 To MyTable.Rows(cntRow).Cells.Length - 1
                arrTable(cntRow, cntCell) = MyTable.Rows(cntRow).Cells(cntCell).innerText
                cntCell = cntCell + 1
            Next
        cntRow = cntRow + 1
    Next
    

Sheet2.Range("a65536").End(xlUp).Resize(UBound(arrTable, 1), 13).Value = arrTable
    

IE.Quit

Set IE = Nothing
Set MyTable = Nothing

End Sub