Monte Carlo Simulation and Excel

Betting quant, can you explain how that works?

I think this is definitely a VBA task. I'll get this done within the next month or so.

I've used the following very crude method. Let's say that in cells A1 to A100 you have 100 random results. You want to generate and store/compare these results 10,000 times. If you have used the rand() functions the value changes every time you change something.


1) Record a macro with shortcut key ('g' for example)
2) copy column A
3) In column B do paste special -> values
4) Insert a column between A and C
5) stop recording

start recording a new macro

Press CTRL+g and keep them pressed. Stop recording.

Assign a shortcut key. now you can quickly execute 1000s of simulation outcomes. It's still going to be woefully slow compared to doing this properly, but better than nothing.

I actually did this (being of limited programming etc ability) by using the sheet as normal. It's kludgy, but so is everything I do.

Put your variables into columns/tables as usual, then have a series of columns for random numbers. Calculate one case and return the result in a final column.

Copy/Paste this for 10000 lines, prepare for your computer to cry for a minute or two and then hit refresh :) (There's also an option that stops it refreshing every time you change one parameter, can't remember where that is offhand, but it's better to set to manual refresh or it gets a bit silly)

After doing that, use the likes of countif to tabulate your results.

i know that Monte Carlo has been used for a long time in 3D rendering to create realistic lighting and it was one of the most advanced algorithms

I do, i think, the same as Lori. But I find I need more than 10,000 lines to get consistent results.

Does anyone know if the Random number generator in Excel is actually truly 100% random? For some reason I have my doubts.

the silverback

I've noticed that in my Excel version (2003) a bug sometimes manifests itself with RAND(),  it works most of the time, but every now and again it starts generating numbers on the low side, which a reboot of Excel fixes.

I know this for certain because of my column of say 10,000 random numbers, the average should always be very close to 0.5.  When the bug appears, the average drops significantly, sometimes well below 0.4 and remains there for every iteration until a restart of Excel.

Frog ~ good to see you back on an even keel (hope you like the nautical reference).

with reference to your question, the way to think about it is the outcome of say a football match is random weighted by the starting odds. i can go into more detail if you wish.

FINE AS FROG HAIR Joined: 12 Mar 07
Replies: 1146 02 Nov 10 16:41 
I can see how using such simulations on events with pure odds makes sense, eg roulette or X games on here, but in sports betting ? Random ?


Here are a couple of examples of how this could be useful.

Example 1:

Take your daily PnL figures, let's say there are 300. Assign each one a probability of 1/300. Then run simulations of what future returns could look like based on similar results. If your returns are growing along with your balance, you could take daily percentage gains instead of absolute returns. You could also adjust risk by increasing the value of your largest losses, and play around with it in other ways. It's not going to be a precise tool, but it will be better than nothing.

Doing this could show the falsehood of the assumption many people have 'luck is not important in the long term for a successful gambler'. For some successful gamblers luck will be more important than others (this depends on volatility of returns). If over a lifetime you have 4 massive losses, your overall returns will be significantly worse than if you only have 3 massive losses, and this could be purely down to luck.

Example 2:

Take a large series of bets (let's say 10,000) that have been matched and settled. Using your PnL on these bets (ignoring commission and other charges) you can work out expected return (based on the assumption odds are fair) versus actual returns. This gives you your average edge per bet.
You can then work out the range in which your returns could have fallen with confidence intervals etc. You need to factor in (negative) correlations, this is where it gets much more complicated.

Sounds interesting.
I think I'll pass.
I'm more of the type who concentrates on what did happen, rather than what didn't happen.
Too many alternative theoretical " what if " scenarios generally create more confusion rather than clarity. Raise more doubt than confidence. Make for more missed opportunities than gained ones.
But this is personal, I understand that perfectly.

FAFH, you could do a test that tells you if your edge was 0%, what are the chances of generating these returns. This chance can never be 0 (although it can approach 0 over a long timeframe).

As far as I know this is the closest you can get to proving whether what you are doing is actually working based on the numbers or whether you are just lucky.

agree 100% FAFH

the bigger the sample the less luck becomes a major factor, but these simulations can give you exactly that

Investor
All the great mathematical minds in the past, before the computer, didn't need to do endless simulations to prove that their maths was sound.
Not arguing here, just saying that it is not mandatory, but I most certainly agree it can't do any harm as long as you don't let it raise doubts, where there shouldn't be any.
In my particular case, ignorance is best I'm sure my detractors will say.

And ALL the mathematical minds in the past combined could not work out that blackjack could be favourable to the player and create a strategy to exploit this before computers.

The Investor Joined: 05 Jun 06
Replies: 3078 02 Nov 10 21:53   


And ALL the mathematical minds in the past combined could not work out that blackjack could be favourable to the player and create a strategy to exploit this before computers.

exactly investors, because BJ cant be solved explicitly.

but say you had a biased coin and the odds of getting head was 0.6, you dont need simulations to tell you that a) you have an edge, b) what that edge is c) variance of that edge

If you had a biased coin where the chance of heads was 0.505, you could be behind after 10,000 tosses quite easily.

Doing a simulation will show you the chance of a certain outcome, and the probability of it being due to luck etc. You don't 'need' to do simulations, you can just work out the math, but simulation makes it easier to alter key variables and quickly see how it affects results. It's much more intuitive I find.


If I have a coin, and I assume the chance of heads is 0.505, how many tosses do I have to do before I have proved whether this is correct? My view on this is that you simply can't prove it. You can just 'approach' certainty as you go from 10,000 to 100,000 to 1,000,000 to the point where it would be ridiculous to assume otherwise. You have to factor in that you'll probably never get the chance to place a million bets. If you can place various bets with returns that are negatively correlated you can smooth out returns, and proceed with a much higher degree of certainty.

betting_quant Joined: 08 Jun 10
Replies: 95 02 Nov 10 21:57 
The Investor Joined: 05 Jun 06
Replies: 3078 02 Nov 10 21:53   


And ALL the mathematical minds in the past combined could not work out that blackjack could be favourable to the player and create a strategy to exploit this before computers.

exactly investors, because BJ cant be solved explicitly.

but say you had a biased coin and the odds of getting head was 0.6, you dont need simulations to tell you that a) you have an edge, b) what that edge is c) variance of that edge

How do you know the chance of getting heads is 0.6 though? In the real world playing with a real coin this would be an imperfect approximation.
Monte Carlo Simulation would help you analyse scenario's, how will it affect my returns if the bias lower or higher etc.

And thanks for the formulas betting quant.

Isn't the biased coin a bad example. You know you have an edge there, so there is no need to do simulations. Simple maths shows you will make money over time. Similarly , if you know for certain that you have a betting edge in sports you don't need to run simulations. However, for mere mortals like me who only THINK we have an edge, then we can run simulations to see what confidence we can have in the perceived edge. I would be amazed if anyone has a mathematical formulation which they know for certain gives them an edge.

My God, it did.

I agree with a lot you are saying.
But I factor the whole element of good luck out, or if you prefer the other way, I factor what I think will be the worst case bad luck into everything I do.