Originally <a href='https://www.sportsbookreview.com/forum/showthread.php?p=27184997'>posted</a> on 09/03/2017:
Problem with using standard distributions is that you don't have uniform probabilities, as KVB said with the poisson errors.
If you have percentages with each game, you can easily create a distribution with a monte carlo (running a simulation)
Set up an excel sheet the following way:
In column A in rows 4-19, write each win probability for each of the 16 games
In B 4-19, write = rand(), you can drag and it will populate
In c4, write =IF(A4>B4,1,0), and drag 19
in row d4, =SUM(C4:C19)
In H1 Write total
In I1 write whatever the offered team total is
J1, Over, J2, Under
In K1, =COUNTIF(K6:K10004,">8")/(COUNTIF(K6:K10004,">8")+(COUNTIF(K6:K10 004,"<8")))
In K2, =1-K1
In L1, =IF(K1>0.5,K1/(1-K1)*-1,(1-K1)/(K1*1))
In L2, =IF(K2>0.5,K2/(1-K2)*-1,(1-K2)/(K2*1))
You've written about vba before, so I don't think you need instructions on how to put code into excel:
Here is the code that will run 10,000 simulations (Takes my computer around a minute, you can add a zero to run 100,000 if you want more precise numbers:
Sub runsim()
Application.Calculation = xlCalculateManual
Dim counter As Integer
counter = 1
Do While counter < 10000
Application.Calculate
Cells(counter + 5, "K").Value = Range("D4").Value
counter = counter + 1
Loop
Application.Calculation = xlCalculationAutomatic
End Sub
You will then have a distribution, by changing the number next to total, you can measure the change on money line, shown in column L
Problem with using standard distributions is that you don't have uniform probabilities, as KVB said with the poisson errors.
If you have percentages with each game, you can easily create a distribution with a monte carlo (running a simulation)
Set up an excel sheet the following way:
In column A in rows 4-19, write each win probability for each of the 16 games
In B 4-19, write = rand(), you can drag and it will populate
In c4, write =IF(A4>B4,1,0), and drag 19
in row d4, =SUM(C4:C19)
In H1 Write total
In I1 write whatever the offered team total is
J1, Over, J2, Under
In K1, =COUNTIF(K6:K10004,">8")/(COUNTIF(K6:K10004,">8")+(COUNTIF(K6:K10 004,"<8")))
In K2, =1-K1
In L1, =IF(K1>0.5,K1/(1-K1)*-1,(1-K1)/(K1*1))
In L2, =IF(K2>0.5,K2/(1-K2)*-1,(1-K2)/(K2*1))
You've written about vba before, so I don't think you need instructions on how to put code into excel:
Here is the code that will run 10,000 simulations (Takes my computer around a minute, you can add a zero to run 100,000 if you want more precise numbers:
Sub runsim()
Application.Calculation = xlCalculateManual
Dim counter As Integer
counter = 1
Do While counter < 10000
Application.Calculate
Cells(counter + 5, "K").Value = Range("D4").Value
counter = counter + 1
Loop
Application.Calculation = xlCalculationAutomatic
End Sub
You will then have a distribution, by changing the number next to total, you can measure the change on money line, shown in column L