Power Query - In-Running Stats

[paspuggie48]

This Power Query imports the data from the Tables from a website with some in-running stats on the day the query is refreshed.

Unfortunately it does not capture the "Pace / Rating"

InRunning.png

InRunningPQ.png

I have added two worksheets, which should be self-explanatory but basically...

1. To uploaded todays data; select the "TodaysData" worksheet, select any cell on the green Table, right-click your mouse and select "Refresh"

2. If you want to keep the days data, you can copy it over to the HistoricalData worksheet.
* On the TodaysData worksheet select any cell inside the green Table (ensuring it's below the Headers), press the keys CRTL+A (to select all cells) then
CRTL+C (to copy)
* Go to the "HistoricalData" worksheet
* Select any cell in the 'Date' column, press the keys "End" then "down arrow" (this will send you to the bottom of the Table)
* Move down one more cell (it appears as if the cell selected is outside the Table, which is correct)
* Press keys CRTL+V to paste
(if you have pasted the data correctly all rows will still be alternately shaded. If they aren't, then you will have copied it wrong)

In-Running Stats.xlsx

If you want to know the data source go to Data Tab > Get data > Data Source Settings

[paspuggie48]

Some people get an error when running queries.

This is usually down to their privacy settings.

To resolve this go to the Data Tab > Get Data > Query Options > Privacy

Ensure 'Always ignore Privacy level settings' is selected.

Capture.PNG

[paspuggie48]

If there is an appetite, I will look at developing a Query that gives one the ability to select any historic date and it gets that data.

If there is extreme hunger for all historic results dating back to 2014, then I'll look at providing it all in one file.

Time permitting !

[MemphisFlash]

With all your training i actually bagged an employee of the month and £50 voucher
as i implemented a Power Query into my workplace that saves the timesheet booking department hours
of data crunching and manual calculations.
they all think i'm a star at excel, lol.

[paspuggie48]

With all your training i actually bagged an employee of the month and £50 voucher
as i implemented a Power Query into my workplace that saves the timesheet booking department hours
of data crunching and manual calculations.
they all think i'm a star at excel, lol.

Brilliant Tom, well done mate

[MemphisFlash]

i was just walking by, coming back from the kitchen
and saw this woman struggling with something in excel.
i just walked over and said to her, if you tell me what your doing is what i think your doing,
i can show you an easier way to do it.

[wearthefoxhat]

Nice work there puggie!

[Dallas]

This Power Query imports the data from the Tables from a website with some in-running stats on the day the query is refreshed.

Unfortunately it does not capture the "Pace / Rating"


InRunning.png



InRunningPQ.png


I have added two worksheets, which should be self-explanatory but basically...

1. To uploaded todays data; select the "TodaysData" worksheet, select any cell on the green Table, right-click your mouse and select "Refresh"

2. If you want to keep the days data, you can copy it over to the HistoricalData worksheet.
* On the TodaysData worksheet select any cell inside the green Table (ensuring it's below the Headers), press the keys CRTL+A (to select all cells) then
CRTL+C (to copy)
* Go to the "HistoricalData" worksheet
* Select any cell in the 'Date' column, press the keys "End" then "down arrow" (this will send you to the bottom of the Table)
* Move down one more cell (it appears as if the cell selected is outside the Table, which is correct)
* Press keys CRTL+V to paste
(if you have pasted the data correctly all rows will still be alternately shaded. If they aren't, then you will have copied it wrong)


In-Running Stats.xlsx


If you want to know the data source go to Data Tab > Get data > Data Source Settings

FYI, I've just added these latest posts to the original index of them all
viewtopic.php?f=83&t=22492

[jtrader]

This Power Query imports the data from the Tables from a website with some in-running stats on the day the query is refreshed.

Unfortunately it does not capture the "Pace / Rating"


InRunning.png



InRunningPQ.png


I have added two worksheets, which should be self-explanatory but basically...

1. To uploaded todays data; select the "TodaysData" worksheet, select any cell on the green Table, right-click your mouse and select "Refresh"

2. If you want to keep the days data, you can copy it over to the HistoricalData worksheet.
* On the TodaysData worksheet select any cell inside the green Table (ensuring it's below the Headers), press the keys CRTL+A (to select all cells) then
CRTL+C (to copy)
* Go to the "HistoricalData" worksheet
* Select any cell in the 'Date' column, press the keys "End" then "down arrow" (this will send you to the bottom of the Table)
* Move down one more cell (it appears as if the cell selected is outside the Table, which is correct)
* Press keys CRTL+V to paste
(if you have pasted the data correctly all rows will still be alternately shaded. If they aren't, then you will have copied it wrong)


In-Running Stats.xlsx


If you want to know the data source go to Data Tab > Get data > Data Source Settings

Thank you very much,paspuggie48! It could be good if BF Prices are added as in your In-Running Ratings.xls

[paspuggie48]

Added by mistake

[paspuggie48]

Added by mistake

[paspuggie48]

Added by mistake

[paspuggie48]

Thank you very much,paspuggie48! It could be good if BF Prices are added as in your In-Running Ratings.xls

It would indeed Jtrader but I would need to create a plethora of queries and it may not scratch most people's itches. Not withstanding one would need to create specific BA columns, BAF files, share them, export data, instructions to go through the process etc etc

Regards results, there are various options but the one's I use are :-

a) create a rule in BetAngel to export the results and other details and merge that csv file & data to my existing list of horses/races using Power Query

b) use the BF Historical Results downloader developed by ODPaul82 (I updated a version currently on page 8) viewtopic.php?f=54&t=14395&start=105
*Note: it is a fair few years old since I developed it

I provided an example of how to get the data for all historic data into Excel on this thread "Power Query - BF Historic Horse Win Data in One Workbook" viewtopic.php?f=83&t=22451

Again, using Power Query you wouldn't need to load the data into Excel but just create a "connection only" and merge it to any existing data, for example against the horses in the In-Running stats list.

Then you have the added advantage of some Python guys on here who have some incredible skills, they may have some solutions

[paspuggie48]

I realise the 10% to 75% Columns are numbers and some would like to see percentages instead.

The way PQ handles %ages is a pain so I made a slight change so that it will show the data as percentages.

Here is that version

In-Running Stats with %ages.xlsx

[paspuggie48]

Not a PQ but all historic results dating back to July 2014, all 22,000+ records !

Had to separate into 2 files due to size restrictions

In-Running Historic Results 2014-18.xlsx

In-Running Historic Results 2019-23.xlsx