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.

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

### 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 Elena,

Yeah well, sorry, in hindsight my previous question didn’t make a lot of sense. However, what I meant is somewhat related I guess.

It is clear to me that high yield equals high risk.

In the course you write :”If the relative deviation (yield) lies at an extreme distance from the expected ranges then take heed!”, and you add possible reasons for such a difference.

Since it it is impossible to know what drives bookies, one might want to avoid these betting opportunities altogether.

The word “extreme” here is however down to subjective opinion, and does not really teach me anything. What is extreme? 5%? 10% 50% Somewhere in the middle?

Seems to me this is an essential piece of info that is missing,

Can you shed some light on this? What is your approach on this issue?

Hi AM0751,

it is impossible to quantify “extreme” in a number as you really need to judge the automatic calculations for each match individually.

I always like bringing up the example with Man Utd after Ferguson left. Man Utd was playing for years like a well-oiled machine under Ferguson and then suddenly changed their performance under the new manager. The team didn’t change, it were still the same players, but their performance dropped like a stone. Of course, the bookies took this massive performance change into account after it became obvious and the differences between the VC calculations and the market prices were huge.

In the case of Man Utd it was obvious what happened and the differences were explainable. However, the automated calculations of the VC were not usable to identify value bets for a very long time, not until the team started to perform (statistically) reliable again.

This is why I write in the course: ”If the relative deviation (yield) lies at an extreme distance from the expected ranges then take heed!” – You can only use the VC and the Cluster Tables for teams that continue playing reliable, otherwise their past statistics are of little use to predict future outcome.

In the VC ,when you enter the odds ,I assume these are backing odds.Therefore when the outcome says lay is it saying lay at those backing odds to be of any value.

Hi Paul,

you have assumed right: the VC calculator is using backing odds as there are many people who are into betting but don’t have access to exchanges.

When the VC flags a particular bet as ‘Lay’ then that means that it’s definitely not a back bet, not at the backing odds entered. If it is actually really a profitable lay bet, this really depends on your strategy and portfolio.

Hello Elena,

One simple question really: in your personal experience, at what point do you decide that a valuebet (Value1, assuming that Value2 and PQ are acceptable to you) changes from a good price into a no-bet because the edge is so high that you realize the bookies know something you don’t?

An interesting conclusion in this thesis (everything above 40% turns out a profit)

https://www.stat.sfu.ca/content/dam/sfu/stat/alumnitheses/2012/FabianMoyaFinalVersion.pdf

Can you concretize a number from your own personal experience??

Hi AM0751,

Sorry for the late reply but I’ve been on holidays and I made a point to go somewhere without Internet access. 😉

To your question, Value I

(expected profitability: the difference between market odds and true odds)can even be negative as strange as it sounds… IF (!) it fits the portfolio that has been build to diversify risk. Therefore, sorry, I cannot concretize a number.Have you actually ever worked through my

course book? There I explain things like that in great detail.Hi guys, just a question regarding h2h matches.

I know it would never happen, but in theory, would too many h2h matches over a 10 year period adversely affect how the VC was intended to work? Or is it the more the merrier? What if, in theory, you had 50+ h2h’s (more than the league games combined) within a 10 year period?

Many thanks, Sam.

The more the merrier. 🙂

Hi Soccerwidow,

I was wondering if the profitability quotient (PQ) that you touch upon in your coursebook is something you take into account when using the value bet detector as well?

Seeing you are risk averse and prefer only the bets above a certain PQ, it could be argued that the bets found with the value calculator above your preferred PQ are few and far between.

Is the PQ function already “built-in” the value calculator in some way or another, or is the usage of, and filtering bets with the PQ a legitimate way of looking at the bets the Value Calculator suggests?

Thanks in advance.

Hi,

The PQ function isn’t really build in into the VC except that the VC calculates ‘Value I’ and ‘Value II’; one of them being the expected profitability [mathematical advantage] and the other the expected Yield [relative deviation].

In the course book I explain the Yield/Profitability quotient (value II divided by value I). For Over/Under bets I suggest to look at values over 3.0, but it is entirely up to you to decide if you like to go lower or even for negative values. It really depends on the strategy that you’re following.

Just add a few more columns to the VC and let the spreadsheet carry out the calculations for you as you like them.

Hi Elena,

