+ Reply to Thread
Results 1 to 26 of 26

Automatically search website (using criteria from excel) and extract element information

  1. #1
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Automatically search website (using criteria from excel) and extract element information

    Hi guys,

    I've been searching relentlessly for a solution to my problem but with no luck, so I am turning to you for advice.
    I have around 7000 football matches, in excel, and want to find the distance between the stadiums of the two football teams for each match. I have a website where I can input both teams' names and search for the distance between their stadiums. I would like to set up a macro that uses the football team names from column B and C as search criteria, enters them on the website, clicks search and then extracts one element ... the driving distance in miles.

    I cannot for the life of me find anything remotely close to this online that would act as a template for me to work with and develop this macro. Perhaps there is a name for this which I don't know so I am searching for the wrong thing. Could you please let me know if you are familiar with the approach I am trying to take, or whether there is a better way to go about obtaining the distance information? If a thread on this matter already exists then please please refer me to it because I haven't found it yet =(

    EDIT:

    URL for obtaining distance info: http://www.sportmapworld.com/distanc...st-ham-united/

    The "distance from" and "distance to" fields would need to be updated with the info in columns B and C of the excel file, and the "driving distance" (in miles) is the element I would like to extract.
    Attached Files Attached Files
    Last edited by Busy001; 06-03-2016 at 10:43 AM.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    You forgot to include the URL (Website) and what and where in the site you are looking for.

  3. #3
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hmm I have one potential idea but its not bullet proof. I checked the URLs and they are of the format: http://www.sportmapworld.com/distance/HOME TEAM (from column B)/AWAY TEAM (from column C)/

    I can build these URLs using the input from columns B and C like this:
    ="http://www.sportmapworld.com/distance/"&Table1[@HomeTeam]&"/"&Table1[@AwayTeam]&"/"

    Then I have to copy this newly created URL and paste as Values to ensure that the URL is now in text format as opposed to a formula.

    Then I can record a macro that conducts a web query: so I copy the newly created URL (Which is now in text format); then I go to DATA; From Web; paste the URL; click Go; copy the "driving distance" info; close the query window; paste the "driving distance" info in the appropriate cell.

    There are two problems with this approach:
    1) The team names in columns B and C do not always match the format of the website e.g.
    B9 (Southampton) and C9 (Bolton) would lead to the following URL: http://www.sportmapworld.com/distanc...ampton/Bolton/ but the correct URL is http://www.sportmapworld.com/distanc...ton-Wanderers/
    So, if there is a way to find the way the website has named each team then I can update columns B and C to match that and the URL would work. Any ideas??

    2) I need to create a loop so that the macro repeats the above process for each row. That is something I don't know how to do? Any ideas or good resources that would help me solve this quickly?

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    No 2, looping is not a problem. You can add each cell in a loop and attach them to URL as you did manually.
    As you pointed out, the issue you have is the names do not match. I tried to find the names on the site
    http://www.sportmapworld.com/map/soc...remier-league/
    But this suffers from the same issue. For e.g Southampton is spelled as Southampton F.C.
    Where do you get the names on column B and C?

  5. #5
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    The names came from http://football-data.co.uk/
    Under "site resources" on the right, then "historical data"

    What's worse is that the URL for southampton isn't even based on the way the site lists the club's name ... http://www.sportmapworld.com/distanc...mpton/arsenal/
    So for Southampton, they list the name as Southampton F.C. but in the URL they only refer to it as Southampton. Maybe what I have to do is to set up the loop as you mentioned and then let it do its work, then I can review the teams for which the macro did not work and I can manually check those teams' names, manually edit them in columns B and C and then re-run the macro... and I can hope that most of them would work without much problems so I wouldn't have a huge list of names to check manually.

    Testing out if the loop would work now.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    This is the basic code for lopping through each cell and opening the site, but the code does not do much.
    The names must match, but not as the names on both columns are now.

    Please Login or Register  to view this content.
    I will be off site in uno momento.

  7. #7
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Thanks AB33! I tried to combine the code that you sent with the code that I had recorded, which is as follows [the L7 is actually supposed to be L2]:


    Please Login or Register  to view this content.
    BUT... I had no luck! Here is what I did:

    Please Login or Register  to view this content.
    Unfortunately, the only thing that this does is that is searches the site on loop and pastes the following in L7:

    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
    "http://www.sportmapworld.com/distance/Barnsley/West-Ham-United/"
    Range("L7").Select
    ActiveSheet.Paste

    I've managed to correct the names of the teams in columns B and C, so now in column K the URLs are the correct ones. But even without column K, your code actually works perfectly. The only thing that I see slightly problematic is that it runs very quickly so I am not sure if there is enough time for the "driving distance" info to load. And the other issues is how to actually copy that piece of information and populate the excel file with it. Can you please help again?

    Attached is the excel file with the data set, the file that I have been working with it (only the top 20 or so rows are error-free, with certainty, the rest I will update as I go along. )
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    I am stumped by this request. I am sure I am missing something, but I can not seem to get the values from the table. I keep getting zero values. These are the values which are shown on the source view code. I suspect the distance calculation is worked out by JavaScript and google distance map.
    Even using excel's exporting table does not seem to work.
    I will let you know if I made any headway.

  9. #9
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hi AB33! I found a solution! Indeed, you are correct. After looking into the souce code I realised that the website only displays the search results from google maps, in a readonly format. So, I wondered whether I could bypass the website and conduct the calculation between my excel file and google maps directly. And luckily I stumbled across this:

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    If you found a solution, please share it with us.

  11. #11
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    A code that computes the distance between two locations: https://www.reddit.com/r/excel/comme...s_api_to_find/

    The results are in the format "time | distance" so I had to split that and work with the distance only, which also had to be converted from km to miles but excel has a function for that.
    Next problem was that I only had the team names and that doesnt give the most accurate results. I needed the addresses. After some research I found a nice website that has all the info I needed but its on a different link for each stadium. So, I needed to scrap that somehow. A couple of months ago I came across "Web Scraper" for Chrome - its awesome and it did the job perfectly. After exporting the results into excel, I did a quick vlookup so that next to each team I had the correct address. Then I applied the function code from the reddit link. It worked like magic!

    After doing some random sample testing I have to say that I am quite pleased with the results.

    Thanks for all your efforts! The code you posted earlier... I was able to initially use that and apply it to the new website that had all the addresses but then I couldn't manage to point out accurately exactly which element I needed to extract. So, then I moved to the above solution. And now I can saw "case closed" (at least for this variable).

  12. #12
    Registered User
    Join Date
    06-03-2013
    Location
    NSW
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hi Busy001 and AB33...

    Hey Busy001, have you ever heard of 'Daft Logic' ???? It's a Google Opensource Map Distance Calculator. I have been using it now for around 5 years or so and it is INCREDIBLY accurate. You can use it's source and even paste to and extract from (such as a datascrape).....On that note, have you tried Excels' in-built datascrape?

    H.

  13. #13
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hi Hawko1973,

    No I hadn't heard about it before. It's awesome though!
    As for excel's in-built datascrape... I only tried to do a webquery. not familiar with its inbuilt datascraping capabilities, but if it has them then that's pretty awesome too! Will check it out :D

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hi Hawko1973 and Busy001,
    Thanks for the info and link!
    I will have a look at some point today.

  15. #15
    Registered User
    Join Date
    06-03-2013
    Location
    NSW
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Cool, always glad to help...
    Here's the link;
    https://www.daftlogic.com/projects-g...calculator.htm

    It's totally awesome!
    --If You Can't Measure It, You Can't Manage It--

  16. #16
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Case has to be reopened

    I applied the function to the first 700 observations. Everything worked perfectly. So, I decided to apply the function to another 1300 or so observations.... and it may have crashed Now when I re-open the file, majority of the output from the function is showing up #Value! error, and a few observations actually return an output (very very very few).

    Would any of you happen to know what may be causing this?
    And do you have any advice?

    Attached is the file that I am working with to illustrate the problem. [What's driving me mad is that I saw it work; initially most of the results came back with very very few #Value! errors; but then it all turned to error]. Any pearls of wisdom are welcome!

    PS. I thought that maybe its an overload so I tried to create a new file with 6 observations, set up a module with the function code again, tried to run it but still same type of error. Thought it might be because there are numbers and letters in the text input but I doubt that's it because it still works for some observations.

  17. #17
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    Here is the attachment
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    I have added a code to test the function. It seems to work with the test, but I am not sure with the massive data, as auto calculation takes ages to finish. Some of the rows do not return any value, hence for not value error. You can include the cell address as a string argument on the test function so as to see why a row fails to return a value. I will upload the file once the calculation is finished.

  19. #19
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    When I tested it 5 minutes ago, it worked, not now getting an error on the body line. So, if you get an error, it most highly likely is due to the site problem, but not your code.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    I agree. It seems to be a site problem. I tried a different code. Everything worked till about 2300 observations. Computing anything more results in errors. I am curious whether this means that what I am doing is not permitted or something. From what I read online, the free Google Maps API has a limit of around 10,000 per day so I am not really sure as to why this is happening. Unless, there is almost an hourly limit..

  21. #21
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    This site suggests to get a Google API key if dealing with heavier traffic: http://www.w3schools.com/googleAPI/g...maps_basic.asp

    So I did get an API key but I don't know where to put and how ... I anticipate it has to be incorporated in the firstVal line, but I am not sure what the correct way is. If you know, could you please help me out?

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    06-03-2016
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Automatically search website (using criteria from excel) and extract element informati

    I think the major issue is that Google has a limit of 2500 calls per 24 hrs. The 10,000 limit that I had in mind is incorrect.

    With that in mind, would incorporating the following code deliver the actual error message below? Or do you advise any alterations to it? And should it be added just before the End Function line?

    Please Login or Register  to view this content.

  23. #23
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    It is funny. When I first test the new code, it seems to work, but on the second and further test, it fails.
    The code is not different to the one you had earlier. Both codes parse using msxml2. The second uses reg expression to extract the relevant data, but the issue you have is that getting the HTML body. I really do not know why they do not work all the time.

  24. #24
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: Automatically search website (using criteria from excel) and extract element informati

    Because google throttles the calls. They set an arbitrary limit on calls in a given period to prevent exactly this kind of behaviour - it's explicitly against their terms and conditions.

    I've written a post in the tips and tutorials section as to an alternative which doesn't (AFAIK) have these stipulations in the T&Cs

  25. #25
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Automatically search website (using criteria from excel) and extract element informati

    Hi Kyle,
    Good to see you back!
    Can the data be scarped using any other methods?

  26. #26
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: Automatically search website (using criteria from excel) and extract element informati

    I reckon you could get it with the method I posted above

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract Information from Website and place it in Excel
    By jjsiow in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2014, 02:50 PM
  2. Automatically pull information from a website? API?
    By hockeyadc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2013, 11:55 PM
  3. Replies: 2
    Last Post: 11-08-2013, 08:22 AM
  4. Replies: 0
    Last Post: 10-16-2013, 10:58 AM
  5. Replies: 2
    Last Post: 05-18-2013, 02:31 PM
  6. Extract information from a website
    By wieslaw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2013, 05:46 AM
  7. Can I create an excel macro to search & extract certain information from a pdf file?
    By razabear in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2011, 02:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1