Defining the webrequest<\/h3>\n\n\n\n
Open a new worksheet.<\/p>\n\n\n\n
First we are going to define our webrequest : choose ‘From Web’ from the Data menu<\/p>\n\n\n\n A dialog box will open, choose ‘advanced’.<\/p>\n\n\n\n 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<\/a> Then in the next field you can specify options, like a particular token : ?symbol=METADOGE<\/a> . That limits the returned data to the token we want to track, in this case, the METADOGE token.<\/p>\n\n\n\n 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>. A CoinMarketCap account is free. The basic account gives you 333 calls a day. That is roughly 15 calls an hour.<\/p>\n\n\n\n When we have entered our URL and our API key, we can make the request, so we can close the form and click OK. <\/p>\n\n\n\n If the webrequest succeeds, we get our response data, that we need to process to get our price quote from the returned data.<\/p>\n\n\n\n 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 :<\/p>\n\n\n\n We can drill down in the response data, which is JSON data. It shows in this form as the “data<\/strong>“-record. We want to access the response data, so click on the “data<\/strong>“-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.<\/p>\n\n\n\n First we see the requested symbol, METADOGE. Click on it.<\/p>\n\n\n\n That gives us the basic data of the METADOGE token :<\/p>\n\n\n\n We want the token price data, and the last entry in the form is the quote<\/strong>-record, that is exactly what we are looking for : so click on the quote<\/strong> record. <\/p>\n\n\n\n We have to choose a currency, we pick USD :<\/p>\n\n\n\n Then we get at the price quote, the data we actually want for our spreadsheet :<\/p>\n\n\n\n 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.<\/p>\n\n\n\n The Power Query Editor will turn the data into a table, ready for the spreadsheet. <\/p>\n\n\n\n We can click “Close & Load”, that closes the editor and loads the table in our Excel spreadsheet.<\/p>\n\n\n\n Once Excel is done getting fresh data from the CoinMarketCap API, we get the data in the table in our workbook :<\/p>\n\n\n\n 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.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.juust.org\/wp-content\/uploads\/2021\/12\/import-data-from-the-web.jpg\")
<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\nMaking the webrequest<\/h3>\n\n\n\n
Processing the response data in Power Query Editor<\/h3>\n\n\n\n
<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\nListing the price quote in the Excel spreadsheet<\/h3>\n\n\n\n
<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\nThe data in Excel<\/h3>\n\n\n\n
<\/a><\/figure>\n\n\n\nUpdating, refreshing all data<\/h3>\n\n\n\n