Back to Articles|Published on November 15, 2026|30 min read

Houseblend Article

NetSuite to Excel: Saved Searches vs Web Query vs ODBC

NetSuite to Excel compared for 2026: saved-search export, Web Query, SuiteAnalytics Connect ODBC, role design and the 2027.1 2FA change, with Oracle-sourced limits and a sizing worksheet.

By Houseblend

NetSuite to Excel: Saved Searches vs Web Query vs ODBC

Summary

  1. 01Use saved-search and report exports for reviewed period-end snapshots. They are role-bound and traceable, but fresh data requires another export.
  2. 02Web Query cannot refresh financial statements. Treat a shared .iqy file as a credential because recipients can see the creator's live report data.
  3. 03SuiteAnalytics Connect is an add-on, read-only route for repeated detail queries on static or slowly changing data. Use a custom role with least privilege and OAuth 2.0.
  4. 04Require 2FA for All Roles becomes automatic in NetSuite 2027.1. Migrate password-based Connect workbooks to OAuth 2.0 and test in Release Preview or a sandbox.
  5. 05Tie Excel totals to the NetSuite statement for the same period, subsidiary and currency. Retain query and execution-log evidence, review the workbook and control its versions.
  6. 06Use a governed warehouse feed or SuiteAnalytics Workbook for high-refresh or multi-consumer needs. Conflicting documented limits and untested .iqy behavior under 2FA require account-specific testing.
Inside this article
  1. 01Executive Summary
  2. 02Introduction and Background
  3. 03Saved-Search and Report Export
  4. 04Saved-Report Excel Web Query (.iqy)
  5. 05SuiteAnalytics Connect over ODBC
  6. 06Add-Ins, SuiteQL Over REST and Warehouse Feeds
  7. 07Feature Comparison
  8. 08Designing the Reporting Role
  9. 09Refresh and Query Design
  10. 10Data Analysis and Evidence
  11. 11Controls for Finance
  12. 12Journal-Entry Upload from Excel: Permission Checklist
  13. 13Implications and Future Directions
  14. 14Frequently Asked Questions (FAQs)
  15. 15Conclusion

Executive Summary

Finance teams that run month-end packs, budget-vs-actual models and pivot tables in Excel have five realistic ways to pull NetSuite data into a workbook: a saved-search export, a saved-report Excel Web Query (.iqy file), SuiteAnalytics Connect over ODBC (Open Database Connectivity), a third-party add-in, or a governed warehouse feed. This report compares them by what each one cannot do, because those limits, documented by Oracle, decide which method fits which workbook. All statements are as of 15 November 2026.

Web Query is not offered for the financial statements, including the Income Statement, Balance Sheet and Budget vs. Actual [1]. A shared.iqy file lets the recipient see the creator's live data even if the recipient's own role would not allow it [1]. Connect is read-only [2], Oracle says it should be used with static or slowly changing data [2], and it is an add-on module with no published price on the pages reviewed [3]. Its NetSuite2.com data source is closed to the Administrator role unless the connection uses OAuth 2.0 [4], and closed to roles that require two-factor authentication (2FA) when the connection uses an email address and password [4], and to roles with IP restrictions [4].

The timing matters. Oracle states that in NetSuite 2027.1 the Require 2FA for All Roles setting will be enabled automatically [5], and that email-and-password Connect connections stop working when the role requires 2FA, for ODBC, JDBC and ADO.NET alike [5]. The remedy Oracle names is OAuth 2.0 [5], which NetSuite enables automatically for Connect accounts as of 2026.2 [6]. OAuth 2.0 must still be configured in both NetSuite and the client application [5], so reporting roles and ODBC data sources have to be rebuilt deliberately.

The decision rule is short. Use saved-search or report exports for period-end snapshots that a person reviews. Never use Web Query for statements it does not support, and treat every.iqy file as a credential. Use Connect with a custom, least-privilege role and OAuth 2.0 for GL detail, aging and inventory valuation workbooks that tolerate slowly changing data. Move high-refresh or multi-consumer workbooks to a governed warehouse or SuiteAnalytics Workbook. Whatever the method, tie the Excel figure to the NetSuite statement each period, because auditors test the accuracy and completeness of company-produced information [7]. A role checklist, a limits table and a sizing worksheet follow.

10,000Row limit for emailed saved searches
100,000On-demand report row ceiling; older FAQ figures differ, so test the actual report
1,000,000Background report row ceiling; older FAQ figures differ, so test the actual report
90 minutesSuiteAnalytics Connect session inactivity timeout

Introduction and Background

