In conjunction with our regular match previews published in the German Betfair blog we are pleased to present our league match with head-to-head history ‘Value’ bet detector spreadsheet.
with H2H History
WITH Asian Handicap
(ex VAT; see below)
WITHOUT Asian Handicap
(ex VAT; see below)
0% VAT to UK customers
0% VAT to Non-EU customers
Read more: EU VAT Legislation
e-shop: Product Overview
View Your Shopping Cart
Our bet selections are always based on true odds calculations identifying mathematical ‘value’ in the markets. Of course, not every bet can win, but ‘value’ betting is the only reliable method of ensuring long-term betting profits.
‘VALUE’ bet detector: The Excel spreadsheet calculates the ‘true’ odds (= expected probabilities based on historical statistics) and then displays each ‘value’ bet recommendation. For every event included the expected values are calculated, compared to market prices, and finally the statistical probability of winning the bet is shown.
Soccerwidow follows the philosophy that only through identifying bets containing mathematical ‘value’ in the prices offered by bookmakers or exchanges is it possible to make consistent profits.
It should be noted that the spreadsheet is not a tool for automated betting. The tables provide vital information to improve the accuracy of your own predictions, and the figures do not substitute for reading salient news and applying good judgement.
The Excel spreadsheet offered for sale comes pre-filled with data from the Premier League game Liverpool v. Manchester City on 26/8/2012. Just delete or over-type this to analyse any match of your choosing.
Read a few sample match previews which apply ‘League Games with H2H History’ calculations:
- 26.8.2012 English Premier League: Liverpool v. Manchester City – Statistical Match Analysis & Football Betting Predictions
- 6.8.2012 Norwegian Tippeligaen: Stabæk v. SK Brann – Statistical Match Analysis & Football Betting Predictions
- 5.8.2012 Belgian Jupiler League: Zulte-Waregem v. Gent – Statistical Match Analysis & Football Betting Predictions
Notes: Adjust the picture quality at the bottom of the screen above by clicking on the ⚙ button (to the left of the YouTube logo), then click on ‘Quality’ and choose a higher resolution as desired. Go to full screen mode by clicking on the box symbol (to the right of the YouTube logo).
Supporting Videos
Data Sourcing: Learn where to find historical match data and download it from www.football-data.co.uk to make your own Excel store of statistics.
Data Input: Historical match results and head-to-head data input to populate the Value Bet Detector ready for input of the best odds you can find.
Frequently asked questions:
Can I use the spreadsheet in other leagues?
Yes, the calculations apply for any league in the world. The only parameters are that the game in question must be between two teams which have played at least their last 25 home or away games in the same league. The calculations are based on the home team’s last 25 home games and the away team’s last 25 away games, in the same league. Also, both teams must have played the corresponding fixture at least 6 times in the last 10 years (in other words, the home team has hosted the away team at least 6 times in all competitions (not friendlies) in the last 10 years).
How long does it take to enter historical data into the spreadsheet?
This is a manual procedure and you will quickly find which sources are best for the full-time and half-time results of both teams in the 25 matches to be analysed, plus the head-to-head encounters. We recommend Football-Data, Betexplorer, Oddsportal and Soccerway, although there are a host of other sites specialising in historical statistics.
At the start, you will find data collection will take about 30 minutes per match including entering the best bookmaker odds you can find (try Betfair and Oddschecker).
Are the formulas for the spreadsheet calculations visible?
Yes, we have hidden none of the formulas.
Exactly which historical records do I have to input?
The full-time and half-time scores for both team’s last 25 matches (home games only for the home team, away games only for the away team), plus the same for the head-to-head encounters over the last 10 years.
What knowledge is required?
You should already have a understanding of odds and how they are calculated. The spreadsheet is just a tool to identify potential bet candidates. You will need to understand what is meant by the term ‘value’ and also have a good idea how to spread risk with a well-structured betting portfolio. The spreadsheet is intermediate level, and you should be become entirely familiar with its mechanics before committing money to any bet recommendations suggested by the spreadsheet.
Hi again Socrates,
We find the spreadsheet accuracy over the long term very satisfactory with many of the 1×2 bet types especially, within +/-3% error rate. That said, we have had to fill-in over 700 spreadsheets from different leagues to gain enough data to analyse with this much confidence.
Personally speaking, we use the highest odds on the evening before kick-off and then place the bets early next morning. So long as you set yourself benchmarks for odds collection and bet placement and stick to them, you should receive pretty uniform results from the calculator.
After all, the Value Calculator looks only at past results (numbers) to predict future performance. It doesn’t take into account all the movements in team news and odds in all the historical fixtures analysed, so there is no need to let yourself become influenced by market movements prior to kick-off. The Value Calculator exists to take out the emotion from betting and turn it into a pure numbers game. Use your judgment once you have the numbers in front of you.
Because the Value Calculator uses the last 25 respective home/away results of the two teams involved, plus all competitive head-to-head results in the last 10 calendar years prior to kick-off (minimum of 6 h2H’s needed for relevance), you are always going to be slightly restricted on your match selection for analysis. But this is good, as you are only ever looking at a cluster of matches which roughly correspond with one another. You will always be comparing ‘apples with apples’ using such an approach.
Try also looking at the probability fields in the Value Calculator – backing high probability outcomes is still profitable in some leagues, even when the odds do not indicate significant value.
I hope I have touched upon “is there value when the odds are dropping”, but it is certainly an excellent point of discussion and perhaps a topic for an article in the near future.
Thanks again for your interest in Soccerwidow, Socrates.
Hi, again
How would you rate predictions based on team strength (table position) brackets or playing styles against pure h2h statistics? H2h gives a gut feeling of having little relevance when we’re talking years of separation. Do you have any indication of where one should draw the line on h2h back history?
Hi SoccerWidow,
I’ve been using the spreadsheet for a couple of years now with excellent results. I am trying to fine tune the model and my main concern is which odds to use?
Should i use the highest opening odds from various top notch bookmakers or wait until the final odds a few minutes prior to game?
Arguments for the latter say that odds just before kick off reflect the true odds of the particular fixture, because the market has considered all variables and therefore this set should be used on our calculation.
Personally, i prefer using the highest opening odds from top bookmakers (bookie’s juice <3%), before market forces distort the prices and making you lose most of the value .
I wonder your thoughts on this ….
I would greatly appreciate an article on "Is there value when the odds are dropping?"
Hi again TBBT,
To be absolutely clear – Backing is what you do when you play against the bookmaker.
I have had a look at Tempobet’s site and they are no different from any other bookmaker. Every bet you make here is a back bet, but please read the “Alternatives to Lay Betting” article linked in my previous post to you. You will see the back bets with bookmakers which are effectively lay bets (i.e. betting against an outcome). For example, Tempobet allows you to make 1X, 12, or X2 bets. This is the double chance market. If you choose 1X, you are backing the match to end in a home win or a draw to win your bet. You are effectively betting against the away team to win, which is the same as placing a straight lay bet on the away team with a betting exchange.
I hope this helps! 🙂
Dear TBBT,
The indicators are formulas we have inserted into the Value Calculator (VC) based on the probabilities of winning each bet type. The formulas are based on a statistically significant data set of matches we have analysed ourselves using the VC. Therefore, past performance has been noted and used to set the benchmarks for identifying future results.
In answer to your numbered quesions:
1. Yes, if the bookmaker odds are higher than the true odds then this is a value back bet. If they are lower than true odds, then this is a value lay bet.
2. We cannot influence your betting decisions but if you bet under-value consistently then you are guaranteed to make a long term loss on your investments.
3. The VC is set up in such a way that a correction factor of at least six competitive head-to-head games must have been played between the two teams at the same venue in the preceding 10 calendar years. By competitive games we mean league and cup fixtures, no friendlies. If you have less than six H2H meetings then do not use the VC for that match.
4. The VC will indicate whether to make a bet if the value is positive and the probability is high enough. At the end of the day, the VC is purely an arithmetical tool and does not substitute for your better judgment. Use the VC to highlight potential bets and then use your own intuition to make the final set of bet selections.
Do you mean no laying only backing results where you play? Laying is effectively betting against a particular result. If you feel Dortmund are only good enough for a draw at best then you should lay them to win. If they lose or draw, your bet wins. This type of bet can only be performed at betting exchanges or in the following ways via bookmakers:
https://www.soccerwidow.com/betting-advice/alternatives-to-lay-betting/
I hope this helps and thanks again for your interest in Soccerwidow.
What I mean by no laying and no backing is, you only play against the bookmaker, I play from Tempobet if you can go to the internet site than you can see what I am talking about. Please help me 🙂
Hi Soccerwidow,
I never play Asian handicap, would you recommend me to buy with or without Asian handicap excel? I mostly play Over/Under and “Both teams score or not” and if I find it worth betting FT. Will the excel sheet you provide enable my betting having a mathematical background. I am a Financial Mathematician in Turkey and I’ll probably understand the language you’ll talk to me but my main concern is that whether or not this excel will help me in my betting. 🙂 If I have a clear answer I would really appreciate it. Thank you
Hi TBBT, thanks for your interest in purchasing the VC… The ASH predictions are more accurate (have a lower error rate – I speak here to a mathematician who knows the term 😉 ) than the FT 1×2 market because the ASH calculations are based on goal differences whilst the 1×2 market is not.
FT 1×2 is tricky for the bettor because only 1 goal in favour of a team can turn the whole match to win in either direction, or turn a lead into a draw.
The same applies to the O/U market – again more accurate that 1×2. However, if you fancy 1×2 betting then the HT market is quite interesting, far more than the FT as the lads have then only 45 minutes to mess up with statistics.
Thanks for the answer Soccerwidow, but my main question here is will this Excel sheet help me to predict a likely outcome of the goals scored in the game? Can I just input past 25 and 7 H2H games and find a probabilistic result?
Yes, you can just input past 25 and 7 H2H games and find probabilistic results.
The spreadsheet calculates the ‘true’ probabilities of different outcomes (e.g. Under 3.5 goals, home team win at HT, etc.), for most bet types with error rates under 3%, meaning that if the VC calculated last weekend for Man City a 49% probability for their away win against West Ham (see example in following article: Are Betting Odds Worthwhile Calculating? Are Betting Odds Always Fair?), then the expected and empirically observed hit rate for matches in this particular probability cluster is (of course, after a good number of observations) somewhere between 46% and 52% (49% +/- 3%).
Here is a screenshot from my monitoring of the VC ‘true’ odds cluster calculations containing 609 observed matches.
Hi again, may i ask which indicators you look when betting over under, 1×2 and will both teams score or not? I believe, correct me if I’m wrong, from which I have learned from your videos is that:
1- if the true odds are lower than the bookmakers odds then there is a value there and is worth betting?
2- What if that even tough the value is negative but we believe that the bet will win, should bet on it, where it give higher odds to that bet.
3- In addition to this what if there are less than 6 h2h games, can we still bet on the game?
4- One last thing the game tomorrow between Bayern Munich and Borussia Dortmund, the model says to bet on Dortmund but I believe this is not a good bet, due to Bayern Munich’s winning streak and strength over Dortmund. Why is the reason for this?
Thank you in advance 🙂
and one last question, I really cannot understand what laying or backing is, from where I play there are no laying or backing. Can you explain this in couple of sentences also 🙂
Hi Soccerwidow
1.When you calculated those 609 and came up with the error rate, which “rules” did you use? Did you pick everthing that has shown a positive Value 1? Or everything that has shown you “Back” (Column D>E,…)? Or did you use more criterias apart from the calculated results (your personal/own estimate)
2.When testing the error rate for the AHC section, i assume u used the formula in automatic aswell, did you?
If yes, how did you came up with the “ColumnZ*1.2>ColumnR”
Did you only choose the Handicaps 0, 0.5, 1 or also the Handicaps 0.25, 0.75 (since these are only created out of the others)
– The problem is that i werent able to reach similar results – the percentages were off and also the expected yield is not matching the real. What could i do better when selecting a bet. (I prefer AHC and 1X2 – when i find a way to match the expected with the real results, like you do, i would be fine abandoning one of them)
3.To be sure: the H2H games are league aswell as cup games? i guess only the league cup in each country (not telekom cup or similar) – Ive read something about only using a max. of 10 H2H. What if e.g. south american leagues play each other 2-3 times a year at home, which leads to 15-20 H2H games.
4.The European Leagues are starting soon – would you leave out the first matchdays or start calculating from day 1.
Hi Kai,
In statistics, if one speaks about ‘error’ then the talk is about deviation: The deviation (error) from the (expected) mean.
You are referring to the screenshot with my monitoring of the VC ‘true’ odds cluster calculations. For example, look at Full-Time HOME – 40.0 to 49.99% probability cluster: You would have expected in this cluster a hit rate (mean) of 45.0%, and the observed hit rate was 48.3% (‘error’ = deviation from the expectation: 3.3%)
If you like to learn deviation and ‘errors’ then you should probably consider of purchasing the Fundamentals of Sports Betting Course; there I explain in great detail this terminology and its use for betting.
Your 2. Question… The automatic formulas are years of research and I simply cannot explain in just a few words how I came up with them. Please keep in mind that the VC was mainly developed to assist me with my match previews whilst I was writing for Betfair. I needed to be able to identify a diversified portfolio of different bet types for one single match, not only 1×2 bets.
If you are mainly interested into 1×2 betting you should probably consider the HDAFU Tables, they are much more useful for developing a betting system for the Home-Draw-Away market.
3. The more H2H you can take in the better. Again, please keep in mind as I was developing the VC that I was writing for Betfair Germany as well as Betfair England. Both were not interested into any American matches but only the EPL and the German BL. Therefore, at that time, I have never thought about the South American leagues when writing articles for Soccerwidow.
However, of course you can use the VC for any other professional leagues in the whole world (statistics are statistics!) as long as it’s a stable format.
4. For betting purposes if you use the VC only I would leave the first six weeks untouched as with taking 25 games for calculation you are going to cover the previous season and the pre-previous season which makes the results very volatile to deviation (errors).
Hi Soccerwidow,
In the case of international club competitions (UCL or Europa etc) where teams come from the same country (e.g. Barcelona vs. Atletico Madrid) is it preferable to use the spreadsheet with H2H history, used for domestic leagues or the spreadsheet for club competitions ??
both seem to apply , but i get totally different value bets !
Regards,
Hi Socrates,
the VC with H2H history returns more accurate predictions than the spreadsheet for international club competitions because of the H2H correction factor. Therefore, it is advisable to use the spreadsheet with H2H history if applicable.
Best wishes,
Soccerwidow
Hi, I bought excel sheet, I have read all the comments and they were all helpful, but I would ask if any of your clients or staff, made a video which shows clearly how to load the data of 25 games at home and outside the home, I can not copy from the sites that you have said ….
I’m doing everything by hand and it is a long job.
it is possible to have a video of a few minutes when you see the start, what should I do?
The video that you have put all the data is useful but Liverpool – Manchester C. are already loaded and does not help people like me who has to start from the beginning.
thanks
Hi Maurizio,
here is a video how to input data: How to input match & H2H data
Although data input is a manual job, with a little practice it will become easier and quicker, and finally the time needed to fill in the data for the last 25 matches and H2H’s should take no more than 5 minutes for each match.
Thanks
Hi Soccerwidow,
I also really appreciate you sharing this knowledge.
Is it possible to have the password to unprotect the sheet?
Hy hunny2o2,
Thanks for your custom. I’ve emailed the password to you 🙂
Hi!
I am just wondering why can’t I change the best odds on Asian Handicap Away team. The cells are green, but it wont let me change the automatic odds the sheets have entered there…these odds are different to the bookies odds I would like to enter there.
And also, can somebody advice me a good site to check odds for the games that have been already played?
Thanks in advance
Hi Lauri,
I have sent you the password to unprotect the spreadsheet, but roughly speaking, odds on one side of the equation are always balanced out by those on the other side. Therefore, the spreadsheet takes away the necessity to fill in the away team odds by automatically calculating the corresponding odds. For example, if one side of the bet is priced with a 30% chance of winning, then the other side of the bet has to be 70% (minus approx. 5% bookmaker overround).
There is a plethora of sites to check for games which have already been played. A good start is our Link Directory, but also the many articles in which we address historical data.
Thanks.
I understand that.
But the “Best odds from the bookmakers” are different than what the sheets give us in the Overs market.
For example:
Southampton vs ManU this weekend. Bet365 offers Over 2.5 @ odds 1.72.
Excel sheets automatically gives Over 2.5 odds @ 1.88.
So if the sheets say there is value betting over 2,5 @ 1.88, but odds with my bookmaker are 1.72, not 1.88 so the value there is wrong …
If the expected O2.5 odds are calculated 1.88 (the formulae in the spreadsheet takes 5% bookmaker overround into consideration), then 1.72 is far, far, far too low! Huge overround, and huge advantage for the bookies.
Do not back bets like this. Laying though may be considered, but it really depends on your strategy and the probability group(s) you are focusing on.
Dear soccer widow,
How would u approach a match between teams who share common ground ?
I mean local derbies like ; AC Milan vs Inter Milan or Roma vs Lazio?
Perhaps : 25 home matches each club + H2H history irrespective of who is home, with a minimum of 25 games?
Enlighten me once again !
You are correct!
Last 25 matches – both teams the home matches
H2H – all matches – irrespective of who is home.. Stick here to last 10 years. You can even reduce to max. 10 H2H matches. It’s only a correction factor in the calculations.
Thank u for the swift reply.
I was thinking of adjusting the spreadsheet for int. club competitions for the W.C.
Explaining : 30 competitive away matches for each team in order to assess the form + 10 on neutral turf and you can get a decent comparison for both sides.
With Brazil you need to include 30 matches on home turf
In any case, all of your sheets, which i bought, produce amazing results, but most importantly they change the way we bet.
Keep up the good work
Hi Socrates, please don’t! It’s not going to work. Save yourself the time and just enjoy the games. 🙂
However, if you wish to continue betting during the summer break in Europe, please note that the VC is applicable to every professional league in the world as long as it’s a league game with H2H history.
So, instead of a European team you can easily use it for Nordic games, or the MLS, for example.
Hi Soccer widow,
Will you produce a value betting spreadsheet for the W.C. in Brazil this summer?
I don’t see something relevant in the online store.
Thank u
Hi Socrates,
I’m sorry, but there is nothing planned for the W.C. this year.
Odds calculation for matches without any (or hardly) any statistics is enormously time-consuming.
We did match previews for the Euro 2012 (archive here), and each of them took most of a whole day to compile and analyse. Although the picks were very successful, unfortunately we do not have the luxury of such time to devote to a similar exercise.
My advice is just to lean back and enjoy the competition. We can give you one tip, England will not win the World Cup! 😉
Hi, I am interested in your spreadsheet, but nowadays http://www.football-data.co.uk do not offer their historical matches results ordered as you demonstrate in your video, and take too much time to input every single result one by one from a data historical result, do you have a solution for this problem? Do you sell your historical results spreadsheet for be able to use the value detector?
Hi Luis,
Football-data.co.uk still offers historical data for free download. Here the link: http://www.football-data.co.uk/data.php, but the data comes raw and needs to be prepared as explained in this video: How to prepare historical football data in Excel for further analysis
Ok I read the article that you sent me and I got in the right direction , but I have one more question.There you say that Value I is mathematical advantage (ROI) and Value II is relative deviation but in the spreadsheet that I have Value I is % price difference between market odds/real odds and Value II is mathematical advantage….
Hi Leon, well observed 😉
The titles in the spreadsheet are somehow ambiguous, but the formulas are exactly as explained in the article.
First column – Value I – price difference/ expected ROI
Second column – Value II – relative deviation
Strictly speaking, both values are ‘mathematical advantages’.
Odds calculation is nowhere properly explained and terminology is wildly mixed up everywhere. Unfortunately, there is no reliable reference literature I can refer you to. I need to come up with the terminology myself and correct it along the way. This is something where I’m consistently trying to link up with native English mathematicians/statisticians to help me with the correct use of mathematical terminology.
My native languages are Russian and German, and my financial and mathematical education is from German universities. Although I have strong links to universities in Germany, the mathematicians there are as lost as I am in regards of the handling of English mathematical terminology.
This is actually the main reason why there are not too many explanatory mathematical articles in this blog as each one needs weeks (and sometimes months) of research before ready for publishing.