Power Query - Example Spreadsheets (Indexed by Sport)

Post Reply
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

stever78 wrote:
Mon Jun 28, 2021 5:08 pm
Hello RealRock

i looked at that document and i see that it was from the sky sports pointers website.
when i clicked the race ( on the website ) i took me to the racepage, and the trainer (on the same line) had no runners in that race.
so i sort of discounted the site altogether.

however, on your document there, the "pointers"
does it show - "ALL" trainers that have runners today

in other words i don't suppose there are any trainers missing from the page, that would have runners today.

final question would be, as usual with stats pages, no key as to what they are providing.

do you think its last 14 days??

i'm sure i could check though

cheers, steve
Because you have to refresh all the tables on that document
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

merging was correct yest, now i have finished the document.

but when i run it tomorrow ill need to change the source name.

question is - do i do that manually for each course smart stats page.

i assume that is the only thing i can do - change the source name - to reflect where i am getting the data from !!

Realrocknrolla wrote:
Mon Jun 28, 2021 4:52 pm
stever78 wrote:
Mon Jun 28, 2021 4:27 pm
Hi All,
would very much appreciate your help with something
i am making some progress with the ATR SMART STATS sheet (mentioned yesterday)

i know i am going to be having a problem later on, and im sure its something you "Jedi's" ( or is it just jedi for plural ??, i digress ). its an issue i think you would have encountered before.

the source page for my PQ is
"https://www.attheraces.com/racecards/Li ... SmartStats"

now when i run the PQ in future , obviously the source page name will change to a different course and different date.

the format of the page , however, should be consistent.

my question is - What is the best way i can change the source data name to reflect the correct ( current day's ) source.
or am i left to change the source manually each time i run it

thanks very much for your help on this
cheers, steve
Pretty sure, the answer was merge into your other doc...

i merge this into another document

just another version of what you have
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Personally without seeing your document it's very difficult to understand what you are trying to do. So, as I am a bit blind to what you are attempting but it seems your ATR URL has today's date and a course, yes?

i.e. "/Lingfield/28-June-2021/SmartStats"

So, you are saying that tomorrow if you refresh your existing data it wont work as the URL has both todays date and a course embedded?

If so, then you are getting into more dynamic variables and / or creating a function to account for it and that requires a bit more advanced PQ expertise.

The simplest way would be to duplicate your URL column in the PQ Editor and split it and then insert todays date as a new column, which is simple to find on Google how to do it (+ ensuring it is formatted the same as the URL requirement)...then knit it all back together to develop your new URL, which you would then get PQ to connect to to get your data.

Even doing that only sorts your date and not the course. To get the courses for the day of the URL each day would require getting a dynamic list of races/courses for that day from the ATR website (or any other website to be fair)..which requires another query and not easy if one is a basic PQ user (no offence). I think there is a workbook for Greyhounds that gets something similar but it would take me hours to explain how to do it.

Anything more than that leaves you with a few options :
a) learn more PQ and practice, practice, practice
b) google whatever it is you want to achieve (I've been doing that for years with PQ and VBA)
c) find another site that is not so dynamic
d) combine some of the workbooks provided on this Forum as they may give you what you want (but that is linked to bullet point "a")
or
e) as RRR showed, he too gets Jockey/Trainer stats (as the URL is not dynamically changing each day) and all he needs to do is 'merge' it with whatever else he needs (like runners of the day based on a trainers name etc).

The tools provided in this Forum are for basic use and do not need any programming or require the user to do any dynamic changes, which makes it easier to use, so they don't need to do anything other than press refresh.

Any more and you are getting into the realms of consultancy...and I am available for £500 an hour :lol:
User avatar
MemphisFlash
Posts: 2126
Joined: Fri May 16, 2014 10:12 pm
Location: Leicester

I'm available at half that price, lol :D :o
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

£200 ph here! ;)
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Share the eloratings solution and I'll do it for free :D :) :lol:
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

paspuggie48 wrote:
Mon Jun 28, 2021 8:21 pm
Personally without seeing your document it's very difficult to understand what you are trying to do. So, as I am a bit blind to what you are attempting but it seems your ATR URL has today's date and a course, yes?