"NetSuite to Excel" sounds like one task but is at least five access designs, each with its own authentication model, row limits, licence position and audit trail. Vendor add-in pages tend to emphasise convenience. This report takes a narrower angle: a method-selection and access-control guide for controllers, FP&A leads and finance-systems owners, and for the NetSuite administrator who has been asked to "just give finance a live connection". Every restriction discussed is documented design behaviour, and each failure is treated as a question of role and workflow design.

Spreadsheets remain the working surface of finance. Staff guidance from the US Public Company Accounting Oversight Board (PCAOB) notes that when a control uses system-generated data or reports, its effectiveness depends in part on the controls over their accuracy and completeness [8]. That is why a connection method is a control decision and not only a convenience decision: the workbook is where a system-generated figure becomes a reported figure.

The report answers how to export a NetSuite saved search to Excel, how the Excel Web Query works, how ODBC and SuiteAnalytics Connect work with Excel, how a saved search differs from SuiteAnalytics Workbook, how to connect Excel to live NetSuite data, which reporting role permissions apply, and how Excel can refresh automatically. Journal-entry upload is covered only as a permission checklist. Sources are Oracle's NetSuite help, Microsoft documentation, PCAOB, SEC and NIST materials, and institutional or peer-reviewed spreadsheet research. Vendor pages are used only for what a vendor states about its own product, and no public benchmark of add-in refresh speed was found, so none is reported.

Disclosure: this report is published by Houseblend. No fact below depends on that relationship. Oracle updates NetSuite help continuously, so re-check the cited pages before changing production roles.

Saved-Search and Report Export

Capabilities

A saved-search export is the simplest method and carries the least access risk. Oracle states that exporting search results requires the Export Lists and Perform Search permissions [9], that Export Lists has a single level, Create [10], and that Excel exports are in XLSX format [11]. Each saved-search export is recorded in the search's execution log [9], which gives a reviewer a built-in trace of who exported what and when. Report exports likewise require the Export Lists permission [12].

Scheduled delivery is native. Saved search emails are limited to 10,000 rows [13], and scheduled searches run one at a time per user [14]. For slow searches that need a CSV, Oracle's persisted-search option saves results to a CSV file available for 7 days [15].

Strengths and Limitations

The strength is a role-bound snapshot with no stored credential. The limits concern format and volume. CSV exports limit decimals to two places [11], and opening a CSV directly in Excel can lose precision on numbers longer than 16 digits [16]. Oracle states report ceilings of 100,000 rows on demand [17] and 1,000,000 rows for a report executed in the background [17], and notes that saved searches lack report-style row limits although extremely large result sets may not export [17]. An older Oracle FAQ page gives lower figures, 25,000 rows for most reports [18] and a 10 MB cap on search results [18]. The pages do not say which applies to every report type, so test the largest search before relying on either number.

A saved-search export is a snapshot and does not refresh. If a workbook needs fresh data daily, someone must re-export or the team must change method. No public adoption statistics for each method were found, so the report does not rank methods by popularity.

Saved-Report Excel Web Query (.iqy)

Capabilities

A saved report can be exported as an Excel Web Query, a small.iqy file that Excel runs to refresh data from NetSuite. The report owner enables Allow Web Query in Report Builder, runs the report and chooses Export-Excel Web Query. When the file opens, Excel asks for the email address of the person who created the query, and Oracle states that the email address is case sensitive [1].

What it cannot do

  • Coverage: Oracle states that Web Query functionality is not available for financial statements [1]. The same page lists the reports where the Allow Web Query option does not appear: Income Statement, Income Statement Detail, Comparative Income Statement, Balance Sheet, Balance Sheet Detail, Comparative Balance Sheet, Cash Flow Statement, Budget Income Statement, Budget Income Statement Detail, Budget vs. Actual, Cash Statement and Cash Statement Detail. A controller who wants a live budget-vs-actual pivot will not get it through this route. Other saved reports can be used, but their numbers then have to be tied out to the statement.
  • Sharing: Oracle's warning is explicit: anyone given the file can run the report at any time and see the same live data the creator sees [1], and the only thing needed to view the report is the email address used to create it [1]. Oracle tells users to check their organization's security policy before sharing.iqy files [1]. In practice a.iqy file is a bearer credential for one saved report, scoped to the creator's role.
  • Traceability: When a user accesses a Web query created by someone else, the login audit trail records the creator's user name, but the IP address belongs to the other person [1]. A control that depends on "who ran this" cannot rely on the login audit trail for shared.iqy files. Oracle also advises caution with the Use this value/reference for future refreshes option [1].
  • Other constraints: A file exported while logged in through SAML single sign-on may fail to retrieve data [1], and IP address rules may prevent recipients from using a Web query [1]. On the Microsoft side, the Save password option does not apply to Web query connections [19].

