Hi All - is there such a thing as a tool to convert all commands from a Guardian set of rules to Excel format?
Ultimate aim is to run my routines against historic Betfair feeds available from one of the replay sites. The idea of running multiple races at 32x normal speed is very attractive.
I have done a bit of programming in my time, but nothing in Excel, beyond formulae. Only VB was years ago, scraping some eBay data to save my partner from typing out hundreds of addresses. Nevertheless I feel that I'd learn a lot, fairly quickly from a sample Excel spreadsheet giving me some idea of syntax.
Is there anything hidden on this forum that would be a good starting point, or is Betfair the place to start?
What workflow does BA use to translate Guardian rules for the Betfair API? is this translation stored upon our workstations somewhere? Any info would be much appreciated.
Convert Guardian bot to Excel?
-
ShaunWhite
- Posts: 9870
- Joined: Sat Sep 03, 2016 3:42 am
You'll have to start from scratch but that's often best anyway moving to such a different platform.
You can go one better than your aim by connecting directly to the Betfair API stream using VBA (or Python or C#) https://betfair-developer-docs.atlassia ... ample+Code. That way you can save the entire api price stream and replay it or analyse it as you see fit. It's too large to save in Excel (perhaps 1gb some days just for UK horses) so SQL is a good repository. You could save yourself a lot of time and effort though by looking at an open source Python project called Betcode that enables you to record data, run simulations and execute live trading. I use C# & SQL but that's personal preference. 32x is modest, I simulate a month's racing in about 15mins.
Don't worry about being a bit rusty, that's where ChatGPT comes in handy, never been a better time to get into it. All the best.
....To whet your appetite further, with good data you could look at ML?
You can go one better than your aim by connecting directly to the Betfair API stream using VBA (or Python or C#) https://betfair-developer-docs.atlassia ... ample+Code. That way you can save the entire api price stream and replay it or analyse it as you see fit. It's too large to save in Excel (perhaps 1gb some days just for UK horses) so SQL is a good repository. You could save yourself a lot of time and effort though by looking at an open source Python project called Betcode that enables you to record data, run simulations and execute live trading. I use C# & SQL but that's personal preference. 32x is modest, I simulate a month's racing in about 15mins.
Don't worry about being a bit rusty, that's where ChatGPT comes in handy, never been a better time to get into it. All the best.
....To whet your appetite further, with good data you could look at ML?
-
ShaunWhite
- Posts: 9870
- Joined: Sat Sep 03, 2016 3:42 am
If you're organised and run batch jobs 247 then the results keep you busy. But tbh it's not about random brute force, and you're not guarenteed to find an edge. Step 1 is data, then you'll have enough to be useful by the time you need it. Betfair sell it but at £200/sport/mo it's expensive, but shows it's worth.
It's a big commitment, but if a job's worth doing...
Just to clarify - is there any means of capturing BF streamed data directly into MySQL?
When I read about those of you like @ShaunWhite back-testing on the scale that you do, does this assume access to Betfair's historic data, or has anybody generated their own historic data bank?
I have a Linux server co-located on an internet backbone which would be ideal for harvesting.
One other query - re: practical size of dataset for back-testing - Obviously several months' worth would be fantastic, but wouldn't even a single month of races include pretty much every situation I'd be likely to encounter, in terms of freak results/occurrences? Things like I saw in AUS a couple of days ago, where despite only being three seconds from the end of the race, my bot (practice mode) laid a red-hot pre-race favourite which was a length clear and pulling away. This despite my rules including a convoluted spike-detection routine taking 5 comparative snapshots over the previous 6 seconds to check that the odds had been steadily increasing, as opposed to it being a flash spike.
Obviously everybody has losses, but do you automated-betting old hands ever really see losses over a whole month? I'm not sure that I'll have the mindset to persevere if I experience that?
When I read about those of you like @ShaunWhite back-testing on the scale that you do, does this assume access to Betfair's historic data, or has anybody generated their own historic data bank?
I have a Linux server co-located on an internet backbone which would be ideal for harvesting.
One other query - re: practical size of dataset for back-testing - Obviously several months' worth would be fantastic, but wouldn't even a single month of races include pretty much every situation I'd be likely to encounter, in terms of freak results/occurrences? Things like I saw in AUS a couple of days ago, where despite only being three seconds from the end of the race, my bot (practice mode) laid a red-hot pre-race favourite which was a length clear and pulling away. This despite my rules including a convoluted spike-detection routine taking 5 comparative snapshots over the previous 6 seconds to check that the odds had been steadily increasing, as opposed to it being a flash spike.
Obviously everybody has losses, but do you automated-betting old hands ever really see losses over a whole month? I'm not sure that I'll have the mindset to persevere if I experience that?
If you are heading down the coding route, then the Bet Angel API is a useful bridge to the Betfair API and you don't have to pay any additional fees. Ultimately that's why we created it: -
https://www.betangel.com/api-guide/
https://www.betangel.com/api-guide/
-
ShaunWhite
- Posts: 9870
- Joined: Sat Sep 03, 2016 3:42 am
You can do what you want with the stream, mine goes straight to SQL. I've been recording it for over 6yrs 247. I save daily bak files and just load what I need for testing.
A month isn't really enough, 6 or 12 is much better. Markets evolve over the months and small margins can result in quite a lot of month to month varience. Plus if there's an element of seasonality you can compare now with 12mths ago. I wouldn't worry about what happens in specific races, it's only the averages that count. Observation can just result in recentism and humans are hopeless at judging big picture, they'll see a 'freak' 50 loss today so change stuff, and forget there were 3 equally 'freaky' £18 gains 3 weeks ago.
You'll also need to split your data in half (periodically, randomly) so that you develop your strategy on one set of data, then test it on the set you haven't seen. If you try to work on one monolithic set of data then you'll almost certainly introduce backfitting. A strategy released into the wild is seeing new data, you can test for that.
With a rigorous approach you also have a good handle on the likely variance before you open your wallet and you can measure it's performance (ev) live in ways other than the slighy randomness of the actual cash. I have strategies that have losing cash months, but I run several strategies over different sports so as a group there're fairly steady. But it's not uncommon for a strategy to not have losing months if it's a strong enough edge making a good number of bets. On that, something placing 1000 bets a day gives you info far faster than something doing 10. Exploit automation's ability to bet at scale, 1000 £1 bets are better than 100 £10 bets when it comes to evening out the twists and turns.
A month isn't really enough, 6 or 12 is much better. Markets evolve over the months and small margins can result in quite a lot of month to month varience. Plus if there's an element of seasonality you can compare now with 12mths ago. I wouldn't worry about what happens in specific races, it's only the averages that count. Observation can just result in recentism and humans are hopeless at judging big picture, they'll see a 'freak' 50 loss today so change stuff, and forget there were 3 equally 'freaky' £18 gains 3 weeks ago.
You'll also need to split your data in half (periodically, randomly) so that you develop your strategy on one set of data, then test it on the set you haven't seen. If you try to work on one monolithic set of data then you'll almost certainly introduce backfitting. A strategy released into the wild is seeing new data, you can test for that.
With a rigorous approach you also have a good handle on the likely variance before you open your wallet and you can measure it's performance (ev) live in ways other than the slighy randomness of the actual cash. I have strategies that have losing cash months, but I run several strategies over different sports so as a group there're fairly steady. But it's not uncommon for a strategy to not have losing months if it's a strong enough edge making a good number of bets. On that, something placing 1000 bets a day gives you info far faster than something doing 10. Exploit automation's ability to bet at scale, 1000 £1 bets are better than 100 £10 bets when it comes to evening out the twists and turns.
Thank you very much. Would you believe, I've been in and out of Bet Angel for a few years, but pretty much living within Guardian and didn't even know that your API existed!Euler wrote: ↑Tue Jan 07, 2025 4:42 pmIf you are heading down the coding route, then the Bet Angel API is a useful bridge to the Betfair API and you don't have to pay any additional fees. Ultimately that's why we created it: -
https://www.betangel.com/api-guide/
Thank you for this considered response - I particularly like the idea of splitting the data, not sure that that would have occurred to me.ShaunWhite wrote: ↑Tue Jan 07, 2025 4:50 pmYou can do what you want with the stream, mine goes straight to SQL. I've been recording it for over 6yrs 247. I save daily bak files and just load what I need for testing ...
A major bugbear has been waiting to accumulate enough data to notice patterns, and I now realise that I've only been scratching the surface. For instance, I was recently running two BA instances side-by-side with just two different adjustments across five Guardian rules and allowing them to run on every horse race across UK/IRE/AUS/US. After four days without a loss in either I was getting 'quite' excited, with the slightly more aggressive algorithm approaching gains of twice the (fixed) liability I was using, and the other instance surprisingly not far behind. I then suffered two losses in each instance, with each loss only appearing in either one of the instances. I'd been waiting for a loss - feeling that I wasn't really learning much from winning all the time - yet it was still deflating as the realisation kicked in that what had seemed like a mass of data whilst scrolling through my Guardian lists in reality represented such a tiny dataset. Hence my thoughts turning toward the possibility of back-testing.
I have to say that with only a few exceptions, the quality of response I see on this forum - to both my own queries and others that I follow - have been among the most considered and helpful of any forum I have ever visited.