Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Park Run Data - Scotland
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Park Run Data - Scotland

    I have created a query to pull all the park run data from all the scottish park runs which works ok

    it uses 43ish urls in a table (a few tables as website didn't like all together) like below

    https://www.parkrun.org.uk/standrews...latestresults/


    I get all the data coming through fine , but the table of data doesn't contain a date - which is visible on the webpage above the data table.

    My concern is that if a particular park run didn't happen one week i would not know and pull the data through as this weeks

    So how can i load the data from the above url and include the date

    Thanks for any help

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,807
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Park Run Data - Scotland

    maybe something like this

    St Andrews parkrun #359

    there is a DateTime of last refresh
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    Unfortunately I get 403 forbidden on the website. However I had a look earlier and would think about the following approach. First create a table of the events you are interested in. Create a query from this and build a link to the event history for each park in a custom column. Then create a function to create a list of every event in each park with the run number and date. Add this in another column. Finally another function to extract the results. If the website comes back up I will try it later.

    Peter

  4. #4
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    Sandy, is that not just the time that you ran your query, rather than the date of the park run that is classed as latest?

  5. #5
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    Quote Originally Posted by peter789 View Post
    Unfortunately I get 403 forbidden on the website. However I had a look earlier and would think about the following approach. First create a table of the events you are interested in. Create a query from this and build a link to the event history for each park in a custom column. Then create a function to create a list of every event in each park with the run number and date. Add this in another column. Finally another function to extract the results. If the website comes back up I will try it later.

    Peter
    Peter that sounds like exactly what i have done, except i'm using "latest" rather than a specific event no. I don't want to have to go through all urls to see what number they are sitting at - especially when the data im after is already on the page

    Just wondering if it is accessable

    I quite often get the error you get and change browser and i get access again - think it must be something to stop DOS attacks

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,807
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Park Run Data - Scotland

    this is last refresh of the data not a date from the site

    text: St Andrews parkrun # 359 - 15/06/2019 is outside of the table
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    Peter, I see what you mean, and have had a go - issue I now see is getting the name of the location.

    Also wondering if the web page would block me with running so many queries, as I was getting some mash-up errors when I tried to do all 40 odd urls at the same time

    I know think i would do this function once to get the latest date of each event, and my existing function to get all the data - but I can't get the location name, in the same way, I couldn't get the date initially

  8. #8
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    Quote Originally Posted by sandy666 View Post
    this is last refresh of the data not a date from the site

    text: St Andrews parkrun # 359 - 15/06/2019 is outside of the table
    Correct - that is the essence of the problem

  9. #9
    Board Regular
    Join Date
    Nov 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    I'll try again later and see if I can work out a way of limiting my calls to their server.
    I didn't realise you just wanted the latest. That makes it a bit easier. I will still try calling up the event listings but just return the highest sequential number of run for each one and look up the actual event date from there. Then I will build a unique URL for each one in a new column and create a function to get the data.
    Peter

  10. #10
    Board Regular
    Join Date
    Nov 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    I'm blocked out again. Looks like I have to wait a few hours, I've tried re-booting without success. I have tried finding the web site's Terms of service, it obviously doesn't like what we are attempting to do and I am slightly concerned about the ethics of continuing.
    However when I get back in I will try Function.Invokeafter with a time delay.
    Back to your question on how to get the location name. I used a quick and dirty approach. Searched for event locations on the website filtered by Scotland; copy and paste into Excel, into a query, lower case, removed spaces.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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