In my last post, I used my understanding of Deephaven and its tools to predict some stock prices. This was a part of my more extensive study of the stock market.
In March, a company called MicroStrategy took a $205 million Bitcoin-backed loan to purchase a large sum of extra Bitcoin. They wanted to protect their monetary assets from inflation. This purchase — or rather bet — is quite risky, as something like a margin call from low Bitcoin prices could cause them to lose a lot of their money. A possible side effect is a stock price that fluctuates based on the price of Bitcoin. Using historical data, I analyzed just how influential this purchase was to their stock price alongside a few other companies that held much more reasonable amounts of Bitcoin.
After my analysis, I found that MicroStrategy’s stock price highly correlated to Bitcoin’s. This is causing high volatility and carries many risks, even going so far as to reduce MicroStrategy’s market cap by $1 billion as Bitcoin’s price dropped. In addition, a margin call on MicroStrategy could lose them large sums of money.
This project put my knowledge to the test and has given me enough confidence to move on to a real-time data project soon. I walk you through my code below.
To start, I grabbed some data from Yahoo Finance after MicroStrategy’s purchase was made. In addition to Microstrategy, I pulled some data for Tesla, since it also holds Bitcoin, and for the S&P 500 Trust, since it represents the overall market. At this point, it took me no time at all to access and import this data.
from deephaven import read_csv
bitcoin = read_csv("https://query1.finance.yahoo.com/v7/finance/download/BTC-USD?period1=1592179200&period2=1653523200&interval=1d&events=history&includeAdjustedClose=true")
.update(["Date = convertDateTime(Date + `T00:00:00 NY`)", "BTCLogPrice = log(Close)"])
.view(formulas=["Timestamp = Date", "BTCPrice = Close", "BTCLogPrice"])
microstrategy = read_csv("https://query1.finance.yahoo.com/v7/finance/download/MSTR?period1=1592179200&period2=1653523200&interval=1d&events=history&includeAdjustedClose=true")
.update(formulas=["Date = convertDateTime(Date + `T00:00:00 NY`)", "MSTRLogPrice = log(Close)"])
.view(formulas=["Timestamp = Date", "MSTRPrice = Close", "MSTRLogPrice"])
tesla = read_csv("https://query1.finance.yahoo.com/v7/finance/download/TSLA?period1=1592179200&period2=1653523200&interval=1d&events=history&includeAdjustedClose=true")
.update(formulas=["Date = convertDateTime(Date + `T00:00:00 NY`)", "TSLALogPrice = log(Close)"])
.view(formulas=["Timestamp = Date", "TSLAPrice = Close", "TSLALogPrice"])
spy = read_csv("https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=1592179200&period2=1653350400&interval=1d&events=history&includeAdjustedClose=true")
.update(formulas=["Date = convertDateTime(Date + `T00:00:00 NY`)", "SPYLogPrice = log(Close)"])
.view(formulas=["Timestamp = Date", "SPYPrice = Close", "SPYLogPrice"])
First, I made a graph comparing the prices directly. Since I knew that Bitcoin had such a high price compared to the other companies, I used logarithms to put them on a smaller scale.
from deephaven.plot.figure import Figure
from deephaven.plot import PlotStyle, Colors, Shape
log_price_comparison_plot = Figure()
.plot_xy(series_name="MicroStrategy", t=microstrategy, x="Timestamp", y="MSTRLogPrice")
.plot_xy(series_name="Bitcoin", t=bitcoin, x="Timestamp", y="BTCLogPrice")
.plot_xy(series_name="Tesla", t=tesla, x="Timestamp", y="TSLALogPrice")
.plot_xy(series_name="SPY", t=spy, x="Timestamp", y="SPYLogPrice")
.show()
As you can see, this wasn’t very helpful. So, I used one of Deephaven’s plotting tools to create multiple y-axes on my plot to better show prices next to each other.
price_comparison_plot = Figure()
.plot_xy(series_name="Bitcoin", t=bitcoin, x="Timestamp", y="BTCPrice")
.x_twin()
.plot_xy(series_name="MicroStrategy", t=microstrategy, x="Timestamp", y="MSTRPrice")
.plot_xy(series_name="Tesla", t=tesla, x="Timestamp", y="TSLAPrice")
.plot_xy(series_name="SPY", t=spy, x="Timestamp", y="SPYPrice")
.show()
This graph was undoubtedly more helpful, but it still didn’t tell me much detail about how much the prices were related.
The next step was to compare each of the stocks separately. I looked at how the prices correlated directly, plotting the price of Bitcoin on the x-axis and the corresponding prices of the stock at the same time.
mstr_btc = microstrategy
.join(table=bitcoin, on=["Timestamp"])
.update(formulas=["MSTRLogDiff = MSTRLogPrice_[ii] - MSTRLogPrice_[ii-1]", "BTCLogDiff = BTCLogPrice_[ii] - BTCLogPrice_[ii-1]"])
tsla_btc = tesla
.join(table=bitcoin, on=["Timestamp"])
.update(formulas=["TSLALogDiff = TSLALogPrice_[ii] - TSLALogPrice_[ii-1]", "BTCLogDiff = BTCLogPrice_[ii] - BTCLogPrice_[ii-1]"])
spy_btc = spy
.join(table=bitcoin, on=["Timestamp"])
.update(formulas=["SPYLogDiff = SPYLogPrice_[ii] - SPYLogPrice_[ii-1]", "BTCLogDiff = BTCLogPrice_[ii] - BTCLogPrice_[ii-1]"])
direct_comparison_MSTR_BTC = Figure()
.plot_xy(series_name="MicroStrategy vs Bitcoin", t=mstr_btc, x="BTCPrice", y="MSTRPrice" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
direct_comparison_TSLA_BTC = Figure()
.plot_xy(series_name="Tesla vs Bitcoin", t=tsla_btc, x="BTCPrice", y="TSLAPrice" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
direct_comparison_SPY_BTC = Figure()
.plot_xy(series_name="SPY vs Bitcoin", t=spy_btc, x="BTCPrice", y="SPYPrice" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
From this, I started to see my first hint that MicroStrategy had a much stronger correlation with Bitcoin’s price. MicroStrategy had a very linear relationship between prices compared to the more random curve seen in Tesla’s and SPY’s graphs. I then looked at the percent change in price from day to day for Bitcoin and the other stocks to confirm my suspicions.
return_comparison_MSTR_BTC = Figure()
.plot_xy(series_name="MicroStrategy vs Bitcoin", t=mstr_btc, x="BTCLogDiff", y="MSTRLogDiff" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
return_comparison_TSLA_BTC = Figure()
.plot_xy(series_name="Tesla vs Bitcoin", t=tsla_btc, x="BTCLogDiff", y="TSLALogDiff" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
return_comparison_SPY_BTC = Figure()
.plot_xy(series_name="SPY vs Bitcoin", t=spy_btc, x="BTCLogDiff", y="SPYLogDiff" )
.axes(plot_style=PlotStyle.SCATTER)
.point(shape=Shape.SQUARE, size=10, label="Big Point", color=Colors.RED)
.show()
As you can see, Tesla and SPY had very little correlation to Bitcoin since their points were distributed randomly across the graph. MicroStrategy, however, had more points in the first and third quadrants, meaning that a change in Bitcoin’s price generally caused a similar price change in MicroStrategy. So what? Likely you could’ve guessed this outcome with way fewer graphs. But with this knowledge in hand, I could take this analysis a step further and try to predict MicroStrategy’s price.
Since I knew that MicroStrategy has a linear relationship to Bitcoin’s price, I figured the best way to predict the stock price would be to use a linear regression. In this way, I predicted the stock prices of each of the three companies. To ensure the predictions were as accurate as possible, I used the log of the prices.
import numpy as np
import numpy.polynomial.polynomial as poly
from deephaven.pandas import to_pandas
def calc_reg(x,y):
x = np.array(x)
y = np.array(y)
reg, stats = poly.polyfit(x,y, 1, full=True)
m = reg[1]
c = reg[0]
SSR = stats[0][0]
diff = y - y.mean()
square_diff = diff ** 2
SST = square_diff.sum()
R2 = 1- SSR/SST
return (m, c, R2)
get_val = lambda rst, i: rst[i]
mstr_btc_with_reg = mstr_btc
.group_by()
.update(formulas=["Reg = calc_reg(array(BTCLogPrice), array(MSTRLogPrice))", "Beta = (double) get_val(Reg,0)", "Intercept = (double) get_val(Reg,1)", "R2 = (double) get_val(Reg,2)"])
.drop_columns(cols=["Reg"])
.ungroup()
.update("MSTRLogPred = Beta * BTCLogPrice + Intercept")
.move_columns(idx = 7, cols = "MSTRLogPred")
tsla_btc_with_reg = tsla_btc
.group_by()
.update(formulas=["Reg = calc_reg(array(BTCLogPrice), array(TSLALogPrice))", "Beta = (double) get_val(Reg,0)", "Intercept = (double) get_val(Reg,1)", "R2 = (double) get_val(Reg,2)"])
.drop_columns(cols=["Reg"])
.ungroup()
.update("TSLALogPred = Beta * BTCLogPrice + Intercept")
.move_columns(idx = 7, cols = "TSLALogPred")
spy_btc_with_reg = spy_btc
.group_by()
.update(formulas=["Reg = calc_reg(array(BTCLogPrice), array(SPYLogPrice))", "Beta = (double) get_val(Reg,0)", "Intercept = (double) get_val(Reg,1)", "R2 = (double) get_val(Reg,2)"])
.drop_columns(cols=["Reg"])
.ungroup()
.update("SPYLogPred = Beta * BTCLogPrice + Intercept")
.move_columns(idx = 7, cols = "SPYLogPred")
Now that I had the prices predicted, the last thing to do was to graph them and see how accurate my predictions were.
reg_MSTR = to_pandas(mstr_btc_with_reg.first_by())
reg_TSLA = to_pandas(tsla_btc_with_reg.first_by())
reg_SPY = to_pandas(spy_btc_with_reg.first_by())
mstr_prediction_plot = Figure()
.plot_xy(series_name="Actual", t=mstr_btc_with_reg, x="Timestamp", y="MSTRLogPrice")
.plot_xy(series_name="Predicted", t=mstr_btc_with_reg, x="Timestamp", y="MSTRLogPred")
.chart_title(title=f"R2 = {reg_MSTR['R2'][0]}, Beta = {reg_MSTR['Beta'][0]}, Intercept = {reg_MSTR['Intercept'][0]}")
.show()
tsla_prediction_plot = Figure()
.plot_xy(series_name="Actual", t=tsla_btc_with_reg, x="Timestamp", y="TSLALogPrice")
.plot_xy(series_name="Predicted", t=tsla_btc_with_reg, x="Timestamp", y="TSLALogPred")
.chart_title(title=f"R2 = {reg_TSLA['R2'][0]}, Beta = {reg_TSLA['Beta'][0]}, Intercept = {reg_TSLA['Intercept'][0]}")
.show()
spy_prediction_plot = Figure()
.plot_xy(series_name="Actual", t=spy_btc_with_reg, x="Timestamp", y="SPYLogPrice")
.plot_xy(series_name="Predicted", t=spy_btc_with_reg, x="Timestamp", y="SPYLogPred")
.chart_title(title=f"R2 = {reg_SPY['R2'][0]}, Beta = {reg_SPY['Beta'][0]}, Intercept = {reg_SPY['Intercept'][0]}")
.show()
As you can see, my prediction for MicroStrategy is very accurate compared to both Tesla and SPY. The actual price fluctuations follow my predicted price for MicroStrategy closely, meaning there is a very strong correlation between the prices of Bitcoin and MicroStrategy. This results in high volatility of their stock price – MicroStrategy’s stock plummeted $1 billion when Bitcoin briefly lowered its trading price by $1,000. This risky bet will have lasting impacts on MicroStrategy. If Bitcoin’s price goes up, MicroStrategy’s price goes up. If Bitcoin’s price goes down, MicroStrategy’s price goes down.
What do you think of their purchase? Reach out to us on Slack and let us know!
Interested in doing some analysis of your own? Be sure to check out Deephaven’s documentation and examples to get started.
Source link
lol