Many thanks for your thoughtful, detailed and extremely informative response. The effort that you, your husband and Right Winger go to for your readers is nothing short of outstanding.

I’ll leave you to get on with your compliance work. And, again, thanks for your time and sorry for having taken up so much of it. Keep up the great work. Your readers are very grateful.

Kind regards,

Audiendi

Hi Soccerwidow,

If I recall correctly, in the course, you touch on the notion that a big difference between the market odds and the ‘true’ odds suggest that the true odds aren’t accurate for a particular bet? Sorry if I’ve misunderstood you there.

But what sort of telltale signs are there? Would you be able to provide some examples?

I’ve just had rather an awful weekend of betting. I placed seventeen bets (all of which calculated with the value calculator). There have been 7 successful bets and 10 unsuccessful bets. So, a hit-rate of 41%. But across the seventeen bets, the average (mean) probability is 77.53% (according to the value calculator’s forecasting). Using Microsoft Excel’s harmean, I’ve found that the average market odds across the seventeen bets is 1.60.

I’m worried that the difference is too great such that I’m not betting on actual value bets. Or that I’m not entering data into the value calculator correctly. But as I’ve watched both your Youtube videos and used the value calculator countless times, I really hope I’m using it correctly.

If I may take the liberty of highlighting some of the bets I placed which lost:

*Borussia Dortmund vs Bayer Leverkusen – Under 3.5 Goals (back)

-True odds of 1.33 (value calculator probability of 75.44%). Market odds of 1.59 (Betfair)

*Marseille vs Lille – Under 3.5 Goals (back)

-True odds of 1.23 (value calculator probability of 81.44%). Market odds of 1.63 (Betfair)

*Juventus vs Napoli – Over 1.5 Goals (back)

-True odds of 1.32 (value calculator probability of 75.89%). Market odds of 1.50 (Betfair)

*Haugesund vs Stromsgodset – Both Teams to Score – Yes (back)

-True odds of 1.39 (value calculator probability of 71.75%). Market odds of 1.72 (Betfair)

*Sporting Kansas City vs Vancouver Whitecaps – Under 3.5 Goals (back)

-True odds of 1.29 (value calculator probability of 77.67%). Market odds of 1.44 (Betfair)

Is this inconsistent? Am I doing something very wrong?

I understand that one needs to have placed over 500 bets before one can make any kind of statement about a system. But this does have me rattled. In the context of all my bets, there are some other worrying trends.

I’ve placed 97 bets so far (all of which with true odds worked out by the value calculator). There have been 66 successful bets. That’s a hit rate of 68%. But across the 97 bets, the value calculator’s average probability is 79.31%. That’s a big difference.

There’s another thing that’s a worry. In the course we learn that market odds don’t (necessarily) reflect the actual statistical probability of an outcome. But across all 97 bets, using harmean on Excel, average market odds are 1.50. In percentage terms, approximately 66%. It’s a bit of a worry that the market odds are a lot closer to the hit rate than the value calculator’s odds.

Sorry about the long, anxious and (somewhat) critical post from me. I’d be grateful for any guidance.

Kind regards,

Audiendi

Hi Audiendi,

I did a quick calculation for the Borussia Dortmund vs Bayer Leverkusen match using the cluster tables (just calculating with the team’s averages, not the HO/AO clusters), and here are the calculations:

The highest odds may differ from yours as I have been using Oddsportal but anyway, the calculations are spot on and the odds for the ‘Over 2.5 goals’ bet are even ‘Zero’ odds.

Please use the

cluster tablesfor calculating over/under goals probabilities when matches are played involving teams that have both a five-season history. The Cluster Tables are far more accurate.Otherwise, please bear in mind that the Value Calculator (VC) has been developed as a ‘shortcut’ to provide very rough estimates of the expected probabilities in order to check against the market prices and compile a portfolio of bets on the same game. I developed the VC as I was writing

match previews for Betfair, back in 2012, and I had to identify three (or more if possible) value bets for a single match. The VC was never developed as a tool to calculate as accurately as possible. Firstly, it uses only the last 25 matches of the teams that are playing as a barometer and secondly, the head-to-head correction factor is applied to all bets equally without any weighting or further corrections – these are done manually.However, with the course and cluster tables I have been trying to develop a tool with a much higher degree of accuracy but, of course, the cluster tables are limited to over/under goals only and also to teams that have played the last five consecutive seasons in the league.

