QuickBooks Online - Solution for max cell limit of 400000 for API response

As per Intuit engineering team, the api response is limited to a range of 400,000 cells. A solution was developed and implemented for this limitation.

Log files

The Add-in Express tool used for ExcelFSM has been upgraded to version 9.5.4661, released on January 29, 2021. With this upgraded version, two log files (adxregistrator.log and adxloader.log) are generated upon the installation of ExcelFSM and the running of Excel, and are stored in the following folder :

{user profile}\AppData\Local\Temp\ExcelFSM\





Available in French and Spanish

Travel from the English to the French and to the Spanish websites by clicking the buttons at the top left of the pages

Consolidation

A validation has been added to make sure that company A has a name specified in the Consolidation list before enabling the Proceed button.
Clicking Insert on an existing Consolidation list, the new enterprise is now inserted at the bottom of the list.
After adding an enterprise into an existing or new Consolidation list, the cursor is now placed at the bottom ot the list.
An issue remains with the use of ExcelFSM QuickBooks Online company files containing the access token, within the Consolidation list. This is issue is described in the following with a workaround.

Sometimes, the following message was displayed when a trial balance had debits and credits on the same column:
     Error: account B- is duplicated in the input Trial Balance. Check the ReadTBData printout.
This issue has been fixed.



BackUp file

Sometimes, the following error message was displayed when read amended trial balance:
     'Sorry, we couldn't find ... - BackUp.xlsx. Is it possible it was moved, renamed or deleted?'
This was generated when ExcelFSM was trying to create the backup file but did not have permission to save the file in the folder indicated in the message.
Now, if ExcelFSM does not have permission to save the backup file in a folder, no backup file will be created.



Fixed issues with the QuickBooks Online classes

Issue when Multi-currency enabled: ExcelFSM was not able to handle classes when the Advanced Setting Multi-currency was On. This has been fixed.

Class list of more than 100 classes: The code used to query the QBO company class list has a result size of 100 by default. This default size has been increased to its maximum value of 1,000.

Sub classes using the same name as Classes: The following message was displayed when sub classes and classes have the same name:
     Error: account ...|... is duplicated in the input Trial Balance.

Now, if ExcelFSM detected a sub-class with the same name as a class, it will use the fullname of the sub-class in the account title.



Item Balance

A user reported that when double-clicking an item balance it took about 5 minutes before the Item Balance dialog appeared in a specific worksheet. That issue was caused by the fact that, for some reasons, the last column value for that worksheet was the maximum value of 16,384 columns. Consequently, ExcelFSM was looking in all those columns before displaying the Item Balance dialog. Code has been added to ExcelFSM to prevent such issue.

Item Balance with no grouped account: Cells intilialized with the FSMITEMBALANCE formula, with no account grouped in the Item Balance dialog, are now highlighted in red.



Total

Total dialog not showing the Items titles: If there was cells containing blanks(spaces) before the Items titles, the Total dialog was not showing the Items titles. Code has been modified to skip the cells with blanks and display the Items titles in the Total dialog.

For ExcelFSM and ExcelFSM with the QuickBooks Online feature

a modification was made to fix the 'mscoree issue'. This does not concern the ExcelFSM with the QuickBooks Desktop feature.

The ExcelFSM QuickBooks Online feature and the web utility app used to create the "ExcelFSM QuickBooks Online" company file containing the access token have been improved, and ExcelFSM is now listed on the Intuit App Store.

ExcelFSM QuickBooks Online Utility program




The ExcelFSM QuickBooks Online Utility program can be launched from My Apps in the QuickBooks Online company




With QuickBooks Online Accountant, the ExcelFSM QuickBooks Online Utility program can be launched from Client apps (by the Admin)

The QuickBooks Online feature has been upgraded to Oauth2

and the "ExcelFSM QuickBooks Online" company file containing the access token is now created with a web utility app.

A solution for the issue with classes with the QuickBooks Desktop Accountant Edition has been integrated in ExcelFSM.

That issue, for which the following message is displayed



occurs when the transactions are collapsed and there is a transaction with multi-allocation using different classes for the same account.

