I’m building an integration that pulls account balances from NetSuite to use as opening balances in our internal system. I want to retrieve balances as of a specific date — for example, the balance of each account on January 1, 2024 — and I’d like to do this programmatically, preferably via SuiteAnalytics Connect (ODBC) or SuiteQL.
I currently:
- Use TransactionAccountingLine + Transaction + Account tables in SQL
- Filter posting = 'T' and trandate <= '2023-12-31'
- Group by account to get balances up to a specific date
I also calculate Retained Earnings separately by summing P&L accounts for prior years, and assign that total to our retained earnings account (e.g., 301000). This mirrors the logic in NetSuite’s Trial Balance report.
My questions:
- Is this the correct and recommended approach for getting accurate opening balances via ODBC?
- Does NetSuite provide any built-in API or dataset/view for directly retrieving Trial Balance or account balances “as of date” (without summing manually)?
- If not, is there a more optimized way to replicate this logic (e.g., via Saved Search or SuiteAnalytics Workbook exposed through API)?
We are not using the Period-End Journal feature — so I assume NetSuite doesn’t close Income/Expense accounts in the GL by default.
Thanks in advance for any clarification or suggestions!