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.
Betexplorer is a copy & paste solution. For the HT results click on ‘details’ of the matches. However, the HT figures need to be entered manually.
With some practice, this whole exercise takes 5 minutes max.
Very rarely Betexplorer may not have all HT results. Another good source is Soccerbase.com. Unfortunately, although this site has more stats, it’s not as easy to use as Betexplorer.
We use these spreadsheets ourselves, and data collection is done within 10-15 minutes.
also what is the password. i am getting angry with this. it is not letting me add any away data without a damn password
this does not give hta or hth figure so it useless. i need a copy and paste solution as this is taking around 2 hours for 1 game which is no good.
were to get good h2h stats that i can copy and paste quicky?
Betexplorer
You need to highlight and copy the whole H2H table, including header and the summary stats rows at the bottom. Then it works without a hitch. If you try to copy only a part of the table you’ll get the data into one cell in Excel, and this is not really helpful.
Also, what’s that column S? I understand it sometimes automatically puts ‘back’ or ‘lay’ there, but on what criterias?
And is it normal that there is Lay in column S for Homewin, but the correct score the spreadsheets predicts is actually 2-1 Homewin?
I hope you understand what I mean 😀
(1) The formulas are not hidden so you can see the criterias. This is our personal strategy to pick the bets for our articles. Feel free to change the formulas suiting your personal strategy.
(2) Yes, this happens. For example, the correct score prediction may be 2-1, and the probability for the home team to win calculates, say, to 60%. Imagine the market offers a price for the home team which translates to 70%. This is a lay-value-bet despite the home team having pretty good chances to win the match because in approx. 40% of the cases they will not win, and therefore one can expect a long-term profit.
And another one:
There are Under 0,5 and Under 1,5 and Under 2,5 goals etc rows, but no Over ‘X’ goals … how can I find out Over’s value and win probability etc.?
Scroll a little to the right. Same rows as Under 0,5, Under 1,5 and Under 2,5 goals, etc.
I must be blind, but there is no Overs in my spreadsheet (except the halftime O 1.5). I put it in the search engine and all … did scroll a little right from Unders rows, still nothing :S
The over bets are the opposite of the under bets. This means that you are looking at rows 32 to 38.
Column O – calculates the ‘true’ odds
Column P – you need to input the current prices (odds) for the respective market for the under bets
Colum Z – automatically calculates the ‘true’ odds
Column Z – calculates the corresponding odds for over bets (of course, it will only work, if you have put in the current market prices for the under bets in column P)
Column AA – calculates the value
Hope this helps 🙂
Okay.
But I don’t understand how the columns P to X work in Under/Over rows (32 to 38)
Here is an example: Fulham – Sunderland this weekend.
I enter U 1.5 odds from Betfair (4,20) into column P. True odds in column O are 2,78. So it would have to be a good value. But when I write ‘back’ in column R it will show -33,9% value in column X. It also shows Win probability: Betfair 76,2% , Real 64%. Doesn’t make sense at all for me.
Should be good value and bigger Real winning probability compaired to the betfair winning probability, when actual odds are bigger than zero odds.
And where does it show the probability of winning for the overs then?
Please email your spreadsheet over to me.
One more question, though.
If I take odds from Betfair. Let’s say for the Arsenal – Tottenham game in the coming weekend. The backing odds are: Arsenal (2,00) Tottenham (4,00) and Draw (3,85). Now I enter these odds into the spreadsheet and by the looks of it laying Tottenham is pretty good value.
But laying odds in the betfair are a bit different (laying Tottetnham would be 4,2).
I know I should probably just put the lay odds in the spreadsheet if I want to find value on lay bets, and backing odds when I want to find value in back bets, but I have read and studied the course and spreadsheets so much the last few days that my brain is overheating and i’m getting confused 😀
Excellent, lorenzo. You answered your own question correctly. 😀
Yep! Thank you very much, Jesse. Way easier to do it now.
And Soccerwidow, thank you even more for that amazing site and work you have done to help other punters to learn that valuable stuff here. Only in these last few days I have learned so much. I have a whole different view on betting now and it’s all thanks to you 😉 Of course it takes way more time than a few days to learn, but it’s a start and I keep reading your blog and öearn from you. So thanks again 🙂
Hi Lorenzo,
let me give You a suggestion regarding on how You can easily sort the data from a historical results spreadsheet found for example at football-data. co.uk:
First you have to select the columns containing date, home team, away team , fulltime result and half time result. Once these columns selected, you can easily sort them by home team and the name of the team/ or away team adn name of team. Excel will then automatically arrange the results by specified sorting criteria. All that is left to do then is as simple as copy and paste the data into soccerwidows home and away tabs.
How can you sort selected columns in a spreadsheet to match specified criteria? Its simple: first select columns, then go to ‘Data’ , then ‘Sort’. Excel will then open a dialog box containing 3 drop-down boxes. In the first you select for instance ‘Home Team’ and in the third you select ‘Custom List’ where you will Add a team’s name. Press Ok and Excel will automatically arrange the results of the Home Teams with specified names.
So the entire operation doesn’t last more then 1 minute.
I hope I was able to help you.
I just bought the spreadsheet as well yesterday and I must say it makes it much easier for a beginner like me. Altough I’m am learning your true odds course also and want to understand everything myself.
But there must be an easier way to enter data into the spreadsheet, rather than manually enter it for every match. I wanted to try it by clicking Data -> Get external data -> From web, but the button is unactive.
Do you know how could I change it active or any other way to make entering data to the spreadsheet a bit easier?
Thanks in advance,
You will have to amend the spreadsheet to accommodate your personal needs. People who buy the spreadsheet do it for different reasons, meaning that everybody has singular needs. Unfortunately, we cannot provide a technical solution for each individual approach.
I just bought the spreadsheet with H2H to test this value betting approach. I remember seeing one of your videos where you input odds-data on all past matches and then look at the last ten matches with similar ratings of (homewin-odds/awaywin-odds). Was is the reason that this calculation is left out in this spreadsheet? Og maybe I just didn’t look at the right place 🙂
You probably remember the video for international club competitions.
The spreadsheet for league games with H2H history does not require 10 matches with similar ratings as there is H2H data available. However, when calculating international matches then there are normally no H2H’s, or rarely very few. In order to calculate these matches accurately the spreadsheet uses the last ten matches against competitors with similar strength.
With teams such as Manchester City, which have changed significantly in stature over the last years, would it generally be more prudent to simply leave out old results or avoid bets alltogether?
Unfortunately, there is no straight answer to your question, except of that you must never bet if you are not totally convinced that it is a good bet. By saying “being convinced” I do not mean that one has a positive gut feeling about a bet, but that it is a mathematically sound bet, fitting in the portfolio, etc..
If a team has changed significantly in stature over the last years, and therefore you don’t trust calculations which are taking H2H results into account, then yes, leave it. There are plenty of other matches to look at.
What happens if the H2H form is say 0-5-5, for example? What do we do with the 0 figure? Also, what do you do with H2H with less than 6 games? Does this simply mean it is not suitable for analysis or can you use the H/A quotient to find the 10 closest fits?
Please explain what you mean exactly with H2H form 0-5-5?
If you have a match with less than 6 H2H then yes, you can use the H/A quotient to find the 10 closest fits.
However be very careful, especially if the teams have played in different leagues the previous season(s). In such cases, it’s possibly better simply to omit betting. At least lots of paper exercises are required before you feel confident to put money on those matches.
Thanks. Sorry I meant if one of the results in the H2H is equal to 0 i.e. team a has never beaten team b. By 0-5-5 i meant 0 home wins, 5 draws and 5 away, for example. This means a zero value will go into the spreadsheet and will skew the results. What do you do in this situation?
It will not really skew the results.
If there were, for example, 0 home wins, then this means that in 0% of the H2H matches the home team won. Say, the team comparison comes up with 20% home wins, then (20%+0%)/2=10%, meaning that the chance for a home win is pretty low, but not Zero.
In case, there are 2 times 0%, then the calculations will come up with a #DIV/0!. This means that this outcome will have odds of 100 and above. Please note that the spreadsheet is not developed for high odds like this as the calculations are based on very little data only.
Hello Soccerwidow!
Have two questions about spreadsheet:
1. Two teams are playing second season in Serie A but before they were both playing in Serie B. I can’t count 25 games just from Serie A matches and also I can’t count 6 H2H matches just from the time they were playing in Serie A. Ofcourse I can count the needed number if I take Serie B games. Is these kind of matches suitable for analytic?
2. If teams were playing a cup or play off game in neutral place are they suitable?
The more mixed an analysis (such as teams playing in Serie A and B) the more inaccurate the results of calculations. Just be aware of this. Do a lot of paper exercising before committing any money.
For H2H comparison you use everything – Serie A, Serie B, cup games, etc. – but not neutral grounds. In league matches is always a home game for one team, and an away game for the other team. Neutral grounds are away games for both teams.
Hi Soccerwidow
I just want to give you a little feedback…
In 3 weeks I have completed 68 bets. My bankroll management hasn’t been perfect but it’s work in progress
Week 1
10 Bets
Bet £352
Returned £135.98 (Profit)
Yield 38.60%
Week 2
24 Bets
Bet £1,232.75
Returned -£54.34 (Loss)
Yield -4.41%
Week 3
34 Bets
Bet £4,612.75
Returned £1,039.73 (Profit)
OVERALL
68 Bets
Bet £6,197.78
Returned £1,121.37 (Profit)
Yield 18.09%
Week 2 loss was mainly down to one match that I messed up on, I shouldn’t have bet 4 different markets in one match that the result affected all 4 markets, I learnt from that. I had lots of time in week 3 and was able to study and find lots of value bets.
One other thing I have learnt from looking over my results is this:
If I highlight 10 bets and take the average percentage of true odds they usually fall between 70% & 80%, When I look to the right to see my win/loss rate more often than not match up. E.G when the average is 80% I lost 2 bets out of 10, and when its 70% I lost 3 bets out of the 10, this to me is unbelievable and confirms that everything you taught is working perfectly. I can do it with any 10 bets and the win/loss ratio is always in line with the average percentage.
One silly thing I found myself doing is trading out, I’ve only done it two or three times and im learning to control myself. Below is an example of one
Palermo v Chievo (Sunday 30th Sept)
True Odds Pointed to Goals, lots of Goals, Betfair market said complete opposite.
First Half Goals O 1.5
True Odds 1.53 – 64%
Betfair Odds 3.35
Over 1.5
True Odds 1.10 – 91%
Betfair Odds 1.42
Over 2.5
True Odds 1.43 – 69.90%
Betfair Odds 2.14
Over 3.5
True Odds 1.90 – 53.70%
Betfair Odds 3.77
My bank had grown so much I was experimenting with ways to bet. I was risking double the percentage, so 64% I backed £128 – 91% I backed £182 – 69.90% I Backed £139.80 & the 53.70% I backed £105.40.
If all bets won I stood to be up approx £874, however as I risked so much and the odds were so far apart I decided to trade out after the first goal and too profit totalling £243 over the markets, As the goals started pooring in I seen why I shouldnt have traded out as indeed the score was 4-1 and I would of been laughing considering it was the first match of the day. Anyway lesson Learnt.
I just wanted to say how great it is having this knowledge, I have been able to fix the sheet so I can price a match up within 5 minutes and after studying I can markets priced and bets placed within 8-10 minutes.
Thank-you and If you would like me to Ill happily keep you informed of my progress in the coming weeks, months, years…
Regards my bet amounts, I’m just experimenting. I started off with Level stakes as you say and the profit was steady, not going down much when I lost, By betting the percentage I notice a few swings but so far its not been risky, I backdated it through all the bets and during week 2 i entered minus profit before my graph exploded in an upward trend, I am keeping track of what is the most profitable and thus far backing say £69 on a 69% true odds is working great, my bank grew and at the minute im backing double the percentages, so for a 70% chance im backing or laying with a liabilty of £140.
Your a genius Soccerwidow..
Here my graph: Graph-2012.10-irelansfav.jpg
Week 2 is the downward slump, I recognized my mistakes and continue to work on them 😉
Hi, mate. First of all, amaing progress with your bankroll.
I was just wondering how did you fix the sheet so you can get all the needed data into it in 5 minutes? If you don’t mind me asking.
Hi lorenzo, there is no fix to the sheet.
What we have is the data of all leagues on the computer, sorted by years and teams. When we wish to analyse a certain match, we simply open the datasheets and then copy and paste the data into the spreadsheet. The H2H results we copy and paste from Betexplorer.
With a little practice this whole manual action only takes 5 minutes per match.
We tried macros, and various other automatizations. However, the main problem is that for this to work the row data has to be standardized, meaning to be in the same format for each league. Unfortunately, there are leagues who have 17 teams, others more, others less. Some leagues don’t play equal numbers of matches home and away, etc.
In the end we found it much faster to get the data manually into our spreadsheet than trying to stay ahead of the ever changing data.