perraleta1981.netlify.com

Inspired by the work of Jack Dahlgren on Free Monte Carlo Simulator for Microsoft Project and with a help of insightfull posts from Glen Alleman on Herding Cats as well as a lot of Googling around I started an open source project Monte Carlo Simulation for MS Project on SourceForge.net.

• Blackjack Excel Vba Tutorial
• Blackjack Excel Vba Cheat
• Excel Vba Functions
• Excel Vba Autofilter

The purpose of the project is to explore the possibilities of Monte Carlo simulation in the area of project management.

Blackjack is a comparing card game between a player and dealer and played with one or more French decks of 52 cards. The player is dealt an initial two card hand with the option of drawing cards to bring the total value to 21 or less without exceeding it, so that the dealer will lose by having a lesser hand than the player or by exceeding 21. Excel Vba Blackjack, casino del este ciudad del este paraguai, colette stewart poker, indian casino ridgefield wa. 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. I've been working on a blackjack simulation program using excel VBA. You can download this file here. Here is a screen shot from the main sheet: With this program, you can alter your stategy, run the simulation on as many hands as you want (say 5000), the program will keep a running total of how you did.

It is not the purpose of the project to compete with professional quantitative risk assesment tools like @Risk (by Palisade), Risk+ (by Deltek) or Crystall Ball (by Oracle, formerly by Decisioneering).

What can Monte Carlo Simulation for MS Project (MCS) do for you?

There are many risks on projects and one of the obvious is that tasks will take more or less time than planned. As theory goes, duration/work on tasks is a random variable with certain probability distribution.

As there are extremely rare cases where we can actually measure cumulative distribution function, which would completely describe probability distribution and even more rare cases where we simply know which probability distribution is the right one and what are its parameters, we have to assume certain distribution. Since triangular distribution looks just as good as any other skewed distribution (beta, betapert, lognormal) from far enough distance and due to practical and performance reasons I started with triangular distribution, which is defined with minimum, maximum and most likely (mode) values.

Since it is very unrealistic to actually define a minimum and a maximum value, MCS offers you an option to enter which percentiles are you estimating. I suggest to use 10th and 90th percentile, which means that 9 out of 10 tasks will complete inside min-max range and one will complete outside of its min-max range, which is much more realistic than saying all my tasks will complete inside estimated range.

MCS uses Duration1 and Duration3 fields to store minimum and maximum estimates plus Duration or Work for most likely estimate. If task type is set to Fixed Duration, then Work field is used as most likely value and during simulation run, Work field is substituted with a random value. If task type is set to a value, other than Fixed Duration, then Duration field is used as most likley value and during simulation run, Work field is substituted with a random value.

There may be other sources of variance on the project, but currently, they’re not taken into account during simulation. Such sources of variance might be:

• resource availability
• resource rates (for cost simulations)
• lag times
• time constraints
• etc.

MCS works by going through all non summary tasks and assigning random triangularly distributed value to Work or Duration, depending on its Type and taking Duration1 and Duration3 as input parameters for triangular distribution. At the end of each run, project is recalculated and these tasks variables are stored for all project’s tasks:

• finish time
• duration
• work
• cost

Same variables are stored for the entire project as well.

At the end of the simulation, various reports are produced in Microsoft Excel:

• sensitivity analysis for duration, work and cost, based on Spearman’s rank correlation between tasks value and project’s value,
• criticality analysis based on calculating the percentage of runs where certain task was on a critical path,
• project finish, duration, work and cost distribution charts with relevant descriptive statistics and calculated percentiles.
• milestones finish dates distribution charts with relevant descriptive statistics and calculated percentiles.
• resources workload distribution charts with relevant descriptive statistics and calculated percentiles.

Installation

• Download the package from SourceForge,
• Unzip it to some folder.
• Run Microsoft Project.
• Press Alt-F11 to run Visual Basic Editor.
• Import all .bas and .cls files from the package into Global.MPT project, so that the application is available to all projects. Warning: Some files in ALGLIB libraries are duplicates. Import them only once!
• Select Tools and References and check Microsoft Excel Object Library.

• From Microsoft Project select Tools, Macros and run MCS macro.
• Type in the number of iterations to perform. You might want to experiment with 100 and going beyond 1000 doesn’t make much sense.
• Type in percentile at which you’ve estimated lower and upper bounds. 0,1 means 10th and 90th percentile.
• Kaboom.

By Cleve Moler, MathWorks

Blackjack Excel Vba Tutorial

Blackjack is the most popular casino game in the world. Using a basic strategy, a knowledgeable player can reduce the casino's advantage to less than one-half of one percent. Simulating blackjack play with this strategy in MATLAB^® is both an instructive programming exercise and a useful parallel computing benchmark.

