Excel VBA Monte Carlo Simulation Stock Prices Generator

Banking, Finance and Investment Industry

VBA / Software Developer: Francis Lim

Project Categories: Data-Mining,  Data Cleansing, Financial Modelling, Numerical Analysis

Freeware: Excel VBA Monte Carlo Stock Prices Generator

System Requirements

Windows XP, Windows Vista, Windows 7

Microsoft Excel 2003 or above

This Excel Spreadsheet using Monte Carlo method to generate stock prices for the use of empirical studies and simulation activities. A freeware Spreadsheet. It is written in Visual Basic Applications (VBA), a macro programming language for Microsoft Office - Access, Excel, Word, FrontPage, Outlook, PowerPoint, and Visio.

How to Use Monte Carlo Stock Prices Generator Excel Sheet

You will need to enable macros for this spreadsheet to work. To enable macros, go to Excel Menu - Tools / Options, click on the Security tab and go to Macro Security. Change the setting to Medium, close and reopen the workbook It will ask you if you want to enable Macros, click 'Yes' to enable it.

User can input the following cells in the Monte Carlo Simulation Excel Sheet:-

B2, C2, and D2 Cell Row - Volatility

Input the Volatility in percentage.

B3, C3, and D3 Cell Row - Growth

Input the Growth in percentage.

B4, C4, and D4 Cell Row - Initial Simulated Price

Input the Initial Simulated Price.

Just Press "Run Monte Carlo Simulation" Button, it will generate up to 3 companies stock prices data.

What is Monte Carlo Simulation?

A technique for estimating the solution, x, of a numerical mathematical problem by means of an artificial sampling experiment. The estimate is usually given as the average value, in a sample, of some statistic whose mathematical expectation is equal to x. In many of the useful applications, the mathematical problem itself arises in a problem of probability in physics or other sciences, operational research, image analysis, general statistics, mathematical economics, or econometrics. The importance of the method arises primarily from the need to solve problems for which other methods are more expensive or impracticable, and from the increased importance of all numerical methods because of the development of the electronic digital computer.

The main advantage of Monte Carlo is that other methods can be more costly or impracticable. A familiar example is the estimation of the probability of winning a game of pure chance: Sometimes the only reasonably simple method of estimation is to play the game several times. There are also numerical problems that can be solved by deterministic methods but can be more simply solved approximately by the Monte Carlo method. Sometimes poor approximations are satisfactory because the aim is merely to determine the strategic variables of a problem. This is likely to be a fruitful technique in mathematical economics.

Another situation where a poor approximation is satisfactory occurs when there is available an iterative method of calculation, that is, a method of successive approximation, which converges closely to the right answer in a reasonable time provided that the first trial solution is not too far from the truth. The Monte Carlo method may then perhaps be used for obtaining a first trial solution. Modern Monte Carlo techniques are themselves usually iterative.

Applications

As mentioned, Monte Carlo simulation methods are especially useful for modeling phenomena with significant uncertainty in inputs and in studying systems with a large number of coupled degrees of freedom. Specific areas of application include:

Physical sciences

Monte Carlo methods are very important in computational physics, physical chemistry, and related applied fields, and have diverse applications from complicated quantum chromodynamics calculations to designing heat shields and aerodynamic forms. The Monte Carlo method is widely used in statistical physics, particularly Monte Carlo molecular modeling as an alternative for computational molecular dynamics as well as to compute statistical field theories of simple particle and polymer models ; see Monte Carlo method in statistical physics. In experimental particle physics, these methods are used for designing detectors, understanding their behavior and comparing experimental data to theory, or on vastly large scale of the galaxy modelling.

Monte Carlo methods are also used in the ensemble models that form the basis of modern weather forecasting operations.

Design and visuals

Monte Carlo methods have also proven efficient in solving coupled integral differential equations of radiation fields and energy transport, and thus these methods have been used in global illumination computations which produce photorealistic images of virtual 3D models, with applications in video games, architecture, design, computer generated films, and cinematic special effects.

Finance and business

Monte Carlo methods in finance are often used to calculate the value of companies, to evaluate investments in projects at a business unit or corporate level, or to evaluate financial derivatives. Monte Carlo methods used in these cases allow the construction of stochastic or probabilistic financial models as opposed to the traditional static and deterministic models, thereby enhancing the treatment of uncertainty in the calculation. For use in the insurance industry, see stochastic modelling.

Telecommunications

When planning a wireless network, design must be proved to work for a wide variety of scenarios that depend mainly on the number of users, their locations and the services they want to use. Monte Carlo methods are typically used to generate these users and their states. The network performance is then evaluated and, if results are not satisfactory, the network design goes through an optimization process.

Games

Monte Carlo methods have recently been applied in game playing related artificial intelligence theory. Most notably the game of Go has seen remarkably successful Monte Carlo algorithm based computer players. One of the main problems that this approach has in game playing is that it sometimes misses an isolated, very good move. These approaches are often strong strategically but weak tactically, as tactical decisions tend to rely on a small number of crucial moves which are easily missed by the randomly searching Monte Carlo algorithm.