Power Query - BF Historic Horse Win Data in One Workbook

[paspuggie48]

Probably one for newbies but if anyone stores the historic BF price csv documents, then this may help you to put all the data from every Country into one workbook.

This example is for Horse Win only markets.

Screenshot 2020-11-24 110036.png

Should you download the UK, IRE, USA, AUS & RSA files, then one needs to create the following folders on their C:\: -

Horse - AUS Win
Horse - IRE Win
Horse - ARSA Win
Horse - UK Win
Horse - USA Win

Download the csv's from https://promo.betfair.com/betfairsp/prices and put them into the respective folders.

As an example, I imported the data from each country for 21/11/2020.

Screenshot 2020-11-24 110123.png

I then added another csv file for another date and pressed Data > Refresh All. One can now see it has updated the table automatically.

Takes seconds to update each day !!

Screenshot 2020-11-24 110501.png

[paspuggie48]

Would help if I attached the document

[paspuggie48]

If creating all those folders is too much, then use this file and create just 1 folder on your C:\ and name it: -

Betfair Historic Results (Horses)

Dump all csv's into this one folder and the attached Power Query document should find them all and filter accordingly.

It will do the same as my previous post but has the benefit that all files are in one place.

[PeterLe]

Nice work!
To make it even easier, check out this post:

viewtopic.php?f=55&t=21045&hilit=python

Just copy the code into an editor and run it, voila, SP data copied and installed into your directories
Just do a few months at a time though

[paspuggie48]

Thanks Peter, I've been meaning to get into Python since I was told about it 3 years ago lol.

I have been known to develop some fantastic time-saving macros in my time at work but my mate back then said "forget VBA, Python is the way to go"...and look where we are now with Data Scientists all using Python for machine learning/AI etc, especially that AI programme developed to win those 'Go' games.

For my personal use, I can see a benefit for downloading the files automatically. I used NigelK's original downloader but it broke and I cannot fix it. It could download a full years worth. As I don't know Python, the only problem I'd have is the data manipulation thereafter. I'd have to know python coding to perform additional analytics I would need...plus there is no cost to me as PQ is free with Excel

[cologg12]

Thank you very much for this. I've been doing this manually for a while! (feel stupid saying that now I see this)

When I import the data I have these formatting issues each time (see below)
I've tried correcting it each time which works, but as soon as I try the next day's upload, the formatting goes again
Any ideas/help/advice would be much appreciated...

[gstar1975]

If I wanted to Power Query to find all of the horses I have ever traded, how would I go about doing that?

I have all the MatchedBetsReports but they are in sub folders with other log files data which I don't need.

Is it possible to extract just the MatchedBetsReports data without having to move the folders into a separate folder which would take forever.

[paspuggie48]

Thank you very much for this. I've been doing this manually for a while! (feel stupid saying that now I see this)

When I import the data I have these formatting issues each time (see below)
I've tried correcting it each time which works, but as soon as I try the next day's upload, the formatting goes again
Any ideas/help/advice would be much appreciated...

A buddie of mine had a similar issue even though I couldn't replicate it and yes I found it weird as it was perfect every time I used it but he got the same errors.

Format the Column to Date or whatever format is required for that column. Refresh the query and if it does not appear correct (as per your screenshot) then Go to the Data Tab > Properties. You may need select/deselect "Preserve cell formatting".

[paspuggie48]

If I wanted to Power Query to find all of the horses I have ever traded, how would I go about doing that?

I have all the MatchedBetsReports but they are in sub folders with other log files data which I don't need.

Is it possible to extract just the MatchedBetsReports data without having to move the folders into a separate folder which would take forever.

PQ can get any data from lots of sources; files & folders are perfect for this type of technology. It is a case of Data Tab > Get data > From file > From folder.

Just look up videos on YouTube e.g. "How to get data from multiple files" (or Folders) and there are a plethora of videos that will take you through the easy steps.