Ask The Doc: Monte Carlo simulations

theLAW

Hi Doc, I would like to learn about Monte Carlo Simulations, can you help me?

Doc

Sure I can, or at least I can try. Let's start by seeing what you know - how did you hear the term?

theLAW

I know absolutely jack about them; I heard about them on the forums at Punters Paradise.

Doc

Let me turn the question around a little - in what way would you hope they are relevant to you?

theLAW

I believe they are used in sports betting and would like to enter in to that sphere fully equipped to tackle such markets.

Doc

I see. Well, the basic premise of Monte Carlo simulations is as follows:

You use random numbers to simulate some physical situation, and then look at the results to help you predict what will happen in the particular situation that interests you. Does that make any sense?

theLAW

It sounds complicated - how are these random numbers generated?

Doc

Hmmmm.....that is a whole topic in itself, but usually a standard random number generator on your computer suffices - e.g. if you type "= Rand()" in a cell in an Excel worksheet, it will return a random number between 0 and 1

theLAW

Still sounds complicated!

Doc

An example of the method might help, do you think?

theLAW

I believe it would.

Doc

Ok, well imagine we have a game that works as follows:

We both roll a dice. If the total on the two dice is greater than 8 I pay you £5, if it is less than 7, you pay me £4, otherwise we each keep our money. You want to know if this is a fair game, or if I am fleecing you!

theLAW

Gotcha so far.

Doc

What would your feeling be - am I giving you a fair deal?

theLAW

My gut reaction is that I have only 4 outcomes, while you have 6; that is unfair.

Doc

Ok - you may well be correct, I didn't think about this game at all before inventing it just now!
What you have done is to use your well honed sense of 'fairness' to decide whether or not you should play; what we might call 'intuitive probability'. However, when/if the game gets more complicated it might not be so easy for you to do this - even if you had considerable expertise in probability and statistics.

theLAW

Right - so is this what the MCS does?

Doc

Not quite - what MCS does is to 'play' this game many times and record the answer. You look at how often you 'won' in the MCS and use that to decide whether it is a fair game or not.

theLAW

Gotcha! Sounds useful.

Doc

Would you like me to show you how to do a MCS of this game in Excel?

theLAW

Yes please - I'm very interested in how this works.

Doc

Ok, well open up Excel and follow my instructions. We're going to use some standard excel functions, nothing too fancy- should take you no more than 10-15 minutes in total.

theLAW

Right Excel open.

Doc

Let me explain very briefly the functions we are going to use. Firstly there is the “ RAND()” function. That will return a random number between 0 and 1.

theLAW

But we want numbers between 1 and 6, not 0 and 1?

Doc

Correct, so we’re going to need another function; “INT()” which returns the integer part of what is in the bracket.

theLAW

What does that mean in English LOL?

Doc

Well if I type INT(7.3) it will return 7, if I type INT(6.9) it will return 6

theLAW

OK, so we use the two of these together to get our numbers?

Doc

Exactly, to be specific we can type “=INT(6* RAND()+1)” in a particular cell. Try it yourself – click on cell A1 and type (or copy and paste) the above and press enter.

theLAW

Why is there a ‘plus one’ in the equation Doc?

Doc

Well, “ RAND()” returns a random number between 0 and 1, so if multiply that by 6 we get a random number between 0 and 6. However the “ RAND()” function will (almost) never actually return of value of 1, so that when we scale it (i.e. multiply by 6), the answer will always be less than 6. When we truncate the answer to the nearest integer (what the “INT()” function does), we will return an integer between 0 and 5 inclusive. We add the 1 to get an integer between 1 and 6 inclusive.

theLAW

OK. I understand that now. I’ve just pasted that formula into cell A1 and it gives me an answer of 3. Is that right?

Doc

I hope so! Try repeating the operation – i.e. pasting the same contents into the same cell and pressing enter again. What happens?

theLAW

I get 5 now!

Doc

You see what the cell is doing?

theLAW

I presume it is supposed to be like the dice?

Doc

Exactly – we are using the functions above to simulate the throwing of a dice. If we really threw a dice, any one of 6 numbers could come up at random. We are using the formulas to generate a random number corresponding to a throw of the dice.

theLAW

Sweet! So if I want to examine what would happen I just keep cutting and pasting and I’ll get a new answer each time?

Doc

Well you could, but that is very laborious. A much quicker way is to press “F9” on your keyboard (on my keyboard it is just above the number keys, between “0” and “-”. Make sure your “F Lock” key is not pressed!

theLAW

Ah right! Every time I press it I get a new value in that cell.

Doc

Correct. F9 is the keyboard shortcut which tells Excel to recalculate all formulas – so in this case it generates new random numbers in the cell – as it would for any other cells containing similar formulae.

theLAW

So I could have a whole sheet full of random numbers, each cell corresponding to a dice?

Doc

If you like, yes – but we’ll look at some more efficient ways to do this shortly. Let’s start slowly for the moment!

theLAW

OK – what next?

Doc

Well, in our game we each roll a dice, so we need another cell to represent what happens when the other dice is rolled. We’ll use cell B1 for this. Copy and paste the same formula to cell B1. A quick way to do this is to click on cell A1, press Ctrl + C, then click cell B1 and press Ctrl + V.

theLAW

Ok done. The value in A1 has just changed again though!

Doc

That’s fine – any time you edit any cell, the sheet will recalculate all formulas.

theLAW

Cool, so now I have A1 representing my dice and B1 representing yours – is that right?

Doc

Correct.

theLAW

So I can just press F9 and keep track of the outcomes?

Doc

You could, but that would not be very efficient. We’ll look at processing the information in the next instalment.