With the QuickBooks Desktop Accountant Edition, the transactions are always collapsed and there is no '_Collapse Transactions' checkbox when you do Edit > Preferences > Reports & Graphs > Company Preferences.

Therefore a specific solution for QuickBooks Desktop Accountant Edition was developped and integrated in ExcelFSM.


Note that if for some reasons, FSM cannot get the detail for some types of collapsed transactions, the following message will be displayed



and, looking at these transactions listed in the 'TransactionsNotDetailled' sheet, you will be able to re-allocate to the account | class thru an adjusting entry.



When it's not an Accountant Edition of QuickBooks Desktop, if collapsed transactions are found with classes, the following message will be displayed






For consolidated - new checkbox to use the same specifications for the enterprises.

A checkbox has been added to the Read Trial Balance Data dialog (excel file and QuickBooks) to use the same specifications for the next enterprises.

     

If the checkbox _Use these specifications for the next enterprises is checked, the Read Trial Balance Data dialog above will not be displayed for the remaining enterprises of the consolidation list, and ExcelFSM will use the same specifications for these enterprises.



For consolidated - accounts of new enterprise now inserted to the bottom of the Chart of Accounts.

Adding an enterprise to the Consolidation list of an existing Consolidated FSM workbook, accounts will now be inserted at the bottom of the Chart of Accounts instead of at the top.

     



For consolidated - selected amended trial balances.

The Help as been updated to provide more explanations on how to specify selected amended trial balances to update a Consolidated FSM workbook.





Adjusted balances button - turned ON every time a sheet gets activated



From now on, if there are adjusting entries, Adjusted balances button will be turned ON every time a sheet gets activated.
If the button is toggled OFF, the unadjusted balances of accounts, items and totals will be displayed by having ExcelFSM ignore the adjusting entries, as previously, and, if the button is not toggled back ON before leaving the sheet, it will remain OFF until the sheet gets activated again.
The working papers will be processed as they were processed before, showing the balances of the related report according to the Adjusted button status (ON/OFF).



Bug fixed:
- For consolidated, QuickBooks Desktop, with classes.
- For consolidated, with GL transactions, QuickBooks Online.
- With GL transactions, when there is no account number, transactions linked to an account with a zero balance could be linked to the wrong account.
- With GL transactions, for some particular transactions (transactions with FSM generated docum), not all the lines appeared in the Transaction Detail
   displayed, although all lines were in the 'Transactions' sheet generated when click the FSM GL button.


Other issues fixed:
- Merged cell A1 issue.
- Account titles do not appear in Details if there is no item title.
- Save of the groupings immediately after using the ‘Transforming Item’s Accounts Into Items’ feature.
- First sheet of workbook hidden issue.


Note: an answer for the following FSM QuickBooks Online error: "There was an error while communicating with the Ids Server"
            has been found and documented. (Issue & Answer)

For periodic financial reports (monthly, quarterly, etc), the Account Balance Detail will now be for the periods range

With the General Ledger data read by ExcelFSM being the year to date data, the Account Balance Detail will be available for any periods range comprised within the year to date range.


(With previous versions, it was always showing the year to date detail.)



Trial Balance working papers - GL Drill Down

In the Trial Balance working papers, the GL Drill Down can now be used for the balances in columns I-J and K-L, not just in columns C-D.



General Ledger Drill Down and Excel specifications and limits



Clicking the GL button, if the limit of 1 million rows is reached while creating a General Ledger worksheet or a Transactions worksheet, a new worksheet with a sequential number will be added:
'General Ledger 2', 'General Ledger 3', etc.
'Transactions 2', Transactions 3', etc.



Issues fixed:
- When read prior period, ExcelFSM now keeps the GL of current period.
- The 2.8.3 issue "Paste is disabled when Copy and Paste contents from a workbook into a FSM workbook": Paste is no longer disabled when copying
   and pasting contents from a workbook into a FSM workbook.




To see your current version: click the FSM 'About' button.



To upgrade your current version:

1. Uninstall the current ExcelFSM (click here to see how)

