[oilcountry99]

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.

[HeeeHAWWWW]

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

[oilcountry99]

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.

[gdon44]

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.

[oilcountry99]

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

[gdon44]

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.

[oilcountry99]

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.

[SBR Drew]

How did this go?

[MadTiger]

---

[oilcountry99]

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

[Waterstpub87]

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.

[Miz]

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.

[oilcountry99]

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.

[Miz]

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

[oilcountry99]

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

you are wrong

[Waterstpub87]

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.

[gdon44]

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).

[Bsims]

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.

[oilcountry99]

What did you guys mean by ‘path 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’m branching out here with Monte Carlo, looking for tools to put in the toolbox. This is a hobby for me and I’m by no means a mathematician.

Bsims....check inbox

[Waterstpub87]

What did you guys mean by ‘path 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’m branching out here with Monte Carlo, looking for tools to put in the toolbox. This is a hobby for me and I’m 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.

[oilcountry99]

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

[oilcountry99]

I found a good basic tutorial used for the NBA

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

[gdon44]

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.

[oilcountry99]

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.

[vampire assassin]

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?

[oilcountry99]

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

[MathNerd]

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.

[Bsims]

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.

[oilcountry99]

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.

[HeeeHAWWWW]

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

[oilcountry99]

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

[MathNerd]

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.

[MathNerd]

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.

[PeterGans]

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!

[swordlord]

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.