Need input on what's possibly going on with half-working VBA (Data Scraping)

Hey guys,

I hope some gentle souls will be able to provide some answers, cause I'm completely loss right now.

For the past 3-years or so, my partner and I have been using a VBA to collect stock data from the web. Everything's been running great, aside from a few occasion where the html codes from the pages were tweaked, which forced us to adapt the VBA accordingly. But that was to be expected, so we were able to adjust. Until a few months ago...

Then, stranger things began to happen. My partner and I are working in separate offices, with our own internet connection. For some reason, one morning, the same VBA would work fine for one of us, while the other one couldn't retrieve any data. The following morning, it would be the other way around. And it seemed totally random. I could go on for days being able to collect the data, while my partner couldn't...then nothing would work for me, and vice-versa. To a point where, a few weeks ago, neither of us could pull any of the data...at least on the US version of the site. So we started to switch to the CAN version of the site, and everything worked fine again, for awhile. When it stopped working, we went back to US, and boom...here it was, working like a charm once more. We started switching back-and-forth, with no problem. Up until now...since Monday, our VBA can't seem to collect any data out of both sites. So we started using the AUS version, and it worked for a couple of days. And then, it stopped working this morning. So we're now on the SA version, and it's working.

So I guess my question is...what do you think is going on ?

Is it just a small change in the html codes that prevents the VBA from finding the data we used to collect ? Is it something bigger, like a protection on the site that is very slowly being installed on each version the site ? Is it related to our IPs ?
I looked at the codes of both page, and couldn't spot any differences in the segment of the codes where the data itself is being pulled off from. But it's been years since I've played with html, so I can't say for sure.

If anyone is willing to take a quick look and give me a quick diagnostic, it would surely help a lot !

I've attached a small version of the file. You'll see three buttons in the upper left corner: a big REFRESH button that launches the VBA procedure, and two smaller buttons that allows to switch the URLs to US version (that currently doesn't work) and SA version (that does at the moment)...and the table in which the DATA is being stored.

If anyone's willing to take a look, it would really help us out.
I'll be in a corner of my office, fighting the tears, being very afraid of the answer. :S

Originally Posted by MarvinP

Hi Komic,

Try to make this change to your code:

Please Login or Register  to view this content.

Instead of waiting an arbitrary amount of time, put in a "DoEvents" which waits for the os to come back. (Hope that works on the Mac?). I'm wondering if the net speed at your different locations might have been the problem.

Hi Marvin, and thanks for taking the time to look at the document.

That segment of the code acts like a timer, so it is designed to 'slow' the procedure down (takes a short pause between each line) so we won't get timed-out by the site, which happened in the past after X amount of connection in X minutes. So I don't think (at least if I understand what you're proposing correctly) your suggestion would solve the current issue, as it speeds up the procedure (I think).

Additional note, since we're on the subject of being timed-out: that doesn't seem to be the current issue. In the past, time-out occured only after DATA had been collected on a few hundred pages. The site would then time us out for about 10 minutes (couldn't even visit the page manually with Chrome), then it would come back. We installed the timer segment, to slow the procedure down, and the problem was solved. The current problem seems to be different, as it doesn't seem to be a time-out issue. We still can visit every page manually, and our procedure can't extract 1 single DATA, not even the first line. At least, on our end. Have you been able to collect data from the US version on your end ?

Originally Posted by TMS

For what it's worth, both locations seem to work for me. Both can be a little slow to start but then click through fairly regularly from 1 to 20. I've cleared the data that is present in order to check that it is refreshed, and it is in both cases.

I've run both URLs several times without any apparent issue.

Oh damn...that's what I was afraid of...

UPDATE

I forgot to mention one thing (that just presented itself, as I'm writting this).

When the VBA fails to collect the DATA, we'd usually try to refresh a couple of times with similar result...until out of nowhere, one line would magically work (could be the first one, could be #3677, totally random). And from that point on, once the VBA successfully gets ONE line, we're usually good to go for the entire day, without any hickups.

This happened again less than two minutes ago. Right after TMS said that both sets of URLs were working fine for him, my partner and I started experimenting with a VPN...just to see if it would make any difference. It didn't at first. I could still get the DATA out of the SA site, but not from the US. Tried a few different servers, no luck...until I tried one in NYC. Went through the US refresh like a charm. Disabled my VPN, ran another refresh from the US site...and now, all the DATA is being collected like it used to, no problem, no matter if my VPN is activated or not. But only for me. My partner, in his office, tried to run (with and without the VPN) the same refresh that worked for me, all blanks.

I did a refresh using the CAN site right after, didn't work. Enabled my VPN, tried a couple of different servers, never got the VBA to work properly. Went back to US, still works...VPN or not.

So I don't think it was the VPN that fixed my issue. But I really don't understand what's causing the issue. And why the issue disappears out of nowhere, once it gets ONE line correctly.

I'm completely lost.

Thank you so much for giving it such an extensive look, really appeciate it (and the work you've done on the .ClearContent part, that looks so neat compared to what it used to be).

I've got to leave for a meeting, but I'll make sur to test your suggestion upon my return, and post the results. US Version works for me right now, but I'll be able to test it out with the CAN version of site, that still doesn't work for me.

In the meantime, you and Marvin can consider yourself Rep'd for you efforts. Thanks a ton !

Originally Posted by TMS

Don't know what to say. I've been playing with this for the last hour or so ... purely out of morbid interest ... and it has worked every time, both sites. Sometimes it's a little slow to start but when it gets going it clicks through.

I've checked the code and re-indented it so (I think) it's easier to follow. I've also shortened the .ClearContents and, for testing, added a Time Stamp so I could actually see that the code had run through and refreshed the data.

The only thing that is odd, maybe, is that every so often it gets to record 5 and then I get Excel Not Responding until the last record has been processed.

Just out of interest, I'd be inclined to comment out the

Please Login or Register  to view this content.

line of code. See if it fails and, if it does, what error do you get. I have tried it and not seen any errors so, I'm guessing, under normal circumstances, your code could/should run through unhindered.

Alright, gave your file a try as soon as I got back. Unfortunately, same result for me as before. US and SA versions worked fine, but when I switched it to CAN (replace the https://www. by https://ca.), the procedure couldn't collect anything.

Regarding the "Excel Not Responding", we're getting that as well. It didn't really bother us, as it didn't seem to have any effect on the refresh procedure itself. The "Not Responding" would just vanish after the refresh is complete, so we never really paid attention to it, since we can't really do anything else with Excel while it's refreshing anyway.

As far as the "On error" part goes, I ran in with it as comment like you left it in your file. Didn't get any "errors" per say, the procedure went through 1-20, but left me with a blank table on CAN as before.

I might be spitballing here, but do you think the ads on the site could modify the html code to a point where the VBA wouldn't be able to find, or misread the line of code I'm trying to retrieve the Data from ? Without giving a "Subscript out of range" error ?

I just find it so odd that the exact same file, with the VBA same procedure, could return result on one CPU, in one office...and not the other. Like right now, my US and SA refresh are both working fine, and they did for you as well. But that same document, which I transferred to my partner after it worked for me and you, don't return anything for him. It's seems so random.