Monte Carlo Simulations tool

Semi-Technical discussion ahead.

A few people that have seen the game have suggested I try to simulate lots of play tests to make sure nothing breaks in the game. I'd heard about Monte Carlo simulations, but did not know how to make one. Searching online for examples didn't show me much in the way of examples either. So, I decided to brute force it using Excel and macros. I think it turned out pretty well, and wanted to share it with everyone in case others can use it too. Below is a description of how the tool works:

1st, a quick description of the game I'm modeling. Abstractly, it's a card shedding game, like Crazy 8's, or Uno. A game ends when one player plays the last card in their hand. 

• Cards can be identified by a color (Coded SF, R, H, M as the first character(s) in the description) or a suite (Coded S, C, M, A as the next characters in the description), and finally a number. An example of one of my cards is coded SFC3, or RM2. There are also starting cards which have the letter B in them.
• For each color, there are five cards of each suite, giving me a total of 80 cards. I also have 5 cards of each color that are used to claim a trick, and kick off the next trick, which brings me to 100 cards. My deck has other cards, but for the initial model, these are the ones I've used. 
• Rule 1 - When playing a card, you can play any other card of the same color.
• Rule 2 - Each card indicates it's suite, but also a different suite that you can play that will change the color in use.
• Rule 3 - A closing/ending card can only be played when at least one of each suite has been played, regardless of color.
• Rule 4 - If a player cannot play a card, they keep drawing cards until they find one that can be played.

So, with that explained, on to spreadsheet!

We've got 8 tabs, and we'll go through them one by one. 

Game parameters is first, and by far the simplest. This game is able to be played by 2-8 players. So, with each run, it randomly chooses how many players for this game. It also randomly chooses who goes first. 

The third parameter is the number of games I want to simulate. That's currently the only field that needs user input!

The Go button starts the simulation, but we'll skip that for now and move on to the next tab. 

Card playing has the entire deck on the top row. Below each card is every possible card that could be played after it. For regular cards, that's 34 each. For starting cards, that's only 20. 

Player hands is our first dynamic table. It tracks what cards players are holding for any given round. 

At the start of  around, each player is given 7 cards. This shows that, by the end of this round, Player 1 is the person who ended the game, and player 3 didn't have the best luck with their cards. 

Card points is another static table that lists the deck and how many points any given card is worth. This is useful for looking at points spread, and tweaking balance. 

Deck Draw is the card shuffler and dealer table. It has the entire deck, and next to each card is a random number generator. At the start of a round, a random number is generated next to each card, and then is sorted in order to effectively randomize the order of cards, shuffling the deck. There is a number called index that is used to track how many cards have been dealt. It starts as the top cell, and works its way down. If we run out of cards, a flag is thrown to end the game.

Calculations is, by far, the busiest table. Each turn is calculated here. Column B shows the card at the top of the stack. Column shows the result of comparing the column that lists the cards in a players hand to the column that lists all eligible cards that can be played against the card in Column B. Out of those eligible cards, a random card is chosen. That card and it's point value is moved to the bottom of Columns H and I respectively, filling out the trick. 

Columns E and F show the suites that have been played. At the beginning of a players turn, it checks if there is at least one of each suite already played, which triggers the option to play a card to claim the trick (One of the 'B' cards).

If a regular card is played, it updates the suite count at the end of the the turn. 

If a card to claim the trick is played, the total score is added to the score of the player who claimed the trick, and the suite count and cards played columns are reset. 

I also count how many cards were played, increment the number of tricks (I call them stories) by 1.

That's it for tracking the single game. The last two tabs are for running lots of games in a row. 

Table takes the values for scores, cards played, stories told, and if the game ran out of cards at the end of a round, and stores those values. 

The final tab is summary, where one can generate charts to visualize the data. At the moment, I only have a chart showing me how many stories are told vs. the number of people playing. 

One thing I've learned is that for two to three players, the spread of possible stories told is quite large. I'm considering capping the number of stores (tricks) that can be claimed before the game ends based on this information. I also need to check to see how often I'm running out of cards, to see if more cards need to be added. 

I think this can be extended/overloaded for a variety of game types. Deckbuilder? The deck deal table can be split into a pool of cards and cards dealt to each player. The playable cards table does not need to be static, nor does the points table. Any of them can be modified to suit the rules of the game in question. 

For this game, simulating a round takes between 20 - 80 seconds. For 200 rounds of games, I needed just over 3 hours. I set it to run and went to bed. 

The code for the macros is commented, so I'm not going to discuss that here, but can make a second post if people want me to talk about it. Before this, I'd not done anything in excel vba, so I've been googling stack overflow and a ton of different tutorial sites to accomplish what I needed. In general, it's pretty straight forward. 

There's more to the tool I'm planning to add. Currently, there are no player strategies, and I've not incorporated the logic for wild cards, but those are more specific to my game, and I thought this more basic state would be more useful for others to modify to suit their needs. 

I cannot attach a file here, so I'll make it available in the discord group. You can also reach out to me at @goldentickga1

Hope this is helpful, and happy developing!