This spreadsheet contains the calculations for international club tournaments between teams with no head-to-head history, applicable for competitions such as the UEFA Champions League, Europa League, or even the South American Copa Libertadores.
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
The instructional video below concentrates on the 1×2 odds section explaining the calculation of expected odds, and comparing them to market prices.
We also show you where to find records of the opening bookmaker odds and their changes over time, and how to interpret this information.
Later in the video we identify a method of easily identifying matches to analyse with the spreadsheet, those with possible strong discrepancies in their odds, by looking at the money matched and waiting to be matched for bets in the betting exchanges.
This is an educational video, aimed at those who wish to further understand odds calculation, and would like to know how market prices are set.
This video is about: Calculation of 1×2 (Home-Draw-Away) True Odds in UEFA Champions League games & How Market Prices are set
The examples used in the video are the UEFA Champions League matches Real Madrid vs. Manchester City, Dortmund vs. Ajax and Manchester United vs. Galatasaray.
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).
The spreadsheet can be used to calculate the ‘true’ odds for any match of your choice for international tournaments between individual clubs. The accuracy of the calculations improves if the teams in question have a similar recent history in the tournament under analysis (for example, both team’s last 10 international club competition games have all been in the Champions League).
All you need to do is fill-in basic data:
- the best odds you can find for the various betting options dealt with by the spreadsheet
- the home team’s last 30 home game half-time and full-time scores in their domestic league and all other competitions (no friendlies)
- the away team’s last 30 away game half-time and full-time scores in their domestic league and all other competitions (no friendlies)
- the home team’s last 10 international club tournament home game half-time and full-time scores (or if not 10, as many within the last 5 years as can be gathered)
- the away team’s last 10 international club tournament away game half-time and full-time scores (or if not 10, as many within the last 5 years as can be gathered)
The team stats can easily be found in sites such as www.soccerway.com or www.oddsportal.com.
Use a combination of an exchange site such as www.betfair.com and www.oddschecker.com for the best odds.
For each of the following events, the expected mathematical ‘value’ is automatically flagged up together with the expected probability (statistical mean) of winning the bet:
- Match Result (home-draw-away) at both half-time and full-time
- Draw No Bet
- Win to Nil
- Clean Sheet
- Both Teams to Score
- Over/Under ‘X’ Goals
- Half-time/Full-time
- Half-time and Full-time Correct Scores
- The full realm of Asian Handicap options including all the half and quarter point thresholds
The spreadsheet calculates ‘true’ odds (expected probabilities), and compares market prices with the statistical probability of winning the bet to show you whether there is any ‘value’ in the bet and therefore whether it is financially worthwhile undertaking.
It should be noted that the spreadsheet is not a tool for automated betting. Although the tables provide vital information to improve the accuracy of predictions, the figures do not substitute for reading salient news and applying good judgment in addition.
Required Acumen
Ideally, you should already possess a basic understanding of odds and how they are calculated. The spreadsheet is a tool to identify potential bet candidates.
However, you 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 become entirely familiar with its mechanics before committing money to any bet recommendations suggested by it.
Good Excel skills would also be an advantage.
For beginners we offer a comprehensive course: Fundamentals of Sports Betting: Betting on Over/Under ‘X’ Goals, which is a step-by-step guide including exercises and tutorials on how to understand odds calculation. Although the example used is the popular over/under ‘X’ goals market, the principles can be just as easily applied to any market of your choosing.
Full support for all of Soccerwidow’s products is always provided should you require pre- or after-sales assistance or clarification. Feel free to ask any question via the Comment section below.
Hi Soccerwidow,
How would you calculate odds for this year’s champions league final?
Am I right to assume that you use the last 30 away games for each team , all competitions, and the results against teams from the same nation’s opponent when away for the last 5 to 8 years ? i.e real madrid playing away against english teams and liverpool playing away against spanish clubs ..
Hi Sokrates,
it would be too good to be true if it would be that easy. Sorry, but it isn’t.
Two teams playing in the finals… if you would have head to head competitions from exactly the same teams in international competitions then you may consider these but you certainly haven’t. But only because a team has been playing another team from the same nation… No! This is absolutely not comparable.
Hi again,
one (hopefully) last question regarding the international sheet. In the video I have noticed, that you count international games twice: at first in the home tab and additionally in the H-international tab. Same for the away team. Is this correct? And what’s the reason for taking these games twice?
Hello again Acepoint,.
In the head-to-head tab, definitely include all relevant, competitive games between the two teams, even if this means duplicating one or two results in the home/away tabs.
The H-international tab acts as a correction factor to each team’s set of home or away results, and if some of these have been against each other, then these are valid statistics, which bring a little more focus to the fixture in question.
Retaining the duplicate information therefore adds more relevancy to the set of data we are using to form our opinions.
It may well be that the two teams in question have played each other more times in the same time scale as our data set than any other team shown in their last set of home or away games.
This is recent, relevant information and needs to be included, even if the duplicated data makes little or no difference to the eventual calculated values.
You will see a difference when the difference matters, and this could be the difference between a positive or negative bet value, or enough of a difference to push the yield of a bet into your portfolio (when otherwise you would have discarded it).
I hope this helps!
Hi Right Winger,
thx for the quick response, but I think you misunderstood what I meant. In the video (min 1:17 you see the CL game Real Madrid vs Ajax in the home tab, in min 2:29 you find the same game in the H-international tab. That is he duplication I was talking about. Anyhow, after reading your answer I guess that putting a certain CL or EL game into both (home and H-international) was on purpose.
Hi Right Winger,
one additional information: after the download I noticed, that the home sheet, cell E22, is protected/locked by an unknown password (I guess, by accident). There are some tricks to unlock it (what I did) but I wonder that no one else mentioned it here or informed you in the past.
Thank you, acepoint! It’s really odd but your are the first (since 2012!!!) who pointed this marginal error out to us. Probably all other people either found a way round this like you did, or they just didn’t fill in the cell as it’s not necessary for the calculations as cell E22 is within a ‘cosmetic’ column in the home tab.
Anyway, I have just corrected the cell E22 and uploaded an updated version to the store.
Thank you again!!! 🙂
Hello, what should i do if im trying to calculate a LaLiga match but some teams have descended to liga123 and other have ascended to LaLiga? I won’t have info between the new teams vs the other teams. Hope you understand me 😀
Games with teams that have been relegated or promoted you will have to leave alone. There are too many unknown factors which the calculations in both Value Calculators don’t factor in.
Hi, i have a problem when opening the football database from football-data.co.uk When i opened it doesn’t have a decent tablet formart instead its just text with comas. Do you know how to fix it?
Hi Yunte,
When you click on the files in football-data.co.uk, instead of choosing to open them, save them instead.
The files are in CSV format on their site (Comma Separated Values). Choose to save the file as an Excel Workbook and you should then be able to see all the information. (You may have to close the saved file and then reopen it).
This should solve your problem.
Thanks it worked. I’ve another question, in LaLiga there are teams that have been relegated (sporting, osasuna and granada) and others have been promoted (levante, girona, getafe). What should i do now with the data of h2h between the teams that are now relegated to LaLiga123 and the new teams in the first division? Hope you understand my question
How good does this spreadsheet work for comparing teams of leagues with bigger different (avg.) strength? E.g. a Champions League match between an english PL-team and a russian PL-team. I see that with the international club games you have a correction factor embedded, similar to the H2H-factor in the league games sheet. But frankly spoken, I have some doubts that this will work here as well.
Hi Rightwinger/Soccerwidow,
I hope you didn’t miss my question? Thanks in advance!
Hi acepoint, thanks for the reminder; we missed your question. Sorry!
For the Euro 2012 it worked like magic. Have a look at the match previews at the time, here’s one example: Euro 2012: Germany v Italy It also works for the UEFA Champions League. It works for international club tournaments between teams with no head-to-head history. All kind of professional teams, also countries.
Hello, I would like to know, if you have a spreadsheet for calculating probabilities in soccer games, of the Brazilian championship.
Thank you
Hi Marco,
you can use the True Odds & Value Detector: League Games with H2H History for any league games.
Unfortunately, I don’t know the Brazilian Championship set-up good enough to advise in depth. However, if it’s like the English Championship then the spreadsheet is usable. But keep in mind that, for example, the problem with the English Championship is that there are hardly any teams in the league who have played in the Championship to get 6 H2H games to serve as the correction factor.
Hi..Very useful information
I have one question ? How have you set the number 30 (games) to look back ? I mean why 30 and not 25 or 40 games ? Because on the League Value Detector you have 25 games to look back and here 30..
Thank you on advance
Hi Chris,
25 matches are enough for the VC with H2H history (which serves as correction factor), and 30 for the VC for International Club competitions (without H2H).
League games are played often enough so that 25 matches cover one season plus a few games the previous season. International competitions are less frequent and usually without a H2H correction, but 30 matches normally cover 4 to 5 years. Anything longer would mean more fluctuations within the teams (changes in personnel, etc.)
In the Metalist-v-Leverkusen game if the sum on the back is greater than the sum of the lay why do you say that there are more people betting against the win?
Money waiting on the back being much higher than that waiting on the lay bet may mean several things (list not exhaustive):-
(1) Traders expect a rise in the back prices
(2) Disparity between market odds and true probabilities
(3) Public hype after some news has been published
(4) There are interesting arbitrage options in this or within a related cross-market
There are many more reasons why the sum on the back may be greater than the sum on the lay. Betting is not limited to “people betting against the win”, it’s about maths, statistics, trading, portfolios and value.
What formula would you use for two teams from the same country?
Hi Marc,
please check out our True Odds & Value Detector: League Games with H2H History.
If the teams have not played in the same league for the last 25 games, and there is no head to head history available, then it is necessary to take the goal supremacy into account as well as factor in the last games as the season goes along.
At the beginning of a season, especially with new teams, the best choice is to stick to the averages of the previous season.
Unfortunately, the answer is not straightforward and certainly not easy, and this is the reason why we do not discuss and explain how to calculate domestic league games without H2H history on this site.
Would these calculations still be applicable for two teams from the same country? or would you simply use the domestic league games formula for that match?
Not sure if u have heard of this Asian sportsbook ,IBCbet .
The reason for asking if it is possible for a mathematician or statistican to calculate value bets in the midst of a live game is because they have other fields in their calculations unlike yours such as : total shots , total shots on target , attack ,dangerous attacks (correct me if i am wrong, thanks!)
Consistent winners on dead ball matches have a few notable personalities in Asia which I have heard and seen their predictions. However, you are the first person to actually ‘show me the light ” in deriving these calculations. I have almost been glued to your website since 2 days ago .Will defintely get your avaliable products once I am done reading all your avaliable articles .
Once again ,thank you for your willingness to share your expertise and knowledge .
Hi SoccerWidow,
Kudos to you for having such a fantastic website! I enjoyed myself while sifting through the countless articles that you have with regards to viewing soccer betting.
I am also in the sportsbook trade where we do see consistent winners ranging form a ROI of 12 percent (at least ) on dead ball matches to ROI of 5-6 percent on live matches.
Given your expertise , would you think if it is possible for a mathematician or statistican to calculate value bets in the midst of a live game ?
Regards,
Thomas
If somebody has sufficient mathematical knowledge as well as plenty (!) of time at hand for research, it’s certainly possible.
However, I don’t really understand you.
You write: “I am also in the sportsbook trade where we do see consistent winners…”
Which sportsbook trade are you in, and why do you ask if you see those consistent winners?
Ok thanks. I did not notice that post earlier.
Hi,
Is this calculation also applicable for Domestic Leagues?
This spreadsheet has been developed for the calculation of odds for international club competitions. It has not been tested yet for international club friendlies, domestic cup competitions, or league matches without H2H history. However, it may produce some accurate results in certain constellations.
Domestic league matches between teams with ample H2H history are calculated using the following spreadsheet: True Odds & Value Detector: League Games with H2H History