Importing public web API to Excel (no coding required)
In the world of Forex and Cryptocurrency Trading, Data Scraping or Web Scraping is often needed so the data you needed is always in your hands. Good thing Microsoft Excel has tools to help you extract data from web sources with or without coding.
In this example, I’m going to show you how you can extract data from a public web API to excel without coding VBA. First, we need a public web API, because cryptocurrencies popular these days, we’re going to use a cryptocurrency API as an example.
https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=288&aggregate=5
To import this on excel go to Data>Get Data>From other Sources> From Web or simply Data>From Web.
Paste the API URL on the prompt then click OK.
Then select Into Table on the Convert tab.
Select the Value of Data then right click>Drill Down.
On the resulting prompt choose Transform>To Table on the menu then click OK.
On the top of the column, click the button with left and right arrow, uncheck use Original name as prefix then click OK. Then finally, Click Close & Load on the Home menu.
And there you go; you’ve successfully imported the JSON data from a public web API.
You can click Refresh on the Query or Data Tab to get the latest data.
Though, for this Web API the time we retrieved is in a Unix timestamp. The next that we’re going to do is add a custom column and convert that to time and date format in Excel.
A quick Google Search would give us the formula =((([time] /60)/60)/24)+DATE(1970,1,1).
To edit our query, go to Query>Edit or Data>Get Data> Launch Query Editor…
On the query editor select Add Column>Custom Column then put out formula.
The query editor uses a different language call Power Query formula so will replace our formula with its equivalent.
Now we have a new column. Let’s convert the column to Date/Time format. To do that, select custom column and on the transform tab and select date/time on the Data Type drop down.
Now we’ll replace the original time column. Move the custom column to the Beginning and remove the time column.
Then rename the custom column to time and go to Home>Close & Load.
Now our time column is in Date/Time instead of Unix timestamp.
Now that the data is in Microsoft Excel you can now do you analysis and graphs to give you an edge to your Forex or Cryptocurrency trading.
Hi,
cool tutorial. I have the following question and requesting help, please:
When I record the complete procedure with the Macro Recorder and try to assign the recorded code then to a button, I receive the error that the name of the database is already used.
What am I doing wrong ?
Who knew this function was available in Excel. Very cool but I’m trying to get financial data from treasury.gov and the downloadable data sets are current, but the api has very limited - time constrained - data such as ‘debt tp the penny’ displays for 1993. Is this a limitation of the fed site or is my Excel function truncating? Have you experienced this?
Is there a way to regularly pull data from API? Every hour for example.