Bitcoin Madness: How to Simulate Bitcoin Prices in Google Sheets

John Young
Nov 1, 2017 · 8 min read
Bitcoin Madness
Bitcoin Madness

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.

girlfriend simulator
girlfriend simulator

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:

A MCS can be run with many different models. Our own process will be:

  1. Specify a model (for here, we will use geometric Brownian motion)
  2. Get historical daily bitcoin prices
  3. Calculate daily returns
  4. Name the daily return range
  5. Summary statistics
  6. Simulate a year
  7. Simulate a year many times
  8. Multi-year summary statistics
  9. 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:

gBm formula
gBm formula

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.

Zeus Striking Down the Price
Zeus Striking Down the Price

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.

Columns Setup OHLCV
Columns Setup OHLCV

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

Calculate Daily Returns
Calculate Daily Returns

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:

Name the range returns
Name 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
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

Time
Time

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:

Normdist
Normdist

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:

Log Return
Log Return

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:

Simulated Price
Simulated Price

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:

Simulated Price for One Year
Simulated Price for One Year
A one-year simulation of Bitcoin Prices

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
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
Multiyear Summary Statistics
Multiyear Summary Statistics

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.09
Median 16,097.74
St. Dev 37,243.84
Min 556.60
Max 479,586
Range 479,029
3sd $1,486
2sd $3,005
1sd $5,850
Current $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
Lognormal Distribution of Bitcoin Prices
Lognormal returns of 1,000 simulations

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

Bio for Spreadstreet
Bio for Spreadstreet

Spreadstreet

Spreadstreet makes it simple to connect cryptocurrency services to spreadsheets.

John Young

Written by

Founder, Cryptosheets.com and Spreadstreet.io. Husband to the most beautiful woman in the world. Runner-up in 6th grade spelling bee. Super-nerd.

Spreadstreet

Spreadstreet makes it simple to connect cryptocurrency services to spreadsheets.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade