BA Admin and Fellow Members
As you will have seen Memphis has been posting a spreadsheet solution using Power Query (PQ).
I'd like to say, with a smile, that I was the one that mentored him and introduced him to that way of working and he has gladly taken it in his stride and started using it in anger.
Trust me when I say I use this PQ daily at work, manipulating millions of rows of data and calcs, all without VBA coding. It truly revolutionises the way one works in Excel.
So, my question is directed more-so to the Admin staff, can we have a 'sticky' for not just Excel/Spreadsheet solutions but Power Query only????
Let me give some examples: -
Have you ever wondered what Trap wins the most for each Greyhound course and can be updated at the click of one button?
From the GreyhoundStats webpages you can use PQ to collate that information for the past 9 years from multiple URLs and tabulate it all into 1 Table. To refresh this Table every morning takes about 10 seconds.
Have you ever wondered what Fav wins in each Trap for each Greyhound course? From the GreyhoundStats webpages you can use PQ to collate that information for the past 9 years from multiple URLs and tabulate it all into 1 Table. To refresh this every morning takes about 10 seconds.
Did you know you can collate the BF Historic records, count the amount of races each horse has ran and find those that have raced 9 times or more this year AND if they are running today? To do this I used NigelK's fantastic download tool to get the BF History csv files (I converted it so I can do this daily). Then I used PQ to connect to all the 300+ CSV files and created a very simple Query & Function to connect to Timeform's list of runner's today and combined both Query's to create the following table. It takes around 30 seconds to get this information dynamically, each and ever day.
The great thing with PQ is there is no coding and one can do any calcs/formulas thereafter to add to one's Tables.
The Power of Power Query
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Ever wanted Jockey Stats each day, at the click of one button?
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Steamers & Drifters from ATR.
You can set the Refresh rate for every minute or hour or whatever.
You can set the Refresh rate for every minute or hour or whatever.
You do not have the required permissions to view the files attached to this post.
- MemphisFlash
- Posts: 2162
- Joined: Fri May 16, 2014 10:12 pm
- Location: Leicester
+1
nice post.
could i have a copy of your modifed nigel spreadsheet please.
nice post.
could i have a copy of your modifed nigel spreadsheet please.
Last edited by MemphisFlash on Thu Oct 29, 2020 12:23 pm, edited 1 time in total.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
How one can use PQ to connect to multiple daily P&L csv files from BF and combine them into weekly figures and create a running profit.
One click again to refresh each day, simply dump your csv in a folder and let PQ do the rest. Takes seconds to update.
One click again to refresh each day, simply dump your csv in a folder and let PQ do the rest. Takes seconds to update.
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Sent to you student MemphisMemphisFlash wrote: ↑Thu Oct 29, 2020 12:10 pmnice post.
could i have a copy of your modifed nigel spreadsheet please.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
For you DOBBERS out there.
Which horse has ran more than twice this year and it's In-Play minimum has averaged more than 50% for all it's race and is running today. Here is just one example >>>
Which horse has ran more than twice this year and it's In-Play minimum has averaged more than 50% for all it's race and is running today. Here is just one example >>>
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Combine Football stats.
Simply dump the files into 1 folder and get PQ to combine them all into 1 table. Here I have combined (Appended) each and every year for each and every league.
Took me ten minutes to do.
Simply dump the files into 1 folder and get PQ to combine them all into 1 table. Here I have combined (Appended) each and every year for each and every league.
Took me ten minutes to do.
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
To be honest, it may be easier if you download NigelK's original file as I have doctored my copy to suit me
viewtopic.php?f=54&t=14395
I'll work on my version and get some instructions drafted as it differs quite a lot
- paspuggie48
- Posts: 636
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West