VBA code works sometimes, the breaks at other times. Am I missing something in this Code?

Question

Thank you in advance for the help.

When I run tickers through the code it stops. This is pulling mutual fund data, so if you want to test the code yourself...I would Use(INDZX, CULAX, ABRZX, TAGBX, PRPFX (Don't use these Mutual funds, they are no good; just for an example)). I literally have to sit by my computer and erase the tickers where the data has already been pulled over so that it can start over again; very time consuming.

Can one of you please help me out.

Let me know if you have further questions on this.

Just to add when it completely breaks, and look at the debug, it highlights the "Do While IE.readystate<> 4: DoEvents: Loop

The other issue I am having is that when there are no tickers left, the code continues to run.

Sub upDown()

Dim IE As Object, Doc As Object, lastRow As Long, tblTR As Object, tblTD As Object,  
strCode As String
lastRow = Range("H65000").End(xlUp).Row


Set IE = CreateObject("internetexplorer.application")
IE.Visible = True


last_row = Sheets("Tickers").Range("H1").End(xlDown).Row

ini_row_dest = 1

Sheets("upDown").Select

Sheets("upDown").Range("A1:m10000").ClearContents

Application.ScreenUpdating = True






     For i = 1 To lastRow
    Application.StatusBar = "Updating upDown" & i & "/" & last_row

    row_dest = ini_row_dest + (i - 1)

    strCode = "Tickers"    ' Range("A" & i).value  
    list_symbol = Sheets("Tickers").Range("h" & i)
    IE.navigate "http://performance.morningstar.com/fund/ratings-risk.action?t=" & list_symbol

    Do While IE.readystate <> 4: DoEvents: Loop

    Set Doc = CreateObject("htmlfile")
    Set Doc = IE.document

    tryAgain:

    Set tblTR = Doc.getelementbyid("div_upDownsidecapture").getelementsbytagname("tr")(3)

    If tblTR Is Nothing Then GoTo tryAgain
    On Error Resume Next



    j = 2
    For Each tblTD In tblTR.getelementsbytagname("td")
        tdVal = Split(tblTD.innerText, vbCrLf)
        Cells(i, j) = tdVal(0)
        Cells(i, j + 1) = tdVal(1)
        j = j + 2



     Next

    Sheets("upDown").Range("A" & row_dest).Value = list_symbol
     Next i

    Range("A3").Select

    Application.StatusBar = False

    Application.Calculation = xlAutomatic


    End Sub

Why are you tagging this as a Java question? I don't see that this has anything to do with Java, but if I'm missing something, please do tell me. — Hovercraft Full Of Eels, Feb 18 '14 at 04:50
Next of all, it looks like you've done no debugging, and this you need to do before posting here. If you don't isolate the problem first, how will we know where you need help and with what? — Hovercraft Full Of Eels, Feb 18 '14 at 04:52
I just added what I am getting on the debug...sorry still a beginner at this, and I am having difficulty finding someone to pay to do some of the work...so literally duck taping some of this. — eddiezeiter, Feb 18 '14 at 04:54
Please explain exactly what the issue is. Do you get an error message? What is it? You need to start by describing your issue properly. — Nick.Mc, Feb 18 '14 at 04:56
Issue: Code runs through tickers I have on "Tickers" worksheet on column H. It will stop running sometimes after pulling the upside downside capture ratios on http://performance.morningstar.com/fund/ratings-risk.action?t=ABRZX&region=usa&culture=en-US. — eddiezeiter, Feb 18 '14 at 04:58
My solution currently: I literally sit doing other work and if it stops, I stop the work I am doing; delete the ticker symbols that have already pulled data and start it over as it never run before. I am pulling about 3800 tickers, so this is like a 4 to 5 hour ordeal — eddiezeiter, Feb 18 '14 at 05:00
As far as error message. Not really, unless it doesn't pull the 1st ticker, then it gives me a "compile error" — eddiezeiter, Feb 18 '14 at 05:01

score 0 · Accepted Answer · answered Feb 18 '14 at 06:07

From your description, when it's 'stuck' you press CTRL-Break, and it stops at

 Do While IE.readystate<> 4: DoEvents: Loop

This means that IE is busy. You should probably work out why. What happens if you switch to the IE window? Maybe it has a popup? It's entirely likely that morningstar.com has detected that you are scraping data and is halting it. Normally you need to pay some kind of a subscription to get this kind of thing.

Anyway what you could do is put in a 'watchdog' that detects this state and tries to recover. Here is some code below but it is basically a hack and I don't quite understand how your row index is meant to work. The code below uses Goto which is just a lazy way of doing things but it is certainly no worse than the existing code.

Anyway try it and see. What you might find is that the IE.Quit line might prompt you to close IE, but at least it can restart from where it failed and you don't need to clear the tickers out and start again.

An alternative solution might be to save the half finished workbook and alter the code to pick up from where it left off based on which tickers have data and which don't

Sub upDown()

Dim IE As Object, Doc As Object, lastRow As Long, tblTR As Object, tblTD As Object,  
strCode As String
Dim iWatchDog as Integer
iWatchDog = 1

lastRow = Range("H65000").End(xlUp).Row
ini_row_dest = 1
Sheets("upDown").Select
Sheets("upDown").Range("A1:m10000").ClearContents

Start:
Set IE = CreateObject("internetexplorer.application")
IE.Visible = True

last_row = Sheets("Tickers").Range("H1").End(xlDown).Row


Application.ScreenUpdating = True






For i = 1 To lastRow
Application.StatusBar = "Updating upDown" & i & "/" & last_row

row_dest = ini_row_dest + (i - 1)

strCode = "Tickers"    ' Range("A" & i).value  
list_symbol = Sheets("Tickers").Range("h" & i)
IE.navigate "http://performance.morningstar.com/fund/ratings-risk.action?t=" &     list_symbol

Do While IE.readystate <> 4
    DoEvents
    DoEvents
    DoEvents
    DoEvents
    DoEvents
    iWatchDog = iWatchDog + 1
    If iWatchDog >= 10000 Then 
        Application.StatusBar = "Stuck - resetting"
        iWatchDog = 1
        IE.Stop
        IE.Quit
        Set IE = Nothing
        DoEvents
        DoEvents
        DoEvents
        DoEvents
        Goto Start
    End If
Loop


Set Doc = CreateObject("htmlfile")
Set Doc = IE.document

tryAgain:

Set tblTR = Doc.getelementbyid("div_upDownsidecapture").getelementsbytagname("tr")(3)

If tblTR Is Nothing Then GoTo tryAgain
On Error Resume Next



j = 2
For Each tblTD In tblTR.getelementsbytagname("td")
    tdVal = Split(tblTD.innerText, vbCrLf)
    Cells(i, j) = tdVal(0)
    Cells(i, j + 1) = tdVal(1)
    j = j + 2
Next

Sheets("upDown").Range("A" & row_dest).Value = list_symbol
 Next i

Range("A3").Select

Application.StatusBar = False

Application.Calculation = xlAutomatic


End Sub

Where is this 3,800 lines of ticker data eventually going? into a database or is it fed into another Excel sheet?

Thanks, I'll try it out. It's fed into another excel spreadsheet. How do we send each other emails? — eddiezeiter, Feb 18 '14 at 06:58
I may need some good advice on other things I am working on and want to make sure that I can provide you value as well — eddiezeiter, Feb 18 '14 at 06:59
Looking at your other questions you are to be commended for your effort in working this whole thing out! I'd rather not communicate directly but I will keep an eye out for your questions on here. — Nick.Mc, Feb 18 '14 at 07:19
It still breaks sometimes...and what I mean is that the web page just stops and doesn't loop through all the tickers...but the code is much cleaner now. The other final issue is that when it does go through the entire ticker list...it keeps continuing as if there are other tickers when there is not, the interesting thing is that it copies the last tickers column J and K and puts it in column A and B downwards until it hits a break. — eddiezeiter, Feb 20 '14 at 05:17
Here is a list of tickers if you want to test it out...vbmpx pttrx nhilx vwnax viiix fcntx vempx wfdsx nscrx vtpsx dodfx irgix vtxvx vtwnx vttvx vthrx vtthx vforx vtivx vfifx vffvx — eddiezeiter, Feb 20 '14 at 05:20
Yes there's something not right in the loop counters - I don't quite understand the logic. If I get a chance I will try it out but I can't guarantee a timeline at this stage. In what way does the web page 'stop'? does IE become non responsive, or is it looking for the URL but just not getting there? Unless you have some licencing with these people then they are probably detecting your activity and stopping it. — Nick.Mc, Feb 20 '14 at 05:55
So, for example, taking from the tickers above; let's say it brought over information from vbmbx, pttrx, nhilx; It'll then load the next ticker URL "vwnamx" and remain on that tickers page, but won't bring information to excel. So I see vwnamx web page. I have thought about that in the past with the licensing, but since I am a user and not profiting from it, it says in the terms that I, as a user can use the data for personal use. And maybe they are. Anyway, give it your best...thanks for all the help...I know at some point I'll find a round about. — eddiezeiter, Feb 22 '14 at 17:32
What's interesting is if I go to http://performance.morningstar.com/ and type in `vwnamx` I get no response from the web page but if I type in `nhilx` I do get a response - maybe it has something to do with the actual code. Why don't you take note of which codes cause the issue and see if it is the same ones? — Nick.Mc, Feb 23 '14 at 03:37
Also `nhilx` gets an autocomplete and `vwnamx` doesn't. Glad you sorted it. You do need to go through the code and clean it up as the index in the loop isn't quite right. — Nick.Mc, Feb 23 '14 at 03:38

VBA code works sometimes, the breaks at other times. Am I missing something in this Code?

1 Answers1