Step 2 – Data Tab: Hiding, Sorting & Filtering
If you haven’t already done so, save a copy of your workbook now before you begin working with it.
Having identified that backing the draw between 3.32 and 3.65 looks promising we now return to the Data Tab to sort our list of matches into draw odds order.
Hiding
To make things easier to work with, we will first hide what we don’t need to see. Hiding does not delete any information.
We’re not interested in the home win at the moment so we will hide it, effectively moving the draw data to the left in the spreadsheet.
For the Excel novices amongst you:
- Click on the ‘AC’ label at the top of column V to highlight the whole column.
- Keeping the left mouse button down, draw the cursor across from the letter AC to column AK and then release the button to highlight all of these columns.
- Right click anywhere in the highlighted area and choose ‘hide’.
Here’s a screenshot to ensure you’re on the right track:
If you really want to go minimalistic, you can also hide everything else you don’t need.
The only columns up to column AL that you will need in view are columns P-W inclusive, and the match number in column G.
Sorting
Next, we sort the entire match list by the draw odds in column S.
To select the match list click on the bottom row of data. You will see in this example that the last row of data is numbered 1329. Left click on the number 1329 to highlight the entire bottom row. Here’s a snapshot.
(Click on the images below to enlarge them in a new tab):
HDAFU Table Data Tab: Highlight Row Example Screenshot
HDAFU Table Data Tab: Highlight Data Field Row Example Screenshot
Click on ‘OK’. A new box will appear:
HDAFU Table Data Tab: Select Column Password Field Example Screenshot
Filtering
We are looking at odds between 3.32 and 3.65.
We now need to hide the portions at either end of this scale. In other words, all matches with draw odds up to and including 3.31, and all those above 3.65.
Do this in the same way as before. Find the row that corresponds with the last value of 3.31. In our example, this is row 106:
HDAFU Table Data Tab: Hide Odds Example Screenshot
Carry out a check that our first inflection point, 3.32, is in fact in profit. Our example reveals that there were only four games in five seasons with draw odds of 3.32, and only one of these won. This odds cluster is not profitable and we can now hide rows 107-110 as we don’t need them.
Look at the next set of odds, 3.33. Rows 111-116 show six games played with these draw odds. Three won and three lost, but there is healthy profit as a result.
Our first inflection point is therefore adjusted to 3.33.
You may need to look at more than a couple sets of odds in this fashion until you find the first one in profit. These small adjustments are needed because Excel can only handle a maximum of 200 data sets (clusters) when compiling a graph, and match lists rarely divide equally into groups of 200.
Our list here is 1,320 matches long, which would mean 6.6 matches in each split. We therefore need to check exactly where the cut-off points are. The Inflection Point graphs tell us where to look, and the Data Tab sorting and filtering exercise allows us to be more accurate identifying where the inflection points really are.
Our data list now starts on row 111 at odds of 3.33.
We repeat this exercise to find the other end of the scale. We hide everything from the largest odds at the bottom of the spreadsheet up to odds of 3.65:
HDAFU Table Data Tab: Hide Odds Exercise Part 2
Our data list now ends on row 666 at odds of 3.63.
Important: The very last thing to do now is select (highlight) the remaining data list and sort it by column G (match number), to put everything back into chronological order. (This time you won’t need a password).
We need to do this to ensure accurate calculations of the winning and losing streaks.
Next Page: Step 3 – Adjusting the Filtered Five Season Analysis Box
Hi,
When will the 2018-19 winter league tables be available for purchase?
Thanks.
Hi Simon,
our deadline is to publish the tables for the 2018-19 season the next two weeks.
Hi – Just bought one of your tables to have a look at – looks great but I have a couple of questions about laying using these tables.
Looking at the inflection point charts there’s often area’s that look like they’d be great for laying so my questions are
– Would you ever have a backing and laying strategy in the same league (assuming the odds ranges were different for each).
– Have you ever found a laying strategy that was the best strategy in a given league? Perhaps my real question is – is it worth looking or would you stick to backing strategies
Many thanks
Bruce
Hi Bruce, you can do both: laying and/or backing.
Say, if the favorites in the particular league you’re looking at are vastly under-priced, then lay them. However, then the underdogs in that particular league will be over-priced… that would be a back bet strategy.
However, you cannot mix exactly these two together because in effect, you would be gambling on the same outcome… the favourite not to win.
Another thing to take into consideration is when deciding whether laying or backing that laying can only be done via exchanges and they charge a 5% commission whilst backing can be done via a huge range of bookmakers without any commission fee. Of course, that only works if you have access to the bookmakers that offer regular the highest prices, especially for underdogs.
You’ll need to do quite a bit of home work in that direction before you’ll be able to make an informed decision.
From what you say there’s no reason not to run laying and backing strategies in the same league – so long as they don’t directly conflict.
Some of what I was wondering about was that if one laid with a liability of 100 units then your returns would be lower and so backing strategies might always win out, while simply staking 100 units per lay might put pressure on one’s bank. Intuitively, and from the little homework I’ve done thus far, it seems for the same liability per bet a laying strategy would probably want more bets to generate a good return.
Hi Bruce,
regarding the best staking you’ll have to do some more homework and run some retroperspective simulations yourself. For that, identify the systems (strategies) you’d like to follow and then calculate various scenarios such as laying/backing with the same stakes or with the same liability or make the stakes dependant on the odds, and so on.
I personally prefer “Marias Staking Plan”. I don’t know if you ever heard of her. This was a thread, now over a decade ago, in which she published lay picks on horses and increased her original starting bank from £3000 within only one year to £100.000. Her stakes (risk) were interlinked with odds clusters. Here’s the original thread in the Webarchive: Marias Laying System. You can learn a lot from that thread.
In addition, on the German sister site Fussballwitwe.com, I published an article about this staking plan transferred to backbetting: Transferring Maria’s Laying System to Back Bets. Unfortunately, this article is not yet translated into English so you’ll need to use Google’s autotranslate option to read it in English.
Hope that helps and good luck!
Thanks for the reply – lots of work for me – I had heard of Maria but hadn’t paid much attention but now you’ve recomended her I’ll take a look.
Thanks again
Bruce
Many thanks for putting me onto this – looking at it, reading some more of your articles and playing around with excel it looks like it’s based on minimising the impact of longest losing streaks – I like this because I’m know I’m not good with drawdown – although I get better as I better understand the math.
So I have couple of things I’d like to ask – Maria was betting a single system on horses – so do you think her figures are too conservative if one is playing a few systems across a few leagues and can you point me to anything that would allow me to better quantify that – I realise that this could get very detailed and I’m not sure how detailed it needs to be but I can’t help being curious.
Hi Bruce,
I don’t really understand your question but I will try my best…
If you would like to replicate with football betting what Maria did with horse racing then you’ll have to search the inflection point graphs in the HDAFU Tables for curves that go permanently down and that have odds below 11.00. For example, betting on away wins in the German Bundesliga within the odds range from 1.67 to 3.00.
The next step is to look closer at this group (in the Data tab) and try to figure out a pattern with a promise of a positive return when laid. For that, split the bets into subsets and try to find for each subset, groups that have a lower hit rate than the odds suggest. These are your lay candidates.
This will reduce the number of available bets that are going to fit your criteria during the next season – in the German Bundesliga in this particular group to 25/30 matches each season.
In total you will need approximately 500 bets over the course of a full season. That means that you’ll have to identify this type of lay bet across perhaps 15 different leagues.
Say, in the Bundesliga the group you found had an average 55% hit rate for lay bets. To balance that low hit rate out you’ll need to find somewhere else a similar big group with a 95% prospective hit rate, and so on. In other words, try to balance the risk within your portfolio.
Once you have drawn up a list of candidates, then run a simulation using the previous season only (not 5 years) and see if you would have actually achieved the desired hit rate of 85% (like Maria achieved) and how your bank would have moved up and down. Remove the leagues that didn’t work or add more leagues/ groups for further diversification.
Really, this is a huge topic and as you say this could get very detailed.
The HDAFU Simulations are just a starting point to look into the right direction but they are really only the very first step.
You are right that the main purpose of Maria’s staking plan was to minimise the impact of long losing streaks.
By the way, all of her bets were value bets with an average mathematical advantage (yield) of 7%. This number may sound very low but she showed the betting community that it is better to concentrate on steady growth than on a high yield.
A high yield is a ‘synonym’ for high risk; the higher the yield you hope to achieve the higher the risk of a long losing streak, frayed nerves, desire to pull the plug on the system, etc. etc.
Just as a side note, could you please wait with any further questions until end of May? We are currently immensely busy in implementing the GDPR (European data protection law), which has to be in place by 25th May. Thanks for understanding! 😉
Many thanks for all your help – no more questions now – sorry if my last question was a bit vague – your answer gave me what I wanted to know – many thanks.
Bruce
Hi guys.
I just thought I’d go over a previous question just to make sure the information keeps up to date.
Regarding which bookies to tick at Oddsportal. This seems like a reasonably important point as we’d like to mirror the exact conditions you guys use when determining qualifying bets
Regarding Right Winger’s response to Tony on 10 May 2017 at 12:35 am
The bookmakers you guys tick at Oddsportal:
“188bet; 888Sport; Betclic; Betfred; BetVictor; Betway; BoyleSports; Comeon; Coral; Expekt; Island Casino; Ladbrokes; SBOBET; Sportingbet; Tipico; Titanbet”
I would’ve thought this info might’ve been a condition of the HDAFU tables themselves. Wouldn’t this info be of some importance in the pursuit of accuracy using your system (or maybe it has a marginal impact)?
Anyway I would just like to know if this info is still current and also why those particular bookmakers?
Keep up the great work you evil geniuses.
Sam.
Hi Sam,
The complete answer to your question can now be found in our article “Understanding the Settings in Oddsportal“.
You’ll find the bookmaker list on page 2.
Hope this helps!
Hi I’ve finally been able to go through your new table and explanation on Paraguay but I have 2 questions and I can’t seem to find the answers above. Firstly you mentioned that you should only choose the best option in each league. i.e. Away win has 2 odds clusters and you mentioned you should only choose one and not both. What about if say you had Draw and Underdog both meeting all parameters, would you include both or choose the best of the 2 so you’re only following one option per league?
Secondly, you have many tables available now. If you purchase all the leagues, what bank would you recommend? Above I noticed some people mention they’re betting 5% which converts to a 20-unit bank while you mentioned your study was with 2500 euros and betting 100 euro fixed bets (or a 25-unit bank). However you mentioned that you should look for an ELS of less than 20 in the historical results. That means you would blow your bank if you had a run of 20 losses and were betting 5% of your bank. I recall reading somewhere on your blog some time back that you should bet ELS x 5 which means in this case you should have a 100-unit bank. I think you actually showed it in a formula.
Anyway I hope I’ve made sense. Look forward to your response.
Hello again Rado,
I am glad you seem to have hit a rich vein of results at the very beginning of your campaign.
In contrary to your last paragraph, the HDAFU Tables are based on odds as near to the close of the ante post market as possible. In other words, within the last hour before kick-off, but mostly within the last five minutes before kick-off.
The optimum betting time is therefore within the last hour before the commencement of any match. Try and get it closer if you can (preferably within the last 15 minutes).
If you read the comments sections in all the HDAFU Tables’ articles, you will see that we do advocate the placement of some bets far longer in advance of the kick-off time, but only on games where we have a good idea of how the market is likely to move.
This is particularly the case with odds that are buried in the middle of the two inflection points, and are likely to remain so for the duration of the ante post period.
We can also take a chance with home teams/favourites that are likely to drift in (i.e. reduce in price). If they drift too far and outside the lower inflection point, we can always lay these situations with an exchange and achieve a second revenue stream in the form of arbitrage.
Attaining these special skills is all about knowing how and why the market prices move. To teach yourself this, you will need to observe price fluctuations in games over the whole of the ante post period.
With time, you will begin to see patterns – each league is slightly different according to its popularity and the weight of money placed by punters. Each bookmaker will follow one of three basic strategies in order to achieve its desired market share, and so on.
What I am saying is that it is possible to anticipate price movements in the market, and this is true of any ‘enclosed’ market with indentifiable boundaries such as football betting. It’s a small pond affected purely by supply and demand based around either two (e.g. yes or no) or three (e.g. 1-X-2) outcomes.
With bookmakers controlling the prices based on demand and their ability to supply, things are far easier to predict than something like the stock market where prices are determined by what people are prepared to pay for something at any given point in time.
Another big difference that makes bookmakers more predictable is the fact that the market is time limited (unlike stocks and shares, where only the IPO, the initial public offering of shares, has a limit – i.e. the length of time taken to fill the IPO).
The bookmaking industry is therefore more similar to the way insurance is transacted than to the vast ocean of unpredictable stocks and shares trading, which, I would also say, carries far greater risks and uncertainties.
Regarding your comment about odds jumping in extreme amounts, I would say that things tend to settle down more in the last 15 minutes before kick-off. Any bookmaker jumping around by 10 ticks or more (using your example), is likely to be desperate for market share in an attempt to address an imbalance in its book. Ignore these outliers as they are usually not representative of the market opinion at the time.
Whatever you decide to do, make sure you enforce the decision entirely. In other words, make a plan and stick to it. You are more likely to succeed doing this than treating every situation on its own merits.
The word ‘luck’ only comes into the equation when the end result turns out better (or worse!) than that to be expected from our own extensive set of skills.
When this happens, we can say that in spite of our skills-set and all the hard work and time that went into creating our understanding of the job, the end-result was better than expected – this is definitely ‘good luck’ in operation. However, you still need the cake in place before you can receive any icing that is likely to come your way…
Successful betting is all about hard work. It’s an evolutionary process, and if you are prepared never to give up learning, then you will become better and better at it. Learning is the key to life on earth. Once you give up learning and adpating to your situation, you become extinct.
Rado, I hope all of this helps in some small way! Thanks for your time and trouble in commenting again.
Time to report the results after my first week of betting 😀
It was an exceptionally good run of winning bets, probably it’s beginner’s luck, I don’t know, but the fact is that in only a week, I increased my starting bank by more than 50%! Let’s hope it continues like this from now on.
Probably I’m getting annoying, but can you please give me a piece of advice about betting times. All right, I now agree that placing ALL the bets on the previous evening is probably not a good idea, so I do my best to track the odds and place the bets in the last hour before kickoff (usually 40-50 mins before match start time).
Is it okay if I do that for all matches, because sometimes in the last hour before kickoff the odds are a true rollercoaster and can jump up or down by more than 10 hundreths in a matter of minutes? Well, I guess the effects will cancel each other out – sometimes I will bet and then the odds will become higher the next minute, but other times I will have luck and the odds will fall after I bet. So that’s probably not a problem. My worry is if the bet gets outside the inflection points. Then I guess I will simply leave it be.
Besides, the intro to your tables says something like “the odds in the HDAFU tables are based on the odds on the evening before kickoff of all matches”. So why then must we wait until the last hour before the game to place our bet, when all our data and inflection points are based on odds 24 hours earlier?