Excel Adapter

This adapter is about enabling good reporting for an organization.

Good reporting is both an art and a science. Mostly when organizations try to report off their data the results are underwhelming. The issue is you need to coach a team to agree on what is important to collect data on.

Then you need good feedback mechanisms for the team to see what is going on and refine what they pay attention to.

The Excel Adapter makes it simple set up a webservice in Iguana which can both serve and receive data from Microsoft Excel which is an excellent tool for implementing reporting using “Pivot Tables”.

Excel is great for data analysis using tools like Pivot tables.

This makes it useful to be able to feed data from Iguana into Excel.

Why build reports from first principles instead of using built in reporting tools in applications

I prefer not to use built-in reporting tools from applications. While they’re quicker for getting initial results, they often hit limits and can’t cross-reference data from different systems, like a customer management system and an accounting application.

It more powerful to build reports from scratch by extracting data from the source systems and storing it in ‘shadow’ database tables that mirror the source data for easy querying.

This is a first principles approach more effective to build reports from scratch by extracting data from the source systems and storing it in ‘shadow’ database tables that mirror the source data for easy querying.

This is a first principles approach.

Creative approaches to coding data using #hash_tags

Getting data is all about making stories map to codes. Coding stories is difficult because it essentially means getting a team to negotiate and create a shared language to see common trends in the conversations they are having.

The same idea applies whether you are a medical team dealing with patients or company dealing with customers.

Over formalized traditional IT systems often make it difficult for a team to evolve a language to talk about the problems they are seeing.

One clever ‘hack’ is to use the practice of putting #hash_tags into notes. Almost all applications support free form text and thus you put hash tags into this text as a simple fast way to ‘code’ data.

The excel adapter makes use of Table objects.

Excel Tables: A Unique Feature

Excel table objects are a powerful feature that simplify the management of tabular data. They allow seamless addition of columns and rows, and automatically update dependent features like PivotTables. When a PivotTable references a table object as its data source, it stays synchronized with any updates to the table.

How the Iguana Excel Adapter Works

The Excel adapter code integrates with Iguana's webservice to efficiently populate Excel tables with data:

Data Fetching: The adapter retrieves data from a specified webservice URL.
Table Creation:
- If the named table doesn’t exist, the adapter:
  - Creates a new worksheet using the table name.
  - Generates a new table object with the same name on that worksheet.
  - Dynamically creates the required columns based on the data structure.
Data Insertion: If the table already exists, the adapter inserts the data into the table, updating existing rows and columns as needed.

Key Advantage: Preserving Customizations

The algorithm is designed to preserve existing tables, including any user-added columns. For example, if a user adds custom columns with formulas referencing other columns in the table, these customizations remain intact even when the table is refreshed with new data.

This makes the adapter particularly user-friendly, enabling dynamic updates without losing manual enhancements or custom calculations.

How do we put the VBA code into Excel?

Excel .xlsm files are essentially zipped bundles of XML files. You can unzip them, modify the content programmatically, and zip them back up.

They also include a vbaProject.bin file, which stores VBA code. The adapter uses this approach to add my VBA code for the adaptor.