Excel Integration with Databases, APIs and Third-Party Software
Your data lives in Simpro, Xero, SQL Server, Shopify, and a dozen other systems. We connect Excel to all of them — so your team works in the tools they know, with live data that does not require manual exports.
Discuss your integrationThe integration problems we solve
Most businesses have data spread across systems that were never designed to talk to each other. Excel is often the practical bridge.
Your software cannot do what you need — and cannot be changed
Third-party platforms like Simpro, Xero, MYOB, and industry-specific tools are built for their core purpose — not yours. Requesting changes is slow, expensive, and often refused. The data exists in the system; getting it out and working with it is the problem.
Exports, manual re-entry, and re-uploads waste hours every week
Teams download data from one system, reformat it in Excel, perform analysis or data massaging, then re-upload it. Each step is manual, error-prone, and invisible to the source system. A structured integration eliminates the re-entry and the errors it introduces.
Excel is isolated from live data
Reports and dashboards built in Excel go stale the moment they are saved. Without a live connection to the database or platform driving your business, every decision is based on yesterday's data. Real-time or scheduled refreshes change this.
Multi-user data management hits Excel's limits
Shared workbooks are fragile. When multiple users need to read and write the same data simultaneously, Excel alone cannot handle it safely. Using a database as the data layer — with Excel as the front end — gives you multi-user capability without abandoning the tools your team knows.
E-commerce and web platform data lives in silos
Order data, inventory levels, customer records, and fulfilment status sit in platforms like Shopify, WooCommerce, or custom APIs. Pulling that data into Excel for analysis, reconciliation, or reporting requires an integration — not a manual export.
Legacy applications have no modern reporting layer
Older business systems often have poor or no native reporting. The underlying data is there — in SQL Server, Access, or an exportable format — but surfacing it requires someone to bridge the gap between the legacy system and a usable reporting environment.
What we connect and how
Different integration challenges require different approaches. We use VBA, Power Query, and direct database connections depending on what the situation calls for.
SQL Database Connectivity
Direct VBA connections to SQL Server, MySQL, PostgreSQL, Oracle, and SQLite via ADO. Read, write, and update records from Excel without any manual export steps. We use parameterised queries, error handling, and connection pooling appropriate for the data volume and user count.
- Live reporting dashboards pulling from SQL Server
- Excel as a data entry front-end writing to a shared database
- Replacing Access databases with SQL Server-backed Excel applications
Third-Party Software — Export / Re-upload Workflows
For platforms that cannot be directly connected to — Simpro, Procore, industry-specific tools, legacy ERP systems — we build structured download-process-upload workflows. Data is pulled via export, transformed and validated in Excel using VBA, then re-uploaded in the exact format the platform requires, with reconciliation checks at each step.
- Simpro job cost data downloaded, reclassified in Excel, re-uploaded with corrected cost codes
- MYOB transaction exports processed and reconciled against budget models
- Tender pricing tools that export in the exact format required by procurement platforms
REST API and Web Service Integration
VBA can call REST APIs using WinHTTP or XMLHTTP, parse JSON and XML responses, and write results directly into Excel. Power Query adds a no-code layer for APIs that support standard authentication. We use both depending on the complexity of the data transformation required.
- Pulling live currency rates, commodity prices, or market data into financial models
- Connecting to e-commerce APIs (Shopify, WooCommerce) to pull order, inventory, and customer data
- Calling weather, logistics, or freight APIs to enrich operational reports
Database-Backed Multi-User Excel Applications
Excel is an exceptional front-end for business users — familiar, flexible, and powerful. By storing data in SQL Server or another relational database and using VBA to handle all reads and writes, we build multi-user applications that look and feel like Excel but behave like proper database applications: concurrent access, record locking, audit trails, and no file corruption.
- Job management tools used by field and office teams simultaneously
- Pricing engines where multiple estimators work from a shared database of rates and materials
- Approval workflow tools where submitters and approvers see the same data in real time
Real-world platform examples
These are the specific integration patterns we build most often for New Zealand businesses.
Job costing and cost code reclassification
Simpro is powerful for field service management but inflexible for cost reporting restructuring. We build Excel tools that download Simpro job data, allow finance teams to reclassify cost codes and margins at scale, and re-upload corrected records — a task that would take weeks of Simpro support tickets.
Budgeting, forecasting and variance reporting
Xero and MYOB handle transactional accounting well but offer limited modelling capability. We connect Excel directly to the Xero or MYOB API, pull actuals in real time, and build forecast models and variance reports that live-update without any manual export.
E-commerce analytics and inventory management
We pull order history, product performance, and inventory data from e-commerce APIs into Excel for analysis, margin calculation, and demand forecasting. Scheduled refreshes keep reports current without anyone touching a keyboard.
Multi-user front-end applications
When a business outgrows a shared workbook but is not ready for a full custom application, a database-backed Excel front-end is often the pragmatic solution. One to five concurrent users, full record history, and proper data integrity — built and running in weeks.
How we approach integrations
Integration Discovery
We map the data flow — what system holds the data, what format it is in, what transformation is needed, and where it needs to go. We identify whether VBA, Power Query, or a combination is the right tool, and whether a direct connection is possible or a structured export-process-upload model is needed.
Connection Architecture
We design the integration layer: connection strings, authentication method, query structure, error handling, and refresh schedule. For database-backed applications, we design the schema. For API integrations, we validate the authentication and rate limit constraints upfront.
Build and Test
The integration is built against real data — not sample files. We test for volume, edge cases, malformed records, connection failures, and concurrent access. Error handling and user-facing messages are built in from the start, not added later.
Handover and Documentation
You receive a working integration, documentation covering connection management, and guidance on maintaining the solution. For database-backed applications, we include a data dictionary and schema documentation your team can hand to any future developer.
Excel integration consulting across New Zealand
We work with New Zealand businesses in construction, field services, manufacturing, retail, finance, and logistics — industries where third-party platforms hold critical data but lack the flexibility to report and analyse it properly. Based in Auckland and working with clients nationwide, we understand the mix of legacy tools, cloud platforms, and practical constraints that NZ businesses operate within.
Frequently asked questions
Can Excel connect directly to our SQL Server database?
Yes. VBA uses ADO (ActiveX Data Objects) to open a direct connection to SQL Server, MySQL, PostgreSQL, Oracle, and other databases. Excel can read, write, update, and delete records with full transactional control. Power Query provides an additional no-code layer for read-only queries where that is sufficient.
Simpro says we cannot access their API. What are our options?
Simpro and similar field service platforms often have limited or gated API access for their standard tiers. The practical alternative is a structured export-process-upload workflow: data is downloaded as CSV or Excel from Simpro, processed and transformed using VBA, then re-uploaded in the format Simpro accepts. We build these workflows to run reliably and include validation at every step to catch errors before they reach the system.
Can multiple people use the same Excel file at the same time?
Standard shared workbooks are unreliable for concurrent editing. The correct architecture for multi-user Excel applications is to store all data in a SQL database and use Excel purely as the front-end. VBA handles all reads and writes to the database, which supports concurrent access properly. The result looks and feels like Excel to users but behaves like a proper application.
How do you handle authentication for REST APIs?
VBA supports API key authentication, Basic authentication, OAuth 2.0 token-based flows, and custom header authentication via WinHTTP. For OAuth, we build a token refresh flow so credentials do not need to be re-entered. Power Query supports a similar range through its built-in web connector. We match the authentication method to what the API requires.
Can Excel pull live data from Shopify or WooCommerce?
Yes. Both platforms have well-documented REST APIs. We build VBA or Power Query connections that authenticate, paginate through results, and load order, product, inventory, or customer data directly into Excel. Refresh can be triggered manually or on a schedule using Task Scheduler.
What happens when the third-party software updates and breaks the integration?
API-based integrations are dependent on the API version and the provider's change management. We build integrations against stable API versions where available, include version pinning, and document all dependencies. For export-based workflows, changes to the export format are the most common break point — we design these to surface format mismatches clearly rather than silently processing incorrect data.
Do you work with cloud databases as well as on-premises SQL Server?
Yes. We connect Excel to cloud-hosted databases including Azure SQL Database, Amazon RDS, Supabase, PlanetScale, and others via standard ADO connection strings. The connection configuration differs slightly for cloud vs on-premises, and firewall and IP whitelisting requirements need to be managed — but the Excel and VBA layer is identical.
Let's talk about what you need
Big or small, we are happy to discuss it. Send us a message or book a free discovery call — we typically respond same business day.
What happens next
- 1
We review your enquiry and reach out to discuss your requirements — no commitment needed.
- 2
We provide a no-obligation quote and estimated delivery timeframe.
- 3
Once agreed, we build in stages and keep you updated throughout.