Open question: 2FA and.iqy files

Oracle's Web Query page asks only for the NetSuite login email and does not mention two-factor authentication. Oracle's documentation does not state how an .iqy refresh behaves for a role that requires 2FA, and the behaviour has not been tested here. Oracle's 2FA page says only that where SAML or OIDC single sign-on applies, that requirement takes precedence and the 2FA requirement is ignored [20]. Test an existing.iqy file in a Release Preview or sandbox account with Require 2FA for All Roles enabled, and treat the result as the answer for that account.

SuiteAnalytics Connect over ODBC

Capabilities

Microsoft describes ODBC as a widely accepted application programming interface for database access [21], in which a user adds a driver to reach a new database system [21]. Oracle states that NetSuite offers ODBC, JDBC and ADO.NET drivers [2] and that Connect provides a read-only method for obtaining NetSuite data [2]. The older NetSuite.com data source is no longer supported [22], and NetSuite2.com is the only data source [2]. Queries written for the old source need rewriting [22]. NetSuite2.com applies role-based access, so users can query only data they can access in the SuiteAnalytics Workbook user interface [23].

Setup requires the SuiteAnalytics Connect permission [24], and the feature is an add-on module [3]. No Oracle page reviewed gives a price, so budget from a quotation. The connection must be encrypted [25]. The service port is 1708, and the field is auto-populated [25]. The account-specific host name is read from the driver download page [26], and Oracle tells users not to build the domain by hand [27].

What it cannot do

Oracle's guidance is that the Connect Service should be used with static data or data that does not change often, because real-time data access applications can slow retrieval and cause connection interruptions [2]. That is the documented reason not to point a volatile "real-time" pivot at Connect. Oracle also lists operating boundaries:

  • Connections: more than 50 open connections may produce an error [28].
  • Queries: only one query may run at a time in a single session, although separate sessions can run in parallel [28].
  • Timeouts: sessions time out after 90 minutes of inactivity [28].
  • Peak usage: new requests may occasionally fail at moments of peak usage [29].

Field count is another boundary. Oracle's Excel page says queries including more than 999 fields will not run [30], while another Oracle page puts the threshold at more than 1000 columns [31]. The pages differ by one, so name columns explicitly and stay well below either figure. Connect is read-only, which is a control benefit: an ODBC reporting role cannot post, correct or reclassify anything through the connection.

Bitness and the DSN

Windows driver downloads come in 32-bit and 64-bit packages [32], and the installed data source name (DSN) defaults to NetSuite [33]. Oracle's Excel walkthrough was written for a 32-bit driver with 32-bit Excel 2016 [30]. Microsoft's ODBC documentation describes the failure mode when architectures differ: a DSN containing an architecture mismatch between the driver and application [34]. No page reviewed states a general rule that Excel and driver bitness must always match, so install the driver build that corresponds to the Excel build and create the DSN in the matching ODBC administrator. Microsoft's Power Query ODBC connector expects the DSN to be configured first in the Windows ODBC Data Source Administrator [35] and accepts an optional SQL statement depending on driver capabilities [35].

That is why a connection method is a control decision and not only a convenience decision: the workbook is where a system-generated figure becomes a reported figure.

Add-Ins, SuiteQL Over REST and Warehouse Feeds

Third-party add-ins

Add-in vendors sell Excel-native connections. This report repeats only what a vendor states about its own product. One vendor's free tier is stated as limited to 5,000 records per query, one user, three data sources and 50 queries a month [36], and another states that scheduled automations are available only on paid plans [37]. These are vendor-stated terms, not independent measurements. A finance team evaluating an add-in should ask which NetSuite authentication method it uses, which role it runs as, and where the credential or token is stored.

SuiteQL over REST web services

SuiteQL queries can be run through REST web services, with a stated maximum of 100,000 results per query and Connect named as the route for more [38]. Oracle also states that from 2027.1 you can no longer create new integrations that use token-based authentication (TBA) for SOAP and REST web services and RESTlets [39], so any add-in or script that uses TBA today should be inventoried now. Oracle's later end of support for existing TBA integrations, tentatively 2028.2, excludes SuiteAnalytics Connect (ODBC/JDBC), so it is separate from the Connect 2FA change.

SuiteAnalytics Workbook versus saved searches, and governed warehouse feeds

SuiteAnalytics Workbook is the in-product alternative to saved searches, and Connect's NetSuite2.com applies the same role-based visibility [23]. The differences matter to a finance reader:

  • Permissions: custom roles need the SuiteAnalytics Workbook permission at Edit level [40], while a saved-search export needs Export Lists and Perform Search [9].
  • Export: exporting a Workbook dataset to CSV needs Export Lists at Create level or higher [41].
  • Migration: a Workbook uses a different data source, so existing searches may need different fields, record types, joins or formulas [42].

