Integration Details

Excel

Export a snapshot, or build a refreshable Power Query connection over the live submission feed.

Talk to us

Overview

Excel remains where most M&E analysis actually happens, and there are two honest ways to get Monival data into it. They serve different purposes and the choice matters more than it looks.

Route one: XLSX export

The simplest path. Export a form’s submissions directly from Monival as an XLSX file (CSV is also available) and open it in Excel.

Use this for:

  • A point-in-time snapshot for a specific piece of analysis.
  • Sharing a dataset with someone who will not have credentials.
  • Attaching a dataset to a report as it stood on a given date.

The limitation is exactly what you would expect: it is a copy. It does not refresh, and once three people have three copies, nobody can say which one the number in the report came from. For anything recurring, use route two.

Route two: refreshable Power Query connection

Excel’s Data → Get Data → From Other Sources → From Web connects to Monival’s submission feed — the same API-key-authenticated JSON feed that Power BI uses — and refreshes on demand.

Use this for:

  • A working file that must reflect current data every time it is opened.
  • A recurring monthly or quarterly report built once and refreshed thereafter.
  • Any figure that will be quoted externally, because a refreshable connection has a traceable source and a copy does not.

Setting it up

  1. Create a scoped API key in Monival with read access and an expiry date.
  2. In Excel: Data → Get Data → From Other Sources → From Web, supply the feed URL for the form and the API key header.
  3. Expand the value array in Power Query — that is where the rows are.
  4. Set the page size explicitly with $top, and page with $skip. The default page size is 100, so a form with more submissions than that will silently return only the first page unless you page through it.
  5. Apply any filtering in Power Query.

Two constraints to design around

$filter is silently ignored. The parameter is accepted by the endpoint and produces no error, but no filtering is applied to the result. Filter in Power Query, never in the URL — a URL filter will appear to work while returning everything.

There is no $metadata document, so Excel’s native OData connector cannot be used. The Web / JSON connector is the supported route.

What comes back

Rows arrive flattened and ready to use: submission id, submission timestamp, status, respondent name, every answer as its own column, GPS latitude, longitude and address where captured, and the time the submission took to complete.

Column names are the question keys, not the question labels. Keys are stable when a label is edited, which keeps a built workbook from breaking — but it does mean you should rename columns once in Power Query for readability rather than expecting labels to appear.

A recommendation

If a number will be quoted to a funder, get it out of a manually exported copy and into a refreshable connection. The most common source of two documents disagreeing is not a calculation error; it is two exports taken a fortnight apart, both labelled “final”.