Power Query - BF Historic Data Downloader

[paspuggie48]

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.

[ODPaul82]

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

[LeTiss]

Crikey you've put some work into that. Excellent stuff

[paspuggie48]

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
I will adapt this query in time for sure

[paspuggie48]

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

[firlandsfarm]

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]

Yes, nicely done & thanks for sharing )

[Dallas]

Well done on sharing this one

[paspuggie48]

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.

Yes, nicely done & thanks for sharing )

Well done on sharing this one

thumb emoji.png

[GaryCook]

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.

[paspuggie48]

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

[GaryCook]

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.