For a warehouse feed, Oracle names the Data Integrator role and custom roles as preferred for data-warehouse transfers [4]. Scheduled refresh then moves outside Excel: Microsoft documents that Power BI, unlike Excel Online, can schedule refresh of a workbook's data model [43], and that a gateway must be installed and running for on-premises sources [43]. That is Microsoft's description of its own product chain, not a NetSuite feature.

Feature Comparison

Table 1 maps typical close-pack workbooks to access methods, reflecting documented behaviour as of November 2026 and the design judgment of this report.

WorkbookSaved-search or report exportWeb Query (.iqy)Connect over ODBCAdd-in or warehouse feed
Period-end snapshot (trial balance, close checklist)Best fit. Role-bound XLSX with an execution-log trace. No refresh.Possible for non-statement reports. Shared file exposes the creator's data.Over-engineered for a monthly pull.Only if the team already runs one.
GL detail for variance workFits under the export ceiling. Manual re-export.Possible as a non-statement report. Audit trail shows the creator only.Best fit for slowly changing, closed-period detail. Field and session limits apply.Good for large volumes.
Budget vs. actualSnapshot export of the statement.Not offered for Budget vs. Actual.Fits with a custom role and named columns.Fits where budgets live outside NetSuite.
AR agingFits as a weekly snapshot.Possible via a saved report.Best fit when many analysts re-cut aging.Fits.
Inventory valuationFits at period end; row ceilings apply.Possible.Fits within session limits.Best fit at high row counts.
Refresh modelManual, or emailed on schedule.Refresh Data in Excel.Excel refresh through the DSN.Depends on the product.
LicenceIncluded with role permissions.Included with report permissions.Add-on module; no published price on the pages reviewed.Vendor-specific.
Role requirementExport Lists plus Perform Search.Report access; creator's email.Connect permission; OAuth 2.0 for 2FA roles.Varies; often a Connect or REST role.

No single method wins. Web Query is the narrowest because of the statement exclusion and the credential-sharing property. Saved-search export is the most auditable but the least live. Connect is the only native method built for repeated, shaped queries, at the cost of an add-on licence, role restrictions and an authentication migration. Four selection rules follow from it:

  • Snapshot workbooks: use exports, and keep the execution-log entry as evidence.
  • Statements: never plan a Web Query for a statement-type report.
  • Repeated detail pulls: use Connect on a custom role with OAuth 2.0, and name the columns.
  • High refresh or many consumers: use a warehouse feed or SuiteAnalytics Workbook.
Figure 01
Snapshots and repeated detail queries
Saved-search and report exportsPeriod-end snapshots
  • Role-bound and traceable, with no automatic refresh.
  • Keep the saved-search execution-log entry as evidence.
  • Fresh data requires another export or a change of method.
SuiteAnalytics Connect over ODBCRepeated detail pulls
  • Use a custom role with OAuth 2.0 for detail workbooks that tolerate slowly changing data.
  • Read-only access cannot post, correct or reclassify through the connection.
  • An add-on licence, role restrictions and an authentication migration add design work.

Web Query is unavailable for financial statements. Use a warehouse feed or SuiteAnalytics Workbook for high refresh or many consumers.

Designing the Reporting Role

Why a custom role

Oracle lists the roles for which NetSuite2.com is not accessible: Administrator, Full Access (Deprecated), roles requiring 2FA when the connection uses email and password, and roles accessing Connect with IP restrictions. For Administrator the exception is OAuth 2.0 [4], yet Oracle still names the Data Integrator role and custom roles as preferred [4]. An Administrator login for reporting also sits poorly with a baseline Oracle states elsewhere: 2FA is required for all Administrator and other highly privileged roles [44]. The answer to "just give finance a live connection" is a purpose-built custom role, owned by the finance-systems team, with the Connect permission [24] and only the record and subsidiary access the reports need.

NIST's control catalogue states the principle as employing least privilege, allowing only authorized accesses for users [45], and calls for reviewing assigned privileges and reassigning or removing them where necessary [45]. Because NetSuite2.com exposes only what a role can see in Workbook, the role itself enforces least privilege. Review it on a calendar, not only at creation.

Authentication: OAuth 2.0 versus TBA

