4 Replies Latest reply: Jul 21, 2014 9:29 AM by Rodrigo Faus RSS

    App of the month: Monte Carlo Simulation

    Paul Van Siclen

      Hello all,

       

      The attached file is an example of running a Monte Carlo simulation inside of QlikView.  Monte Carlo simulation is a modeling technique that allows the user to measure the range of possible outcomes across a normally distributed population of assumptions (other distrubutions are possible but this model is based on a normal curve).

       

      The ability to run a simulation like this has many implications in the analytics space:

       

      • Strategic Planning
      • Discounted Cash FlowsProfitability Modeling
      • Price Elasticity
      • Loss Forecasting
      • Many others

       

      I wanted to share this example file with the Community to demonstrate the ability of QlikView to handle.  In fact, it handles very well as I can run 10,000, 100,000 or even a million simulations on my desktop.

       

      Enjoy.

       

      Paul Van Siclen

        • Re: App of the month: Monte Carlo Simulation
          Advait Thakur

          Hello Paul,

            Nice to hear about simulations inside Qlikview, also the implications in the analytics space are covered well.

           

          Is Monte Carlo simulation like SAP ERP Simulation which has been developed by Baton !!

           

           

          Thanks

          Advait

          • Re: App of the month: Monte Carlo Simulation
            Moritz Michalke

            Great demo App !

             

            Thanks for sharing, Paul !

            • Re: App of the month: Monte Carlo Simulation

              Hi Paul,

               

              Do you know if I can use QlikView in order to calculate some Greek Letters of a portfolio? Like the Deta, Gamma,Vega, Rho of na option:

               

              Thanks,

               

              Rodrigo

                • Re: Re: App of the month: Monte Carlo Simulation

                  Hi Rodrigo,

                   

                  I knocked up a quick app (attached) to demonstrate the option pricing functionality in Qlik. Please observe the following assumptions

                  - Black Scholes formula used, which is limited to european options, not paying dividends

                  -  for all greeks ( i havent included second order greeks (like Gamma), I have implemented a shift variable).

                  - To calculate each greek, I then use a standard blackscholes option price, and that with shift applied. The difference will be the sensitivity to the underlying price input, or greek, if we want to scare people off with some nice jargon

                   

                  - to take this example and extend to the portfolio, you would need to perform the caulculations as attached, for each instrument, then multiply by the position (number of the instrument held).

                  - The portfolio greek then will be a sum of the positions.

                   

                  I hope this helps.

                    • Re: Re: App of the month: Monte Carlo Simulation

                      Hi Stuart,

                      First off all, thanks for the help.I really appreciated. Just few questions and concern:

                      - Can I also use the  Black Scholes model in order to calculate the value of an American non dividends option? I guess the model is really close for both.

                      - What do you mean by the shift applied? Can  I just use the others instruments?

                      - Have you tried to comercialize a app that shows the historical value of a portfolio (with different assets), possibles investment in options, strategies (straddle, strangle, bull spread...) and risk analysis?

                      Thanks once again.

                      Rodrigo

                        • Re: App of the month: Monte Carlo Simulation

                          Hi Rod,

                           

                          - Can I also use the  Black Scholes model in order to calculate the value of an American non dividends option?

                          - Answer: The black Scholes model works only for european options as it only allows for exercise of the option. The binomial model is the only true way to value american options. The price differenences will be pronounced for puts with high risk free rate, where binomial model will correctly value the early exercise premium.

                           

                           

                          - What do you mean by the shift applied? Can  I just use the others instruments?

                          - Answer: rather than work out actual greeks, which measure sensitivity of moves in pricing inputs, the shift is used to price once with basline and one with shift appiled to variable in question. The difference between the two will show the sensitivity.

                          I've created a new dashboard, this time calculating greeks via formulae: Option Price Calculator

                           

                           

                          - Have you tried to comercialize a app that shows the historical value of a portfolio (with different assets), possibles investment in options, strategies (straddle, strangle, bull spread...) and risk analysis?

                          - Answer: I was just playing with the blackscholes function. Actually I see differences in derived theoretical price and the Qlik provided BlackScholes function. See my post: Option Price Calculator for details.

                           

                          I hope this helps

                          Cheers

                          Stu

                    • Re: Re: App of the month: Monte Carlo Simulation

                      Hi Everyone,

                       

                      Is is possible to calculate the EWMA (Exponentially Weighted Moving Average) with QlikView?

                       

                      Thanks,

                       

                      Rodrigo