by For the arbitrage betting calculator let’s firstly build the model on basis of formulas
The ground for that basis originates from the main theorem
I’ll not go too deep into its design, but rather into its functionality
The main idea of how things should settle in is presented in the picture:
Model’s constituent parts
the input area is a placeholder for given odds:3 places
The indicator area is the place, cell with an inscribed formula for harmonic mean: =HARMEAN(D2,E2,F2)
So far so good
The most demanding part of the model is the calculation area,
which de facto serves to accommodate the matrix system : FAB-M:
A*X=B
whose solution has the form: X=A-1*B
Matrix system
In the current case, we got:
X- stake-profit vector
A- Arb matrix
A-1 -it’s inverse (this is done by excel)
B-wager vector
The inverse of Arb matrix
In order to determine its inverse, one must first select a 4×4 cell somewhere on the sheet, as in the picture:
After selection, in active cell(here is D8 !) inscribe formula: =MINVERSE(
after that, select the area of the Arb matrix (e.g. D3:G6):
Last step; press 3 keys: Ctrl+Shift+Enter and voila:
Matrix multiplication in ms Excel
Let’ go further and in:
I8:I11 enter the punter-profit vector (50,0,0,0):
next, select empty range K8:K11 and in the formula, bar enter this formula; =MMULT(
select range D8:G11
enter comma – ,
select I8:I11
press keys combination: CTRL + Shift+ Enter
The result of multiplying the matrix A-1 with the vector will be printed in this range!
clarifying of result
10.69760434 is the stake’s amount to Bournemouth home win:s(1)
14.16302546 stake height for Draw outcome, s(0)
25.1393702 – amount to Brighton away win s(2)
Total stake: s(1)+ s(0)+ s(2)= 50
punter’s revenue, nrevenue: 50.27874
Actual punter’s profit is: nrevenue – total stake=50.27874-50 ~ 0.28=0.56%
What to say except significantly negligible:0.56%
Profits that aren’t worth the effort!
And to make matters worse, due to strange payments (to 8 decimal places), the bookmaker can easily spot arbing
But the introduced model is valuable, so we continue to develop it!
Designing an Arbitrage betting calc in Excel
Creating process
Step-1: create an input area(black background) and indicators(gray background)
In G2 cell enter next formula: =1/A2+1/B2+1/C2
while in H2 cell enter: =HARMEAN(A2:C2)
Step 2- matrix system forming
in range B7:E7 enter the first row of the Arb matrix
Range B8:E8 is filled up with 2. row, B9:E9 with 3. and finally B10:E10 with 4. row of the same matrix
Colorized cells in range B7:E10 contains formulas:
And over those simple formulas, the Arb matrix is linked up to the input area!
In range F7:F11 enter wagers vector, but so that in cell F7 is entered formula:=E2
Range K7:K10 is reserved for members of the punter’s-profit vector:
1. Put range L7:L10 in the selection
2. in the formula bar enter:=Mmult(
3. enter Minverse(
4. put B7:E10 in the selection
5 enter ) to close inside the formula. and enter coma – ;
6. press keys:CTRL+Shift+Enter:
Designing a user interface
Interface by itself could be placed anywhere in the worksheet
The calculation hasn’t to be visible, but the input area should be visible!
For this purpose, do as follow:
click in cell I4
activate View tab and select Freeze panes menu
The consequence of this action, when scrolling down, the range A1:H2 stays always visible
Formula layer in the user interface:
Given that:
we are free to change the values of the cells within A2:C2 and E2
contains both profit and revenue
FAB-M doesn’t depend on the values of the coefficients
The created UI is an accurate and full-fledged bet calculator with indicators of the Dutch book’s direction!
To bookmaker or to a punter
ABC Upgrading
we can improve e.g. the odds entry in the range A2:C2
For this purpose, first range BC4:BC1205 fill with autofill method
then select A2 and go to the Data ribbon-Data tool menu:
click on data validation and inscribe data source BC4:BC1205:
Now we get this new functionality:
Repeat the same procedure 2x for B2 and C2 cells and you are ready to explore improved bet calculator
More advanced calculators of the same type can be found e.g. here
Words of caution:
This calculator covers up only all stake’s options, so it can’t(yet!) be used for ‘normal’ bet analyze
By ‘all stake’s options’ it means: p(1)+p(0)+p(2)
If one of the p’s is missing -Dutching appears!
If two p’s are missing we deal with a normal (single) bet
