1. #1
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    Looking for info/tutorial on building Monte Carlo model for game outcome probability

    I'm interested in using Monte Carlo simulation to predict daily game outcomes (NHL). Ideally what I'd like is a sample of how to input/format the data in excel and run a simulation 'x' amount of times to get a win probability. I really have no clue where to begin, there is information online regarding these simulations but I would like something directed on single event sport outcomes. An excel template, or step by step would be ideal. I know I'm asking a lot but these things must exist. I'm no math guru, that's why step by step in laymans terms is ideal. Any help is greatly appreciated, thanks in advance.
    Nomination(s):
    This post was nominated 1 time . To view the nominated thread please click here. People who nominated: swordlord

  2. #2
    HeeeHAWWWW
    HeeeHAWWWW's Avatar Become A Pro!
    Join Date: 06-13-08
    Posts: 5,293
    Betpoints: 36

    Excel is hopeless for this sort of thing - teach yourself R.
    Points Awarded:

    KVB gave HeeeHAWWWW 1 Betpoint(s) for this post.


  3. #3
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    I'm looking to get in at an entry level before I'm an expert. Can you point me in the direction of a good R tutorial focused on sports prediction model using Monte Carlo?

    Saying teach yourself R, does nothing to answer my question.

  4. #4
    gdon44
    gdon44's Avatar SBR PRO
    Join Date: 04-10-16
    Posts: 58
    Betpoints: 356

    https://corsica.teachable.com/p/an-intro-to-r

    Haven't tried this but have read good things about it and it's hockey focussed.
    Points Awarded:

    MadTiger gave gdon44 1 Betpoint(s) for this post.


  5. #5
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    gdon44....excellent stuff, I'll look into and greatly appreciate your feedback!

  6. #6
    gdon44
    gdon44's Avatar SBR PRO
    Join Date: 04-10-16
    Posts: 58
    Betpoints: 356

    Tell me what you think. I haven't had time to dive in so stick with what I know in excel. I might give this course a try in the future.


  7. #7
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    I'm still looking for other options and ideally would like to stay within excel. Learning a new software is not my top priority, excel is probably capable of doing what I would need.

  8. #8
    SBR Drew
    SBR Drew's Avatar Administrator
    Join Date: 01-08-18
    Posts: 3,885
    Betpoints: 24

    How did this go?
    175 pts

    3-QUESTION
    SBR TRIVIA WINNER 03/07/2019

    $20
    Angelman
    donation 02/18/2019


  9. #9
    MadTiger
    Wait 'til next year!
    MadTiger's Avatar Become A Pro!
    Join Date: 04-19-09
    Posts: 2,562
    Betpoints: 165

    ---
    Last edited by MadTiger; 05-26-18 at 06:34 AM. Reason: Deleted link--too buggy

  10. #10
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    link worked fine, didn't understand it though lol!

  11. #11
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,284
    Betpoints: 12

    Relatively simple in excel.

    1. Use the rand function to generate
    2. Use do while and whatever you want to do

    Likely not a good tutorial anywhere. Just google as you go: "How to automatically Create a new Sheet VBA" will usually tell you what you need to do quickly at each step.

  12. #12
    Miz
    Miz's Avatar SBR PRO
    Join Date: 08-30-09
    Posts: 542
    Betpoints: 408

    Another idea might be to team up with someone who is more experienced at programming than you. First you'll need to formulate all of the inputs so you can give him a concise, rule-driven model to implement. That will require a lot of work on your part. Often times, brilliant programmers just want a challenging hobby task to work on that may or may not produce financial gains. They love real world scenarios, testing, and results. Sports betting provides that for them, and for you. Plus, working as a team is gratifying. I've worked alone and as a team, and profited in both, but I had had much more fun as a team. Just be up front with your potential team member, that there is no promise of financial gain. You may be surprised that people are willing to work with you.

  13. #13
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    I have the data (stats) for the day's games. I want to know how to set up excel to run a monte carlo simulation on the data. how many inputs (stats) can be used in the simulation and give me a win probability.

  14. #14
    Miz
    Miz's Avatar SBR PRO
    Join Date: 08-30-09
    Posts: 542
    Betpoints: 408

    it seems like you want people to just tell you how to do things. nothing is free, do some research

  15. #15
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    Quote Originally Posted by Miz View Post
    it seems like you want people to just tell you how to do things. nothing is free, do some research
    you are wrong

  16. #16
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,284
    Betpoints: 12

    It seems like you don't really know where to get started, but generally have an idea of what a monte carlo is. This is a good video for building an excel monte carlo for stock prices which should give a general idea of where to start:

    https://www.youtube.com/watch?v=e79OtCamxD0

    Basically, set your variables up in order to get the probabilty you are looking for. Hockey is kind of an odd sport to do this with, but consider football instead. Say that a team gains 5 yards + (DVOAoff - DVOADef) +- 3 yards, you would set that up with a rand() to get the random numbers for the monte carlo.

    In my own work, using golf as an example (even though I lose my face every weekend betting golf). I have 4 variables that give me a double birdie, birdie, par, boogy, double boogie percentage based on the par for the hole and historical data. So for example, say that the distribution is as follows:

    Double Birdie: .05
    Birdie: .25
    Par: .50
    Boogey .15
    Double Boogey .05

    My rand() would produce a number, so if it produce .15, I would have a birdie for that golfer for that hole. The vba script would record that value, and then recalculate the Rand(), record again, and then recalculate 1,000 times, and then move to the next golfer, and so on.

    Have you considered using a poisson instead of a monte carlo for Hockey? Hockey is not really path dependent, except for pulling the goalies, so you might be able to solve that parametrically instead of through simulation. Sports like football and baseball are more path dependent.

    Good luck, once you build one, it becomes easier to do it in other sports.

  17. #17
    gdon44
    gdon44's Avatar SBR PRO
    Join Date: 04-10-16
    Posts: 58
    Betpoints: 356

    Quote Originally Posted by oilcountry99 View Post
    I have the data (stats) for the day's games. I want to know how to set up excel to run a monte carlo simulation on the data. how many inputs (stats) can be used in the simulation and give me a win probability.
    Further to Waterspub (or in support of it I guess) you need to find a win probability that you enter into a RAND() formula and then run a Monte Carlo. I think that's the hurdle you need to figure out. Whatever the inputs/stats you have selected, you have to figure out the formula to get that into a win probability. As Waterspub points out that's tough in NHL given the variance of the game and horrible job NHL does of providing accurate stats making it not path dependent. (Can't wait for NHL to bring in the player tracking that IIHF used at the WHC).

  18. #18
    Bsims
    Bsims's Avatar Become A Pro!
    Join Date: 02-03-09
    Posts: 620
    Betpoints: 681

    Quote Originally Posted by Miz View Post
    Another idea might be to team up with someone who is more experienced at programming than you. First you'll need to formulate all of the inputs so you can give him a concise, rule-driven model to implement. That will require a lot of work on your part. Often times, brilliant programmers just want a challenging hobby task to work on that may or may not produce financial gains. They love real world scenarios, testing, and results. Sports betting provides that for them, and for you. Plus, working as a team is gratifying. I've worked alone and as a team, and profited in both, but I had had much more fun as a team. Just be up front with your potential team member, that there is no promise of financial gain. You may be surprised that people are willing to work with you.
    I messaged you to see if you are interested in trying this.

  19. #19
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    What did you guys mean by 叢ath dependent?

    Water...I do use poisson. I will check out the video, thankyou for your input!

    Gdon....your saying I need a win probability before I run the Monte Carlo? I thought a Monte Carlo would give a win %.

    Guys I知 branching out here with Monte Carlo, looking for tools to put in the toolbox. This is a hobby for me and I知 by no means a mathematician.

    Bsims....check inbox

  20. #20
    Waterstpub87
    Life-Style Arbitrage
    Waterstpub87's Avatar SBR PRO
    Join Date: 09-09-09
    Posts: 3,284
    Betpoints: 12

    Quote Originally Posted by oilcountry99 View Post
    What did you guys mean by 叢ath dependent?

    Water...I do use poisson. I will check out the video, thankyou for your input!

    Gdon....your saying I need a win probability before I run the Monte Carlo? I thought a Monte Carlo would give a win %.

    Guys I知 branching out here with Monte Carlo, looking for tools to put in the toolbox. This is a hobby for me and I知 by no means a mathematician.

    Bsims....check inbox
    Path dependent means something that is dependent on steps before it. So consider baseball, the number of runs scored on a single is dependent on how many men are on base.

  21. #21
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    Water...thanks for clearing that up, I知 one step brighter this morning

  22. #22
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    I found a good basic tutorial used for the NBA

    https://www.youtube.com/watch?v=tsdFrOLnTXw

  23. #23
    gdon44
    gdon44's Avatar SBR PRO
    Join Date: 04-10-16
    Posts: 58
    Betpoints: 356

    Quote Originally Posted by oilcountry99 View Post
    What did you guys mean by ‘path dependent?

    Gdon....your saying I need a win probability before I run the Monte Carlo? I thought a Monte Carlo would give a win %.
    Poor choice of phase on my part. I may be wading out past my depth here and risk muddying the water but a Monte Carlo gives you a random selection of what will happen given an uncertainty in the possible outcomes. So you need to figure out how the stats you have selected will reach an outcome. If you're looking at just win/loss then you have to say 'ok Team A has 58% corsi and Sh% of 9.8% and Team B has SV% 918 and that results in Team A scoring 2 times 40% of the time and scoring 3 times 48% and then do the same with Team B and run a Monte Carlo to find out how often Team A wins over 1000 games.

    The hurdle you need to cross is determining those steps that lead to a goal that ultimately tells you that Team A should win 3-2. That's the path dependent part. You can't just throw stats into a machine and it'll tell you who will win. Using Water's golf example, you need the historical occurrence of an event to determine how often different events will happen in the future and then Monte Carlo will give you the odds of that happening over 1000 occurrences. So you need to work out what stats you're using and how they produce an outcome for each team then put those outcomes against Team B's potential outcomes in the simulation.

    So in an ineloquent way, my point was the Monte Carlo tool doesn't compute win % based on the stats, it produces win probability based on the outcomes from those stats and you need to provide those outcomes.

  24. #24
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    gdon
    appreciate the breakdown...makes total sense and my main purpose of this thread is how to setup excel to run a monte carlo on the given predicted outcomes at you state. the link I posted is exactly what I was looking for, a simplistic approach on how to set up excel to run a monte carlo to get a probability for a given game. Of course the example can be expanded upon and stats can be adjusted but the process is there.

  25. #25
    vampire assassin
    vampire assassin's Avatar Become A Pro!
    Join Date: 03-09-18
    Posts: 70
    Betpoints: 1597

    If your approach is fairly simple (e.g., you are giving each team a probability to score based on that team's strength, the time remaining, and the game state), excel works very nicely for markov chains. This will give you a similar result to a monte-carlo, but you don't have to wait for it to converge.

    How were you implementing your monte carlo?

  26. #26
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    I have not implemented anything yet. I will not concerned with time remaining and game state.

  27. #27
    MathNerd
    MathNerd's Avatar Become A Pro!
    Join Date: 10-21-17
    Posts: 4
    Betpoints: 36

    OP: It sounds like you're squared away with the idea for an NHL MCSim. Below, I've provided a toy MLB example in R (because I value my time!). My hope is that you'll implement this in Excel and realize that you should add R to your tool belt in the future. Best of luck with your MCSim.

    The _model_ is simple:
    1. I assume players bat 4x in a game
    2. If the players OBS >= to a random number, the player scores a point
    3. The team that scores the most points is the winner
    4. I ignore "tie games"

    Here is the code:

    ## OBS (from RotoChamp) -- This could be scraped from the site
    BAL <- c(0.307, 0.297, 0.392, 0.326, 0.258, 0.239, 0.309, 0.318, 0.313)
    NYY <- c(0.363, 0.423, 0.294, 0.323, 0.317, 0.339, 0.292, 0.433, 0.376)

    ## Here's the basic idea
    at.bats <- 4 * 9
    rnd1 <- runif(at.bats)
    rnd2 <- runif(at.bats)

    ## Each true value is a point (Note: This works because of the 'recycle rule' in R)
    rnd1 <= BAL
    rnd2 <= NYY

    ## Total the scores for one iteration
    sum(rnd1 <= BAL)
    sum(rnd1 <= NYY)

    ## The score function returns a vector of n scores
    score <- function(obs, n) replicate(n, expr = sum(runif(at.bats) <= obs))

    ## Run the MCSim
    iterations <- 100000
    BAL.scores <- score(BAL, n = iterations)
    NYY.scores <- score(NYY, n = iterations)

    ## Ignore the "tie games"
    iterations <- iterations - sum(BAL.scores == NYY.scores)

    ## Calculate the win probability
    sum(BAL.scores > NYY.scores) / iterations
    sum(BAL.scores < NYY.scores) / iterations


    And the output:

    > ## OBS (from RotoChamp) -- This could be scraped from the site
    > BAL <- c(0.307, 0.297, 0.392, 0.326, 0.258, 0.239, 0.309, 0.318, 0.313)
    > NYY <- c(0.363, 0.423, 0.294, 0.323, 0.317, 0.339, 0.292, 0.433, 0.376)
    >
    > ## Here's the basic idea
    > at.bats <- 4 * 9
    > rnd1 <- runif(at.bats)
    > rnd2 <- runif(at.bats)
    >
    > ## Each true value is a point (Note: This works because of the 'recycle rule' in R)
    > rnd1 <= BAL
    [1] FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE
    [25] FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE
    > rnd2 <= NYY
    [1] FALSE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE FALSE FALSE TRUE FALSE FALSE TRUE FALSE TRUE
    [25] FALSE TRUE TRUE FALSE TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE
    >
    > ## Total the scores for one iteration
    > sum(rnd1 <= BAL)
    [1] 13
    > sum(rnd1 <= NYY)
    [1] 14
    >
    > ## The score function returns a vector of n scores
    > score <- function(obs, n) replicate(n, expr = sum(runif(at.bats) <= obs))
    >
    > ## Run the ** sim
    > iterations <- 100000
    > BAL.scores <- score(BAL, n = iterations)
    > NYY.scores <- score(NYY, n = iterations)
    >
    > ## Ignore the "tie games"
    > iterations <- iterations - sum(BAL.scores == NYY.scores)
    >
    > ## Calculate the win probability
    > sum(BAL.scores > NYY.scores) / iterations
    [1] 0.3280267
    > sum(BAL.scores < NYY.scores) / iterations
    [1] 0.6719733
    >

    NYY wins 67.19% of the time based on this idiotic model. It take about a second to run. It took longer to write the forum post than the code.

  28. #28
    Bsims
    Bsims's Avatar Become A Pro!
    Join Date: 02-03-09
    Posts: 620
    Betpoints: 681

    Quote Originally Posted by MathNerd View Post
    OP: It sounds like you're squared away with the idea for an NHL MCSim. Below, I've provided a toy MLB example in R (because I value my time!). My hope is that you'll implement this in Excel and realize that you should add R to your tool belt in the future. Best of luck with your MCSim.

    The _model_ is simple:
    1. I assume players bat 4x in a game
    2. If the players OBS >= to a random number, the player scores a point
    3. The team that scores the most points is the winner
    4. I ignore "tie games"

    Here is the code:
    Welcome to the forum. You look to be a good addition. Hopefully you will gain some benefits from here.

  29. #29
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    MathNerd
    Thank you for sharing a detailed example we can all benefit from. I have dabbled with R, doing some online courses and such. I haven't truly invested time in R. I see it's popularity growing amongst the 'betting' community. I like excel because you see everything all together in your 'workbook'. I may look into R further but not at this time

    Thank you for the positive example of a monte carlo system. Solid information is hard to come by, all the best to you and your endevours moving forward.

  30. #30
    HeeeHAWWWW
    HeeeHAWWWW's Avatar Become A Pro!
    Join Date: 06-13-08
    Posts: 5,293
    Betpoints: 36

    Quote Originally Posted by oilcountry99 View Post
    Thank you for sharing a detailed example we can all benefit from. I have dabbled with R, doing some online courses and such. I haven't truly invested time in R. I see it's popularity growing amongst the 'betting' community. I like excel because you see everything all together in your 'workbook'.
    If you use rstudio, you can view a dataframe/matrix like an excel workbook:
    https://support.rstudio.com/hc/en-us...he-Data-Viewer

  31. #31
    oilcountry99
    oilcountry99's Avatar SBR PRO
    Join Date: 08-29-10
    Posts: 499
    Betpoints: 3421

    Quote Originally Posted by HeeeHAWWWW View Post
    If you use rstudio, you can view a dataframe/matrix like an excel workbook:
    https://support.rstudio.com/hc/en-us...he-Data-Viewer
    HH

  32. #32
    MathNerd
    MathNerd's Avatar Become A Pro!
    Join Date: 10-21-17
    Posts: 4
    Betpoints: 36

    Quote Originally Posted by Bsims View Post
    Welcome to the forum. You look to be a good addition. Hopefully you will gain some benefits from here.
    Thanks BSims.

    I've actually benefited quite a bit out of the forum mostly lurking since the end of last year: Ganchrow's posts and Kelly Calculator have proven quite useful. Unfortunately, work became extremely busy during the first half of the year, so I haven't been online much until recently. I'm sure that I'll learn more about the various books and handicapping from the forum members, and hopefully, I'll be able to help some of you with math, simulations and R.

    Cheers.

  33. #33
    MathNerd
    MathNerd's Avatar Become A Pro!
    Join Date: 10-21-17
    Posts: 4
    Betpoints: 36

    Quote Originally Posted by oilcountry99 View Post
    MathNerd
    Thank you for the positive example of a monte carlo system. Solid information is hard to come by, all the best to you and your endevours moving forward.
    OC99 - Thanks for the kind words. Best of luck to you with the Monte Carlo sim.

  34. #34
    PeterGans
    PeterGans's Avatar Become A Pro!
    Join Date: 07-24-18
    Posts: 6
    Betpoints: 24

    Uuuuuuuuh I will follow this thread closely. Monte Carlo can get slight edges over a time-adjusted full grown poisson model (talking soccer here). They are in the same ballpark, I have done the full poisson model (a very precise one) but back in the day I could not get Monte Carlo to work with Mathematica.

    I had to close the project down because germany decided to screw gamblers over big time, and betfair deciding to not even touch the german market. And I had all my API downloads of odds already written for betfair. I was so angry.

    I still have the files, so maybe if I can get another API running (I hope asianodds88 can do that) I might actually reactivate my betting bot. It was a beauty and worked really well for the time betfair was open to germans.

    In 2 months I am finishing university, so... I hope this will roll nicely. Keep us all updated on your simulations!

  35. #35
    swordlord
    swordlord's Avatar Become A Pro!
    Join Date: 12-05-18
    Posts: 5
    Betpoints: 54

    I can help with that

    Quote Originally Posted by oilcountry99 View Post
    I'm interested in using Monte Carlo simulation to predict daily game outcomes (NHL). Ideally what I'd like is a sample of how to input/format the data in excel and run a simulation 'x' amount of times to get a win probability. I really have no clue where to begin, there is information online regarding these simulations but I would like something directed on single event sport outcomes. An excel template, or step by step would be ideal. I know I'm asking a lot but these things must exist. I'm no math guru, that's why step by step in laymans terms is ideal. Any help is greatly appreciated, thanks in advance.
    Hello, i have done simulating models for predicting icehockey, football etc based on monte carlo in excel. Its pretty simple procedure. with this you can calculate 1X2, under/over, Spread etc.
    Are you still interested? Just let me know.

    First you need to calculate mean and standard deviation for both teams.
    Then use box-muller method https://www.statisticshowto.datascie...le-definition/ for generate randomized goals. Remove all negative numbers (if occurs) because goals are positive integers. I usually generate 10000 numbers.
    Now you can do your calculations in spreadsheet. Transform your numbers first into integers. Positive Goal-difference means home win and negative goal-difference means guest win. you can also delete all goal values from both teams over 7 because they are so rare.
    Last edited by swordlord; 12-05-18 at 05:47 AM.

12 Last
Top