To find an algorithm that works for absolutely everything is impossible. Therefore, the only advice that I can give to you is: Keep searching and experimenting (on paper, please!) until you find something that works for you.

On the Value Bet Detector, try the 50% to 60% cluster. That worked very well back in 2012. And also try to mix and match bets on one game. Have a look at the

match previews published at the timeas we explained our reasoning for the picks on each game in these articles.The regime for each match preview was: Firstly, use the VC to flag-up potential betting candidates (often more than five), and secondly, choose three, four or five of these as a mini portfolio of bets using reasoning and occasionally carrying out some more calculations.

I hope this all makes sense and good luck!

Elena

Hi Elena,

Thanks for the detailed and informative response. It definitely gives me something to work with going forward. When doing the course, I found myself weighing up the two tools (cluster tables and value calculator) and perhaps was not dispassionate enough in my approach. I wanted the value calculator to be the sharpest tool, but I understand how (without being able to lean on that five seasons’ curve in the value calculator), the cluster tables could be rather sharper for over/under betting. There was a reader (irelandfav) who, in 2012 or 2013, spoke of placing over 500 bets using the value calculator. Apparently, he/she was getting a 12% yield and the hit-rate was very, very close to the value calculator’s probability. Perhaps, I wanted this approach too much.

I think I’ll have to find a nuanced approach of combining both the cluster tables and the value calculator for analysis (but obviously it will have to be consistent).

May I ask, what sort of yield is achievable using the cluster tables? Would 10% or higher be unrealistic, do you think?

Kind regards,

Audiendi

Hi Audiendi,

both the VC and the Cluster Tables are odds calculation tools, one more accurate than the other. But what you do with that knowledge is completely up to you. The only thing that I can say about the reader (irelandfav), who in 2012 or 2013 achieved a 12% yield, is that he worked out how best to use the VC. As I was publishing the Betfair reviews, my yield was 11.67%. That was a ‘mixed’ strategy: taking a mix of high-risk and low-risk bets into ‘mini portfolios’.

Just yesterday as I was writing the

FAQs for the Cluster Tables, and whilst I was checking emails and comments for questions to publish, I stumbled upon one I received a while ago from a (professional) statistician who checked the course book for calculation errors before I put it on sale:“Overall, I think the book is an excellent introduction to the subject. It is very compelling that the bookmaker odds are based so clearly on the statistics, not the minute details that fans are so focused on. How close the fair odds calculations come to the actual posted odds is really striking. I can see why it is so eye-opening for the casual bettor.Just as an aside, probably four different times, I tried to jump ahead and not do some portion of the earlier exercises, because I was convinced I knew the probability calculations already. And even though I did, I still found I really needed to work through everything methodically if I wanted to fully understand it. I bet other readers will probably try to do the same thing! However, my advice is to go through everything slowly and steadily; it is very well laid out and each piece builds on the next, just as it is intended to.”I’m quoting this because if the statistician needed to work through everything methodically in order to comprehend the concepts then other readers certainly will need to do that as well.

In the chapter 3.1. (Yield, Profitability, ROI) I explain in great detail that Yield is mistakenly overstated enormously by many people.

Yield is mainly an indicator of the risk taken: The higher the yield the higher the risk strategy and vice versa.Using the cluster tables (calculating the ‘fair’ odds ranges) you should be able to toggle any strategy you wish that works for you and, whatever selection criteria you finally go for, it may produce a high yield (high risk) or low yield (low risk).

Generally speaking and of course, depending on the strategy employed, it is said that in football betting any yield over 7% is very good.

The Cluster Tables are not for achieving as high yield as is possible but for calculating over/under probabilities as accurately as possible and hence, reducing the risk of judgement errors.Summarising:Have a great day!

Hi Elena,

I definitely favour a low risk strategy. I’m sceptical about any yield which is too high as I always worry the pendulum could just as easily swing that much the other way (into negative yield). It just feel as if so much less control is exercised with higher yields. What I’m after is consistency. And I think any long-term success in betting will hinge on the bettors ability to look beyond profit and focus on the numbers being consistent.