Blackjack is also known as '21.' The object is to get a hand with a value close to, but not more than, 21. Face cards are worth 10 points, aces are worth either 1 or 11, and all other cards are worth their numerical value. You play against the dealer. You each start with two cards. Your cards are dealt face up; one of the dealer's cards stays face down.

You signal 'hit' to receive additional cards. When you are satisfied with your hand, you 'stand.' The dealer then reveals the hidden card and finishes the hand. If your total ever exceeds 21, you are 'bust,' and the dealer wins the hand without drawing any more cards.

The dealer has no choices to make, and must draw on hands worth less than 17 and stand on hands worth 17 or more. (A variant has the dealer draw to a 'soft' 17, which is a hand with an ace counting 11.) If neither player goes bust, then the hand closest to 21 wins. Equal totals are a 'push,' and neither wins.

The fact that you can bust before the dealer takes any cards is a disadvantage that would be overwhelming were it not for three additional features of the game. On your first two cards:

• An ace and a face card or a 10 is a 'blackjack,' which pays 1.5 times the bet if the dealer does not also have 21
• You can 'split' a pair into two separate hands by doubling the bet
• You can 'double down' a good hand by doubling the bet and receiving just one more card

Basic strategy was first described in the 1956 paper 'The Optimum Strategy in Blackjack,' published in the Journal of the American Statistical Association by four authors from the Aberdeen Proving Ground. It is now presented, with a few variations, on dozens of web pages, including Wikipedia. The strategy assumes that you do not retain information from earlier hands. Your play depends only on your current hand and the dealer's up card. With basic strategy, the house advantage is only about one half of one percent of the original bet.

My MATLAB programs, shown in the sidebar, use three functions to implement basic strategy. The function hard uses the array HARD to guide the play of most hands. The row index into HARD is the current total score, and the column index is the value of the dealer's up card. The return value is 0, 1, or 2, indicating 'stand,' 'hit,' or 'double down.' The other two functions, soft and pair, play similar roles for hands containing an ace worth 11 and hands containing a pair.

The most important consideration in basic strategy is to avoid going bust when the dealer has a chance of going bust. In our functions, the subarray HARD(12:16,2:6) is nearly all zero. This represents the situation where both you and the dealer have bad hands—your total and the dealer's expected total are each less than 17. You are tempted to hit, but you might bust, so you stand. The dealer will have to hit, and might bust. This is your best defense against the house advantage. With naïve play, which ignores the dealer's up card, you would almost certainly hit a 12 or 13. But if the dealer is also showing a low card, stand on your low total and wait to see if the dealer goes over 21.

Card counting was introduced in 1962 in Beat the Dealer, a hugely popular book by Edward Thorp. If the deck is not reshuffled after every hand, you can keep track of, for example, the number of aces, face cards, and nonface cards that you have seen. As you approach the end of the deck, you may know that it is “ten rich”—the number of aces and face cards remaining is higher than would be expected in a freshly shuffled deck. This situation is to your advantage because you have a better than usual chance of getting a blackjack and the dealer has a better than usual chance of going bust. So you increase your bet and adjust your strategy.

Blackjack Excel Vba Cheat

Card counting gives you a mathematical advantage over the casino. Exactly how much of an advantage depends upon how much you are able to remember about the cards you have seen. Thorp's book was followed by a number of other books that simplified and popularized various systems. My personal interest in blackjack began with a 1973 book by John Archer. But I can attest that card counting is boring, error-prone, and not very lucrative. It is nowhere near as glamorous—or as dangerous—as the recent Hollywood film '21' portrays it. And many venues now have machines that continuously shuffle the cards after each hand, making card counting impossible.

My original MATLAB program, written several years ago, had a persistent array that is initialized with a random permutation of several copies of the vector 1:52. These integers represent both the values and the suits in a 52-card deck. The suit is irrelevant in the play, but is nice to have in the display. Cards are dealt from the end of the deck, and the deck is reshuffled when there are just a few cards left.

This function faithfully simulates a blackjack game with four decks dealt without reshuffling between hands. It would be possible to count cards, but this shuffler has two defects: It does not simulate a modern shuffling machine, and the persistent array prevents some kinds of parallelization.

My most recent simulated shuffler is much simpler. It creates an idealized mechanical shuffler that has an infinite number of perfectly mixed decks. It is not possible to count cards with this shuffler.

