Park Run Data - Scotland

[GorD]

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/results/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

 

[sandy666]

maybe something like this

St Andrews parkrun #359

there is a DateTime of last refresh

 

[peter789]

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

 

[GorD]

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?

 

[GorD]

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

 

[sandy666]

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

 

[GorD]

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

 

[GorD]

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

 

[peter789]

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

 

[peter789]

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.