I love the BF Historic Downloader tool and use it every singe day.
viewtopic.php?f=54&t=14395&start=120
However I was intrigued, can I do something similar in Power Query and the answer is YES !
There is a way of getting the data from CSV files that are embedded on a website without having to download the file.
So, I developed a Query that gets the data from all the files of that particular day.
To use; type in a date in the relevant cell, select the green Table then right-click your mouse and select "Refresh"
I tested it for yesterdays data (6,600 records) and it loaded it in 2 minutes.
There are a few caveats but basically I have only set it to collect one days data only. In time I may add "range of dates" and make it more dynamic in case the BF Header Columns are inconsistent. I have also not split any data between Horses or Greyhounds. It is as per the structure of the CSV files but if I get a bit more time I'll look at adding a bit more transformation.
Power Query - BF Historic Data Downloader
- paspuggie48
- Posts: 697
- 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.
Super nice work!
If it's of any assistance for when you go to do further processing ,this is the logic I use in a seperate tool for splitting out the course from menu hint (GB only but easily tweaked) and then distance & race type from the column where it's all combined together. I also do an exclusion for "TBP" in text as some of the historic files bring this out in the win market by mistake.
If it's of any assistance for when you go to do further processing ,this is the logic I use in a seperate tool for splitting out the course from menu hint (GB only but easily tweaked) and then distance & race type from the column where it's all combined together. I also do an exclusion for "TBP" in text as some of the historic files bring this out in the win market by mistake.
Code: Select all
With pudtmkts.Market(pudtmkts.Count)
.EventID = varVals(0)
.MenuHint = varVals(1)
.EventName = varVals(2)
.EventDate = varVals(3)
' Obtain the course
strTemp = Replace(varVals(1), "GB / ", EMPTY_STRING)
For c = 1 To Len(strTemp)
If Not IsNumeric(Mid(strTemp, c, 1)) Then
strCourse = strCourse & Mid(strTemp, c, 1)
Else
Exit For
End If
Next c
.Course = Trim(strCourse)
' Distance & race type
.Distance = Left(varVals(2), InStr(varVals(2), " ") - 1)
.RaceType = Mid(varVals(2), InStr(varVals(2), " ") + 1)
.Runners = 1
End With
- paspuggie48
- Posts: 697
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Not a scooby what you wrote there Paul but looks lovely LOL.ODPaul82 wrote: ↑Tue Nov 14, 2023 5:01 pmSuper nice work!
If it's of any assistance for when you go to do further processing ,this is the logic I use in a seperate tool for splitting out the course from menu hint (GB only but easily tweaked) and then distance & race type from the column where it's all combined together. I also do an exclusion for "TBP" in text as some of the historic files bring this out in the win market by mistake.
Code: Select all
With pudtmkts.Market(pudtmkts.Count) .EventID = varVals(0) .MenuHint = varVals(1) .EventName = varVals(2) .EventDate = varVals(3) ' Obtain the course strTemp = Replace(varVals(1), "GB / ", EMPTY_STRING) For c = 1 To Len(strTemp) If Not IsNumeric(Mid(strTemp, c, 1)) Then strCourse = strCourse & Mid(strTemp, c, 1) Else Exit For End If Next c .Course = Trim(strCourse) ' Distance & race type .Distance = Left(varVals(2), InStr(varVals(2), " ") - 1) .RaceType = Mid(varVals(2), InStr(varVals(2), " ") + 1) .Runners = 1 End With
All my existing PQ stuff (personal offline) incorporates the same filters you've adapted there and more importantly the dreaded TBP rows.
It's much easier when downloading the Guardian cards and results and combining certain fields to create the "unique" common denomination.
Suffice to say I was shocked to see how PQ could get this data without having to download a file.
After 6 years of PQ, I never knew it could be done
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
I will adapt this query in time for sure
![Cool 8-)](./images/smilies/icon_cool.gif)
- paspuggie48
- Posts: 697
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
- firlandsfarm
- Posts: 2894
- Joined: Sat May 03, 2014 8:20 am
Absolutely brilliant pp, just what I need to save oodles of time, I can now just copy and paste into the database. Thanks a million.
- paspuggie48
- Posts: 697
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
firlandsfarm wrote: ↑Wed Nov 15, 2023 9:11 amAbsolutely brilliant pp, just what I need to save oodles of time, I can now just copy and paste into the database. Thanks a million.
You do not have the required permissions to view the files attached to this post.
Looks great from your image. Ive downloaded it but the 'Match Results' page is blank. Do I need to fire the VBa manually?
EDIT: Don't worry. I think its just because I was using 64 Bit Excel. In 32 bit it works awesome.
Is there an explanation of the columns, please? Unless I missed that.
EDIT: Don't worry. I think its just because I was using 64 Bit Excel. In 32 bit it works awesome.
Is there an explanation of the columns, please? Unless I missed that.
- paspuggie48
- Posts: 697
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
No VBA in it bud, it uses Power Query to get the data. As for the explanation of columns, they are exactly extracted, as is, from the BF website https://promo.betfair.com/betfairsp/pricesGaryCook wrote: ↑Wed Mar 27, 2024 5:45 amLooks great from your image. Ive downloaded it but the 'Match Results' page is blank. Do I need to fire the VBa manually?
EDIT: Don't worry. I think its just because I was using 64 Bit Excel. In 32 bit it works awesome.
Is there an explanation of the columns, please? Unless I missed that.
Sorry wrong post. Actually was for another one of yours but it's resolved now. Thanks.paspuggie48 wrote: ↑Wed Mar 27, 2024 8:01 amNo VBA in it bud, it uses Power Query to get the data. As for the explanation of columns, they are exactly extracted, as is, from the BF website https://promo.betfair.com/betfairsp/pricesGaryCook wrote: ↑Wed Mar 27, 2024 5:45 amLooks great from your image. Ive downloaded it but the 'Match Results' page is blank. Do I need to fire the VBa manually?
EDIT: Don't worry. I think its just because I was using 64 Bit Excel. In 32 bit it works awesome.
Is there an explanation of the columns, please? Unless I missed that.