There’s something Kipling wrote which I think is very good to be mindful of for those in betting. Please, bear in mind that I disagree with Rudyard Kipling, in his views and values relating to gender, race, class, imperialism, manhood, hetereonormativity and patriarchy. However, he wrote something which I think is good advice for bettors (indeed any human being). Kipling wrote:

‘If you can meet with Triumph and Disaster

And treat those two impostors just the same’

If you’re cool as cucumber, you can probably do as Kipling advises while employing a high risk strategy. But whatever approach you adopt, it’s crucial not to get carried away. Thus, I think a low risk strategy is more in keeping with the idea of calmly responding to whatever unfolds (however good or bad). I don’t want great material riches from betting. But I’d be ever so grateful if it’s possible to lean on mathematical probability (and hard work) to be able to make enough money to get by, anywhere in the world.This may not be possible. But I’m glad I’ve taken the trouble to learn as much as I can from the course and from putting this stuff to the test. If it does, I can maybe study just about anything I want overseas and pay my way, or live overseas and focus on Not For Profit work or even just get by while I look for a job that I actually want to do. If this doesn’t for (for me, or in general) then- oh well, least I can be happy that I gave it a damn good try. And by God I’ve learnt a lot from you, Elena 🙂

Anyway, one thing I noticed was in the small number of bets I’ve placed. If I have the value calculator probability averaging at about 75-80% and the market odds averaging under 1.50, then I struggle to break even. So I thought, I need that cushion. I’m going to look extra hard for value bets. So this week I went for bets where the value calculator still averaged 75-80%. But the average of the market odds was 1.60 (in fact, 1.62 with the two bets I placed last night). But so, so, so often where the market odds were over 1.55, the bets lost.

I’m trying to figure out what the happy medium is for market odds and if it’s a bad idea to go after something with very high market odds when the value calculator (or cluster tables) say there’s actually a high probability.

Kind regards,

Audiendi

Hi Audiendi,

that is my last reply to you this week. Although I enjoy your questions quite a lot as you encourage me to clarify things but unfortunately, they are taking up too much of my time. Currently, with the new General Data Protection Regulations that will come in effect on 25th May I’ll have to focus on more exciting things than betting for a while, such as getting Soccerwidow and its sister site, Fussballwitwe, fully legally compliant, e.g. updating our Privacy Policy and attending to other thrilling jobs like that. Sorry!

Do you actually know that I offer individual coaching per Skype or even personally? You can book a certain number of hours and ask me on Skype or in person all questions you possibly can think of. Skype is, of course, available internationally, but for face-to-face personal coaching you would have to come to Tenerife. You can do your studies without my presence and only pay for my time to prepare the meetings, checking your work as well as the time any appointment takes.

Anyway, to your question…

As already mentioned on the site, if the calculations and the market odds differ hugely then the bookmakers are taking something into account which the mathematical formulas are not.

The most straightforward and prominent example I can think of was the retirement of Alex Ferguson from management of Manchester United at the end of the 2012–13 season, having just won the Premier League. After that, Man Utd’s results went into decline. If you would have calculated the 1×2 odds, for example, the VC would have given you odds of 1.5 for United to win whilst in reality, they were maybe something as high as 2.5 (especially in their first year after the change). The bookmakers would offer that bet for, say 2.2 – a ‘clear value bet’ according to the VC but far away from the ‘real’ mathematical expectations, taking the decline of the performance of Man Utd into account.

That is what I call “choosing the bets using good reasoning and occasionally carrying out some more calculations”. When I see a big discrepancy between the automatically calculated odds and the market odds then I know that there must be something that the calculations don’t take into consideration. Although I normally preach throughout this website, and also in the course that reading football news is not necessary, in cases like this, even I start reading football news. Not really the latest football news but Wikipedia and other more permanent sources to see if I can find anything that may explain the anomaly.

If I cannot figure it out, I simply drop the match. Guessing is never a good bed fellow for a punter!

If you look closer at your calculations you will see that matches like the one between Borussia Dortmund and Bayer Leverkusen are stable and unexciting (betting-wise, I mean). The results of these games tend to be pretty reliable. These are matches between clubs that churn out expected results like well-oiled machinery and can be easily calculated in advance. This is why the Cluster Tables are so much more reliable than the VC because they require both teams to have played in all of the previous five consecutive seasons in the same league (however, this approach still doesn’t save you from anomalies like our Man Utd example).