Oracle states that both mechanisms work for Connect and that OAuth 2.0 is preferred [6], using the client credentials flow or the authorization code grant flow [46]. The client credentials flow is machine-to-machine [47], which suits a scheduled refresh account. The OAuth 2.0 specification (RFC 6749) lets a client request an access token using only its client credentials [48], restricts that grant to confidential clients [48], and describes tokens as representing specific scopes and durations of access [48]. The secret or certificate should therefore sit with the finance-systems team, not in a workbook. The integration record needs the SuiteAnalytics Connect box checked on its OAuth 2.0 subtab [47], and the role needs the Log in Using OAuth 2.0 Access Tokens permission [47]. The access token is valid for one hour [49], so a long-open workbook needs a token-refresh procedure.

How the TBA question squares with 2027.1

Oracle's documentation does not explicitly reconcile two points on TBA: the NetSuite2.com page says the Data Integrator role requires TBA [4], yet from 2027.1 Oracle no longer allows creating new TBA integrations for SOAP and REST web services and RESTlets, and the tentative 2028.2 end of support for existing integrations excludes SuiteAnalytics Connect (ODBC/JDBC). On Oracle's pages the role is named Data Integrator, not Data Warehouse Integrator. Oracle's TBA page excludes SuiteAnalytics Connect (ODBC/JDBC) from the tentative 2028.2 end of support for existing integrations [39], while stating that new integrations cannot use TBA from 2027.1 [39]. Oracle's documentation does not state how those statements apply to a new Connect connection using the Data Integrator role, or how the 2FA change affects TBA-authenticated Connect roles. The safe design is to build new finance reporting roles as custom roles on OAuth 2.0, leave any existing Data Integrator and TBA connection untouched until Oracle's guidance is clear, and test both in Release Preview.

2FA in 2027.1

NIST defines a multi-factor authenticator as one that requires two factors to execute a single authentication event [50], and the US Cybersecurity and Infrastructure Security Agency (CISA) states that users who enable multifactor authentication are significantly less likely to get hacked [51]. Oracle's setting extends 2FA to every role, so integration paths that rely on a password have to change. It applies to users with the Employee, Vendor or Partner entity in all their assigned roles [5]. NetSuite notified Connect customers on 20 October 2026 about the expansion [5], and an opt-in option was available from 21 September 2026 [52]. Roles that still require 2FA cannot use email-and-password authentication for Connect [5]. An administrator can disable the setting, which restores the previous role-specific 2FA requirements [5], but that is an account-wide security trade-off, not a connection fix. Oracle also states that after five failed attempts the account is locked [6], which matters when a scheduled refresh keeps retrying a stale saved password.

Table 2 turns these findings into a role checklist.

CheckWhat Oracle documentsDesign action
Role typeAdministrator and Full Access (Deprecated) are not accessible on NetSuite2.com, with an OAuth 2.0 exception for AdministratorCreate a custom reporting role; do not reuse Administrator
PermissionThe SuiteAnalytics Connect permission is required to download a driver and access dataAdd it to the custom role; limit subsidiary and account access
AuthenticationOAuth 2.0 is preferred; TBA is also supportedUse OAuth 2.0; inventory any TBA connections
2FAEmail and password fails for 2FA roles; Require 2FA for All Roles is automatic in 2027.1Test OAuth 2.0 in Release Preview before 2027.1
IP restrictionsRoles accessing Connect with IP restrictions are not accessibleRemove IP rules from the Connect role, or document the compensating network control
Excel sideSaved passwords are not encrypted in the connection [19]Do not save credentials in workbooks
Web QueryThe creator's email alone opens a shared fileRestrict who may create.iqy files

Removing IP restrictions from a Connect role widens the network surface, so document the compensating control, for example a service account whose OAuth 2.0 certificate is held by the finance-systems team and rotated on a schedule. Oracle's pages do not prescribe that control; it is a design suggestion.

Refresh and Query Design

Refresh settings in Excel

Microsoft documents a refresh-on-open option [19], a timed refresh at regular intervals [19], and background refresh, which lets the user keep working while the query runs [19]. Background refresh is not available for connections that load the Data Model [53], and refreshing on open requires data connections to be enabled in the Trust Center [53]. Microsoft describes Power Query as shaping data while the original source remains unchanged [54]. These behaviours depend on desktop Excel being open, so the answer to "can NetSuite refresh an Excel workbook automatically" is no, not from NetSuite's side. NetSuite can email a scheduled saved search, Excel can refresh a connection while open, and a gateway-based service can schedule refresh of a data model. Those are three different mechanisms.

Query design

Name columns instead of selecting everything. Read the field list from Oracle's Records Catalog [55], not the older Connect Browser, which Oracle says is no longer updated [56]. Keep queries to closed-period data where possible. Keep Excel itself in range: a worksheet holds at most 1,048,576 rows and 16,384 columns [57], 32-bit Excel is subject to 2 gigabytes of virtual address space [57], and the Power Query engine is limited to about 1 GB in the 32-bit version [58]. Excel for the web has a 250 MB total file size limit for Data Model workbooks [59].

