How about getting fresh cryptocurrency token price quotes in your Excel spreadsheet ?
I built a spreadsheet to track the development of the value of an investment in a liquidity pool. I had to register the numbers and prices of each token, the numbers and prices at the start, and the current numbers and prices. I was updating the current price data manually. That gets tiresome, constantly switching screens, typing prices. Clumsy. I wondered if I could use some crypto API to get some of the data.
I figured I could use the CoinMarketCap API to get the price updates of the two pool tokens. I thought I would have to program a routine in VBA to get the data from the CoinMarketCap API. But that is not necessary. There is an easier way, a web API interface of Excel itself. A working “no code” solution.
How does it work ? Well, step by step.
- we define the webrequest
- we make the webrequest
- we process the response data
- we get the prices quotes in a spreadsheet
- we refresh the data at the touch of a button
Defining the webrequest
Open a new worksheet.
First we are going to define our webrequest : choose ‘From Web’ from the Data menu
A dialog box will open, choose ‘advanced’.
We want the current price quote for one particular token. We enter the CoinMarketCap API url for quotes, and the parameters, at ‘URL parts’. First you enter the quotes-Url : https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest Then in the next field you can specify options, like a particular token : ?symbol=METADOGE . That limits the returned data to the token we want to track, in this case, the METADOGE token.
We also have to supply an API key with the webrequest, so we enter the API key in the headers. Under ‘HTTP request header parameters’ you add the key X-CMC_PRO_API_KEY. The corresponding value is the API key. You can find the API key in the account section at the CoinMarketCap website. A CoinMarketCap account is free. The basic account gives you 333 calls a day. That is roughly 15 calls an hour.
When we have entered our URL and our API key, we can make the request, so we can close the form and click OK.
Making the webrequest
If the webrequest succeeds, we get our response data, that we need to process to get our price quote from the returned data.
Processing the response data in Power Query Editor
Excel made the webrequest, and we have return data. We need to access the response data to get our price quote from it, and to help us do that, a Power Query Editor window will open :
We can drill down in the response data, which is JSON data. It shows in this form as the “data“-record. We want to access the response data, so click on the “data“-record. The response data of the CoinMarketCap API is a whole data-tree, we are only interested in the price quote, so we have to dig a bit to get at the price data we want.
First we see the requested symbol, METADOGE. Click on it.
That gives us the basic data of the METADOGE token :
We want the token price data, and the last entry in the form is the quote-record, that is exactly what we are looking for : so click on the quote record.
We have to choose a currency, we pick USD :
Then we get at the price quote, the data we actually want for our spreadsheet :
Listing the price quote in the Excel spreadsheet
That is the data we want, now we want it as table in the spreadsheet. So we pick the button ‘Into Table’ from the Convert menu.
The Power Query Editor will turn the data into a table, ready for the spreadsheet.
We can click “Close & Load”, that closes the editor and loads the table in our Excel spreadsheet.
The data in Excel
Once Excel is done getting fresh data from the CoinMarketCap API, we get the data in the table in our workbook :
As long as the CoinMarketAPI doesn’t change, the response data always has the same format. So if you refresh the data link, it returns the exact same table, just with fresh data. You can link to the data in the table and build your spreadsheets based on that table. The API price will always be listed in cell B2 in this example.
Updating, refreshing all data
If you want the newest data, just hit the Refresh All button on the Data menu : that refreshes all webrequests in the workbook. So you are immediately fully up to date again.
Have fun building your own Excel crypto portfolio manager spreadsheet, with the most recent CoinMarketCap data.
