Excel: GL Account Activity

One row per GL account with twelve months of AR, AP and journal activity

One row per GL account: the account, its name and category, base, cost centre and location segments, status and inactive date — then twelve months each of AR, AP and journal activity as invoice and line counts, debit, credit, net and last-activity date, plus the combined totals and an overall activity flag.

This is the one table that does real work before Excel sees it. Four saved queries run in parallel and merge in the script, because OpenAccess rejects the shape a four-way outer join would need. Net is Debit minus Credit, so a positive net is a debit balance.

Because of that, this recipe installs four saved queries rather than one. The wizard points all four at the same ODBC connection.

Take the .xlsx link rather than .csv. Account numbers like 1000-000-0000 and segment codes like 000 are text that CSV would mangle.

How to connect it

Open the script, click Connect to Excel and copy the link. In Excel, Data → From Web, paste, choose Anonymous, and Load. From then on Refresh All re-runs the query — there is no exported file to hunt down, nothing is scheduled and nothing is emailed around.

How current is it?

This table reads Plex over its ODBC reporting connection, not the live transactional system. That copy can trail production by up to about four hours, so treat the numbers as recent rather than real-time. Refreshing re-runs the query, but it re-runs it against the same reporting copy — it does not reach past it.

That is the right trade for reporting, review meetings and models. Anything that has to be accurate to the minute — shipping a container, releasing a job — belongs in Plex itself, not in a workbook.

Filters

Every input is an optional URL query parameter on the link, so one script serves a whole team: two sheets can be the same feed with different values. For example ?account=VALUE&category=VALUE.

What this installs

The Excel Data - Financial Account Summary script and 4 saved SQL queries it runs on. The SQL lives in the SQL editor, so a column you want added is an edit in one place that every workbook picks up on its next refresh. The table has 34 columns as shipped, and no row cap — a cap would truncate silently and the workbook would look complete.

What you need

One ODBC credential pointed at your Plex reporting connection. The install wizard asks for it once and applies it to all 4 queries. If you do not have one yet, create it first in Script Engine → Credentials with type odbc.