Why not a volatile pivot on Connect

A pivot refreshed every few minutes by many analysts meets three documented boundaries at once: Oracle's guidance against real-time use [2], the concurrent-session guidance, which says many simultaneous connections can slow performance [28] and the peak-usage note [29]. If a workbook must refresh many times a day, land the data in a store built for it and let Excel read the store.

Data Analysis and Evidence

Documented limits

Table 3 collects the numeric limits referenced above, each from the page that states it.

LimitValueSource
Report rows, on demand100,000 [17]Oracle
Report rows, background1,000,000 [17]Oracle
Saved search email rows10,000 [13]Oracle
SuiteQL via REST, results per query100,000 [38]Oracle
Connect fields per query999 or 1000, per two Oracle pages [30] [31]Oracle
Open Connect connections before errors may occur50 [28]Oracle
Connect session inactivity timeout90 minutes [28]Oracle
OAuth 2.0 access token validityOne hour [49]Oracle
Excel worksheet grid1,048,576 rows by 16,384 columns [57]Microsoft
Journal import, per job25,000 records or 50 MB [60]Oracle

A GL-detail pull can exceed 100,000 report rows long before it nears the Excel grid, so Oracle's limits usually bind first. Two ceilings differ between Oracle pages (999 versus 1000 fields, and the report-row figures on an older FAQ page), which is why the report recommends testing against the real account.

Spreadsheet error evidence

Does the export step or the workbook carry more risk? Published field research on operational spreadsheets points to the workbook. A 2000 paper reviewing seven field audits found errors in 24% of the 367 spreadsheets audited [61], and the more recent audits in that review found errors in at least 86% [61]. A 2015 paper by the same author pooled six intensive-inspection studies and reported errors in 94% of the spreadsheets studied [62]. A separate study of 50 operational spreadsheets reported errors in 0.8% to 1.8% of formula cells depending on the definition [63].

These figures need care. The authors of the 2007 study caution that it is not possible at present to estimate accurately the prevalence of spreadsheet errors [63], because categories, detection methods and samples differ, and none of the samples was drawn specifically from NetSuite-fed workbooks. Inspection experiments found average error detection rates of 60% [62]. The takeaway is directional: a connection method removes copy-and-paste transcription but not formula, version or review errors, which is why the controls below sit beside the access design.

Sizing worksheet

For each workbook, fill in these lines.

  • Rows per refresh: rows returned for the largest period, compared with 100,000 and 1,000,000 for reports and 10,000 for emailed searches.
  • Columns per query: named columns, far below 999.
  • Refreshes per day: scheduled and ad hoc refreshes across all analysts.
  • Concurrent users: compared with Oracle's 50-open-connection note and its guidance that many simultaneous connections can slow performance; each session runs one query at a time.
  • Data volatility: closed-period (suits Connect) or open-period and changing.
  • Statement dependency: if the workbook is a financial statement, Web Query is not an option.
  • Bitness: Excel build and driver build.

(Hypothetical Example) A workbook pulls 40,000 GL lines and 30 named columns once at close, refreshed five times each by three analysts. That is 15 refreshes of data that is closed after the period ends, which fits Connect on a custom role. A second workbook wants the same lines refreshed every ten minutes by 25 analysts. That runs against the real-time guidance and the concurrent-connection guidance, and belongs on a warehouse feed.

Controls for Finance

Tie-out and evidence

Under PCAOB's standard, auditors using company-produced information as evidence test its accuracy and completeness, or the controls over it [7], and reliance on automated controls is linked to effective general controls over program changes, access to programs and computer operations [64]. The Securities and Exchange Commission's guidance for management names the same areas [65] and gives the consistent application of a formula or the performance of a calculation as an example of an IT-dependent control [65]. A spreadsheet that applies formulas to an ERP extract is that kind of control. The American Institute of Certified Public Accountants (AICPA) likewise points auditors to information quality attributes including accuracy and completeness [66].

PCAOB staff commentary describes the expectation that auditors identify the sources of the underlying data in reports and how the reports are generated [67], and notes a good practice of a central repository of reports [67]. It states that it is not a rule, policy or standard of the Board [67], so it is context. The transferable idea is an inventory: record each report, its source, how it was tested and the conclusion.

