# Excel API POC - GL Accounts

This POC covers only the PDF section **GL ACCOUNTS FUNCTIONS**.

## Files

- `gl_accounts_sql_poc.sql`: PostgreSQL schema/view/functions.
- `GLAccountsExcelApi.bas`: VBA module exposing Excel formulas.
- `GL_Accounts_POC.xlsx`: sample workbook with formulas wired to the VBA UDFs.
- `GL_Accounts_POC_client_safe.xlsm`: macro-enabled workbook copy configured
  with manual calculation and no forced recalculation on open.
- `GL_Accounts_POC_client_formula_text.xlsx`: client-safe workbook template
  with formulas stored as text, no VBA project, and no active UDF formulas.
- `GL_Accounts_Client_Template.xlsx`: recommended client delivery workbook
  with setup and functions sheets, formulas stored as text, no VBA project,
  and no active UDF formulas.
- `CLIENT_DELIVERY_README.md`: recommended client packaging and setup flow.
- `GL_Accounts_Functions_Documentation.md`: user-facing function reference.
- `GL_Accounts_Functions_Documentation.pdf`: PDF export of the function
  reference.

## SQL Objects

The SQL creates:

- `excel_api.gl_account_lines`
- `excel_api.currency_rate_at(currency_id, value_date)`
- `excel_api.get_account_base_amount(company, account, currency, date)`
- `excel_api.get_account_real_base_amount(company, account, currency, date)`
- `excel_api.get_account_amount(company, account, currency, date)`
- `excel_api.get_account_abbr(company, account, currency, date)`
- `excel_api.get_account_name(company, account, currency, date)`
- `excel_api.get_account_contact(company, account, currency, date)`
- `excel_api.get_account_contact_code(company, account, currency, date)`
- `excel_api.get_account_contact_name(company, account, currency, date)`

## Excel Formulas

After importing the VBA module:

```excel
=GetAccountBaseAmount("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountRealBaseAmount("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountAmount("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountAbbr("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountName("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountContact("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountContactCode("COMPANY01","400000","USD",DATE(2026,5,31))
=GetAccountContactName("COMPANY01","400000","USD",DATE(2026,5,31))
```

The sample workbook already contains these formulas in the `GL Accounts POC`
sheet. Save it as `.xlsm` after importing the VBA module.

## Current POC Assumptions

- The `company` parameter accepts either `party_party.code` of the company
  party or the company party name.
- Only posted moves are included.
- The currency parameter filters transaction currency. It accepts the internal
  currency id, `currency_currency.code`, `name`, `symbol`, or `numeric_code`.
- `GetAccountAbbr` returns `account_account.code` because no separate GL
  abbreviation field exists in the current schema.
- `GetAccountContact` is kept for backward compatibility and returns the
  contact code when available, otherwise the contact name.
- `GetAccountContactCode` returns contact codes when available, otherwise
  names.
- `GetAccountContactName` returns contact names when available, otherwise
  codes.
- `GetAccountBaseAmount` revalues the transaction amount at `value_date` using
  `currency_currency_rate`.

## Client-Safe Workbook Packaging

For external delivery, prefer `GL_Accounts_Client_Template.xlsx` as the stable
starting point. It contains no VBA project and no active UDF formulas, so Excel
cannot open an ODBC connection during workbook startup.

The previous `.xlsm` workbooks can block at `Opening ... 0%` because they
combine an embedded VBA project with active `GetAccount...` formulas. Even
with manual calculation, Microsoft 365 may inspect or recalculate these UDF
cells while loading the workbook.

Recommended delivery workflow:

1. Send the `.xlsx` template with formulas stored as text.
2. Send/import `GLAccountsExcelApi.bas` separately where macros are allowed.
3. On the `Functions` sheet, run `InstallGLApiFormulas` instead of editing
   formula text manually. It prepares result cells without writing live
   worksheet formulas.
4. Use `RefreshGLApi` to call the database and write plain result values
   instead of relying on edit-time, open-time, or paste-time recalculation.

## Session Notes - 2026-05-26

Validated manually with Excel Desktop in safe mode after Excel initially hung
while opening from the web flow.

What worked:

- Workbook saved as macro-enabled file:
  `GL_Accounts_POC_with_formulas.xlsm`.
- VBA module imported into a standard module after removing the raw
  `Attribute VB_Name = ...` line when pasted manually.
- `#NOM?` was resolved once macros/VBA compilation were correct.
- Temporary VBA error display (`#ERR <number>: <message>`) helped expose ODBC
  failures instead of generic `#VALEUR!`.
- `ICT TRADING` can be entered as the company value after SQL support for
  matching by company party name.
- Currency matching now supports values such as `1`, `EUR`, and `USD` by
  checking currency id/code/name/symbol/numeric code.
- Verified live examples:
  - `ICT TRADING`, account `20011`, currency `1`/`USD`, date `2026-05-13`
    returns Debtors values and contact list.
  - `ICT TRADING`, account `30011`, currency `EUR`, date `2026-05-13`
    returns Creditors values and contact `1741`.

Important findings:

- In the tested database, `currency_currency.code` is not the ISO label:
  `id=1` has `code=1`, `name=USD`, `symbol=USD`; `id=2` has `code=2`,
  `name=EUR`, `symbol=EUR`.
- The currency parameter currently means transaction-currency filter, not
  output currency conversion. `EUR` and `USD` can legitimately return different
  subsets for the same account.
- The VBA cache can make testing confusing; run `ClearGLApiCache` and
  `CloseGLApiConnection` before comparing changed parameters.
- Excel/ODBC may return `SQLExecDirectW unable due to the connection lost`;
  the VBA now resets the ADODB connection and retries once.
- The `@` shown before UDF names in modern Excel formulas is normal implicit
  intersection syntax and was not the issue.

Next session:

- Decide whether the currency parameter should remain a transaction-currency
  filter or become a requested output currency with conversion.
- Add an explicit workbook refresh button or visible refresh instructions for
  `ClearGLApiCache` / `CloseGLApiConnection`.
- Consider moving ODBC connection settings to a hidden/settings worksheet
  instead of hardcoding `GL_API_CONNECTION_STRING`.
- Import the new VBA wrappers if a workbook needs distinct contact code/name
  formulas.

## Recommended Production Shape

For production, prefer a readonly DB user and a named ODBC System DSN, for
example `DSN=TradonGLApi;Uid=tradon_readonly;Pwd=...;`. The VBA module is a
minimal proof of concept; a production workbook should add:

- a settings sheet for connection parameters;
- refresh/cache controls;
- error messages visible to users;
- optional Power Query preload for large reports.
