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.
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.
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 !!
Power Query - BF Historic Horse Win Data in One Workbook
- paspuggie48
- Posts: 679
- 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.
- paspuggie48
- Posts: 679
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
Would help if I attached the document
You do not have the required permissions to view the files attached to this post.
- paspuggie48
- Posts: 679
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
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.
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.
You do not have the required permissions to view the files attached to this post.
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
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
- Posts: 679
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
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
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
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...
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...
You do not have the required permissions to view the files attached to this post.
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.
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
- Posts: 679
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
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.cologg12 wrote: ↑Thu Dec 17, 2020 9:14 amThank 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...
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
- Posts: 679
- Joined: Thu Jun 20, 2013 9:22 am
- Location: South-West
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.gstar1975 wrote: ↑Fri Jan 05, 2024 1:52 pmIf 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.
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.