Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Success Stories

Learn how Alteryx customers transform their organizations using data and analytics.
STORIES WANTED

Showcase your achievements in the Maveryx Community by submitting a Success Story now!

SUBMISSION INSTRUCTIONS

Stock Price Simulation Using Monte Carlo Methods

patel_bm
8 - Asteroid

Overview of Use Case
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:
  1. Sources the stock price data.
  2. Performs analysis of the historical prices.
  3. Uses historical price metrics to perform Monte Carlo simulations.
  4. 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.

 

SourceStockPrices.JPG

 

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.

CalculatePriceDistributionParameters.JPG

 

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.

 

SimulationMacros.JPG

 

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.

PythonMacro.JPG

 

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.

 

SimSamp_Macro.JPG

 

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.

 

ReportingReporting

 

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.

 

FinalReport.jpg

 

 

Describe the benefits you have achieved
  1. 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.

  2. 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.  

  3. 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. 

 
Next Steps...

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.

 

Related Resources
This workflow is inspired from the following python sample:
 
Read more about Monte Carlo simulation:
 
 
Attachments
Comments
JakeS
Alteryx Alumni (Retired)

Hi @patel_bm 

 

Thank you so much for sharing, this is an awesome use case!

 

Trying to help with performance....

 

I understand that you have 16GB of RAM on the machine, but I'm curious to see how much you're applying to Alteryx for Sort\Join memory? This setting is located in Options>User Settings>Edit User Settings:

 

SortJoin.png

 

As you have a lot of sort\join tools, increasing the value of this setting may increase performance.

 

Another thing that may be worth trying is to run the workflow with 'performance profiling' enabled. This setting is located within the Workflow>Runtime settings:

 

EnablePerformanceProf.png

 

When the workflow is run, it will tell you how long each tool is taking. You should be able to pick out from here any tools that are taking more time and you can try to optimize individually:

 

PerformanceProfiling.png

bramdoo
5 - Atom

Thanks for a great solution.  

 

Did you incorporate Dividend Yield anywhere in the simulation?  I can't seem to find that input.

patel_bm
8 - Asteroid

@bramdoo thanks for your kind words.

 

No. It doesn't include any dividend based calculation. This is a pure stochastic model based forecasting and not discounted cash flow based.

However, you can try using historical dividend data to forecast future dividends using Monte Carlo and recalculate stock price based on DCF formula. Unfortunately, there will be significantly low number of data points for dividend payments to get as reliable forecasting as in case of stock price.

StockMarket
8 - Asteroid

Hello @patel_bm

 

Thank you so much for this great write up. 👍

cpmonteiro
10 - Fireball

Congrats @patel_bm !!

I've been reading Taleb theses days and your solution just fit like a glove for my studies!

Thanks for sharing.