tradon/notes/accounting/excel_api/README_GL_ACCOUNTS_POC.md

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:

=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.