2. From the Downloads/Buy page check the options you want and click the ‘Download’ button to install the latest version.

The ExcelFSM QuickBooks Online option is now available

for which 'With GL transactions' can be selected for the General Ledger Drill Down,

as well as 'With Classes'.




Image of the General Ledger Drill Down showing the account | class balance detail, and the selected transaction detail.

Issue fixed

To avoid getting the 'Interop.QBFC11.Lib' issue when the Support QuickBooks Desktop option is not checked, the QBFC related files were removed from the ExcelFSM products that do not include the Support QuickBooks Desktop option.

New option to create Trial Balance working papers with subtotals for the financial report items.



With this option checked, the accounts are listed according to the items of the financial report, and subtotals are added in the trial balance for these items.




New update option for the working papers.



When changes are made to a financial report, the related Working Papers are automatically updated the next time the sheets become active. For some working papers, this updating process takes few seconds, and for that reason, the automatic updated is no longer the default for the working papers. To make it the default, the checkbox '_WP auto update' in the 'Sheets Manager' must be checked. (Note: for existing FSM workbooks, the checkbox will be checked by default).
If not checked, when changes are made to a financial report, the tag of the related Working Papers are colored in red to indicate that the working paper is not up to date. Clicking the 'update' button in the 'Sheets Manager' will update the working papers with tag colored in red.



QuickBooks

Choice is now offered between Accrual Basis and Cash Basis for the trial balance data and the general ledger data, with the QuickBooks option.



Also, the algorithm for the 'With Classes' feature has been reviewed and improved.
This fixes issues occuring sometimes with subclasses, mainly when QuickBooks Preferences to _Show lowest subaccount only was not checked.



Bug fixed: when the last row of a financial report has merged cells, the grouped accounts were not removed from the Ungrouped Accounts list of the Item Balance dialog. This bug has been fixed.

Other issues fixed:
- The "Keep Position and Size" check mark (Item Titles, Item Balance, Total) is no longer lost if you click on to another spreadsheet.
- The new period FSM workbook will now be saved in the same directory as the existing FSM workbook.
- To avoid the "All Cells Show Zeros" issue, an automatic workbook recalculation has been added when a workbook is activated.

To avoid any possibility of confusion, the toggle 'Balance Variation' button has been replaced by the non-toggle button 'Select Period'.

'Comparative' button is no longer a toggle button. Clicking the 'Comparative' button allows to insert additional balances, for the selected previous financial periods, to the right of the last balance on each row in your financial statements, and in the dynamic and customizable Leadsheets.
The removal of those additional balances is now done manually.

The period of these additional balances can be modified with the 'Select Period' button.


The 'Select Period' button has been added to the context menu that pops up when you right click on a worksheet cell.





Details that explain the button's function are now displayed when the mouse pointer is hovered over a button.





New UI to edit/modify the name of the enterprise, initialized in the Read Trial Balance Data process.

Clicking the Name button on a blank cell initializes the cell with the FSMENTERPRISE formula.
Clicking the Name button or double-clicking a cell containing the FSMENTERPRISE formula allows to edit/modify the name of the enterprise.





New UI for the Date button.

Clicking the Date button now allows you to initalize a cell with a date different than the As-of Date of the current period, which is checked by default.
Clicking the Date button or double-clicking a cell containing the FSMDATE formula allows to edit/modify the date by selecting a different one.





New UI to get comparative Leadsheets.

After clicking the Create button, you are prompted to select a comparative period (period or periods range) for the Leadsheets.



Additional comparative balances can further be added to the customizable Leadsheets with the Comparative button, and the period of these additional balances can be modified with the Select Period button.



Modified UI for the Trial Balance prior period.
A periods range can now be selected as the prior period of the Trial Balance working paper, for the prior period in the Trial Balance working paper of the Statement of Cash Flows for example.



Note that the prior period in the Trial Balance worksheet can still be modified with the Date button, but now with a periods range as well.



Modified UI for the 'Delete period' feature. To delete a period, i.e., to remove a period from the FSM workbook:
· Select a blank cell
· Click on Select Period button