A practical tie-out has four parts:

  • Control total: the Excel total equals the NetSuite financial statement total for the same period, subsidiary and currency. The ICAEW Financial Modelling Code recommends an equality check for each set of values calculated separately that should match [68].
  • Source agreement: ICAEW's review guide describes data review as checking that input data agrees with the external sources it comes from [69], and an ICAEW article for auditors adds checking that key data agrees back to the underlying database [70].
  • Evidence: keep the query definition, the saved-search execution-log entry, and the tie-out file.
  • Review: the Code recommends a system of peer review [68].

Who may create access, and version discipline

Because a.iqy file works as a credential, only named finance-systems staff should enable Allow Web Query. Microsoft advises checking that a connection file was authored by a reliable individual [53] and avoiding saving logon information in connections [19]. Power Query lets a user delete the credentials stored for a data source [71], which belongs in the leaver and role-change checklist.

On version control, ICAEW's review guide requires a clear protocol for recording version numbers [69], treats using an incorrect version of the spreadsheet as a process error [69], and notes that detailed change logs are very helpful [69]. An ICAEW article for auditors observes that spreadsheets often lack the robust audit trail of IT systems [70], so the naming convention (entity, period, version, preparer, tie-out date) is the audit trail. Protect sensitive ranges, as the Code recommends [68], while remembering that Microsoft states worksheet-level protection is not intended as a security feature [72]. Test formulas against a manual calculation periodically [73], and reconcile input data to source documentation [73].

Keep a workbook inventory with: workbook name, owner, source saved search or report, access method, role used, last tie-out date, next review date and retire-by date. Retire a workbook from direct NetSuite access when it needs refreshes beyond Oracle's guidance for Connect, is consumed by many people who each need their own authentication, approaches the query field ceiling, or regularly fails tie-out. The destination is SuiteAnalytics Workbook for governed in-product analysis, or a warehouse fed through a Data Integrator or custom role.

Figure 02
Finance tie-out and evidence workflow
  1. 01Match the control total

    Check that the Excel total equals the NetSuite financial statement total for the same period, subsidiary and currency.

  2. 02Check source agreement

    Check input data against its external sources and key data against the underlying database.

  3. 03Retain the evidence

    Keep the query definition, saved-search execution-log entry and tie-out file.

  4. 04Arrange peer review

    Use a system of peer review for the workbook.

Journal-Entry Upload from Excel: Permission Checklist

Excel is also used in the opposite direction, to prepare journal entries for upload. Oracle describes CSV import as the most common method for transferring small to medium-sized data sets [74]. The format has weak guarantees: RFC 4180 says CSV had never been formally documented [75] and does not specify an Internet standard [75], and a double quote inside a field must be escaped by another double quote [75], so test memo fields that contain quotes or commas. This section is a short checklist only and does not cover import design.

  • Permissions: Make Journal Entry at Edit or Full [76], plus Posting Period on Transactions and Import CSV File. Setting entries to approved also requires Journal Approval at Edit or Full [76].
  • Dedicated role: Oracle states that the safest route is a specialized role used only for journal entry imports [76].
  • File rules: at least two lines with balancing debits and credits [77], and in OneWorld, journals and intercompany journals in separate files and jobs [77].
  • Size limits: up to 10,000 lines per journal entry [78], and 25,000 records or 50 MB per import job [60].
  • Approval: where approval is required, entries do not post to the general ledger until approved [79].

NIST's control AC-5 calls for defining system access authorizations to support separation of duties [45], and its discussion says the practice helps reduce the risk of malevolent activity without collusion [45]. The person who prepares the Excel file, the person who imports it and the person who approves it should therefore be different users where team size allows.

Implications and Future Directions

The central implication is a deadline. Require 2FA for All Roles will be enabled automatically in NetSuite 2027.1 [5]. For any workbook that connects through ODBC with a stored email and password, the migration steps are:

  • Role: create a custom reporting role.
  • OAuth 2.0: configure it on both sides [5].
  • Connection: rebuild the DSN or connection string.
  • Test: run the refresh in Release Preview or a sandbox.

Oracle states that OAuth 2.0 allows connections to use eligible roles that require 2FA [5].

Two other migrations overlap. NetSuite.com is no longer supported, so old queries need rewriting [22], and the TBA timeline adds a second migration for add-ins and scripts. Microsoft documents that Excel's Web (Legacy) data import wizard is deprecated [80]. That statement concerns the legacy web wizard, and Microsoft's documentation gives no timeline for .iqy files, so treat the .iqy route as one to plan away from, not one with a stated end date.

Three actions follow for finance leadership:

  • Inventory: build the workbook inventory this quarter.
  • Migrate: move every workbook that depends on a shared.iqy file or a stored password to a controlled method.
  • Test: try the 2027.1 setting in a Release Preview account before the production upgrade.
