Everyone is talking about Bitcoin! Which got me thinking … could we use Excel to analyse the price of Cryptocurrencies? Might it be possible to create some cool custom analysis of Bitcoin price? Whether you actually hold Bitcoin, or you think it’s a load of ‘old tosh’, currency analysis is a great way to explore the newer capabilities of Excel. So, let’s get to it – download the Excel download file and work along with me.
First, displaying live Bitcoin price in Excel requires new features only available in Excel 365. This runs against the ethos of the channel somewhat – usually we stick to tried-and-tested features in Excel, since we’re usually building files for others to use. If you don’t have Excel 365, I would encourage you to consider it since it does bring some new functionality and, overall, represents good value for money. Let’s explore some of its capabilities now.
First – how to display the live price for Bitcoin in Excel? This is easily done using a currency pairing and the currencies data type. We want to know how many US dollars a Bitcoin is worth. Simply type in the pairing (‘BTC/USD’), bearing in mind the any spelling mistakes will cause problems. Yes, as usual, we need our computer programmer heads on to get everything working. Take a moment to refresh the data to prove this price is live, or at least close to it. Cool!
But, to produce a helpful chart, we need more than just the live price. This is where Excel 365’s STOCKHISTORY formula can help us. In the video, I show you how to put the formula together to display historic Bitcoin price data, and how to visualize the data in a candlestick chart.
You might be thinking ‘That’s great, but why would I use Excel to do that? These data and visualisations are freely available online.’ And you’d be right – it’s really only a training exercise. Unless, you’re interested in backtesting trading strategies and other analyses for Bitcoin using Excel. If you are, make sure you leave me a comment below the video – I have plenty of experience in this area and would love to make some videos, as long as people like you would get value from them.
I’d be delighted to answer your questions in the YouTube comments.
%20Price%20In%20Excel.jpg)