Very frequently, we receive questions about the difference between Recalculate and Refresh on the XLGL menu. Recently, someone asked the difference between the Excel recalculation shortcut (F9 and such) and XLGL Recalculate.
The quick answer is:
- Recalculate will update all formulas in the workbook, while making sure XLGL worksheet functions return the latest data from Simply Accounting;
- Refresh will update XLGL lists, adding/removing rows/columns as needed, and then recalculate everything.
Looking at the basic example of the Balance Sheet, Recalculate will update the balances, while Refresh will add new accounts to the list.
XLGL builds on the Excel calculation engine to provide flexibility and interactivity while keeping your report fully dynamic. There are two risks with this approach that the architecture of XLGL makes sure to avoid.
First, it could get very slow as each function act as a single query to the database. If a function takes 0.1 seconds to update because of network speed, you wouldn’t notice when you enter just one, but when you copy that over a column a hundred times, it would take 10 seconds to update.
Second, if the database changes while creating your report, you could end up with an unbalanced report. For example, imagine you create the assets part of a balance sheet. In the meantime, a transaction in Simply Accounting is made affecting both assets and liabilities. When you finish your report, you could have a report that does not balance.
XLGL avoids these problems with an architecture that buffers some part of the database. The upside is that only data needed to operate is fetched from the database into memory, resulting in very fast access and guaranteed to balance. The downside is that very recent transaction may not be available. This is very similar to the way Google operates.
So, what do you do to make sure you have the latest data from the database? This is where Recalculate and Refresh comes in. Using Excel’s recalculation shortcuts (F9) will only return the same data because the XLGL buffers are not refreshed. However, using XLGL Recalculate will refresh those buffers and then recalculate everything in the workbook, making sure everything still balance.
Now, data like account’s balance fit very nicely in a cell and can we can use the Excel calculation engine to update those easily. However, when the need come to enumerate data, such as a list of customers or transactions, then we need something else. This is why XLGL introduce Lists, an object similar to Excel’s QueryTable, but more powerful and adapted to our needs.
XLGL Lists, Excel QueryTables and PivotTables all have something in common. They will alter the worksheet by adding/removing rows and formatting. This is why Refresh functions are always kept separated from the calculation.
So, what should you use? Most of the time, you should be using Recalculate. Refresh is necessary when you have data that could add/remove rows/columns from your worksheet.
Refresh has giving some problems to people who lost their formatting and did not understand why. We are working on making this more user friendly. For now, just remember that Refresh can alter your workbook. If you’re unsure, save a copy of your work and test it.
Posted by Pierre Alain Carrier 
