Let’s convert the column to Date/Time format. The query editor uses a different language call Power Query formula so will replace our formula with its equivalent. On the query editor select Add Column>Custom Column then put out formula. To edit our query, go to Query>Edit or Data>Get Data> Launch Query Editor… The next that we’re going to do is add a custom column and convert that to time and date format in Excel.Ī quick Google Search would give us the formula =((( /60)/60)/24)+DATE(1970,1,1). Though, for this Web API the time we retrieved is in a Unix timestamp. You can click Refresh on the Query or Data Tab to get the latest data. Then finally, Click Close & Load on the Home menu.Īnd there you go you’ve successfully imported the JSON data from a public web API. On the top of the column, click the button with left and right arrow, uncheck use Original name as prefix then click OK. On the resulting prompt choose Transform>To Table on the menu then click OK. Select the Value of Data then right click>Drill Down. Then select Into Table on the Convert tab.

Paste the API URL on the prompt then click OK. To import this on excel go to Data>Get Data>From other Sources> From Web or simply Data>From Web. First, we need a public web API, because cryptocurrencies popular these days, we’re going to use a cryptocurrency API as an example.

In this example, I’m going to show you how you can extract data from a public web API to excel without coding VBA. Good thing Microsoft Excel has tools to help you extract data from web sources with or without coding. 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.

Import json into excel