Power Query - Example Spreadsheets (Indexed by Sport)

Post Reply
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

You can navigate here to find stuff https://www.racingpost.com/profile/jock ... oked-rides
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

thanks for reply,

i wanted to automate the whole process.

so , run the " get data"
and
MACRO
to show me the runners for the HIGH TRAINERS for the day.

im thinking its a bit more complex than i first thought though !!!
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

stever78 wrote:
Sun Jun 27, 2021 1:48 pm
Hello Pugspar, rocknroller, and all.
thanks for your help yesterday

can i ask you , and anyone who knows, for your thoughts on how possible this is, and if so approx how would i go about doint it.

i have extracted the ATR SMART STATS into excel. ( i enclose pic of cartmels stats )

i can see that the trainer gary hanmer has a strike rate of 41%.

if i wanted to automate the process of taking ALL trainers with say over 30% strike rate AND then see the runners for those trainers.

Would i need to import a file of all runners + trainers for the day and then MATCH the two up.

and in terms of automating that, would i use a macro?.

do you think that would be the BEST way to go about it??

thanks for your help, much appreciated

steve
On the PQ thread you will see a document that gets the runners for the day. Then it's a case of merging both your document and the information from that document. Or you could dive into the PQ Editor to see how it is done etc.

To 'merge' tables/documents requires a bit of homework for you. I would go view YT channels like ExcelIsfun, Goodly, Access Analytic and many others...or just do a search how to do a PQ merge.

None of which will require any 'coding', unlike macros.

Peefa
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

stever78 wrote:
Sun Jun 27, 2021 2:33 pm
thanks for reply,

i wanted to automate the whole process.

so , run the " get data"
and
MACRO
to show me the runners for the HIGH TRAINERS for the day.

im thinking its a bit more complex than i first thought though !!!
It's not.

Imagine Workbook 1 is your Trainers data and Workbook 2 is your daily runners.

You merge the two (like vlookup) and using PQ to do that is much easier (with no formulas) and it is automated.
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 pm

thanks very much for the replies

i think i need to read up on PQ. just need to take a breather after loading and looking into numerous MACROS ( some of which may now be redundant !!!!)

should of spent the time on PQ i think !!

thanks all
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

Special thanks to Captain Jack Puggsie for all your help.

Finally cracked it.
02AA9ADE-C220-4D0C-B687-7235B1833FCC.jpeg
This is me this week.
3B2DE6D7-5CFF-45F8-89AD-F396DBFB79F9.jpeg
You do not have the required permissions to view the files attached to this post.
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Realrocknrolla wrote:
Mon Jun 28, 2021 11:08 am
Special thanks to Captain Jack Puggsie for all your help.

Finally cracked it.
"Looking Good Billy-Ray, Feeling good Lewis" ;)

(Trading Places)
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 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
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 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
You do not have the required permissions to view the files attached to this post.
stever78
Posts: 93
Joined: Wed May 24, 2017 7:43 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
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: 2128
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! ;)
Post Reply

Return to “Excel Power Query”