Cryptocurrency direct prices on EXCEL
-
- 0
- 14814
- 0
Many sites offer an API to directly retrieve cryptocurrency prices, historical prices, and various information directly on Excel. This data is offered free of charge and can be automatically retrieved by any user.
Here are two example sources:
- https://coinmarketcap.com/api/
- https://www.cryptocompare.com/api/
Attached is an Excel file which refreshes the prices on all cryptocurrencies (more than 1600) every minute.
This file also includes the following information for each cryptocurrency:
- Name of the cryptocurrency
- Currency symbol
- Classification according to its capitalization
- Price in USD
- Price in BTC
- Volume traded over 24 hours
- Capitalization
- Price variation over 1hr, 24hrs, and 7 days
- etc.
Following this first import, you can carry out a lot of processes. In this example, I have included a mini virtual portfolio. The user simply needs to specify the Ticker, the amount purchased and at what price to then have the current capital gain (or loss) in real time.
NB: Depending on the version of excel you use, it may be necessary you have to modify the decimal separator from a ".” to a ",". The automatically imported prices have a ".“ and therefore cannot be used for calculations. Changing that isn’t complicated, go to: File / Excel Options / Advanced Options
Deselect the "Use system separators" box.Select “Decimal Separator".
The APIs provided by cryptocurrency sites generally work with a JSON file. To read the JSON file on Excel, you need "Power Query" and an Excel version higher than 2010.
When opening Excel you will immediately see if this extension is already installed:
And if you don't have “Power Query”? Don't panic... Microsoft offers this extension for free here -> https://www.microsoft.com/fr-FR/download/details.aspx?id=39379
Documentation for the API is generally good. The variables to include in the URL to get the right information are easy to understand.
In my example, I used this link: https://api.coinmarketcap.com/v1/ticker/?limit=10000
NB: to ensure all the cryptocurrency prices are imported, use a limit of “10000”.
In the “Power query” tab on the Excel menu, click "From the web":
Enter the API URL, and click “OK”.
A new window called "Query Editor" opens, showing a list of what has been recorded:
Then click this little button, to divide everything into columns. This is what it looks like after clicking:
The only thing left to do is to import the data directly into the Excel cells.
Finished!
You have just retrieved data for all cryptocurrencies!
Yes, it's nice to get all the prices, but it isn’t fun to have to update the source manually.
To automate the import of cryptocurrency rates, go to the "Data" tab in the Excel menu, then to "Data range property":
Did you have problems creating your cryptocurrency prices Excel file? Feel free to come and ask your questions here.
Have you improved on this example Excel file? You have added a cryptocurrency converter to a new Excel file page, or a more advanced virtual portfolio, statistics, studies, etc.
Feel free to share your Excel file with the community! Thanks in advance!
Here are two example sources:
- https://coinmarketcap.com/api/
- https://www.cryptocompare.com/api/
Attached is an Excel file which refreshes the prices on all cryptocurrencies (more than 1600) every minute.
This file also includes the following information for each cryptocurrency:
- Name of the cryptocurrency
- Currency symbol
- Classification according to its capitalization
- Price in USD
- Price in BTC
- Volume traded over 24 hours
- Capitalization
- Price variation over 1hr, 24hrs, and 7 days
- etc.
Following this first import, you can carry out a lot of processes. In this example, I have included a mini virtual portfolio. The user simply needs to specify the Ticker, the amount purchased and at what price to then have the current capital gain (or loss) in real time.
NB: Depending on the version of excel you use, it may be necessary you have to modify the decimal separator from a ".” to a ",". The automatically imported prices have a ".“ and therefore cannot be used for calculations. Changing that isn’t complicated, go to: File / Excel Options / Advanced Options
Deselect the "Use system separators" box.Select “Decimal Separator".
How to create an EXCEL file with live prices for cryptocurrencies
1/ Do you have the right version of Excel?
The APIs provided by cryptocurrency sites generally work with a JSON file. To read the JSON file on Excel, you need "Power Query" and an Excel version higher than 2010.
When opening Excel you will immediately see if this extension is already installed:
And if you don't have “Power Query”? Don't panic... Microsoft offers this extension for free here -> https://www.microsoft.com/fr-FR/download/details.aspx?id=39379
2/ What API data do you need?
Documentation for the API is generally good. The variables to include in the URL to get the right information are easy to understand.
In my example, I used this link: https://api.coinmarketcap.com/v1/ticker/?limit=10000
NB: to ensure all the cryptocurrency prices are imported, use a limit of “10000”.
3/ Import the data
In the “Power query” tab on the Excel menu, click "From the web":
Enter the API URL, and click “OK”.
A new window called "Query Editor" opens, showing a list of what has been recorded:
Then click this little button, to divide everything into columns. This is what it looks like after clicking:
The only thing left to do is to import the data directly into the Excel cells.
Finished!
You have just retrieved data for all cryptocurrencies!
How to automatically refresh Excel imports from external sources
Yes, it's nice to get all the prices, but it isn’t fun to have to update the source manually.
To automate the import of cryptocurrency rates, go to the "Data" tab in the Excel menu, then to "Data range property":
Did you have problems creating your cryptocurrency prices Excel file? Feel free to come and ask your questions here.
Have you improved on this example Excel file? You have added a cryptocurrency converter to a new Excel file page, or a more advanced virtual portfolio, statistics, studies, etc.
Feel free to share your Excel file with the community! Thanks in advance!
