An API is a ‘bridge‘ through which one app obtains/sends information from/to another app. This means that you can load miscellaneous data from your source app to your Excel workbook using the REST API. Moreover, you can even schedule data loads via the API to Excel sheet without any VBA coding! Read our guide to learn how you can do this.
How to use API in Excel – options you have
Basically, you have three options to link API to Excel:
- Power Query: You can query data from APIs using Excel’s built-in tool.
- Coupler.io: This third-party importer will let you automate data exports via APIs to Excel on a custom schedule.
- VBA: This is a code-based option that is suitable for tech-savvy Excel users.
Let’s check out each option in action so that you can make the best choice for your needs. For this, we asked Richard, a cryptocurrency enthusiast, to help us.
Richard wants to analyze the growth tendency of the top crypto coins. To do this, he needs to load data from CryptoCompare using their JSON API.
For testing, Richard will use the following API URL that will load data about the price for five cryptocoins: Bitcoin, Ethereum, Terra, Binance Coin, and Tether.
https://min-api.cryptocompare.com/data/pricemulti?fsyms=BTC,ETH,LUNA,BNB,USDT&tsyms=USD,EUR&api_key={enter-api-key}Note: This API requires authorization, so Richard has already generated a free API key.
So, let’s start with the option that allows you to schedule exports via API to Excel.
How to regularly pull API data into Excel
For scheduled exports of data from API to Microsoft Excel, Richard will use Coupler.io. It’s a solution that allows you to connect to multiple sources and load data from them to Excel, Google Sheets, or BigQuery. You can export data on demand and automate import on a custom schedule, such as every day or every hour.
For Richard’s case, we’ll use the JSON integration with Excel. Let’s see how it works.
The instructions provided in this article are sufficient for creating an automated integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API.
If you need help with the setup or want to have a more user-friendly version of this connector, we can develop this premium integration for you for free. Just select the app you need from the list and follow the instructions on requesting the automated integration.
Excel API example with Coupler.io
Sign up to Coupler.io with your Microsoft account. Click Add new importer – this is a boilerplate to set up your connection to API. Choose JSON as a source application and Microsoft Excel as the destination application. Then complete the following steps to load data from API to Excel sheet.
Source
- Insert the API URL to the JSON URL field. Click Continue.
https://min-api.cryptocompare.com/data/pricemulti
Note: You’ve noticed that this API URL differs from the one mentioned in the beginning. It lacks attached query strings because Coupler.io provides separate fields to specify request headers and URL query parameters. So, Richard will use those to demonstrate how they work.
- Request headers – here, you can apply specific headers to your request, such as Authorization, as Richard did.
authorization: Apikey {enter-api-key}- URL query parameters – this field is for query parameters for your requests to the API. In Richard’s case, he specified the following strings:
fsyms: BTC,ETH,LUNA,BNB,USDTtsyms: USD,EUR
Other fields, Request body, Columns, and Path are optional. You can learn more about them in the Coupler.io Help Center.
Note: You can find information about the parameters used in those fields in the API documentation of your application. Pay attention to the information about API authentication and details of API endpoints – these are usually located in separate sections.
Now you’re ready to Jump to the Destination Settings.
Destination
- Select a workbook on OneDrive and a worksheet where the data will be loaded. You can select an existing Excel worksheet or create a new one. Click Continue.
Note: Optionally, you can change the first cell for your imported data range, change the import mode, and add a column that contains the date and time of the last update.
Clicking on Save and Run loads the data from API to Excel right away. But Richard needs to automate this export on a recurring basis, so he needs to complete another step.
How to get data from API in Excel recurrently
You can configure the schedule for your JSON importer during the initial setup or at any other time. For this, you need to toggle on the Automatic data refresh and configure the frequency:
- Interval – from every month to every 15 minutes.
- Days of week – select the days you want to have the importer run.
- Time preferences – select the time when you want to have your data importer from the API.
- Schedule time zone – do not forget to specify the time zone for your dataflow.
That’s it! Let’s check out the results of the records pulled from API to Excel sheet?
The imported data does not look very neat. Nevertheless, you can create a separate sheet where the data will be transformed to the format you want and link Excel sheets. Therefore, every time Coupler.io refreshes your data, it will be automatically refreshed on the linked sheet.
Connect API to Excel with Power Query
Power Query is a data transformation engine within Excel. As well, it allows you to query data from different sources including REST APIs. Here is what you need to do to link API to Excel.
- In your Excel workbook, go to Data => New Query => From Other Sources => From Web.
- Insert the API URL and click OK.
- Choose the web content access. For this example, we can go with Anonymous. Click Connect.
- The Power Query editor will open where you can preview the data loaded from API. Go to the Convert tab and click Into Table.
- This will convert the data from API to a table, and you’ll be able to transform it before loading it to the Excel workbook, for example, transposing rows to columns or vice versa.
- You see that values in the Value column are hidden. The reason is that you need to select which columns to display. For this, click on the Expand button and select the columns. Click OK.
- Now you see two columns: Value.USD and Value.EUR. When all the necessary transformations are done, click Close & Load.
There you go!
You can also check out the details of your connection.
Can you link API to Excel on a schedule with PowerQuery?
You can refresh data from the API on demand or set up a frequency for the automatic refresh. For this, go to Data => Connections => Properties… and configure the Refresh control.
The Power Query setup looks somewhat intricate because the main purpose of this tool is to transform data. So, it’s better to use Power Query in combination with Coupler.io – your data will go to Excel on a schedule where you can transform it with Power Query as you wish.
How to connect API to Excel using VBA
The answer is very simple – you need to learn VBA and implement your coding knowledge. 🙂 The benefit of a code-based solution is that you can make it custom. This is achievable if you’re proficient in coding and specifically VBA. Alternatively, you can find some templates on GitHub or somewhere else and tweak them. But it is unlikely that you will find a code sample that will cover all your needs.
Which way to connect API to Excel sheet is the best one?
The no-code way is the choice of many, including Richard. For his needs with low data volume and infrequent data refresh, he can use the Coupler.io Personal plan. In addition to the option of connecting API to Excel sheet, you can benefit from many ready-to-use integrations, as well as other destination apps – Google Sheets and BigQuery. You can try out Coupler.io for free with a 14-day trial.
Streamline your data analytics & reporting with Coupler.io!
Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!
- Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
- Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
- Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
- Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
- Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.
Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.
Start 14-day free trial
The built-in PowerQuery functionality seems to be the actionable option to link API to Excel. It allows you to grab data and transform it on the go. However, the main drawback of PowerQuery is that it’s only available for the Excel desktop app. If this is not an issue for you, you can go with it.
Who’s going to choose the VBA script to connect API to Excel? This is the way for tech-savvy Excel experts who prefer to create custom functions and integrations for their liking. If you are one of those, then it’s probably your way as well.
Zakhar Yung
A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼
