AFL Stats, Manipulating Dropdown Boxes, fireEvent

Hi everyone,

I haven't done much VBA interaction with IE and am doing this to learn as much as I can. So any help/tips or tricks/better ways to do this would be greatly appreciated. I have already spend a huge amount of time getting to where I am now and am using Excel Forum as a last resource, so I'm sincerely asking for help.



Please see the following website: http://www.afl.com.au/stats

Problem 1: I am able to get vba to select "SEASON" from the dropdown boxes But the data stays the same (very wrong). I think I am missing an .fireEvent or run Function? or a "reload"?
Problem 2: I an unable to find any unique IDs for selecting "ROUND", So I am unable to manipulate this for TEAMS or PLAYERS. (I haven't a clue how to work around this)
Problem 3: Once I am able to manipulate "SEASON", "PLAYERS" and "ROUND", I am interested in manipulating "Advanced Options" (I haven't looked at this yet)
Problem 4: After "Advanced Options" I want to extract the data to excel. (I haven't looked at this yet)

Please Login or Register  to view this content.

Edit:
I have also also linked the question to stackoverflow http://stackoverflow.com/questions/4...28831_43177027


Thanks guys!

Jimmy

OP updated posf

Originally Posted by JimmyWilliams

Actually, all the stats are available on the website. they're just displayed very poorly.
I am more interested in using this as a way to practice and learn more about how to manipulate IE. So i really need someone to show me better methods/ how to get the job done.

i think David is right in this case
while websites like this offer the information freely on their site, they do not people to scrap their sites without actually visiting the website
it would how they justify the success of the website as well as collect ad revenue

Excel is NOT the best tool for the job, quick google can you will get sense and feel of what is

my personal opinion is that Web scrapping (or whatever you want to call it) generally is against most websites T&C's
if i was a website owner i would want people to visit my actual front page instead of trying to just go in the backdoor and take everything i have on offer

however this isn't going to stop people trying to do it

Originally Posted by JimmyWilliams

I have had some people tell me to learn java or python for web scrapping. Do you have any opinion on this, should I continue trying it with VBA or start with a different language and then try to have it exported into excel?

I would defenitely opt for python for web-scraping. It's easy and fun to learn the language, especially if you have some programming background

Hi JimmyWilliams,
I am not quite experienced enough to get you started as I have only moderate VBA skills and an initial bit of experience with web scrapping.
But some general comments that might be of interest to you……

I started learning Excel a couple of years back for a personal project that included getting a lot of data for the nutritional values of food products. I needed to have this either_..........
_ into permanently
or
_ quickly accessible, that is to say, temporarily, into,
_.........a main large file.

The info came anywhere and everywhere from typing in manually what was on products labels to getting info from lots of different web sites, commercial, research, academic… etc… etc…

I made two big mistakes:
_1 Not learning VBA earlier. This is because some things that are very difficult and memory eating with formulas are ridiculously easy with a few simple lines of code.
_2 I did not get , and get a good understanding of, a scrapping code earlier for one of the free info giving sites that I often used. …...I made a lot of formulas and code to try to get the data I wanted into the form I wanted after I had done a simple manual copy and paste of bits of info from the web site.

Then ,eventually, I did make a start at “scrapping” myself, and also, like you, went initially, the “IE.navigate” way. I soon moved on to the “Back door HTML request way” after some initial great help from Leith Ross ( https://www.mrexcel.com/forum/excel-...cations-2.html )

I have older computer and operating system, some use Internet explorers 7 - 9 which either don’t work any more on most things or take ages. Some “IE.navigate” things don’t seem to work anymore for me either.- … but….. Never the less, - the Back door HTML request way code I have/ had worked still at the speed of light on computers and systems that barely manage to do anything else much anymore… other than die….

I had to revisit / rewrite the code after I got caught out last year by a change in the Website HTML source code. https://www.excelforum.com/excel-pro...te-change.html

I got some great help not only on modifying my code , but , more importantly in my opinion, on actually understanding fully the code, ( some chap Kyle I seem to remember …. :-) )
https://www.excelforum.com/excel-pro...ml#post4439005
http://www.excelforum.com/showthread...t=#post4446317
https://www.excelforum.com/developme...ed-please.html
‘EP: https://www.excelforum.com/developme...ml#post4450773


I finally came up with, what is for me, just about my favourite code now: https://www.excelforum.com/developme...ml#post4449914
https://www.excelforum.com/developme...ml#post4449690


The speed is so fast, that rather than store all the info that is available freely from that particular site, I access it “real time / instant stylio” when I need it based on a closed workbook reference pseudo intellisense search of the product name in the first column stored “pseudo XML like” ..……( https://www.excelforum.com/excel-pro...ml#post4673161 )

But, just now, I cant get much Internet access for a while. Last night I had a chance for a few hours. So, I took my basic final scrapping code from above, turned it into a “Pseudo” Function – (
Sub GetNutsEP(ByVal URL As String, ByRef arrdbHds() As Variant, ByRef arrdbVls() As Variant) in the attached file ). I made another function again based on the same great help from before, Sub GetdebinetLinks( , along with a few simple code lines in a couple of routines, Sub GermanLinks() , Sub FilldebinetG2().
Last night while I still had internet, I set off Sub FilldebinetG2() , then went to bed. When I went to the Toilet later, the code was already finished. The results are in worksheet “debinetG2” of the uploaded file, which also has those last few codes in it. ( The codes are all actually quite short and compact, they just look long due to all the ‘Comments in them. )

It was quite a life saver. And amazingly simple to get all that info. I finally have about 35000 rows of info each row with about 150 Nutrient values columns: … To compare – I often needed a week or more each, to get between a few 100 to a few thousand rows of such info with much less number of column per row info in a simple table form similar to the one that my computer gave last night in a few hours on its own while I slept.
It would have taken me without web scraping, I expect months to get all that info one way or another.
I am very grateful to have got all that info, so quickly , in one go, as a back up to use now.

After getting all this info from the site I scrapped, I am encouraged to go more often to main site, look at what is to offer etc, try our stuff maybe etc.. etc... I think often when a free version of something works well you are more likely to consider them for a paid upgrade or something else or recommend them further.
I am not sure if the HTML back door request way is not registered by the site as a visit/view ?? I do not know if you are seen , or incognito?? It would be a shame if they made such site difficult to scrap. I expect it would not be too difficult to mess a source code up a bit to make it difficult to scrap. Some sites I wanted to scrap seemed to have the whole site in some continuous string so I could not see how I would do the “HTMLdoc.getElementsByTag___” stuff to work. The “HTMLdoc.getElementsByTag___” is one of the key things to get sets of what you want

Bottom line , is,

Originally Posted by JimmyWilliams

....this more as a learning experience, I want to develop these skills but am at an impasse and am not sure how to proceed. I want to be able to extract the data and manipulate it all in a single button, because once I am able to do that, I could re-apply the same in other places......

my advice, go into Google Chrome, get your web site up, right click and select “view source code”. Copy it to a simple text File, or preferably Notepad ++ . If you can, get a hard copy of it.
Study it. Read up or check out Threads scrapping with the Back door HTML request way
Have a go at writing a code , at least as far as getting your .responseText and creating your HTML Document object from it.
Then see if you can get help to get you further.

Alan.

P.s.
As for API. ?? I seem to see that recommended a lot. I never see any explanation of how to actually use one or “it”. Seems like a blind way of doing something and relying on someone to have done something so that “it” always works even if the HTML changes in a site. I couldn’t find anyone who really knew what “it” was in as much detail to actually use ”it” or make one of “it” ???.
I think ideally if you get a good scrapping code that you understand, then either alone or with some help you will be able to modify your scrapping code appropriately. With hind site it was incredibly easy and obvious to change my code when a site change caught me out , but as I did not really understand fully my code initially, it was very difficult.
Thanks to some great help here I have that well sussed now



File : “debinet.xlsb” https://app.box.com/s/drc8tv66yktse4dspbx24pzb854rqdfv

Hi Jimmy,

Using 2016 and Data -> Web, I can pull down this data. Power Query or Get & Transform is a lot more powerful in newer versions of Excel. Is this what you wanted?

PQ Load Table 0.xlsx

Hi Jimmy,

Originally Posted by JimmyWilliams

..thanks in the meantime!

Yous welcome, thanks for the feedback.
Good luck
Alan

EDIT: Thanks for the Rep

Originally Posted by JimmyWilliams

...I'll try learning python ..

Hi Jimmy,
Just incase you did not catch these Threads on Python..
https://www.excelforum.com/the-water...-in-excel.html
https://www.excelforum.com/the-water...thon-tips.html
Alan

I was thinking of having a quick look at Python myself, later when the snow comes...