I have two blackjack programs, both available on MATLAB Central. One program offers an interface that lets you play one hand at a time. Basic strategy is highlighted, but you can make other choices. For example, Figures 1 and 2 show the play of an infrequent but lucrative hand. You bet $10 and are dealt a pair of 8s. The dealer's up card is a 4. Basic strategy recommends splitting the pair of 8s. This increases the bet to $20. The first hand is then dealt a 3 to add to the 8, giving 11. Basic strategy recommends always doubling down on 11. This increases the total bet to $30. The next card is a king, giving the first hand 21. The second hand is dealt a 5 to go with the 8, giving it 13. You might be tempted to hit the 13, but the dealer is showing a 4, so you stand. The dealer reveals a 10, and has to hit the 14. The final card is a jack, busting the dealer and giving you $30. This kind of hand is rare, but gratifying to play correctly.

Figure 1. Start of an atypical but important example: You are dealt a pair of 8s, and the dealer's up card is a 4. Basic strategy, highlighted in red, recommends splitting the pair.'>

Figure 1. Start of an atypical but important example: You are dealt a pair of 8s, and the dealer's up card is a 4. Basic strategy, highlighted in red, recommends splitting the pair.

Figure 2. The final outcome. After splitting, you double down on your first hand and stand on your second. The dealer goes bust, giving you a rare 3x win.'>

Figure 2. The final outcome. After splitting, you double down on your first hand and stand on your second. The dealer goes bust, giving you a rare 3x win.

My second program plays a large number of hands using basic strategy and collects statistics about the outcome.

Accelerating the Simulations with Parallel Computing

I like to demonstrate parallel computing with MATLAB by running several copies of my blackjack simulator simultaneously using Parallel Computing Toolbox™. Here is the main program:

The matlabpool command starts up many workers (copies of MATLAB) on the cores or processors available in a multicore machine or a cluster. These workers are also known as labs. The random number generators on each lab are initialized to produce statistically independent streams drawn from a single overall global stream. The main program on the master MATLAB creates an array B, and then the parfor loop runs a separate instance of the sequential simulator, blackjacksim, on each lab. The results are communicated to the master and stored in the columns of B. The master can then use B to produce the plot shown in Figure 3. With 'only' 25,000 hands for each player, the simulation is still too short to show the long-term trend. The computation time is about 11 seconds on my dual-core laptop. If I do not turn on the MATLAB pool, the computation uses only one core and takes almost 20 seconds.

Figure 3. Four players in a parallel simulation. Green wins, red nearly breaks even, cyan muddles through, and blue should have quit while he was ahead.'>

Figure 3. Four players in a parallel simulation. Green wins, red nearly breaks even, cyan muddles through, and blue should have quit while he was ahead.

This run can also produce the histograms shown in Figure 4. The cumulative return from the four players is the dot product of the two vectors annotating the horizontal axis. The discrepancy between the frequency of $10 wins and $10 losses is almost completely offset by the higher frequencies of the larger wins over the larger losses. The average return and a measure of its variation are shown in the title of the figure. We see that in runs of this length, the randomness of the shuffle still dominates. It would require longer runs with millions of hands to be sure that the expected return is slightly negative.

Figure 4. Histograms for each player. The $10 bet is a push 9% of the time, a $10 win 32%, and a $10 loss 42%. Blackjacks yield $15 wins 4.5% of the time. The less frequent $20, $30, and $40 swings come from doubling down, splitting pairs, and doubling after splitting.'>

Excel Vba Functions

Figure 4. Histograms for each player. The $10 bet is a push 9% of the time, a $10 win 32%, and a $10 loss 42%. Blackjacks yield $15 wins 4.5% of the time. The less frequent $20, $30, and $40 swings come from doubling down, splitting pairs, and doubling after splitting.

Blackjack can be a surrogate for more sophisticated financial instruments. The first graph in Figure 5 shows the performance of the Standard & Poor's stock market index during 2011. The second shows the performance of our blackjack simulation playing 100 hands a day for each of the 252 days the stock market was open that year. The S&P dropped 14.27 points. Our blackjack simulation, which bet $10 per hand, lost $3860 over the same period. More important than these final results is the fact that both instruments show large fluctuations about their mean behavior. Over the short term, stock market daily behavior and card shuffling luck are more influential than any long-term trend.

Figure 5. The Standard & Poor's stock market index over one year versus 100 hands of blackjack per day for the same time. Short-term random fluctuations dominate any long-term trend.'>

Figure 5. The Standard & Poor's stock market index over one year versus 100 hands of blackjack per day for the same time. Short-term random fluctuations dominate any long-term trend.

Published 2012 - 92052v00

References

• Baldwin, R. R., Cantey, W. E., Maisel, H., and McDermot, J. P. “The Optimum Strategy in Blackjack,” Journal of the American Statistical Association, 51:429-439 (Sept.), 1956.
• Thorp, E. O. Beat the Dealer. New York: Random House, 1962.

• Wikipedia. https://en.wikipedia.org/wiki/Blackjack

Excel Vba Autofilter