This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
MonteCarlo simulation is at the heart of many financial forecasting and decision making. It is also widely used in non-financial use cases. In this project I wanted to create a sample Alteryx workflow that:
Sources the stock price data.
Performs analysis of the historical prices.
Uses historical price metrics to perform Monte Carlo simulations.
Analyse the output of the simulations to drive business decision making.
Describe the business challenge or problem you needed to solve
This simulation technique is widely used in many industries to perform statistical forecasting. The stock price movement example was common enough for everyone to benefit from and hence I pursued it to implement in Alteryx. The main aim is to provide an Alteryx template for this idea of Monte Carlo simulation based forecasting. This example is for the Alteryx community members to benefit from and enrich it further.
Describe your working solution
Step 1: Get historical stock prices.
The solution begins with sourcing the stock prices. I have sourced Yahoo Finance stock prices. For this, I had to use python tool as I needed ability to specify stock index, and time duration for which I needed to source prices. Unfortunately, we don’t have any ready to use Alteryx tool/macro for the same. I have used Python library 'pandas_datareader' to fetch stock prices. Once received, I have appended it with stock name and calculated daily percentage change in the stock price. Our simulation will forecast this price movement in the workflow further down.
Step 2: Prepare simulation parameters
Using the stock information we have collected in the previous step, we need to prepare following metrics to run Monte Carlo simulation:
Daily mean change in the stock price.
Daily volatility for the change in stock price.
How long in future we want to project simulated prices. In our example it’s a year in future. i.e. 252 trading days to represent price at the end of a year in future.
Step 3: Perform Monte Carlo simulation.
Once these numbers are ready, we have the basis for simulating stock price movements. This simulation will also need to be run many times to build the basis of our decisions. I have used batch macros to run the simulation multiple times. The ‘RunNumber’ in the generate records tool controls numbers of times the simulation is run.
For simulation, I needed a way to generate a random distribution using the parameters we calculated in previous step. Here, I had couple of alternates. One was to use Python NumPy library and the other to use Alteryx tool 'Simulation Sampling’. For the comparison purpose, I have implemented it both options. I will suggest disabling one of the two options for performance reasons when executing the workflow.
Step 3 - Option 1: Python based
In the Python Macro, we have python code using Numpy library function random() to generate price change time series for 252 trading days. All other parameters in the function are provided by the previous step using macro input.
Once the random distribution is created, it’s combined with last close price for the stock and daily prices for a year ahead are calculated.
Sep 3 - Option 2: Alteryx tool based
Just like python based macro, Alteryx 'Simulation Sampling' tool is used in another macro using the parameters we generated in previous step. All remaining steps are similar to our Python macro above to generate daily stock prices.
The one necessary additional step in this macro is to pass the seed value to Simulation Sampling tool. We must pass unique values every time we run this macro to ensure different prices are generated.
Step 4: Analyse simulation results and decide next plan of action
Now that we have projected stock price for the stock, we can perform analysis on it to drive our decision making. I have used various reporting and data analysis tools in Alteryx to understand the foretasted prices.
Using Interactive Chart tool, I have produced a chart to represent all the projected prices. However to drive investing decision making we will only focus on the all the prices we generated at the end of the year. Using the price data series for the end of forecast period, I created a histogram to understand the distribution. Followed by this, I wanted to find out what amount of profit or loss we will make at 10% confidence level. For this, I used 10 and 90% percentile values in the histogram. Assuming the analysis suggest that our level of profit expected for 10% confidence is much larger than possible loss at same confidence level, we will be happy to recommend taking position in the stock.
I also used various other Alteryx reporting tools to prepare a dynamically generated report that includes Price Projections Interactive chart, Histogram of the prices and associated commentary generated dynamically using various metrics we have calculated in the workflow.
Describe the benefits you have achieved
This workflow has forced me to think of every corners of the Alteryx tools to achieve the final result. It has surely taught me a thing or two along the way.
Ability in Alteryx to inspect outcomes at every steps and scale it to run for multiple iteration was very handy throughout development of this sample use case.
This sample can very well be extended further to add on many more features such as:
Let user choose stock to analyse
Analyse multiple stocks at the same time
Build an application that lets you choose iterations for the simulation
Build more intuitive reporting user interface to let user dynamically analyse simulation outcome.
Although it was wonderful learning experience to produce this analysis, it still has its own challenges and can surely be improved further. One of the main challenge I see is that this workflow takes a lot of time to execute. For 1000 simulations it took about 45 minutes on my i7, 16GB RAM desktop. I am sure we have fellow community members who will love to extend this workflow and further improve its performance.
Reporting in the workflow is very basic for illustration purpose and can be further enhanced. Monte Carlo simulation is widely used in many other applications such as VAR(Market Risk), Expected Loss(Credit Risk) calculations in Finance. I will like to encourage everyone to extend this workflow and customize to their own specific use case and share it with the community.
This workflow is inspired from the following python sample: