Bitcoin Madness: How to Simulate Bitcoin Prices in Google Sheets
Original Medium post can be found here: http://ift.tt/2z3hwNz
You know the scenario...
Bitcoin had another huge increase, but you missed the opportunity. You wanted to get in, but your gut instinct told you no. And rightfully so...no one knows where the price is going to go. What if you invested, and it had another 20% loss? These sort of price movements are common in the volatile world of cryptocurrencies.
Seriously...how far can this Bitcoin price really go?
BITCOIN IS A VOLATILE BEAST
Risk analysis must be a part of every decision you make.
You are constantly faced with uncertainty, ambiguity, and variability. Variability, in the case of Bitcoin, unlike anything we have ever seen before. And even though we have unprecedented access to information, we can’t accurately predict the future.
Luckily, we have methods that enable you to see all the possible outcomes of your decisions, and assess the impact of risk.
WHERE TO START?
Running simulations can prepare us for the worst.
Monte Carlo simulation (also known as the Monte Carlo Method) allows for better decision making under uncertainty.
One of the most common ways to estimate risk is the use of a Monte Carlo simulation (MCS). From Investopedia:
For example, to calculate the value at risk (VaR) of a portfolio, we can run a Monte Carlo simulation that attempts to predict the worst likely loss for a portfolio given a confidence interval over a specified time horizon - we always need to specify two conditions for VaR: confidence and horizon. (For related reading, see The Uses And Limits Of Volatility and Introduction To Value At Risk (VAR) - Part 1 and Part 2.)
A MCS can be run with many different models. Our own process will be:
- Specify a model (for here, we will use geometric Brownian motion)
- Get historical daily bitcoin prices
- Calculate daily returns
- Name the daily return range
- Summary statistics
- Simulate a year
- Simulate a year many times
- Multi-year summary statistics
- Quick analysis of results
STEP 1. WTF IS GEOMETRIC BROWNIAN MOTION?
The geometric Brownian motion (GBM) is a statistical method that is used heavily in the forecasting of stock prices. The reason the process is so attractive for this is because of the following:
- The change in price over one period of time is unrelated to the change in price over a disjoint period of time.
- The change in log(price) over any period of time is normally distributed with a distribution depending only on the length of the period.
- Samples of the distribution are continuous, with probability 100%.
The GBM is technically a Markov process, which is a fancy way of saying "A random process whose future probabilities are determined by its most recent values." Said another way, past price information is already incorporated and the next price movement is "conditionally independent" of past price movements.
Math geeks have a habit of making things infinitely more complicated than they have to be. I will do my best to make this as simple as possible.
The formula for GBM is as follows:
Where:
- B is the bitcoin price
- m or "mu" is the expected return
- s or "sigma" is the standard deviation of returns
- t is time
- e or "epsilon" is the random variable
This formula can be broken down into two very important terms: "drift" and "shock".
For each time period, our model assumes the price will "drift" up by the expected return. But the drift will be shocked (added or subtracted) by a random shock. The random shock will be the standard deviation "s" multiplied by a random number "e". This is simply a way of scaling the standard deviation.
STEP 1A. THE THUNDER GOD ELI5
The ELI5 version: The thunder god Zeus is a great god. A just god.
But Zeus is subject to wild mood swings.
Every day Zeus can shoot his magic lightning into the price of Bitcoin, and cause it to go up or down.
Some days he is in such a good mood, that he shocks the price up by a random amount. On other days, he is in such a poor mood that he shocks the price down for opposing him.
And thus, we have the essence of GBM: a series of steps with an expected upward drift, where each step is hit with a plus/minus shock (which is a function of the stock's standard deviation).
STEP 2. HISTORICAL DAILY BITCOIN PRICES
Copy the raw data scores from coinmarketcap. Paste the data into your own spreadsheet.
For this exercise, your columns will be: Time, Open, Close, High, Low, Volume.
Want to automatically pull in Bitcoin prices? Use the Spreadstreet Google Sheets Add-in.
STEP 3. CALCULATE DAILY RETURNS
Calculate daily returns from the "Close" price. in H2 put the formula:
=LN(C2/B2)
Drag it all the way down to the end of the prices to fill the entire Returns column
STEP 4. NAME THE DAILY RETURNS RANGE
Create a named range from the returns column, called returns, to make our life easier. Highlight all the data in column H, i.e. cells H1:H1000, then click on the menu Data > Named ranges… and call the range returns:
STEP 5. SUMMARY STATISTICS
Set up a small summary table with the close, daily volatility, annual volatility, daily drift, annual drift, and mean drift of our population. The formulas are:
In K1, enter:
=C2
and name it close.
In K2, enter:
=STDEV(returns)
and name it dailyVolatility
In K3, enter:
=dailyVolatility*SQRT(365)
and name it annualVolatility
In K4, enter:
=AVERAGE(returns)
and name it dailyDrift
In K5, enter:
=dailyDrift*365
and name it annualDrift
In K6, enter:
=dailyDrift-0.5*dailyVolatility^2
and name it meanDrift
Create Summary Statistics Table
STEP 6. SIMULATE A YEAR
Setup the yearly simulation table with Time, Normdist, Log Return, and Simulated Price
Time
In J12 put 0, and in J13 put:
=J12+1
Drag it all the way down to your preferred forecast timeframe. Here I simulated a year (365 days), so I copied down to J377
Normdist
Let’s set up the normal distribution curve values.
Google Sheets has a formula NORMDIST which calculates the value of the normal distribution function for a given value, mean and standard deviation. Since we ascribe to the random walk theory, we want to use a mean of 0, and a standard deviation of 1.
In K13, put the formula:
=NORMINV(RAND(),0,1)
Drag it all the way down to K377 to fill the whole Normdist column:
Log Return
To get the percentage of daily stock movement, we will calculate log return.
In L13, put the formula:
=meanDrift+dailyVolatility*K13
Copy the formula all the way down to L377:
Simulated Price
Now to the real meat. Let's calculate the simulated Bitcoin price.
In M12 put the Close price, and in M13, put:
=M12*EXP(L13)
Copy the formula all the way down to M377:
Forecasted Bitcoin price for one year
Let's see what the pricing data looks like.
Select from M12 to M377, then Insert - Chart and select line chart:
We have now successfully completed one simulation. And depending on your results, they could look normal...or downright crazy.
STEP 7. SIMULATE A YEAR MANY TIMES
We completed one simulation, but we want to run many different trials.
Create a scenario tab, setup a table to simulate 1,000 different one-year trials. In A3 to A1003, put the numbers 1 through 1000.
In B3, put the formula:
=Close*EXP((annualDrift-0.5*annualVolatility^2)+annualVolatility*norminv(rand(),0,1))
Copy the formula down all the way. Name this range "scores":
Simulate Bitcoin Prices for Many Years
STEP 8. MULTI-YEAR SUMMARY STATISTICS
Set up a small summary table with the mean, median, standard deviation, min, max, and range of our new population. The formulas are:
=AVERAGE(scores) =STDEVP(scores) =MIN(scores) =MAX(scores) =E6-E5
STEP 9. QUICK ANALYSIS OF RESULTS
My results will look different than yours (due to the random nature of NORMDIST and the time you pulled the Bitcoin prices). But let's take a look at the results:
Mean $27,147 Median $16,097 St. Dev $37,243 Min $556 Max $479,586 Range $479,029 3sd $1,486 2sd $3,005 1sd $5,850 Cur $16,098 1sd $43,896 2sd $81,998 3sd $190,129
How to read: We can be 95% certain that the price of Bitcoin will fall between $3,005, and $81,998 in one year.
Wait really? Should I buy? No, this is not telling you to buy. This should be one tool of many to help you in your buying and risk decisions.
Lognormal Distribution of Bitcoin Prices
CONCLUSION
You now know how to complete a geometric Brownian motion analysis of Bitcoin prices. Congratulations!
Good statistical analysis methods can be scary, but they don't have to be. Here we covered off on a great method for estimating future Bitcoin prices, which can also be applied to other cryptocurrencies.
With this new tool in place, you can be confident in your risk analysis methods by seeing all the possible outcomes of your decisions, and assess the impact of risk.
Deliberate. Analytical. Intelligent.
WANT YOUR OWN COPY?
Simulate Bitcoin Prices Download
RELATED POSTS
High-Flyers and Shitcoins: What I Learned from Analyzing CoinMarketCap Data in Google Sheets
7 Smart Ethereum Price Prediction Methods for HODL’ers
About the Author
John Young is the founder of Spreadstreet, former financial analyst for a big-ass company, and runner-up in the 6th grade spelling bee. He would have invested in Google if he knew about it...and had any money.
He is the author of the Spreadstreet blog, which has over 3 readers (not a typo). He hopes to hit 10, but honestly writing is a lot of work.
[link] [comments]
Read More