Figure 03
Migrate password-based ODBC workbooks
  1. 01Create the reporting role

    Create a custom reporting role for the workbook connection.

  2. 02Configure OAuth 2.0

    Configure OAuth 2.0 in both NetSuite and the client application.

  3. 03Rebuild the connection

    Rebuild the DSN or connection string used by the workbook.

  4. 04Test the refresh

    Run the refresh in Release Preview or a sandbox before the production upgrade.

Frequently Asked Questions (FAQs)

How can a NetSuite saved search be exported to Excel?

Use the Excel export on the search results, which requires the Export Lists and Perform Search permissions and produces an XLSX file.

How does the Excel Web Query work, and can it refresh financial statements?

It exports a saved report as an.iqy file that Excel runs to refresh data, using the creator's login email. It is not available for financial statements.

Can Excel connect to live NetSuite data through ODBC?

Yes, with SuiteAnalytics Connect and the NetSuite2.com data source, but Oracle recommends static or slowly changing data, and the role and authentication rules above apply.

Which roles cannot use NetSuite2.com, and what changes in 2027.1?

Administrator (unless OAuth 2.0 is used), Full Access (Deprecated), 2FA roles on email-and-password connections, and IP-restricted roles. In 2027.1 Require 2FA for All Roles is enabled automatically, so email-and-password Connect connections stop working for roles that require 2FA.

What does SuiteAnalytics Connect cost?

Oracle describes it as an add-on module and no price appeared on the pages reviewed, so obtain a quotation.

Can NetSuite refresh an Excel workbook automatically?

Not from NetSuite's side. Excel can refresh on open or at intervals while the workbook is open, and scheduled refresh of a data model needs a service such as Power BI with a gateway for on-premises sources.

Conclusion

No single access method fits every workbook, and each native method has a documented boundary. Saved-search and report exports are role-bound and traceable but static. Web Query is excluded for the financial statements and works as a bearer credential for a shared file. SuiteAnalytics Connect over ODBC is the native route for repeated, shaped queries, but it is an add-on, read-only, closed to Administrator, to 2FA roles on email-and-password connections and to IP-restricted roles, and Oracle advises using it with static or slowly changing data.

The reporting role is the real design object. A custom, least-privilege role on OAuth 2.0 is the configuration most consistent with Oracle's pages and with the 2027.1 change. Beyond access, the Excel number needs a tie-out to the NetSuite statement, a version convention and a retire-by date, because research on operational spreadsheets suggests errors are common and hard to detect by review alone. Where this report found gaps, such as how.iqy files behave under 2FA, the correct step is a sandbox test, not an assumption.

External Sources (80)

About

Houseblend

Make NetSuite work better for your finance and operations teams with Houseblend. We help design, implement, integrate and improve ERP systems, with practical support for the people who use them every day.

Houseblend is a NetSuite consulting firm serving finance and operations teams. We help organizations implement ERP systems, connect business applications, improve existing configurations and maintain the systems that support everyday work. Our audience includes finance leaders, controllers, operations managers, NetSuite administrators and implementation teams.

Implementation and architecture

Houseblend provides NetSuite implementation, architecture and data migration services. We help teams evaluate how business processes, reporting requirements and existing data should fit together in an ERP environment. Training supports the people responsible for adopting and operating the resulting system.

Integrations, customization and AI

Our services include NetSuite integrations and customization, as well as AI integrations and AI transformation work. These engagements connect ERP data and workflows with the broader application landscape. The right design depends on the organization's systems, controls and operating needs.

Improve and support an existing system

Houseblend offers NetSuite health checks, optimization, managed support and project rescue services. We also provide expertise for analytics and specialist workflows, including NetSuite Analytics Warehouse, warehouse management and field service management. Published educational material helps teams investigate options and prepare informed questions for their implementation or support work.

Work with Houseblend

Explore NetSuite implementation, integrations, managed support and AI integrations. Contact Houseblend to discuss your current system and priorities.

Article examples explain concepts rather than promising a particular license, product capability, delivery schedule or outcome. Engagement scope is confirmed with the Houseblend team.

Disclaimer

This document is provided for informational purposes only. No representations or warranties are made regarding the accuracy, completeness, or reliability of its contents. Any use of this information is at your own risk. Houseblend shall not be liable for any damages arising from the use of this document. This content was generated with assistance from artificial intelligence tools, which may contain errors or inaccuracies. Readers should verify critical information independently. All product names, trademarks, and registered trademarks mentioned are property of their respective owners and are used for identification purposes only. Use of these names does not imply endorsement. This document does not constitute professional or legal advice. For specific guidance related to your needs, please consult qualified professionals.