Power Query - BF Historic Data Downloader

Post Reply
User avatar
paspuggie48
Posts: 702
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

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.

BF Downloader.png

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"

BF Data Downloader.xlsx

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.
You do not have the required permissions to view the files attached to this post.
User avatar
ODPaul82
Posts: 731
Joined: Sun May 08, 2011 6:32 am
Location: Knebworth, Herts

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.

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
User avatar
LeTiss
Posts: 5451
Joined: Fri May 08, 2009 6:04 pm

Crikey you've put some work into that. Excellent stuff
User avatar
paspuggie48
Posts: 702
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

ODPaul82 wrote:
Tue Nov 14, 2023 5:01 pm
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.

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
Not a scooby what you wrote there Paul but looks lovely LOL.
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 :D
I will adapt this query in time for sure 8-)
User avatar
paspuggie48
Posts: 702
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

LeTiss wrote:
Tue Nov 14, 2023 5:06 pm
Crikey you've put some work into that. Excellent stuff
Didn't take long Tiss...the wonders of PQ, I just didn't realise it could be done until now :D
User avatar
firlandsfarm
Posts: 2941
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.
sionascaig
Posts: 1286
Joined: Fri Nov 20, 2015 9:38 am

Yes, nicely done & thanks for sharing )
User avatar
Dallas
Posts: 23065
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

Well done on sharing this one

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

firlandsfarm wrote:
Wed Nov 15, 2023 9:11 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.
sionascaig wrote:
Wed Nov 15, 2023 9:15 am
Yes, nicely done & thanks for sharing )
Dallas wrote:
Wed Nov 15, 2023 12:45 pm
Well done on sharing this one
Image
thumb emoji.png
You do not have the required permissions to view the files attached to this post.
User avatar
GaryCook
Posts: 130
Joined: Sat Jan 27, 2018 6:35 pm

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.
User avatar
paspuggie48
Posts: 702
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

GaryCook wrote:
Wed Mar 27, 2024 5:45 am
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.
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/prices
User avatar
GaryCook
Posts: 130
Joined: Sat Jan 27, 2018 6:35 pm

paspuggie48 wrote:
Wed Mar 27, 2024 8:01 am
GaryCook wrote:
Wed Mar 27, 2024 5:45 am
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.
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/prices
Sorry wrong post. Actually was for another one of yours but it's resolved now. Thanks.
Post Reply

Return to “Excel Power Query”