· Check the period to remove from the FSM workbook and click Delete period.



Bug fixed: item balance period indexes were reset to 0,0 when editing/modifying the grouping of a prior period. This bug has been fixed.

2.8.1 error fixed: the GL button was accidentally deactivated in version 2.8.1. It has been reactivated in version 2.8.2.

When the first sheet of the workbook was hidden, the following error was generated after clicking the Read Trial Balance Data button. This error has been fixed.





Other minor change:

In the Read Trial Balance Data dialog for QuickBooks file, the buttons 'Non-zero balance only' and 'All' are now enabled only if _With Classes is checked.

replacing the previous Standard, Hybrid, and Professional editions

You can now select the period or periods range of an item directly from the dialog, by clicking the link line




For the Show Account Balances feature, the balances are now formatted and displayed based on culture. For example, the decimal number 600726.49 is formatted as 600,726.49 for the culture English (United States) and 6,00,726.49 for the culture English (India)

ExcelFSM 2.7.1 introduces a new way to store data within the FSM workbook. This new data storage allows WordFSM to get the adjusted trial balance data without having to run Excel. This also means that previous ExcelFSM versions cannot use FSM workbooks saved with version 2.7.1. On the other hand, version 2.7.1 will work with FSM workbooks saved with previous versions.


‘Sync With WordFSM’ button

ExcelFSM 2.7.1 adds the button ‘Sync With WordFSM’, which transforms a copied financial statement from a FSM document and synchronizes the groupings with the FSM document.


Required for WordFSM

Selected FSM workbook in WordFSM must have been opened and saved with ExcelFSM version 2.7.1 or later to be compatible with WordFSM.

You can select "Non-zero balance only" if you do not want the QuickBooks classes with a zero balance to be part of the trial balance.





The error HRESULT:0x8007000E (E_OUTOFMEMORY) has been fixed.





Other minor change:
show the account balance according to period range with the 'Show Account Balances' option in the Item Balance dialog.



Minor adjustments to the General Ledger Drill Down option (Beta version):
- fix the 'read general ledger data' process, to work when the account column description contains only the account number;
- fix the 'Other transactions fields' title issue.

The following limitation has been removed




New and unique feature:                                    The General Ledger Drill Down Beta

Thanks to the technology of the ROMAD patent (Reading Organizing and Manipulating Accounting Data - of any accounting software), ExcelFSM will read and organize all the transactions from the beginning of the year of specified the as-of date, and you will be able to get the general ledger detail of an account balance, from the customized financial statements built with ExcelFSM.



Click here for more information about the General Ledger Drill Down Beta

New capability to process amended trial balances of prior periods and automatically update the financial statements:

Providing the as-of date of an existing prior period, ExcelFSM will assume you are re-reading an amended trial balance for an existing period and, if you confirm to proceed, will update the existing period's data




NOTE: ExcelFSM can also process amended trial balances for selected enterprises in an existing FSM Consolidated workbook.





New feature allowing to transform Item's accounts into Items


From the toggled Details of an Item, you can select accounts (shown with the setting 'without account numbers' in the following sample)




then click the Item Balance button from the FSM ribbon



and choose to transform the selected accounts


into one item per selected account




or into one item for all selected accounts







New feature for Item Balance: the NewId button


Copying Item Balances and using the NewId button can be really useful for the building of the financial statement Notes and for the Statement of Cash Flows, as shown in the following sample:




Copying an Item Balance cell from the Balance Sheet to the Statement of Cash Flows

then clicking the NewId button to replace the item's unique identifier with a new one in the formula



allows to change the grouping or the sense of such an Item in the new cell without modifying the Item of the original cell.


Here is the formula, and its resulting value, after changing the sense of the Item to Credit and after selecting the periods range







Other minor changes:
- show the adjusted balance with the 'Show Account Balances' option in the Item Balance dialog;
- keep the filepath of the non excel files in the consolidation list.

New user interface to select period(s) for a range of cells:



The periods indexes used in the FSM formulas are now displayed, between parentheses.




New features for the Item Balance:






New features for the Total:






New feature for the Suggested Item Titles:






The Multiple Column Report Issue With Total has been fixed.

Version 2.3 offers a new user interface for consolidated financial statements (ExcelFSM Professional Edition)

The Consolidation list can combine files of various types (Excel files, QuickBooks Desktop Company files, QuickBooks Online Company files)





Version 2.3 also adds a new button to automatically reorder new accounts in the Chart Of Accounts.

Version 2.2.2 solves the issue where the following error message was displayed

    


when _With Classes was checked in the ‘Read Trial Balance Data’ process, with the ExcelFSM for QuickBooks Desktop editions

Version 2.2.1 solves the issue where nothing happens when clicking ‘Read Trial Balance Data’ because the user account has a dot.

    


The 'Read Trial Balance Data' process ended because of the dot in the user account.

    

A- ExcelFSM now supports QuickBooks Online Products (except Simple Start)

     Read Trial Balance Data - QuickBooks Online | Creating the ExcelFSMQBO company file


B- ExcelFSM now supports Excel 64-bit

     Microsoft Excel must be 32-bits, to access QuickBooks Desktop. Otherwise, Microsoft Excel can be either 32-bits or 64-bits.


C- The following error occuring in some cases in the 'Read Trial Balance Data' process after checking With Classes, has been fixed.

A- Improved handling of QuickBooks Classes

B- The default Sense (Debit or Credit) of a zero balance account read from the trial balance will be the accounting sense of the preceding account in the trial balance instead of always Debit



C- Fixed error when clicking 'Read Trial Balance Data' if no workbook is open.

A- Compatible with Microsoft Excel 2013



B- Fully supports Excel Workbooks containing Chart Sheets


C- Improved 'Read Trial Balance Data' process for Standard Edition and Professional Edition:

     · The trial balance data is now directly copied into a temporary 'Data' sheet,
        thus considerably reducing the time to import trial balances with thousands of accounts.

    · The trial balance data can now have debits and credits in the same column.

D- Hybrid Edition for QuickBooks Users and Professional Edition with QuickBooks Support now handle QuickBooks Classes

E- Professional Edition with QuickBooks Support now allows consolidation of QuickBooks company files.

A- An 'About' button has been added in the FSM Ribbon to provide pertinent information about your ExcelFSM software (Edition, version, etc.),
information that will be useful for technical support.



B- The QuickBooks feature (Hybrid Edition and Professional Edition with QuickBooks Support) has been simplified to facilitate the initial set up that allows ExcelFSM to access directly the data of the QuickBooks Company file.

The first time you perform ExcelFSM’s Read Trial Balance Data function for a Company file, with QuickBooks running and a Company file open, QuickBooks will prompt you to allow ExcelFSM to read and modify the QuickBooks company file.


Select 'Yes, always; allow access even if QuickBooks is not running' and click 'Continue'

(Note that ExcelFSM only reads data. It does not modify the QuickBooks company file.)

A- All predefined date and number formats are removed and replaced by 'localized' formats, to internationalize ExcelFSM.

For example, the decimal separator, as well as the thousands groups separator, vary depending on the country. The United States and Great Britain are two of the few places in the world that use a period to indicate the decimal place. Many other countries use a comma instead. Likewise, while the U.S. and U.K. use a comma to separate groups of thousands, many other countries use a period instead, and some countries separate thousands groups with a thin space.

The reports generated by ExcelFSM now use localized formats.

For the financial statement reports built by the user, before starting to build a report, user will set the desired format
by clicking on top left corner of the sheet


and selecting the desired and localized number format:



B- An 'update' button has been added in the Sheet Manager.

The Working Papers are dynamic and updated whenever a change is made in the related financial report. If for some reasons, a working paper is not updated following a change, clicking that button will turn the swith update ON for the selected sheets, and next time these selected sheets are activated, they will be updated.


C- Fixed error 'A- is duplicated in the input Trial Balance’ occuring with some input trial balance when checked '_For Consolidated'


D- First translation of ExcelFSM has been released: French ExcelFSM

Complete Sample (23.18)

SpreadsheetFSM (9:25)