Whether you’re a cryptocurrency investor or trader, importing live cryptocurrency prices in Excel can definitely help you track your cryptocurrency investments.
If you’re looking to import crypto prices to Excel, then you’ve come to the right place, there are actually several ways to import cryptocurrency prices in Excel.
The easiest way to import live cryptocurrency prices is by using the
Cryptosheets Addon within Excel.
In this tutorial, we will show you exactly 3 ways that you can import cryptocurrency prices using excel.
Affiliate Disclaimer: We sometimes use affiliate links in our content. This won’t cost you anything, but it helps keep our lights on and pays our writing and developer teams. We appreciate your support!
Method 1: Using Excel Cryptosheets Addon
From our research, the best way we found to import cryptocurrency data in Excel is using cryptosheets addon (Excel has a special button that allows you to use addon features).
Cryptosheets basically gather real-time cryptocurrency prices through multiple cryptocurrency exchanges, by doing so they can provide information for both altcoins and the main cryptocurrency coins.
Whereas other methods only provide the main cryptocurrency coins such as BTC, ETH, and ETC.
In doing so, you do not need to use any programming, web scraping, or connecting to API functions.
Cryptosheets basically does all the hard work for you.
They also provide much more functions such as:
- NFT token tracking
- Connection to IEX cloud
To get our cryptosheets addon we need to follow these steps:
- Click on Insert
- Click on Get Add-ins
- Search for cryptosheets
- Click on Add Button
- Follow and agree to the terms and conditions
Once you’ve successfully installed cryptosheets it should appear as an extra header option.
To use cryptosheets you need to first create an account with them.
To create an account, you can follow these steps:
- Click on Cryptosheets
- Click on Register
- Input registration details
- Confirm your email
Once we have now successfully created and verified our account, we can now use the cryptosheets functions.
To get our cryptocurrency prices we can use the function “=cs.price(Ticker, USD)“, this function basically grabs the price of a particular cryptocurrency through two inputs; the cryptocurrency ticker symbol and the quote (USD).
As shown above, cryptosheets has successfully imported both the altcoins and the main coins as well.
Another interesting function we can also use is called cs.exrate.
This function basically pulls real-time prices of cryptocurrency, and updates instantly based on the intervals that have been provided.
To use the function you need to declare the ticker symbol of the cryptocurrency, currency output, and the interval at which you want the currency to update.
This is the function: “cs.exrate(Ticker, USD, Interval)“.
As you can see the prices are automatically updating each second, this feature can be useful if you plan to make your own personalized life cryptocurrency portfolio tracker.
You can also generate some pretty interesting data visualizations using cs.exrate such as the one below:
This is a live-action treemap that automatically updates its rectangles based on the size of each cryptocurrency change. So the bigger the change the greater size of the rectangle becomes.
Cryptosheets teaches you how to build that exact one on their blog “How to Build a Real-Time Streaming Cryptocurrency Dashboard in Excel in 5 Minutes“.
Method 2: Using Excel Convert To Stocks Button
The easiest of all is using the “convert to stocks” button that excel provides in their spreadsheets.
The only negative aspect about the “convert to stocks” button that excel has, is it only provides a limited amount of cryptocurrency coins.
These are the only available ones as of writing this article:
- Bitcoin (BTCUSD)
- Bitcoin Cash (BCHUSD)
- Litecoin (LTCUSD)
- Ripple (XRPUSD)
- Ethereum (ETHUSD)
These are the following attributes that excel provides for each of the cryptocurrency coins:
- 52 week high
- 52 week low
- Change (%)
- From Currency
- Instrument Type
- Last trade time
- low Name
- Previous close
- Ticker symbol
To use this function you need to highlight the relevant cell that contains your cryptocurrency, in my case, I am importing the price of BTC.
To convert to cryptocurrency you can follow these steps:
- Click on the cell that holds your cryptocurrency ticker symbol
- Click on Data
- Click on Stocks
Once you have successfully used the “convert to stocks” button your ticker symbol should input a stock icon ().
To input our attributes for the cryptocurrency we can use our icon (insert data) on the top right of the ticker symbol ().
By clicking on our insert data symbol, and selecting our attributes excel automatically inputs the attribute to the right-hand side of our ticker symbol.
If you click on each attribute you’ll see that it is actually a function, so if you’d like to place your attributes in another orientation you can call the ticker symbol location such as =B3 and excel will automatically input a drop-down menu with all the available attributes.
Method 3: Using Excel Power Query to import cryptocurrency prices
Power query is an option that excel provides to import data from many different sources and then clean, transform and reshape your data.
We can use this function to import cryptocurrency prices through the coinmarketcap website.
You don’t particularly need to use coinmarketcap, you can also use other websites such as tradingview, kukoin, etc.
As long as the website outputs the cryptocurrency coin and the price, excel can handle the data extraction process.
To import the coinmarketcap data we can follow these steps:
- Click on Data
- Click on From Web
- Enter “https://coinmarketcap.com/” in the URL box
- Click on OK
Basically excel is now gathering the data to import into our sheet, this may take a while depending on your computer and internet speed.
After the data has successfully imported we can now edit the data columns.
To edit our data columns we can follow these steps:
- Click on Table 0
- Click on Transform Data
Basically, all the other columns were deleted other than the “name” and “price” columns.
To delete and save our columns you can follow these steps:
- Shift click the column header names
- Click on Remove Columns
- Click on Close & Load
Your output should look something like the image below, as you have noticed, a few of the cryptocurrencies have a “buy” next to their ticker symbols.
This is most likely due to coinmarketcap’s buy button, where excel imported the buy letters as well.
Furthermore, it is also critical to know the price updates each time prices changes, to automatically update your cryptocurrency prices using power query you can follow these steps:
- Click on the Cryptocurrency Table
- Click on Query Header
- Click on Properties
- Update to 1 in “Refresh every x Minutes”
- Tick “Refresh Every 1 minutes”
- Click on “OK”
Conclusion To Importing Cryptocurrency Prices Using Excel
In this article, we have gone through 3 different methods that you can import cryptocurrency prices using excel.
By far the best option we believe is using cryptosheets, where they provide active live market price data without the need for programming.
Benefits of tracking cryptocurrency prices using Excel
- Track all your cryptocurrencies in one place – If you hold multiple altcoins from different exchanges, it may be difficult to track all your investments. Making an excel sheet helps automatically track all your investments in one place.
- Extremely useful for those who hold their cryptocurrency in cold storage – If you plan to hold your cryptocurrency offline in cold storage, it may be difficult to keep track of your live cryptocurrency prices. Making a spreadsheet in Excel can definitely help solve this.
- Conduct statistical analysis – Excel provides plenty of mathematical and statistical functions, this means you can analyze your portfolio to review your diversification, your average purchase price and many more.
- Create your own personalised analysis – As you may know, some trading websites require you to purchase their premium plan to use specific functions. By making your own tools in excel, you can have your own tailored analysis and tools that are completely free!
Importing cryptocurrency into Excel FAQ
One way to keep track of cryptocurrency investments is to create your own cryptocurrency investment tracker in Excel.
There are multiple ways to import cryptocurrency prices in Excel, we recommend that you use an addon called “cryptosheets” which pulls real-time data from exchanges to include onto your spreadsheets.
Currently, the only three ways to import altcoins into excel are using the addon “cryptosheets”, connecting to an exchange API, or gathering data using excel power query from coinmarketcap and relevant websites.