Cryptocurrencies Now In Excel!
When Excel received the capability to retrieve stock data directly within the spreadsheet via data types, cryptocurrencies were also included in the dataset. This article will show you how to pull current crypto prices and historical data natively inside Excel.
Please note this feature is only available with a Microsoft 365 subscription.
Which Cryptocurrencies are available?
There are hundreds (if not thousands) of cryptocurrencies that are bought and sold every day. Microsoft has only paid to receive data for the handful of major cryptocurrencies traded around the world.
The currently available cryptos in Excel include:
Bitcoin (BTC)
Bitcoin Cash (BCH)
Litecoin (LTC)
Ripple (XRP)
Ethereum (ETH)
Pulling Real-time Pricing
Inputting the proper naming for cryptocurrencies is a little tricky as you can’t simply enter in the typical abbreviation for the coin and expect to get a result. You actually need to merge the coin’s abbreviation with the desired price currency you want to output. The formula essential goes like this: Cryptocurrency + Desired Price Currency
For example, if you wanted to see Bitcoin in Euros, you could enter either “BTCEUR” or “BTC/EUR” and Excel will recognize that as a valid input.
Let’s visit Excel’s Data tab and see how we can set up a cryptocurrency data pull for Bitcoin in U.S. dollars. After activating the Data tab, you should see a group of buttons called Data Types. If you do not see this, you are currently using an Excel version that does not have this capability. Select your cell with the ticker symbol and click the Stocks data type button. If your ticker is entered in properly (no spelling mistakes), you’ll notice a few changes to your cell value.
If your ticker symbol was converted succesfully, you should notice two things:
The bank symbol should appear to the left of the ticker symbol (indicating a Stock Data Type)
When the cell with the ticker is selected, you should see an “Add Field” button appear
Once your cell text has been successfully converted into a stock data type, you can begin adding fields associated with your crypto. Simply select a particular piece of information you’d like to retrieve and it will populate in the next available cell to the right of the cryptocurrency.
You’ll also notice that when a field is added, there is a formula that populates as well utilizing a cell reference + period + field name nomenclature. You can utilize formulas to move your data around your spreadsheet to suit your formatting needs.
Retrieving Historical Prices
You can utilize Excel’s new STOCKHISTORY function to not only pull pricing data for stocks but also Cryptocurrencies! Below is a simple example of you can pull the daily closing price for Bitcoin in USD for every day of 2020.
Notice I have set up 3 input cells for the ticker symbol (cell C2), the start date (Cell C4), and the desired end date (Cell C6). The STOCKHISTORY function has the following inputs:
Stock Name - Ticker Symbol
Start Date - First date you wish to pull data for
End Date - Last date you wish to pull data for
Interval - Frequency of prices pulled between start/end date
Daily = 0
Weekly = 1
Monthly = 2
Headers - Do you want headings inserted with your data set automatically?
No Headers = 0
Show Headers = 1
Show headers and Stock Ticker = 2
Property 1 - You will get a prompted list for fields you can add to your table (ie Price, 52 wk high, etc…)
Property 2…. - Continue listing out the properties you want with the coordinating number
Refreshing Your Data
Unfortunately, there is no way to automatically refresh your crypto pricing data at a specified time interval. If you wish to manually refresh your pricing data, you can do so by navigating to
Here are some VBA solutions you can utilize to help assist with auto-refreshing your data if you don’t want to do it manually:
Hopefully, I was able to explain how you can use Excel to pull cryptocurrency prices and historical information so that you can track and analyze your investments. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
About The Author
Hey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.
Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. I hope I was able to provide you some value today and hope to see you back here soon! - Chris
