Your ERP Has an API — So What Now?

The month-end closing is due, and data from the ERP system needs to be processed. Your ERP provider recently introduced a new API, promising direct access to financial data. Many finance teams are therefore asking how to use an ERP API for reporting. The expectation is to automate the ERP export to Excel for the monthly report. For systems like Abacus, the question is how to connect the Abacus API to Excel. In practice, however, it often turns out that the work only truly begins after the data has been exported.

The API — A Technical Gateway, Not a Finished Report

An API (Application Programming Interface) is a programming interface. It allows two systems to communicate and exchange data. You can think of it as a socket for data. The ERP system provides the data, and another application can retrieve it. This exchange takes place in a structured, machine-readable format. The bexio API, for example, uses the JSON format for data exchange via so-called RESTful endpoints (bexio 2021).

However, this raw data is not a finished financial report. It is a long list of journal entries, account balances or open items. The data is technically correct but not yet meaningful from a business perspective. The logic for creating a management report, a liquidity plan or a consolidation is not included. The ERP provider's responsibility usually ends with the provision of this raw data.

The Gap Between Raw Data and the Finished Report

After the data export, the real work of the controlling department begins. The raw data must be imported into the familiar Excel environment. There, it is cleaned, validated and transformed. Charts of accounts must be mapped, cost centres allocated and accruals posted. Often, data from multiple sources — the ERP, the CRM, a separate payroll system — must be consolidated.

This manual processing in Excel is error-prone and time-consuming. A study shows that over 40% of Swiss SMEs still use Excel for parts of their accounting (Run my Accounts AG 2025). This Excel layer surrounding the ERP system is often complex and only fully understood by a few people in the company. It forms a critical but fragile link in the reporting chain. Any change in the ERP system or in the reporting requirements can break this chain.

Tools to Bridge the Gap

There are various tools to reduce manual data preparation. Business intelligence solutions like Microsoft Power BI or Tableau are common. They can connect directly to ERP databases or APIs. With tools like Power Query in Excel, transformation steps can be automated. The user builds a data model and defines how the raw data is processed into reports.

Using such tools is an important step. However, it does not replace the need to create and maintain the logic yourself. The finance team becomes the developer and maintainer of data models and queries. This requires specific technical knowledge and ties up resources. The automation is limited to the tool and often does not cover the entire process from data acquisition to the distribution of the finished reports.

Initiatives to standardise APIs aim to simplify data exchange. The Swiss Fintech Innovations association, for example, promotes common standards for APIs in the financial industry (Swiss Fintech Innovations 2018). Such harmonisations are valuable. They facilitate technical access to the data. However, they do not solve the fundamental problem: the processed data must still be converted into a commercially meaningful form.

What Complete Automation in Reporting Means

Complete automation goes beyond mere data export and the provision of tools. It views the entire process as a closed chain. The process starts with the export from the ERP and ends with the validated, ready-to-send report. An automation solution handles not only the data transport but also the entire processing logic.

This includes validating the source data, transforming it according to the company's business rules, and creating the final analyses. The solution is not a toolkit, but a managed process. Changes to the chart of accounts, new companies or adjusted reporting requirements are updated in the automation logic. The goal is not to give the finance team another tool, but to relieve them of recurring, manual work.

Such an approach does not replace the ERP system. It builds on it and automates the Excel layer that surrounds it. Sovereignty over the financial data and accounting remains entirely with the company and its ERP. The automation creates a robust and traceable bridge between the raw data and the decision-relevant information for management.

Concrete Steps for Your Reporting This Month

You don't have to wait for a major IT initiative to gain clarity. You can start preparing your reporting for automation this month. The following steps will help you to capture the status quo and define the requirements.

• Document the current process: Map the data's journey from the ERP export to the finished report. What manual steps do you perform in Excel? Who is responsible for which task? Which files are used?
• Check your data sources: Request the technical documentation for the API from your ERP provider. Clarify exactly which data fields are available. Does this data meet your reporting requirements?
• Define the target state: Design the ideal target report. Which key performance indicators (KPIs) are truly relevant for managing the company? Who are the recipients of the reports and what information do they need?
• Quantify the manual effort: Estimate the time your team spends each month on manually creating reports. This figure provides a solid basis for evaluating automation solutions.

Abacus, bexio, Excel, Power BI, Tableau, SAP, Odoo, Sage and Dynamics are registered trademarks or trademarks of their respective owners.