i.e. "/Lingfield/28-June-2021/SmartStats"

So, you are saying that tomorrow if you refresh your existing data it wont work as the URL has both todays date and a course embedded?

If so, then you are getting into more dynamic variables and / or creating a function to account for it and that requires a bit more advanced PQ expertise.

The simplest way would be to duplicate your URL column in the PQ Editor and split it and then insert todays date as a new column, which is simple to find on Google how to do it (+ ensuring it is formatted the same as the URL requirement)...then knit it all back together to develop your new URL, which you would then get PQ to connect to to get your data.

Even doing that only sorts your date and not the course. To get the courses for the day of the URL each day would require getting a dynamic list of races/courses for that day from the ATR website (or any other website to be fair)..which requires another query and not easy if one is a basic PQ user (no offence). I think there is a workbook for Greyhounds that gets something similar but it would take me hours to explain how to do it.

Anything more than that leaves you with a few options :
a) learn more PQ and practice, practice, practice
b) google whatever it is you want to achieve (I've been doing that for years with PQ and VBA)
c) find another site that is not so dynamic
d) combine some of the workbooks provided on this Forum as they may give you what you want (but that is linked to bullet point "a")
or
e) as RRR showed, he too gets Jockey/Trainer stats (as the URL is not dynamically changing each day) and all he needs to do is 'merge' it with whatever else he needs (like runners of the day based on a trainers name etc).

The tools provided in this Forum are for basic use and do not need any programming or require the user to do any dynamic changes, which makes it easier to use, so they don't need to do anything other than press refresh.

Any more and you are getting into the realms of consultancy...and I am available for £500 an hour :lol:
thanks for the detailed reply, paspag

yes that is what i were asking, basically the best way to change URL when they themselves change and are not the same each time.

you have explained how i could do this for the date and then courses could be more complicated, however you are spot on in that , practise, practise and learning is obviously the way forward on this issue.
ive just taught myself how to edit macro's and other VBA so my head is a bit punched out !! haha.

im new to PQ, and it didnt think getting data from sites would work ((as past experience of scalping and data retrieval goes), but it does work and now i can see the potential, as well as others i see !!

but its help from you and other members that really help ideas get off the ground , so many, many thanks.
cheers, steve

for now, i need to upgrade. that fuzzy matching thing and splitting cells by uppercase is not available in my 2013 !!!
so it look like 365 then.
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

stever78 wrote:
Wed Jun 30, 2021 11:49 am
paspuggie48 wrote:
Mon Jun 28, 2021 8:21 pm
Personally without seeing your document it's very difficult to understand what you are trying to do. So, as I am a bit blind to what you are attempting but it seems your ATR URL has today's date and a course, yes?

i.e. "/Lingfield/28-June-2021/SmartStats"

So, you are saying that tomorrow if you refresh your existing data it wont work as the URL has both todays date and a course embedded?

If so, then you are getting into more dynamic variables and / or creating a function to account for it and that requires a bit more advanced PQ expertise.

The simplest way would be to duplicate your URL column in the PQ Editor and split it and then insert todays date as a new column, which is simple to find on Google how to do it (+ ensuring it is formatted the same as the URL requirement)...then knit it all back together to develop your new URL, which you would then get PQ to connect to to get your data.

Even doing that only sorts your date and not the course. To get the courses for the day of the URL each day would require getting a dynamic list of races/courses for that day from the ATR website (or any other website to be fair)..which requires another query and not easy if one is a basic PQ user (no offence). I think there is a workbook for Greyhounds that gets something similar but it would take me hours to explain how to do it.

Anything more than that leaves you with a few options :
a) learn more PQ and practice, practice, practice
b) google whatever it is you want to achieve (I've been doing that for years with PQ and VBA)
c) find another site that is not so dynamic
d) combine some of the workbooks provided on this Forum as they may give you what you want (but that is linked to bullet point "a")
or
e) as RRR showed, he too gets Jockey/Trainer stats (as the URL is not dynamically changing each day) and all he needs to do is 'merge' it with whatever else he needs (like runners of the day based on a trainers name etc).

