Excel Blackjack Simulator Program Guide
The Excel Blackjack simulator program simulates the play of blackjack for up to thousands of blackjack hands. It consists of several Excel sheets, and a macro that runs a series of functions. These guidelines are designed for anyone somewhat familiar with Microsoft Excel. To run the program, no programming experience is necessary, though the source code can be altered to tailor the program to specific blackjack variations. General Blackjack knowledge is recommended to understand the basic concepts.
Running the Simulator
To simply run the macro, from the top menu, select: tools, macro, macros. Then run the macro named ‘macro1’. The program should start flipping through various sheets while running. It should stop on sheet1 when completed (Unless the chart Generator cell has the value TRUE). While the program is running, do not click any cell or click to another sheet, as this potentially could cause an error in the program.
Viewing Results After Running Simulator
After running the Macro, you can view the results by clicking on Sheet3, on the bottom tab. The results will be listed in column A, starting with your initial dollar amount, and ending with your final dollar amount, after playing all your hands. You can create a graph of these results by selecting the entire‘column A’ (on Sheet3), then clicking on insert, chart, in the top menu. Select the ‘line’ chart (third from the top) and click through, hitting the ‘next’ button, and finally the ‘finish’ button until your chart is displayed. A chart can automatically be generated also, by setting the ‘Chart Generated’ field (Cell I:2, Sheet1) to TRUE. It is recommended to delete both the chart and data from the previous iteration before you run the program.
Editing and Viewing the Code
To see the programming behind this from the top menu select: tools, macro, visual basic editor, and then choose macro1. Looking at the code, all the macro1 procedure does is call the ‘playgame’ procedure, from which all the other functions are called. It is recommended that you make changes to this code only if you have prior programming experience, as changes to this code can determine whether the program works or not.
Sheet1 – Settings Sheet
The following is a picture of Sheet1:
`
This is the sheet used to set the settings for play, as well as places (cells) the program uses to store temporary values. Some of these should not be tampered with as they may cause the program not to work. The only cells that you should change are listed below.
Cell H:2 – Total Cash
This is the player’s total cash before starting play. After running the program, this value will change, as the total cash the player has will depend on whether he wins or loses.
Cell H:4 – Bet Value
This cell stores the amount of money a player initially places to make a bet, on each hand. This value will double if the player’s option is to double or split the cards. This value will always be the same every hand played, but eventually I plan to add functionality the change this.
Cell I:2 – Chart Generator
If this optionis set to TRUE, after running, the program it will automatically generate a chart, on Sheet3. The program will end with Sheet3 displayed.
Cell J:2 – Logging
This determines whether logging is on or off. The log is saved on sheet 4, column A. Logging will be on if this value is set to True. Logging will be off if this value is set to False. All other values will set logging to False. See Sheet4 – Logging Sheet for more details.
Cell J:4 Log Index
This cell stores the location of where the program should start logging, if logging is set to True. To start a new log it is recommended to click on Sheet4, clearing out the log (select every cell with a value and hitting delete), and then setting the log index to 1.
Cell K:2 Stats
With this value set to TRUE, after running the program, several statistics about the previously play will be listed on Sheet6, such as the number of hands played, the number of cards dealt to the player and number of card dealt to the dealer.
Cell G:2 # Decks
This determines the number of decks you want to play with. The more decks you play in each shoe determines the length of time to get through each shoe. Also, the more decks in the shoe, the less your odds are against the dealer. This function has currently not been implemented.
Cell G:4 - # Shoes
This determines the number of shoes you will play. The greater this number, the longer the program will take to run, but it will also be more representative of how you will do in the long run.
Cells A-D: 2-212
Not recommended to change these without a functional knowledge of how the program works, but if you wanted to see how the game would play if you had more high cards (ten, face cards and aces) in the deck than usual, or low cards (2-6), you can make changes to these fields. Once again, changing these fields is only recommended if you know what you are doing.
The other areas highlighted in color are working areas used internally by the program. Changing these values may cause the program not to work.
Sheet2 – Strategy Sheet
The following is a picture of Sheet2:
This is the strategy sheet. You can use this to set the strategy you want to play. On the left are listed your hand total, on the top is listed the dealers up card. The letter in the box corresponding to the player’s total (row) and dealer’s card (column) indicates what play you would do for that situation. The following lists what each letter stands for:
H= Hit (red)
S=Stand (green)
P=Split (purple)
D=Double (blue)
The rows 3-26 are the player totals, assuming the player does not have a soft ace or can split two cards. Rows 28-36 are playing strategies used when the player has a soft ace. Rows 37-46 are the strategies used when the player has two of the same cards and has the option to split his cards.
To alter the strategy, you can simply type the letter corresponding of the play you want. You do not need to change the color of the play in the box, but if you want to change the color of the cell, select ‘Format’, ‘Cells’ from the top menu. Select the Patterns tab, select the color you want, and click the ‘ok’ button. The current sheet is set-up for the basic strategy, which is the optimal strategy to win the game.
One thing to note, certain changes to this strategy may cause the program not to work. For example, if the players total is greater than 21, this strategy advises the player to stand, even though the player technically already busted. This was done to meet the needs of the program I had. I might decide to change this in the future. Generally, if you have a hard hand of 17 or over, it is unwise to hit.
Sheet3 – Results Sheet
The following is a picture of Sheet3:
As previously stated in ‘Viewing Results After Running Simulator’, Sheet3 is the results sheet. After running the program, you can view the results and generate a graph from these results from this sheet. Before running the program, this sheet should be blank.
Depicted in this picture, is a graph of results after playing 100 shoes of blackjack, starting with $1000, always placing an initial bet of $10. The raw data is listed in column A of this sheet.
If cell I:2 on Sheet1 is set to TRUE, the program will end on this sheet with a graph already displayed.
Sheet4 – Logging Sheet
The following is a picture of Sheet4:
Sheet4 is the logging sheet. This is only functional if logging (Sheet1, Cell J:2) is set to TRUE.
This sheet displays the player’s total cash after each hand, each time the dealer shuffles, the player’s cards, the dealer’s upcard, dealer’s downcard, the player’s move, the dealer’s move, and the results of each hand played.
This I used to test to make sure the program was working correctly. To turn logging off, type FALSE in the logging cell in sheet1.
Sheet5 – Card Counting Deviations
This is still under work. This will take some time.
Sheet6 – Statistics
The following is a picture of Sheet6:
This sheet is only functional if the ‘Stats’ field(cell K:2 on Sheet1) has the value TRUE. This sheet displays the total cards the player is dealt, the total cards the dealer is dealt, the total hands played, the average amount of cards per hand for both the player and the dealer, the total number of blackjacks, and the percentage of blackjacks per hand.
I am currently adding functionality to the program, as this sheet may look different from what is depicted in this picture.