With the VC you can ‘guesstimate’ any match as long as they have at least 25 past matches in the same league (approximately 1.5 years) and at least six head-to-head encounters at the same ground. This makes it a more unreliable tool as you can calculate ‘unreliable’ teams (those without five consecutive seasons’) with it as well. To use the VC properly, a much better knowledge of the bookmakers’ market dynamics and maths behind odds calculation is required than with the Cluster Tables.

Hope that makes sense.

Best wishes,

Elena

P.S. Kipling… I think he was a great writer. You may disagree with his views but that was his time, not ours. Historically speaking whatever he wrote was considered ‘politically correct’ then and we need literature like that to remember our roots and also to be able to see how our value system has developed over time.Cheers Soccerwidow.

That makes sense. Just to confirm, do you look at cup games in the H2H?

For example, so far I’ve included (in the H2H) games from the DFB-Pokal when looking at teams in the Bundesliga. And the Coppa Italia for H2H in the Serie A.

Hi Audiendi,

cup games are not league games and the games are played with a different motivation than league games. In addition, the finals are not played on the home grounds of any of the teams. It’s all about consistency and comparable data.

Hi Soccerwidow,

Sorry about the confusion. So are you saying you’d exclude cup games (at that specific venue) from the H2H? I agree with you that there might be different motivations.

In the article above it says

‘…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).’

I took that to mean that one should include cup games between the two sides at that specific venue for the H2H data. But you don’t include them?

Kind regards,

Audiendi

Hi Audiendi,

if you have 6 H2H’s for the last 10 years (league only), then don’t use the cup games. If you have only 5 H2H’s then add a cup game to get 6. Make sure that you don’t have more cup games than league games in your correction factor.

Six is already a very low number to draw any reliable conclusions from. However, you’re currently working through the course and you should be by now aware how challenging it is with only little data available to predict ‘accurately’ what the future will bring.

Hi Soccerwidow,

Cheers that makes perfect sense.

I’ve effectively finished the course. And I’m most grateful to you for your guidance. Last week I paper tested the Value Calculator with 22 bets. The average statistical probability (according to the Value Calculator) was 82%. Out of the 22 bets, 19 were hits. That’s about an 86% hit rate (from a very small sample space).

I think I’m starting to understand what you mean, from a statistical/mathematical viewpoint, with the idea that there’s simply too little data to predict outcomes with enough accuracy.

If I may be so bold, my inference was that across 500+ bets per season, there should be a broad statistical trend which is hopefully reasonably accurate.

So we can’t say how accurate the prediction for any given match will be. But we can have a degree of confidence that a certain proportion of matches will go as predicted (give or take standard deviation). So in other words, maybe about 400 out of 500 matches will end up with a particular characteristic (e.g. Over X goals). We don’t know which 400 matches. But we know about 400 matches.

It’s all about surfing that wave of distribution.

If I’ve misunderstood anything, feel free to shout me down.

Kind regards,

Audiendi

Brilliant! You’ve got it!!! 🙂

Hey Guys,

Just wondering, with the H2H minimum history, can we include matches between the two sides when they were in a lower division?

E.g. I’m having a look at Roda JC vs Sparta Rotterdam. If one were only to include H2H matches from the Eredivisie, then there are only three such matches (with Roda at home) in the past ten years. However, in that period, there has also been one KNVB Cup match and two matches when the two were in Eerste Divisie. That takes it up to six.

As I understand it, the last 25 home and last 25 away matches of the respective teams requirement is only met if all those 50 of those matches are in the same league. But that’s not the case for H2H is it?

Kind regards,

Audiendi

Hi Audiendi, sorry, but you have to compare apples with apples!

Eredivisie has a totally different quality of contestants than Eerste Divisie, and cup matches are again something totally different.

Therefore, no. H2H from one league only.

Also I wanted to ask, as there seem to be different answers to this in the comments. Should I include cup matches between the sides in the head to head or simply stick to league games?

Hi Vlad,

Include all competitive games at that same venue during the last 10 calendar years prior to the match date in question. So yes, this includes cup games.

As an aside, as EPL side Tottenham are playing their home games at Wembley this season, it is probably wise not to analyse any of their home matches as to do so would be comparing apples with pears. You’ll need to be aware of things like this when deciding which games to analyse.

I hope this helps!