The tools provided in this Forum are for basic use and do not need any programming or require the user to do any dynamic changes, which makes it easier to use, so they don't need to do anything other than press refresh.

Any more and you are getting into the realms of consultancy...and I am available for £500 an hour :lol:
thanks for the detailed reply, paspag

yes that is what i were asking, basically the best way to change URL when they themselves change and are not the same each time.

you have explained how i could do this for the date and then courses could be more complicated, however you are spot on in that , practise, practise and learning is obviously the way forward on this issue.
ive just taught myself how to edit macro's and other VBA so my head is a bit punched out !! haha.

im new to PQ, and it didnt think getting data from sites would work ((as past experience of scalping and data retrieval goes), but it does work and now i can see the potential, as well as others i see !!

but its help from you and other members that really help ideas get off the ground , so many, many thanks.
cheers, steve

for now, i need to upgrade. that fuzzy matching thing and splitting cells by uppercase is not available in my 2013 !!!
so it look like 365 then.
https://brytesoft.com/microsoft-office- ... -plus.html
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

hello all, i would really appreciate your thoughts on this.

if i run a PQ that gets some stats for trainers and then i run (merge) that against a list of trainers running that day.
(im getting list of trainers from GG.co.uk)

and im doing this so when i get a stat that is interesting it can show me the runners attributed to the stat.
e.g
Rae Guest 75 strike rate at course , so what is Rae Guest's runners today?

how do you deal with PARTIAL matches of the trainer.
for example R GUEST on the stats table and RAE GUEST on the GG site table, does not find a match.

would you use the FUZZY MATCHING ??

or would you split the columns to initials only ( i assume that's possible )

just interested to know what type of techniques you use, in order to match stats with trainers runners today..

As said, really appreciate your thoughts and help, just a pointer as to what you would do? obv not expecting too much of you time in response but appreciate any help u can give
thanks, steve
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

I think you need to search Google or YouTube first Steve (or MrExcel, Ozgrid) as this is not a Power Query help forum.

However, while I'm here... like most Microsoft products there are many many ways of doing it. The first being 'replace text' i.e. replace R GUEST with RICHARD GUEST and so on, for whatever trainer needs it.

After a while you may think this is inefficient (and it is), so after a search you may find that creating a unique table of records of your own will suffice. So in Col A you have R GUEST and in Col B you have RICHARD GUEST. When you merge this unique table to your master records you can pull back whichever column you need. Then merge those pulled back columns to your other data, hopefully.

...as for Fuzzy Matching, I've not used it in 2 years...it's more for partial finding or text contains type stuff.
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

thanks pas,
your suggestion of changing names has led me to just replacing them on PQ.
this fuzzy matching could provide too many undesired results
thanks for your help
mgrant
Posts: 9
Joined: Mon Jun 07, 2021 11:48 am

Hi folks
Dogs related,could power query be used to download the daily cards for the greyhounds???
Thanks
Granty
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

mgrant wrote:
Wed Jul 07, 2021 11:23 am
Hi folks
Dogs related,could power query be used to download the daily cards for the greyhounds???
Thanks
Granty
Yea
mgrant
Posts: 9
Joined: Mon Jun 07, 2021 11:48 am

Good I will have a go then. :)
Office 2010(my usual office) doesn't seem to have the capabilities so I am using 2019
Ive done a bit with pivot tables,and a lot with VBA and excel.I am not too convinced yet of PQ's ability to scrape effectively but then I dont know too much about it.I have done a lot with Python and a little web scraping,but at the moment teaching myself HTML,Java Script etc.
But by what I have seen so far it looks like PQ could be just what I want.
My first project will to be downloading the dog cards,and I will put my feeble attempts up here :) :) :)
Granty.
User avatar
MemphisFlash
Posts: 2126
Joined: Fri May 16, 2014 10:12 pm
Location: Leicester

will compare with what i hav already done once you have posted. :D
Post Reply

Return to “Excel Power Query”