Unlocking Cryptocurrency Pricing in Excel Without Coding
Written on
Chapter 1: Introduction to Cryptocurrency Pricing in Excel
In June 2019, Microsoft introduced a feature in Office 365 that allows Excel users to import real-time stock prices into their spreadsheets. In this guide, we will explore how to retrieve cryptocurrency prices using Excel's built-in stocks functionality and the STOCKHISTORY formula. This tutorial will cover obtaining both current and historical cryptocurrency prices.
Note: To follow this guide, ensure you have a version of Office 365 released after June 2019.
Section 1.1: Setting Up Cryptocurrency Pairs
To begin, create a new workbook in Excel. In your spreadsheet, set up headers for Crypto, Price, Change %, 52 Week High, and 52 Week Low, along with a selection of cryptocurrency pairs.
Section 1.2: Accessing the Data Tab
Next, navigate to the Data Tab in Excel. Here, you will find the "Stocks" feature under the "Data Types" section.
Section 1.3: Creating a Cryptocurrency Scorecard
Now, select all the cryptocurrency pairs you've listed and click on the "Stocks" feature. You will notice stock exchange icons appear next to your cryptocurrency pairs.
Click on the stock exchange icon, and a tooltip will prompt "Show Card". Upon clicking, a detailed scorecard will display current pricing information for the selected cryptocurrencies.
Section 1.4: Summarizing Current Cryptocurrency Pricing
You may not need all the information displayed on the scorecard. Excel allows you to selectively extract the relevant pricing details into new cells. Click on one of the cryptocurrency pairs, and the "Insert Data" icon will appear.
Select "Price" from the dropdown list, and the current BTC/USD price will populate the adjacent cell.
Repeat this for "Change %", "52 Week High", and "52 Week Low" to complete your summary table.
Now, drag the values down to the "BTC/EUR" row to create a comprehensive overview of the current pricing information for your selected cryptocurrency pairs.
Chapter 2: Retrieving Historical Cryptocurrency Prices
Section 2.1: Creating a Dropdown List for Cryptocurrency
Before obtaining historical prices, let’s establish a dropdown list for your selected cryptocurrency pairs. This will allow dynamic selection and viewing of their historical prices. Create a new header titled "Crypto" in your existing spreadsheet.
Select cell B9, navigate back to the Data Tab, and choose "Data Validation".
In the Data Validation Tool, select "List" from the dropdown.
Specify the source for your dropdown by selecting the cryptocurrency pairs, and then click "OK".
Section 2.2: Fetching Historical Prices
With the dropdown list set, you can now use the Excel STOCKHISTORY function to retrieve historical prices. In a new cell (e.g., A11), type the formula =STOCKHISTORY() and fill in the required parameters.
Set cell "B9" as your first parameter, linking it to the selected cryptocurrency pair. Next, specify a start date using the TODAY() function minus 30 days to get historical data from the past month.
Set the end date using TODAY() again, and configure the interval parameter to zero for daily prices.
Finally, choose the pricing information you want to display, such as Date, Close, Open, High, Low, and Volume.
After completing the formula, hit enter, and the historical prices for your selected cryptocurrency will populate the spreadsheet.
You can choose another cryptocurrency from the dropdown, and the historical price list will update accordingly.
Section 2.3: Visualizing Historical Prices
To visualize the historical prices, simply select the data and navigate to the “Insert” tab to create a chart. Choose the OHLC chart for clear representation.
You can change the selection in the dropdown to see different cryptocurrency pairs reflected in the chart as well.
Afterword
In this guide, we've covered how to efficiently gather cryptocurrency pricing data in Excel without requiring coding skills. This method allows users to leverage Excel's powerful features for both current and historical price analysis.
If you found this article useful, consider subscribing to Medium to explore more insightful content. Your support enables me to create more articles that benefit the community.