How To Pull Cryptocurrency Prices In Excel
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:
- Refresh data when File Opens
- Refresh data connections when a worksheet is activated
- Refresh data connections every 15 minutes
I Hope This Helped!
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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.