Analyzing Bitcoin with Excel

First Step: Cleaning and Formatting the Dataset

The first steps of the project were to import the csv file into and clean up the dataset to begin analysis. I used commands such as TRIM() and LEFT() to organize the Month and Symbol columns to ensure all the data had the same name. Finally, I used the Filter menu for each column to identify any missing entries in the dataset and I sorted the data from lowest to highest in order to find any outliers. I noted this information down as it could skew the results.

Second Step: Determining Upside Potential

After the data was cleaned and formatted, the first step into analyzing Bitcoin was to calculate its Upside potential vs the SP500 in 2021. I extracted the opening and closing values for each asset from the dataset and calculated the percentage change using the formula [(New-Old) / Old].

After analyzing the percentage change of the value of each asset, it is clear that Bitcoin grew 12% more than the SP500 index in 2021. That being said, both assets performed substantially better than in their previous years, demonstrating a strong year within the financial markets.

Third Step: Analyzing Spread

The next step was to analyze the volatility of each asset in 2021. First I found the Low and High of each asset by using MiN() + IF() and MAX() + IF() functions. Since there was an error in the dataset ($300,000 price for Bitcoin (outlier)), I had to LARGE() instead of MAX() to extract the second highest value for Bitcoin.

The volatility between Bitcoin and the SP500 vary tremendously, with Bitcoin having an overall percentage change of 148% from its lowest point in 2021 to its highest, compared to the SP500 which only had a 31% difference. This chart indicates that Bitcoin is a more volatile asset with a price that can change at a much faster rate than that of the SP500. This can be caused by Bitcoin being a relatively new asset with no true valuation and because of it not being considered a regular security like the companies held inside of the SP500. There is a lot more skepticism within Bitcoin which can cause much more volatility as shown above.

Fourth Step: Creating Average Monthly Closing Value Pivot Table

The pivot table above visualizes the average closing price of each asset in every month of 2021 with conditional formatting to help understand the lowest closing value's (red) and highest closing values (green) each month.

As you can see, Bitcoin demonstrates it self to be a much more volatile asset, with its closing price varying from lows to highs twice in 2021 while the SP500 is seen as a more stable asset with its lows starting in January due to COVID and its price rises slowly but steadily throughout the year.

Fifth Step: Creating Daily Price Chart

The Pivot Table and Chart above represent the High value of both assets every day in 2021.

As you can see by the chart above, the SP500 is a much less volatile stock, with its price change from the beginning of the year to the end being minimal but nevertheless still a positive change. Bitcoin on the other hand had a much more volatile price change throughout the entire year, rising for the first 5 months, then dropping drastically. It continued to consolidate for a few more months before beginning a short term bull run until December, where the price began to dip again.

Although the value of Bitcoin was more volatile throughout the year, the Upside from the beginning of the year until the end was substantially more than the SP500.

Final Recommendation

After analyzing the trends of the SP500 and BTCUSD during the year 2021, My final recommendations are that the upsides of BTCUSD outweigh the risks of volatility. Although Bitcoin's price action is much more significant than that of the SP500, it has shown strength in its lows and continued to rally on. The investor must have a much stronger psychological mindset when investing in Bitcoin as the current losses can be stressful on the mind of the investor which can lead them to panic sell their holdings.

If you focus exclusively on preserving capital and always position yourself for the next huge market crash, you’re likely to miss out on substantial market gains. And missing out on large gains can be just as detrimental to your investment performance as taking part in large losses.

I’m certainly not advocating for investors to abandon downside risk management and ignore the impact losses can have on both your bottom line and your psyche as an investor. Even though the losses may pale in comparison to the gains over the very long haul, those losses hurt twice as bad  as the gains feel good because of our inherent loss aversion.

But investors who focus exclusively on downside protection without a plan for how to handle the upside will be sorry. No one really views upside in the markets as a risk but it certainly can be a risk if you miss out on large gains or have no plan of attack for how to handle them.

‍